MySQL 索引创建规范

索引创建原则

必要性原则

只为必要的列创建索引：避免为所有列创建索引
基于查询频率：为频繁查询的列创建索引
基于数据分布：为选择性高的列创建索引

选择性原则

高选择性列：选择区分度高的列创建索引

选择性计算：

sql

-- 计算列的选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

选择性阈值：一般选择性大于20%的列适合创建索引

最左前缀原则

组合索引顺序：将选择性高的列放在前面
查询匹配：查询条件要匹配索引的最左前缀
避免索引失效：避免跳过组合索引中的列

最小化原则

索引列长度：使用最小的必要列长度
前缀索引：对长字符串使用前缀索引
索引数量：控制表的索引数量，一般不超过5个

覆盖索引原则

包含查询列：索引包含查询所需的所有列
避免回表：减少回表查询，提高查询性能
适合场景：频繁的列查询和排序操作

索引命名规范

命名格式

索引类型	命名格式	示例
主键索引	PRIMARY	PRIMARY
唯一索引	uk_表名_列名	uk_user_email
普通索引	idx_表名_列名	idx_user_created_at
前缀索引	idx_表名_列名_前缀长度	idx_user_name_10
组合索引	idx_表名_列名1_列名2	idx_user_name_age

命名规则

表名：使用小写字母，单词间用下划线分隔
列名：使用小写字母，单词间用下划线分隔
索引类型：使用标准前缀（uk_、idx_）
长度限制：索引名长度不超过64个字符
一致性：保持命名风格的一致性

示例

sql

-- 主键索引
ALTER TABLE `user` ADD PRIMARY KEY (`id`);

-- 唯一索引
ALTER TABLE `user` ADD UNIQUE KEY `uk_user_email` (`email`);

-- 普通索引
ALTER TABLE `user` ADD INDEX `idx_user_created_at` (`created_at`);

-- 前缀索引
ALTER TABLE `user` ADD INDEX `idx_user_name_10` (`name`(10));

-- 组合索引
ALTER TABLE `user` ADD INDEX `idx_user_name_age` (`name`, `age`);

索引创建规范

主键索引

规范

使用自增ID：优先使用自增整数作为主键
避免复合主键：除非业务需要，否则避免使用复合主键
避免UUID：UUID作为主键会导致索引碎片和性能问题
主键长度：使用最小的必要长度

示例

sql

-- 推荐
CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 不推荐
CREATE TABLE `user` (
  `uuid` VARCHAR(36) NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB;

唯一索引

规范

业务唯一性：用于确保业务逻辑上的唯一性
考虑NULL值：唯一索引允许NULL值
组合唯一索引：适用于多列组合唯一的场景

示例

sql

-- 单列唯一索引
ALTER TABLE `user` ADD UNIQUE KEY `uk_user_email` (`email`);

-- 组合唯一索引
ALTER TABLE `order` ADD UNIQUE KEY `uk_order_user_product` (`user_id`, `product_id`);

普通索引

规范

查询条件：为WHERE子句中频繁使用的列创建索引
排序和分组：为ORDER BY和GROUP BY中的列创建索引
连接条件：为JOIN操作中的连接列创建索引

示例

sql

-- WHERE条件列
ALTER TABLE `user` ADD INDEX `idx_user_status` (`status`);

-- ORDER BY列
ALTER TABLE `user` ADD INDEX `idx_user_created_at` (`created_at`);

-- JOIN连接列
ALTER TABLE `order` ADD INDEX `idx_order_user_id` (`user_id`);

前缀索引

规范

长字符串列：对CHAR、VARCHAR、TEXT类型的长字符串使用前缀索引
前缀长度：选择合适的前缀长度，平衡索引大小和选择性
选择性测试：测试不同前缀长度的选择性

示例

sql

-- 前缀索引创建
ALTER TABLE `user` ADD INDEX `idx_user_name_10` (`name`(10));

-- 测试前缀长度的选择性
SELECT
  COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS sel_5,
  COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(name, 15)) / COUNT(*) AS sel_15
FROM `user`;

组合索引

规范

列顺序：按照选择性从高到低排列
最左前缀：考虑查询的最左前缀匹配
覆盖查询：包含常用的查询列
避免冗余：避免创建与已有索引前缀重复的索引

示例

sql

