mysql8 WHERE子句优化（只看收获部分去，其余部分忽略）

10.2.1.1 WHERE子句优化

你可能想重写查询以加快算术运算速度，但这样做会牺牲可读性。因为MySQL会自动执行类似的优化，所以你通常无需进行这些操作，而是保持查询更易理解和维护的形式。MySQL执行的部分优化如下：

• 1. 移除不必要的括号：((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)。
• 2. 常量折叠：(a<b AND b=c) AND a=5 优化为 b>5 AND b=c AND a=5。
• 3. 移除常量条件：(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) 优化为 b=5 OR b=6。在MySQL 8.0.14及更高版本中，此操作在准备阶段而非优化阶段进行，这有助于简化连接操作。更多信息和示例请参见 “mysql8 外连接优化”。
• 4. 索引使用的常量表达式仅计算一次。
• 5. 从MySQL 8.0.16开始，会检查数值类型列与常量值的比较，并对无效或超出范围的值进行折叠或移除。例如：

-- 创建表t
CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
-- 优化前查询
SELECT * FROM t WHERE c < 256; 
-- 优化后查询
SELECT * FROM t WHERE 1;

更多信息请参见10.2.1.14节 “常量折叠优化”。

• 6. 对于MyISAM和MEMORY表，在单表且无WHERE子句的情况下，COUNT(*) 直接从表信息中获取。当仅使用一个表时，任何NOT NULL表达式也会这样处理。
• 7. 尽早检测无效的常量表达式。MySQL能快速检测到某些SELECT语句不可能有结果，并返回空行。
• 8. 如果未使用GROUP BY 或聚合函数（COUNT()、MIN()等），HAVING会与WHERE合并。
• 9. 对于连接中的每个表，都会构建一个更简单的WHERE子句，以便快速评估表的WHERE条件，并尽快跳过不匹配的行。
• 10. 在查询中，所有常量表会在其他表之前先读取。常量表包括以下几种：
  • 空表或只有一行数据的表。
  • 在WHERE子句中，使用PRIMARY KEY 或UNIQUE索引，且所有索引部分都与常量表达式进行比较，并且定义为NOT NULL的表。
    例如：

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

• 11. 通过尝试所有可能的组合，找到连接表的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列都来自同一个表，那么在连接时会优先选择该表。
• 12. 如果存在ORDER BY子句和不同的GROUP BY子句，或者ORDER BY或GROUP BY包含连接队列中第一个表以外的其他表的列，则会创建一个临时表。如果使用SQL_SMALL_RESULT修饰符，MySQL会使用内存临时表。
• 13. 会查询每个表的索引，除非优化器认为使用全表扫描更高效，否则会使用最佳索引。是否使用扫描基于表大小、行数和I/O块大小等更多因素进行估算。
• 14. 在某些情况下，MySQL甚至无需查询数据文件，就能从索引中读取行数据。如果从索引中使用的所有列都是数值类型，那么仅使用索引树就能解析查询。
• 15. 在输出每一行之前，会跳过不匹配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 MAX(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, ... ;

意外收获：
mysql8 支持并行查询，对处理大规模数据和复杂查询有显著提升

-- 设置最大并行线程数
SET GLOBAL max_parallel_workers = 8;
-- 设置每个查询允许使用的最大并行线程数
SET GLOBAL parallel_workers_per_gather = 4;
-- 启用并行查询
SET GLOBAL innodb_parallel_read_threads = 4;

可以使用 EXPLAIN ANALYZE 语句来查看查询是否使用了并行执行

• 11. 通过尝试所有可能的组合，找到连接表的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列都来自同一个表，那么在连接时会优先选择该表。
• 12. 如果存在ORDER BY子句和不同的GROUP BY子句，或者ORDER BY或GROUP BY包含连接队列中第一个表以外的其他表的列，则会创建一个临时表。如果使用SQL_SMALL_RESULT修饰符，MySQL会使用内存临时表。
      避免临时表创建：尽量使ORDER BY和GROUP BY子句中的列来自同一表，以减少临时表的创建。若无法避免，可考虑使用SQL_SMALL_RESULT修饰符，让 MySQL 使用内存临时表，提升性能。