检查SQL语句

最后更新于1年前

检查SQL语句

1. 启用插桩

要启用语句检测，需要启用statement类型的插桩（默认已经启用）

UPDATE setup_instruments
SET enabled = 'YES'
WHERE name LIKE 'statement/%'
  AND enabled = 'NO';

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';

在5.7版本中， events_statements_current、events_statements_history 默认处于enabled状态，events_statements_history_long 则处于disabled状态。

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 ? |
+-----------------------------------------------------------------+