mysql 索引和orderby

来源：互联网发布：2017网络效应判断题编辑：程序博客网时间：2024/06/05 16:30

create table T(
k int unsigned not null auto_increment,
a date,
b varchar(24),
c int,d varchar(24),
primary key(k),unique key a_index (a DESC,b DESC),
key k1(b),key k2(c),key k3(d));

explain select b from T WHERE b like 'aaa%'\G;

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: T
   partitions: NULL
         type: index
possible_keys: k1
          key: k1
      key_len: 75
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

explain select a,b from T WHERE a='2015-10-25' ORDER BY b ASC,c ASC\G;

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: T
   partitions: NULL
         type: ref
possible_keys: a_index
          key: a_index
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

explain select a,b,c from T WHERE a='2015-10-25' ORDER BY b ASC\G;

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: T
   partitions: NULL
         type: ref
possible_keys: a_index
          key: a_index
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

explain select a,b,c from T WHERE a='2015-10-25' ORDER BY a,b\G;

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: T
   partitions: NULL
         type: ref
possible_keys: a_index
          key: a_index
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

0 0