联合索引有哪些讲究?

电玩女神 2023-02-12 07:29 33阅读 0赞

## 1. 认识联合索引 ##

联合索引：是指对表上的多个列进行索引。适合where条件中的多列组合，在某些场景可以避免回表。  
如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTg2MDE1_size_16_color_FFFFFF_t_70]  
在进行order by操作时，联合索引的字段数量大于1，比如上图就有a和b两个字段，与单个字段的B+树一样，也是按照字段排序的。比如图中a、b两个字段的值都是(1,1),(1,2),(1,3),(2,1),(2,2),(2,3)，是按照(a,b)进行排序的。因此对于a、b两个字段都作为条件时，查询是可以走索引的；对于单独a字段查询也是可以走索引的。但是对于b字段单独查询就走不了索引了，因为b字段对应的值是1，2，3，1，2，3，显然不是有序的，所以走不了b字段的索引。  
所以当使用联合索引的时候：

*  在where条件中，经常同时出现的列放在联合索引中；
 *  把选择性最大的列放在联合索引的最左边；

## 2. 联合索引使用分析 ##

### 2.1 可以完整用到联合索引的情况 ###

*  a、b、c为一个联合索引，因此当联合索引各字段都作为条件时

select * from t11 where a=1 and b=1 and c=1;

*  各字段的位置不会影响联合索引的使用

select * from t11 where c=1 and b=1 and a=1;

*  当联合索引前面的字段使用了范围查询，后面的字段作为条件时仍然可以使用完整的联合索引

select * from t11 where a=2 and b in (1,2) and c=2;

*  联合索引前面的字段作为条件时，对后面的字段做排序可以使用完整的联合索引

select * from t11 where a=1 and b=2 order by c;

*  对联合索引第一个字段做条件筛选时，对后面两个字段做排序可以使用完整的联合索引

select * from t11 where a=1 order by b,c;

*  对联合索引的字段同时做排序时，排序的是三个字段要跟联合索引中的三个字段完全一致，而且查询出的字段只能是联合索引中的字段，可以完整的用到联合索引

### 2.2 只能使用部分联合索引的情况 ###

*  当条件只包含联合索引的前面部分字段时，可以用到部分联合索引

select * from t11 where a=1 and b=1;

*  对于联合索引a，b，c，如果条件中只包含a和c，则只能用到联合索引中a的索引。c这里是用不了索引的。联合索引a、b、c相当于单个索引a、联合索引a、b，联合索引a、b、c三种索引，称为联合索引的最左原则。

select * from t11 where a=1 and c=1;

*  当联合索引前面的字段使用了范围查询，对后面的字段排序使用不了索引排序，也就是说只能用到联合索引的前面两个字段a和b的索引

### 2.3 可以用到覆盖索引的情况 ###

覆盖索引就是从辅助索引中就可以查询到结果，不需要回表查询聚集索引中的记录。通过覆盖索引，不需要回表扫描聚集索引，因此可以减少SQL执行过程中的IO次数。

*  通过a字段上的条件，去联合索引idx\_a\_b\_c的索引树上可以直接找到b字段和c字段的值，不需要回表，因此下面的SQL语句使用了覆盖索引
    
        select b,c from t11 where a=3;
 *  通过a和b字段的值可以字节找到c的值，因此下面SQL语句使用了覆盖索引
    
        select c from t11 where a=1 and b=1 ;
 *  通过a、b、c三个字段的值，去联合索引树中的叶子节点找到主键id，不需要回表，因此下面SQL语句使用了覆盖索引
    
        select id from t11 where a=1 and b=1 and c=1;

### 2.4 不能使用联合索引的情况 ###

*  如果只使用联合索引后面的字段作为条件查询，则使用不了联合索引，因为联合索引的最左匹配原则，如果第一个字段在条件中没有出现，那么联合索引的后面所有字段作为条件都无法使用这个联合索引
    
        select * from t11 where b=1;
 *  对联合索引后面的字段做排序操作，也使用不了联合索引
    
        select * from t11 where c=1;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTg2MDE1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200525134600214.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTg2MDE1,size_16,color_FFFFFF,t_70