DBA 常用SQL
本文目录：
一、连接相关
二、长事务
三、元数据锁
四、锁等待
五、全局读锁
六、内存使用监控
七、分区表
八、数据库信息概览
九、长时间未更新的表
十、主键、索引
十一、存储引擎
十二、实时负载
阅读提示：
1）本篇文章涉及到大量 SQL 语句，在** PC 端阅读效果更佳。
2）SQL 基于 Oracle MySQL 5.7 版本，其它版本因数据源不同不完全适用。
3）SQL 使用场景包含会话连接、元数据锁、全局锁、锁等待、长事务、内存监控、分区表、低频更新表、主键、索引、存储引擎、实时负载**属于工具型文章，建议收藏保存以便后续查看。
一、连接相关
查看某用户连接的会话级别参数设置及状态变量，用于观测其它会话连接行为，辅助定位连接类问题
例：查看用户连接 ID 为 19 的字符集设置，也可不指定 PROCESSLIST_ID 条件，查看所有用户连接
SELECT T1.VARIABLE_NAME, T1.VARIABLE_VALUE, T2.PROCESSLIST_ID, concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST), T2.PROCESSLIST_DB, T2.PROCESSLIST_COMMAND FROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1, PERFORMANCE_SCHEMA.THREADS T2 WHERE 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)
例：发现用户 ID 为 254 的连接关闭了 sql_log_bin 设置
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_COMMAND FROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1, PERFORMANCE_SCHEMA.THREADS T2 WHERE 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 | root@10.211.55.2 | NULL | Sleep | | sql_log_bin | ON | 258 | root@10.211.55.2 | 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)
例：查看用户连接 ID 为 24 的网络流量变化
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_COMMAND FROM PERFORMANCE_SCHEMA.STATUS_BY_THREAD T1, PERFORMANCE_SCHEMA.THREADS T2 WHERE 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 | root@127.0.0.1 | NULL | Sleep | | Bytes_sent | 182 | 24 | root@127.0.0.1 | NULL | Sleep | +----------------+----------------+----------------+----------------+----------------+---------------------+ 2 rows in set (0.00 sec)
二、长事务
事务开启后，超过 5s 未提交的用户连接
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) >= 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)
三、元数据锁
MySQL 5.7 开启元数据锁追踪，以便追踪定位元数据锁相关的阻塞问题
// 临时开启，动态生效 UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl'; // 配置文件中添加，重启生效 performance-schema-instrument = wait/lock/metadata/sql/mdl=ON
场景 1：杀掉持有 MDL 锁的会话，使 DDL 语句顺利执行。
DDL 语句被阻塞通常因为存在获取资源后未及时提交释放的长事务。因此，查找 kill 掉事务运行时间大于 DDL 运行时间的会话即可使 DDL 语句顺利下发，SQL 语句如下：
/
// 查找事务运行时间 >= 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_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%') ; +----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+ | 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;
注：因 MySQL 元数据信息记录有限，此处可能误杀无辜长事务，且误杀无法完全避免。
当 kill 掉阻塞源后，可能存在 DDL 语句与被阻塞的 SQL 语句同时加锁的情况，此时会出现事务开始时间等于 DDL 开始时间连接，此类事务也需 kill。
//查找事务开始时间 = 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_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 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;
场景 2：kill 掉下发 DDL 语句的用户连接，取消 DDL 语句下发，保障业务不被阻塞。
// 查找DDL语句所在用户连接
// 查找DDL语句所在用户连接 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE 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;
四、锁等待
查看锁等待相关的阻塞线程、被阻塞线程信息及相关用户、IP、PORT
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_query FROM sys.x$innodb_lock_waits T1 LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID LEFT 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 | dks@127.0.0.1:56724 | X | 1 | 231 | root@127.0.0.1:50852 | S | 0 | 1 | insert into db.t1(id) values(2) | +--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+ 1 row in set, 3 warnings (0.00 sec)
若不关心阻塞相关的用户、IP、PORT，可直接查看 innodb_lock_waits 表信息。
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 207
sql_kill_blocking_connection: KILL 207
1 row in set, 3 warnings (0.00 sec)
五、全局读锁
PERFORMANCE_SCHEMA.METADATA_LOCKS 表 LOCK_DURATION 列为 EXPLICIT 状态表示 FTWRL 语句添加，OBJECT_TYPE 出现 COMMIT 状态表示已经加锁成功
场景 1：杀掉添加 FTWRL 的会话，恢复业务运行
SELECT processlist_id, mdl.OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS FROM performance_schema.metadata_locks mdl INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id AND 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;
场景 2：杀掉语句执行时间大于 FTWRL 执行时间的线程，确保 FTWRL 下发成功
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_TIME FROM INFORMATION_SCHEMA.processlist T1 LEFT JOIN PERFORMANCE_SCHEMA.THREADS T2 ON T1.ID=T2.PROCESSLIST_ID LEFT JOIN INFORMATION_SCHEMA.INNODB_TRX T3 ON T1.id=T3.trx_mysql_thread_id WHERE 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)
六、内存使用监控
默认只对 performance_schema 库进行内存统计，对全局内存统计需要手工开启
//动态开启，开启后开始统计 update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%'; //配置文件中添加，重启生效 performance-schema-instrument='memory/%=COUNTED'
SELECT event_name, current_alloc FROM sys.memory_global_by_current_bytes WHERE 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)
七、分区表
查看实例中的分区表相关信息
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 ; +--------------+------------------+-----------------+------------------+-------------+--------------+------------+ | 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)
查看某分区表具体信息，此处以库名为 db、表名为 e 的分区表为例
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_SIZE FROM INFORMATION_SCHEMA.PARTITIONS WHERE 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)
八、数据库信息概览
统计实例中各数据库大小
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 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 | 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)
统计某库下各表大小
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, engine FROM INFORMATION_SCHEMA.TABLES WHERE 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)
查看某库下表的基本信息
SELECT TABLE_SCHEMA, TABLE_NAME, table_collation, engine, table_rows FROM INFORMATION_SCHEMA.tables WHERE 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)
九、长时间未更新的表
UPDATE_TIME 为 NULL 表示实例启动后一直未更新过
SELECT TABLE_SCHEMA, TABLE_NAME, UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE 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)
十、主键、索引
无主键、唯一键及二级索引基表
SELECT T1.TABLE_SCHEMA, T1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE 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)='';
无主键、唯一键，仅有二级索引表
该类型表因无高效索引，因此从库回放时容易导致复制延迟
SELECT T1.TABLE_SCHEMA, T1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE 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';
仅有主键、唯一键表
该类型表结构因无二级索引，可能导致应用 SQL 语句上线后频繁全表扫描出现性能抖动
SELECT T1.TABLE_SCHEMA, T1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE 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';
无主键、唯一键表
SELECT T1.TABLE_SCHEMA, T1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE 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';
十一、存储引擎
存储引擎分布
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;
非 INNODB 存储引擎表
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION, ENGINE, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE 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 ;
十二、实时负载
while true
do
mysqladmin -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
#查看MDL持有锁的SQL
SELECT t1.* FROM information_schema.processlist t1 INNER JOIN performance_schema.threads t2 ON t1.id =t2.processlist_id INNER JOIN performance_schema.metadata_locks ml ON ml.owner_thread_id = t2.thread_id WHERE ml.lock_status='GRANTED'
螃蟹在剥我的壳，笔记本在写我，漫天的我落在枫叶上雪花上，而你在想我。
--章怀柔
