本文目錄：

一、連接相關

二、長事務

三、元數據鎖

四、鎖等待

五、全局讀鎖

六、內存使用監控

七、分區表

八、數據庫信息概覽

九、長時間未更新的表

十、主鍵、索引

十一、存儲引擎

十二、實時負載

閱讀提示：

1）本篇文章涉及到大量 SQL 語句，在 PC 端閱讀效果更佳。

2）SQL 基於 Oracle MySQL 5.7 版本，其它版本因數據源不同不完全適用。

3）SQL 使用場景包含會話連接、元數據鎖、全局鎖、鎖等待、長事務、內存監控、分區表、低頻更新表、主鍵、索引、存儲引擎、實時負載

一、連接相關

查看某用戶連接的會話級別參數設置及狀態變量，用於觀測其它會話連接行為，輔助定位連接類問題

• 例：查看用戶連接 ID 為 19 的字符集設置，也可不指定 PROCESSLIST_ID 條件，查看所有用戶連接

<code>SELECT T1.VARIABLE_NAME,       T1.VARIABLE_VALUE,       T2.PROCESSLIST_ID,       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST),       T2.PROCESSLIST_DB,       T2.PROCESSLIST_COMMANDFROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1,     PERFORMANCE_SCHEMA.THREADS T2WHERE T1.THREAD_ID = T2.THREAD_ID  AND T1.VARIABLE_NAME LIKE 'character%'  AND PROCESSLIST_ID ='19';+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+| VARIABLE_NAME            | VARIABLE_VALUE | PROCESSLIST_ID | concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) | PROCESSLIST_DB | PROCESSLIST_COMMAND |+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+| character_set_client     | gbk            |             19 | root@localhost                                      | db             | Query               || character_set_connection | gbk            |             19 | root@localhost                                      | db             | Query               || character_set_database   | utf8mb4        |             19 | root@localhost                                      | db             | Query               || character_set_filesystem | binary         |             19 | root@localhost                                      | db             | Query               || character_set_results    | gbk            |             19 | root@localhost                                      | db             | Query               || character_set_server     | utf8mb4        |             19 | root@localhost                                      | db             | Query               |+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+6 rows in set (0.01 sec)/<code>

• 例：發現用戶 ID 為 254 的連接關閉了 sql_log_bin 設置

<code>SELECT T1.VARIABLE_NAME,       T1.VARIABLE_VALUE,       T2.PROCESSLIST_ID,       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',       T2.PROCESSLIST_DB,       T2.PROCESSLIST_COMMANDFROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1,     PERFORMANCE_SCHEMA.THREADS T2WHERE T1.THREAD_ID = T2.THREAD_ID  AND T1.VARIABLE_NAME LIKE 'sql_log_bin';+---------------+----------------+----------------+------------------+----------------+---------------------+| VARIABLE_NAME | VARIABLE_VALUE | PROCESSLIST_ID | User@Host        | PROCESSLIST_DB | PROCESSLIST_COMMAND |+---------------+----------------+----------------+------------------+----------------+---------------------+| sql_log_bin   | OFF            |            254 | root@localhost   | NULL           | Sleep               || sql_log_bin   | ON             |            256 | root@localhost   | NULL           | Sleep               || sql_log_bin   | ON             |            257 | [email protected] | NULL           | Sleep               || sql_log_bin   | ON             |            258 | [email protected] | NULL           | Sleep               || sql_log_bin   | ON             |            259 | root@localhost   | NULL           | Query               || sql_log_bin   | ON             |            261 | root@localhost   | NULL           | Sleep               |+---------------+----------------+----------------+------------------+----------------+---------------------+4 rows in set (0.00 sec)/<code>

• 例：查看用戶連接 ID 為 24 的網絡流量變化

<code>SELECT T1.VARIABLE_NAME,       T1.VARIABLE_VALUE,       T2.PROCESSLIST_ID,       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',       T2.PROCESSLIST_DB,       T2.PROCESSLIST_COMMANDFROM PERFORMANCE_SCHEMA.STATUS_BY_THREAD T1,     PERFORMANCE_SCHEMA.THREADS T2WHERE T1.THREAD_ID = T2.THREAD_ID  AND T2.PROCESSLIST_USER = 'root'  AND PROCESSLIST_ID= 24  AND VARIABLE_NAME LIKE 'Byte%';+----------------+----------------+----------------+----------------+----------------+---------------------+| VARIABLE_NAME  | VARIABLE_VALUE | PROCESSLIST_ID | User@Host      | PROCESSLIST_DB | PROCESSLIST_COMMAND |+----------------+----------------+----------------+----------------+----------------+---------------------+| Bytes_received | 224            |             24 | [email protected] | NULL           | Sleep               || Bytes_sent     | 182            |             24 | [email protected] | NULL           | Sleep               |+----------------+----------------+----------------+----------------+----------------+---------------------+2 rows in set (0.00 sec)/<code>

