查询优化与执行
查询优化器是数据库的大脑，它将声明式的 SQL 语句转换成高效的执行计划。理解优化器的工作原理，有助于写出更高效的查询，也有助于调优数据库配置。
查询处理流程
解析与预检查
解析器将 SQL 文本解析成抽象语法树（AST），这一步只检查语法正确性，不关心语义。预处理器进行语义分析：表是否存在、列是否存在、是否有权限等。预处理还会展开视图，将视图定义替换到查询中，处理子查询嵌套。
逻辑优化
逻辑优化应用基于规则的转换（RBO），目标是简化查询、消除冗余。常见的优化规则包括：
列裁剪：删除查询中不需要的列。例如 SELECT * 但只使用部分列，优化器会将 * 展开为实际需要的列，避免读取不必要的列。
谓词下推：将过滤条件下推到最接近数据源的位置。例如 JOIN 前先过滤，减少 JOIN 的数据量。子查询中的条件下推到子查询内部。
常量传递：利用已知条件推导新的常量条件。例如 WHERE a = 1 AND b = a，推导出 b = 1。
表达式简化：简化常量表达式，如 1 + 2 → 3，a AND TRUE → a。
子查询展开：将某些子查询转换为 JOIN。例如 WHERE id IN (SELECT id FROM t) 转换为 JOIN。
外连接消除：如果外连接的右侧表没有在 SELECT 或 WHERE 中使用，且没有 UNIQUE/PRIMARY KEY 约束，外连接可以转换为内连接。
物理优化
物理优化选择每个操作的实现方式，基于成本估算（CBO）。优化器需要考虑：
访问方法：全表扫描、索引扫描、覆盖索引扫描、索引查找。全表扫描顺序读取所有数据页，适合扫描大量数据；索引扫描先定位到索引范围，再回表查询；覆盖索引扫描只读索引，不回表；索引查找通过主键或唯一索引定位单条记录。
连接算法：嵌套循环连接（Nested Loop Join）、块嵌套循环连接（Block Nested Loop Join）、索引嵌套循环连接（Index Nested Loop Join）、哈希连接（Hash Join）。嵌套循环连接对任意连接条件都有效，但复杂度 O(m×n)；块嵌套循环连接使用 join buffer 缓存外层表的数据，减少内层表扫描次数；索引嵌套循环连接使用内层表的索引加速查找；哈希连接构建哈希表，适合等值连接，复杂度 O(m + n)。
连接顺序：多表连接有多种顺序，优化器需要选择一个代价最小的顺序。n 个表连接有 n! 种顺序，穷举不现实。优化器使用动态规划或贪心算法，考虑统计信息（行数、索引选择性）估算每种顺序的代价。
统计信息
统计信息是 CBO 的基础，包括表的行数、列的唯一值数量、列的数据分布（直方图）、索引的高度和叶子页数等。统计信息通过 ANALYZE TABLE 收集，定期更新。
行数估算影响连接顺序：小表驱动大表通常更高效。唯一值数量影响选择性：高选择性的列（如主键）适合索引查找。直方图影响范围查询的行数估算：均匀分布的列和非均匀分布的列，估算策略不同。
统计信息不准确会导致优化器选择错误的执行计划，例如误判索引扫描优于全表扫描，实际索引扫描需要大量回表。定期收集统计信息、配置直方图桶数，可以提高估算准确性。
EXPLAIN 执行计划
EXPLAIN 显示优化器选择的执行计划，是调优的利器。关键字段包括：
id：查询序列号，表示 SELECT 的执行顺序。id 相同时从上到下执行，id 不同时 id 大的先执行（子查询）。
select_type：查询类型，SIMPLE（简单查询）、PRIMARY（最外层查询）、SUBQUERY（子查询）、DERIVED（派生表）、UNION（联合查询）。
type：访问类型，性能从好到差：NULL（不访问表，如 SELECT 1）、const（主键或唯一索引等值查询，返回单行）、eq_ref（连接时使用主键或唯一索引，每行匹配单行）、ref（非唯一索引等值查询，可能匹配多行）、range（范围扫描）、index（索引扫描，通常遍历索引树）、ALL（全表扫描）。
possible_keys：可能使用的索引。
key：实际使用的索引。
key_len：使用的索引字节数，判断是否使用了复合索引的全部列。
ref：索引比较的列，通常是常量或某个表的列。
rows：估算扫描的行数，越小越好。
Extra：额外信息。Using index（覆盖索引，无需回表）、Using filesort（需要文件排序，通常需要优化）、Using temporary（使用临时表，通常需要优化）、Using where（使用 WHERE 过滤）、Using index condition（索引下推）。
优化策略
索引优化
最左前缀原则：复合索引 (A, B, C) 支持查询条件 A、AB、ABC，不支持 B、BC、C。这是由于 B+ 树的有序性是按索引列顺序建立的。
覆盖索引：索引包含查询所需的所有列，避免回表。例如索引 (name, age)，SELECT age FROM t WHERE name = 'xxx' 可以使用覆盖索引。
索引下推：MySQL 5.6+ 支持，将 WHERE 条件下推到存储引擎，在索引扫描时过滤，减少回表次数。例如索引 (name, age)，查询 WHERE name LIKE '张%' AND age > 18，存储引擎可以用索引过滤 age > 18。
避免索引失效：索引列上使用函数、类型转换、OR 条件（除非两边都有索引）、LIKE 前缀通配符、IS NULL（某些情况），都可能导致索引失效。
SQL 写法优化
避免 SELECT *：只查询需要的列，减少网络传输和内存占用，也可能使用覆盖索引。
分页优化：大偏移量的分页（LIMIT 1000000, 10）性能差，因为需要扫描 1000010 行。优化方案：记住上一页的最大 ID，WHERE id > last_id LIMIT 10；或者使用延迟关联，先通过覆盖索引定位 ID，再 JOIN 查询完整记录。
子查询优化：某些子查询可以转换为 JOIN，性能更好。IN 子查询如果外层表大、内层表小，可以转换为 EXISTS，或者让优化器自动转换。
批量操作：单条 INSERT 插入多行，或使用 LOAD DATA INFILE，比多条 INSERT 高效。
表设计优化
垂直分表：将不常用或大字段分离到另一张表，减少主表的行大小，提高缓存命中率。
水平分表：按时间、ID 哈希等规则拆分大表，减少单表数据量。分表后需要应用层路由。
适当的数据类型：能用 TINYINT 就不用 INT，能用 VARCHAR 就不用 TEXT，减少存储空间和内存占用。
字符集选择：UTF8MB4 支持 emoji，但比 UTF8 和 LATIN1 占用更多空间。根据业务需求选择。
查询优化是一个系统工程，需要结合索引优化、SQL 写法优化、表设计优化、数据库配置优化。EXPLAIN 是起点，慢查询日志是目标，逐步逼近最优执行计划。