今天我们来聊聊让无数开发者又爱又恨的索引——数据库索引。
相信不少小伙伴在工作中都遇到过这样的夺命场景：
明明已经加了索引，为什么查询还是连问慢？为什么有时候索引反而导致性能下降？联合索引到底该怎么设计才合理？别急，今天我就通过10个问题，索引带你彻底搞懂索引的夺命奥秘！
希望对你会有所帮助。连问
简单来说，索引就是连问数据的目录。
就像一本书的索引目录能帮你快速找到内容一样，数据库索引能帮你快速定位数据。夺命
复制-- 没有索引的连问查询（全表扫描） SELECT * FROM users WHERE name = 苏三; -- 需要遍历所有记录 -- 有索引的查询（索引扫描） CREATE INDEX idx_name ON users(name); SELECT * FROM users WHERE name = 苏三; -- 通过索引快速定位1.2.3.4.5.6.
图片
索引的底层结构（B+树）：
图片
场景还原：
复制CREATE INDEX idx_name ON users(name); SELECT * FROM users WHERE name LIKE %苏三%; -- 还是云南idc服务商很慢！1.2.原因分析：
前导通配符：LIKE %苏三% 导致索引失效索引选择性差：如果name字段大量重复，连问索引效果不佳回表代价高：索引覆盖不全，需要回表查询解决方案：
复制-- 方案1：避免前导通配符 SELECT * FROM users WHERE name LIKE苏三%; -- 方案2：使用覆盖索引 CREATE INDEX idx_name_covering ON users(name, id, email); SELECT name, id, email FROM users WHERE name LIKE苏三%; -- 不需要回表 -- 方案3：使用全文索引（对于文本搜索） CREATE FULLTEXT INDEX ft_name ONusers(name); SELECT * FROM users WHERE MATCH(name) AGAINST(苏三);1.2.3.4.5.6.7.8.9.10.绝对不是！ 索引需要维护代价：
复制-- 每个索引都会影响写性能 INSERT INTO users (name, email, age) VALUES (苏三, susan@example.com, 30); -- 需要更新: -- 1. 主键索引 -- 2. idx_name索引（如果存在） -- 3. idx_email索引（如果存在） -- 4. idx_age索引（如果存在）1.2.3.4.5.6.7.索引的代价：
存储空间：每个索引都需要额外的磁盘空间写操作变慢：INSERT/UPDATE/DELETE需要维护所有索引优化器负担：索引太多会增加查询优化器的选择难度黄金法则：一般建议表的索引数量不超过5-7个
最左前缀原则：联合索引只能从最左边的列开始使用
复制-- 创建联合索引 CREATE INDEX idx_name_age ON users(name, age); -- 能使用索引的查询 SELECT * FROM users WHERE name = 苏三; -- √ 使用索引 SELECT * FROM users WHERE name = 苏三 AND age = 30; -- √ 使用索引 SELECT * FROM users WHERE age = 30 AND name = 苏三; -- √ 优化器会调整顺序 -- 不能使用索引的查询 SELECT * FROM users WHERE age = 30; -- × 不符合最左前缀1.2.3.4.5.6.7.8.9.10.联合索引结构：
选择原则：
高选择性字段在前：选择性高的字段能更快过滤数据经常查询的字段在前：优先满足常用查询场景等值查询在前，范围查询在后复制-- 计算字段选择性 SELECT COUNT(DISTINCT name) / COUNT(*) as name_selectivity, COUNT(DISTINCT age) / COUNT(*) as age_selectivity, COUNT(DISTINCT city) / COUNT(*) as city_selectivity FROM users; -- 根据选择性决定索引顺序 CREATE INDEX idx_name_city_age ON users(name, city, age); -- name选择性最高1.2.3.4.5.6.7.8.9.覆盖索引：索引包含了查询需要的所有字段，不需要回表查询
复制-- 不是覆盖索引（需要回表） CREATE INDEX idx_name ON users(name); SELECT * FROM users WHERE name = 苏三; -- 需要回表查询其他字段 -- 覆盖索引（不需要回表） CREATE INDEX idx_name_covering ON users(name, email, age); SELECT name, email, age FROM users WHERE name = 苏三; -- 所有字段都在索引中1.2.3.4.5.6.7.覆盖索引的优势：
避免回表：减少磁盘IO减少内存占用：只需要读取索引页提升性能：查询速度更快NULL值的问题：
复制-- 创建索引 CREATE INDEX idx_email ON users(email); -- 查询NULL值 SELECT * FROM users WHERE email IS NULL; -- 可能不使用索引 SELECT * FROM users WHERE email IS NOT NULL; -- 可能不使用索引1.2.3.4.5.6.解决方案：
避免NULL值：设置默认值使用函数索引（MySQL 8.0+） 复制-- 使用函数索引处理NULL值 CREATE INDEX idx_email_null ON users((COALESCE(email, ))); SELECT * FROM users WHERE COALESCE(email, ) = ;1.2.3.索引优化排序和分组：
复制-- 创建索引 CREATE INDEX idx_age_name ON users(age, name); -- 索引优化排序 SELECT * FROM users ORDER BY age, name; -- √ 使用索引避免排序 -- 索引优化分组 SELECT age, COUNT(*) FROM users GROUP BY age; -- √ 使用索引优化分组 -- 无法使用索引排序的情况 SELECT * FROM users ORDER BY name, age; -- × 不符合最左前缀 SELECT * FROM users ORDER BY age DESC, name ASC; -- × 排序方向不一致1.2.3.4.5.6.7.8.9.10.11.12.常见索引失效场景：
函数操作：WHERE YEAR(create_time) = 2023类型转换：WHERE phone = 13800138000（phone是varchar）数学运算：WHERE age + 1 > 30前导通配符：WHERE name LIKE %苏三使用EXPLAIN分析：
复制EXPLAIN SELECT * FROM users WHERE name = 苏三; -- 查看关键指标： -- type: const|ref|range|index|ALL（性能从好到坏） -- key: 实际使用的索引 -- rows: 预估扫描行数 -- Extra: Using index（覆盖索引）| Using filesort（需要排序）| Using temporary（需要临时表）1.2.3.4.5.6.7.定期索引维护：
复制-- 查看索引使用情况（MySQL） SELECT * FROM sys.schema_index_statistics WHERE table_schema = your_databaseAND table_name = users; -- 重建索引（优化索引碎片） ALTER TABLE users REBUILD INDEX idx_name; -- 分析索引使用情况 ANALYZE TABLE users;1.2.3.4.5.6.7.8.9.索引监控：
复制-- 开启索引监控（Oracle） ALTER INDEX idx_name MONITORING USAGE; -- 查看索引使用情况 SELECT * FROM v$object_usage WHERE index_name = IDX_NAME;1.2.3.4.5.MySQL vs PostgreSQL索引差异：
特性
MySQL
PostgreSQL
索引类型
B+Tree, Hash, Fulltext
B+Tree, Hash, GiST, SP-GiST
覆盖索引
支持
支持（使用INCLUDE）
函数索引
8.0+支持
支持
部分索引
支持
支持
索引组织表
聚簇索引
堆表
PostgreSQL示例：
复制-- 创建包含索引（Covering Index） CREATE INDEX idx_users_covering ON users (name) INCLUDE (email, age); -- 创建部分索引（Partial Index） CREATE INDEX idx_active_users ON users (name) WHERE is_active = true; -- 创建表达式索引（Expression Index） CREATE INDEX idx_name_lower ON users (LOWER(name));1.2.3.4.5.6.7.8.
图片
好的索引设计是数据库性能的基石。
不要盲目添加索引，要基于实际查询需求和数据分布来科学设计。
源码库相关文章：
相关推荐：