二、長事務

事務開啟後，超過 5s 未提交的用戶連接

<code>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_QUERYFROM INFORMATION_SCHEMA.INNODB_TRX trxJOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.idWHERE trx_mysql_thread_id != connection_id()  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 5 ;+----------------+---------------------+---------------------+---------------+------+-----------------+------+-----------+| PROCESSLIST_ID | NOW()               | TRX_STARTED         | TRX_LAST_TIME | User | Host            | DB   | TRX_QUERY |+----------------+---------------------+---------------------+---------------+------+-----------------+------+-----------+|             24 | 2019-12-16 02:49:52 | 2019-12-16 02:41:15 |           517 | root | 127.0.0.1:58682 | db   | NULL      |+----------------+---------------------+---------------------+---------------+------+-----------------+------+-----------+1 row in set (0.01 sec)/<code> 

三、元數據鎖

MySQL 5.7 開啟元數據鎖追蹤，以便追蹤定位元數據鎖相關的阻塞問題

<code>// 臨時開啟，動態生效UPDATE performance_schema.setup_consumersSET ENABLED = 'YES'WHERE NAME ='global_instrumentation';UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES'WHERE NAME ='wait/lock/metadata/sql/mdl';// 配置文件中添加，重啟生效performance-schema-instrument = wait/lock/metadata/sql/mdl=ON/<code>

場景 1：殺掉持有 MDL 鎖的會話，使 DDL 語句順利執行。

• DDL 語句被阻塞通常因為存在獲取資源後未及時提交釋放的長事務。因此，查找 kill 掉事務運行時間大於 DDL 運行時間的會話即可使 DDL 語句順利下發，SQL 語句如下：

<code>// 查找事務運行時間 >= DDL等待時間的線程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_QUERYFROM INFORMATION_SCHEMA.INNODB_TRX trxJOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.idWHERE 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%') ;+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+| PROCESSLIST_ID | NOW()               | TRX_STARTED         | TRX_LAST_TIME | User | Host      | DB   | TRX_QUERY |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+|            253 | 2019-12-24 01:42:11 | 2019-12-24 01:41:24 |            47 | root | localhost | NULL | NULL      |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+1 row in set (0.00 sec)// kill掉長事務，釋放持有的MDL資源kill 253;/<code>

注：因 MySQL 元數據信息記錄有限，此處可能誤殺無辜長事務，且誤殺無法完全避免。

• 當 kill 掉阻塞源後，可能存在 DDL 語句與被阻塞的 SQL 語句同時加鎖的情況，此時會出現事務開始時間等於 DDL 開始時間連接，此類事務也需 kill。

<code>//查找事務開始時間 = DDL語句事務開始時間的線程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_QUERYFROM INFORMATION_SCHEMA.INNODB_TRX trxJOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.idWHERE trx_mysql_thread_id != connection_id()  AND trx_started =    (SELECT MIN(trx_started)     FROM INFORMATION_SCHEMA.INNODB_TRX     GROUP BY trx_started HAVING count(trx_started)>=2)  AND TRX_QUERY NOT LIKE 'alter%table%'  OR TRX_QUERY IS NULL;+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+| PROCESSLIST_ID | NOW()               | TRX_STARTED         | TRX_LAST_TIME | User | Host      | DB   | TRX_QUERY |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+|            255 | 2019-12-24 01:42:44 | 2019-12-24 01:42:33 |            11 | root | localhost | NULL | NULL      |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+1 row in set (0.00 sec)//殺掉阻塞源kill 255;/<code> 

場景 2：kill 掉下發 DDL 語句的用戶連接，取消 DDL 語句下發，保障業務不被阻塞。

