MySQL5.7优化之SQL语句优化

优化SQL语句

数据库应用的核心逻辑是通过SQL语句执行，无论是直接在命令行接口上执行还是后端API执行。本文所讲的调优方式有助于加快各种MySQL应用的速度，这些方式覆盖了SQL读写数据、一般SQL执行的幕后开销、用于特定场景的操作，例如数据库监控。

优化SELECT语句

SELECT形式的查询语句，执行数据库中的所有查找操作。优化这些语句有最高的优先级，无论是缩短动态的web页面的响应时间，还是缩短生成大的报表的时间。

除了SELECT语句，查询的调优技术也适用于CREATE TABLE … AS，INSERT INTO … SELECT和在DELETE语句中的WHERE子句。这些语句有额外的性能考虑因素，因为他们将写操作与面向读的操作相结合。

优化查询的主要考虑因素是：

  • 使慢的SELECT … WHERE变快，第一件事是检查能否添加索引。在WHERE子句列上创建索引，来加速解析，过滤和获取最终的返回结果。为了避免浪费磁盘空间，构建一个小的索引集来加速你应用中的很多相关查询。 索引对于使用连接或外键特性引用的不同表的查询尤其重要。你可以使用EXPLAIN语句来确定SELECT语句使用的哪种索引。

  • 分离和调优查询的任何部分，例如函数调用，需要花费额外的时间。根据查询的结构，一个函数可能在结果集的每一行调用，甚至在表的每一行调用，极大的降低了效率。

  • 在你的查询中把全表扫描的次数降到最低，尤其是大表。

  • 定期的使用ANALYZE TABLE语句更新表统计信息，目的是让优化器拥有构建高效执行计划的信息。学习指定到每个表的调优技术，索引技术和配置参数。InnoDB和MyISAM有使用指南并且支持高性能查询。

  • 避免将查询转换成难以理解的形式，尤其是在优化器会自动做一样的转换的时候。

  • 如果通过引导不容易解决性能问题，那么通过EXPLAIN计划调查指定查询的内部细节，并且调节索引，WHERE子句，JOIN子句等等。（当你达到一定的专业程度，读每个查询的EXPLAIN计划可能是你做的第一步）

  • 调节应用于MySQL缓存的内存大小和属性。由于InnoDB缓存池，MyISAM key缓存，MySQL查询缓存的充分利用，重复的查询运行的非常快，因为之后的查询从内存返回。

  • 即使一个查询使用内存缓存运行的非常快，你仍然可以进一步优化它，目的是减少缓存占用，使你的应用更具可伸缩性。可伸缩性的意思是让你的应用在没有明显性能下降的情况下能处理更多的同时在线人数，大访问量等等。

  • 处理锁问题，查询的速度可能受同时访问这个表的其他会话影响。

优化WHERE子句

本节讨论用于WHERE子句优化，示例使用SELECT语句，但是相同的优化也可应用于带有WHERE子句的DELETE和UPDATE语句。 注：因为MySQL优化工作持续不断的进行，所以不是所有的MySQL优化都会记录在这。

你可能会牺牲可读性来重写查询使算法计算更快。因为MySQL会自动作类似的优化，你应该避免这种工作，并且让查询保持更好的可读性和可维护性。MySQL执行的优化如下：

  • 去掉不必要的圆括号：
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
  • 常量替换:
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
  • 条件化简：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

  • 被索引使用的常量表达式只计算一次。（这块的意思应该是通过这个常量可过滤掉不满足条件的行）

  • 单表上的没有WHERE条件的COUNT(*)查询直接从MyISAM和MEMORY表的表信息中返回。在单表的情况下，这适用于任何NOT NULL表达式。

  • 早期常量表达式检测。MySQL快速检测不可能的和没有返回的SELECT语句。

  • 如果没有使用GROUP BY或聚合函数（COUNT()，MAX()等）HAVING会和WHERE合并。

  • 对于join中的每张表，简单的WHERE能使WHERE执行得更快和更早的跳过行。

  • 在查询中，所有的常量表比所有其他的表优先读取。常量表满足下面任意一个要求：

    • 空表或者只有一行的表。
    • 在WHERE子句包含主键或唯一索引的表中，所有的索引部分和常量进行比较并且被定义为NOT NULL。

    下面所有的表被作为常量表使用：

    SELECT * FROM t WHERE primary_key=1;

    SELECT * FROM t1,t2
        WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

  • 最好的表连接方式是通过尝试所有的可能找到的。如果在ORDER BY和GROUP BY子句里面的所有的列都来自同一张表，这种表在连接中会作为首选。

  • ORDER BY子句和一个不同的GROUP BY子句，或者ORDER BY子句和GROUP BY子句包含列不是连接队列里面的第一个表，那么会创建临时表。

  • 如果使用了SQL_SAMLL_RESULT修饰符，MySQL使用内存临时表。

  • 每个被查询的表，会使用最好的索引，除非优化器相信表扫描有更好的效率。以前，表扫描基于索引是否跨越30%的表，但是固定的百分比不再决定是否使用索引或表扫描。现在优化器更加复杂了，这种评估基于表大小，表行数，I/O块大小等因素。

  • 在某些情况下，MySQL可以在不访问数据文件的情况下读取索引行。如果索引中的所有的列都是数字，仅使用索引树来解析查询。

  • 行被输出前，会跳过与HAVING子句不匹配的行。

一些查询是非常快的：

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
    WHERE key_part1=constant;

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

MySQL解析下面的查询仅使用索引树，假设索引列是数字：

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
    WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

下面的查询使用索引来检索排好序的行而不用分开排序：

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

（完）