这篇文章给大家介绍如何优化MySQL查询,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。
解析器和预处理器
一开始,MySQL的解析器将查询语句拆分成一系列指令并从中构建一棵“解析树”。解析器使用MySQL的SQL语法去翻译和验证查询语句。例如,解析器保证了查询中的指令是有效且次序正确,并且会检查那种类似字符串引号未配对的错误。
预处理器则检查构建好的解析树中那些解析器无法处理的语义信息,例如,检查数据表和列是否存在,并且处理字段名称和别名以保证列引用没有歧义。接下来,预处理器会检查权限,通常这会非常快(除非你的服务端有一大堆权限配置)。
查询优化器
经过解析器和预处理器后,解析树就被确定是有效的了,可以被优化器进行处理并最终转变为一个查询计划。一个具有相同结果的查询通常有很多种执行方式,而优化器的职责是找出其中最优的选项。
MySQL使用基于代价估计的优化器,这意味着它视图预测众多执行计划的代价,并选择代价最低的那个。最初的单位成本是随机的4 kb数据页读取,而现在变得更为复杂,包括了如执行,比较条件的代价。可以通过显示Last_query_cost会话变量来查看查询优化器估计查询语句的代价。
显示的 Last_query_cost 意味着优化器估计需要执行对应次数的随机数据页访问才能完成查询。这是基于如下统计估算的结果：
数据表或索引占据的数据页数；
索引的候选值；
数据行、键及键值分布对应的数据长度。
优化器并不会考虑估计内容的缓存——它假设每次都从磁盘 I/O 读取结果。优化器并不是每次都能选择最优的执行计划，原因如下：
统计本身可能是错误的。服务端的统计结果依赖于存储引擎，而存储引擎可能十分准确也可能很不准确。例如，InnoDB 由于其 MVCC 架构，并不保留数据表的准确行数。
估计的代价和实际运行的代价并不等价，因此即便统计是准确的，查询的代价与 MySQL 的估计也会或多或少存在偏差。一个读取更多数据页的查询计划也可能代价更低，例如如果是有序的磁盘 I/O 访问就会更快，又或是结果本身就已经在缓存中。因此，优化器本身并不知道查询会引起多少次 I/O 操作。
MySQL 人为的优化也许与我们期待的不同。我们要的可能是更快的执行时间，而 MySQL 并不是只追求快，它是最求最小化代价。因此，通过代价并不一定科学。
MySQL并不考虑并发中的查询，而这可能会影响查询运行的速度。
MySQL 并不是一直都按代价估计做优化。有时候仅仅是遵循一些规则，例如如果有一个全文匹配条件（MATCH 方法）则使用全文索引。即便是有一个更快的的其他索引和非全文条件查询，MySQL 也不会按更快的方式执行查询。
优化器对于不归它控制的操作的代价并不会考虑，例如执行存储过程或自定义函数。
优化器并不总是能够估计每一个执行计划，有些时候它会忽略一个更优的计划。
MySQL 查询优化器是其中非常复杂的一部分，使用了很多优化方式将查询语句转换成为一个查询执行计划。通常有两种优化方式：静态优化和动态优化。静态优化可以简单地通过检查解析树进行。例如，优化器可以将 WHERE 条件通过数学运算规则转换成一个等式。静态优化与具体的值无关，例如 WHERE条件的常量值。他们执行一次后会一直有效，即便是查询语句使用了不同的值再次执行。可以理解为是“编译时优化”。
相反，动态优化是基于具体的情景的，并依赖于多种因素。例如，WHERE 条件中的值或索引中对应的数据行数。这个过程在每次查询都需要重新估计，可以理解为是“运行时优化”。以下是一些 MySQL 的典型优化方式：
联合查询重新排序：数据表并不一定需要按照查询语句的顺序联合。决定最优的联合查询次序是十分重要的优化。
将外联接转换为内联接：一个外联接并不一定需要按外联接查询。有些因素，例如 WHERE 条件和数据表结构可以将外联接查询等价于内联接。MySQL 可以识别这些情况，并重写联合查询。
如何优化MySQL查询