<code>// 查找DDL語句所在用戶連接SELECT *FROM INFORMATION_SCHEMA.PROCESSLISTWHERE INFO LIKE 'ALTER%TABLE%';+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+| ID  | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                             |+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+| 254 | root | localhost | NULL | Query   |  730 | Waiting for table metadata lock | alter table db.t1 add index (id) |+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+1 row in set (0.00 sec)// 殺掉DDL語句所在用戶連接kill 254;/<code>

四、鎖等待

查看鎖等待相關的阻塞線程、被阻塞線程信息及相關用戶、IP、PORT

<code>SELECT locked_table,       locked_index,       locked_type,       blocking_pid,       concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)",       blocking_lock_mode,       blocking_trx_rows_modified,       waiting_pid,       concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)",       waiting_lock_mode,       waiting_trx_rows_modified,       wait_age_secs,       waiting_queryFROM sys.x$innodb_lock_waits T1LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.IDLEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+| locked_table | locked_index | locked_type | blocking_pid | blocking(user@ip:port) | blocking_lock_mode | blocking_trx_rows_modified | waiting_pid | waiting(user@ip:port) | waiting_lock_mode | waiting_trx_rows_modified | wait_age_secs | waiting_query                   |+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+| `db`.`t1`    | PRIMARY      | RECORD      |          228 | [email protected]:56724    | X                  |                          1 |         231 | [email protected]:50852  | S                 |                         0 |             1 | insert into db.t1(id) values(2) |+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+1 row in set, 3 warnings (0.00 sec)/<code>

• 若不關心阻塞相關的用戶、IP、PORT，可直接查看 innodb_lock_waits 表信息。

<code>select * from sys.x$innodb_lock_waits\\G*************************** 1. row ***************************                wait_started: 2019-12-23 02:14:22                    wait_age: 00:00:32               wait_age_secs: 32                locked_table: `db`.`t1`                locked_index: PRIMARY                 locked_type: RECORD              waiting_trx_id: 7204404         waiting_trx_started: 2019-12-23 02:14:18             waiting_trx_age: 00:00:36     waiting_trx_rows_locked: 1   waiting_trx_rows_modified: 0                 waiting_pid: 213               waiting_query: delete from db.t1 where id=200             waiting_lock_id: 7204404:1994:3:4           waiting_lock_mode: X             blocking_trx_id: 7204394                blocking_pid: 207              blocking_query: select * from   sys.x$innodb_lock_waits            blocking_lock_id: 7204394:1994:3:4          blocking_lock_mode: X        blocking_trx_started: 2019-12-23 02:10:06            blocking_trx_age: 00:04:48    blocking_trx_rows_locked: 1  blocking_trx_rows_modified: 1     sql_kill_blocking_query: KILL QUERY 207sql_kill_blocking_connection: KILL 2071 row in set, 3 warnings (0.00 sec)/<code>

影響鎖等待超時的參數

五、全局讀鎖

PERFORMANCE_SCHEMA.METADATA_LOCKS 表 LOCK_DURATION 列為 EXPLICIT 狀態表示 FTWRL 語句添加，OBJECT_TYPE 出現 COMMIT 狀態表示已經加鎖成功

• 場景 1：殺掉添加 FTWRL 的會話，恢復業務運行

<code>SELECT processlist_id,       mdl.OBJECT_TYPE,       OBJECT_SCHEMA,       OBJECT_NAME,       LOCK_TYPE,       LOCK_DURATION,       LOCK_STATUSFROM performance_schema.metadata_locks mdlINNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_idAND processlist_id <> connection_id()AND LOCK_DURATION='EXPLICIT';+----------------+-------------+---------------+-------------+-----------+---------------+-------------+| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |+----------------+-------------+---------------+-------------+-----------+---------------+-------------+|            231 | GLOBAL      | NULL          | NULL        | SHARED    | EXPLICIT      | GRANTED     ||            231 | COMMIT      | NULL          | NULL        | SHARED    | EXPLICIT      | GRANTED     |+----------------+-------------+---------------+-------------+-----------+---------------+-------------+2 rows in set (0.00 sec)// 殺掉添加FTWRL的用戶連接kill 231;/<code>

• 場景 2：殺掉語句執行時間大於 FTWRL 執行時間的線程，確保 FTWRL 下發成功

