MySQL执行计划解析

向右看齐 2023-02-15 03:58 23阅读 0赞

![format_png][]

点击上方蓝字，关注我们

![format_png 1][]

![format_png 2][]

##  ##

前言

--------------------

在实际数据库项目开发中，由于我们不知道实际查询时数据库里发生了什么，也不知道数据库是如何扫描表、如何使用索引的，因此，我们能感知到的就只有SQL语句的执行时间。尤其在数据规模比较大的场景下，如何写查询、优化查询、如何使用索引就显得很重要了。

那么，问题来了，在查询前有没有可能估计下查询要扫描多少行、使用哪些索引呢？

答案是肯定的。以MySQL为例，MySQL通过explain命令输出执行计划，对要执行的查询进行分析。

什么是执行计划呢？

简单来说，就是SQL在数据库中执行时的表现情况，通常用于SQL性能分析、优化等场景。

本文从MySQL的逻辑结构讲解，过渡到MySQL的查询过程，然后给出执行计划的例子并重点介绍执行计划的输出参数，从而理解为什么我们会选择文中建议的方案。

###     ###

##  ##

MySQL逻辑架构

--------------------

MySQL逻辑架构分为三层，如下图：

![format_png 3][]

客户端：

如，连接处理、授权认证、安全等功能；

核心服务：

MySQL大多数核心服务均在这一层，包括查询解析、分析、优化、缓存、内置函数（如，时间、数学、加密等），所有的跨存储引擎的功能也在这一层，如，存储过程、触发器、视图等；

存储引擎

负责MySQL中的数据存储和读取中间的服务层通过API与存储引擎通信，这些API屏蔽了不同存储引擎间的差异；

重点解释下查询缓存：对于select语句，在解析查询之前，服务器会先检查查询缓存（Query Cache）。如果命中，服务器便不再执行查询解析、优化和执行的过程，而是直接返回缓存中的结果集。

###     ###

##  ##

MySQL查询过程

###     ###

--------------------

如果能搞清楚MySQL是如何优化和执行查询的，对优化查询一定会有帮助。很多查询优化实际上就是遵循一些原则让优化器能够按期望的合理的方式运行。

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。本文只给予简单的介绍。

![format_png 4][]

MySQL查询过程如下：

1.客户端将查询发送到MySQL服务器；

2.服务器先检查查询缓存，如果命中，立即返回缓存中的结果；否则进入下一阶段；

3.服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划；

4.MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询；

5.服务器将结果返回给客户端，同时缓存查询结果；

###     ###

--------------------

##  ##

优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），并对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。

用户可通过关键字提示（hint）优化器，从而影响优化器的决策过程。也可以通过通过优化器解释（explain）优化过程的各个因素，使用户知道数据库是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和数据库表的schema、修改数据库配置等，使查询尽可能高效。

例子

![format_png 5][]

这个执行计划给出的信息是，该查询通过一个简单的给定范围的扫描，共扫描55183行，使用index condition条件在dt\_user表中筛选出，扫描过程中使用PRIMARY和idx\_city\_name索引。

输出参数

id：select查询序列号，id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行；

select\_type：查询数据的操作类型，有如下：

simple，简单查询，不包括子查询和union；

primary，包含复杂的子查询，最外层查询标记为该值；

subquery，在select活where中包含子查询，被标记为该值；

derived，在from列表中包含的子查询被标记为改值，MySQL会递归这些子查询，将结果保存到临时表；

union，第二个select出现在union之后，被标记为该值；union包含在from的子查询中，外层select被标记为derived；

union result，从union表获取结果的select；

table：显示该行数据是关于哪张表；

partitions：匹配的分区；

type：表的连接类型，其值、性能由高到底排列如下：

system，表中只有一行记录，相当于系统表；

const，通过索引一次命中，匹配一行数据；

eq\_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用语主键或唯一索引扫描；

ref，非唯一性索引扫描，返回匹配某个单独值的所有行，用于=、<或>操作符带索引的列；

range，只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；

index，只遍历索引树；

all，全表扫描；

前5种情况都是理想的索引的情况。通常优化至少到range级别，最好能优化到ref。

possible\_keys：指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能；

key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询；

key\_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好显示的是索引字段的最大长度，并非实际使用长度；

ref：显示该表的索引字段关联了哪张表的哪个字段；

rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好；

filtered：返回结果的行数占读取行数的百分比，值越大越好；

extra：包含不适合在其他列中显示但十分重要的额外信息。常见的值如下：

using filesort，MySQL会对数据使用一个外部索引排序，而不是按照表内索引顺序进行读取，若出现改值，则应优化SQL语句；

using temporary，使用临时表缓存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by，若出现该值，则应优化SQL；

using index，表示select操作使用了覆盖索引，避免了访问表的数据行；

using where，where子句用于限制哪一行；

using join buffer，使用连接缓存；

