MySQL索引优化：五个高频实用技巧提升查询效率

MySQL索引优化其实没那么复杂，掌握好下面五个核心技巧，就能解决80%的常见问题。

用好索引是提升SQL查询效率的关键，正确的索引能让慢查询“飞起来”，反之则可能拖垮数据库性能。这里整理了五个高频且实用的优化要点，内容扎实、通俗易懂，帮你避开常见陷阱，建议收藏备用～

1. 大字段建索引？优先考虑前缀索引

当需要为较长的字符串字段（比如用户名、备注等varchar(255)字段）建立索引时，直接创建普通索引会占用大量存储空间，拖慢查询速度。这时候，前缀索引就能派上用场了！

前缀索引的核心作用是：通过只索引字段的前面一部分字符，来显著减小索引条目的大小，让单个索引页能存储更多的索引值，从而提升查询效率。那么，如何确定合适的前缀长度呢？我们可以通过计算字段的选择性来决定。执行以下SQL，当比值接近1时，对应的前缀长度就是比较合适的选择：SELECT count(distinct left(col, n)) / count(*) FROM table。

使用前缀索引需要特别注意它的局限性：它无法用于ORDER BY排序操作，排序时会失效；同时，它也不能作为覆盖索引使用，查询时可能仍然需要回表查询完整数据。

2. 讨厌回表？覆盖索引才是终极方案

很多后端开发都遇到过因“回表”导致的查询性能下降，而覆盖索引正是解决这个问题的利器！

简单来说，回表就是先从二级索引树上查到主键值后，还得再回到聚簇索引（主键索引）里，才能找到完整的行记录。而覆盖索引恰好相反——你的SQL查询语句中所需要的所有字段，都能在索引树的叶子节点上直接找到，不用再回到聚簇索引去查完整数据，这样就完全避免了耗时的回表操作。

例如，对于高频查询SELECT username, age FROM users WHERE username = “xxx”，最好的做法就是建立一个(username, age)的联合索引。有了这个覆盖索引，你想要的数据都在索引树上，无需回表，I/O操作减半，查询性能直接翻倍！

3. 主键索引：自增的才是高效的设计

在InnoDB引擎中，主键索引默认是聚簇索引，数据就存储在索引的叶子节点上。因此，主键的设计方式会直接影响数据插入和查询的效率。

当使用自增主键时，新插入的数据会按顺序追加到当前索引节点的末尾，不需要移动已有数据。页写满后会自动开辟新页，插入效率很高。但如果使用非自增主键，比如UUID或随机字符串，插入时主键值无序，新数据可能插入到数据页中间，这时就需要移动数据、甚至将数据复制到新页（即“页分裂”），这会产生内存碎片，拖慢后续查询。

此外，主键字段长度应尽量短！因为二级索引的叶子节点存储的正是主键值，主键越短，二级索引占用的空间就越小，整体效率越高。

4. 警惕索引失效的常见陷阱

明明建了索引却感觉没效果？很可能踩中了下面这些坑。牢记这几个高频场景，避免做无用功：

模糊匹配不当：使用LIKE ‘%xx’（左模糊）或LIKE ‘%xx%’（左右模糊）会导致索引失效，而LIKE ‘xx%’（右模糊）则可以正常使用索引。
对索引列做计算或函数操作：对索引列进行运算、使用函数、或进行隐式类型转换（比如varchar字段和int值比较），索引会直接失效。
联合索引未遵循最左匹配原则：例如联合索引是(a, b, c)，那么查询条件中只包含b或c时，是无法利用到这个索引的。
OR条件使用不当：如果OR前的条件是索引列，OR后的条件不是，那么整个索引可能会失效（需要保证OR前后的字段都有独立索引）。
IN列表参数过多：虽然IN通常能走索引，但MySQL优化器会计算成本。如果IN列表里的值过多，优化器可能会认为全表扫描比逐个查找索引树更快，从而弃用索引。
使用了NOT系列操作符：例如!=、<>、NOT IN、NOT LIKE 通常无法使用索引。因为B+树索引是基于“等于”或“范围”查询建立的，寻找“不等于某个值”的数据，索引树几乎无法提供加速，优化器通常会直接选择全表扫描。
ORDER BY顺序与索引顺序不一致：这属于典型的“索引未能完全发挥作用”。例如，索引为(a, b)，但查询使用ORDER BY b, a（顺序相反）或者ORDER BY a ASC, b DESC（排序方向不一致）。
字段字符集（Collation）不一致：这是最隐蔽、最让人抓狂的失效场景之一。比如表A的user_id字段是utf8mb4字符集，表B的user_id是utf8。当执行SELECT * FROM A JOIN B ON A.user_id = B.user_id时，由于字符集不同，MySQL在关联时会自动进行转换，相当于对索引列加了函数，索引瞬间失效。
范围查询阻断后续索引列：在联合索引中，如果中间某个字段使用了范围查询（>， <， between），那么该字段之后的所有索引列将全部失效。例如，索引为(a, b, c)。当执行WHERE a = 1 AND b > 10 AND c = 2时，a和b能走索引，但c无法被利用。因此在设计联合索引时，应尽量将需要进行范围查询的字段放在最后。

5. 索引列尽量设置为NOT NULL

别小看NULL值，它会直接影响索引效率和存储空间。建议将所有索引列都设置为NOT NULL，并给予一个默认值（如0或空字符串）：

增加优化器负担：NULL值会让索引统计、值比较变得更复杂，比如COUNT统计时会忽略NULL值，影响优化器的判断。
浪费存储空间：NULL值会占用额外物理空间，InnoDB至少会用1字节来标记列为NULL，增加了存储压力。

6. 最后总结

MySQL索引优化无需追求复杂，把这5个基础技巧用熟，就能解决80%的索引相关问题。核心原则就是：让索引更小、更高效、不失效，减少不必要的I/O和数据移动。