简介
本文介绍MySQL的联合索引(也可以称为:组合索引、复合索引)的用法。
MySQL一次查询只能使用一个索引。如果要对多个字段使用索引,需要建立复合索引。
联合索引的原理
联合索引是对多个列进行索引。
- 联合索引也是一棵B+树。
- 联合索引的键值数量不是1,而是大于等于2。
- B+树在对第一个索引排序的基础上,对第二个索引排序
- 联合索引遵循最左前缀(最左匹配)原则。
假定上图联合索引的为(a,b),B+树在对索引a排序的基础上,对索引b排序。所以数据按照(1,1), (1,2), (2,1), (2,4), (2,4), (3,1), (3,2)顺序排放。
从整体来看
- a是有序的:1,1,2,2,3,3
- b是无序的:1,2,1,4,1,2
从局部来看
当a的值确定的时候,b是有序的。例如a = 1时,b值为1,2是有序的状态。当a=2时候,b的值为1,4也是有序状态。
总结
- WHERE a = 1 AND b = 2
- a, b字段能用到联合索引。
- 原因:当a的值确定的时候,b是有序的。
- WHERE b = 2
- 此时b字段不能能用到联合索引。
- 原因:b不是有序的
- WHERE a > 1 AND b = 2
- a字段能用到索引,b字段用不到索引。
- 原因:a的值此时是一个范围,不是固定的,在这个范围内b值不一定是有序的,因此b字段用不上索引。
综上所述:只有保证最左匹配原则,才能用上联合索引。
联合索引有效的情景
假设:创建联合索引:INDEX index_name (a, b);
以下情景都走索引
- SELECT * FROM table_name WHERE a=XX
- 会使用索引。
- SELETE * FROM table_name WHERE a=XX AND b=YY
- 会使用(a,b)联合索引的
- SELECT * FROM table_name WHERE b=YY AND a=XX
- 这条语句不符合最左匹配原则。但由于查询优化器的存在,MySQL优化器会自动调整where后的a,b的顺序与索引顺序一致。
联合索引中可以将唯一性最高的列放在索引最前面。例如:在一个公司里以age 和gender为索引,显然age要放在前面,因为性别就两种选择男或女,选择性不如age。
联合索引失效的情景
假设:创建联合索引:INDEX index_name (a, b);
以下情景都不走索引
- SELECT * FROM table_name WHERE b=YY
- 叶子节点的b值为1,2,1,4,1,2,它不是有序的,因此不能使用(a,b)联合索引。
- SELECT * FROM table_name WHERE a>XX AND b=YY
- 此处只会用到a索引,不会用到b索引。
- 对于联合索引,会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
- 比如:a = 1 AND b = 2 AND c > 3 AND d = 4, 若建立(a,b,c,d)索引,d用不到索引。
- 使所有索引都有效的方案:将c放到建立索引的语句的最后,例如:建立(a,b,d,c)的索引(a,b,d的顺序可以任意调整),这样执行sql的时候,优化器会帮我们调整WHERE后a,b,c,d的顺序(将c放到最后),让我们用上索引。
请先
!