MySQL索引，这回真讲明白了

数据库慢查询是每个后端都会遇到的问题而90%的慢查询问题都可以通过合理设计索引来解决。这篇文章不聊深奥的底层原理从实际使用的角度讲清楚索引。索引到底是什么你可以把索引理解为一本书的目录。没有索引你要从头翻到尾找内容有了索引直接查目录跳到对应页码。MySQL的InnoDB引擎用的是B树索引。叶子节点存储数据非叶子节点只存索引值和指针。B树的好处是树的高度低3层能存几千万条数据范围查询效率高叶子节点有双向指针链。sql-- 没有索引时这条SQL会全表扫描 SELECT * FROM users WHERE email zhangsanexample.com; -- 创建索引后直接走B树查找速度提升几十倍 CREATE INDEX idx_email ON users(email);主键索引与二级索引InnoDB中数据是按照主键组织的所以主键索引的叶子节点直接存储完整的数据行这叫聚簇索引。而普通索引二级索引的叶子节点存储的是主键的值。通过二级索引查数据需要先找到主键再回主键索引取完整数据这个过程叫回表。sql-- 假设users表有主键id还有二级索引idx_name CREATE INDEX idx_name ON users(name); -- 这条SQL只需要查name和id索引里就有不用回表 SELECT id, name FROM users WHERE name 张三; -- 这条SQL要查email索引里没有需要回表取完整数据 SELECT id, name, email FROM users WHERE name 张三;最左前缀原则这是使用联合索引时最重要的原则也是面试高频考点。sql-- 创建联合索引 (name, age, city) CREATE INDEX idx_name_age_city ON users(name, age, city); -- ✅ 能用到索引 SELECT * FROM users WHERE name 张三; SELECT * FROM users WHERE name 张三 AND age 25; SELECT * FROM users WHERE name 张三 AND age 25 AND city 北京; -- ✅ 注意where条件的顺序不影响优化器会自动调整 SELECT * FROM users WHERE age 25 AND name 张三; -- 也能用到 -- ❌ 用不到索引跳过了name SELECT * FROM users WHERE age 25; SELECT * FROM users WHERE city 北京; SELECT * FROM users WHERE age 25 AND city 北京;联合索引从左往右匹配跳过了前面的字段就用不了索引。所以建联合索引时区分度高的字段放左边。sql-- 假设name区分度比age高这样建更合理 CREATE INDEX idx_name_age ON users(name, age); -- 而不是 idx_age_name覆盖索引与回表前面提到了回表。如果查询的字段都在索引里就不用回表了这叫覆盖索引性能会好很多。sql-- 索引是 (name, age) -- ✅ 覆盖索引查询字段都在索引中 SELECT name, age FROM users WHERE name 张三; -- ❌ 需要回表查询了不在索引中的字段 SELECT * FROM users WHERE name 张三;所以写SQL的时候尽量只查需要的字段少用SELECT *既减少网络传输也可能利用覆盖索引避免回表。索引失效的常见场景实际开发中建了索引但SQL没用上是经常发生的事情。以下场景索引会失效1. 在索引列上用函数sql-- ❌ 索引失效 SELECT * FROM orders WHERE DATE(create_time) 2024-01-01; -- ✅ 改成范围查询 SELECT * FROM orders WHERE create_time 2024-01-01 AND create_time 2024-01-02;2. 隐式类型转换sql-- 假设phone是varchar类型 -- ❌ 索引失效传了数字发生类型转换 SELECT * FROM users WHERE phone 13800138000; -- ✅ 传字符串 SELECT * FROM users WHERE phone 13800138000;3. LIKE以%开头sql-- ❌ 索引失效 SELECT * FROM users WHERE name LIKE %三; -- ✅ 可以用到索引 SELECT * FROM users WHERE name LIKE 张%;4. OR条件中有非索引列sql-- 假设name有索引age没有索引 -- ❌ 索引失效OR两边都要能用索引才行 SELECT * FROM users WHERE name 张三 OR age 25; -- ✅ 把age也建上索引或者用UNION SELECT * FROM users WHERE name 张三 UNION SELECT * FROM users WHERE age 25;5. 索引列参与计算sql-- ❌ 索引失效 SELECT * FROM products WHERE price * 0.8 100; -- ✅ 把计算移到右边 SELECT * FROM products WHERE price 100 / 0.8;6. 使用不等于! 或 或 IS NULLsql-- 不等于和IS NULL通常不走索引 SELECT * FROM users WHERE status ! 1;用EXPLAIN分析SQL遇到慢查询第一件事就是用EXPLAIN看执行计划。sqlEXPLAIN SELECT * FROM users WHERE name 张三;输出结果重点看这几列列名含义关注点type访问类型至少要到range或refALL就是全表扫描possible_keys可能用的索引看有没有预期中的索引key实际用的索引如果是NULL说明索引没用到rows扫描的行数这个值越大性能越差Extra额外信息出现Using filesort或Using temporary要警惕type的优劣排序从好到差systemconsteq_refrefrangeindexALL目标至少达到range级别。实战建索引的建议1. 不要建太多索引每个索引都占用磁盘空间且拖慢INSERT、UPDATE、DELETE的速度。一个表一般建议不超过5个索引。2. 区分度不高的字段不建索引比如性别男/女走索引还不如全表扫描快。sql-- 区分度 COUNT(DISTINCT column) / COUNT(*) -- 区分度低于10%的字段建索引效果不好 SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users; -- 约 0.5%3. 长字符串用前缀索引sql-- 只对前20个字符建索引节省空间 CREATE INDEX idx_content ON articles(content(20));4. 频繁查询的字段优先建索引WHERE条件中频繁出现的字段ORDER BY排序的字段GROUP BY分组的字段JOIN连接的条件字段5. 不要用UUID作为主键UUID是随机字符串插入时会导致B树频繁分裂性能很差。建议用自增ID或者有序的雪花ID。总结索引的核心理念就一句话用空间换时间让查询少扫描数据。用好索引的要点搞懂最左前缀原则合理设计联合索引避免索引失效的写法函数、类型转换、前置%用EXPLAIN验证执行计划覆盖索引优于回表索引不是越多越好要精准索引是数据库性能优化的第一板斧掌握好它能解决绝大部分慢查询问题。