MySQL联合索引深度解析

题目:
假设某个表有一个联合索引(c1,c2,c3,c4),以下那个只能使用联合索引的c1,c2,c3部分:
A.where c1=x and c2=x and c4>x and c3=x
B.where c1=x and c2=x and c4=x order by c3
C.where c1=x and c4=x group by c3,c2
D.where c1=? and c5=? order by c2,c3
E.wherre c1=? and c2=? and c5=? order by c2,c3
创建一张表并插入数据:
root@db5.7.18[test]> use test;
Database changed
root@db5.7.18[test]> create table t(c1 char(10),c2 char(10),c3 char(10),c4 char(10),c5 char(10)) engine innodb charset utf8;
Query OK, 0 rows affected (0.07 sec)


root@db5.7.18[test]> insert into t values('a1','a2','a3','a4','a5');
Query OK, 1 row affected (0.00 sec)


root@db5.7.18[test]> insert into t values('b1','b2','b3','b4','b5');
Query OK, 1 row affected (0.00 sec)


root@db5.7.18[test]> select * from t;
+------+------+------+------+------+
| c1   | c2   | c3   | c4   | c5   |
+------+------+------+------+------+
| a1   | a2   | a3   | a4   | a5   |
| b1   | b2   | b3   | b4   | b5   |
+------+------+------+------+------+
2 rows in set (0.00 sec)


root@db5.7.18[test]> root@db5.7.18[test
    -> \c
root@db5.7.18[test]> alter table t add index c1234(c1,c2,c3,c4);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


看一下题目第一个选项:
A.where c1=x and c2=x and c4>x and c3=x
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c4>'a4' and c3='a3'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: c1234
          key: c1234
      key_len: 124
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
优化器会将上面的SQL转换为以下等值查询:
select  *  from t where c1='a1' and c2='a2' and c3='a3' and c4>'a4' \G;
说明A选项使用了联合索引,进行范围查询,说明C4的索引使用到了,索引长度为124
索引长度计算方法:
如果列是字符型,则还需要考虑字符集,如某列的定义是char(10),且是utf8,不能为空,则key_len为10 * 3 ,可以为空则key_len为10*3+1
key_len=(10*3+1)+(10*3+1)+(10*3+1)+(10*3+1)=124


如果在联合索引中,将C2不放到查询条件中,会是什么情况?
root@db5.7.18[test]> explain select * from t where c1='a1'  and c4>'a4' and c3='a3'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 31
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引长度:key_len=(10*3+1) 说明只使用了联合索引的C1
索引长度由124变成31,说明只使用了c1的索引,后面的C2,C3,C4索引都没有用到,因为C2变成了一个未知数,导致后成的所有索引都无法使用


如果C3没有出现在WHERE条件中,会是什么情况?
root@db5.7.18[test]> explain select * from t where c1='a1' and  c2='a2' and c4>'a4' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 62
          ref: const,const
         rows: 1
     filtered: 50.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引长度:key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1,C2,其它索引没有使用到






看一下题目第二个选项:
B.where c1=x and c2=x and c4=x order by c3
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 62
          ref: const,const
         rows: 1
     filtered: 50.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引长度:key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1,C2,其它索引没有使用到,没有用到全部的联合索引
这个里order by c3为什么没有用到临时表排序?
因为在联合索引中,C3的数据是有序的,所有排序不需要通过临时表,直接在索引上就可以完成排序,所有不需要使用临时表




看一下题目第三个选项:
C.where c1=x and c4=x group by c3,c2
root@db5.7.18[test]> explain select c3,c2 from t where c1='a1' and c4='a4' group by c3,c2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 31
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using where; Using index; Using temporary; Using filesort
索引长度:key_len=(10*3+1)  说明只使用了联合索引的C1,其它索引没有使用到,没有用到全部的联合索引
看到Using temporary; Using filesort,这是为什么呢?
看一下联合索引是有序的,它的顺序是C1,C2,C3,C4,而SQL执行过程是先执行group by 分组函数,再通过分组后返回值
而分组的顺序不是联合索引的顺序,就里要通过临时表进行排序。


如果分组的前台调换一下会怎么样?
root@db5.7.18[test]> explain select c3,c2 from t where c1='a1' and c4='a4' group by c2,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 31
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using where; Using index
索引长度:key_len=(10*3+1)  说明只使用了联合索引的C1,其它索引没有使用到,没有用到全部的联合索引
可以看到,现在没有使用临时表排序,说明执行计划是直接通过联合索引排序,因为索引是有序的,可以一边扫描索引,一边进行分组
不会使用到中间的临时表




看一下题目第四个选项:
D.where c1=? and c5=? order by c2,c3
root@db5.7.18[test]> explain select * from t where c1='a1' and c5='a5' order by c2,c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 31
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using index condition; Using where
索引长度:key_len=(10*3+1)  说明只使用了联合索引的C1,其它索引没有使用到,没有用到全部的联合索引
按C2,C3联合索引的顺序来排序,所有不会使用到中间临时表,直接在联合索引上进行排序,直接输出




看一下题目第五个选项:
E.where c1=? and c2=? and c5=? order by c2,c3
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c2,c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 62
          ref: const,const
         rows: 1
     filtered: 50.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
 索引长度:key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1 C2,其它索引没有使用到,没有用到全部的联合索引
按C2,C3联合索引的顺序来排序,没有使用到中间的临时表.


再看下面的SQL,order by c3,c2,与联合索引的顺序不一样,看一下会发生什么?
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c3,c2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 62
          ref: const,const
         rows: 1
     filtered: 50.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
 索引长度:key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1 C2,其它索引没有使用到,没有用到全部的联合索引
--注意:key_len是表示得到结果集所使用的选择的索引的长度,但不包括order by,也就是说,如果order by也使用了索引则key_len则不计算在内。
为什么这次没有用到中间表,而是直接通过索引排序呢?
原因是:WHERE条件中,C2='a2'的值已确定,所有在排序的时候,C2就变成了一个常量,而C3在联合索引中又是有序的,所有可以直接通过索引排序,不需要使用临时表来排序


总结:
索引左前缀规则:
如(f1,f2,f3,fN)的复合索引
where 条件按建立的顺序来使用索引(不代表and条件必须要按顺序来写)
如果中间某列没有条件,或like条件,导致后面的列索引使用不了
索引也能用于排序和分组
所有,我们的order by ,group by,如果能针对有顺序的表进行,可以避免使用临时表或文件排序,即,我们的order by ,group by 按顺序使用索引的列,可发挥索引的作用。


分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS