文章目录
1. sql执行生命周期
想要对sql进行一些相关优化,我们需要了解查询sql的整个生命周期,sql的执行流程分成了如下图多个子任务,只要通过降低或者避免其中子任务的耗时,则整个sql性能就能得到提高。
-
客户端连接服务端并发送请求,执行之前还会做权限的判断。
-
在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回。在MySQL内部一个查询缓存,查询缓存默认是不开启的。
因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的。
-
没有开启查询缓存或没有命中,那么就到了解析器,解析器主要对SQL语法进行解析生成解析树。
- 验证sql的语法错误 关键字顺序,别名等sql语法规范。
- 检查数据表和数据列是否存在
- 验证权限
-
得到没有经过优化解析树之后,需要对这棵树进行预处理,得到优化后的解析数。比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等…
-
查询优化器:这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL。该优化器基于最低成本的执行计划。
SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1是先执行username = toby还是password = 1? 每一条SQL的执行,查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,数据量,会根据统计数据,进行一个综合的判定选择一个运行最快查询计划。 最低成本:由于mysql自身的统计信息可能不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但这个方案可能和我们想象的不一样。
-
执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行查询计划(涉及到磁盘IO读取),获取对应的结果并返回。
-
如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。
1.1 客户端/服务端连接
mysql客户端和服务端的连接是”半双工“通信,则每次客户端请求后,需要等待服务端将所有的数据返回给客户端才能结束。
同时每一个客户端与服务端的连接都有如下状态:
- sleep: 服务端线程正在等待客户端发送请求。
- Query: 服务端线程正在执行查询/给客户端发送结果。
- locked: 服务端线程正在等待表锁。
- Analying and Statistics: 正在收集存储引擎的统计信息,并生成查询计划。
- Copying to tmp table [on disk]: 线程在执行查询并将结果复制到临时表中,如果含on disk 则说明在将临时表复制到磁盘中。
- Sorting result: 正在对结果集进行排序。
- Sending Data : 线程在多个状态传输数据、生成结果集、向客户端返回数据。
1.2 查询优化处理
优化器作用
对于一条查询有多种执行方式,优化器作用会基于成本(成本最低)选择一条‘最好’的执行计划。
-- 每当执行一个查询 可以通过查询当前会话的Last_query_cost获取其查询成本
select * from #{tableName} where #{query}
-- 获取当前查询成本
SHOW STATUS LIKE "Last_query_cost"
上述图显示 查询大概需要做12个数据页的随机查找才能完成。
多种原因会导致mysql优化器选择错误的执行计划
统计信息不准确
执行估算成本等于真实的成本、不考虑并发影响
我们期望时间短,优化器考虑成本最优
优化策略
静态优化:简单的代数、常量进行等价转换,编译时优化,一次优化,多次执行。
动态优化:每次执行根据数据量和索引优化执行。
-
重排关联表的顺序。
-
外链接转换为内连接
-
等价变幻 (a=5 and a>5 = a>=5)
-
count()、min()和max() B-tree 可以很快获取到min 和max 通过explain 为"select tables optimized away" 常数计算。
-
转换为常数表达式 explain: type = const
-
覆盖索引
-
自查询优化
-
提前终止查询 比如空结果、limit。
-
等值传播
select a.fid from a inner join b using(fid) where a.fid >500 -- 等值传播 select a.fid from a inner join b where a.fid > 500 and b.fid >500
-
IN()查找 等待于mysql的 多个等值查找 适合列的取值重复。
1.3 统计数据
统计信息由存储引擎实现,mysql生成查询执行计划时候从存储引擎获取统计信息,统计信息包括
-
表/索引占用的页数(页 存储引擎基本单元)
-
表的索引基数
基数又叫索引基数,是数据列所包含的不同值的数量。
例如,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4
-
数据行/索引长度
数据行长度:每行数据长度限制
索引行长度:索引列长度限制
-
索引的分布情况