MySQL查询优化学习笔记

概述

大量被问及的性能问题源于低质量查询-原因是返回大量应用实际上不用的数据

理解业务需求,缩小查询数据结果集

  • 仅查询应用所需的行,相应使用WHERE语法
  • 仅查询应用所需的列,避免使用SELECT * ,尽量在SELECT语句中列出列名
  • 避免多次获取相同的数据,使用应用缓冲来临时存取数据
  • 更多在数据库层上进行ORDER BY操作,在SELECT语法中使用ORDER BY,而不是到了应用中再排序

MYSQL查询优化过程

Client -> QueryCache -> Parser -> Reprocessor -> Query Optimizer -> Query Execution Engine -> Storage Engine ->数据

查询的执行路径
  • 查询缓冲:如果启用了QueryCache,Mysql会将第一次查询的结果集和查询文本放入查询缓冲中,如果Mysql未能在QueryCache中找到严格查询(包括类似多一个空格这样子都不算是一个相同的文本)文本,那么就会转到解析器和预处理器,如果能找到,就会直接将对应结果集返回到客户端,而无需继续执行。
  • 解析器:将单个查询分解为多个单词(token),操作符和内建解析树,之后进行Mysql语法验证检查。Mysql语法检查,即检查各种关键词及SQL语句操作符的顺序,如ORDER BY是否在查询语句最后,FROM是否在SELECT之后等等。
  • 预处理器:对权限和语句语义进行检查,检查表是否存在在数据库中,对应表列和别名是否存在等,时间花费不多。
  • 查询优化器:到达查询优化器,说明之前的语法验证都已通过,查询优化器将会从解析吕在,预处理器所篮球的解析树转换为执行计划,过程中将涉及查询重写,静态优化,动态优化等优化操作。同时查询优化器会对各种执行计划进行评估并从中找到最佳方案,查询优化器是一个基于成本的优化器,它会选择成本最低的执行计划。基于成本计算的查询优化器不会考虑QUERYCACHE的效果,它总是认为每次的数据读是指对磁盘IO的操作。
  • 查询执行引擎:引擎在接收了查询被优化后所生成的执行计划,并按其中指示来获取所需结果集。Mysql查询执行引擎会调用存储引擎接口来进行操作,每个执行计算都有这样一个接口实例存在,这些接口API会由MyISAM,InnoDB或其它存储引擎来实现最终交互并将数据返回回查询引擎。
查询优化器的责任和限制
  • 责任

    • 将不是最优的连接类型转换为更优的连接逻辑
    • 重新定义表的连接顺序
    • 减少静态表达式
    • 优化数学运算规则
    • 逻辑短路
    • 最佳索引使用
    • 分组优化
    • 优化聚合函数
  • 限制

    • 非并行查询执行
    • 不考虑查询的并行运行情况
    • 取决于存储引擎参数
    • 最快的查询和最优资源的查询之间的选择
    • 在进行操作成本的计算时,并不常将存储过程和功能所耗成本计算在内

我们可以用好查询优化器以达到性能最大化-查询重写

了解查询状态

查询状态:SHOW FULL PROCESSLIST
  • SLEEP
  • QUERY
  • LOCKED
  • ANALYZING,STATICSITCS
  • COPYING TO TMP TABLE
  • SORTING RESULT
  • SENDING DATA

在进行调优时,尝试执行查询以检查其不同状态,了解执行所处的大致情况

EXPLAIN或EXPLAIN EXTENDED命令的使用

MYSQL执行计划是以树形结构展示的,可以通过EXPLAIN命令进行查看。如果我们的表被重新组织或JOIN连接被进行了优化,MYSQL执行计划将帮助使用这些优化后的结果来重建结构化查询,而通过EXPLAIN和SHOW WARNINGS,我们可以了解到MYSQL真实的执行语句

查看语句执行时间

show status like ‘Last_Query_Cost’;

#####查看查询优化器优化过后的语句
show warnings;我的这个MySQL版本,命令只能在EXPLAIN EXTENDED命令后使用

查询性能调优

查询性能对比
  • 单个复杂查询与多个简单查询

    • 在无索引的情况下,单个复杂查询与多个简单查询之间的优劣不定。
    • 在建有索引的情况下,通过union/union all这些小查询,最终的性能比较单个复杂查询来的好一点
  • INNER JOIN 与 外连接

    • 在INNER JOIN 时,如果结果集是一样的话,相应的执行计划也是相同的,也就是基本上不会有性能差异
    • 在外连接时,表的连接顺序则会对性能有影响,可能通过测试得到最优的表连接顺序
  • 子查询,Exists,表连接

    • 写在存储过程中循环调用的话,明显感觉子查询会快一点
  • 聚合函数

    • 如果仅仅是查找MIN与MAX的话,可以考虑利用ORDER BY 与 LIMIT 1结合的语法
查询性能优化建议
  • 使用EXPLAIN语法检查查询执行计划

    • 查看索引的使用情况
    • 查看行扫描情况
  • 当需要获取唯一一行时,最好使用LIMIT 1语法

    • 对MIN或MAX等聚合计算操作有帮助
  • 避免使用SELECT *

    • 会导致表的全扫描
    • 网络带宽会被浪费
  • 将DELETE,UPDATE或INSERT中的查询语句解构成更小的子查询

  • QUERY CACHE是大小写及空格敏感的

  • WHERE语法中的列加索引

  • 对JOIN的表列加索引

  • 避免使用<>,这会导致索引不被使用

  • 如果不存在重复行数据,使用UNION ALL替代UNION


个人总结

感觉所有的这些优化的建议很多还是建立在索引是否可用的前提下,如果可以利用索引,尽量利用索引。多利用EXPLAIN命令,查看命令执行的情况。一个EXPLAIN基本上可以搞定调优的工作了。

额外的一些查询优化
  • 不仅仅是上述的MIN、MAX不能使用索引,实际上对采用函数处理的字段都不能使用索引,如subStr这样的函数,因此在需要利用函数进行处理时,尽量不要对列进行函数处理。
  • LIKE模糊查询时,对于右模糊“ABC%”是可以使用索引的,而对于“%ABC”、“%ABC%”是无法使用索引进行查询的
  • 对于是数据类型一定要尽量匹配,虽然有些varchar类型是数字,你直接写数字也可以,但这会导致索引无法使用
  • 尽量避免使用or条件,因为条件中只要有一个字段没有索引,这就无法应用索引
Leo wechat
欢迎订阅公众号,建设中!