-- 组合索引创建
ALTER TABLE `user` ADD INDEX `idx_user_name_age` (`name`, `age`);

-- 有效查询（匹配最左前缀）
SELECT * FROM `user` WHERE `name` = 'John';
SELECT * FROM `user` WHERE `name` = 'John' AND `age` = 30;

-- 无效查询（不匹配最左前缀）
SELECT * FROM `user` WHERE `age` = 30; -- 索引失效

索引创建步骤

1. 需求分析

查询分析：分析SQL查询语句
业务场景：了解业务使用场景
数据特征：分析数据分布和增长趋势

2. 选择性评估

计算选择性：

sql

SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

数据分布：

sql

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name ORDER BY COUNT(*) DESC LIMIT 10;

3. 索引设计

类型选择：根据查询类型选择索引类型
列选择：选择合适的列和顺序
命名规范：遵循命名规范

4. 测试验证

创建索引：创建设计的索引
性能测试：测试查询性能

执行计划分析：分析索引使用情况

sql

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

5. 上线监控

监控性能：监控查询性能变化
监控开销：监控索引对写入性能的影响
定期评估：定期评估索引的有效性

索引维护规范

定期审查

审查频率：每季度审查一次索引使用情况
审查工具：使用EXPLAIN分析查询计划
审查内容：
- 未使用的索引
- 重复的索引
- 低效的索引

索引优化

删除未使用的索引：

sql

-- 查找未使用的索引
SELECT * FROM information_schema.statistics WHERE table_schema = 'database_name' AND table_name = 'table_name';

-- 删除未使用的索引
ALTER TABLE `table_name` DROP INDEX `index_name`;

合并重复索引：
- 检查前缀重复的索引
- 合并功能相似的索引
优化索引结构：
- 调整组合索引的列顺序
- 修改前缀索引的长度

索引碎片整理

识别碎片：

sql

-- 检查表的碎片情况
SELECT table_name, data_free, engine FROM information_schema.tables WHERE table_schema = 'database_name' AND data_free > 0;

碎片整理：

sql

-- InnoDB表
ALTER TABLE `table_name` ENGINE=InnoDB;

-- 或使用OPTIMIZE TABLE
OPTIMIZE TABLE `table_name`;

监控指标

索引使用率：
- 监控索引的使用频率
- 识别低效索引
写入性能：
- 监控索引对写入性能的影响
- 平衡读写性能
存储空间：
- 监控索引占用的存储空间
- 控制索引大小

索引使用注意事项

避免索引失效

全表扫描：避免使用SELECT * 查询
函数操作：避免在索引列上使用函数
类型转换：避免索引列的类型转换
不等于操作：!= 或 <> 操作可能导致索引失效
NULL值判断：IS NULL 或 IS NOT NULL 可能导致索引失效
LIKE操作：LIKE '%value' 会导致索引失效
OR条件：多个OR条件可能导致索引失效

优化建议

使用覆盖索引：

sql

-- 推荐：使用覆盖索引
SELECT id, name FROM `user` WHERE name = 'John';

-- 不推荐：SELECT *
SELECT * FROM `user` WHERE name = 'John';

避免函数操作：

sql

-- 不推荐：索引列上使用函数
SELECT * FROM `user` WHERE DATE(created_at) = '2023-01-01';

-- 推荐：使用范围查询
SELECT * FROM `user` WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';

使用合适的查询条件：

sql

-- 不推荐：LIKE '%value'
SELECT * FROM `user` WHERE name LIKE '%John';

-- 推荐：LIKE 'value%'
SELECT * FROM `user` WHERE name LIKE 'John%';

特殊场景处理

大量插入：
- 插入前禁用索引
- 插入后重建索引
大表索引：
- 在线添加索引（MySQL 5.6+）
- 使用pt-online-schema-change工具
分区表索引：
- 分区键包含在索引中
- 考虑本地索引和全局索引

索引案例分析

案例1：用户表索引设计

表结构

sql

CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `age` INT UNSIGNED NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

查询场景

根据email查询用户
根据status查询用户列表，按created_at排序
根据name和age查询用户

索引设计

sql

-- 唯一索引：email
ALTER TABLE `user` ADD UNIQUE KEY `uk_user_email` (`email`);