<code>SELECT T2.THREAD_ID,       T1.ID AS PROCESSLIST_ID,       T1.User,       T1.Host,       T1.db,       T1.Time,       T1.State,       T1.Info,       T3.TRX_STARTED,       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIMEFROM INFORMATION_SCHEMA.processlist T1LEFT JOIN PERFORMANCE_SCHEMA.THREADS T2 ON T1.ID=T2.PROCESSLIST_IDLEFT JOIN INFORMATION_SCHEMA.INNODB_TRX T3 ON T1.id=T3.trx_mysql_thread_idWHERE T1.TIME >=    (SELECT MAX(Time)     FROM INFORMATION_SCHEMA.processlist     WHERE INFO LIKE 'flush%table%with%read%lock')  AND Info IS NOT NULL;+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+| THREAD_ID | PROCESSLIST_ID | User | Host              | db   | Time | State                   | Info                                        | TRX_STARTED         | TRX_LAST_TIME |+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+|       284 |            246 | root | localhost         | NULL |  364 | User sleep              | select * from db.t1 where sleep(1000000000) | 2019-12-23 14:57:23 |           364 ||       286 |            248 | root | 10.211.55.2:55435 | NULL |  232 | Waiting for table flush | flush table with read lock                  | NULL                |          NULL |+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+2 rows in set (0.00 sec)/<code>

六、內存使用監控

默認只對 performance_schema 庫進行內存統計，對全局內存統計需要手工開啟

<code>//動態開啟，開啟後開始統計update performance_schema.setup_instruments setenabled = 'yes' where name like 'memory%';//配置文件中添加，重啟生效performance-schema-instrument='memory/%=COUNTED'/<code>

• 查看實例內存消耗分佈，sys 庫下有多張 memory 相關視圖用於協助用戶定位分析內存溢出類問題

<code>SELECT event_name,       current_allocFROM sys.memory_global_by_current_bytesWHERE event_name LIKE 'memory%innodb%';+-------------------------------------------+---------------+| event_name                                | current_alloc |+-------------------------------------------+---------------+| memory/innodb/buf_buf_pool                | 134.31 MiB    || memory/innodb/log0log                     | 32.01 MiB     || memory/innodb/mem0mem                     | 15.71 MiB     || memory/innodb/lock0lock                   | 12.21 MiB     || memory/innodb/os0event                    | 8.37 MiB      || memory/innodb/hash0hash                   | 4.74 MiB      |...+-------------------------------------------+---------------+42 rows in set (0.01 sec)/<code>

七、分區表

• 查看實例中的分區表相關信息

<code>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_SIZEFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_NAME NOT IN ('sys',                         'mysql',                         'INFORMATION_SCHEMA',                         'performance_schema')  AND PARTITION_NAME IS NOT NULLGROUP BY TABLE_SCHEMA,         TABLE_NAMEORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;+--------------+------------------+-----------------+------------------+-------------+--------------+------------+| TABLE_SCHEMA | TABLE_NAME       | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |+--------------+------------------+-----------------+------------------+-------------+--------------+------------+| db           | t1               |             365 |                0 | 5.70M       | 17.11M       | 22.81M     || db           | t2               |             391 |                0 | 6.11M       | 0.00M        | 6.11M      || db           | t3               |               4 |            32556 | 2.28M       | 0.69M        | 2.97M      || db           | t4               |              26 |                0 | 0.41M       | 2.44M        | 2.84M      || db           | t5               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      || db           | t6               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |+--------------+------------------+-----------------+------------------+-------------+--------------+------------+6 rows in set (1.04 sec)/<code>

• 查看某分區表具體信息，此處以庫名為 db、表名為 e 的分區表為例

<code>SELECT TABLE_SCHEMA,       TABLE_NAME,       PARTITION_NAME,       PARTITION_EXPRESSION,       PARTITION_METHOD,       PARTITION_DESCRIPTION,       TABLE_ROWS,       CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH,       CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH,       CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZEFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA NOT IN ('sys',                         'mysql',                         'INFORMATION_SCHEMA',                         'performance_schema')  AND PARTITION_NAME IS NOT NULL  AND TABLE_SCHEMA='db'  AND TABLE_NAME='e';+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+| db           | e          | p0             | id                   | RANGE            | 50                    |       4096 | 0.20M       | 0.09M        | 0.30M      || db           | e          | p1             | id                   | RANGE            | 100                   |       6144 | 0.28M       | 0.13M        | 0.41M      || db           | e          | p2             | id                   | RANGE            | 150                   |       6144 | 0.28M       | 0.13M        | 0.41M      || db           | e          | p3             | id                   | RANGE            | MAXVALUE              |      16172 | 1.52M       | 0.34M        | 1.86M      |+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+4 rows in set (0.00 sec)/<code>

