8.2.1 优化select 语句

8.2 优化SQL 语句

8.2.1 优化 select 语句

查询,表现为 select语句，完成了数据库中的所有查找操作。
调整这些语句具有高优先级，无论是为了给动态页面提供亚秒级的响应还是缩短产生大量夜间报表的时间。
除了select 语句，调整的技巧同样适用于 create table ... as select，insert into ...select ,和 delete 语句中的where 子句,
这些语句需要额外的性能考虑，因为它们结合了面向读的查询操作和写操作。

优化查询的主要考虑因素有:

• 为了使 慢的 select ...where 查询变快,第一件事是检查是否能添加索引。为where 子句中使用的列添加索引来加快计算，过滤和取结果。为避免浪费磁盘空间,创建一小部分索引来加速一些相关查询。
  索引在那些使用 joins 和 foreign keys 中用不同表的查询中尤其重要。你可以用 explain 语句来确定select 中用到了哪些索引。参看 8.3.1 MySQL 如何使用索引和 8.8.1 用explain 来优化查询。
• 分离并优化查询中的任一部分，像函数调用，会耗费多余的时间。由于查询语句组织的不同，一个函数，可能结果集的每一行都会调用，甚至表中的每一行都会调用， 低效会很大程序的放大。
• 减少查询中的 full table scans, 尤其是在大表中。
• 周期性地使用analyze table 语句来使表的统计信息保持更新,这样优化器就能又足够的信息来构建一个高效的执行计划。
• 学习调整的技术，索引的技术，对特定存储引擎的配置参数。

8.2.1.1 where 子句的优化

本节讨论能用在where 子句中的优化。例子是使用select 语句,但同样的优化也适用于delete 和update 中的where 子句。
注意：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
• 索引中使用的常量表达式只会计算一次。 ？？？
• MyISAM 和内存表里没有where 子句的 count(*)直接从 表信息中获取。对于单个表的NOT NULL表达式也是如此。 * 不满足的表达式提前检测。 MySQL 快速检测select 语句中 不可能的条件返回空行。
• having 和 where 合并，如果没有使用group by 或聚合函数(count(),min()等)
• 对join 中的每个表,构建了一个简单的where 语句来获得快速的where 计算以及尽可能跳过无用行。
• 所有的常量表最先读取。
  * 常量表的定义
  1. 空表或只返回一行结果的表.
  2. where 中使用 primary key 或unique index 查询的表，其中索引查询条件是常量表达式并且NOT NULL。
  下列表被视为常量表:
  SELECT * FROM t WHERE primary_key=1;
  SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
• 尝试所有的可能找出最好的join 结合策略。如果在order by 和 group by 中的所有列都来自同一张表,
  那张表会优先join。
• 如果有 order by 和不同的 group by 子句, 或者 order by 和 group by 包含 join 队列中第一张表中之外的列, 就会建立一个临时表。
• 如果使用 SQL_SMALL_RESULT 修饰符，MySQL 使用 内存临时表。 如果是 SQL_BIG_RESULT, 使用磁盘临时表。针对group by 或 order by 排序的时候。
• 每个表索引都会被查询,最优的索引会被使用，除非 优化器认为table scan 更有效。每次，根据最优索引是否超过了表的30%来决定是否采用scan,但也不是确定的。优化器现在更复杂了，还会根据表大小，行数，IO块等作判断。
• 在有些情形下，MySQL能从索引直接读取数据神主不用data file。 如果索引中使用的所有列都是数值，那只用索引树就能搞定查询。
• 在每行输出之前,那些不满足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, ... ;