-- 组合索引：status, created_at
ALTER TABLE `user` ADD INDEX `idx_user_status_created_at` (`status`, `created_at`);

-- 组合索引：name, age
ALTER TABLE `user` ADD INDEX `idx_user_name_age` (`name`, `age`);

案例2：订单表索引设计

表结构

sql

CREATE TABLE `order` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

查询场景

根据user_id查询订单列表，按created_at排序
根据product_id查询订单
根据status和created_at查询订单

索引设计

sql

-- 组合索引：user_id, created_at
ALTER TABLE `order` ADD INDEX `idx_order_user_id_created_at` (`user_id`, `created_at`);

-- 普通索引：product_id
ALTER TABLE `order` ADD INDEX `idx_order_product_id` (`product_id`);

-- 组合索引：status, created_at
ALTER TABLE `order` ADD INDEX `idx_order_status_created_at` (`status`, `created_at`);

索引工具

分析工具

EXPLAIN

功能：分析SQL执行计划

使用：

sql

EXPLAIN SELECT * FROM `user` WHERE `name` = 'John';

输出分析：
- type：访问类型（ALL, index, range, ref, eq_ref, const, system, NULL）
- key：使用的索引
- rows：估计扫描的行数
- Extra：额外信息

SHOW INDEX

功能：查看表的索引信息
使用：
sql
```
SHOW INDEX FROM `user`;
```

INFORMATION_SCHEMA.STATISTICS

功能：查询索引元数据

使用：

sql

SELECT * FROM information_schema.statistics WHERE table_schema = 'database_name' AND table_name = 'table_name';

优化工具

MySQLTuner

功能：分析MySQL配置和索引使用情况

使用：

bash

perl mysqltuner.pl --user=root --pass=password

pt-index-usage

功能：分析索引使用情况

使用：

bash

pt-index-usage --user=root --password=password /var/log/mysql/slow-query.log

pt-duplicate-key-checker

功能：查找重复的索引

使用：

bash

pt-duplicate-key-checker --user=root --password=password

常见问题（FAQ）

Q1: 如何判断一个列是否适合创建索引？

A1: 判断列是否适合创建索引的方法：

选择性分析：

sql

SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

选择性大于20%的列适合创建索引

查询频率：
- 频繁出现在WHERE、ORDER BY、GROUP BY子句中的列
- 频繁作为JOIN条件的列
数据分布：
- 数据分布均匀的列
- 避免为枚举值较少的列创建索引

Q2: 如何选择组合索引的列顺序？

A2: 选择组合索引列顺序的原则：

选择性优先：将选择性高的列放在前面

sql

-- 假设name的选择性高于age
ALTER TABLE `user` ADD INDEX `idx_user_name_age` (`name`, `age`);

查询频率：将频繁使用的列放在前面

排序和分组：将用于排序和分组的列放在后面

sql

-- 用于WHERE和ORDER BY
ALTER TABLE `user` ADD INDEX `idx_user_status_created_at` (`status`, `created_at`);

覆盖查询：包含查询所需的所有列

Q3: 如何处理长字符串列的索引？

A3: 处理长字符串列索引的方法：

使用前缀索引：

sql

-- 对长字符串使用前缀索引
ALTER TABLE `user` ADD INDEX `idx_user_name_10` (`name`(10));

选择合适的前缀长度：
- 测试不同前缀长度的选择性
- 平衡索引大小和选择性

考虑哈希索引：

对完全匹配的场景，可考虑使用哈希列

sql

ALTER TABLE `user` ADD COLUMN `name_hash` BIGINT UNSIGNED GENERATED ALWAYS AS (CRC32(`name`)) STORED;
ALTER TABLE `user` ADD INDEX `idx_user_name_hash` (`name_hash`);

Q4: 索引越多越好吗？

A4: 不是，索引过多会带来以下问题：

写入性能下降：每次写入操作都需要更新索引
存储空间增加：索引会占用额外的存储空间
查询优化器负担：过多的索引会增加查询优化器的选择时间
维护成本增加：索引需要定期维护和优化

建议：

控制表的索引数量，一般不超过5个
定期审查和清理未使用的索引
优先考虑覆盖索引和组合索引

Q5: 如何处理索引失效的情况？

A5: 处理索引失效的方法：

分析执行计划：

sql

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