八、數據庫信息概覽

• 統計實例中各數據庫大小

<code>SELECT TABLE_SCHEMA,       round(SUM(data_length+index_length)/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,       COUNT(*) AS TABLESFROM INFORMATION_SCHEMA.tablesWHERE TABLE_SCHEMA NOT IN ('sys',                           'mysql',                           'INFORMATION_SCHEMA',                           'performance_schema')GROUP BY TABLE_SCHEMAORDER BY 2 DESC;+--------------+----------+---------+----------+--------+| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | TABLES |+--------------+----------+---------+----------+--------+| cloud        |   229.84 |  223.02 |     6.83 |     41 || db           |    66.42 |   30.56 |    35.86 |     31 || dks          |    14.41 |    9.70 |     4.70 |    621 || test         |     0.06 |    0.06 |     0.00 |      4 || db2          |     0.03 |    0.03 |     0.00 |      2 |+--------------+----------+---------+----------+--------+5 rows in set, 1 warning (0.91 sec)/<code>

• 統計某庫下各表大小

<code>SELECT TABLE_SCHEMA,       TABLE_NAME TABLE_NAME,                  CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,                  CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,                  CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,                  engineFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,                           'performance_schema',                           'sys',                           'mysql')  AND TABLE_SCHEMA='db'ORDER BY (data_length + index_length) DESC LIMIT 10;+--------------+-----------------------+-------------+--------------+------------+--------+| TABLE_SCHEMA | table_name            | data_length | index_length | total_size | engine |+--------------+-----------------------+-------------+--------------+------------+--------+| db           | t1                    | 5.70M       | 22.81M       | 28.52M     | InnoDB || db           | t2                    | 15.19M      | 9.59M        | 24.78M     | InnoDB || db           | t3                    | 6.11M       | 0.00M        | 6.11M      | InnoDB || db           | t4                    | 2.28M       | 0.69M        | 2.97M      | InnoDB || db           | t5                    | 0.41M       | 2.44M        | 2.84M      | InnoDB || db           | t6                    | 0.17M       | 0.00M        | 0.17M      | InnoDB || db           | t7                    | 0.17M       | 0.00M        | 0.17M      | InnoDB || db           | t8                    | 0.02M       | 0.11M        | 0.13M      | InnoDB || db           | t9                    | 0.08M       | 0.00M        | 0.08M      | InnoDB || db           | t10                   | 0.05M       | 0.02M        | 0.06M      | InnoDB |+--------------+-----------------------+-------------+--------------+------------+--------+10 rows in set, 1 warning (0.01 sec)/<code>

• 查看某庫下表的基本信息

<code>SELECT TABLE_SCHEMA,       TABLE_NAME,       table_collation,       engine,       table_rowsFROM INFORMATION_SCHEMA.tablesWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,                           'sys',                           'mysql',                           'performance_schema')  AND TABLE_TYPE='BASE TABLE'  AND TABLE_SCHEMA='db'ORDER BY table_rows DESC ;+--------------+-----------------------+--------------------+--------+------------+| TABLE_SCHEMA | table_name            | table_collation    | engine | table_rows |+--------------+-----------------------+--------------------+--------+------------+| db           | t1                    | utf8_general_ci    | InnoDB |     159432 || db           | t2                    | utf8mb4_general_ci | InnoDB |      32556 || db           | t3                    | utf8mb4_general_ci | InnoDB |       2032 |...| db           | t100                  | utf8mb4_general_ci | InnoDB |          0 || db           | t101                  | utf8mb4_general_ci | InnoDB |          0 |+--------------+-----------------------+--------------------+--------+------------+25 rows in set, 1 warning (0.01 sec)/<code>

九、長時間未更新的表

UPDATE_TIME 為 NULL 表示實例啟動後一直未更新過

