轻松搞懂MySQL的执行计划,不再怕SQL优化了

2021年11月23日 阅读数:1
这篇文章主要向大家介绍轻松搞懂MySQL的执行计划,不再怕SQL优化了,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

摘要:数据库的解释计划阐明了sql的执行过程,展现了执行的细节,只要根据数据库告诉咱们的问题按图索骥的分析就能够。html

 

本文分享自华为云社区​​《轻松搞懂mysql的执行计划,不再怕sql优化了》​​,做者:香菜聊游戏。mysql

 

近期要作一些sql优化的工做,虽然记得一些经常使用的sql 优化技巧,可是在工做中仍是不够,因此须要借助工具的帮助,数据库的解释计划阐明了sql的执行过程,展现了执行的细节,咱们只要根据数据库告诉咱们的问题按图索骥的分析就行了,可是解释计划也不是那么容易看懂,因此今天就学习下解释计划的一些参数的意义。sql

一、准备工做

准备三张表,一张角色表,一张装备表,一张基础数据表,这里只展现一些教程中须要的字段,在游戏开发的过程当中确定不止这么几个字段,我想你们都懂的。数据库


角色表:ide


CREATE TABLE `role` (
`n_role_id` int DEFAULT NULL,
`s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


​装备表:工具


CREATE TABLE `equip` (
`n_equip_id` int DEFAULT NULL,
`s_equip_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`n_config_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


装备配置表:性能


CREATE TABLE `dict_equip` (
`n_equip_id` int DEFAULT NULL,
`s_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

二、初识解释计划

有两种方式能够查看解释计划:学习

一、命令的方式:explainsql,或者descsql ,两个命令均可以,我以为记住explain比较好,单词很直接。优化

二、借助工具 Navicat(其余的不熟,估计也有),点击查询窗口的解释,能够不用加关键字explainui


轻松搞懂MySQL的执行计划,不再怕SQL优化了_数据库


能够看到结果里面包含了不少列,有的是null 有的有值,只要咱们看懂了解释计划是否是就能够有的放矢的优化sql。

三、字段详解

解释计划的字段仍是蛮多的,Navicat显示了12个字段,有些字段咱们须要重点关注,有些知道怎么回事就行了。

官方的文档解释:​​https://dev.mysql.com/doc/refman/5.7/en/explain-output.html​


一、id 执行的顺序

id 是select的执行顺序,id越大优先级越高,越先被执行,id 相同时​下面的先执行.

缘由是由于执行子查询时,先查内层的,再查外层


SELECT
de.*
FROM
dict_equip de
WHERE
de.n_equip_id = (
SELECT n_equip_id FROM equip e WHERE
e.n_role_id = (
SELECT n_role_id FROM role r WHERE r.s_name = '香菜' )
)


轻松搞懂MySQL的执行计划,不再怕SQL优化了_sql_02


从上面的执行计划能够看到先执行了查询role表,后执行了equip ,最后执行了 dict_equip


二、select_type select 的类型

轻松搞懂MySQL的执行计划,不再怕SQL优化了_mysql_03


三、table 查询涉及的表或衍生表

当前输出的正在使用的表,能够有下面几种:

<unionM,N> : 行数据是联合以后的数据id 处于 m和n

<derived*N*>: 衍生表

<subqueryN>: 子查询


四、partitions 查询涉及到的分区

在使用分区表的时候才能用到,暂时没用到过这种高级功能。


五、type 查询的类型

表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型以下:


轻松搞懂MySQL的执行计划,不再怕SQL优化了_sql_04


​性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

由左至右,由最差到最好

在进行优化的时候若是查询出的数据量大的话可使用全表扫描,避免使用索引。

若是只是查询不多的数据尽可能使用索引。


六、possible_keys:预计可能使用的索引

在不和其余表进行关联的时候,查询表的是可能使用的索引


七、key:实际查询的过程当中使用的索引

显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL


八、key_len

表示索引中使用的字节数,可经过该列计算查询中使用的索引的长度


九、ref 显示该表的索引字段关联了哪张表的哪一个字段

轻松搞懂MySQL的执行计划,不再怕SQL优化了_mysql_05


注: 我在equip和 dict_equip 两张表都分别添加了索引,索引列是n_equip_id

经过上面的执行计划能够看出,首先使用了索引


十、rows:根据表统计信息及选用状况,大体估算出找到所需的记录或所需读取的行数,数值越小越好

好比 一个列上虽然没作索引,可是都是惟一的,这个时候查找的时候若是是全表读取,就是表里有多少数据这个值就是多少,这个时候你须要优化的就是尽量的读取少的表,能够增长索引,减小读取行数


十一、filtered:返回结果的行数占读取行数的百分比,值越大越好

好比全表有100条数据,可能读取了全表数据,可是只有一条匹配上,这个时候百分比就是1,因此你须要让这个比例越大越好,也就是读到的数据尽可能都是有用的,避免读取不用的数据,由于IO是很费时的。


十二、extra

常见的有下面几种

use filesort:MySQL须要额外的一次传递,以找出如何按排序顺序检索行,若是是这个值,应该优化索引。

use temporary:为了解决查询,MySQL须要建立一个临时表来容纳结果。典型状况如查询包含能够按不一样状况列出列的GROUP BY和ORDERBY子句时。

use index:从只使用索引树中的信息而不须要进一步搜索读取实际的行来检索表中的列信息。当查询只使用做为单一索引一部分的列时,可使用该策略

use where:where子句用于限制哪一行

四、总结

sql 优化的原则就是在保证正确的状况下缩短期,目标是肯定的,经过目标进行回推能够知道想要执行的快就要尽量的少读数据,减小读取数据的方式大的只有两种过滤和使用索引,在这样的规则范围内进行优化,可是注意索引会占用额外的空间,要平衡好这二者的关系。


​点击关注,第一时间了解华为云新鲜技术~​