索引使用

索引怎么用才生效：最左前缀法则、常见失效场景、SQL 提示、覆盖索引与前缀索引。

一、验证索引效率

tb_sku 表按 sn 查询，建索引前后对比：

SELECT * FROM tb_sku WHERE sn = '100000003145001';   -- 未建索引：20.78 sec
CREATE INDEX idx_sku_sn ON tb_sku(sn);               -- 为 sn 建索引
SELECT * FROM tb_sku WHERE sn = '100000003145001';   -- 建索引后：0.00 sec

海量数据下，无索引的等值查询只能全表扫描，耗时从「秒级」降到「毫秒级」。

二、最左前缀法则

对联合索引，查询条件必须从索引的最左列开始，并且不跳过中间列；跳过某一列，则该列之后的字段索引失效。

以 idx_user_pro_age_sta(profession, age, status) 为例：

查询条件	type	key_len	结论
`profession = '软件工程' and age = 31 and status = '0'`	ref	54	三列全部命中
`age = 31 and status = '0' and profession = '软件工程'`	ref	54	条件顺序可任意，仍全部命中
`profession = '软件工程' and age = 31`	ref	54	命中前两列
`profession = '软件工程'`	ref	47	只命中第一列
`profession = '软件工程' and age > 30 and status = '0'`	range	49	范围查询右侧的 `status` 失效
`age = 31 and status = '0'`	all	—	跳过最左列，索引失效

三、索引失效场景

场景	原因 / 示例
联合索引范围查询	范围条件（`>`、`<`）右侧的列索引失效，如 `profession='软件工程' and age > 30 and status='0'`
索引列参与运算	在索引列上做函数/运算会失效：`where substring(phone,10,2) = '15'` → 全表
字符串不加引号	字符串字段不加引号会隐式类型转换，索引失效：`where phone = 17799990015` → 全表
头部模糊匹配	`like '%工程'`、`like '%工%'` 失效；`like '软件%'`（尾部模糊）不失效
or 连接的条件	`or` 前后都需要有索引：`where id = 10 or age = 23`，`age` 无索引时整体不走索引（`key = NULL`）；给 `age` 建索引后变为 `index_merge`，`key = PRIMARY,idx_user_age`
数据分布影响	优化器评估「走索引比全表更慢」时就不走索引。如 `phone >= '17799990000'` 命中大部分行 → `type=all`；`phone >= '17799990020'` 命中少量行 → `type=range`
is null / is not null	是否走索引同样取决于数据分布，与字段取值比例有关，两种写法都可能全表

判断依据看 explain 的 type（all 即全表）与 key（NULL 即未用索引）。

四、SQL 提示

人为建议优化器使用 / 忽略某个索引：

-- 建议使用指定索引
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

-- 忽略指定索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

-- 强制使用指定索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

三者都是「提示」，最终是否采用仍由优化器决定（force index 例外，它要求必须使用）。

五、覆盖索引

覆盖索引：查询使用了索引，并且需要返回的列在该索引中都能找到 → 不需要回表，效率更高。

以 idx_user_pro_age_sta(profession, age, status) 为例：

-- Extra: Using where; Using index —— 返回列都在索引中，无需回表
explain select id, profession, age, status from tb_user
where profession = '软件工程' and age = 31 and status = '0';

-- Extra: Using index condition —— 返回的 name 不在索引中，需要回表
explain select id, profession, age, status, name from tb_user
where profession = '软件工程' and age = 31 and status = '0';

explain select * from tb_user
where profession = '软件工程' and age = 31 and status = '0';

尽量用覆盖索引、少用 select *：id 是二级索引叶子节点自带的，所以 select id 也算覆盖。

六、前缀索引

字符串字段（varchar / text）很长时，整列建索引会占用大量空间、浪费磁盘 IO，此时只对前 n 个字符建索引：

CREATE INDEX idx_xxxx ON table_name(column(n));

n 取多少取决于选择性：选择性 = 不重复的索引值（基数）/ 表记录总数，越高越好，唯一索引的选择性为 1。

-- 整列的选择性
select count(distinct email) / count(*) from tb_user;
-- 前 5 个字符的选择性（接近整列即可）
select count(distinct substring(email,1,5)) / count(*) from tb_user;

create index idx_email_5 on tb_user(email(5));
show index from tb_user;   -- Sub_part 列显示前缀长度

查询流程：先在辅助索引 email(5) 上定位到前缀匹配的记录（拿到 id），再回表取出完整行、比较完整值，若有多条前缀相同的记录需要多次回表。

七、单列索引与联合索引

单列索引：一个索引只包含单个列。
联合索引：一个索引包含多个列。

多个查询条件时，建议建联合索引而不是多个单列索引：

-- 已有 idx_user_phone(phone)、idx_user_name(name)
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
-- key = idx_user_phone —— 优化器会评估哪个字段的索引效率更高，选择其一

-- 建联合索引后
create unique index idx_phone_name on tb_user(phone, name);
explain select id, phone, name from tb_user use index(idx_phone_name)
where phone = '17799990010' and name = '韩信';
-- key_len = 249，Extra = Using index（覆盖索引）

多条件联合查询时，MySQL 优化器会评估哪个字段的索引效率更高，选择该索引完成本次查询。