一、 利用等待事件找出sql慢在哪里
使用sys.session视图结合performance_schema等待事件。
首先启用等待事件相关instruments和consumers
call sys.ps_setup_enable_instrument('wait');
call sys.ps_setup_enable_consumer('wait');
然后利用sys.session视图进行查询,关注last_wait、last_wait_latency字段
select * from session where command='query' and conn_id!=connection_id()\G;
二、 查看是否有事务锁等待
innodb_lock_wait视图通常由于分析事务锁(行锁)等待,注意必须要有事务正在等待该视图才会有值。这个视图其实在前面介绍performance_schema应用时就用到过。
MySQL 5.7版本中也可以使用sys.innodb_lock_waits视图查询,但是在8.0中,该视图联结查询的表不同(把之前版本中使用的information_schema.innodb_locks和information_schema.innodb_lock_waits表替换为了performance_schema.data_locks和performance_schema.data_lock_waits表)。
select * from sys.innodb_lock_waits\G;
*************************** 1. row ***************************
wait_started: 2018-01-14 21:51:59
wait_age: 00:00:11 <-- 等待时间
wait_age_secs: 11
locked_table: `xiaoboluo`.`t_luoxiaobo` <-- 表信息
locked_table_schema: xiaoboluo
locked_table_name: t_luoxiaobo
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY <-- 主键字段
locked_type: RECORD <-- 行锁
waiting_trx_id: 55566
waiting_trx_started: 2018-01-14 21:51:59
waiting_trx_age: 00:00:11
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 8 <-- 被阻塞的线程id
waiting_query: update t_luoxiaobo set datet_time=now() where id=2 <-- 被阻塞者正执行的sql语句
waiting_lock_id: 55566:2:4:2
waiting_lock_mode: X <-- 锁模式
blocking_trx_id: 55562
blocking_pid: 7 <-- 阻塞的线程id
blocking_query: NULL <-- 阻塞者正执行的sql语句(已经执行完了,但没提交)
blocking_lock_id: 55562:2:4:2
blocking_lock_mode: X
blocking_trx_started: 2018-01-14 21:34:44
blocking_trx_age: 00:17:26 <-- 持锁时间
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 7
sql_kill_blocking_connection: KILL 7 <-- kill阻塞者的语句
1 row in set (0.02 sec)
5.6及其之前的版本中默认没有sys库,可以使用如下语句代替:
SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
rl.lock_table AS locked_table,
rl.lock_index AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
rl.lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
bl.lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
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
INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;
三、 查看是否有MDL锁等待
通过schema_table_lock_wait视图(5.7.9新增)可以查看当前线程的MDL等待信息,显示哪些会话被阻塞与阻塞源。数据来源为performance_schema下的threads、metadata_locks、events_statements_current表,可以查看视图定义获取相应sql。
首先也要启用MDL等待相关instruments
call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
schema_table_lock_wait视图字段如下,含义比较明显,详细可参考 https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html
desc schema_table_lock_waits;
+------------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------+-------+
| object_schema | varchar(64) | YES | | NULL | |
| object_name | varchar(64) | YES | | NULL | |
| waiting_thread_id | bigint(20) unsigned | NO | | NULL | |
| waiting_pid | bigint(20) unsigned | YES | | NULL | |
| waiting_account | text | YES | | NULL | |
| waiting_lock_type | varchar(32) | NO | | NULL | |
| waiting_lock_duration | varchar(32) | NO | | NULL | |
| waiting_query | longtext | YES | | NULL | |
| waiting_query_secs | bigint(20) | YES | | NULL | |
| waiting_query_rows_affected | bigint(20) unsigned | YES | | NULL | |
| waiting_query_rows_examined | bigint(20) unsigned | YES | | NULL | |
| blocking_thread_id | bigint(20) unsigned | NO | | NULL | |
| blocking_pid | bigint(20) unsigned | YES | | NULL | |
| blocking_account | text | YES | | NULL | |
| blocking_lock_type | varchar(32) | NO | | NULL | |
| blocking_lock_duration | varchar(32) | NO | | NULL | |
| sql_kill_blocking_query | varchar(31) | YES | | NULL | |
| sql_kill_blocking_connection | varchar(25) | YES | | NULL | |
+------------------------------+---------------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
四、 查看InnoDB缓冲池中热点库
使用innodb_buffer_stats_by_schema视图可按照schema分组查询InnoDB缓冲池的统计信息
select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | 23.73 MiB | 21.76 MiB | 1519 | 0 | 24 | 21474 |
| mysql | 240.00 KiB | 14.57 KiB | 15 | 0 | 15 | 179 |
| xiaoboluo | 128.00 KiB | 38.93 KiB | 8 | 0 | 5 | 982 |
| sys | 16.00 KiB | 354 bytes | 1 | 0 | 1 | 6 |
| 小萝卜 | 16.00 KiB | 135 bytes | 1 | 0 | 1 | 3 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
视图字段含义如下:
- object_schema:对象所在schema,如果该表属于Innodb存储引擎,则该字段显示为InnoDB System,如果是其他引擎,则该字段显示为每个schema name(db名)。
- allocated:当前已分配给schema的总内存字节数
- data:当前已分配给schema的数据部分使用的内存字节总数
- pages:当前已分配给schema内存总页数
- pages_hashed:当前已分配给schema的自适应hash索引页总数
- pages_old:当前已分配给schema的旧页总数(位于LRU列表中的旧块子列表中的页数)
- rows_cached:buffer pool中为schema缓冲的总数据行数
五、 查看冗余索引
使用MySQL 5.7.9新增的sys.schema_redundant_indexes视图,其数据来源为sys.x$schema_flattened_keys。
字段含义参考 https://www.docs4dev.com/docs/zh/mysql/5.7/reference/sys-schema-redundant-indexes.html
select * from schema_redundant_indexes;
六、 查看未使用的索引
schema_unused_indexes视图可以查看未用过的索引,其数据来源为performance_schema.table_io_waits_summary_by_index_usage。该视图在数据库运行足够长时间后数据才有参考意义,删除索引前一定要跟业务方确认。
字段含义参考 https://www.docs4dev.com/docs/zh/mysql/5.7/reference/sys-schema-unused-indexes.html
select * from schema_unused_indexes;
七、 查看表IO及耗时统计信息
schema_table_statistics_with_buffer 视图可以查看表的增、删、改、查数据量,IO耗时,以及在InnoDB缓冲池中占用情况等统计信息。
select * from schema_table_statistics_with_buffer;
字段含义参考 https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics-with-buffer.html
八、查看磁盘文件产生的磁盘流量与读写比例
io_global_by_file_by_bytes 视图可以按照文件路径+名称分组(磁盘文件名)查看全局IO字节数、读写文件IO事件数量统计信息,默认按总IO读写字节数进行降序排列。
select * from io_global_by_file_by_bytes;
字段名 | 意义 |
File | 被操作的文件名 |
Count_read | 总共有多少次读 |
Total_read | 总共读了多少字节 |
Avg_read | 平均每次读多少字节 |
Count_write | 总共多少次写 |
Total_written | 总共写了多少字节 |
Avg_write | 平均每次写的字节 |
Total | 读和写总共的IO |
Write_pct | 写占IO里的占比 |
字段含义参考 https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-bytes.html
九、 查看哪些语句使用了全表扫描
statements_with_full_table_scans可查看全表扫描或者没有使用到最优索引的语句(经过标准化转化的语句文本),默认按照平均扫描次数百分比和语句总延迟时间(执行时间)降序排序。数据来源:performance_schema.events_statements_summary_by_digest
select * from statements_with_full_table_scans limit 1\G;
*************************** 1. row ***************************
query: SELECT `performance_schema` . ... ance` . `SUM_TIMER_WAIT` DESC
db: sys
exec_count: 1
total_latency: 938.45 us
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 3
rows_examined: 318
rows_sent_avg: 3
rows_examined_avg: 318
first_seen: 2017-09-07 09:34:12
last_seen: 2017-09-07 09:34:12
digest: 5b5b4e15a8703769d9b9e23e9e92d499
1 row in set (0.01 sec)
视图字段含义如下:
- query:经过标准化转换的语句字符串
- db:语句对应的默认数据库,如果没有默认数据库,该字段为NULL
- exec_count:语句执行的总次数
- total_latency:语句执行的总延迟时间(执行时间)
- no_index_used_count:语句执行没有使用索引扫描表(而是使用全表扫描)的总次数
- no_good_index_used_count:语句执行没有使用到更好的索引扫描表的总次数
- no_index_used_pct:语句执行没有使用索引扫描表(而是使用全表扫描)的次数与语句执行总次数的百分比
- rows_sent:语句执行从表返回给客户端的总数据行数
- rows_examined:语句执行从存储引擎检查的总数据行数
- rows_sent_avg:每个语句执行从表中返回客户端的平均数据行数
- rows_examined_avg:每个语句执行从存储引擎读取的平均数据行数
- first_seen:该语句第一次出现的时间
- last_seen:该语句最近一次出现的时间
- digest:语句摘要计算的md5 hash值
字段含义参考 https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-full-table-scans.html
十、 查看哪些语句使用了文件排序
statements_with_sorting视图可查看执行了文件排序的语句,默认情况下按照语句总延迟时间(执行时间)降序排序,数据来源:performance_schema.events_statements_summary_by_digest
select * from statements_with_sorting limit 1\G;
*************************** 1. row ***************************
query: SELECT IF ( ( `locate` ( ? , ` ... . `COMPRESSED_SIZE` ) ) DESC
db: sys
exec_count: 4
total_latency: 46.53 s
sort_merge_passes: 48
avg_sort_merges: 12
sorts_using_scans: 16
sort_using_range: 0
rows_sorted: 415391
avg_rows_sorted: 103848
first_seen: 2017-09-07 12:36:58
last_seen: 2017-09-07 12:38:37
digest: 59abe341d11b5307fbd8419b0b9a7bc3
1 row in set (0.00 sec)
视图字段含义如下:
- query:经过标准化转换的语句字符串
- db:语句对应的默认数据库,如果没有默认数据库,该字段为NULL
- exec_count:语句执行的总次数
- total_latency:语句执行的总延迟时间(执行时间)
- sort_merge_passes:语句执行发生的语句排序合并的总次数
- avg_sort_merges:语句的平均排序合并次数 SUM_SORT_MERGE_PASSES/COUNT_STAR
- sorts_using_scans:语句排序执行全表扫描的总次数
- sort_using_range:语句排序执行范围扫描的总次数
- rows_sorted:语句执行发生排序的总数据行数
- avg_rows_sorted:语句的平均排序数据行数 SUM_SORT_ROWS/COUNT_STAR
- first_seen:该语句第一次出现的时间
- last_seen:该语句最近一次出现的时间
- digest:语句摘要计算的md5 hash值
字段含义参考 https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-sorting.html
十一、 查看哪些语句使用了临时表
tatements_with_temp_tables 查看使用了临时表的语句,默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序。数据来源:performance_schema.events_statements_summary_by_digest
select * from statements_with_temp_tables limit 1\G;
*************************** 1. row ***************************
query: SELECT `performance_schema` . ... name` . `SUM_TIMER_WAIT` DESC
db: sys
exec_count: 2
total_latency: 1.53 s
memory_tmp_tables: 458
disk_tmp_tables: 38
avg_tmp_tables_per_query: 229
tmp_tables_to_disk_pct: 8
first_seen: 2017-09-07 11:18:31
last_seen: 2017-09-07 11:19:43
digest: 6f58edd9cee71845f592cf5347f8ecd7
1 row in set (0.00 sec)
视图字段含义如下:
- query:经过标准化转换的语句字符串
- db:语句对应的默认数据库,如果没有默认数据库,该字段为NULL
- exec_count:语句执行的总次数
- total_latency:语句执行的总延迟时间(执行时间)
- memory_tmp_tables:语句执行时创建内部内存临时表的总数量
- disk_tmp_tables:语句执行时创建的内部磁盘临时表的总数量
- avg_tmp_tables_per_query:语句使用内存临时表的平均数量 SUM_CREATED_TMP_TABLES/COUNT_STAR
- tmp_tables_to_disk_pct:内存临时表的总数量与磁盘临时表的总数量百分比,表示磁盘临时表的转换率 SUM_CREATED_TMP_DISK_TABLES/SUM_CREATED_TMP_TABLES
- first_seen:该语句第一次出现的时间
- last_seen:该语句最近一次出现的时间
- digest:语句摘要计算的md5 hash值
字段含义参考 https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-temp-tables.html