Mysql学习之优化总结(1)--从mysql查询过程看优化

一、前言

提到mysql查询优化，很多人脑海里可能会想到NOT NULL、合理索引、不使用select *、合适的数据类型等等，可是这些优化技巧是怎么来的？我们是否了解其中真正的原理？这些技巧是否又适合所有场景呢？优化需要注意哪些地方呢？我们来一起探讨下。

二、优化方向

优化有风险，修改需谨慎！！

通常来说，一个业务的稳定和可持续性，比稳定性更重要。

优化如果在一个已经上线很复杂的系统，优化让性能变好的同时，可能会使系统稳定性变差。所以通常来讲优化是由业务需求驱动的。

数据库的优化有两个方向：

• 安全 ：提高数据的稳定性和可持续性
• 性能 ：提高数据访问的高性能性

数据库优化可以从几个维度出发：

• 优化硬件设施
• 优化系统配置
• 优化表结构
• 优化SQL及索引优化

其中后2者是成本最低而且成效最高的。

三、Mysql工作原理

想要优化数据库就要先了解数据的架构和工作原理，这样才能清晰的知道我们应该遵循什么样的规则和优化的方向，才能让数据库更高效顺畅的工作。

首先来看下mysql的架构图：

• 当用户发起请求，服务器端连接线程处理器会为客户端分配一个线程并开辟内存空间，开始提供查询和缓存的机制。服务器会负责缓存线程，所以服务层不需要为每个连接新建线程，提升了连接效率。

再来看一下mysql查询过程：

上图是网上一张较为通用的流程图，看似比较复杂，可以分为6个步骤：

一起来看下每个步骤做了些什么，从中可以有些什么优化。

1）请求查询

通信协议：

客户端与服务端间的通信协议为“半双工”协议：即在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。

1、客户端向服务端传送的packet不能过大，否则会抛异常

2、服务端向客户端传输的所有非常多数据客户端必须完整接收，不能只接收一部分而让服务器停止传输。

优化建议：

查询语句尽量简单，只查询所需要的数据，尽量避免使用select *和加上分页设置limit。减小传输packet的数量和大小。

2）缓存查询

缓存过程：

MySQL将缓存存放在一个引用表，通过一个哈希值索引（通过查询语句本身、查询的数据库、协议版本号等计算）来存放和获取缓存。这个查询缓存,默认是不开启的,因为查询缓存要求SQL和参数都要一样,所以命中率非常低。

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

缓存失效：

查询涉及到的每个表，如果有数据或结构发生变化，那么和这张表相关的所有缓存数据都将失效。

性能消耗：

• 有写操作时，会将关联的表的所有缓存设置为失效，当缓存数据很大时，这个系统消耗将会非常大。
• 打开查询缓存时，任何的查询语句在开始之前都必须经过检查。
• 查询结果可被缓存存入缓存时，也有一定的系统消耗。

优化建议：

缓存收益>性能消耗收益时，才考虑打开缓存。

合理控制缓存大小。

对单个查询语句设置是否缓存，对经常写操作的表不要轻易使用缓存。

批量插入代替循环单条插入

3）解析查询

解析与预处理过程：

如果没有开启查询缓存,或者缓存未命中,那么就到了解析器。

解析器主要对SQL语法进行解析，通过语法规则来验证和解析，比如SQL中关键字是否正确或者关键字的顺序是否正确，解析结束后就变成一颗解析树。

得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...预处理还会进一步检查解析树是否合法，比如检查要查询的数据表和数据列是否存在。

预处理完毕之后就得到一棵初步优化的的树。

4）查询优化

查询优化过程：

我们写一条SQL,比如SELECT * FROM Anchor WHERE uin = 12345678 AND nickname = "主播";

它会怎么去执行?它是先执行uin = 12345678还是nickname = "主播"?

每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,在真正执行SQL之前,MySQL会根据自己数据进行综合的判定,判断此次查询在多种执行方式里面,选哪种执行方式运行最快.它会选择其中成本最小的一个。

平时优化SQL,就是想让查询优化器按照我们的想法,帮我们选择最优最快的执行方案。因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.

MySQL的查询优化器的优化策略：

　　 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

　　 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）

　　提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）

　　优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

从这里可以看出我们可以通过优化表结构，优化索引等来优化查询优化的过程。

5）查询执行

这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行uin = 12345678还是nickname = "主播"

在解析查询和优化后，MySQL会生成对应的执行计划，这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询。

影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是平时熟悉的IO操作,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO就是根据传过来的执行计划去操作的。

6、结果返回

如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。

即使没有数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。

前面提到过，服务器端向客户端返回数据是通过一些数据包的。这些数据包需要符合客户端/服务器通信协议。如果数据量过大，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

四、总结

　了解了mysql的查询过程，可以让我们知道mysql执行过程中时间消耗到了哪里，帮助我们理解一些常用到的优化技巧背后的原理。

未来篇：

mysql学习之优化总结(2)--查询优化

mysql学习之优化总结(3)--解读索引的数据结构和算法，了解索引原理和设计优化

mysql学习之优化总结(4)--优化表的设计