.png)
.png)
本章节，我们聊聊MySQL 8 官网文档中《10.2.1 Optimizing SELECT Statements》
10.2.1.1 WHERE 子句优化
WHERE 子句优化
通过优化 WHERE 条件的判断顺序、利用索引等方式，减少数据扫描量。
-- 优化前（未使用索引）
SELECT * FROM users WHERE age > 18 AND city = '北京';
-- 优化后（为 city 和 age 建立复合索引）
CREATE INDEX idx_age_city ON users(age, city);
SELECT * FROM users WHERE age > 18 AND city = '北京';
10.2.1.2 范围优化
范围优化
针对 WHERE 中的范围条件（如 BETWEEN、>、< 等），利用索引快速定位数据范围，避免全表扫描。
-- 范围查询使用索引
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30';
-- 假设 order_date 已建立索引，MySQL 会直接扫描该时间范围内的索引条目。
10.2.1.3 索引合并优化
索引合并优化
当查询条件涉及多个索引时，合并多个索引的结果集，提高查询效率。
-- 为 name 和 age 分别建立索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
-- 索引合并
SELECT * FROM users WHERE name LIKE '张%' OR age > 30;
-- MySQL 会合并两个索引的查询结果，避免全表扫描。
10.2.1.4 哈希连接优化
哈希连接优化
针对等值连接（如 JOIN ON a = b），通过构建哈希表加速表关联，适用于大表关联场景。
-- 哈希连接（MySQL 自动选择该优化方式）
SELECT u.name, o.order_id FROM users u JOIN orders o ON u.id = o.user_id;
-- 当关联条件为等值连接时，MySQL 可能使用哈希连接优化。
10.2.1.5 存储引擎条件下推优化
这个我们下个章节专门聊聊
存储引擎条件下推优化
将 WHERE 条件下推到存储引擎层处理，减少上层数据传输量。
-- 假设表使用 InnoDB 引擎
SELECT * FROM products WHERE category = '电子产品' AND price < 1000;
-- MySQL 会将条件下推给 InnoDB，直接在存储层过滤数据。
10.2.1.6 索引条件下推优化（ICP）
索引条件下推优化
在索引扫描时先过滤部分条件，减少回表次数。
-- 表结构id, name, age, address（假设 name 有索引）
CREATE INDEX idx_name ON users(name);
-- 未优化时通过 name 索引查 id，再回表查 age；优化后在索引层先过滤 age 条件
SELECT * FROM users WHERE name LIKE '李%' AND age > 40;
10.2.1.7 嵌套循环连接算法
嵌套循环连接算法
一种基础连接算法，通过外层表循环匹配内层表数据，常用于小表关联。
-- 嵌套循环（MySQL 自动选择算法）
SELECT * FROM small_table st JOIN large_table lt ON st.id = lt.small_id;
-- 若 small_table 数据量小，MySQL 会将其作为外层表循环匹配。
10.2.1.8 嵌套连接优化
嵌套连接优化
优化嵌套循环算法的性能，如利用索引减少内层表扫描。
同嵌套循环算法，通过索引优化内层表查询效率。
10.2.1.9 外连接优化
外连接优化
优化 LEFT JOIN/RIGHT JOIN 的执行效率，避免冗余操作。
-- 外连接优化
SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
-- MySQL 会优化外连接的表顺序和条件判断。
10.2.1.10 外连接简化
外连接简化
将复杂外连接转换为更简单的查询结构，减少计算量。
当外连接条件可简化为内连接时，MySQL 会自动优化。
10.2.1.11 多范围读取优化（MRR）
多范围读取优化
按索引顺序读取数据，减少磁盘随机 IO，提升范围查询性能。
-- 假设对 age 建立索引
SELECT * FROM users WHERE age IN (20, 25, 30, 35);
-- MRR 会按索引顺序读取这些 age 对应的行，减少 IO 颠簸。
10.2.1.12 块嵌套循环和批量键访问连接
块嵌套循环和批量键访问连接
批量处理连接数据，减少内层表查询次数（适用于无索引的大表关联）。
-- 当关联表无索引时，MySQL 可能使用块嵌套循环，批量读取外层表数据再匹配内层表。
10.2.1.13 条件过滤
条件过滤
提前过滤不满足条件的数据，减少后续处理量。
在 WHERE 子句中先处理高过滤性条件（如主键 = 1）。
10.2.1.14 常量折叠优化
常量折叠优化
在编译阶段计算常量表达式，避免运行时重复计算。
-- 编译时会计算 100*2=200，而非运行时计算
SELECT * FROM products WHERE price < 100*2;
10.2.1.15 IS NULL 优化
IS NULL 优化
针对 IS NULL 条件，利用索引快速定位空值数据。
-- 为 email 建立索引（允许 NULL）
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email IS NULL;
10.2.1.16 ORDER BY 优化
ORDER BY 优化
利用索引有序性避免额外排序操作（Filesort）。
-- 若 age 有索引，ORDER BY 可直接使用索引顺序
SELECT * FROM users ORDER BY age DESC;
10.2.1.17 GROUP BY 优化
GROUP BY 优化
利用索引优化分组操作，或转换为 ORDER BY 处理。
-- 若 category 有索引，GROUP BY 可直接利用索引分组
SELECT category, COUNT(*) FROM products GROUP BY category;
10.2.1.18 DISTINCT 优化
DISTINCT 优化
利用索引或排序优化去重操作，减少内存消耗。
-- 若 id 有索引，DISTINCT 可通过索引直接去重
SELECT DISTINCT id FROM orders;
10.2.1.19 LIMIT 查询优化
LIMIT 查询优化
优化 LIMIT 子句的执行效率，避免全表扫描后再截断。
-- 大偏移量查询优化（利用索引定位）
SELECT * FROM users ORDER BY id LIMIT 100000, 10;
-- 可通过覆盖索引或子查询优化此类查询。
10.2.1.20 函数调用优化
函数调用优化
减少函数调用对索引的影响，或优化函数计算效率。
-- 避免在索引列上使用函数（会导致索引失效）
-- 优化前
SELECT * FROM users WHERE SUBSTRING(name, 1, 1) = '张';
-- 优化后（建立前缀索引）
CREATE INDEX idx_name_first ON users(SUBSTRING(name, 1, 1));
10.2.1.21 窗口函数优化
窗口函数优化
优化窗口函数的计算方式，减少重复计算。
-- 窗口函数（MySQL 自动优化排序和分组计算）
SELECT id, score, RANK() OVER (ORDER BY score DESC) AS rk FROM students;
10.2.1.22 行构造器表达式优化
行构造器表达式优化
优化多行数据构造时的效率（如 VALUES 列表）。
10.2.1.23 避免全表扫描
避免全表扫描
通过索引、条件过滤等方式，减少或避免全表扫描操作。
为常用查询条件建立索引，如 WHERE 中的高频字段。
评论区