【057】MySQL 索引系列之一：建了索引还慢成蜗牛？5 种索引的 “图书馆用法”，菜鸟一看就懂

零、引入

王二的后背都快被领导的目光灼穿了。屏幕上的订单查询页面转了足足 6 秒才加载出来，领导的声音像冰碴子一样砸过来：“用户反馈 APP 查订单卡得能煮鸡蛋，你这索引是建了个寂寞？今天下班前解决不了，就别下班了。”
键盘上的键帽都被他攥得发潮，明明给order表建了主键索引，怎么还这么慢？桌上的外卖盒敞着口，炒饭的热气早就散了，像极了他此刻凉透的心情。走投无路的他，抱着笔记本电脑，像只犯错的小猫，挪到了隔壁哇哥的工位旁。
哇哥正在慢悠悠地泡熟普，紫砂壶里飘出的醇厚香气，把办公室的压抑驱散了几分。听王二说完情况，他呷了口茶，指了指对面的椅子：“坐。你这不是没建索引，是建错了索引。索引就像图书馆的书签，你给小说贴了个历史类的标签，找起来能不慢吗？”

点赞 + 关注，跟着哇哥把 MySQL 的 5 种核心索引扒得明明白白，用图书馆找书的大白话讲透用法，下次建索引再也不踩坑，查询速度直接飞起来！

一、用 “图书馆找书” 讲透 5 种核心索引：贴对标签才好查

哇哥拉过王二的笔记本，随手画了个简单的图书馆示意图，语气像聊家常一样：“咱们把 MySQL 的表当成书架，数据就是书，索引就是书签。不同的索引，就是不同类型的书签，适配不同的找书需求，乱贴就白搭。”

➡️ 第一种，主键索引

“第一种，主键索引，这是最核心的‘馆藏编号书签’。” 哇哥指着示意图的中心，“就像每本书唯一的馆藏号，比如‘文 A20250618001’，每本书只有一个。在 InnoDB 里，这书签最特殊 —— 书的内容（数据）就直接写在书签对应的页面上。你按馆藏号找书，找到书签就直接拿到书，不用再翻其他地方。”
王二赶紧记笔记：“那主键索引必须建吧？”
“必须的！” 哇哥点点头，“比如订单表的order_id、用户表的user_id，都得设为主键索引。而且最好用自增的bigint，别用 UUID—— 自增编号就像排队买票，按顺序来不插队；UUID 是乱序的，插一本要挪一堆书，效率低得很。”

👉 第二种，二级索引

“第二种，二级索引，就是‘分类标签’。” 哇哥笔尖一移，画了个分支，“比如图书馆里的‘散文类’‘小说类’标签，你按分类找书，先找到分类标签，标签上会写着书的馆藏号（主键），再按编号找书。二级索引就是干这个的 —— 查非主键字段时，先通过二级索引拿到主键，再回表（按主键索引）找数据。比如你要查‘user_id=1234 的所有订单’，就给user_id建个二级索引。”

📢 第三种，联合索引

“第三种，联合索引，就是‘分类 + 作者’的组合标签。” 哇哥画了个叠加的标签，“比如你要找‘散文类 - 三毛’的书，单一分类标签不够精准，组合标签就管用。联合索引是多个字段组合而成的，比如idx_user_create_time(user_id, create_time)，专门应对‘按用户 ID 和创建时间查订单’这种多条件查询。但它有个规矩 —— 最左匹配原则，必须从第一个字段开始查，不能跳着来，就像你不能跳过‘散文类’直接找‘三毛’的书。”

✅ 第四种，唯一索引

“第四种，唯一索引，就是书的‘ISBN 编号书签’。” 哇哥举了个例子，“每本正版书的 ISBN 都是唯一的，对应到数据库里，就是字段值不能重复。比如订单表的order_no（订单编号）、用户表的phone（手机号），都可以建唯一索引。它和主键索引的区别是：主键索引不允许为空，唯一索引允许为空（比如用户可未绑定手机号，存 NULL）。”

💯 第五种，全文索引

“第五种，全文索引，这是‘内容关键词书签’。” 哇哥笑了笑，“比如你想找所有提到‘撒哈拉’的书，总不能一本本翻吧？全文索引就是干这个的，专门用于长文本的模糊查询。比如商品表的product_desc（商品描述），用全文索引查‘高品质 热销’，比用LIKE '%高品质%热销%'快 10 倍都不止。”

