MySQL索引设计最佳实践

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、索引设计的基本原则

#

1.1 三星索引法则

《高性能MySQL》提出的三星索引标准：

• 第一星：索引将相关的记录放在一起（减少IO）
• 第二星：索引中的列顺序满足排序需求（避免文件排序）
• 第三星：索引包含查询所需的所有列（覆盖索引，避免回表）

#

1.2 索引选择性（Cardinality）

选择性 = 不同值数量 / 总记录数，越接近1越好：

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

建议：选择性低于0.1的列，单独建索引效果不佳。

#

1.3 索引长度估算

-- 查看索引大小
SELECT 
    database_name,
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS index_size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'your_table';

二、单列索引设计

#

2.1 主键索引

设计要点：

1. 使用自增整数

   CREATE TABLE user (
       id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       -- ...
   ) ENGINE=InnoDB;

2. 避免使用业务字段作为主键

   • 手机号可能变更
   • 身份证号涉及隐私
   • UUID随机插入性能差

3. 主键不宜过长

   • 主键会复制到所有二级索引中
   • 主键越长，二级索引越大

#

2.2 唯一索引

确保业务唯一性的同时建立索引：

-- 方式一：UNIQUE约束
CREATE TABLE user (
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uk_email (email)
);

-- 方式二：唯一索引
CREATE UNIQUE INDEX uk_phone ON user(phone);

注意：唯一索引不能包含NULL（NULL不等于NULL）。

#

2.3 普通索引

为高频查询条件列创建：

CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

三、联合索引设计

#

3.1 最左前缀原则

联合索引(a, b, c)等效于创建了(a)、(a,b)、(a,b,c)三个索引：

-- 有效：用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3   -- 只用a

-- 无效：跳过最左列
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3

#

3.2 联合索引列顺序设计

原则一：等值查询列在前，范围查询列在后

-- 查询条件
WHERE a = 1 AND b > 10 AND c = 5

-- 推荐索引：(c, a, b) 或 (a, c, b)
-- 不推荐：(a, b, c)，因为b是范围查询，c无法使用索引

原则二：区分度高的列在前

-- 假设：status有5种值，user_id有100万种值
-- 查询：WHERE status = 1 AND user_id = 100

-- 推荐索引：(user_id, status)
-- 原因：user_id区分度高，先过滤掉大部分数据

原则三：排序列放入索引

-- 查询
SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 10;

-- 推荐索引：idx_user_time(user_id, create_time)
-- 避免文件排序，直接利用索引有序性

#

3.3 实际案例

电商订单表索引设计：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL,
    pay_time DATETIME,
    amount DECIMAL(10,2) NOT NULL,
    -- 其他字段
    
    -- 核心索引
    PRIMARY KEY (id),
    
    -- 用户查询自己的订单列表（高频）
    KEY idx_user_time (user_id, create_time),
    
    -- 按状态查询（管理后台）
    KEY idx_status_time (status, create_time),
    
    -- 支付超时查询（定时任务）
    KEY idx_status_paytime (status, pay_time)
) ENGINE=InnoDB;

四、前缀索引

#

4.1 适用场景

长字符串列（如URL、邮箱、描述）的索引：

-- 完整索引占用空间大
CREATE INDEX idx_url ON pages(url);  -- url可能很长

-- 前缀索引更省空间
CREATE INDEX idx_url ON pages(url(20));

#

4.2 前缀长度选择

目标是选择性接近完整列的选择性：

-- 测试不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(DISTINCT url) AS sel_10,
    COUNT(DISTINCT LEFT(url, 20)) / COUNT(DISTINCT url) AS sel_20,
    COUNT(DISTINCT LEFT(url, 30)) / COUNT(DISTINCT url) AS sel_30,
    COUNT(DISTINCT LEFT(url, 40)) / COUNT(DISTINCT url) AS sel_40
FROM pages;

建议：选择性达到0.9以上即可。

#

4.3 前缀索引的限制

-- 前缀索引无法用于覆盖索引
SELECT url FROM pages WHERE url = 'https://example.com';
-- 即使只查url，也需要回表（因为索引只存了前缀）

-- 前缀索引无法用于ORDER BY
SELECT * FROM pages ORDER BY url;
-- 可能使用文件排序

五、索引优化技术

#

5.1 索引条件下推（ICP）

MySQL 5.6引入的优化：在存储引擎层过滤数据，减少回表次数。

