mysql索引优化有哪些技巧？失效原因是什么

mysql索引优化的常用类型

说起mysql索引优化，得先知道它有哪些“帮手”，就像玩游戏选英雄，不同类型的索引有不同的技能，得选对了才能发挥最大作用。B+树索引是最常用的，它像一棵倒挂的大树，叶子节点串成链表，范围查询时特别方便，比如查“id在100到200之间的数据”，它能顺着链表快速找到。哈希索引则像个字典， key-value直接映射，单值查询速度飞起，但它不支持范围查找，要是你想查“age>30”这种,它就帮不上忙了。

还有全文索引，专门对付文本内容，比如文章摘要里搜“人工智能”，它能像搜索引擎一样找出包含关键词的记录，我之前帮朋友优化博客数据库，他文章表有几十万条数据，搜标题关键词慢得要死，加了全文索引后，查询时间从3秒降到了0.1秒，他当时激动得差点请我喝奶茶。空间索引比较小众，适合存储地理位置数据，比如外卖平台查“5公里内的商家”,用它就能快速定位。

mysql索引优化失效的常见原因

索引这东西看着好用，实际用起来却经常“掉链子”，我去年碰到个坑：有个订单表，明明给user_id加了索引，可查“user_id=123 and status='paid'”时就是不走索引，后来才发现，索引字段用了函数或表达式，比如之前有人写“left(user_id,3)='123'”，数据库没法直接用索引，只能全表扫描，还有模糊查询以%开头，像“like '%abc'”，索引也会失效，就像你翻字典从中间开始找,目录完全没用。

另一个常见问题是隐式类型转换，比如字段是int型，查询时传了字符串“123”，数据库会偷偷转类型，索引直接罢工，我同事就吃过这亏，他写“where phone='13800138000'”，结果phone字段是bigint，索引没生效，查了半天才发现是类型不对，还有or连接非索引字段，where id=1 or name='张三'”，如果name没索引，整个查询就会放弃id的索引，直接全表扫,坑得很。

mysql索引优化的实用操作技巧

想让索引好好干活，得学会“调教”它，首先是合理建联合索引，遵循“最左前缀原则”，比如建了“(a,b,c)”的索引，查“a=1”“a=1 and b=2”“a=1 and b=2 and c=3”都能用，但查“b=2”“a=1 and c=3”就不行，我之前给一个电商表建索引，把“user_id, order_time”设为联合索引，查用户最近订单时,速度比单索引快了3倍。

然后要定期删除冗余索引，有些表用久了，索引堆得像杂物间，比如同时有“(a)”和“(a,b)”的索引，后者其实包含了前者的功能，留着只会拖慢写入速度，我上周帮公司清理数据库，删了5个冗余索引， insert操作的响应时间立马降了一半，还有避免索引选择性太低，比如给“性别”这种只有“男/女”的字段建索引，索引树几乎和全表一样大,还不如全表扫。

哦对了，用explain分析执行计划是个好习惯，每次写复杂查询前，跑一下explain，看看key列有没有显示用到的索引，rows列预估扫描行数多不多，我刚开始学优化时，写了个嵌套子查询，explain一看rows有10万，吓一跳，后来改成join，rows直接降到100,效率差了1000倍。

mysql索引优化的适用场景分析

索引不是万能药，得看场景用。读多写少的表最适合加索引，比如商品表、用户表，查询频繁但修改少，索引能明显提速，我之前做的资讯网站，文章表每天查询几十万次，但更新只有几十次，加了标题和分类的索引后，页面加载速度快了一倍,用户投诉都少了。

相反，写多读少的表就要谨慎，比如日志表、流水表，每秒都在插入数据，索引会让写入变慢，我朋友公司的支付流水表，加了5个索引，结果高峰期insert卡得要死，后来只留了主键索引，写入速度才恢复正常，还有小表没必要加索引，比如只有几百行的配置表，全表扫也就一瞬间,加索引反而浪费空间。

范围查询多的场景适合B+树索引，比如订单表按时间范围查；精确匹配多的场景用哈希索引更合适，比如用户登录查账号密码，之前帮一个游戏公司优化，他们用户登录表用了哈希索引，查账号速度比B+树快了20%,玩家登录加载时间缩短了不少。

mysql索引优化与同类工具对比优势

和其他数据库比，mysql的索引优化有自己的“绝活”，先跟PostgreSQL比，mysql的B+树索引在范围查询和排序上更高效，我之前做过测试，同样一张100万行的订单表，查“order_time between '2023-01-01' and '2023-01-31'”，mysql用B+树索引耗时0.05秒，PostgreSQL用默认索引要0.12秒,差距还挺明显。

再看MongoDB，它的索引虽然支持文档类型，但事务支持不如mysql，如果是电商订单这种需要强事务的场景，mysql的索引优化结合事务功能，能保证数据一致性，我之前帮一个生鲜平台做项目，他们用MongoDB存订单，索引优化后查询快了，但偶尔出现订单状态不一致，后来换成mysql，索引+事务,问题直接解决。

还有SQL Server，它的索引功能强大，但配置复杂，对新手不友好，mysql的索引优化更“接地气”，比如用“show index from 表名”就能看索引情况，建索引的语法也简单，“create index idx_name on 表名(字段)”，小白也能快速上手，我教过一个刚毕业的实习生，他半天就学会了给公司的用户表建索引,还优化了几个慢查询。

mysql索引优化的实际案例分享

说个我自己的真实经历吧，去年帮一家做在线教育的公司优化数据库，他们有个“课程学习记录表”，存了500多万条学生的学习进度，老师查“某课程最近7天的学习人数”时，页面要加载5分钟，学生抱怨等得花儿都谢了，我先看表结构，发现只给course_id加了索引，而查询条件是“course_id=123 and learn_time >= '2023-10-01'”,learn_time没在索引里。

我当时想，建个联合索引“(course_id, learn_time)”应该就行，但试了一下，查询还是慢，用explain一看，rows列显示要扫30万行，仔细一看，原来learn_time是datetime类型，而查询里写的是“learn_time >= '2023-10-01'”，数据库得把字符串转成datetime，索引又失效了！把查询条件改成“learn_time >= str_to_date('2023-10-01', '%Y-%m-%d')”，再跑explain，rows直接降到5000，查询时间从5分钟变成了0.3秒，老师当场拍大腿：“早知道这么简单，我就不用天天被学生催了！”

后来我又发现他们有个冗余索引“(user_id)”和“(user_id, course_id)”，删了前者后，学生完成学习记录的提交速度快了30%，服务器CPU占用也降了不少，现在他们数据库跑了大半年，没再出现慢查询问题,老板还特地给我发了个大红包呢。