常见原因和解决方案：
- 函数操作：避免在索引列上使用函数
- 类型转换：确保查询值与列类型一致
- LIKE操作：避免使用LIKE '%value'
- OR条件：考虑使用UNION替代OR
- 最左前缀：遵循组合索引的最左前缀原则
优化查询：
- 重写查询语句
- 调整索引结构
- 使用强制索引（谨慎使用）
sql
```
SELECT * FROM table_name FORCE INDEX (index_name) WHERE column_name = 'value';
```

Q6: 如何监控索引的使用情况？

A6: 监控索引使用情况的方法：

使用Performance Schema：

sql

-- 启用索引使用统计
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE 'wait/%';

-- 查看索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'database_name' AND object_name = 'table_name';

使用慢查询日志：
- 启用慢查询日志
- 分析慢查询中的索引使用情况

使用pt-index-usage：

bash

pt-index-usage --user=root --password=password /var/log/mysql/slow-query.log

定期审查：
- 每季度审查一次索引使用情况
- 删除未使用的索引

Q7: 如何在大表上添加索引？

A7: 在大表上添加索引的方法：

在线添加索引（MySQL 5.6+）：

sql

-- 在线添加索引
ALTER TABLE `large_table` ADD INDEX `idx_large_table_column` (`column_name`) ALGORITHM=INPLACE, LOCK=NONE;

使用pt-online-schema-change：

bash

pt-online-schema-change --alter="ADD INDEX idx_large_table_column (column_name)" --user=root --password=password D=database_name,t=large_table --execute

选择合适的时间：
- 在业务低峰期执行
- 提前通知相关团队
监控执行过程：
- 监控服务器负载
- 监控执行进度

Q8: 如何处理索引碎片？

A8: 处理索引碎片的方法：

识别碎片：

sql

-- 检查表的碎片情况
SELECT table_name, data_free, engine FROM information_schema.tables WHERE table_schema = 'database_name' AND data_free > 0;

重建索引：

sql

-- InnoDB表重建
ALTER TABLE `table_name` ENGINE=InnoDB;

-- 或使用OPTIMIZE TABLE
OPTIMIZE TABLE `table_name`;

定期维护：
- 为经常更新的表定期重建索引
- 监控碎片增长情况
预防措施：
- 使用合适的主键类型
- 避免频繁的删除操作
- 控制索引数量

MySQL 索引创建规范 ​

索引创建原则 ​

必要性原则 ​

选择性原则 ​

最左前缀原则 ​

最小化原则 ​

覆盖索引原则 ​

索引命名规范 ​

命名格式 ​

命名规则 ​

示例 ​

索引创建规范 ​

主键索引 ​

规范 ​

示例 ​

唯一索引 ​

规范 ​

示例 ​

普通索引 ​

规范 ​

示例 ​

前缀索引 ​

规范 ​

示例 ​

组合索引 ​

规范 ​

示例 ​

索引创建步骤 ​

1. 需求分析 ​

2. 选择性评估 ​

3. 索引设计 ​

4. 测试验证 ​

5. 上线监控 ​

索引维护规范 ​

定期审查 ​

索引优化 ​

索引碎片整理 ​

监控指标 ​

索引使用注意事项 ​

避免索引失效 ​

优化建议 ​

特殊场景处理 ​

索引案例分析 ​

案例1：用户表索引设计 ​

表结构 ​

查询场景 ​

索引设计 ​

案例2：订单表索引设计 ​

表结构 ​

查询场景 ​

索引设计 ​

索引工具 ​

分析工具 ​

MySQL 索引创建规范

索引创建原则

必要性原则

选择性原则

最左前缀原则

最小化原则

覆盖索引原则

索引命名规范

命名格式

命名规则

示例

索引创建规范

主键索引

规范

示例

唯一索引

规范

示例

普通索引

规范

示例

前缀索引

规范

示例

组合索引

规范

示例

索引创建步骤

1. 需求分析

2. 选择性评估

3. 索引设计

4. 测试验证

5. 上线监控

索引维护规范

定期审查

索引优化

索引碎片整理

监控指标

索引使用注意事项

避免索引失效

优化建议

特殊场景处理

索引案例分析

案例1：用户表索引设计

表结构

查询场景

索引设计

案例2：订单表索引设计

表结构

查询场景

索引设计

索引工具

分析工具