07-索引设计与调优
- 索引设计原则：where/order by/group by 字段优先，选基数大的字段，控制字段长度
- 联合索引字段顺序：等值查询在前，范围查询在后
- 范围查询后面的字段会失效，可以用"枚举字段"替代连续范围查询
- 别乱建索引：区分度低的字段、频繁更新的字段、大文本字段不适合建索引
- 用 EXPLAIN 分析执行计划，重点关注 type、key、rows、Extra
1. 索引怎么设计？
1.1 基本原则
- 围绕查询设计：看 SQL 的
WHERE、ORDER BY、GROUP BY条件，针对性建索引 - 选基数大的字段：值越多（区分度越高），B+ 树的二分查找越高效。性别这种只有两三个值的字段，单独建索引意义不大
- 控制字段长度：tinyint 比 int 小，int 比 varchar 小。索引字段越小，一个页能存的索引条目越多，树越矮
- 联合索引覆盖查询：尽量用 2-3 个联合索引覆盖表的所有查询，别一个查询建一个索引
- 主键用自增：避免页分裂，见 索引原理。千万别用 UUID，随机值会导致频繁分裂
1.2 联合索引的字段顺序
核心原则：等值查询字段在前，范围查询字段在后。
因为范围查询后面的字段就用不上索引了（见 最左前缀匹配规则）。
-- 假设索引是 (a, b, c)
WHERE a=1 AND b=2 AND c=3 -- 三个字段都命中
WHERE a=1 AND b>2 AND c=3 -- a 精确匹配，b 范围扫描，c 逐行过滤
2. 什么时候不该建索引？
索引不是越多越好，这些情况不适合建索引：
| 场景 | 原因 |
|---|---|
| 区分度极低的字段（如性别、状态） | 过滤后还要扫大量数据，不如全表扫描 |
| 频繁更新的字段 | 每次更新都要维护 B+ 树，开销大 |
| 大文本字段（text/blob） | 索引体积太大，可以用前缀索引或全文索引 |
| 查询很少用到的字段 | 索引占空间，还要维护，白费 |
| 表数据量很小（几百条） | 全表扫描比走索引还快 |
经验：线上表的索引数量控制在 5 个以内，太多会影响写入性能。
3. 范围查询怎么处理？
联合索引里，第一个范围查询之后的字段都用不上索引。所以如果业务上需要多个范围条件，要想办法把范围查询转成等值查询。
案例：交友 APP 用户筛选
业务需求：按省份、城市、性别、爱好、性格、年龄筛选用户。
SELECT * FROM user_info
WHERE province='广东' AND city='深圳' AND sex in ('female','male')
AND hobby='游戏' AND age BETWEEN 20 AND 30
ORDER BY score DESC
LIMIT 20;
sex IN ('female', 'male') 的妙处
这里 sex IN ('female', 'male') 等价于不限制性别，但它能让联合索引继续匹配后面的字段。
如果用户不选性别，直接去掉 sex 条件，索引匹配到 city 就断了，后面的 hobby 用不上。但写成 sex IN ('female', 'male')，索引可以继续往后走，hobby 也能命中。
这就是"占位"技巧：联合索引中某个字段不是必选条件时，用 IN (所有可能值) 占位，让索引不中断。类似地，province IN ('广东','北京','上海') 也能解决"不选省份时索引断裂"的问题。
第一版索引：(province, city, sex, hobby, character, age)
问题：age 是范围查询，后面的字段用不上。如果还有 latest_login_time <= 7 这种需求，范围查询后面会全失效。
优化方案：新增枚举字段替代范围查询。
-- 新增字段：最近 7 天是否登录
ALTER TABLE user_info ADD COLUMN login_recent_7 TINYINT DEFAULT 0;
-- 查询改写
SELECT * FROM user_info
WHERE province='广东' AND city='深圳' AND sex='female'
AND hobby='游戏' AND login_recent_7=1 AND age BETWEEN 20 AND 30;
优化后的索引：(province, city, sex, hobby, character, login_recent_7, age)
把 login_recent_7 这个等值条件加进来，range 查询只在最后的 age。
另一种场景：排序优化
SELECT * FROM user_info WHERE sex='female' ORDER BY score DESC LIMIT 20;
sex 区分度太低（只有 male/female），命中索引后还是要扫大量数据再排序。
方案：建 (sex, score) 联合索引，让排序直接利用索引的有序性。
4. 怎么判断索引有没有生效？
用 EXPLAIN 分析执行计划：
EXPLAIN SELECT * FROM user WHERE name = '张三';
重点关注这几列：
| 列名 | 含义 | 关注点 |
|---|---|---|
| type | 访问类型 | const > ref > range > index > ALL（全表扫描） |
| key | 实际使用的索引 | NULL 表示没用索引 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index 是覆盖索引，Using filesort 要优化 |
type 的好坏排序（从好到差）：
system > const > eq_ref > ref > range > index > ALL
一般要求至少到 range，最好到 ref。出现 ALL 就得检查索引了。
5. 常见索引失效场景
| 场景 | 示例 | 为什么失效 |
|---|---|---|
| 对索引字段用函数 | WHERE YEAR(create_time) = 2024 |
破坏了索引的有序性 |
| 对索引字段做运算 | WHERE id + 1 = 10 |
同上 |
| 隐式类型转换 | WHERE phone = 13800138000（phone 是 varchar） |
字符串和数字比较，发生类型转换 |
| LIKE 左模糊 | WHERE name LIKE '%三' |
无法用 B+ 树前缀匹配 |
| OR 条件没全建索引 | WHERE a=1 OR b=2（只有 a 有索引） |
b 没索引导致全表扫描 |
| NOT IN / NOT EXISTS | WHERE id NOT IN (1,2,3) |
通常走全表扫描 |
| 联合索引跳过中间字段 | WHERE a=1 AND c=3（跳过 b） |
不满足最左前缀 |
6. 索引下推（ICP）
MySQL 5.6 引入的优化，把部分过滤条件下推到存储引擎层，减少回表次数。
没有 ICP 时：
- 存储引擎根据索引找到所有匹配的主键
- 回表取完整数据
- Server 层过滤不符合条件的行
有 ICP 时：
- 存储引擎根据索引找到匹配的主键
- 在引擎层就判断其他索引字段是否满足条件
- 只有满足条件的才回表
-- 联合索引 (name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 25;
没有 ICP：所有 name 以"张"开头的都回表，再过滤 age。
有 ICP：在索引层就判断 age=25，不满足的直接跳过，减少回表。
EXPLAIN 的 Extra 里看到 Using index condition 就说明用了索引下推。