索引优化策略一：核心原则与高效索引设计
1 ) 索引列禁止使用表达式或函数
策略核心：在索引列上使用表达式或函数会导致索引失效。
- 典型错误案例：查找
expiry_date列 30 天内过期的数据时，错误写法使索引失效：-- 错误写法（索引失效） SELECT * FROM opportunities WHERE DATE_ADD(expiry_date, INTERVAL 30 DAY) < NOW(); -- 正确改写（利用索引） SELECT * FROM opportunities WHERE expiry_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
技术原理：
- 索引存储原值而非计算值，改写后直接比较原字段方可命中
expiry_date的 B树索引 - B树索引存储原始键值，对列计算会破坏索引的有序性，强制全表扫描。日期列是最易违反此规则的场景，需显式转换计算逻辑
2 ) 前缀索引的权衡应用
核心问题：B树索引的键值大小受限（InnoDB≤767字节，MyISAM≤1000字节），长字符串需用前缀索引缩减空间。
-
创建语法：
CREATE INDEX idx_name_prefix ON table_name (column_name(255)); -- 指定前缀长度 示例: -- 对title列前255字符建索引 CREATE INDEX idx_title_prefix ON films (title(255)); -
关键问题：
- 索引选择性降低：前缀长度过短会导致键值重复率升高。例如：
- 前2字节
AB、BC仅2个唯一值，选择性差 - 前3字节
ABC、ABD等唯一值增多，选择性提升
- 前2字节
- 索引选择性降低：前缀长度过短会导致键值重复率升高。例如：
-
关键权衡：
- 优势：减少索引体积，提升 I/O 效率（尤其
TEXT/VARCHAR列） - 劣势：选择性（Selectivity）降低 → 索引值重复率升高
- 优势：减少索引体积，提升 I/O 效率（尤其
-
优化建议：通过统计不同前缀长度的唯一值比例，平衡索引大小与选择性：
-- 计算选择性语法 SELECT COUNT(DISTINCT LEFT(column_name, 3)) / COUNT(*) AS selectivity_3, COUNT(DISTINCT LEFT(column_name, 5)) / COUNT(*) AS selectivity_5 FROM table_name; -- 示例 SELECT COUNT(DISTINCT LEFT(title, 3)) / COUNT(*) AS selectivity_3, COUNT(DISTINCT LEFT(title, 5)) / COUNT(*) AS selectivity_5 FROM films; -
最佳实践：选择最小前缀长度，使选择性 > 0.3（避免重复值过多），选择性 > 0.3 时通常可接受
3 ) 联合索引列顺序三原则
| 优先级 | 原则 | 说明 |
|---|---|---|
| 1 | 高频查询列优先 | 最左列应覆盖80%查询场景（如user_id） |
| 2 | 高选择性列优先 | 过滤性强的列（如order_id）应前置，减少扫描范围 |
| 3 | 小宽度列优先 | 在选择性相近时，优先整型等小类型（如INT vs VARCHAR(255)） |
联合索引 (col1, col2, col3) 的列顺序至关重要，策略优先级：
- 高频查询列优先：最左列应覆盖高频过滤条件（如
status状态列WHERE status=val），快速过滤大量数据 - 高选择性列优先：高基数（Cardinality）列（如用户ID）靠左（如
user_id>gender） - 小宽度列优先：小类型列（如
INT）靠左减少单页存储量，提升 I/O 效率
-
反例：若将低选择性列（如
gender）置于最左，优化器可能忽略索引 -
错误案例：
CREATE INDEX idx_poor ON orders(status, create_time); -- status选择性低，应避免最左
4 ) 覆盖索引（Covering Index）的高效查询优化
定义：索引包含查询所需全部字段（WHERE/SELECT/ORDER BY/GROUP BY），无需回表查询
-
核心优势：
- I/O 优化：仅读取索引页（体积 << 数据行），减少磁盘访问
- 顺序 I/O 加速：B树索引有序存储，将随机 I/O 转为顺序 I/O（磁盘顺序读写性能提升 10-100 倍）
- 二级索引优化：InnoDB 二级索引叶节点含主键值，覆盖索引避免二次主键查找
-
使用限制：
场景 是否支持 原因 SELECT *❌ 无法覆盖所有字段 内存引擎（MEMORY） ❌ 存储引擎不支持 LIKE '%value%'❌ 存储引擎 API 限制 -- 覆盖索引正例（仅需索引） EXPLAIN SELECT language_id FROM film WHERE film_id = 1; -- Extra: "Using index" -- 反例（需回表） EXPLAIN SELECT * FROM film WHERE language_id = 1; -- Extra: "Using where"- 失效场景：
- 存储引擎不支持（如MEMORY）
- 查询包含
SELECT *或LIKE '%双百分号%' - 索引未包含全部所需列
- 失效场景：
扩展：索引的选择性
在 MySQL 中，索引选择性（Index Selectivity） 是衡量一个索引是否有效的关键指标
选择性越高，表示索引越能有效地过滤数据，从而加快查询速度
- 选择性定义：选择性 = 唯一值数量 / 总行数
- 选择性高：意味着大多数值都不重复，索引过滤能力强
- 选择性低：意味着很多值是重复的，索引过滤能力差
选择性高低对比表
| 指标 | 高选择性 | 低选择性 |
|---|---|---|
| 唯一值数量 | 接近总行数 | 远小于总行数 |
| 举例字段 | 用户邮箱 | 用户性别、状态 |
| 索引效率 | 高 | 低 |
| 优化器倾向 | 优先使用索引 | 可能放弃索引 |
为什么低选择性会导致索引失效？
当你在复合索引的最左列使用了低选择性字段（如状态列只有3个值），MySQL 的优化器可能会认为：
- “与其通过索引一个个查找，还不如直接全表扫描来的快。”
这是因为：
- 低选择性字段的值重复率高，通过索引查找仍然需要访问大量行；
- 索引访问的成本（如回表）可能超过直接全表扫描的成本；
- MySQL 优化器基于成本模型选择最优执行计划，可能会放弃低效索引
示例说明：
-- 假设复合索引为 (status, user_id)
SELECT * FROM users WHERE status = 'active' AND user_id = 123;
- 如果
status字段只有 3 个值（active、inactive、pending），那它就是低选择性字段； - 即便
user_id是高选择性的，优化器也可能因为最左列status的低选择性而跳过整个索引； - 如果反过来，索引是
(user_id, status)，因为user_id是高选择性，索引很可能被正常使用。
补充说明：如何提高低选择性字段的索引效率？
你可以通过以下方式优化：
结论
低选择性 指的是字段中不同值较少、重复率高的情况。在 MySQL 中，这种字段如果放在复合索引的最左列，可能导致优化器放弃使用索引，从而引发性能问题。因此，设计索引时应优先考虑将高选择性字段放在前列。
索引优化策略二：高级应用与查询加速
1 ) 利用索引优化排序（ORDER BY）
生效条件：
- 索引列顺序/升降序与
ORDER BY完全一致。 - 排序字段均属关联表的第一张表（多表 JOIN 时）。
- 最左列禁用范围查询（否则右侧列索引失效）。
- InnoDB vs MyISAM 差异：
- InnoDB表主键天然支持排序
- MyISAM需显式创建索引
联合索引排序示例：-- InnoDB 主键索引天然支持排序 EXPLAIN SELECT * FROM rental ORDER BY rental_id; -- Type: index (索引扫描排序) -- MyISAM 需显式索引支持 EXPLAIN SELECT * FROM rental_myisam ORDER BY return_date; -- Extra: "Using filesort"（文件排序）CREATE INDEX idx_rental ON rental (return_date, inventory_id, customer_id); -- 有效排序（索引顺序匹配） EXPLAIN SELECT * FROM rental WHERE return_date = '2005-05-24' ORDER BY inventory_id, customer_id; -- Extra: NULL（无 filesort） -- 失效案例（升降序冲突） EXPLAIN SELECT * FROM rental ORDER BY return_date DESC, inventory_id ASC; -- Extra: "Using filesort"
强调：B树索引天然有序，可替代 ORDER BY 的文件排序：
-- 使用索引 (return_date, inventory_id, customer_id) 排序
SELECT * FROM rental
WHERE return_date = '2005-05-24'
ORDER BY return_date, inventory_id, customer_id;
严格条件：
- 索引列顺序、升降序（ASC/DESC）必须与
ORDER BY完全一致 - 范围查询（如
return_date > '2005-05-24'）会使右侧索引列失效
有效排序（利用联合索引 (return_date, customer_id)）：
SELECT * FROM rental
WHERE return_date = '2005-05-09'
ORDER BY return_date ASC, customer_id ASC; -- Using index
失效场景：
ORDER BY return_date DESC, customer_id ASC; -- 方向不一致 → Using filesort
WHERE return_date > '2005-05-09'
ORDER BY customer_id; -- 最左列范围查询 → Using filesort
2 ) B树索引模拟哈希索引
适用场景：长字符串精确匹配（当哈希索引不可用时）
步骤：
- 新增哈希值列（如 MD5）并建索引。
- 查询时双重校验（哈希列 + 原字段），避免哈希冲突
-
SQL 实现：
-- 1. 添加哈希列 ALTER TABLE film ADD COLUMN title_md5 CHAR(32); UPDATE film SET title_md5 = MD5(title); -- 2. 创建 B树索引 CREATE INDEX idx_title_md5 ON film (title_md5); -- 3. 查询（双重过滤防冲突） SELECT * FROM film WHERE title_md5 = MD5('ACADEMY DINOSAUR') AND title = 'ACADEMY DINOSAUR'; -
哈希函数选择：
- 推荐：
MD5（128位）、SHA1（160位） - 避免
CRC32（冲突率高）
- 推荐：
-
注意事项：
- 哈希函数需生成定长值（如MD5、SHA1）
- 强制内存过滤：哈希冲突时需用原字段二次校验
3 ) 覆盖索引的存储引擎差异
| 存储引擎 | 二级索引结构 | 覆盖索引收益 |
|---|---|---|
| InnoDB | 叶节点存主键值 | 避免主键二次查找 |
| MyISAM | 叶节点存数据行物理地址 | 避免系统调用（更高） |
-- MyISAM 覆盖索引避免系统调用
EXPLAIN SELECT actor_id, last_name FROM actor_myisam
WHERE last_name = 'WAHLBERG'; -- 直接索引返回数据
索引优化策略（下）：锁、冗余与维护
1 ） 索引优化行级锁（InnoDB）
InnoDB锁优化：
- 索引未命中时锁全表（例：
last_name无索引时SELECT ... FOR UPDATE锁所有行） - 索引命中后仅锁筛选行，提升并发性
机制：索引帮助存储引擎层过滤无效行，减少锁定范围
- 无索引时的锁问题：
-- Session 1（无索引，全表锁） BEGIN; SELECT * FROM actor WHERE last_name = 'GUINESS' FOR UPDATE; -- 锁全表 阻塞其他会话 -- Session 2（被阻塞） BEGIN; SELECT * FROM actor WHERE last_name = 'WAHLBERG' FOR UPDATE; -- 等待锁释放 - 索引优化后：
CREATE INDEX idx_last_name ON actor (last_name); -- Session 1（仅锁目标行） SELECT ... WHERE last_name = 'GUINESS' FOR UPDATE; -- Session 2（立即执行） SELECT ... WHERE last_name = 'WAHLBERG' FOR UPDATE;
结论：索引将锁粒度从表级降至行级，提升并发性能
2 ) 冗余与重复索引处理
-
重复索引：同一列建立等效索引（如
PRIMARY KEY+UNIQUE）-- 错误案例（ID 列重复） CREATE TABLE t ( id INT PRIMARY KEY, UNIQUE (id) -- 冗余！主键已是唯一索引 ); -
冗余索引：联合索引包含前缀列索引（如
(a)+(a,b)）- 检测工具：
pt-duplicate-key-checker（Percona Toolkit）
输出示例：pt-duplicate-key-checker --database=sakilaTable `sakila.payment`: INDEX `idx_customer_id` (customer_id) -- 冗余，可被联合索引覆盖 INDEX `idx_customer_staff` (customer_id, staff_id) # Key idx_customer_id on payment is redundant to idx_customer_staff - DROP INDEX idx_customer_id ON payment; payment表:idx_customer_id可被联合索引(customer_id, staff_id)替代，建议删除
- 检测工具：
-
例外场景：联合索引过大时，可保留高频单列索引提升性能
-
清理策略：定期检查未使用索引：
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'sakila';
3 ) 索引统计与碎片维护
统计信息更新：优化器依赖统计信息选择索引
-- 更新表统计信息（InnoDB/MyISAM）
ANALYZE TABLE payment;
| 存储引擎 | 统计存储位置 | 更新成本 |
|---|---|---|
| MyISAM | 磁盘 | 高（全索引扫描） |
| InnoDB | 内存 | 低（随机采样） |
引擎差异：
- MyISAM：全索引扫描，锁表代价高
- InnoDB：随机采样评估，效率高但可能不精确
索引碎片维护：
-- 重建表与索引（消除碎片）
OPTIMIZE TABLE payment; -- 锁表操作，需在维护窗口执行
注意：OPTIMIZE TABLE 会锁表，需在维护窗口执行
碎片影响：
- 物理存储无序 → 随机 I/O 增加
- 页填充率下降 → 内存利用率降低
关键总结
- 索引失效陷阱：表达式计算、函数调用、前缀索引过短
- 覆盖索引本质：用空间换时间，避免回表与随机I/O
- 联合索引顺序：高频 > 高选择性 > 小宽度
- 锁优化核心：索引将锁粒度最小化，提升并发吞吐量
- 维护必做项：定期清理冗余索引、更新统计信息、整理碎片
原生SQL示例
/* 覆盖索引优化 */
CREATE INDEX idx_covering ON rental (return_date, customer_id, staff_id);
EXPLAIN
SELECT return_date, customer_id
FROM rental
WHERE return_date = '2005-05-09'
ORDER BY customer_id; -- Using index
NestJS 集成示例（
1 ) TypeORM 索引管理
// film.entity.ts
import { Entity, Column, PrimaryColumn, Index } from 'typeorm';
@Entity()
@Index('idx_title_md5', ['titleMd5']) // 前缀索引
@Index('idx_language', ['languageId']) // 单列索引
@Index('idx_rental', ['returnDate', 'inventoryId', 'customerId']) // 联合索引
export class Film {
@PrimaryColumn()
film_id: number;
@Column({ length: 255 })
title: string;
@Column({ name: 'title_md5', length: 32 })
titleMd5: string; // 哈希索引模拟
@Column()
languageId: number;
}
// 更新统计信息（通过原生查询）
import { getManager } from 'typeorm';
async function analyzeTable() {
await getManager().query('ANALYZE TABLE film;');
}
2 ) 定义和使用索引
// 实体定义（TypeORM）
@Entity()
export class Film {
@PrimaryGeneratedColumn()
id: number;
@Column({ length: 255 })
title: string;
@Column({ name: 'title_md5', length: 32 })
titleMd5: string; // 哈希索引模拟
@Index('idx_language_id')
@Column()
languageId: number;
}
// 查询服务
@Injectable()
export class FilmService {
constructor(@InjectRepository(Film) private filmRepository: Repository<Film>) {}
async findFilmByTitle(title: string): Promise<Film> {
const titleMd5 = createHash('md5').update(title).digest('hex');
return this.filmRepository.findOne({
where: { titleMd5, title },
}); // 利用覆盖索引
}
}
总结要点（合并至 5 点内）：
- 索引设计三原则：避表达式、前缀长度权衡、联合索引按选择性排序
- 索引列禁用函数是基础规范，日期计算需重写表达式
- 前缀索引需平衡长度与选择性，避免
SELECTivity < 0.8 - 联合索引顺序遵循“高频→高选择性→小宽度”原则
- 覆盖索引是性能加速器，但需避免
SELECT *和LIKE '%...'
- 覆盖索引核心价值：减少 I/O、避免回表、加速排序分组
- 高级策略：B树模拟哈希索引、索引排序条件匹配、锁范围缩减
- 冗余处理：删除重复索引，权衡保留高频单列索引
- 维护关键：定期更新统计信息 (
ANALYZE TABLE)、消除碎片 (OPTIMIZE TABLE)
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
