提到mysql查询优化,很多人脑海里可能会想到NOT NULL、合理索引、不使用select *、合适的数据类型等等,可是这些优化技巧是怎么来的?我们是否了解其中真正的原理?这些技巧是否又适合所有场景呢?优化需要注意哪些地方呢?我们来一起探讨下。
优化有风险,修改需谨慎!!
通常来说,一个业务的稳定和可持续性,比稳定性更重要。
优化如果在一个已经上线很复杂的系统,优化让性能变好的同时,可能会使系统稳定性变差。所以通常来讲优化是由业务需求驱动的。
数据库的优化有两个方向:
数据库优化可以从几个维度出发:
其中后2者是成本最低而且成效最高的。
想要优化数据库就要先了解数据的架构和工作原理,这样才能清晰的知道我们应该遵循什么样的规则和优化的方向,才能让数据库更高效顺畅的工作。
首先来看下mysql的架构图:
再来看一下mysql查询过程:
上图是网上一张较为通用的流程图,看似比较复杂,可以分为6个步骤:
一起来看下每个步骤做了些什么,从中可以有些什么优化。
1)请求查询
通信协议:
客户端与服务端间的通信协议为“半双工”协议:即在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。
1、客户端向服务端传送的packet不能过大,否则会抛异常
2、服务端向客户端传输的所有非常多数据客户端必须完整接收,不能只接收一部分而让服务器停止传输。
优化建议:
查询语句尽量简单,只查询所需要的数据,尽量避免使用select *和加上分页设置limit。减小传输packet的数量和大小。
缓存过程:
MySQL将缓存存放在一个引用表,通过一个哈希值索引(通过查询语句本身、查询的数据库、协议版本号等计算)来存放和获取缓存。这个查询缓存,默认是不开启的,因为查询缓存要求SQL和参数都要一样,所以命中率非常低。
如果查询中包含任何用户自定义函数、存储函数、用户变量、时间变量、临时表、mysql库中的系统表,其查询结果都不会被缓存。
缓存失效:
查询涉及到的每个表,如果有数据或结构发生变化,那么和这张表相关的所有缓存数据都将失效。
性能消耗:
优化建议:
缓存收益>性能消耗收益时,才考虑打开缓存。
合理控制缓存大小。
对单个查询语句设置是否缓存,对经常写操作的表不要轻易使用缓存。
批量插入代替循环单条插入
解析与预处理过程:
如果没有开启查询缓存,或者缓存未命中,那么就到了解析器。
解析器主要对SQL语法进行解析,通过语法规则来验证和解析,比如SQL中关键字是否正确或者关键字的顺序是否正确,解析结束后就变成一颗解析树。
得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...预处理还会进一步检查解析树是否合法,比如检查要查询的数据表和数据列是否存在。
预处理完毕之后就得到一棵初步优化的的树。
查询优化过程:
我们写一条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密集型应用,效率会高很多)
从这里可以看出我们可以通过优化表结构,优化索引等来优化查询优化的过程。
这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行uin = 12345678还是nickname = "主播"
在解析查询和优化后,MySQL会生成对应的执行计划,这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询。
影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是平时熟悉的IO操作,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO就是根据传过来的执行计划去操作的。
如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。
即使没有数据,MySQL仍然会返回这个查询的相关信息,比如该查询影响到的行数以及执行时间等等。
前面提到过,服务器端向客户端返回数据是通过一些数据包的。这些数据包需要符合客户端/服务器通信协议。如果数据量过大,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。
了解了mysql的查询过程,可以让我们知道mysql执行过程中时间消耗到了哪里,帮助我们理解一些常用到的优化技巧背后的原理。
未来篇:
mysql学习之优化总结(2)--查询优化
mysql学习之优化总结(3)--解读索引的数据结构和算法,了解索引原理和设计优化
mysql学习之优化总结(4)--优化表的设计
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。