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 子句不匹配的行。

一些查询非常快的示例：

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;

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

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, ... ;