<code>SELECT TABLE_SCHEMA,       TABLE_NAME,       UPDATE_TIMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('SYS',                           'MYSQL',                           'INFORMATION_SCHEMA',                           'PERFORMANCE_SCHEMA')  AND TABLE_TYPE='BASE TABLE'ORDER BY UPDATE_TIME ;+--------------+-----------------------+---------------------+| TABLE_SCHEMA | TABLE_NAME            | UPDATE_TIME         |+--------------+-----------------------+---------------------+| db           | t1                    | NULL                || db           | t2                    | NULL                || db           | t3                    | NULL                || db           | t4                    | 2019-12-16 07:45:29 || db           | t5                    | 2019-12-16 16:52:01 |+--------------+-----------------------+---------------------+22 rows in set, 1 warning (0.01 sec)/<code>

十、主鍵、索引

無主鍵、唯一鍵及二級索引基表

• MySQL Innodb 存儲引擎為索引組織表，因此設置合適的主鍵字段對性能至關重要

<code>SELECT T1.TABLE_SCHEMA,       T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS',                           'MYSQL',                           'INFORMATION_SCHEMA',                           'PERFORMANCE_SCHEMA')  AND T2.TABLE_TYPE='BASE TABLE'  AND T1.TABLE_SCHEMA='db'GROUP BY T1.TABLE_SCHEMA,         T1.TABLE_NAME HAVING MAX(COLUMN_KEY)='';/<code>

無主鍵、唯一鍵，僅有二級索引表

• 該類型表因無高效索引，因此從庫回放時容易導致複製延遲

<code>SELECT T1.TABLE_SCHEMA,       T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS  T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS',                           'MYSQL',                           'INFORMATION_SCHEMA',                           'PERFORMANCE_SCHEMA')  AND T2.TABLE_TYPE='BASE TABLE'  AND T1.COLUMN_KEY != ''GROUP BY T1.TABLE_SCHEMA,         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';/<code>

僅有主鍵、唯一鍵表

• 該類型表結構因無二級索引，可能導致應用 SQL 語句上線後頻繁全表掃描出現性能抖動

<code>SELECT T1.TABLE_SCHEMA,       T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS',                           'MYSQL',                           'INFORMATION_SCHEMA',                           'PERFORMANCE_SCHEMA')  AND T2.TABLE_TYPE='BASE TABLE'  AND T1.COLUMN_KEY != ''  AND T1.TABLE_SCHEMA='db'GROUP BY T1.TABLE_SCHEMA,         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'MUL';/<code>

無主鍵、唯一鍵表

<code>SELECT T1.TABLE_SCHEMA,       T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS',                           'MYSQL',                           'INFORMATION_SCHEMA',                           'PERFORMANCE_SCHEMA')AND   T2.TABLE_TYPE='BASE TABLE'GROUP BY T1.TABLE_SCHEMA,         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';/<code>

十一、存儲引擎

• 存儲引擎分佈

<code>SELECT TABLE_SCHEMA,       ENGINE,       COUNT(*)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,                           'PERFORMANCE_SCHEMA',                           'SYS',                           'MYSQL')  AND TABLE_TYPE='BASE TABLE'GROUP BY TABLE_SCHEMA,         ENGINE;/<code>

• 非 INNODB 存儲引擎表

<code>SELECT TABLE_SCHEMA,       TABLE_NAME,       TABLE_COLLATION,       ENGINE,       TABLE_ROWSFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,                           'SYS',                           'MYSQL',                           'PERFORMANCE_SCHEMA')  AND TABLE_TYPE='BASE TABLE'  AND ENGINE NOT IN ('INNODB')ORDER BY TABLE_ROWS DESC ;/<code>

十二、實時負載

<code>while truedomysqladmin -uroot -pxxxxxxx extended-status -r -i 1 -c 30 --socket=/mysqldata/mysqld.sock 2>/dev/null|awk -F"|" "BEGIN{ count=0 ;}"'{ if($2 ~ /Variable_name/ && ++count == 1){\\    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\\    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\\}\\else if ($2 ~ /Queries/){queries=$3;}\\else if ($2 ~ /Com_select /){com_select=$3;}\\else if ($2 ~ /Com_insert /){com_insert=$3;}\\else if ($2 ~ /Com_update /){com_update=$3;}\\else if ($2 ~ /Com_delete /){com_delete=$3;}\\else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\\else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\\else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\\else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\\else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\\else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\\else if ($2 ~ /Uptime / && count >= 2){\\  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\\  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\\  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\\  printf("|%10d %11d\\n",innodb_lor,innodb_phr);\\}}'done/<code>

閱讀更多 愛可生 的文章

關鍵字: 語句 數據庫 文章