mysql - 索引

1 B+树

非叶子节点只存键值（不存数据）： 极大地增加了单个节点能容纳的索引数量，让树变得更“矮胖”，从而减少磁盘 I/O 次数。

数据全在叶子节点： 叶子节点之间通过双向链表连接，非常适合数据库中常见的范围查询（找到起点后，顺着链表往后遍历即可）。

示意图

表有三个字段，c1（主键）、c2（数字号码）、c3（字符）

1.1 对比其他数据结构

• 二叉查找树（BST）： 在极端情况下会退化成链表，查询效率从 O(logN) 变成 O(N)。

• 平衡二叉树（AVL）/ 红黑树： 虽然解决了退化问题，但由于每个节点只能有两个子节点，树的层级会很深。数据

• 库是存在磁盘上的，层级深意味着大量的磁盘 I/O，这是致命的性能瓶颈。

• B 树： 变成了多叉树，层级变浅了。但是它的非叶子节点也存储了数据。这导致一个磁盘页（默认 16KB）能容纳的节点数量变少，树依然会变高。

• Hash： 只支持等值查询（=、IN），不支持范围查询（>、<）。

2 索引分类

2.1 聚簇索引

2.2 二级索引

除了聚簇索引之外的其他索引，都叫二级索引。比如用 ALTER TABLE ADD INDEX(name) 创建的索引。

特点： 二级索引的叶子节点不存储整行数据，而是存储了索引列的值 + 对应的主键值。

示意图

• c2列建索引

2.2.1 回表

• 如果通过 name 字段查询：SELECT * FROM user WHERE name = '张三';

• InnoDB 会先在二级索引的 B+ 树中找到 ‘张三’，拿到它的主键 ID（比如 id=5）。

• 然后再拿着 id=5 去聚簇索引的 B+ 树中重新查找，最后拿到完整数据。

2.2.2 覆盖索引

• 如果执行的 SQL 是：SELECT id, name FROM user WHERE name = '张三';

• 此时，因为 id 和 name 这两个字段的值，已经全部存在于二级索引的叶子节点中了，就不需要再去聚簇索引查了。

2.2.3 联合索引

属于二级索引，多个列建索引

索引优化

索引失效

2.1示例表与数据

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_no VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    dept_id INT NOT NULL,
    salary DECIMAL(10,2),
    hire_date DATE NOT NULL,
    INDEX idx_emp_no (emp_no),
    INDEX idx_name (name),
    INDEX idx_age (age),
    INDEX idx_hire_date (hire_date),
    INDEX idx_dept_age (dept_id, age) -- 复合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id (主键)	emp_no	name	age	dept_id	salary	hire_date
1	E1001	Alice	28	1	8000.00	2020-03-15
2	E1002	Bob	32	2	9500.00	2019-07-22
3	E1003	Charlie	28	1	7800.00	2021-01-10
4	E1004	Diana	35	3	12000.00	2018-11-05
5	E1005	Eve	28	2	8200.00	2022-05-18
6	E1006	Frank	40	1	15000.00	2017-09-30
7	E1007	Grace	32	3	9800.00	2020-12-01
8	E1008	Hank	28	2	7500.00	2023-02-14
9	E1009	Ivy	35	1	11000.00	2019-04-25
10	E1010	Jack	28	3	8500.00	2021-08-08

🔍 场景一：对索引列使用函数或表达式

❌ 失效原因

B+ 树索引叶子节点存储的是列的原始值，并按原始值排序。当在索引列上套用函数或表达式时，MySQL 无法直接用索引树中的原始值进行匹配，必须对每一行数据先计算函数结果，再比较。这等价于全表扫描，优化器会直接放弃索引。

📝 结合数据的3个示例

示例SQL	匹配数据行	失效详细分析
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;	行1(Alice), 行7(Grace)	idx_hire_date 存储的是完整日期如 '2020-03-15'。YEAR() 函数作用在列上，MySQL 必须逐行提取年份再与 2020 比较。B+ 树的日期字典序被破坏，无法定位扫描起点，走全表扫描(type=ALL)。
SELECT * FROM employees WHERE age + 2 = 30;	行1,3,5,8,10 (age=28)	idx_age 叶子节点按 28,32,35,40 排序。age+2 是表达式，优化器无法将 30 反向推导为 age=28 去查索引（除非手动改写）。引擎只能逐行计算 age+2 的值，索引失效。
SELECT * FROM employees WHERE UPPER(name) = 'ALICE';	行1(Alice)	idx_name 存储的是原始大小写 'Alice'。UPPER(name) 强制对每行做大小写转换，B+ 树无法直接匹配 'ALICE'。即使只返回1行，仍需扫描全表计算函数结果。

✅ 优化方案：将函数/表达式移到等号右侧，保持索引列“裸露”。
如：hire_date >= '2020-01-01' AND hire_date < '2021-01-01'、age = 28、name = 'Alice'。

---

🔍 场景二：隐式类型转换（字符串列 vs 数字）

❌ 失效原因

MySQL 在比较不同类型时会自动进行隐式转换。当字符串类型的索引列与数字比较时，MySQL 会将字符串列转换为数字，等价于 CAST(column AS SIGNED) = value。这相当于在索引列上加了函数，B+ 树原始值无法直接匹配，索引失效。
⚠️ 注意：如果是 INT 列与字符串比较（如 age = '28'），MySQL 会将常量字符串转为数字，索引仍可使用。失效仅发生在字符串列被转为数字时。

📝 结合数据的3个示例

