MySQL DBA 常用工具 SQL

【前言】

本篇文章介绍一些 MySQL 管理的实用语句及适用的场景。SQL 基于 MySQL 5.7 版本。

1. 长事务

事务长时间未提交，即使状态为Sleep也可能造成一些锁等待的问题，使用该查询可以查出未提交的事物，常常用于辅助定位问题。

select proc.id, 
       proc.user, 
       proc.HOST,
       proc.DB,
       proc.COMMAND,
       proc.STATE,
       proc.INFO,
       TIMESTAMPDIFF(SECOND, trx.trx_started, now()) as Trx_time
from information_schema.INNODB_TRX as trx join information_schema.processlist proc 
on trx.trx_mysql_thread_id = proc.id;

2. 锁等待

通过下方 SQL 可以查到被堵塞的 SQL 及堵塞源，再通过其它 SQL 辅助基本就可以分析定位到问题。

-- waiting_trx_id: 被阻塞的事务 ID
-- waiting_thread: 被阻塞的 mysql 线程 ID
-- waiting_query: 被阻塞的 SQL 语句
-- blocking_trx_id: 阻塞者的事务 ID
-- blocking_thread: 阻塞者的 mysql 线程 ID
-- blocking_query: 阻塞者的 SQL 语句
SELECT 
  r.trx_id AS waiting_trx_id, 
  r.trx_mysql_thread_id AS waiting_thread, 
  r.trx_query AS waiting_query, 
  b.trx_id AS blocking_trx_id, 
  b.trx_mysql_thread_id AS blocking_thread, 
  b.trx_query AS blocking_query 
FROM 
  information_schema.innodb_lock_Waits w 
  JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id 
  JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id

案例：

• Session 1：开启一个事物，然后删除一行记录，未提交；

• Session 2：删除一定范围的数据，与 Session 1 行冲突，发生锁等待。

下面是 show processlist 的结果，如果是生产环境会话很多，基本看不出堵塞源：

+----+------+-----------+--------------------+---------+------+----------+---------------------------------------+
| Id | User | Host      | db                 | Command | Time | State    | Info                                  |
+----+------+-----------+--------------------+---------+------+----------+---------------------------------------+
|  4 | root | localhost | sbtest             | Sleep   | 1430 |          | NULL                                  |
|  5 | root | localhost | sbtest             | Query   |    7 | updating | delete from archive_log where id < 10 |
|  7 | root | localhost | information_schema | Query   |    0 | starting | show processlist                      |
+----+------+-----------+--------------------+---------+------+----------+---------------------------------------+

下面是使用该 SQL 查询到的结果，可以明显看出 id = 4 的会话有问题 blocking_query = NULL 就可以猜测是一个长时间未提交的事物，此时可以使用上面长事务的 SQL 去核实。

+----------------+----------------+---------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query                         | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+---------------------------------------+-----------------+-----------------+----------------+
| 8221           |              5 | delete from archive_log where id < 10 | 8210            |               4 | NULL           |
+----------------+----------------+---------------------------------------+-----------------+-----------------+----------------+

此时我们定位到堵塞源是一条长时间未提交的会话，那么如果想知道该会话最后执行的一条 SQL 如何查看呢？

select * from performance_schema.threads where PROCESSLIST_ID = 4\G

*************************** 1. row ***************************
          THREAD_ID: 28
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 4
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: sbtest
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 3073
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: delete from archive_log where id = 1
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 30344

3. MDL 锁

SELECT 
  trx_mysql_thread_id AS PROCESSLIST_ID, 
  NOW(), 
  TRX_STARTED, 
  TO_SECONDS(now())- TO_SECONDS(trx_started) AS TRX_LAST_TIME, 
  USER, 
  HOST, 
  DB, 
  TRX_QUERY 
FROM 
  INFORMATION_SCHEMA.INNODB_TRX trx 
  JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id = pcl.id 
WHERE 
  trx_mysql_thread_id != connection_id() 
  AND TO_SECONDS(now())- TO_SECONDS(trx_started) >= (
    SELECT 
      MAX(Time) 
    FROM 
      INFORMATION_SCHEMA.processlist 
    WHERE 
      STATE = 'Waiting for table metadata lock' 
      AND INFO LIKE 'alter%table%' 
      OR INFO LIKE 'truncate%table%'
  );

4. 数据量大小

通过下方 SQL 可以查询所有库的数据量及表数据等。

SELECT TABLE_SCHEMA,
       round(SUM(data_length + index_length + DATA_FREE) / 1024 / 1024, 2) AS TOTAL_MB,
       round(SUM(data_length) / 1024 / 1024, 2)                            AS DATA_MB,
       round(SUM(index_length) / 1024 / 1024, 2)                           AS INDEX_MB,
       round(SUM(DATA_FREE) / 1024 / 1024, 2)                              AS FREE_MB,
       COUNT(*)                                                            AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;

+--------------+----------+---------+----------+---------+--------+
| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | FREE_MB | TABLES |
+--------------+----------+---------+----------+---------+--------+
| new_data     |   625.09 |  621.02 |     0.08 |    4.00 |      2 |
| sbtest       |   358.55 |  323.45 |    26.09 |    9.00 |      5 |
| archery      |     1.47 |    0.70 |     0.77 |    0.00 |     45 |
| test         |     0.35 |    0.08 |     0.27 |    0.00 |      9 |
+--------------+----------+---------+----------+---------+--------+

