我要评论一、为什么要用索引？—— 先讲个血泪故事
想象你去图书馆找一本《mysql从入门到入土》：
没有索引的情况（全表扫描）：
你从第一排书架开始，一本一本翻，看到《西游记》...《三体》...《java编程思想》... 翻了3个小时，终于在第5000本书里找到了。这时候你已经想"从入门到放弃"了。
有索引的情况：
你去电脑查一下，系统告诉你"在第3区第5架第2层"，你2分钟就拿到的书，还能顺便借本《redis深度历险》。
数据库也是这个道理。 没有索引，mysql就要一行一行地"翻书"；有了索引，直接"导航定位"。
真实数据说话： 假设你有100万条用户数据，查 where phone = '13800138000'：
| 情况 | 耗时 | 磁盘io |
|---|---|---|
| 无索引 | 几秒~几十秒 | 扫描100万行 |
| 有索引 | 几毫秒 | 可能只需3-5次io |
索引的本质：用空间换时间，用写性能换读性能。
tip: 在数据量小的时候，尽量不要使用索引
二、索引的原理——b+树到底是个啥？
别被"b+树"这个名字吓到，它其实就是个 "很会做排序的多叉树" 。
2.1 为什么不用其他结构？
| 结构 | 为什么mysql不用 | 缺点 |
|---|---|---|
| 哈希表 | hash索引 | 只能精确匹配，不能范围查询（> < between），不能排序 |
| 二叉树 | 高度太高 | 100万数据，树高20层，查一次要20次磁盘io，慢死 |
| b树 | b+树的哥哥 | 数据存在非叶子节点，浪费空间，范围查询麻烦 |
2.2 b+树长什么样？（简化版）
[10 | 30 | 50] ← 根节点（只存键值，不存数据）
/ | \
[5|10] [20|30] [40|50|60] ← 非叶子节点（还是只存键值）
/ \ / \ / \ \
[1,2,3,4,5] [10,11] [20,25] [30,35] [40,45] [50,55] [60,65] ← 叶子节点（存真实数据/指针）
所有叶子节点用链表相连：1→2→3→4→5→10→11→20→25→30→35...b+树的三大杀手锏：
- 矮胖设计：一个节点存很多键（innodb默认16kb一页），1000万数据可能只有3-4层，查一次最多3-4次io
- 数据都在叶子节点：非叶子节点只存"导航信息"，一页能存更多键，树更矮
- 叶子节点链表连接：范围查询（
between、>、<）直接顺着链表走，不用回树上层
2.3 聚簇索引 vs 非聚簇索引（重点！）
聚簇索引（clustered index）—— 数据本身：
- 叶子节点存的就是完整的行数据
- innodb表必须有，且只有一个
- 默认主键就是聚簇索引；没主键就用第一个唯一索引；再没有就隐式生成6字节的row_id
聚簇索引查找： [找主键10] → 直接定位到叶子节点 → 拿到完整数据（id=10, name='张三', age=20...）
非聚簇索引（secondary index）—— 数据的"快递单号"：
- 叶子节点存的是索引列 + 主键值
- 查到后还要拿主键去聚簇索引查一次完整数据（叫"回表"）
非聚簇索引查找： [找name='张三'] → 叶子节点拿到(id=10) → 再去聚簇索引查id=10的完整数据
对比：
聚簇索引（主键id） 非聚簇索引（name列）
[1] ['alice'] → id=1
/ \ ['bob'] → id=2
[1] [2] ['carol'] → id=3
/ \
数据行1 数据行2 查到'bob'后，拿id=2去聚簇索引找完整数据（回表）
三、索引的用法——实战指南
3.1 索引类型全家福
-- 1. 主键索引（自动创建，聚簇索引）
create table user (
id int primary key auto_increment, -- 这就是主键索引
name varchar(50),
phone varchar(20)
);
-- 2. 唯一索引（值不能重复，允许null）
create unique index uk_phone on user(phone);
-- 3. 普通索引（最常用）
create index idx_name on user(name);
-- 4. 组合索引（多列联合，最左前缀原则！）
create index idx_name_age on user(name, age);
-- 5. 全文索引（mysql 5.6+，用于文本搜索）
create fulltext index idx_content on article(content);
-- 6. 前缀索引（省空间，用于长字符串）
create index idx_email on user(email(10)); -- 只索引前10个字符3.2 组合索引的最左前缀原则（面试必问！）
创建 index idx_a_b_c (a, b, c)，相当于建了3个索引：
(a)(a, b)(a, b, c)
能用上索引的查询：
where a = 1 -- ✓ 用到了idx_a_b_c的a部分 where a = 1 and b = 2 -- ✓ 用到了a和b where a = 1 and b = 2 and c = 3 -- ✓ 完美，全用上 where a = 1 and c = 3 -- ✓ 只用到了a（c跳过了b，断了）
用不上索引的查询（踩坑预警）：
where b = 2 -- ✗ 没a，最左缺失 where b = 2 and c = 3 -- ✗ 没a where a = 1 or b = 2 -- ✗ or导致索引失效（除非两边都有索引） where a like '%xxx' -- ✗ 前导模糊，索引失效
记忆口诀：最左优先，中间不断，范围停步。
3.3 索引下推（index condition pushdown, icp）
mysql 5.6+的优化，在存储引擎层就过滤数据，减少回表。
-- 有索引 idx_name_age(name, age) select * from user where name like '张%' and age = 20; -- 老版本：先找到所有姓张的，回表查age，再过滤 -- 5.6+：在索引里就直接判断age=20，只回表符合条件的数据
3.4 覆盖索引（covering index）—— 不回表的神技
如果查询的列都在索引里，直接返回，不用回表查聚簇索引。
-- 有索引 idx_name_age(name, age) select name, age from user where name = '张三'; -- ✓ 覆盖索引！索引里就有name和age，直接返回，速度飞起 select * from user where name = '张三'; -- ✗ 需要回表，因为索引里没有其他列（如phone、address等）
设计技巧： 经常一起查的字段，考虑建组合索引或加入索引。
四、提升效率——索引优化实战
4.1 explain命令——索引优化的"体检报告"
explain select * from user where phone = '13800138000';
关键字段解读：
| 字段 | 含义 | 优化目标 |
|---|---|---|
type | 访问类型 | 至少range，最好ref或const，避免all（全表扫描） |
possible_keys | 可能用的索引 | 看有没有合适的索引 |
key | 实际用的索引 | null就是没用索引，悲剧 |
rows | 估计扫描行数 | 越小越好 |
extra | 额外信息 | using index（覆盖索引，好）using filesort（需要排序，坏）using temporary（用了临时表，坏） |
type性能排序（从好到坏）：
system > const > eq_ref > ref > range > index > all ↓ ↓ ↓ ↓ ↓ ↓ ↓ 最快 主键/唯一 联表主键 普通索引 范围扫描 索引扫描 全表扫描
4.2 索引设计的"三要三不要"
三要：
1.要建在where、join、order by、group by的列上
-- 经常这样查？ select * from order where user_id = 100 and status = 1 order by create_time; -- 考虑：index idx_user_status_time(user_id, status, create_time)
2.要高选择性的列放前面
-- 性别（只有男女）选择性低，放后面 -- 手机号（几乎唯一）选择性高，放前面 create index idx_phone_gender on user(phone, gender); -- ✓ 好 create index idx_gender_phone on user(gender, phone); -- ✗ 差，gender区分度太低
3.要利用覆盖索引减少回表
-- 如果经常只查name和email create index idx_name_email on user(name, email); select name, email from user where name = 'xxx'; -- 覆盖索引，不回表
三不要：
1.不要在低选择性列上建单列索引
-- 性别字段只有0和1，建索引后mysql可能直接全表扫描 select * from user where gender = 1; -- 可能走可能不走，看数据分布
2.不要对索引列做函数或运算
where year(create_time) = 2023 -- ✗ 函数导致索引失效 where create_time >= '2023-01-01' and create_time < '2024-01-01' -- ✓ 范围查询 where id + 1 = 100 -- ✗ 运算导致失效 where id = 99 -- ✓ 直接比较
3.不要建太多索引（写操作会哭）
- 每个索引都是一棵b+树，插入/更新/删除时要维护所有索引
- 建议：单表索引不超过5个，组合索引列不超过5个
4.3 索引失效的常见坑（排雷手册）
-- 1. 前导模糊查询 where name like '%张%' -- ✗ 失效 where name like '张%' -- ✓ 有效（用到索引的name部分） -- 2. 隐式类型转换 where phone = 13800138000 -- ✗ phone是字符串，数字会转换，索引失效 where phone = '13800138000' -- ✓ 正确 -- 3. 不等于、not in（可能失效，看数据分布） where status != 0 -- 数据量大时可能全表扫描 -- 4. is null vs is not null（看列是否允许null） -- 如果列not null，is null直接返回空，很快 -- 如果列允许null，is not null可能扫描大量数据 -- 5. or条件（两边都要有索引） where id = 1 or name = '张三' -- 如果只有id有索引，name没索引，可能全表扫描 -- 解决：分别查询union，或给name也建索引
4.4 大表优化策略
场景：千万级用户表，查询慢
- 分页优化（深分页问题）
-- 慢：offset越大越慢，需要排序后跳过前面1000000条 select * from user order by id limit 1000000, 10; -- 快：先查id，再join（利用覆盖索引） select * from user u join (select id from user order by id limit 1000000, 10) tmp on u.id = tmp.id; -- 更快：记录上次位置（游标分页） select * from user where id > 上次最大id order by id limit 10;
- 分区表（partition）
-- 按时间分区，查询只扫相关分区
create table log (
id int,
create_time datetime
) partition by range (year(create_time)) (
partition p2022 values less than (2023),
partition p2023 values less than (2024),
partition p2024 values less than maxvalue
);
- 读写分离 + 归档
- 热数据（最近3个月）放主库，有索引，快速查询
- 冷数据归档到历史库，甚至可以去掉部分索引省空间
五、总结：索引使用 checklist
□ 查询是否用了索引？（explain看key字段） □ 是否避免了全表扫描？（type不是all） □ 组合索引是否遵循最左前缀？ □ 是否利用了覆盖索引减少回表？ □ 索引列是否做了函数/运算/隐式转换？ □ 前导模糊查询是否必须？能否用全文索引？ □ 分页是否太深？是否需要优化？ □ 写性能是否可接受？（索引别太多）
最后一句忠告： 索引不是银弹，它是读性能的加速器，写性能的减速带。设计时平衡读写比例，监控慢查询日志，定期用optimize table整理碎片，才能让mysql跑得又快又稳。
到此这篇关于mysql索引用法实战指南的文章就介绍到这了,更多相关mysql索引用法内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论