• 数据库调优中，目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

数据库服务器的优化步骤

  • 整个流程划分为观察（Show status）和行动（Action）两部分。观察就需要使用相应的分析工具，行动就是对应分析可以采取的行动。

    性能分析工具 - 图1

SQL调优的3个步骤：慢查询、EXPLAIN、SHOW PROFILING

数据库调优的效果和成本

性能分析工具 - 图2

查看系统性能参数

  • 在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。

  • 查询语句如下：SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

  • 参数：
    • Connections：连接MySQL服务器的次数。
    • Uptime：MySQL服务器的上线时间。
    • Slow_queries：慢查询的次数。
    • Innodb_rows_read：Select查询返回的行数
    • Innodb_rows_inserted：执行INSERT操作插入的行数
    • Innodb_rows_updated：执行UPDATE操作更新的行数
    • Innodb_rows_deleted：执行DELETE操作删除的行数
    • Com_select：查询操作的次数。
    • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
    • Com_update：更新操作的次数。
    • Com_delete：删除操作的次数。

统计SQL查询成本

  • 一条SQL在交给存储引擎执行之前，需要生成执行计划，如果有多条执行计划，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

  • 如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值得到当前查询的成本。它是io_cost和cpu_cost的开销总和

  • 通常作为评价一个查询的执行效率的指标。可以对应到SQL语句需要读取的页的数量。

    如下两条SQL语句的执行，对应的查询成本分别为20.29和40.32

既然是io_cost和cpu_cost，成本应该大多在io上。肯定和读取页的数量正相关。

性能分析工具 - 图3

  • 如果页在数据库缓冲池中，读取的效率比从磁盘读取更高。成本也就更低

  • 如果页的读取是顺序读取而非随机读取，那么效率也会提升。成本也就更低

慢查询日志

  • 用于记录响应时间超过阈值的SQL语句，也即具体运行时间超过long_query_time的SQL，会被记录到慢查询日志中。
  • 默认值是10s，不包含10s
  • 主要作用是帮助我们发现执行时间特别长的SQL查询，并且针对性地进行优化，从而提升系统的整体效率。
  • 默认情况下，此功能是关闭状态。需要手动开启，只有在调优的时候需要开启，一般运行时候不开启，多少对性能来说会有影响。
  • 慢查询日志支持将日志记录写入文件。

开启慢查询日志

  • 查看慢查询日志相关：show variables like '%slow_query_log%';

    性能分析工具 - 图4

  • 开启慢查询日志：set global slow_query_log='ON';

  • 查询运行时间阈值：show variables like 'long_query_time%';

    默认10s

性能分析工具 - 图5

  • 设置long_query_time：set global long_query_time = 1;，设置时间为1s

  • 查询当前系统中的慢查询记录：SHOW GLOBAL STATUS LIKE '%Slow_queries%';

