MySQL 数据库 9（Day57）
主题：索引查询进阶（等值/范围/运算/函数/隐式转换/模糊查询）、索引条件下推（ICP）、覆盖索引与回表、事务 ACID 特性及并发读现象
结构总览
MySQL 数据库 9
├─ B+树、聚集索引、辅助索引、覆盖索引、回表、联合索引复习
├─ 索引测试：选择性、结果集规模、字段长度与分页
├─ 查询条件对索引的影响：等值、范围、运算、函数、类型转换、LIKE
├─ 索引条件下推（ICP）：索引层先过滤，减少不必要的回表
└─ 事务：ACID、COMMIT/ROLLBACK、隔离级别与并发读现象
关键要点
InnoDB 索引与回表
- B+树非叶子节点主要保存索引键和指针，叶子节点按顺序连接，适合等值、范围和排序查询。
- 聚集索引叶子节点保存完整行；辅助索引叶子节点通常保存索引列和主键值。
- 辅助索引先定位主键，再回到聚集索引取完整记录的过程称为回表。
- 查询所需字段全部被索引覆盖时，可以直接从索引返回结果，称为覆盖索引。
- 联合索引
(name, age, gender)应遵循最左前缀；遇到范围条件后，右侧列通常不能继续缩小索引扫描范围。
索引选择与查询方式
索引不是越多越好。索引会占用磁盘空间，并增加 INSERT/UPDATE/DELETE 的维护成本。选择索引列时应结合非空性、选择性、基数、字段长度、查询频率和数据分布判断。
SELECT name, COUNT(*) AS total
FROM s1
GROUP BY name
ORDER BY total DESC;
SELECT COUNT(DISTINCT name) AS distinct_name_count
FROM s1;
SHOW INDEX FROM s1;全表列表、统计大部分记录或返回 SELECT * 时，索引未必带来收益。应优先考虑分页、字段裁剪和基于主键/唯一索引的范围分页：
SELECT id, name, price
FROM product
WHERE id > 1000
ORDER BY id
LIMIT 20;常见条件与索引效果
| 查询方式 | 通常的索引表现 |
|---|---|
| 高区分度字段等值查询 | 可以显著缩小扫描范围 |
| 命中大量记录的等值查询 | 回表成本高，优化器可能选择全表扫描 |
| 小范围查询 | 通常适合使用索引 |
| 过大范围查询 | 需要扫描大量索引记录，收益可能下降 |
| 索引字段参与运算/函数 | 通常不能直接按原索引值定位 |
| 类型不一致 | 可能发生隐式转换，影响索引使用 |
LIKE 'prefix%' | 通常可以利用索引有序性 |
LIKE '%keyword' / LIKE '%keyword%' | 普通 B+树索引通常无法有效定位 |
-- 不推荐：对索引字段运算
SELECT COUNT(id) FROM s1 WHERE id * 12 = 10000;
-- 改写：把运算放在常量侧
SELECT COUNT(id) FROM s1 WHERE id = 10000 / 12;
-- 不推荐：对日期列做函数
SELECT COUNT(id) FROM s1 WHERE YEAR(created_at) = 2025;
-- 改写为范围条件
SELECT COUNT(id)
FROM s1
WHERE created_at >= '2025-01-01'
AND created_at < '2026-01-01';应尽量保持查询参数类型与列定义一致，例如整数列使用数值参数，字符串列使用字符串参数。最终是否使用索引以及是否值得使用，必须通过 EXPLAIN 或 EXPLAIN ANALYZE 验证。
索引条件下推（ICP）
索引条件下推（Index Condition Pushdown，ICP） 会把能够在索引层判断的条件提前执行。以联合索引 (name, age) 为例：
CREATE INDEX idx_user_name_age ON user(name, age);
SELECT *
FROM user
WHERE name LIKE 'egon%'
AND age = 18;没有 ICP 时，数据库可能先扫描满足 name 的辅助索引记录、回表，再判断 age。使用 ICP 时，age = 18 可以先在索引层过滤，只有更可能满足条件的记录才回表。
| 对比项 | 覆盖索引 | 索引条件下推 |
|---|---|---|
| 是否回表 | 查询字段被覆盖时不需要 | 仍可能需要回表 |
| 主要作用 | 直接从索引返回结果 | 提前过滤，减少回表次数 |
| 常见执行信息 | Using index | Using index condition |
| 适用条件 | 查询所需字段都在索引中 | 查询还需要索引之外的完整字段 |
查看或临时切换 ICP：
SHOW VARIABLES LIKE 'optimizer_switch';
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN SELECT * FROM user WHERE name LIKE 'egon%' AND age = 18;Using index condition 不表示完全不回表；实际收益还取决于扫描范围、过滤比例、返回字段和数据分布。必要时用 EXPLAIN ANALYZE 观察真实执行行数与耗时。
事务与 ACID
事务是一组必须作为整体执行的数据库操作。成功时提交，出现异常时回滚，常用于转账、支付和库存扣减等多语句业务。
| 特性 | 含义 |
|---|---|
| 原子性 Atomicity | 全部成功或全部撤销 |
| 一致性 Consistency | 执行前后满足约束和业务规则 |
| 隔离性 Isolation | 并发事务按隔离规则互不产生不应有的影响 |
| 持久性 Durability | 提交结果在故障恢复后仍应保留 |
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;如果中途失败，应在异常处理路径执行 ROLLBACK，不能只依赖某一条 SQL 的执行结果。
并发读现象与隔离级别
| 现象 | 含义 |
|---|---|
| 脏读 | 读到了其他事务尚未提交、随后可能回滚的数据 |
| 不可重复读 | 同一事务两次读取同一行，期间被其他事务提交修改 |
| 幻读 | 同一事务按相同条件重复查询，结果集出现新增/减少的符合条件记录 |
| 丢失更新 | 并发修改互相覆盖，后提交结果覆盖先提交结果 |
常见隔离级别：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 避免 | 可能 | 可能 |
| REPEATABLE READ | 避免 | 避免 | 需结合快照读/当前读与锁机制分析 |
| SERIALIZABLE | 避免 | 避免 | 避免 |
SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;普通 SELECT 与 SELECT ... FOR UPDATE 的并发行为不同，实验时至少使用两个数据库连接，并记录事务开始、读取、修改和提交的先后顺序。
当日总结
- 二级索引查询完整行通常需要回表，覆盖索引可以避免回表。
- 查询范围、选择性、返回字段和数据分布共同决定索引收益。
- 函数、字段运算、隐式类型转换和前置通配符可能使索引难以有效使用。
- ICP 在索引层提前过滤，减少回表；它不等于覆盖索引。
- 事务通过 ACID 保证多条操作的可靠性，隔离级别影响并发读现象。
- 验证索引和事务行为应结合
EXPLAIN ANALYZE及多连接并发实验。
相关页面
- MySQL 概念页 — 索引进阶、ICP、事务与锁机制总览
- MySQL 数据库 8（Day56） — B+树、选择性/基数、覆盖索引与联合索引前置
- MySQL 数据库 10（Day58） — 事务运行模式、保存点与数据库锁
- MySQL 数据库 11（Day59） — InnoDB 锁算法、死锁与 MVCC