三、实操：给订单表贴对 “书签”，查询速度飙升 100 倍

• 哇哥说着，直接上手帮王二改造订单表。全程可复制，跟着做一遍就能学会（JDK21+，MySQL8.0+）

👍 建表 + 创建索引

-- 1. 删除旧表（生产环境谨慎！先备份）DROPTABLEIFEXISTS`order`;-- 2. 新建订单表，添加正确的索引CREATETABLE`order`(`order_id`bigintNOTNULLAUTO_INCREMENTCOMMENT'订单ID（主键索引）',`user_id`bigintNOTNULLCOMMENT'用户ID',`order_no`varchar(50)NOTNULLCOMMENT'订单编号（唯一索引）',`amount`decimal(10,2)NOTNULLCOMMENT'订单金额',`status`tinyintNOTNULLCOMMENT'订单状态：0待支付/1已支付/2已取消',`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',`product_desc`varchar(500)DEFAULTNULLCOMMENT'商品描述（全文索引）',PRIMARYKEY(`order_id`)COMMENT'主键索引（聚簇索引）',KEY`idx_user_id`(`user_id`)COMMENT'二级索引：按用户ID查订单',KEY`idx_user_create_time`(`user_id`,`create_time`)COMMENT'联合索引：按用户ID+创建时间查订单',UNIQUEKEY`uk_order_no`(`order_no`)COMMENT'唯一索引：订单编号唯一',FULLTEXTKEY`ft_product_desc`(`product_desc`)COMMENT'全文索引：商品描述检索')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='订单表（索引优化版）';

🔥 插入 10 万条测试数据（模拟真实场景）

-- 创建存储过程，批量插入测试数据DELIMITER//CREATEPROCEDUREinsert_test_order()BEGINDECLAREiINTDEFAULT1;WHILEi<=100000DOINSERTINTO`order`(user_id,order_no,amount,status,product_desc)VALUES(FLOOR(1000+RAND()*9000),-- 模拟1000-9999的用户IDCONCAT('ORDER_202506_',LPAD(i,8,'0')),-- 唯一订单编号RAND()*1000+100,-- 模拟100-1100元的订单金额FLOOR(RAND()*3),-- 随机订单状态CONCAT('商品',FLOOR(RAND()*100),' 高品质 热销款 限时折扣')-- 商品描述);SETi=i+1;ENDWHILE;END//DELIMITER;-- 执行存储过程，插入数据CALLinsert_test_order();

✔️ 测试代码

importcom.mysql.cj.jdbc.MysqlDataSource;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.time.Duration;importjava.time.Instant;/** * MySQL索引性能测试（JDK21+，需引入mysql-connector-java 8.0.36+依赖） * 对比：无索引 vs 有联合索引 的查询速度 */publicclassIndexPerformanceTest{// 数据库配置（替换成自己的库信息）privatestaticfinalStringURL="jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";privatestaticfinalStringUSER="root";privatestaticfinalStringPASSWORD="root123";publicstaticvoidmain(String[]args)throwsSQLException{MysqlDataSourcedataSource=getDataSource();// 测试1：无联合索引查询（模拟王二原来的慢查询）System.out.println("=== 测试1：无联合索引查询 ===");StringnoIndexSql="SELECT * FROM `order` WHERE user_id = 1234 AND create_time > '2025-01-01'";testQueryPerformance(dataSource,noIndexSql);// 测试2：有联合索引查询（优化后）System.out.println("\n=== 测试2：有联合索引查询 ===");StringhasIndexSql="SELECT * FROM `order` WHERE user_id = 1234 AND create_time > '2025-01-01'";testQueryPerformance(dataSource,hasIndexSql);// 测试3：全文索引查询（商品描述检索）System.out.println("\n=== 测试3：全文索引查询 ===");StringfullTextSql="SELECT * FROM `order` WHERE MATCH(product_desc) AGAINST('高品质 热销')";testQueryPerformance(dataSource,fullTextSql);}/** * 测试查询性能，输出执行时间 */privatestaticvoidtestQueryPerformance(MysqlDataSourcedataSource,Stringsql)throwsSQLException{try(Connectionconn=dataSource.getConnection();PreparedStatementpstmt=conn.prepareStatement(sql)){Instantstart=Instant.now();// 执行查询并统计行数（模拟业务处理）ResultSetrs=pstmt.executeQuery();intcount=0;while(rs.next()){count++;}Instantend=Instant.now();longcost=Duration.between(start,end).toMillis();System.out.println("查询SQL："+sql);System.out.println("查询到"+count+"条数据，耗时："+cost+"ms");}}/** * 获取MySQL数据源（JDK21+适配） */privatestaticMysqlDataSourcegetDataSource(){MysqlDataSourcedataSource=newMysqlDataSource();dataSource.setURL(URL);dataSource.setUser(USER);dataSource.setPassword(PASSWORD);returndataSource;}}

🔥 测试结果：索引加持，速度飙升 100 倍

===测试1：无联合索引查询===查询SQL：SELECT * FROM`order`WHERE user_id=1234AND create_time>'2025-01-01'查询到15条数据，耗时：5280ms===测试2：有联合索引查询===查询SQL：SELECT * FROM`order`WHERE user_id=1234AND create_time>'2025-01-01'查询到15条数据，耗时：45ms===测试3：全文索引查询===查询SQL：SELECT * FROM`order`WHERE MATCH(product_desc)AGAINST('高品质 热销')查询到926条数据，耗时：38ms

王二看着结果，眼睛都亮了：“我的天，加了联合索引居然快了 100 多倍！之前慢是因为我只建了主键索引，没给查询字段贴对‘书签’。”

四、面试必问：索引基础核心题（王二笔记版）

哇哥知道王二最近在准备跳槽，特意整理了 3 道高频面试题，用大白话总结，记起来不费劲儿：

👍 面试题 1：主键索引和二级索引的区别是什么？InnoDB 为什么推荐用自增主键？

1. 区别：主键索引是聚簇索引，数据直接存在索引的叶子节点（找书签就拿书）；二级索引是辅助索引，叶子节点存主键 ID，需要回表（按主键找数据）；
2. 自增主键的原因：自增 ID 是有序的，插入数据时不会打乱索引结构，减少碎片；UUID 是乱序的，插入时要频繁调整索引，像排队时有人插队，效率低。

📢 面试题 2：联合索引的 “最左匹配原则” 是什么？举个例子说明。

最左匹配就是查联合索引时，必须从第一个字段开始，不能跳、不能反。比如联合索引idx_user_create_time(user_id, create_time)：

• 能命中：WHERE user_id=1234、WHERE user_id=1234 AND create_time>‘2025-01-01’；
• 不能命中：WHERE create_time>‘2025-01-01’（跳第一个字段）、WHERE status=1 AND user_id=1234（字段顺序反）。

➡️ 面试题 3：什么时候用唯一索引？它和主键索引的核心区别是什么？

1. 场景：需要保证字段值唯一，但允许为空的情况，比如手机号（用户可未绑定，存 NULL）、订单编号；
2. 核心区别：主键索引不允许为空，一张表只能有一个；唯一索引允许为空，一张表可以有多个。

五、总结：索引不是越多越好，贴对 “书签” 才管用

王二按照哇哥的方法，给订单表加了联合索引和全文索引，重新测试 —— 查询速度从 6 秒降到 50ms 以内，用户投诉直接消失了。他把经验总结成口诀，贴在显示器上：
“主键必建自增型，高频查询二级引；多条件查联合引，唯一约束唯一引；文本检索全文引，贴对书签快又稳。”
他拿着优化报告找领导，领导满意地点点头：“不错，不仅解决了问题，还懂了原理。” 王二心里美滋滋的，转头给哇哥泡了杯热乎的熟普。

哇哥接过茶杯，抿了一口，慢悠悠说道：
哇哥说：“索引如书签，贴错终是闲。世间万物皆有章法，数据库的索引亦如此 —— 不是越多越好，而是越精准越好。开发者的修行，不在于堆砌技术，而在于精准适配，就像这熟普，陈放得宜才醇厚，索引用得恰当才高效。”
关注我，下一篇讲 MySQL 索引的 “坑王”——7 个让索引失效的常见错误，90% 的程序员都踩过！避开这些坑，你的查询性能再上一个台阶！