常用有效检测数据库运行状态SQL脚本

-- 查看正在执行的SQL
select * from information_schema.`PROCESSLIST` where command !='Sleep' AND db !='null'
\G

substring_index(host, ':', 1) as host

-- 查看所有连接的客户端ip
SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;

-- 查看数据库中的锁请求信息(5.6/5.7)
SELECT r.`trx_id` waiting_trx_id,
        r.`trx_mysql_thread_id` waiting_thread,
        r.`trx_query` waiting_query,
        b.`trx_id` bolcking_trx_id,
        b.`trx_mysql_thread_id` blocking_thread,
        b.`trx_query` block_query
 FROM information_schema.`INNODB_LOCK_WAITS` w
 INNER JOIN information_schema.`INNODB_TRX` b
 ON b.`trx_id`= w.`blocking_trx_id`
 INNER JOIN information_schema.`INNODB_TRX` r
 ON r.`trx_id`= w.`requesting_trx_id`;


-- 查看数据库中的锁请求信息(8.0)
SELECT * FROM performance_schema.data_lock_waits
SELECT * FROM performance_schema.data_locks

 
-- 表中查看当前未提交的事务
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G


-- 查看innodb状态
show engine innodb status\G


-- 5.6 定位导致 DDL 被阻塞的会话
-- 如果 MySQL 5.7 中 MDL 相关的 instrument 没有打开或在 MySQL 5.6 中，可使用该方法
SELECT concat('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i, (
    SELECT MAX(time) AS max_time
    FROM information_schema.processlist
    WHERE state = 'Waiting for table metadata lock'
      AND (info LIKE 'alter%'
      OR info LIKE 'create%'
      OR info LIKE 'drop%'
      OR info LIKE 'truncate%'
      OR info LIKE 'rename%'
  )) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;


-- 5.7+ 定位导致 DDL 被阻塞的会话
-- MySQL 5.7 中，MDL 相关的 instrument 默认没有打开   select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
-- 临时开启：   UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'  WHERE NAME = 'wait/lock/metadata/sql/mdl';
-- 修改配置文件： performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
  AND (waiting_query LIKE 'alter%'
  OR waiting_query LIKE 'create%'
  OR waiting_query LIKE 'drop%'
  OR waiting_query LIKE 'truncate%'
  OR waiting_query LIKE 'rename%');


-- 查看数据库中不为 InnoDB 引擎的表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA NOT IN
      ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
   AND ENGINE != 'InnoDB';


-- 查看总的数据库大小
select SUM(DATA) from ( 
SELECT TABLE_SCHEMA ,SUM(DATA) as data FROM (
SELECT TABLE_SCHEMA, 
CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2), 'GB') AS DATA
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN 
('sys', 'mysql', 'performance_schema', 'information_schema', 'test') )X GROUP BY TABLE_SCHEMA
) xx;


-- 查看各个数据库大小
SELECT TABLE_SCHEMA ,SUM(DATA) FROM (
SELECT TABLE_SCHEMA, 
CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2), 'GB') AS DATA
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN 
('sys', 'mysql', 'performance_schema', 'information_schema', 'test') )X GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC ;


-- 查看每个表大小
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS,
       CONCAT(round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), 'MB') as data
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN  ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')  
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;


-- 查找数据库中无显式主键索引的表
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
  FROM information_schema.TABLES t
 WHERE (t.TABLE_SCHEMA, t.TABLE_NAME) NOT IN
       (SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
          FROM information_schema.COLUMNS
         WHERE COLUMN_KEY = 'PRI')
   AND t.TABLE_SCHEMA NOT IN
       ('sys', 'mysql', 'performance_schema', 'information_schema', 'test');


-- 查找数据库中主键为联合主键的表
SELECT TABLE_SCHEMA, TABLE_NAME,
       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') cols,
       MAX(SEQ_IN_INDEX) len
  FROM information_schema.STATISTICS
 WHERE INDEX_NAME = 'PRIMARY'
   AND TABLE_SCHEMA NOT IN
       ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
 GROUP BY TABLE_SCHEMA, TABLE_NAME
HAVING len > 1;


-- 查找数据库中不为自增主键的表
SELECT TABLE_SCHEMA, TABLE_NAME
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
   AND (TABLE_SCHEMA,TABLE_NAME) NOT IN 
       (SELECT TABLE_SCHEMA, TABLE_NAME
          FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
           AND IS_NULLABLE = 'NO'
           AND COLUMN_TYPE LIKE '%int%'
           AND COLUMN_KEY = 'PRI'
           AND EXTRA = 'auto_increment');


-- 查看数据库中存在外键约束的表
SELECT c.TABLE_SCHEMA,
       c.REFERENCED_TABLE_NAME,
       c.REFERENCED_COLUMN_NAME,
       c.TABLE_NAME,
       c.COLUMN_NAME,
       c.CONSTRAINT_NAME,
       t.TABLE_COMMENT,
       r.UPDATE_RULE,
       r.DELETE_RULE
  FROM information_schema.KEY_COLUMN_USAGE c
  JOIN information_schema.TABLES t
    ON t.TABLE_NAME = c.TABLE_NAME
  JOIN information_schema.REFERENTIAL_CONSTRAINTS r
    ON r.TABLE_NAME = c.TABLE_NAME
   AND r.CONSTRAINT_NAME = c.CONSTRAINT_NAME
   AND r.REFERENCED_TABLE_NAME = c.REFERENCED_TABLE_NAME
 WHERE c.REFERENCED_TABLE_NAME IS NOT NULL;


