检查SQL语句
1. 启用插桩
要启用语句检测,需要启用statement类型的插桩(默认已经启用)
UPDATE setup_instruments
SET enabled = 'YES'
WHERE name LIKE 'statement/%'
AND enabled = 'NO';
相关的插桩如下表:
statement/sql
SQL语句,如 SELECT、CREATE TABLE等
statement/sp
存储过程控制
statement/scheduler
事件调度器
statement/com
命令,如 quit、kill、DROP DATABASE、Binlog Dump等 有些命令是用户不可用的,只能由 mysqld 进程调用
statement/abstract
包括 4 类命令:clone、Query、new_packet 和 relay_log
2. 启用消费者表
Performance Schema将语句指标存储在 events_statements_current、events_statements_history 和 events_statements_history_long 表中。因此,接着需要启用(enable)这三张消费者表:
UPDATE setup_consumers
SET enabled = 'YES'
WHERE name LIKE 'events_statements_%'
AND enabled = 'NO';
3. 查询消费者表,找出需要优化的SQL语句
在 events_statements_history 表中可以作为优化指标的列如下:
CREATED_TMP_DISK_TABLES
查询创建的临时磁盘表的数量。
高
CREATED_TMP_TABLES
查询创建的临时表的数量。
中
SELECT_FULL_JOIN
因为没有合适的索引,所以导致JOIN执行了全表扫描。
高
SELECT_FULL_RANGE_JOIN
JOIN操作是否使用了被引用表的范围搜索。
中
SELECT_RANGE
JOIN操作是否使用了范围搜索来解析第一个表中的行。
低
SELECT_RANGE_CHECK
如果JOIN操作没有索引,则会检查每一行之后的键。 这是一种非常糟糕的症状,如果该值大于零,则需要重新考虑表中的索引设计。
高
SELECT_SCAN
JOIN操作是否对第一个表执行了全表扫描。 如果第一个表很大则会使一个问题。
中
SORT_MERGE_PASSES
排序必须执行的合并过程数。 如果该值大于零且查询性能较低,则可能需要增加 sort_buffer_size 的值。
低
SORT_RANGE
是否使用的是范围排序。
低
SORT_ROWS
排序的行数。 如果排序的行数比返回的行数多,则可能需要优化查询。
中
SORT_SCAN
排序是否是通过扫描表完成的。 这是一个非常糟糕的迹象,除非有意选择表中的所有行而不使用索引。
高
NO_INDEX_USED
查询没有使用索引。
高
NO_GOOD_INDEX_USED
查询所用的索引不是最合适的。 如果该值大于零,则需要重新考虑索引的设计。
高
要找出哪些语句需要优化,可以选择上述列中的任何一列,并将其与 0 进行比较。
找到所有没有使用合适索引的查询
SELECT
thread_id,
sql_text,
rows_sent,
rows_examined,
created_tmp_disk_tables,
created_tmp_tables,
no_index_used,
no_good_index_used
FROM events_statements_history
WHERE no_index_used > 0
OR no_good_index_used > 0;
找出所有创建了临时表的查询
SELECT
thread_id,
sql_text,
rows_sent,
rows_examined,
created_tmp_disk_tables,
created_tmp_tables,
no_index_used,
no_good_index_used
FROM events_statements_history
WHERE created_tmp_disk_tables > 0
OR created_tmp_tables > 0;
找出所有返回错误的查询
SELECT
thread_id,
sql_text,
mysql_errno,
returned_sqlstate,
message_text,
errors
FROM events_statements_history
WHERE errors > 0;
找出所有执行时间超过 5 秒的查询
SELECT
*
FROM events_statements_history
WHERE timer_wait > 5000000000;
4. 使用sys Schema
sys schema提供了可用于查找有问题语句的视图,如下表所示:
statement_analysis
具有聚合统计信息的规范化语句视图,按每个规范化语句的总执行时间进行排序。
类似于 events_statements_summary_by_digest表,但没有那么详细。
statements_with_errors_or_warnings
所有引起错误或警告的规范化语句。
statements_with_full_table_scans
所有执行了全表扫描的规范化语句。
statements_with_runtimes_in_95th_percentile
所有平均执行时间在前 95% 的规范化语句。
statements_with_sorting
所有执行了排序的规范化语句。
statements_with_temp_tables
所有使用了临时表的规范化语句。
sys schema 使用摘要文本而不是查询文本,因此你将获得查询文本的摘要,而不是像直接访问Performance Schema那样获得的要么是SQL语句要么是摘要文本:
SELECT
query
FROM sys.statements_with_temp_tables
WHERE db = 'sakila'
AND query LIKE '% ORDER BY %';
+-----------------------------------------------------------------+
|query |
+-----------------------------------------------------------------+
|EXPLAIN SELECT `payment_date` ... BY `payment_date` ORDER BY ? |
+-----------------------------------------------------------------+
最后更新于