一、索引基础：数据库查询的加速器
1. 什么是索引？
索引是MySQL中用于快速定位数据的一种数据结构，类似于书籍的目录。它通过建立数据与存储位置的映射关系，将随机查询转为有序查找，从而大幅提升查询效率。对于百万级以上的数据表，合理使用索引可使查询速度提升几个数量级。
2. 索引分类与创建示例
| 索引类型 | 特性说明 | 创建示例 |
|---|---|---|
| 主键索引 | 唯一且非空，每表仅一个 | CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(20)); |
| 普通索引 | 无唯一性限制，加速普通列查询 | CREATE INDEX idx_name ON t(name); |
| 唯一索引 | 列值唯一但可为NULL | CREATE UNIQUE INDEX uni_email ON t(email); |
| 全文索引 | 针对文本内容的模糊搜索（需MyISAM/InnoDB引擎支持） | CREATE FULLTEXT INDEX ft_content ON articles(content); |
| 组合索引 | 多列联合索引，遵循最左前缀原则 | ALTER TABLE t ADD INDEX idx_multi (col1, col2); |
二、索引底层数据结构演进：从二叉树到B+树
1. 哈希表：适合等值查询
原理：通过哈希函数计算键值存储位置，拉链法解决冲突。
缺点：不支持范围查询，适合NoSQL场景（如Memcached）。
2. 有序数组：静态数据最优解
原理：数据按序存储，二分查找效率高。
缺点：插入/删除成本高，仅适合静态数据。
3. 二叉搜索树（BST）与平衡二叉树（AVL）
BST问题：极端情况下退化为链表（时间复杂度O(n)）。
AVL优化：通过旋转保持左右子树高度差≤1，但频繁插入时维护成本高。
4. B-Tree与B+Tree：数据库索引的核心
B-Tree特点：多路平衡树，非叶节点存储数据。
B+Tree优化：
非叶节点仅存键值，叶节点双向链表连接。
支持高效范围查询，减少磁盘IO次数。
InnoDB中，3层B+Tree可支撑千万级数据查询。
三、索引使用原则：高效查询的关键
1. 列的离散度：越高越好
公式：
离散度 = COUNT(DISTINCT col) / COUNT(*)示例：
gender列（值0/1）离散度低，不适合单独建索引。
2. 组合索引的最左匹配原则
规则：索引
(a,b,c)生效场景：WHERE a=?
WHERE a=? AND b=?
WHERE a=? AND b=? AND c=?
失效场景：
WHERE b=?（缺少最左字段）
WHERE a=? AND c=?（中间字段断裂）
-- 有效索引使用
EXPLAIN SELECT * FROM user WHERE name='张三' AND phone='13800138000';
-- 无效索引使用（未命中最左字段）
EXPLAIN SELECT * FROM user WHERE phone='13800138000';3. 索引创建最佳实践
推荐操作：
高频查询条件列优先建索引。
使用前缀索引减少存储空间（
ALTER TABLE t ADD INDEX idx (col(10))）。联合索引替代多个单列索引。
避免操作：
在低离散度列（如性别）建索引。
使用函数或表达式操作索引列（
WHERE YEAR(create_time)=2023）。无序值（UUID）作为主键，导致页分裂。
四、索引失效场景：绕过性能陷阱
1. 隐式类型转换
-- 失效：name为字符串类型，与数值比较触发转换
EXPLAIN SELECT * FROM user WHERE name=136;
-- 有效：明确类型匹配
EXPLAIN SELECT * FROM user WHERE name='136';2. 前导通配符模糊查询
-- 失效：以%开头无法使用索引
EXPLAIN SELECT * FROM articles WHERE content LIKE '%数据库%';
-- 有效：前缀匹配可走索引
EXPLAIN SELECT * FROM articles WHERE content LIKE '数据库%';3. 索引列参与计算
-- 失效：对索引列进行运算
EXPLAIN SELECT * FROM orders WHERE total_price+100 > 2000;
-- 优化：将计算移至右侧
EXPLAIN SELECT * FROM orders WHERE total_price > 1900;五、总结：索引优化的核心策略
理解数据结构：B+Tree的存储特性决定索引设计方向。
遵循最左原则：组合索引字段顺序需匹配查询模式。
监控离散度：优先为高区分度列建索引。
规避失效场景：避免函数、隐式转换和前导%模糊查询。
权衡代价：索引会降低写性能，频繁更新的表需谨慎。