MySQL 查询优化 | ruofei’s blog

type

Post

status

Published

date

Dec 17, 2023

slug

mysql-select-optimize

summary

MySQL查询过程、查询优化、执行计划、Explain、查询优化器提示

MySQL 查询过程

MySQL查询过程如下：

客户端发送一条查询给服务器。

服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

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

将结果返回给客户端。

查询优化的方法

慢 SQL 的优化，主要从两个方面考虑：SQL 语句本身的优化；数据库设计的优化

1、避免不需要的列

SQL 查询的时候，应该只查询需要的列，而不要包含额外的列。像 select * 这种写法应该尽量避免。

2、分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

延迟关联

先通过 where 条件筛选出主键，再将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行。

id 偏移量

偏移量就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit

3、索引优化

合理地设计和使用索引，是优化慢 SQL 的利器。

覆盖索引

InnoDB 使用二级索引查询数据时会回表，但是如果索引的叶子节点中已经包含要查询的字段，那它没有必要回表查询了，这就叫覆盖索引。简单理解就是查询列就是索引列。

避免使用 or 查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代。因为早起的 MySQL 版本使用 or 查询可能导致索引失效；高版本引入了索引合并，解决了这个问题，不过还是建议不用。

避免使用 != 或者 <> 操作符

SQL 中，!= 操作符会导致索引引擎放弃查询索引，引起全表扫描。

解决方法：通过把不等于操作改为 or，可以使用索引，避免全表扫描。

适当使用前缀索引

适当的使用前缀索引，可以降低索引的空间占用，提高索引的查询效率。

下面总结一下什么情况下使用前缀索引：

字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%'

字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。

前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式，否则可能会导致存储引擎无法正确的使用索引，从而影响查询效率。

正确使用联合索引

使用联合索引时，注意最左匹配。

4、JOIN 优化

优化子查询

尽量使用join语句来替代子查询，因为子查询时嵌套查询，而嵌套查询会新创建一张临时表，而零时表的创建与销毁会占用一定的资源以及花费一定的时间，同事对于返回结果集比较大的子查询，其对查询的性能影响更大。

小表驱动大表

关联查询的时候要拿小表驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接被驱动表。

适当添加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所以可以适当的增加冗余字段，以优化多张表的关联查询，这是以空间换时间的优化策略。

避免使用 join 关联太多的表

不要关联超过三张表。（阿里巴巴Java开发手册）

五、排序优化

利用索引扫描做排序

MySQL 有两种方式生成有序结果：一是对结果集进行排序操作；二是按照索引顺序扫描得出的结果，索引是排好序的数据结构，自然是有序的。

但是如果索引不能覆盖所有的查询所需的列（非覆盖索引），就会没扫描一条记录回表查询一次（逐个获取），这个读操作是随机IO，通常会比顺序全表扫描还慢，有时会直接放弃使用索引转为全表扫描。

因此，在设计索引时，尽可能使用同一个索引即满足排序又用于查找行。

只有当索引的顺序和 order by 字句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。

六、UNION 优化

条件下推

MySQL 处理 union 的策略是先创建零时表，然后将各个查询结果填充到零时表中，最后再来做查询，很多优化策略在 union 查询中都会失效，因为他无法利用索引。

所以需要将 where，limit等字句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。

此外，除非确实需要服务器去重，一定要使用 union all，如果不加 all 字段，MySQL 会给零时表加上 distinct 选项，这会导致对整个零时表做唯一性检查，代价很高。

执行计划

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

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

Explain

简介

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 ➤ 通过EXPLAIN，我们可以分析出以下结果：

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

➤ 使用方式如下：

EXPLAIN +SQL语句

执行计划包含的信息

各字段含义

参考：https://blog.csdn.net/why15732625998/article/details/80388236

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id的结果共有3中情况

id相同，执行顺序由上至下 [总结] 加载表的顺序如上图table列所示：t1 t3 t2

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在如上图所示，在id为1时，table显示的是 <derived2> ,这里指的是指向id为2的表，即t3表的衍生表。

select_type

常见和常用的值有如下几种：

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE 简单的select查询，查询中不包含子查询或者UNION

PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY 在SELECT或WHERE列表中包含了子查询

DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT 从UNION表获取结果的SELECT

table

指的就是当前执行的表

type（重要）

type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

从最好到最差依次是：

一般来说，得保证查询至少达到range级别，最好能达到ref。

system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。

eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的） id是主键，所以存在主键索引

all Full Table Scan 将遍历全表以找到匹配的行

possible_keys

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key（重要）

实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

rows 和 filtered

rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好；

filtered 列表明了 SQL 语句执行后返回结果的行数占读取行数的百分比，值越大越好。MySQL 会使用 Table Filter 来读取出来的行数据进行过滤，理论上，读取出来的行等于返回结果的行数时效率最高，过滤的比率越多，效率越低。

Extra

包含不适合在其他列中显式但十分重要的额外信息

Using filesort（九死一生）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

Using temporary（十死无生）

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

Using index（发财了）

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where

表明使用了where过滤

Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

impossible where

where子句的值总是false，不能用来获取任何元组

select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

实例分析

执行顺序1：select_type为UNION，说明第四个select是UNION里的第二个select，最先执行【select name,id from t2】

执行顺序2：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为DERIVED【select id,name from t1 where other_column=’’】

执行顺序3：select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】

执行顺序4：id列为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3>,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name …】

执行顺序5：代表从UNION的临时表中读取行的阶段，table列的< union1,4 >表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】

查询优化器提示（hint）

简介

在MySQL中，当我们提交SQL查询时，查询优化器默认选择一些索引来获得最佳的查询计划，有时可能不是最好的，但是可通过使用名为USE INDEX的索引提示来推荐查询优化器应该使用的索引。

一起看下MySQL USE INDEX提示的语法：

查看索引

使用 show create table 查看

使用 show indexes 查看