-- 查找数据库中低区分度索引（区分度小于0.1）
SELECT p.TABLE_SCHEMA, p.TABLE_NAME, c.INDEX_NAME, c.car, p.car total
  FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(CARDINALITY) car
          FROM information_schema.STATISTICS
         WHERE INDEX_NAME != 'PRIMARY'
           AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
         GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) c
 INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, MAX(CARDINALITY) car
               from information_schema.STATISTICS
              WHERE INDEX_NAME = 'PRIMARY'
                AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
              GROUP BY TABLE_SCHEMA, TABLE_NAME) p
    ON c.TABLE_NAME = p.TABLE_NAME
   AND c.TABLE_SCHEMA = p.TABLE_SCHEMA
 WHERE p.car > 0
   AND c.car / p.car < 0.1;


-- 查找数据库中重复索引前缀的索引
 SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, a.cols, b.INDEX_NAME, b.cols
  FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
               CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols
          FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
           AND INDEX_NAME != 'PRIMARY'
         GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a
 INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
                    CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols
               FROM information_schema.STATISTICS
              WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
                AND INDEX_NAME != 'PRIMARY'
              GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) b
    ON a.TABLE_NAME = b.TABLE_NAME
   AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
   AND a.cols LIKE CONCAT(b.cols, '%')
   AND a.INDEX_NAME != b.INDEX_NAME;


-- 查找数据库中包索引重复包含主键列的索引
SELECT a.*, b.pk
  FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
               CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') cols
          FROM information_schema.STATISTICS
         WHERE INDEX_NAME != 'PRIMARY'
           AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
         GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a
 INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME,
                    CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') pk
               FROM information_schema.STATISTICS
              WHERE INDEX_NAME = 'PRIMARY'
                AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
              GROUP BY TABLE_SCHEMA, TABLE_NAME) b
    ON a.TABLE_NAME = b.TABLE_NAME
   AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
   AND a.cols LIKE CONCAT('%', b.pk, '%');


-- 查找数据库中没有被使用的索引
SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS
  FROM performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE a
 INNER JOIN information_schema.TABLES b
    ON a.OBJECT_SCHEMA = b.TABLE_SCHEMA
   AND a.OBJECT_NAME = b.TABLE_SCHEMA
 WHERE a.INDEX_NAME IS NOT NULL
   AND a.INDEX_NAME != 'PRIMARY'
   AND a.COUNT_STAR = 0
   AND OBJECT_SCHEMA NOT IN
       ('sys', 'mysql', 'performance_schema', 'information_schema')
 ORDER BY OBJECT_SCHEMA, OBJECT_NAME;


-- 查找数据库中没有创建索引的表
SELECT t.table_schema,t.table_schema FROM information_schema.tables AS t
LEFT JOIN (SELECT DISTINCT table_schema, table_name FROM information_schema.`KEY_COLUMN_USAGE` ) AS kt ON kt.table_schema=t.table_schema AND kt.table_name = t.table_name
WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'test','sys','zabbix') AND kt.table_name IS NULL


-- 查找冗余索引（5.7之后）
SELECT * FROM  sys.schema_redundant_indexes


-- 查找无效索引（5.7之后）
SELECT * FROM  sys.schema_unused_indexes


-- 查找低效索引
SELECT i.database_name AS `db`,
       i.table_name AS `table`,
       i.index_name AS `index_name`,
       i.stat_description AS `cols`,
       i.stat_value AS `defferRows`,
       t.n_rows AS `ROWS`,
       ROUND(((i.stat_value / IFNULL(IF(t.n_rows < i.stat_value,i.stat_value,t.n_rows),0.01))),2) AS sel_persent
FROM mysql.innodb_index_stats i INNER JOIN mysql.innodb_table_stats t
ON i.database_name = t.database_name AND i.table_name= t.table_name
WHERE i.index_name != 'PRIMARY' AND i.stat_name LIKE '%n_diff_pfx%';

  
-- 查询某个数据库中所有包含数据记录的表名
select TABLE_NAME 
from information_schema.TABLES 
where TABLE_SCHEMA = '需要查询的数据库名' and TABLE_ROWS > 0;


-- 查找某个用户下索引
SELECT a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
where a.table_schema='XXX' and a.index_name !='PRIMARY'
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name
order by 1,2,3,4


-- 查看数据库中有大写的表
SELECT table_schema,table_name FROM information_schema.tables
WHERE table_schema NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
AND table_name REGEXP BINARY '[A-Z]';


-- 查看MySQL已经运行的时长
status

或者
\s


-- 查看历史SQL执行情况
select t1.THREAD_ID,t1.SQL_TEXT,t1.CURRENT_SCHEMA,\
  t1.NESTING_EVENT_TYPE,t1.ROWS_EXAMINED, 
  DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status \
  WHERE VARIABLE_NAME='UPTIME') - t1.TIMER_START*10e-13 second) AS 'start_time',
  ROUND(t1.timer_wait*10E-10, 3) AS 'wait in (ms)' \
from performance_schema.events_statements_history t1 \
join performance_schema.threads t2  on t1.THREAD_ID=t2.THREAD_ID \
where t2.PROCESSLIST_ID in(2154,2163) order by TIMER_START;


-- 在从库查看主库host、账号、密码、端口号等信息
select * from mysql.slave_master_info \G


-- 查看MYSQL数据库哪些表使用了分区
SELECT 
    table_name, 
    COUNT(DISTINCT partition_ordinal_position) AS num_partitions 
FROM 
    information_schema.partitions 
GROUP BY 
    table_name 
HAVING 
    num_partitions > 1;