示例SQL	匹配数据行	失效详细分析
SELECT * FROM employees WHERE emp_no = 1001;	无匹配（emp_no含字母E）	emp_no 是 VARCHAR，右侧是 INT。MySQL 执行 CAST(emp_no AS SIGNED) = 1001。'E1001' 转数字为 0，所有行转换后都不等于1001。因转换作用在列上，idx_emp_no 失效，全表扫描。
SELECT * FROM employees WHERE name = 0;	行1~10（全部）	字符串列 name 与数字 0 比较。MySQL 将 'Alice'、'Bob' 等全转为数字 0，导致 0=0 全部匹配。转换破坏索引结构，即使返回全表数据，也走 type=ALL。
SELECT * FROM employees WHERE emp_no = TRUE;	无匹配	TRUE 在MySQL中等价于 1。实际执行 CAST(emp_no AS SIGNED) = 1。所有 'E10xx' 转数字均为 0，不匹配。隐式转换发生在索引列，idx_emp_no 无法使用。

✅ 优化方案：严格保持类型一致，字符串必须加引号：emp_no = 'E1001'。

---

🔍 场景三：LIKE 左模糊或全模糊匹配

❌ 失效原因

B+ 树索引按从左到右的字符字典序构建。LIKE 'abc%' 能确定前缀起点，可直接定位索引范围。但 LIKE '%abc' 或 LIKE '%abc%' 无法确定首字符，索引的有序性完全失效，只能逐行扫描判断。

📝 结合数据的3个示例

示例SQL	匹配数据行	失效详细分析
SELECT * FROM employees WHERE name LIKE '%lice';	行1(Alice)	idx_name 叶子节点顺序为 Alice, Bob, Charlie...。%lice 首字符未知，B+ 树无法判断从哪个节点开始扫描。即使只匹配1行，也必须遍历所有叶子节点检查后缀。
SELECT * FROM employees WHERE emp_no LIKE '%100%';	行1(E1001), 行10(E1010)	%100% 属于全模糊。索引树按 E1001, E1002... 排序，中间匹配无法利用前缀跳跃。优化器判定索引扫描成本高于全表，直接 type=ALL。
SELECT * FROM employees WHERE name LIKE '%ob%';	行2(Bob)	同上。虽然 Bob 在索引中排第2位，但 %ob% 无法利用字典序定位。若表有百万行，索引树遍历+回表成本远超顺序全表扫描，优化器主动放弃索引。

✅ 优化方案：尽量使用右模糊 LIKE 'Ali%'；若必须左模糊，可考虑全文索引（FULLTEXT）或 Elasticsearch。

---

🔍 场景四：违反复合索引最左前缀法则

❌ 失效原因

复合索引 idx_dept_age(dept_id, age) 的 B+ 树排序规则是：先按 dept_id 排序，dept_id 相同再按 age 排序。如果查询跳过 dept_id 直接用 age，或 dept_id 使用范围查询，则 age 在索引树中是全局无序的，无法利用索引快速定位。

📝 结合数据的3个示例

示例SQL	匹配数据行	失效详细分析
SELECT * FROM employees WHERE age = 28;	行1,3,5,8,10	索引树叶子节点顺序为：(1,28),(1,28),(2,28),(2,28),(3,28)...。age=28 分散在不同 dept_id 下，全局无序。B+ 树无法直接跳跃到 age=28 的起始位置，idx_dept_age 完全失效。
SELECT * FROM employees WHERE dept_id > 1 AND age = 28;	行5(Eve), 行8(Hank)	dept_id > 1 是范围查询。B+ 树能定位到 dept_id=2 的起点，但范围扫描后，age 的有序性仅在单个 dept_id 内有效。跨部门后 age 无序，因此只有 dept_id 走索引，age 部分失效（key_len 仅包含 dept_id）。
SELECT * FROM employees WHERE age = 32 ORDER BY dept_id;	行2(Bob), 行7(Grace)	过滤条件跳过前导列 dept_id，无法使用索引查找；同时 ORDER BY dept_id 也无法利用该索引排序（因为索引是按 dept→age 排的，不是 age→dept）。最终走全表扫描 + Using filesort。

✅ 优化方案：查询条件必须包含最左列；范围查询尽量放在复合索引最后；或为高频查询单独建 idx_age(age)。

---

🔍 场景五：OR 条件中包含无索引列

❌ 失效原因

OR 本质是多个结果集的并集。如果其中一个条件没有索引，MySQL 必须全表扫描才能评估该条件。既然已经要全表扫描，优化器会认为“顺便把其他条件也一起判断”比“先走索引再合并结果”成本更低，从而整体放弃索引。

📝 结合数据的3个示例

示例SQL	匹配数据行	失效详细分析
SELECT * FROM employees WHERE name = 'Alice' OR salary = 8000.00;	行1(Alice)	name 有索引，但 salary 无索引。评估 salary=8000 必须全表扫描。优化器计算成本：全表扫描1次 vs 索引查name+全表查salary+合并。前者更优，故 idx_name 失效，整体 type=ALL。
SELECT * FROM employees WHERE age = 35 OR emp_no LIKE '%1009';	行4(Diana), 行9(Ivy)	age 有索引，但 emp_no LIKE '%1009' 左模糊失效。两个条件都无法高效定位，优化器直接选择全表扫描。即使 age=35 能走索引，也被 OR 拖累失效。
SELECT * FROM employees WHERE dept_id = 1 OR salary > 10000;	行1,3,6,9,4	假设 dept_id 有索引。salary>10000 无索引需全表。OR 的并集特性导致优化器放弃 dept_id 索引，直接全表过滤。执行计划显示 type=ALL, Using where。

✅ 优化方案：将 OR 改写为 UNION（或 UNION ALL），让每个子查询独立使用索引：

SELECT * FROM employees WHERE name = 'Alice'
UNION
SELECT * FROM employees WHERE salary = 8000.00;

---