MySQL系列(19): SELECT 查询语句优化

  MySQL 查询是以 SELECT 语句形式执行数据库中的所有查找操作,SELECT 操作在执行的 SQL 语句中占大比例,SELECT 调优可以说是对 SQL 优化的首要任务。

  查询调优除了应用于 SELECT 语句外,还适用于诸如 create table…as select、insert in to…select 和 delete 语句中的 where 子句等构造。这些语句还有额外的性能考虑,因为它们将写操作与面向读的操作结合在了一起。

查询优化考虑因素

  1. SELECT 优化优先考虑索引的使用
    要优化 SELECT … WHERE 查询,首先考虑索引使用是否恰当,或在 WHERE 子句中使用的列上创建索引,以加快评估、过滤和结果的最终检索。为了避免浪费磁盘空间,可以构造一小组索引,以加速应用程序中使用的许多相关查询,尽可能地避免查询中的全表扫描,尤其是对于大表。

    索引对于引用不同表、使用诸如连接和外键特性的查询非常重要。如果索引使用未达预期,可以使用 EXPLAIN 来查看具体那些 SELECT 用了索引,或并调整索引、WHERE 子句、连接子句等来查看查询的内部信息。

  2. 对查询语句进行隔离和调整,例如函数调用,会占用较多的时间。
    根据查询的结构,可以为结果集中的每一行调用一次函数;甚至为表中的每一行调用一次函数,从而大大提高效率。【有疑惑,待细究】

  3. 定期使用 ANALYZE TABLE 语句更新表统计信息到最新,使优化器可以构建更高效的扫许计划。

  4. 了解表所使用的存储引擎的调优技术、索引技术和配置参数。
    InnoDB 和 MyISAM 都有相关的优化指导,可以在查询中实现和提高性能。

  5. 避免难以理解的类型转换查询、尤其是在优化器自动执行某些隐式类型转换的情况下。

  6. 调整 MySQL 用于缓冲区大小和属性。
    通过有效使用 InnoDB 缓冲区、MyISAM key 缓存 和 MySQL 查询缓存,重复的查询运行的更快,因为第二次及以后的时间内从缓存中检索结果。

    即使使用了高速缓存快速运行的查询,仍可进一步优化,以便使用更少的高速缓存,从而使应用程序更具可伸缩性。
    可伸缩性意味着可以处理更多的并发用户,更大的请求等,而不会出现性能大幅下降的性况。

  7. 处理锁的问题,查询速度可能会受到同时访问表的其他会话的影响。

WHERE 子句优化

WHERE 子句的优化,示例使用 SELECT 语句,同样也和达用于 DELETE 和 UPDATE 语句中的 WHERE 子句。

  1. 索引使用常量表达式只计算一次,常量索引是最高效的。

  2. 对于 MyISAM 和 MEMORY 表,在单个表上使用没有 WHERE 的 COUNT(*),可以直接从表的信息中获取该值。
    当只与一个表一起使用时,也可以对任何 NOT NULL 表达式执行此操作。

  3. 早些检查表效的常量表达式。MySQL可以快速检测到不可用的 SELECT 语句,并且不返回任何行。【有疑惑,待细究】

  4. 如果不使用 GROUP BY 或 聚合函数(COUNT(),MIN() 等),HAVING 将与 WHERE 合并。

    实际上 HAVING 常与 GROUP BY 配合使用,若无 GROUP BY 使用了 HAVING,则多此一举。

  5. 对于联接中的每一个表,构造一个更简单的 where 以获得表的快速 where 评估,以尽快跳过行(获取小表)。

    先获取小表(小数据集),再连接大表,尽可能避免笛卡尔乘积。

  6. 首先读取所有常量表,然后再读取查询中的任何其他表。常量表有以下种情况:

    • 空表或只有一行的表。
    • WHERE 子句使用了主键或唯一索引。其中索引列定义为 NOT NULL,索引与常量表达式进行比较。
  7. 尝试所有可能以找到多表连接的最佳组合方式。如果 ORDER BY 和 GROUP BY 子句中的所有列都来自同一个表,则连接首选该表。

  8. 如果存在 ORDER BY 子句和不同的 GROUP BY 子句,或者 ORDER BY 或 GROUP BY 包含来自联接第一个表以外的表的列,则创建临时表。

  9. 查询每个表的索引,并使用最佳索引,除非优化器认为全表扫描更有效。
    根据使用的索引评估检索的行数若是占表的 30% 以上,则可能使用全表扫描。但选择索引还是全表扫描,也不全由这个固定的百分比决定。现在优化器评估非常复杂,还包含了其他因 素,如表大小、行数和 I/O 块大小。

  10. 在某些情况下,MySQL可以从索引中读取行,甚至不需要查询数据文件。(覆盖索引的使用)
    如果使用的索引列都是数字,则只使用索引树来解析查询。

  11. 在输出每一行之前,将跳过与 HAVING 子句不匹配的行。

一些查询非常快的示例:

1
2
3
4
5
6
7
8
9
10
11
12
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;

使用的索引列是数字类型,解析以下查询仅使用索引树:

1
2
3
4
5
6
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;

使用索引来排序检索行:

1
2
3
4
5
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;

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

光星

发布于

2019-03-18

更新于

2023-03-06

许可协议

评论