SQL查询优化简介


在了解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条数据中返回,极大的提升了运行效率

坚持原创技术分享,您的支持将鼓励我继续创作!