5. 分区表统计

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME, 
  count(PARTITION_NAME) AS PARTITION_COUNT, 
  sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS, 
  CONCAT(
    ROUND(
      SUM(DATA_LENGTH) / (1024 * 1024), 
      2
    ), 
    'M'
  ) DATA_LENGTH, 
  CONCAT(
    ROUND(
      SUM(INDEX_LENGTH) / (1024 * 1024), 
      2
    ), 
    'M'
  ) INDEX_LENGTH, 
  CONCAT(
    ROUND(
      ROUND(
        SUM(DATA_LENGTH + INDEX_LENGTH)
      ) / (1024 * 1024), 
      2
    ), 
    'M'
  ) TOTAL_SIZE 
FROM 
  INFORMATION_SCHEMA.PARTITIONS 
WHERE 
  TABLE_NAME NOT IN (
    'sys', 'mysql', 'INFORMATION_SCHEMA', 
    'performance_schema'
  ) 
  AND PARTITION_NAME IS NOT NULL 
GROUP BY 
  TABLE_SCHEMA, 
  TABLE_NAME 
ORDER BY 
  sum(DATA_LENGTH + INDEX_LENGTH) DESC;

6. 存储引擎数量

用来确认是否有非 innodb 的表，MySQL 5.7 后新增 disabled_storage_engines参数可以禁用某个存储引擎。

SELECT 
  TABLE_SCHEMA, 
  ENGINE, 
  COUNT(*) 
FROM 
  INFORMATION_SCHEMA.TABLES 
WHERE 
  TABLE_SCHEMA NOT IN (
    'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 
    'SYS', 'MYSQL'
  ) 
  AND TABLE_TYPE = 'BASE TABLE' 
GROUP BY 
  TABLE_SCHEMA, 
  ENGINE;

7.  冗余索引

冗余索引不仅消耗磁盘空间，还会影响数据库性能。可以通过下方 SQL 查询冗余索引。

select *  from sys.schema_redundant_indexes\G

建议定期巡检，如果发现有冗余索引，建议立即处理掉。

*************************** 1. row ***************************
              table_schema: new_data
                table_name: quality_check_log
      redundant_index_name: idx_order_line_num
   redundant_index_columns: order_line_num
redundant_index_non_unique: 1
       dominant_index_name: uniq_orderLineNum_type
    dominant_index_columns: order_line_num,type,is_deleted
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `new_data`.`quality_check_log` DROP INDEX `idx_order_line_num`

下方 SQL 可以查未使用的索引，或者说是 MySQL 认为这些索引存在无意义 The schema_unused_indexes View 可供参考，删除索引是一件非常危险的事，还是需要多 check。

select * from sys.schema_unused_indexes;

select a.`table_schema`,
       a.`table_name`,
       a.`index_name`,
       a.`index_columns`,
       b.`index_name`,
       b.`index_columns`,
       concat('ALTER TABLE `', a.`table_schema`, '`.`', a.`table_name`, '` DROP INDEX `', a.`index_name`, '`')
  from((
select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`, `information_schema`.`statistics`.`TABLE_NAME` AS `table_name`, `information_schema`.`statistics`.`INDEX_NAME` AS `index_name`, max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`, max(if(isnull(`information_schema`.`statistics`.`SUB_PART`), 0, 1)) AS `subpart_exists`, group_concat(`information_schema`.`statistics`.`COLUMN_NAME`
 order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns`
  from `information_schema`.`statistics`
 where((`information_schema`.`statistics`.`INDEX_TYPE`= 'BTREE')
   and(`information_schema`.`statistics`.`TABLE_SCHEMA` not in('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')))
 group by `information_schema`.`statistics`.`TABLE_SCHEMA`, `information_schema`.`statistics`.`TABLE_NAME`, `information_schema`.`statistics`.`INDEX_NAME`) a join(
select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`, `information_schema`.`statistics`.`TABLE_NAME` AS `table_name`, `information_schema`.`statistics`.`INDEX_NAME` AS `index_name`, max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`, max(if(isnull(`information_schema`.`statistics`.`SUB_PART`), 0, 1)) AS `subpart_exists`, group_concat(`information_schema`.`statistics`.`COLUMN_NAME`
 order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns`
  from `information_schema`.`statistics`
 where((`information_schema`.`statistics`.`INDEX_TYPE`= 'BTREE')
   and(`information_schema`.`statistics`.`TABLE_SCHEMA` not in('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')))
 group by `information_schema`.`statistics`.`TABLE_SCHEMA`, `information_schema`.`statistics`.`TABLE_NAME`, `information_schema`.`statistics`.`INDEX_NAME`) b on(((a.`table_schema`= b.`table_schema`)
   and(a.`table_name`= b.`table_name`))))
 where((a.`index_name`<> b.`index_name`)
   and(((a.`index_columns`= b.`index_columns`)
   and((a.`non_unique`> b.`non_unique`)
    or((a.`non_unique`= b.`non_unique`)
   and(if((a.`index_name`= 'PRIMARY'), '', a.`index_name`)> if((b.`index_name`= 'PRIMARY'), '', b.`index_name`)))))
    or((locate(concat(a.`index_columns`, ','), b.`index_columns`)= 1)
   and(a.`non_unique`= 1))
    or((locate(concat(b.`index_columns`, ','), a.`index_columns`)= 1)
   and(b.`non_unique`= 0)))) ;

8. 表自增 ID 监控

select * from schema_auto_increment_columns;

9. 无主键表

SELECT table_schema,
       table_name
FROM information_schema.TABLES
WHERE (table_schema, table_name) NOT IN
      (SELECT DISTINCT table_schema, table_name FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI')
  AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');

10. MySQL 文件大小

select FILE_NAME, concat(TOTAL_EXTENTS, ' MB') AS TOTAL_MB from information_schema.files order by TOTAL_EXTENTS desc;