在了解MySQL查询优化之前，先来看看关于MySQL的执行流程，因为关于数据层优化来说，归根结底还是关于IO的优化，只有知道了关于MySQL的执行流程，我们才能把优化做的更好
MySQL的流程分析
1.连接
1. 客户端发起一条Query请求，监听客户端的‘连接管理模块’接收请求
2. 将请求转发到‘连接进/线程模块’
3. 调用‘用户模块’来进行授权检查
4. 通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求
2.处理
1. 先查询缓存，检查Query语句是否完全匹配，接着再检查是否具有权限，都成功则直接取数据返回
2. 上一步有失败则转交给‘命令解析器’，经过词法分析，语法分析后生成解析树
3. 接下来是预处理阶段，处理解析器无法解决的语义，检查权限等，生成新的解析树
4. 再转交给对应的模块处理
5. 如果是SELECT查询还会经由‘查询优化器’做大量的优化，生成执行计划
6. 模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限
7. 有则调用‘表管理模块’，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件
8. 根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理
9. 上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中
3.结果
1. Query请求完成后，将结果集返回给‘连接进/线程模块’
2. 返回的也可以是相应的状态标识，如成功或失败等
3. ‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接
4:小结
用户模块校验用户,然后去线程连接池拿线程(连接足够的话),找命令分发器,到查询缓存模块查SQL语句,如果没有,走命令解析器,然后访问控制模块,设定用户的权限,设定好后走表管理模块,获取锁和缓存,然后获取各种信息,存储的方式:存储引擎,从存储引擎获取数据,然后返回
一、优化的入手点
查找分析查询慢的原因
- 1.记录慢查询日志（慢查询日志的使用以及分析见本章slow_query.md)
- 2.show profile:
- set profiling = 1;开启，服务器上执行的所有语句会检测消耗的时间，存到临时表
- show profiles 查看语句执行消耗的时间
- show profile for query 临时表ID 查看某个查询的详细消耗
- 3.分析单条语句使用explain（explain.md查看explain用法）
优化查询中的数据访问
- 1.访问数据太多导致查询性能下降，尽量不要使用select *
- 2.确定应用程序逻辑需要的数据量，使用limit返回一部分即可
- 3.确定MySQL是否检索了索引，避免全表扫描
- 4.重复查询相同的数据，可以缓存数据，下次直接读缓存
- 5.是否存在扫描额外的记录（使用explain分析发现需要扫描大量的数据，却只返回少数行）：
使用索引覆盖扫描，把所用到的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
- 优化长难语句
一个复杂的查询和多个简单的查询相比较，mysql每秒可以查询上百万的数据，响应给客户端的速度就要慢得多，所以使用尽可能少的查询是好的，但是有时候把一个大查询分解为多个小的查询也是必要的。因为长时间的sql会产生临时表、锁表、占用数据连接等情况，影响其他的查询。
- 解决方案：
- 1.切分查询，将一条大的查询切分成多个小的查询，分批次执行
- 2.分解关联查询， 将一个关联语句分解成多个sql来执行，减少锁的竞争，并且在应用层进行关联，以后更容易拆分数据库
- 优化特定类型的查询语句
- 1.优化关联查询，确定on的子句有没有索引，避免全表扫描
- 2.确保group by和order by中只有一个表中的列，这样才会使用到索引
- 3.优化子查询，尽量使用关联查询来替代子查询(因为mysql对关联查询会有一些优化器，但是高性能mysql上说了，在mysql5.6以上版本或者MariaDB中，子查询和关联查询的效率是差不多的)
- 4.优化limit分页，当limit偏移量大的时候，查询效率就会很低。此时我们可以记录上次查询的最大id，下次查询时直接根据该id来查询
比如每页需要显示10条数据，到limit 10000,10的时候，其实是查出了10010条结果集，返回了10条。如果我们记住上一次查询的最大id，10000.我们可以使用 where id > 10000 limit 10，这样还是只在10条数据中返回，极大的提升了运行效率
