💡 摘要：你是否遇到过数据库查询越来越慢？是否在纠结该为哪些字段创建索引？是否曾经创建了索引却发现性能反而下降？
正确的索引设计能让查询速度提升百倍，而错误的索引则会拖垮整个数据库。索引不是越多越好，而是越精准越好。
本文将从实际业务场景出发，深入解析索引设计的核心原则，通过大量实战案例教你如何创建高性能MySQL索引，避免常见陷阱，让你的数据库飞起来！
一、索引设计核心原则
1. 索引选择黄金法则
| 原则 | 说明 | 示例 |
| 选择性原则 | 选择区分度高的列 | 身份证号 > 性别 |
| 最左前缀原则 | 复合索引的左优先 | INDEX(a,b,c) 支持 a、a,b、a,b,c |
| 覆盖索引原则 | 索引包含查询所需所有字段 | SELECT id,name FROM users |
| 短索引原则 | 使用前缀索引减少存储 | INDEX(email(20)) |
| 适度原则 | 不是越多越好 | 每个表3-5个优质索引 |
2. 索引选择性的计算方法
sql
-- 计算字段的选择性
SELECT
COUNT(DISTINCT column_name) * 1.0 / COUNT(*) AS selectivity,
COUNT(DISTINCT column_name) AS distinct_values,
COUNT(*) AS total_rows
FROM table_name;
-- 示例：用户表字段选择性分析
SELECT
COUNT(DISTINCT id) * 1.0 / COUNT(*) AS id_selectivity, -- 1.0
COUNT(DISTINCT email) * 1.0 / COUNT(*) AS email_selectivity, -- 0.98
COUNT(DISTINCT username) * 1.0 / COUNT(*) AS username_selectivity, -- 0.95
COUNT(DISTINCT gender) * 1.0 / COUNT(*) AS gender_selectivity, -- 0.02
COUNT(DISTINCT city) * 1.0 / COUNT(*) AS city_selectivity -- 0.35
FROM users;
二、单列索引设计实战
1. 主键索引设计
sql
-- 自增主键（推荐）
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
-- UUID主键（需要优化）
CREATE TABLE orders (
id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
user_id INT UNSIGNED,
amount DECIMAL(10,2)
) ENGINE=InnoDB;
-- 使用有序UUID优化
CREATE TABLE orders_optimized (
id BINARY(16) PRIMARY KEY,
user_id INT UNSIGNED,
amount DECIMAL(10,2)
);
-- 插入时转换
INSERT INTO orders_optimized (id, user_id, amount)
VALUES (UNHEX(REPLACE(UUID(), '-', '')), 1, 100.00);
2. 唯一索引设计
sql
-- 基本唯一索引
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE, -- 自动创建唯一索引
phone CHAR(11) UNIQUE
);
-- 复合唯一索引
CREATE TABLE user_relationships (
user_id INT,
friend_id INT,
created_at DATETIME,
UNIQUE KEY uk_user_friend (user_id, friend_id)
);
-- 避免重复数据插入
INSERT IGNORE INTO user_relationships (user_id, friend_id)
VALUES (1, 2);
-- 或者使用ON DUPLICATE KEY UPDATE
INSERT INTO user_relationships (user_id, friend_id)
VALUES (1, 2)
ON DUPLICATE KEY UPDATE created_at = NOW();
3. 普通索引设计
sql
-- 选择高选择性字段
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_phone ON users(phone);
-- 前缀索引（针对长文本）
CREATE INDEX idx_username_prefix ON users(username(10));
CREATE INDEX idx_email_prefix ON users(email(15));
-- 函数索引（MySQL 8.0+）
CREATE INDEX idx_created_date ON users((DATE(created_at)));
CREATE INDEX idx_lower_email ON users((LOWER(email)));
-- 查看索引效果
EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';
三、复合索引设计实战
1. 复合索引设计原则
sql
-- 正确的列顺序：高选择性在前，低选择性在后
CREATE INDEX idx_composite ON employees(department_id, gender, status);
-- 错误的顺序：低选择性在前
CREATE INDEX idx_bad_composite ON employees(gender, department_id, status);
-- 覆盖索引设计
CREATE INDEX idx_covering ON orders(user_id, status, amount, created_at);
-- 使用覆盖索引查询（避免回表）
EXPLAIN SELECT user_id, amount, created_at
FROM orders
WHERE user_id = 1001 AND status = 'completed';
2. 常见业务场景索引设计
sql
-- 电商订单查询
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
status TINYINT COMMENT '1待支付 2已支付 3已完成',
amount DECIMAL(10,2),
created_at DATETIME,
paid_at DATETIME,
INDEX idx_user_status (user_id, status), -- 用户订单状态查询
INDEX idx_status_created (status, created_at), -- 状态时间查询
INDEX idx_user_created (user_id, created_at) -- 用户历史订单
);
-- 社交平台feed流
CREATE TABLE feeds (
feed_id BIGINT PRIMARY KEY,
user_id BIGINT,
content TEXT,
created_at DATETIME,
visibility TINYINT DEFAULT 1,
INDEX idx_user_feed (user_id, created_at DESC), -- 用户个人feed
INDEX idx_public_feed (visibility, created_at DESC) -- 公共时间线
);
-- 时间范围查询优化
CREATE TABLE access_logs (
log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
action VARCHAR(50),
created_at DATETIME,
INDEX idx_time_user (created_at, user_id), -- 时间+用户查询
INDEX idx_user_time (user_id, created_at) -- 用户行为分析
);
3. 排序和分组优化
sql
-- 支持排序的索引设计
CREATE INDEX idx_sorting ON products(category_id, price DESC, created_at DESC);
-- 查询使用索引排序（避免filesort）
EXPLAIN SELECT * FROM products
WHERE category_id = 101
ORDER BY price DESC, created_at DESC
LIMIT 20;
-- 分组查询优化
CREATE INDEX idx_grouping ON sales(sale_date, product_id, region);
-- 使用索引进行分组
EXPLAIN SELECT product_id, region, SUM(amount)
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_id, region;
四、高级索引技巧
1. 索引下推优化
sql
-- MySQL 5.6+ 索引下推
CREATE INDEX idx_name_age ON employees(name, age);
-- 索引层面过滤age条件，减少回表
EXPLAIN SELECT * FROM employees
WHERE name LIKE '张%' AND age > 25;
-- 查看索引下推使用情况
SET optimizer_switch = 'index_condition_pushdown=on';
2. 索引合并优化
sql
-- 多个单列索引的合并
CREATE INDEX idx_firstname ON employees(first_name);
CREATE INDEX idx_lastname ON employees(last_name);
-- 使用索引合并
EXPLAIN SELECT * FROM employees
WHERE first_name = 'John' OR last_name = 'Smith';
-- 强制使用索引合并
SELECT * FROM employees
FORCE INDEX(idx_firstname, idx_lastname)
WHERE first_name = 'John' OR last_name = 'Smith';
3. 不可见索引和降序索引
sql
-- 不可见索引（测试索引效果）
CREATE INDEX idx_test ON users(phone) INVISIBLE;
-- 测试查询性能
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- 切换索引可见性
ALTER TABLE users ALTER INDEX idx_test VISIBLE;
ALTER TABLE users ALTER INDEX idx_test INVISIBLE;
-- 降序索引（MySQL 8.0+）
CREATE INDEX idx_desc ON orders(created_at DESC, amount ASC);
-- 支持混合排序查询
EXPLAIN SELECT * FROM orders
ORDER BY created_at DESC, amount ASC
LIMIT 100;
五、索引性能分析与优化
1. 使用EXPLAIN深入分析
sql
-- 详细分析查询执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'completed';
-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics
WHERE table_name = 'orders';
-- 监控索引使用频率
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';
2. 索引优化实战案例
sql
-- 案例1：删除冗余索引
-- 原有索引
SHOW INDEX FROM orders;
/*
idx_user_id (user_id)
idx_user_status (user_id, status) -- 这个可以替代idx_user_id
*/
-- 删除冗余索引
DROP INDEX idx_user_id ON orders;
-- 案例2：优化大文本字段索引
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
author_id INT,
created_at DATETIME
);
-- 错误的索引
CREATE INDEX idx_content ON articles(content(100)); -- 前缀太长
-- 正确的优化
CREATE INDEX idx_author_time ON articles(author_id, created_at);
CREATE INDEX idx_title_prefix ON articles(title(20));
3. 索引维护与监控
sql
-- 查看索引碎片情况
SELECT
TABLE_NAME,
INDEX_NAME,
ROUND(STATS_AVG_ROW_LENGTH) AS avg_row_length,
ROUND(STATS_DATA_LENGTH / 1024 / 1024, 2) AS data_size_mb,
ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_size_mb
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY index_size_mb DESC;
-- 重建索引优化性能
ALTER TABLE orders ENGINE=InnoDB; -- 重建表
OPTIMIZE TABLE orders; -- 优化表
-- 定期分析表统计信息
ANALYZE TABLE orders;
ANALYZE TABLE users;
六、常见索引陷阱与解决方案
1. 索引失效的常见场景
sql
-- 1. 函数操作导致索引失效
SELECT * FROM users WHERE DATE(created_at) = '2023-01-01'; -- 失效
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'; -- 有效
-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000; -- 失效（phone是varchar）
SELECT * FROM users WHERE phone = '13800138000'; -- 有效
-- 3. 前导通配符LIKE
SELECT * FROM users WHERE username LIKE '%john%'; -- 失效
SELECT * FROM users WHERE username LIKE 'john%'; -- 有效
-- 4. OR条件不当使用
SELECT * FROM users WHERE username = 'john' OR age = 25; -- 可能失效
2. 过度索引的问题
sql
-- 过多的索引导致写性能下降
CREATE TABLE over_indexed_table (
id INT PRIMARY KEY,
col1 INT, INDEX idx1 (col1),
col2 INT, INDEX idx2 (col2),
col3 INT, INDEX idx3 (col3),
col4 INT, INDEX idx4 (col4),
col5 INT, INDEX idx5 (col5)
);
-- 每个INSERT需要更新6个索引！
INSERT INTO over_indexed_table VALUES (1,1,1,1,1,1);
-- 解决方案：使用复合索引
DROP INDEX idx1, idx2, idx3, idx4, idx5 ON over_indexed_table;
CREATE INDEX idx_composite ON over_indexed_table(col1, col2, col3, col4, col5);
七、实战：电商系统索引设计完整案例
1. 表结构设计
sql
-- 商品表
CREATE TABLE products (
product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
status TINYINT NOT NULL DEFAULT 1 COMMENT '1上架 0下架',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 索引设计
INDEX idx_category_status (category_id, status),
INDEX idx_price (price),
INDEX idx_name (product_name(20)),
INDEX idx_created (created_at DESC)
) ENGINE=InnoDB;
-- 订单表
CREATE TABLE orders (
order_id VARCHAR(32) PRIMARY KEY COMMENT '订单号',
user_id BIGINT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL COMMENT '1待支付 2已支付 3已发货 4已完成 5已取消',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
paid_at DATETIME NULL,
-- 索引设计
INDEX idx_user_status (user_id, status),
INDEX idx_created_status (created_at, status),
INDEX idx_paid (paid_at),
INDEX idx_user_created (user_id, created_at DESC)
) ENGINE=InnoDB;
-- 订单明细表
CREATE TABLE order_items (
item_id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id VARCHAR(32) NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
-- 索引设计
INDEX idx_order (order_id),
INDEX idx_product (product_id),
INDEX idx_order_product (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;
2. 高频查询优化
sql
-- 查询用户订单列表
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status IN (2,3,4)
ORDER BY created_at DESC
LIMIT 10;
-- 使用索引: idx_user_status
-- 商品分类搜索
EXPLAIN SELECT * FROM products
WHERE category_id = 101
AND status = 1
AND price BETWEEN 100 AND 1000
ORDER BY created_at DESC
LIMIT 20;
-- 使用索引: idx_category_status
-- 订单统计查询
EXPLAIN SELECT DATE(created_at) as date, COUNT(*) as order_count
FROM orders
WHERE created_at >= '2023-01-01'
AND status = 2
GROUP BY DATE(created_at);
-- 使用索引: idx_created_status
八、索引设计检查清单
1. 设计阶段检查
- 是否分析了字段选择性？
- 复合索引的顺序是否合理？
- 是否考虑了最左前缀原则？
- 是否使用了覆盖索引？
- 索引数量是否适度（3-5个）？
2. 实施阶段检查
- 是否使用了前缀索引优化长字段？
- 是否避免了冗余索引？
- 是否考虑了排序和分组需求？
- 是否使用了合适的索引类型？
3. 维护阶段检查
- 是否定期监控索引使用情况？
- 是否清理了未使用的索引？
- 是否优化了索引碎片？
- 是否更新了统计信息？
九、总结与进阶学习
1. 核心要点回顾
- 选择性优先：选择区分度高的字段创建索引
- 左前缀原则：复合索引的列顺序至关重要
- 覆盖索引：避免回表查询提升性能
- 适度原则：索引不是越多越好
- 持续优化：定期分析和维护索引
2. 性能提升效果
| 优化措施 | 性能提升 | 适用场景 |
| 添加合适索引 | 10-100倍 | 大多数查询 |
| 使用覆盖索引 | 2-10倍 | 查询特定字段 |
| 优化索引顺序 | 2-5倍 | 复合索引 |
| 索引下推 | 1.5-3倍 | MySQL 5.6+ |
| 删除冗余索引 | 提升写性能 | 写密集场景 |
3. 进阶学习方向
- 分区表索引优化：结合分区策略设计索引
- 全文索引：针对文本搜索场景
- 空间索引：GIS地理数据查询
- 索引压缩：减少索引存储空间
- 机器学习索引优化：基于查询模式自动优化
通过本文的实战指南，你应该已经掌握了高性能MySQL索引设计的核心技巧。记住：好的索引不是一次设计完成的，而是需要持续观察、分析和优化的。现在就开始为你数据库设计合适的索引吧！