慢查询演示

  • 数据准备 ```mysql

    创建student表

    CREATE TABLE student ( id INT(11) NOT NULL AUTO_INCREMENT, stuno INT NOT NULL, name VARCHAR(20) DEFAULT NULL, age INT(3) DEFAULT NULL, classId INT(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

如果报错log_bin_trust_function_creators

set global log_bin_trust_function_creators = 1;

产生随机字符串

DELIMITER // CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) #该函数会返回一个字符串 BEGIN DECLARE chars_str VARCHAR(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ’; DECLARE return_str VARCHAR(255) DEFAULT ‘’; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1)); SET i = i + 1; END WHILE; RETURN return_str; END // DELIMITER ;

DELIMITER // CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1)); RETURN i; END // DELIMITER ;

添加数据的存储过程

DELIMITER // CREATE PROCEDURE insert_stu1(START INT, max_num INT) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; #设置手动提交事务 REPEAT

  1.     #循环
  2.     SET i = i + 1; #赋值
  3.     INSERT INTO student (stuno, NAME, age, classId)
  4.     VALUES ((START + i), rand_string(6), rand_num(10, 100), rand_num(10, 1000));
  5. UNTIL i = max_num END REPEAT;
  6. COMMIT; #提交事务

END // DELIMITER ;

插入400,0000条数据,从100001号开始

CALL insert_stu1(100001, 4000000);

  3. - 
  4. 查看一条记录并查看慢查询条数
  5. > ![](https://blog.chasingwind.top/6oLWoz.png#alt=)
  10. <a name="08da11bc"></a>
  11. ### 慢查询日志分析:mysqldumpslow
  13. - 
  14. 使用mysqldumpslow查看慢查询的语句，后续使用EXPLAIN进行分析
  15. > ![](https://blog.chasingwind.top/WjowTO.png#alt=)
  20. <a name="561b9764"></a>
  21. ### 慢查询日志关闭
  23. - 临时性关闭：`SET GLOBAL slow_query_log=off;`
  24. - 永久性关闭可以修改配置文件
  26. <a name="abe8c44a"></a>
  27. ## 查看SQL执行成本：Show Profile
  29. - 
  30. 查看开启状态：`show variables like 'profiling';`
  32. - 
  33. 开启：`set profiling = 'ON';`
  35. - 
  36. 查看已经执行的SQL语句：`show profiles`
  38. - 
  39. 查看对应的SQL语句的执行成本：`show profile for query 编号;`
  40. > ![](https://blog.chasingwind.top/86P94C.png#alt=)
  44. - 
  45. **此命令将被弃用，可以从information_schema.PROFILING表查看**
  48. <a name="afac4737"></a>
  49. ## 分析查询语句：EXPLAIN
  51. <a name="a4d3b02a"></a>
  52. ### 概述
  54. - [官网](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html)
  55. - MySQL优化器会对执行SQL给出他认为最优的执行计划。其中展示了具体执行计划的方式。**而`EXPLAIN`就是用来查看执行计划的工具。**
  57. <a name="f2b0b493"></a>
  58. ### 语法
  60. - 
  61. `explain + 需要执行的SQL语句`
  62. > ![](https://blog.chasingwind.top/B8yXSu.png#alt=)
  66. - 
  67. 列的含义
  68. > ![](https://blog.chasingwind.top/OOy6sx.png#alt=)
  73. <a name="9195cc17"></a>
  74. ### 数据准备
  76. - 
  77. sql语句
  78. ```mysql
  79. # 两个表字段相同 索引也相同
  80. CREATE TABLE s1
  81. (
  82.     id           INT AUTO_INCREMENT,
  83.     key1         VARCHAR(100),
  84.     key2         INT,
  85.     key3         VARCHAR(100),
  86.     key_part1    VARCHAR(100),
  87.     key_part2    VARCHAR(100),
  88.     key_part3    VARCHAR(100),
  89.     common_field VARCHAR(100),
  90.     PRIMARY KEY (id),
  91.     INDEX idx_key1 (key1),
  92.     UNIQUE INDEX idx_key2 (key2),
  93.     INDEX idx_key3 (key3),
  94.     INDEX idx_key_part (key_part1, key_part2, key_part3)
  95. ) ENGINE = INNODB
  96.   CHARSET = utf8;
  98. CREATE TABLE s2
  99. (
  100.     id           INT AUTO_INCREMENT,
  101.     key1         VARCHAR(100),
  102.     key2         INT,
  103.     key3         VARCHAR(100),
  104.     key_part1    VARCHAR(100),
  105.     key_part2    VARCHAR(100),
  106.     key_part3    VARCHAR(100),
  107.     common_field VARCHAR(100),
  108.     PRIMARY KEY (id),
  109.     INDEX idx_key1 (key1),
  110.     UNIQUE INDEX idx_key2 (key2),
  111.     INDEX idx_key3 (key3),
  112.     INDEX idx_key_part (key_part1, key_part2, key_part3)
  113. ) ENGINE = INNODB
  114.   CHARSET = utf8;
  • 表结构

    性能分析工具 - 图6

  • 添加数据 ```mysql set global log_bin_trust_function_creators = 1; # 不加global只是当前窗口有效

    随机字符串函数

    DELIMITER // CREATE FUNCTION rand_string1(n INT) RETURNS VARCHAR(255) #该函数会返回一个字符串 BEGIN DECLARE chars_str VARCHAR(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ’; DECLARE return_str VARCHAR(255) DEFAULT ‘’; DECLARE i INT DEFAULT 0; WHILE i < n
    1.   DO
    2.       SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1)); SET i = i + 1;
    3.   END WHILE;
    RETURN return_str; END // DELIMITER ;

表1添加数据

DELIMITER // CREATE PROCEDURE insert_s1(IN min_num INT(10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s1 VALUES ((min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ;

表s2添加数据

DELIMITER // CREATE PROCEDURE insert_s2(IN min_num INT(10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s2 VALUES ((min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ;

调用存储过程

CALL insert_s1(10001, 10000);

CALL insert_s2(10001, 10000);

  4. <a name="7d64ccdb"></a>
  5. ### EXPLAIN字段
  7. <a name="89cbf0e6"></a>
  8. #### table：表名
  10. - 
  11. 查询的**每一行记录都对应着一个单表**
  12. > ![](https://blog.chasingwind.top/YqrgZn.png#alt=)
  17. <a name="48bd060c"></a>
  18. #### id：标识符
  20. - 
  21. 在一个大的查询语句中**每个SELECT关键字都对应一个唯一的id**
  22. > ![](https://blog.chasingwind.top/zovMNM.png#alt=)
  24. > ![](https://blog.chasingwind.top/ubOoDs.png#alt=)
  26. > ![](https://blog.chasingwind.top/Jn9A2g.png#alt=)
  28. > 而对于UNION ALL不需要去重，那么就有两个id
  30. > ![](https://blog.chasingwind.top/G5ICMB.png#alt=)
  34. - 
  35. id小结
  37.    - **id如果相同，可以认为是一组，从上往下顺序执行**
  38.    - **在所有组中，id值越大，优先级越高，越先执行**
  39.    - **关注点：每一个id表示一趟独立的查询, 一个sql的查询趟数越少越好**
  41. <a name="af815777"></a>
  42. #### select_type：SELECT类型
  44. - 
  45. select_type包含的类型
  46. > ![](https://blog.chasingwind.top/NY9sYN.png#alt=)
  50. - 
  51. UNION和DEPENDENT UNION
  52. > ![](https://blog.chasingwind.top/wtOWxE.png#alt=)
  54. > ![](https://blog.chasingwind.top/DIddkv.png#alt=)
  58. - 
  59. DERIVED
  60. > 派生表：将查询结果作为表
  62. > ![](https://blog.chasingwind.top/BHNbAb.png#alt=)
  66. - 
  67. MATERIALIZED（物化）
  68. > 官网对于物化的介绍：[Optimizing Subqueries with Materialization](https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html)
  70. > 为了加速子查询，物化将子查询的结果存在内存中。
  72. > ![](https://blog.chasingwind.top/Undqlh.png#alt=)
  74. > ![](https://blog.chasingwind.top/9kwVfH.png#alt=)
  79. <a name="a5c48833"></a>
  80. #### partitions：分区
  82. - 
  83. 表示分区表中的命中情况。对于非分区表，该值为NULL
  85. - 
  86. [ ALTER TABLE Partition Operations](https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html)
  87. > ![](https://blog.chasingwind.top/7y2wlt.png#alt=)
  92. <a name="c5fdc841"></a>
  93. #### type：访问类型
  95. - 
  96. [EXPLAIN Join Types](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types)：The `type` column of [`EXPLAIN`](https://dev.mysql.com/doc/refman/8.0/en/explain.html) output **describes how tables are joined**.
  98. - 
  99. 表示MySQL**对某个表的执行查询时的访问方法**。最好到最坏的方法如下： `system` ， `const` ， `eq_ref` ， `ref` ， `fulltext` ， `ref_or_null` ， `index_merge` ， `unique_subquery` ， `index_subquery` ， `range` ， `index` ， `ALL` 。
  101. - 
  102. **system**：当表中只有一条记录并且存储引擎的统计数据是精确的
  103. > MyISAM存储引擎中每一个表都有额外的字段记录总的记录条数
  105. > 只有一条记录并且存储引擎是MyISAM，结果type是`system`
  107. > ![](https://blog.chasingwind.top/xsxdEj.png#alt=)
  109. > 只有一条记录并且存储引擎是InnoDB，结果type是`ALL`
  111. > ![](https://blog.chasingwind.top/P37bw0.png#alt=)
  115. - 
  116. `const`：**根据主键或者唯一二级索引列与常数进行等值匹配**时，对单表的访问方法就是`const`
  117. > ![](https://blog.chasingwind.top/4Korka.png#alt=)
  119. > 而对于普通索引就是ALL
  121. > ![](https://blog.chasingwind.top/TjMUWr.png#alt=)
  123. > **因为对于唯一索引，找到某个叶子结点中的记录的主键之后就可以进行回表操作。不再需要查看其他记录。**
  127. - 
  128. `eq_ref`：在连接查询时，如果**被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的**，则对该被驱动表的访问方法就是`eq_ref`
  129. > 如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较
  131. > ![](https://blog.chasingwind.top/Ipgp64.png#alt=)
  133. > 其实这里就是遍历s1表，**拿到每一个记录的id（这里为主键），此时就是常量了，去s2表中找id相同的记录。**
  137. - 
  138. `ref`：**通过普通的二级索引列与常量进行等值匹配时来查询某个表**，那么对该表的访问方法就可能是`ref`
  139. > ![](https://blog.chasingwind.top/8OhhkH.png#alt=)
  143. - 
  144. `ref_or_null`：当对**普通二级索引进行等值匹配查询和NULL值匹配查询时**
  145. > ![](https://blog.chasingwind.top/ldRoAL.png#alt=)
  149. - 
  150. `index_merge`：索引合并使用
  151. > ![](https://blog.chasingwind.top/NrQbRn.png#alt=)
  155. - 
  156. `unique_subquery`：如果查询**优化器决定将`IN`子查询转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话**
  157. > ![](https://blog.chasingwind.top/WH3ezC.png#alt=)
  161. - 
  162. `range`：使用**索引获取某些`范围区间`的记录**
  163. > ![](https://blog.chasingwind.top/OnxV9N.png#alt=)
  167. - 
  168. `index`：当我们**可以使用索引覆盖，但需要扫描全部的索引记录时**
  169. > ![](https://blog.chasingwind.top/bJI6np.png#alt=)
  173. - 
  174. `ALL`：全表扫描
  175. > ![](https://blog.chasingwind.top/b2Rs3V.png#alt=)
  180. ☆小结：结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL<br />
  181. SQL 性能优化的目标：**至少要达到range级别，要求是ref级别，最好是const级别。**（阿里巴巴开发手册要求）
  183. <a name="c1149e21"></a>
  184. #### key和possible_keys
  186. - 
  187. possible_keys表示执行计划中**可能用到的索引**
  189. - 
  190. key表示**实际使用到的索引。**
  191. > ![](https://blog.chasingwind.top/p8DOjz.png#alt=)
  196. <a name="a6389def"></a>
  197. #### key_len：实际使用到的索引长度
  199. - 
  200. 单位：字节数
  202. - 
  203. 是否充分利用了索引，值越大越好。**主要针对于联合索引**，有一定的参考意义。
  204. > ![](https://blog.chasingwind.top/4viDGX.png#alt=)
  206. > 长度计算：
  208. > 允许为NULL，需要1个字节记录
  210. > 可变长度的字段（比如varchar），需要2个字节记录长度
  212. > 字符转换为字节：character set：utf8=3字节,gbk=2字节,latin1=1字节
  217. <a name="81836de8"></a>
  218. #### ref：与索隐列进行匹配的对象信息
  220. - 
  221. 当使用索引列进行等值查询的时候，与索隐列进行匹配的对象信息
  222. > ![](https://blog.chasingwind.top/FwDteB.png#alt=)
  224. > ![](https://blog.chasingwind.top/5WZY7T.png#alt=)
  229. <a name="b7028e13"></a>
  230. #### rows：预计需要读取的行数
  232. - 
  233. 值越小越好
  234. > ![](https://blog.chasingwind.top/ii6NBL.png#alt=)
  239. <a name="faf6213e"></a>
  240. #### filtered：过滤百分比
  242. - 
  243. 某个表**经过搜索条件过滤后剩余记录条数的百分比**
  244. > ![](https://blog.chasingwind.top/ZxVrDT.png#alt=)
  246. > 更常用在连接查询中驱动表对应的执行计划的filtered值
  248. > ![](https://blog.chasingwind.top/aQ5w0R.png#alt=)
  253. <a name="18971b60"></a>
  254. #### extra：额外信息
  256. - 
  257. 额外信息很重要。我们可以通过此信息**更准确地理解MySQL到底将如何执行给定的SQL语句**。
  259. - 
  260. 常见的信息如下：
  262.    - 
  263. No tables used
  264. > 当查询语句的没有`FROM`子句时将会提示该额外信息
  266. > ![](https://blog.chasingwind.top/DsW2gQ.png#alt=)
  270.    - 
  271. Impossible WHERE
  272. > **查询语句条件永远不可能成立**的时候提示的额外信息
  274. > ![](https://blog.chasingwind.top/bY9OoZ.png#alt=)
  278.    - 
  279. Using where
  280. > 当我们使用**全表扫描来执行对某个表的查询，并且该语句的`WHERE`子句中有针对该表的搜索条件时(除了索引字段)**，在`Extra`列中会提示上述额外信息。
  282. > ![](https://blog.chasingwind.top/RiY6QT.png#alt=)
  286.    - 
  287. No matching min/max row
  288. > 当表中有MIN或MAX聚合函数，并且符合条件的记录不存在的时候
  290. > ![](https://blog.chasingwind.top/xvapyA.png#alt=)
  294.    - 
  295. Using Index（覆盖索引，建议）
  296. > 使用索引覆盖，不需要回表操作。**查询列表以及搜索条件中只包含属于某个索引的列**
  298. > ![](https://blog.chasingwind.top/W3nw5A.png#alt=)
  300. > 由于非聚簇索引中有主键的值，所以查询列表中有主键和索引列的值的时候，也会显示此信息
  302. > ![](https://blog.chasingwind.top/Ck5Xvz.png#alt=)
  306.    - 
  307. Using index condition（**使用了索引条件下推**）
  308. > 对于**条件中出现了索引字段，但是并未用上索引**
  310. > ![](https://blog.chasingwind.top/wB1VUi.png#alt=)
  312. > 分析：`SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';`
  314. > 1. 首先，根据索引`key1`查找到满足`key1>'z'`的记录。拿到记录对应的主键值
  315. > 2. 然后再去聚簇索引中根据主键值找到记录的key1值满足`key1 LIKE '%a'`的记录（回表），放入到结果集中
  316. > 3. 然后将结果集返回
  318. > 
  319. 事实上，**回表操作是很耗费时间的（随机IO）**。可能导致上面391条记录，经过`key1 LIKE '%a'`过滤后满足的很少，但是还是需要每个都去聚簇索引中查找。
  321. > **优化**：既然使用了key1索引，并且两个条件都是key1相关的字段。由于key1索引中已经包含了key1字段的值，那么在满足`key1>'z'`的基础之上，先**不进行回表操作，而是通过条件`key1 LIKE '%a'`过滤出最后的结果之后，再去聚簇索引拿到其他字段的信息。**
  325.       - 
  326. [索引条件下推（Index Condition Pushdown (ICP)）](https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html)：一句话来说就是：**索引条件下推`ICP`就是尽可量利用二级索引筛除不符合`where`条件的记录，如此一来减少需要回表继续判断的次数**
  328.       - 
  329. 索引条件下推**默认开启**，可以调整
  330. > 查看：`show variables like '%optimizer_switch%'`
  332. > 开启：`SET optimizer_switch = 'index_condition_pushdown=on';`
  334. > 关闭：`SET optimizer_switch = 'index_condition_pushdown=off';`
  336. > 关闭后再次执行，只显示Using where
  338. > ![](https://blog.chasingwind.top/zJgk2Q.png#alt=)
  342.    - 
  343. Using join buffer (hash join)
  344. > 在连接查询执行过程中，当**被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫`join buffer`的内存块来加快查询速度**，也就是我们所讲的`基于块的嵌套循环算法`
  346. > ![](https://blog.chasingwind.top/RYKeqI.png#alt=)
  350.    - 
  351. Not exists
  352. > 当我们使用左（外）连接时，**如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，而且那个列又是不允许存储`NULL`值的**，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息
  354. > ![](https://blog.chasingwind.top/rKRQwC.png#alt=)
  358.    - 
  359. Using union
  360. > 表示使用**索引合并的方式执行查询**
  362. > ![](https://blog.chasingwind.top/adzA04.png#alt=)
  366.    - 
  367. Zero limit
  368. > limit 0
  370. > ![](https://blog.chasingwind.top/3Jiwq0.png#alt=)
  374.    - 
  375. Using filesort（文件排序，尽量规避）
  376. > **很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序**（英文名：`filesort`）。
  378. > ![](https://blog.chasingwind.top/33hSXq.png#alt=)
  382.    - 
  383. Using temporary（使用临时表，尽量避免）
  384. > 在许多查询的执行过程中，MySQL可能会**借助临时表来完成一些功能**，比如去重、排序之类的，比如我们在执行许多**包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中**，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的`Extra`列将会显示`Using temporary`提示
  386. > 非索引字段去重需要使用临时表将数据加载进内存中，然后再进行去重
  388. > ![](https://blog.chasingwind.top/PyR0sp.png#alt=)
  390. > 而对于索引字段去重就不需要临时表，索引本身就是排好序的，直接在索引字段上进行去重即可
  392. > ![](https://blog.chasingwind.top/dDSDHf.png#alt=)
  397. <a name="eb1e3038"></a>
  398. ### EXPLAIN输出格式
  400. - 
  401. EXPLAIN可以输出四种格式： `传统格式` ， `JSON格式` ， `TREE格式` 以及 `可视化输出` 。用户可以根据需要选择适用于自己的格式。
  403. - 
  404. 传统格式
  405. > 以表格的形式呈现
  407. > ![](https://blog.chasingwind.top/78GREf.png#alt=)
  411. - 
  412. JSON格式：`EXPLAIN FORMAT=JSON SQL语句`
  413. > 4种里面信息最详尽的格式，包含了**执行的成本信息**
  415. > ![](https://blog.chasingwind.top/lIKoUx.png#alt=)
  418. ```json
  419. {
  420.   "query_block": {
  421.     "select_id": 1,
  422.     "cost_info": {
  423.       "query_cost": "1360.07"   //执行成本
  424.     },
  425.     "nested_loop": [
  426.       {
  427.         "table": {
  428.           "table_name": "s1",
  429.           "access_type": "ALL",
  430.           "possible_keys": [
  431.             "idx_key1"
  432.           ],
  433.           "rows_examined_per_scan": 9895,
  434.           "rows_produced_per_join": 989,
  435.           "filtered": "10.00",
  436.           "cost_info": {
  437.             "read_cost": "914.80",
  438.             "eval_cost": "98.95",
  439.             "prefix_cost": "1013.75",
  440.             "data_read_per_join": "1M"
  441.           },
  442.           "used_columns": [
  443.             "id",
  444.             "key1",
  445.             "key2",
  446.             "key3",
  447.             "key_part1",
  448.             "key_part2",
  449.             "key_part3",
  450.             "common_field"
  451.           ],
  452.           "attached_condition": "((`test_db`.`s1`.`common_field` = 'a') and (`test_db`.`s1`.`key1` is not null))"
  453.         }
  454.       },
  455.       {
  456.         "table": {
  457.           "table_name": "s2",
  458.           "access_type": "eq_ref",
  459.           "possible_keys": [
  460.             "idx_key2"
  461.           ],
  462.           "key": "idx_key2",
  463.           "used_key_parts": [
  464.             "key2"
  465.           ],
  466.           "key_length": "5",
  467.           "ref": [
  468.             "test_db.s1.key1"
  469.           ],
  470.           "rows_examined_per_scan": 1,
  471.           "rows_produced_per_join": 989,
  472.           "filtered": "100.00",
  473.           "index_condition": "(cast(`test_db`.`s1`.`key1` as double) = cast(`test_db`.`s2`.`key2` as double))",
  474.           "cost_info": {
  475.             "read_cost": "247.38",
  476.             "eval_cost": "98.95",
  477.             "prefix_cost": "1360.08",
  478.             "data_read_per_join": "1M"
  479.           },
  480.           "used_columns": [
  481.             "id",
  482.             "key1",
  483.             "key2",
  484.             "key3",
  485.             "key_part1",
  486.             "key_part2",
  487.             "key_part3",
  488.             "common_field"
  489.           ]
  490.         }
  491.       }
  492.     ]
  493.   }
  494. }
  • TREE格式

    性能分析工具 - 图7

Show warnings

  • 在执行Explain之后，可通过show warnings查看与执行的EXPLAIN的相关的一些扩展信息

    可以看到优化器优化后的语句

比如之前子查询的语句

性能分析工具 - 图8

show warnings中显示的信息

性能分析工具 - 图9

小结

  • EXPLAIN不会考虑缓存，所以不存在缓存影响多次运行分析的结果不同
  • EXPLAIN不能显示MySQL在执行查询时所做的优化工作，比如，IN转化为EXISTS
  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
  • 部分统计信息是估算的，并非精确值

分析优化器执行计划：trace

  • OPTIMIZER_TRACE可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到information_schema.OPTIMIZER_TRACE表中

  • 默认关闭，开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免音内存过小导致信息不完整 ```mysql SET optimizer_trace=”enabled=on”,end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000; ```

MySQL监控分析视图：sys schema

  • MySQL5.7.7中新增，将information_schema和performance_schema中的数据以更容易理解的方式总结归纳为视图，目的就是为了降低查询performance_schema数据库的复杂度

    性能分析工具 - 图10

性能分析工具 - 图11