mysql问题和理解

参考文章：MySQL的索引类型和左前缀索引，这里写链接内容，EXPLAIN 命令详解

---

问题一

假设某个表有一个联合索引（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 where c1=? and c2=? and c5=? order by c2,c3

有谁知道下面A-E能否可以使用索引！！为什么？

语句	Are
A where c1=x and c2=x and c4>x and c3=x	使用了c1,c2,c3,c4
B where c1=x and c2=x and c4=x order by c3	使用了c1,c2。没用上c3，c4
C where c1=x and c4= x group by c3,c2	使用了c1。没用上c2，c3，c4
D where c1=? and c5=? order by c2,c3	使用了c1。没用上c2，c3，c4
E where c1=? and c2=? and c5=? order by c2,c3	使用了c1，c2，c3。没用上c4

create table t4 (
    c1 tinyint(1) not null default 0,
    c2 tinyint(1) not null default 0,
    c3 tinyint(1) not null default 0,
    c4 tinyint(1) not null default 0,
    c5 tinyint(1) not null default 0,
    index c1234(c1,c2,c3,c4)
);
insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);
mysql版本5.6.17

选项A

mysql> EXPLAIN SELECT * FROM t4 where c1=1 and c2=2 and c4>4 and c3=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: range
possible_keys: c1234
          key: c1234
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

选项B

mysql> EXPLAIN SELECT * FROM t4 where c1=1 and c2=2 and c4=3 order by c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 2
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

选项C

mysql> EXPLAIN SELECT * FROM t4 where c1=1 and c4=3 group by c3,c2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 1
          ref: const
         rows: 1
        Extra: Using index condition; Using where; Using temporary; Using fileso
rt
1 row in set (0.00 sec)

Using index，该值表示相应的select操作中使用了覆盖索引（Covering Index）
MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件
包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）
注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

选项D

mysql> EXPLAIN SELECT * FROM t4 where c1=1 and c5=3 order by c2,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 1
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

选项E

mysql> EXPLAIN SELECT * FROM t4 where c1=1 and c2=2 and c5=3 order by c2,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 2
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)