数据库：如何设计最佳索引 / 数据库最佳索引设计指南

索引是数据库性能优化的核心武器但“乱建索引”比“不建索引”更可怕——它会拖慢写入速度、占用额外存储空间却对查询毫无帮助。下面我从索引设计原则、实战场景、避坑指南三个维度系统梳理最佳索引设计方法。一、索引设计五大核心原则原则说明示例1. 最左前缀匹配联合索引按定义顺序生效查询条件必须从索引最左列开始索引(a,b,c)能匹配WHERE a1、WHERE a1 AND b2但不能匹配WHERE b22. 高选择性优先优先为区分度高的字段建索引区分度 不同值数量 / 总行数性别2种区分度低 ×手机号几乎全不同区分度高 √3. 覆盖索引索引包含查询所需的所有字段避免回表SELECT id, name FROM users WHERE id1→ 索引(id, name)可直接返回无需回表4. 索引下推利用索引过滤更多数据减少回表次数MySQL 5.6联合索引(age, city)WHERE age18 AND city北京可在索引层过滤5. 区分高频查询优先为高频查询条件建索引低频查询可以不走索引主查询走索引报表类低频查询可走全表扫描二、实战索引设计方案2.1 单列索引 vs 联合索引场景推荐索引原因查询条件单一单列索引简单、轻量查询条件固定多个联合索引索引覆盖所有条件减少回表查询条件变化组合维护 2-3 个联合索引覆盖高频组合避免过多索引联合索引顺序设计口诀等值条件放前面范围条件放后面查询类型索引列顺序原因WHERE a1 AND b2(a, b)或(b, a)等值查询顺序不重要WHERE a1 AND b10(a, b)a 等值过滤后b 范围查询仍能利用索引WHERE a1 AND b2(b, a)b 等值过滤后a 范围查询仍能利用索引2.2 覆盖索引设计查询语句推荐索引为什么SELECT id, name FROM users WHERE age25(age, name)索引包含age和name无需回表SELECT id, name, age FROM users WHERE age25(age, name, age)索引覆盖所有字段完全覆盖2.3 前缀索引对于长文本字段如VARCHAR(255)可以只索引前 N 个字符sqlCREATE INDEX idx_email_prefix ON users (email(10));场景推荐原因文本字段前缀索引节省空间提升索引效率区分度足够前缀长度 10-15 字符需验证区分度是否够高2.4 索引设计速查表查询场景推荐索引注意事项WHERE id ?主键索引自动不需要额外建索引WHERE name ?(name)高选择性字段优先WHERE status 1 AND created_at 2024-01-01(status, created_at)等值在前范围在后WHERE city 北京 AND age BETWEEN 18 AND 30(city, age)高选择性优先ORDER BY created_at DESC(created_at)单列索引即可GROUP BY category_id(category_id)分组字段建索引JOIN ON users.id orders.user_id两张表关联字段都建索引外键索引LIKE 张%普通索引可用前缀匹配可用索引LIKE %张❌ 无法使用索引改为全文索引LIKE %张%❌ 无法使用索引改为全文索引或Elasticsearch三、索引失效场景与避坑指南场景原因解决方案WHERE name 张三 OR status 1OR 两侧都走索引才能生效改为UNION或分别在两侧建索引WHERE YEAR(created_at) 2024对索引列做函数运算改为created_at BETWEEN 2024-01-01 AND 2024-12-31WHERE name LIKE %张通配符在开头改为WHERE name LIKE 张%前缀匹配WHERE id 1 100对索引列做计算改为WHERE id 99WHERE name IS NOT NULL索引列参与 NULL 判断❌ 不会使用索引需重写查询索引列类型不匹配WHERE phone 123phone 是字符串改为WHERE phone 123联合索引未遵循最左前缀索引(a,b,c)查询WHERE b2补充索引(b)或调整索引顺序四、索引优化实战从慢查询到快查询案例一张订单表1000 万行数据sqlCREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, status TINYINT, amount DECIMAL(10,2), created_at DATETIME, updated_at DATETIME );1️⃣ 慢查询sqlSELECT * FROM orders WHERE user_id 10086 AND status 1 AND created_at 2024-06-01 ORDER BY created_at DESC LIMIT 10;分析EXPLAIN显示全表扫描typeALL扫描 1000 万行耗时 3.2 秒2️⃣ 设计索引sqlCREATE INDEX idx_user_status_created ON orders (user_id, status, created_at DESC);3️⃣ 优化后效果指标优化前优化后提升扫描行数10,000,00050 行减少 99.999%查询耗时3.2 秒0.01 秒320 倍五、索引监控与维护操作命令作用查看索引使用情况SHOW INDEX FROM table_name;查看当前所有索引分析查询计划EXPLAIN SELECT ...;确认是否使用了正确的索引查看索引使用频率SELECT * FROM sys.schema_index_statistics;找出未使用的冗余索引重建索引ALTER TABLE table_name ENGINEInnoDB;整理索引碎片删除无用索引DROP INDEX idx_name ON table_name;释放存储空间提升写入性能六、总结索引设计核心要点原则一句话总结最左前缀联合索引查询条件必须从第一列开始高选择性优先给区分度高的字段建索引覆盖索引索引包含查询所需的所有字段等值在前联合索引中等值条件放前面范围条件放后面避免函数运算不要在索引列上做函数运算前缀匹配LIKE 张%可用索引LIKE %张不可定期维护删除无用索引重建碎片索引控制在5个以内一张表索引数量建议控制在 5 个以内过多会影响写入性能