MySQL索引设计：如何高效创建高性能索引一、索引的本质与代价索引是典型的空间换时间策略： B+树结构：主流数据库默认

索引如同数据库的导航系统——优秀的设计让数据高效触达，错误的设计则会将查询效率大打折扣。

一、索引的本质与代价

索引是典型的空间换时间策略，通常采用B+树的数据结构（主流数据库默认索引类型），多路平衡搜索树能够保证高效范围查询，它可以将全表扫描O(n)复杂度降至O(log n)。

三大代价：
1. 存储开销：索引通常占据数据表20%-30%空间
2. 写操作延迟：每次INSERT/UPDATE/DELETE需更新索引
3. 优化器误判：冗余索引可能导致执行计划选择错误
核心价值
1. 查询加速：数据定位速度提升显著
2. 排序优化：ORDER BY无需临时排序
3. 锁粒度降低：减少锁定范围和时长
4. 连接加速：多表关联性能提升

二、索引类型（InnoDB引擎）

索引类型	创建语法	特点	适用场景
主键索引	PRIMARY KEY(id)	聚簇索引，包含完整行数据	所有表必须创建
唯一索引	UNIQUE idx_email(email)	强制列唯一性	邮箱、身份证等唯一字段
普通索引	INDEX idx_name(name)	基本索引类型	高频查询的WHERE条件
联合索引	INDEX idx_a_b_c(a,b,c)	多列组合索引	多条件查询
前缀索引	INDEX idx_name(name(10))	仅索引字段前N个字符	TEXT/VARCHAR大字段
全文索引	FULLTEXT idx_content(content)	关键词搜索	文章内容搜索

三、索引设计法则

法则1：选择合适的字段

高筛选性字段：区分度 > 20%，如user_id、order_no

避免索引陷阱：

-- 反面案例：状态字段索引低效
CREATE INDEX idx_status ON orders(status); -- 不推荐

-- 正面案例：结合时间范围高效查询
CREATE INDEX idx_status_time ON orders(status, create_time);

法则2：理解联合索引左前缀原则

最左匹配机制：

CREATE INDEX idx_composite ON products(category_id, price, stock);

/* 生效场景 */
WHERE category_id=101                  -- ✅ 使用索引
WHERE category_id=101 AND price<100     -- ✅ 使用索引
WHERE price>50 AND stock>10            -- ❌ 无法命中索引

法则3：索引排序优化

ORDER BY与GROUP BY优化：

-- 需要额外排序
SELECT * FROM logs WHERE app='web' ORDER BY create_time DESC;

-- 优化方案：覆盖索引避免排序
CREATE INDEX idx_app_time ON logs(app, create_time DESC);

法则4：避免冗余索引

工具检测：

-- MySQL冗余索引检测
SELECT * FROM sys.schema_redundant_indexes;

-- PostgreSQL检测脚本
SELECT indrelid::regclass, indexrelid::regclass
FROM pg_index WHERE indisvalid;

法则5：短索引原则

优先使用短字段建立索引：

-- UUID（差） vs 自增ID（优）
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 推荐
  uuid CHAR(36) UNIQUE                   -- 不推荐主键
);

-- 使用前缀索引（减少70%空间）
ALTER TABLE logs ADD INDEX idx_url(url(32));

法则6：选择性原则

索引选择性 = 不重复值数量 / 总行数

-- 计算某列的选择性
SELECT 
  COUNT(DISTINCT status)/COUNT(*) AS selectivity 
FROM orders;

选择性 > 0.2：非常适合建索引
选择性 < 0.01：建索引效果差

使用EXPLAIN分析所有核心查询的执行计划，提前发现索引缺失或者无法命中索引的问题。