多列索引实验

最新推荐文章于 2025-05-16 01:00:00 发布

原创最新推荐文章于 2025-05-16 01:00:00 发布 · 423 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

71 篇文章

订阅专栏

本文通过具体示例深入探讨了MySQL中多列索引的使用规则，包括不同查询条件下索引的选择与利用，以及ORDER BY子句如何影响索引的有效性。

回顾：简单介绍多列索引生效规则

先建表

create table t1(
    c1 char(1) not null default '',
    c2 char(1) not null default '',
    c3 char(1) not null default '',
    c4 char(1) not null default '',
    c5 char(1) not null default '',
    key(c1,c2,c3,c4)
)engine myisam charset utf8;
insert into t1 values ('a','b','c','d','e');
insert into t1 values ('a','B','c','d','e');
insert into t1 values ('a','b','C','d','e');
insert into t1 values ('a','b','c','D','e');
insert into t1 values ('a','b','c','D','E');

c1,c2,c3,c4列有一个复合索引。

mysql> explain select * from t1 where c1='a' and c2='b' and c4>'a' and c3='c'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 9
          ref: const,const,const
         rows: 2
     filtered: 33.33
        Extra: Using index condition
1 row in set, 1 warning (0.02 sec)

mysql> explain select * from t1 where c1='a' and c2='b' and c4='a' order by c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 6
          ref: const,const
         rows: 4
     filtered: 20.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c1='a' and c2='b' and c4='a' order by c5\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 6
          ref: const,const
         rows: 4
     filtered: 20.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

可以看出order by c5的时候Using filesort，要二次排序

mysql> explain select * from t1 where c1='a' and c5='a'  order by c2,c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 3
          ref: const
         rows: 4
     filtered: 20.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

这里为什么没有Using filesort？，是因为order by c2,c3 是排序好的。

那么要是order by c3,c2，优先安装c3来排序呢？

mysql> explain select * from t1 where c1='a' and c5='a'  order by c3,c2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 3
          ref: const
         rows: 4
     filtered: 20.00
        Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c1='a' and c2='b' and c5='a'  order by c3,c2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 6
          ref: const,const
         rows: 4
     filtered: 20.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)