-- 有联合索引(name, age, address)
SELECT * FROM user 
WHERE name = '张' AND age > 20 AND address LIKE '%北京%';

-- 无ICP：
-- 1. 用name找到所有记录
-- 2. 回表查完整数据
-- 3. 在Server层过滤age和address

-- 有ICP：
-- 1. 用name找到记录
-- 2. 在引擎层用age过滤
-- 3. 只有符合条件的才回表

查看是否使用ICP：

EXPLAIN SELECT ...;
-- Extra列显示"Using index condition"表示使用ICP

#

5.2 索引合并（Index Merge）

当WHERE条件涉及多个索引时，MySQL可能合并多个索引的结果：

-- 有idx_name和idx_age两个独立索引
SELECT * FROM user WHERE name = 'Alice' OR age = 25;

-- Index Merge Union
-- 分别查name和age，然后合并结果

EXPLAIN中显示：

type: index_merge
key: idx_name, idx_age
Extra: Using union(idx_name,idx_age)

注意：索引合并不如联合索引高效，优先设计合适的联合索引。

#

5.3 覆盖索引

查询的所有列都在索引中，无需回表：

-- 联合索引(user_id, status, create_time)
SELECT user_id, status, create_time 
FROM orders 
WHERE user_id = 100;

-- 覆盖索引，Extra显示"Using index"

优化技巧：适当添加查询列到联合索引末尾，实现覆盖索引。

-- 原索引：idx_user_status(user_id, status)
-- 查询：SELECT user_id, status, create_time FROM ...

-- 优化为覆盖索引：
KEY idx_user_status_time (user_id, status, create_time)

六、索引维护

#

6.1 查看索引使用情况

-- 查看索引使用统计（MySQL 8.0）
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    ROWS_SELECTED,
    ROWS_INSERTED,
    ROWS_DELETED
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL;

#

6.2 删除冗余索引

-- 查找未使用的索引（MySQL 8.0）
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema NOT IN ('mysql', 'performance_schema');

#

6.3 索引重建

当索引页利用率低时，重建索引：

-- MySQL 5.7: ALTER TABLE
ALTER TABLE user ENGINE=InnoDB;

-- MySQL 8.0: OPTIMIZE TABLE
OPTIMIZE TABLE user;

七、索引设计检查清单

#

7.1 创建索引前检查

• 该列是否出现在WHERE、JOIN、ORDER BY、GROUP BY中？
• 该列的选择性是否足够高（>0.1）？
• 是否已存在可以复用的联合索引？
• 单表索引数量是否已超过5个？
• 索引列的数据类型是否尽可能小？

#

7.2 联合索引设计检查

• 是否符合最左前缀原则？
• 等值查询列是否在前，范围查询列在后？
• 是否包含ORDER BY的列？
• 是否可以设计为覆盖索引？

#

7.3 索引优化检查

• 是否使用了前缀索引替代长字符串索引？
• 是否有冗余索引可以删除？
• 是否有未使用的索引可以删除？
• 是否定期检查索引碎片？

八、常见错误与解决方案

#

8.1 索引过多导致写入慢

现象：插入、更新、删除操作变慢

原因：每次写操作都要维护所有索引

解决：

1. 删除未使用的索引
2. 合并冗余索引
3. 将部分查询移到从库

#

8.2 索引选择错误

现象：EXPLAIN显示使用了错误的索引

解决：

-- 使用FORCE INDEX强制指定索引
SELECT * FROM orders FORCE INDEX (idx_user_time) 
WHERE user_id = 100;

-- 或使用ANALYZE TABLE更新统计信息
ANALYZE TABLE orders;

#

8.3 隐式类型转换导致索引失效

-- 错误：phone是VARCHAR类型
WHERE phone = 13800138000   -- 数字，发生隐式转换

-- 正确
WHERE phone = '13800138000'  -- 字符串，使用索引

九、总结

索引类型	适用场景	注意事项
主键索引	每表必须	用自增整数，避免UUID
唯一索引	业务唯一性约束	不能含NULL
单列索引	单条件高频查询	选择性要高
联合索引	多条件组合查询	注意最左前缀和列顺序
前缀索引	长字符串列	无法覆盖索引

好的索引设计需要：

1. 深入理解业务查询模式
2. 掌握最左前缀等核心原则
3. 利用EXPLAIN分析执行计划
4. 持续监控和优化索引使用

核心要点

1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

3. 事务隔离级别的选择：根据业务需求选择合适的级别

4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。