做后端开发的同学，几乎每天都会和MySQL打交道，而索引是提升MySQL查询性能的关键。但很多人存在一个误区，觉得索引建得越多越好，其实不然，建错索引不仅不能提升性能，反而会降低写入速度，甚至导致查询索引失效，全表扫描。今天就结合5个真实生产案例，跟大家聊一聊MySQL索引优化的实战技巧，避开常见坑点，让你的查询速度翻倍。
首先跟大家明确一个核心原则：索引的本质是帮MySQL快速定位数据，减少扫描的数据量，其底层是B+树结构，所以索引的设计必须贴合查询场景，否则就会失效。常见的索引失效场景有：忽略最左前缀原则、在索引列上使用函数、隐式类型转换、OR条件使用不当、LIKE前缀通配符等，这些都是我们日常开发中最容易踩的坑。
第一个案例，最左前缀原则被忽略，复合索引形同虚设。某电商平台的订单表orders，有500万条数据，查询条件经常是user_id+status+create_time，开发同学建立了复合索引idx_user_status_time，但是查询时却只用到了status和create_time两个字段，写的SQL是SELECT * FROM orders WHERE status='PAID kl.A2C.ORG.cN ' AND create_time>'2026-01-01'。这种情况下，MySQL无法使用复合索引，只能进行全表扫描，查询时间长达2.3秒。
优化方案有两个：一是调整查询语句，带上user_id字段，比如SELECT kG.A2C.ORG.cN * FROM orders WHERE user_id=123 AND status='PAID' AND create_time>'2026-01-01'，这样就能正常使用复合索引；二是如果业务上经常需要单独按status和create_time查询，就单独建立索引idx_status_time。优化后，查询时间从2.3秒降至15ms，性能提升了150多倍。
第二个案例，索引列上使用函数，索引直接失效。用户表users的phone字段有索引，开发同学需要查询以138开头的手机号，写的SQL是SELECT * FROM users WHERE SUBSTRING(phone,1,3)=' ki.A2C.ORG.cN 138'。这种情况下，MySQL会对索引列phone进行函数操作，导致索引失效，全表扫描100万条数据，查询时间长达1.8秒。
正确的做法是避免在索引列上使用函数，改用范围查询，SQL调整为SELECT * FROM users WHERE phone LIKE '138%'，这样就能正常使用索引，查询时间降至50ms。同理，查询某一天的订单时，避免使用DATE(create_time)='2026-03-10'，改用create_time>='2026-03-10 00:00:00' AND create_time<'2026-03-11 00:00:00 kh.A2C.ORG.cN '，同样能避免索引失效。
第三个案例，隐式类型转换，索引静默失效。订单表orders的order_no km.A2C.ORG.cN 字段是VARCHAR类型，并且建立了索引，但开发同学查询时却写成了SELECT * FROM orders WHERE order_no=20260310001，把字符串类型当成了数字类型。MySQL会自动对order_no进行隐式类型转换，导致索引失效，全表扫描500万条数据，查询时间长达3.1秒。
优化方法很简单，保持查询条件的数据类型与索引列一致，给order_no加上引号，SQL调整为SELECT * FROM orders WHERE order_no='20260310001'，优化后查询时间降至8ms，性能提升了380多倍。这种问题最隐蔽，开发环境数据量少感觉不到，一旦上生产，数据量增大就会直接出现性能问题，代码审查时一定要注意类型匹配。
第四个案例，OR条件使用不当，索引失效。商品表products需要查询状态为1或分类ID为100的商品，开发同学写的SQL是SELECT * FROM products WHERE status=1 OR category_id=100。虽然status和category_id分别有索引，但OR连接的条件中，只要有一个字段没有索引，整个查询就会走全表扫描，200万条数据查询时间长达2.5秒。
优化方案有两个：一是用UNION ALL代替OR，拆分查询语句，写成 ke.A2C.ORG.cN SELECT FROM products WHERE status=1 UNION ALL SELECT FROM products WHERE category_id=100 kj.A2C.ORG.cN ；二是如果查询模式固定，建立复合索引idx_status_category。优化后，查询时间降至120ms，性能提升了20倍。需要注意的是，OR不是不能用，关键是确保OR两边的字段都有索引，并且最好用EXPLAIN查看执行计划，确认索引是否生效。
第五个案例，SELECT 导致覆盖索引失效。日志表logs有500万条数据，开发同学只需要查询id和create_time，但却写了SELECT FROM logs WHERE user_id=123 ORDER BY create_time DESC LIMIT 10 kf.A2C.ORG.cN 。虽然有(user_id, create_time)的复合索引，但SELECT *会导致MySQL kn.A2C.ORG.cN 回表查询所有字段，失去覆盖索引的优势，查询时间长达800ms。
优化方法很简单，只查询需要的字段，利用覆盖索引，SQL调整为SELECT id, create_time FROM logs WHERE user_id=123 ORDER BY create_time DESC LIMIT 10。优化后，查询时间降至30ms kk.A2C.ORG.cN ，Extra字段从Using index condition变成Using index，完全利用了覆盖索引，性能提升了26倍。
最后总结一下，MySQL索引优化的核心是“贴合查询场景”，建索引前先分析查询语句，明确查询条件和排序字段，避免盲目建索引；同时避开常见的索引失效场景，用EXPLAIN工具验证索引是否生效。记住，好的索引是提升查询性能的关键，而不合理的索引只会拖慢服务，日常开发中一定要多总结、多测试，让索引真正发挥作用。
