MySQL 优化 —— WHERE 子句优化

引言

本文翻译自 MySQL 官网:WHERE Clause Optimization

WHERE 子句优化

这一部分我们来讨论对 WHERE 子句的优化处理。本部分的案例都是以 SELECT 语句为例,但这些优化同样适用于 DELETE 和 UPDATE 语句中的 WHERE 子句。

注意

因为对 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
  • 被索引使用的常量表达式只会计算一次
  • 单表的 COUNT(*) ,在没有 WHERE 子句的情况下可以直接从表信息中获得,但只针对 MyISAM 和 MEMORY 存储引擎。对于仅与一个表一起使用的任何NOT NULL表达式,也可以这样做。
  • 早期检测无效的常量表达式。MySQL 会快速地检测到一些根本不可能的 SELECT 语句,并直接返回空的结果集。
  • 如果你不使用 GROUP BY 或聚合函数(COUNT(),MIN()等),HAVING 子句会被合并到 WHERE 中。
  • 对于连接中的每张表,MySQL会构建更简单的 WHERE ,以此来获取每张表的快捷 WHERE 子句处理,同时尽可能快的跳过记录。
  • 所有的常量表会先于其他表的查询。下面所列都是常量表(其实博主认为这里应该翻译为“常量查询”更恰当一些):
  1. 空表或只有一条记录的表。
  2. 使用了 primary key 或者 unique key 作为 WHERE 查询条件的查询,并且条件都是等值判断,且所有索引列都是定义为非空的。(博主:因为主键、唯一键都可能是多列复合,所以这里要求每个索引列都必须的非空)。下面的表都被视为常量表:
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 包含的列不属于连接队列中的第一张表,那么MySQL就会建立一张临时表。
  • 如果你使用 SQL_SMALL_RESULT 修饰符,MySQL 会使用一张内存中的临时表。
  • 每张表的索引都会被查询,并会使用最优索引,除非MySQL优化器认为全表扫描比用索引更高效。曾经,扫描的使用基于是否最优索引覆盖超过了表数据的 30 % ,但是,固定的百分比不再作为使用索引或扫描的决定因素。如今的优化器更加复杂,它对优化的计算基于更多额外的因素,比如表的大小,记录的数量,以及 IO 块的大小。 
  • 有些情况,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, ... ;

总结

从本文可以看出,MySQL官方给出了一些 MySQL 优化器内部对于查询的优化处理,对于开发者的优化建议虽然没有明确指出,但是我们可以通过分析这些来自优化器内部的操作来定制我们的SQL查询。这些知识可以作为我们日后采取更具体优化措施的原理基础。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页