MySQL 优化篇（二）
分析查询语句：EXPLAIN
定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE 语句的使用方法与EXPLAIN语句是一样的，并且 分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Quy提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DB认为是最优的，这部分最耗费时间)
这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL 为我们提供了 EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，大家看懂 EXPLAIN 语句的各个输出项，可以有针对性的提升我们查询语句的性能。
基本语法
EXPLAIN 或 DESCRIBE 语句的语法形式如下：
EXPLAIN SELECT select_options;
或者
DESCRIBE SELECT select_options;
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN：
mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN 语句输出的各个列的作用如下：
| 列名 | 描述 |
|---|---|
| id | 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的idid 如果相同，可以认为是一组，从上往下顺序执行； 在所有组中，id 值越大，优先级越高，越先执行 |
| select_type | SELECT关键字对应的那个查询的类型 |
| table | 表名 |
| partitions | 匹配的分区信息，非分区表，该项为 NULL。一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL。 |
| type | 支队单表的访问方法 |
| possible_type | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际使用到的索引长度 |
| ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数，值越小越好 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外的信息 |
重要字段解析
select_type
一条大的查询语句里边可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT：关键字的 FROM 子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。
MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下 select_type 都能取哪些值，请看官方文档：
| 名称 | 描述 |
|---|---|
| SIMPLE | Simple SELECT(not using UNION or subqueries） 简单 SELECT（不使用 UNION 或子查询） |
| PRIMAPY | Outermost SELECT 最外层选择 |
| UNION | Second or later SELECT statement in a UNION UNION 中的第二个或以后的 SELECT 语句 |
| UNION RESULT | Result of a UNION UNION 的结果 |
| SUBQUERY | First SELECT in subquery 子查询中的第一个 SELECT |
| DEPENDENT SUBQUERY | First SELECT in subquery,dependent on outer query 子查询中的第一个 SELECT，依赖于外部查询 |
| DERIVED | Derived table 派生表 |
| MATERIALIZED | Materialized subquery 物化子查询 |
| UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query 一个子查询，其结果无法缓存，必须为外部查询的每一行重新计算 |
| UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) 属于不可缓存子查询的 UNION 中的第二个或更晚选择（请参阅 UNCACHEABLE SUBQUERY） |
type
执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，又称“访问类型"，其中的 type 列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到 type 列的值是ref，表明 MySQL 即将使用 ref 访问方法来执行对 s1 表的查询。
完整的访问方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge， unique_subquery，index_subquery，range，index，ALL；
-
system
当表中
只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM，Memory。那么对该表的访问方法就是system。 -
const
当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是
cost;示例是根据 employees 表中的主键进行查询：
-
eq_ref
在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是
eq_ref。
-
ref
当通过普通的二级索引列与常量进行等值匹配来查询某个表时，那么对该表的访问方法就可能是
ref
-
ref_or_null
当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是
ref_or_null； -
index_merge
单表访问方法时在某些场景下可以使用
Intersection、Union、sort-Union这三种索引合并的方式米执行查询
-
unique_subquery
unique_subquery 是针对在一些包含
IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。 -
range
如果使用索引获取某些~范围区间`的记录，那么就可能使用到`range`访问方法
-
index
当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是
index -
ALL
全表扫描
possible_key 与 key
可供选择的索引与实际用到的索引
在 EXPLAIN 语句输出的执行计划中，possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key 列表示实际用到的索引有哪些，如果为 NULL 则没有使用索引。
key_len
实际使用到的索引长度（即：字节数），检查是否充分的利用上索引。对于联合索引来说：值越大越好。说明充分利用到了索引。
计算规则示例：
- varchar(10)：变长字段且允许 NULL = 10 * (character set:utf8=3,gbk=2,latinl=1) + 1 (NULL) + 2 (变长字段)
- varchar(10)：变长字段且不允许 NULL = 10 * (character set:utf8=3,gbk=2,latinl=1 ) + 2 ( 变长字段 )
- char(10)：固定字段且允许NULL = 10 (character set:utf8=3,gbk=2,latinl=1) + 1(NULL)
- char(10)：固定字段且不允许NULL = 10 (character set:utf8=3,gbk=2,latinl=1)
filtered
某个表经过搜索条件过滤后剩余记录条数的百分比。
如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数（即：rows * filtered)
Extra
Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，只挑比较重要的额外信息介绍。
-
No tables used
当查询语句没有 FROM 子句时会提示该额外信息，比如：
-
Impossible WHERE
查询语句的
WHERE子句水远为FALSE时将会提示该额外信息：
-
Using where
当我们使用全表扫描来执行对某个表的查询，并且该语句的
WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。当使用索引访问米执行对某个表的查询，并且该语句的
WHERE子句中有除了该索引包含的列之外的其他搜索条件时，在Extra列中也会提示上述额外信息。
-
No matching min/max row
当查询列表处有
MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息。
-
Using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可使用覆盖索引的情况下，在
Extra列将会提示该额外信息。比如说下边这个查询中只需要用到索引而不需要回表操作：
-
Using index condition
搜索条件里面出现了索引列，但是却不能使用索引：
-
join buffer
在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫
join buffer内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法:
-
Not exists
当我们使用左（外）连接时，如果
WHEE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息：
-
Using filesort
很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：Using filesort)
-
Using temporary
在许多查询的执行过程中，
MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示
执行计划中出现
Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表。
小节
- EXPLAIN 不考虑各种 Cache；
- EXPLAIN 不能显示 Mysql 在执行查询时所做的优化；
- EXPLAIN 不会告诉你关于触发器存储过程的信息或用户自定义函数对查询的影响情况；
- 部分统计信息是估算的，并非精确值。
EXPLAIN 的进一步使用
EXPLAIN 四种输出格式
这里谈谈 EXPLAIN 的输出格式。EXPLAIN 可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。
传统格式
传统格式简单明了，输出是一个表格形式，概要说明查询计划。
例子就是上面所展示的。
JSON 格式
第1种格式中介绍的 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性一一成本。而 JSON 格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。
-
JSON 格式：在 EXPLAIN 单词和真正的查询语句中间加上
FORMAT=JSONEXPLAIN FORMAT=JSON SELECT ...... -
EXPLAIN的Column与JSON的对应关系：
Table 8.1 EXPLAIN Output Columns
Column JSON Name Meaning idselect_idThe identifier SELECTselect_typeNone The type SELECTtabletable_nameThe table for the output row partitionspartitionsThe matching partitions typeaccess_typeThe join type possible_keyspossible_keysThe possible indexes to choose keykeyThe index actually chosen key_lenkey_lengthThe length of the chosen key refrefThe columns compared to the index rowsrowsEstimate of rows to be examined filteredfilteredPercentage of rows filtered by table condition ExtraNone Additional information
json 格式的执行计划示例：
mysql> explain FORMAT=JSON select * from employees t1 left join salaries t2 on t1.emp_no = t2.emp_no \G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "392293.77" # 这个sql的成本
},
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL", # 访问类型是ALL，表示表扫描
"rows_examined_per_scan": 299689, # 访问的表一共299689行
"rows_produced_per_join": 299689,
"filtered": "100.00",
"cost_info": { # 这里详细说明成本消耗的细节
"read_cost": "232.25", # 读成本232.25
"eval_cost": "29968.90", # 评估成本29968.90，优化器消耗掉的成本，如果这里消耗太高可以用Hint提示
"prefix_cost": "30201.15", # 这个字段在这个例子其实没用，它用在join语句后面子句记录前面子句的成本
"data_read_per_join": "38M" # 每个join读取的数据量 如果没有 join 的话就是总评估需要读取的数据量
},
"used_columns": [
"emp_no",
"birth_date",
"first_name",
"last_name",
"gender",
"hire_date"
]
}
},
{
"table": {
"table_name": "t2",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"emp_no"
],
"key_length": "4",
"ref": [
"employees.t1.emp_no"
],
"rows_examined_per_scan": 9,
"rows_produced_per_join": 2864992,
"filtered": "100.00",
"cost_info": {
"read_cost": "75593.35",
"eval_cost": "286499.27",
"prefix_cost": "392293.77",
"data_read_per_join": "43M"
},
"used_columns": [
"emp_no",
"salary",
"from_date",
"to_date"
]
}
}
]
}
}
1 row in set, 1 warning (0.00 sec)
成本字段简介
-
read_cost 是由下边两部分组成的：
IO 成本- 检测
rows * （1 - filter）条纪录的CPU成本
-
eval_cost计算规则：- 检测
rows * filter条纪录的成本。
- 检测
TREE 格式
TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。
mysql> explain FORMAT=TREE select * from employees t1 left join salaries t2 on t1.emp_no = t2.emp_no \G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop left join (cost=392293.77 rows=2864993)
-> Table scan on t1 (cost=30201.15 rows=299689)
-> Index lookup on t2 using PRIMARY (emp_no=t1.emp_no) (cost=0.25 rows=10)
1 row in set (0.01 sec)
OPTIMIZER_TRACE
EXPLAIN仅显示查询的预期执行计划，而没有显示为什么没有选择其他可选的执行策略。理解为什么没有选择可选方案可能会令人困惑：
- 是因为其他方案不合适吗(例如，一些优化只能应用于特定的用例)?
- 是因为人们认为其他方案成本更高吗?
- 如果另一种选择是更高的成本，是多少?
OPTIMIZER_TRACE为这些问题提供了答案。它的设计目的是提供更多关于优化器的诊断数据，但它对于实际故障排除以及了解优化器成本模型的工作方式非常有用。
典型用法
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";
示例：EXPLAIN显示未使用新添加的索引
mysql> EXPLAIN FORMAT=JSON select * from dept_emp where from_date = '1995-12-03' and dept_no like 'd%' \G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "33298.55"
},
"table": {
"table_name": "dept_emp",
"access_type": "ALL", # 虽然经过优化器的处理，但此查询还是将进行表扫描
"possible_keys": [
"dept_no" # 识别到可用的索引
],
"rows_examined_per_scan": 331143,
"rows_produced_per_join": 16557,
"filtered": "5.00",
"cost_info": {
"read_cost": "31642.84",
"eval_cost": "1655.71", # 这里评估成本有所增加，是因为评估了索引
"prefix_cost": "33298.55",
"data_read_per_join": "517K"
},
"used_columns": [
"emp_no",
"dept_no",
"from_date",
"to_date"
],
"attached_condition": "((`employees`.`dept_emp`.`from_date` = DATE'1995-12-03') and (`employees`.`dept_emp`.`dept_no` like 'd%'))"
}
}
}
1 row in set, 1 warning (0.00 sec)
在示例2中，我们可以看到索引dept_no 在被添加到表后并没有被选中。EXPLAIN显示它是一个候选，但它没有描述为什么它没有被选中。要找到原因，我们需要求助于OPTIMIZER_TRACE。
mysql> SET optimizer_trace="enabled=on";
mysql> EXPLAIN FORMAT=JSON select * from dept_emp where from_date = '1995-12-03' and dept_no like 'd%' \G
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: EXPLAIN FORMAT=JSON select * from dept_emp where from_date = '1995-12-03' and dept_no like 'd%'
TRACE: {
"steps": [
{
"join_preparation": { # "*"转换成了全部列名，明确所有列名前都加了表名
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `dept_emp`.`emp_no` AS `emp_no`,`dept_emp`.`dept_no` AS `dept_no`,`dept_emp`.`from_date` AS `from_date`,`dept_emp`.`to_date` AS `to_date`from `dept_emp` where ((`dept_emp`.`from_date` = '1995-12-03') and (`dept_emp`.`dept_no` like 'd%'))"
}
]
}
},
{
"join_optimization": { # where语句处理，调整查询条件顺序，计算数学公式改为常量，移除多余的查询条件
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`dept_emp`.`from_date` = '1995-12-03') and (`dept_emp`.`dept_no` like 'd%'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`dept_emp`.`dept_no` like 'd%') and multiple equal('1995-12-03', `dept_emp`.`from_date`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`dept_emp`.`dept_no` like 'd%') and multiple equal('1995-12-03', `dept_emp`.`from_date`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`dept_emp`.`dept_no` like 'd%') and multiple equal(DATE'1995-12-03', `dept_emp`.`from_date`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`dept_emp`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`dept_emp`",
"range_analysis": { # 这里说明表区域扫描审计
"table_scan": { # 扫 331143 行，成本 33300.7
"rows": 331143,
"cost": 33300.7
},
"potential_range_indexes": [ # 可以用的索引
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable" # 主键不适用
},
{
"index": "dept_no",
"usable": true, # 普通索引 dept_no 可以使用
"key_parts": [
"dept_no",
"emp_no"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "dept_no",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [ # 表区域扫描可选方案
{
"index": "dept_no",
"ranges": [
"d <= dept_no <= d???" # 使用索引 dept_no 扫描
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 165571,
"cost": 57950.1, # 此方案成本 57950.1
"chosen": false, # 但是没有选择此方案
"cause": "cost" # 因为它消耗的成本太多
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [ # 最后选择的方案
{
"plan_prefix": [
],
"table": "`dept_emp`",
"best_access_path": {
"considered_access_paths": [ # 直接扫表
{
"rows_to_scan": 331143,
"filtering_effect": [
],
"final_filtering_effect": 0.0499998,
"access_type": "scan",
"resulting_rows": 16557.1,
"cost": 33298.6, # 扫表成本 33298.6，成本比可选的用索引p做区域扫描的 57950.1 少很多
"chosen": true # 选择了这个方案
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 16557.1,
"cost_for_plan": 33298.6,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`dept_emp`.`from_date` = DATE'1995-12-03') and (`dept_emp`.`dept_no` like 'd%'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`dept_emp`",
"attached": "((`dept_emp`.`from_date` = DATE'1995-12-03') and (`dept_emp`.`dept_no` like 'd%'))"
}
]
}
},
{
"finalizing_table_conditions": [ # 查询后进行条件过滤
{
"table": "`dept_emp`",
"original_table_condition": "((`dept_emp`.`from_date` = DATE'1995-12-03') and (`dept_emp`.`dept_no` like 'd%'))",
"final_table_condition ": "((`dept_emp`.`from_date` = DATE'1995-12-03') and (`dept_emp`.`dept_no` like 'd%'))"
}
]
},
{
"refine_plan": [
{
"table": "`dept_emp`"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
//第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
//第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一股在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
小节
要说明为什么会这样，我们首先需要了解索引的排除工作原理。在这个数据集中，dept_emp 表中大多数行的dept_no前缀都不是d，优化器判断直接行扫描比用索引搜索行更快。因为不是InnoDB的覆盖索引和主键，所以使用这种普通索引的原理是查询到键，然后回表取数据，然后再回到索引查找下一个键，再回表取数据，如此反复，所以使用索引反而导致成本更高。当然高级用户可以配置如何做出这些决策的成本。
SHOW WARNINGS
在我们使用EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用SHOW WARNINGS语句查看与这个查询的执行计划有关的一些扩展信息，比如这样：
mysql> explain select * from employees t1 where t1.emp_no in (select emp_no from dept_emp where to_date like '9999%') \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: <subquery2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: <subquery2>.emp_no
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: dept_emp
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 331143
filtered: 11.11
Extra: Using where
3 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`t1`.`emp_no` AS `emp_no`,`employees`.`t1`.`birth_date` AS `birth_date`,`employees`.`t1`.`first_name` AS `first_name`,`employees`.`t1`.`last_name` AS `last_name`,`employees`.`t1`.`gender` AS `gender`,`employees`.`t1`.`hire_date` AS `hire_date` from `employees`.`employees` `t1` semi join (`employees`.`dept_emp`) where ((`employees`.`t1`.`emp_no` = `<subquery2>`.`emp_no`) and (`employees`.`dept_emp`.`to_date` like '9999%'))
1 row in set (0.00 sec)
可以看到 SHOW WARNINGS展示出来的信息有三个字段，分别是Level、Code、Message。我们最常见的就是 code 为 1003 的信息，当 code 值为 1003 时，Message 字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。比如我们上边的查询本来是一个子查询语句，但是查询优化器直接给我们优化成了 semi join 半连接查询。
sys schema
MySQL 监控分析视图
关于MySQL的性能监控和问题诊断，我们一般都从 performance_schema 中去获取想要的数据，在 MySQL5.7.7 版本中新增 sys schema，它将 performance_schema 和 information_schema 中的数据以更容易理解的方式总结归纳为"视图”，其目的就是为了降低查询 performance_schema的复杂度，让DBA能够快速的定位问题。下面看看这些库中都有哪些监控表和视图，掌握了这些，在我们开发和运维的过程中就起到了事半功倍的效果。
sys schema 视图摘要
- 主机相关：以host_summary开头，主要汇总了 IO 延迟的信息；
- Innodb 相关：以 Innodb 开头，汇总了 innodb buffer 信息和事务等待 innodb 锁的信息；
- IO 相关：以io开头，汇总了等待I/O、I/0 使用量情况；
- 内存使用情况：以 memory 开头，从主机、线程、事件等角度展示内存的使用情况；
- 连接与会话信息：processlist 和 session 相关视图，总结了会话相关信息；
- 表相关：以 schema_table 开头的视图，展示了表的统i计信息；
- 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况；
- 语句相关：以 statement 开头，包含执行全表扫描、使用l临时表、排序等的语句信息；
- 用户相关：以 user 开头的视图，统计了用户使用的文件 I/O、执行语句统计信息；
- 等待事件相关信息：以 wait 开头，展示等待事件的延迟情况。
sys schema 视图使用场景
索引情况
#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;
表相关
# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';
语句相关
#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;
IO相关
#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;
innodb 相关
#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;
浙公网安备 33010602011771号