distinct，发现第一个匹配后，停止为当前的行组合搜索更多的行；

###     ###

##  ##

小结

--------------------

数据库性能优化很多，本文只简单了介绍MySQL逻辑结构、查询过程和执行计划参数。根据执行计划输出的索引使用情况、扫描的行数可以预估查询效率，帮助我们重构查询、优化表结构或者索引，从而尽可能提供查询效率。

![format_png 6][]

[大型系统的发布部署方案（图文）][Link 1]

[分布式系统中Redis为什么那么重要？][Redis]

[彻底搞清分库分表（垂直分库，垂直分表，水平分库，水平分表）][Link 2]

[小白也能学会的RAID磁盘冗余阵列教程][RAID]

![format_png 7][]

欢迎分享转发，有帮助的话点个“在看”

![format_png 8][]

![format_png 9][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9VUzEwR2NkMHRRSER0ZTZaelhpY2xyWVVUQ1FIaWFrMGszcEtyS2ZPdW9JY2FzYVNLV2JLaWFpYnozRE55UHZVSk9yYXdodHV1aDZ2cm1oOE1XemNoZ04xSWcvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9jNmdxbWhXaWFmeW9LSGU4OWliMlBrZ0Q2eXJSUXpOUnc0MVhHUHVMUHljT095UW5maWE0QkZ5b1QybTVsSVpZZThhOUV1ZEgzQ2RvYllDcjFpYUFEWHZKakEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy94aWNXWVRTSUN6UnRLY3UxbkpFMnZ1aFV3ZmliSzdZRzl1SmNhRDJRMmFOZ1A5dzFmNVNRVFRiWTVDT29ZaWE2VkRWaWJFQXRTazl1VFZVS2VkVmljMk1TTUtBLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L3hpY1dZVFNJQ3pSdEtjdTFuSkUydnVoVXdmaWJLN1lHOXVSMWNjaDA5aHRodzBKckxxeTVRQmFhVEowdGdEbURXYVh4dWlhcU5DRk4zbDFpYVp1WXBWbjVYZy82NDA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L3hpY1dZVFNJQ3pSdEtjdTFuSkUydnVoVXdmaWJLN1lHOXVQS0hXTWlhbGtDaWF4MzhXUkZ1d2xFR2NNaWNiNjB2d3lkRUlUQm1jOTJuc3FHOERSR1pZZ1lJT1EvNjQw?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6L3hpY1dZVFNJQ3pSdEtjdTFuSkUydnVoVXdmaWJLN1lHOXV3UW0wV1hzOFB6NDJPS2E2VHF2Z3NUUnFzR0lQZzYxNk9EVDN1WnhTMVhucFBEbURXcEkxNFEvNjQw?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9jMlNpYjNNcDdwT1BpY2ZvT3hEc2liamRkWUpvemVoaWNMTjlwWEJiQ1JGWXJ6VXVnUkp0M29nbFBjSW1IbTliMk93SlppYlpsT2ljRjRMSGw0bmVhQmJrY1ZZZy82NDA?x-oss-process=image/format,png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f6d1cb337fc72ee5a2202c8a559eec12e44f167db6c45919f16aeac1b6c085bf1531c930&idx=1&mid=2247484392&scene=21&sn=4ecf420faf3fad5778bac5ed11c9dc5e#wechat_redirect
[Redis]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f6dbcb337fcdd56552b6a500167f1ac3d49e4d61028a10b223f3b6ce21a378e4415c688a&idx=1&mid=2247484386&scene=21&sn=73e58f95078cd8f93ab315c4c78d05a1#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f6e2cb337ff47a54d15bdb0b4c8711319e26ad4db1a7d3e30c8c8bcc0b6a504f6bfca7c5&idx=1&mid=2247484379&scene=21&sn=4964f64ad5b6a5d024ead17cb518262f#wechat_redirect
[RAID]: https://blog.csdn.net/u012811805/article/details/105347148
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnZ6aGRLck1pY1NQSWliZzhQRWJPa2E4bmpjOHJFZmFLa09YTHAyd21wckxpY0JRaWNYMWlhdVFzWEdTTDlpYXhreFZTb3RQQ2F2U2hva1NvVHcvNjQw?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi94aWNXWVRTSUN6UnNKRmliZXRqdzJEblVBaG1VQkdrRWF0QXZlMkYxMEtuQjlTVnZzd01lRGxMVzUyY1d3RWljTmoyRU84bG9YQTZMcEhRbTQ5NFdCZUt1US82NDA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy94aWNXWVRTSUN6UnRLY3UxbkpFMnZ1aFV3ZmliSzdZRzl1Z3hpYXhRQlNpY3ZFUTIwdm55TFJycE1mYmw3V21TMjBWdWJTZ1ZnWFVmaWExQzVpYlJxUm1KeVdCZy82NDA?x-oss-process=image/format,png