MySQL里联合索引怎么跟Where条件搭配用才更快点优化效果好

要理解联合索引就像是一本电话簿,它不是按单个姓氏排序，而是先按姓氏排序，姓氏相同的人再按名字排序，如果还有中间名，再按中间名排序，MySQL的联合索引也是类似的原理，它按照你创建索引时列的顺序，一层一层地建立排序规则，比如你创建了一个 (A, B, C) 的联合索引，那么数据会先按A列排序，A相同的情况下按B列排序，B也相同的情况下再按C列排序，这个根本原则决定了它如何与Where条件搭配才能高效。

核心原则：最左前缀原则

这是联合索引工作的黄金法则,意思是，你的SQL查询条件必须从联合索引的最左边的列开始，才能有效地利用到这个索引，它不要求条件里包含索引的所有列，但必须从最左边开始，并且是连续的（这里的连续指的是索引定义的顺序，不是Where条件书写的顺序）。

具体搭配场景分析（怎么用才更快）：

1. 全值匹配，效果最好 当你的Where条件中包含了联合索引的所有列，并且都使用等值查询（=, IN）时，这是最优情况，索引可以被完全利用，能快速定位到非常精确的数据范围。

   • 例子：索引是 (city, name, age)，查询是 WHERE city='杭州' AND name='张三' AND age=30。
   • 效果：MySQL可以沿着索引树，先快速找到所有city='杭州'的记录，在这些记录里找name='张三'的，最后再精确找到age=30的，效率极高，可能只需要扫描几条数据。

2. 匹配最左前缀 这是最常用的情况，你不需要用到索引的所有列，但必须从最左边的列开始。

   • 例子：索引是 (city, name, age)。
     • WHERE city='杭州' ：可以用到索引，因为从最左列city开始，索引能快速筛选出所有杭州的记录。
     • WHERE city='杭州' AND name='张三' ：也可以用索引，先按city筛，再在结果里按name筛。
   • 效果：虽然不如全值匹配精确，但因为利用了索引的有序性，能快速缩小数据扫描范围，效果仍然很好。

3. 匹配列前缀 指的是对索引列使用左模糊匹配（LIKE '前缀%'），因为索引是按值排序的，所以前缀是固定的，索引依然可以发挥作用。

   

   • 例子：索引是 (name)，查询是 WHERE name LIKE '张%'。
   • 在联合索引中：索引是 (city, name)，查询是 WHERE city='杭州' AND name LIKE '张%'。
   • 效果：MySQL可以先通过city='杭州'定位到一个范围，然后在这个范围内，利用索引对name的排序，快速找到所有姓“张”的人。但注意，如果LIKE以通配符开头（如 %张），索引就会失效，因为无法利用排序。

4. 匹配范围值 当对联合索引中的某一列进行范围查询（如 >, <, BETWEEN）后，它右边的列如果还有条件，索引可能无法被完全利用。

   • 例子：索引是 (city, name, age)。
     • WHERE city='杭州' AND name > '王五' AND age=25。
     • 过程：MySQL能用索引找到city='杭州'的所有记录，然后在这些记录里，利用索引找到name > '王五'的记录。对于age=25这个条件，索引就无能为力了，因为name已经是一个范围查询，在这个范围内的age字段在索引中是无序的，MySQL只能读取所有满足city='杭州' AND name > '王五'的记录，然后回表到主键索引（或直接在联合索引里）去逐一判断age是否等于25。
   • 效果：范围查询本身可以用到索引，但它就像一堵“墙”，会阻断联合索引中后续列的有效使用，优化时，应尽量把需要等值查询的列放在范围查询的列前面。

5. 精确匹配某一列并范围匹配另外一列 这其实是上面几种情况的组合，只要遵循最左前缀且连续，就能高效利用。

   • 例子：索引是 (city, age)，查询是 WHERE city='杭州' AND age > 20。
   • 效果：先精确找到所有杭州的记录，因为这些记录是按age排序的，所以再找age > 20的记录也非常快。

需要避免的坑（怎么用会慢）：

1. 跳过索引最左边的列 这是最致命的错误，如果查询条件没有包含联合索引的第一列，索引基本上就失效了。

   • 例子：索引是 (city, name, age)，查询是 WHERE name='张三' 或 WHERE age=30。
   • 原因：因为没有city这个“第一排序依据”，name和age在索引中是全局无序的，MySQL无法快速定位，只能进行全表扫描。

2. 中间列被跳过 如果Where条件没有按顺序提供索引列，跳过了中间的某一列，那么索引只能使用到跳过的列之前的部分。

   • 例子：索引是 (city, name, age)，查询是 WHERE city='杭州' AND age=30。
   • 效果：索引只能用到city这一列，因为跳过了name，age在索引中（在city='杭州'这个组内）是无序的，MySQL会先通过索引找到所有杭州的记录，然后必须对这些记录逐一检查age是否等于30。

3. 在索引列上做计算或函数操作 在Where条件中，对索引列使用函数（如 YEAR(create_time) = 2023）或者进行计算（如 age + 1 > 30），会导致MySQL无法使用索引。

   • 解决方法：尽量将计算转移到常量上，比如写成 create_time BETWEEN '2023-01-01' AND '2023-12-31'。

总结与实战建议

• 设计索引时：不仅要考虑哪些列需要查询，更要考虑这些列在Where条件中出现的频率和顺序，将最常用、筛选性最好（即重复值少的列，如用户名）的列放在联合索引的最左边。
• 编写SQL时：要有意识地让你的Where条件去“迎合”已经存在的联合索引，确保条件从索引的最左列开始且尽量连续，如果现有索引不满足，就要考虑新建一个更合适的联合索引。
• 使用EXPLAIN命令：这是最可靠的验证方法，在你的SQL语句前加上 EXPLAIN 关键字执行，查看结果中的 key 字段是否使用了你期望的索引，rows 字段显示了大概需要扫描的行数，可以用来判断效率。

根据阿里巴巴Java开发手册等业界实践规范,建立索引时的一个常见建议是：WHERE子句中的排序、分组或字段范围条件返回记录数量较少时，建议将相关列建立联合索引，要避免创建过多索引，因为索引会占用空间并降低写操作（增删改）的速度，核心思想就是让索引的排序特性最大化地服务于查询的筛选过程，减少需要检查的数据行数。