mysql 多列索引学习-经典实例

------------------------

B 只能用到 c1,c2, c3排序
explain select * from t6 where c1=‘a‘ and c2=‘b‘ and c4=‘d‘ order by c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: ref
possible_keys: c1
          key: c1
      key_len: 6
          ref: const,const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)
key_len: 12 #2个索引用上( c1,c2) 2个索引 * 3字节

ps:这里c3索引用在了排序上
可以通过下面来比较
explain select * from t6 where c1=‘a‘ and c2=‘b‘ and c4=‘d‘ order by c5\G
注意观察Extra : Using filesort

------------------------

------------------------

C 只能用到 c1
explain select * from t6 where c1 = ‘a‘ and c4 = ‘d‘ group by c3,c2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: ref
possible_keys: c1
          key: c1
      key_len: 3
          ref: const
         rows: 2
        Extra: Using index condition; Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

key_len: 12 #用上1个索引( c1) 1个索引 * 3字节
Extra： Using temporary->使用到临时表

详解 有group by语句一般要先按分组字段顺序排列,如果此字段没排序好，mysql内部会先用临时表排序
explain select * from t6 where c1 = ‘a‘ and c4 = ‘d‘ group by c2,c3\G
因为查找用到c1, 正好c2是顺序,c3 不会建立临时表

----------------------

----------------------

D 只能用到 c1,  c2,c3排序
explain select * from t6 where c1 = ‘a‘ and c5 = ‘e‘ order by c2,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: ref
possible_keys: c1
          key: c1
      key_len: 3
          ref: const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)
key_len: 12 #用上1个索引( c1) 1个索引 * 3字节
Extra：发现没有用文件排序 , (c2,c3顺序)正好用上

----------------------------

----------------------------

E 只能用到 c1,c2,c3
explain select * from t6 where c1 = ‘a‘ and c2=‘b‘ and c5 = ‘e‘ order by c2,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: ref
possible_keys: c1
          key: c1
      key_len: 6
          ref: const,const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)
key_len: 12 #用上2个索引( c1,c2) 2个索引 * 3字节
Extra：发现没有用文件排序 , (c2,c3顺序)正好用上

倒过来:
explain select * from t6 where c1 = ‘a‘ and c2=‘b‘ and c5 = ‘e‘ order by c3,c2\G
Extra:没用到文件排序 ， 因为查找的时候 已经找到了c2 ,是一个常量

对比

explain select * from t6 where c1 = ‘a‘ and c5 = ‘e‘ order by c3,c2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: ref
possible_keys: c1
          key: c1
      key_len: 3
          ref: const
         rows: 2
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)
Extra: 文件排序

mysql 多列索引学习-经典实例

标签：学习   explain   rom   影响   exp   myisam   详解   tree   file