闲聊MySQL（八）：查询优化

前言

在上几篇中，我们对MySQL的InnoDB引擎进行深入了解，对其内部架构实现进行了分析，了解了InnoDB内部机制，也同时需要掌握高效SQL的编写技巧，才能发挥出更加出色的性能。本篇，我们就来聊一下SQL的查询优化。

查询优化

MySQL逻辑架构

说起SQL的查询优化，我们就不得不从一条SQL的执行说起，例如一条简单的SQL语句：select * from user，当执行这条SQL语句后，MySQL又做了什么呢？

想知道这个答案，我们先来看一下MySQL的逻辑架构组成。

上图是MySQL的逻辑架构示意图。最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接的处理、授权认证、安全等等。

第二层架构是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：包括存储过程、触发器、视图等。

第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。和Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包括几十个底层函数，用于执行注入“开始一个事务”或者“根据主键提取一行记录”等操作。但存引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单的响应上层服务器的请求。

OK，了解完MySQL的逻辑架构，我们再来看看一条SQL查询执行的过程是怎样的。

SQL执行过程

我们来回到最开头的问题，执行一条简单的SQL语句：select * from user后，发生了什么？

与把大象装冰箱，一共需要几步的问题一样，当向MySQL发送一个请求的时候，MySQL进行下面几步操作：

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

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

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

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

5、拿到执行结果，将执行结果进行缓存，再将结果集返回给客户端。

我们来对每一步操作进行简单的分析。

查询缓存

MySQL查询缓存保存查询返回的完整结构。当查询命中该缓存时，MySQL会立刻返回结果，不会执行下面的步骤。

MySQL将缓存存放在一个引用表（类似于K-V的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。

所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。

查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。

而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1、任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

2、如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

这样听起来，这个缓存是有点鸡肋的意思，感觉用起来还不如不用，事实上正是如此，查询缓存默认的情况下是不开启的，如果你希望开启缓存，需要评估好业务场景的实际情况，是否是读极多而写极少，否则，开启查询缓存可能会带来适得其反的效果。

通过系统命令可以查看缓存状态：

show variables like '%query_cache%';

如果你希望开启，可以通过修改配置文件，修改[mysqld]下的query_cache_size和query_cache_type（如果没有则添加）。

其中query_cache_size表示缓存的大小，而query_cache_type有3个值，表示缓存哪种类型的select结果集 ，query_cache_type各个值如下：

• 0或off关闭缓存

• 1或on开启缓存，但是不保存使用sql_no_cache的select语句，例如不缓存

  select sql_no_cache name from user where user_id=1

• 2或demand开启有条件缓存，只缓存带sql_cache的select语句，例如缓存

  select sql_cache name from user where user_id=4

配置可以参考：

query_cache_size=10M
query_cache_type=1

解析与预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。

比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。

预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等。

查询优化器

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。

多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。

查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。

存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

执行结果返回

在上一步查询执行引擎拿到查询结果后，将结果集返回至客户端，即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

SQL优化

我们了解了MySQL的SQL执行过程，解释了select * from user这条语句执行后发生了什么，下面，我们从数据表设计以及SQL编写的角度，来聊一聊SQL的优化。当然，SQL的优化是一个非常复杂的过程，这里我只是简要的介绍一部分优化的技巧，仅供您进行参考。

数据表结构优化

1、通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
MySQL对于索引字段为NULL时候的处理，where条件中使用is null查询时，查询可能命中索引，引自MySQL官方文档：

MySQL can perform the same optimization on col_name IS NULL that it can use for 
col_name = constant_value. For example, MySQL can use indexes and ranges to search 
for NULL with IS NULL.

而使用is not null查询时，则无法命中索引，会进行全表扫描。

2、UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。一般情况下，建议使用UNSIGNED，除非你需要存储负数。
参见 阿里巴巴开发规约

3、尽量避免使用DECIMAL数据类型存储数字，在Java中，对于双精度浮点数会有精度丢失的问题，虽然可以使用BigDecimal类型进行映射，但是没有必要。可以将小数扩大N倍，使用BIGINT类型进行存储。

4、TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

5、一张表的属性字段不易过多，原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

6、数据量特别大的表执行alter table要非常慎重，特别对于新增唯一索引字段，在旧版本的MySQL中，会使用锁表的方式进行变更，在新版本的MySQL中，采用了Online DDL，但依然有一定的风险。

7、对经常需要进行查询的属性字段，一定要建立索引，建立的原则是：一个包含所有相关列的索引要优于多个独立索引。

8、避免创建重复的索引，比如有一个索引(A,B)，再创建索引(A)就是冗余索引。

9、对字段创建索引时，应考虑字段数据的重复度，例如性别字段，即使对其创建了索引，SQL执行查询时也不会有太大的用途。

执行优化

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及 order by 涉及的列上建立索引。order by的属性列如果不是索引列，会触发file sort操作，影响性能。

2、应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，

如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

但仍需注意的是，如果num为0的数据太多时，即使加索引后，查询的效率与全表扫描也不会有太大的区别。

3、应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

4、避免使用select *，如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。至于背后的实现原理，我们在上一篇介绍InnoDB的索引结构时，进行过介绍，可以参见闲聊MySQL：（七）InnoDB之索引结构

5、查询条件中使用like时，遵循左匹配原则，例如：select id from user where name like 'zhang%'。

6、应尽量避免在 where 子句中对字段进行表达式或函数操作，这将导致引擎放弃使用索引而进行全表扫描。

7、尽可能的避免使用in操作，可以使用exists替代。

8、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

比较典型的例子就是IP地址的存储。MySQL提供了inet_aton与inet_ntoa函数进行支持。

9、当使用limit分页时，需要注意，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。

建议可以使用主键来缩小查询的范围，例如：

SELECT id FROM user WHERE id > 10000 LIMIT 10;

10、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

总结

本篇，我们对MySQL的查询优化方面进行了了解，主要介绍了MySQL中SQL的执行过程与SQL语句书写中需要注意的一些点，SQL的优化是一个非常复杂的事情，需要有较丰富的经验，结合实际场景进行“对症下药”。

我个人的建议是，多用explain看一下代码中的SQL，排除隐患，道路千万条，干掉慢SQL第一条。

本篇的内容就到这里，感谢您的阅读，下一篇，我们来聊一下MySQL的主从复制，敬请期待。

更多精彩文章， 请关注我的个人公众号：老宣说
让我们一起共同学习成长！
感谢您的支持！

本文参考：

万字总结：学习MySQL优化原理，这一篇就够了！

mysql查询缓存配置和查看

MySQL探秘(二)：SQL语句执行过程详解

《高性能MySQL第三版》