MySQL

MySQL 06 - 索引优化案例

简介:索引优化案例

1. 准备数据

首先根据下面的语句准备测试表和数据:

  • create table test (
  • id int primary key not null auto_increment,
  • c1 char(10),
  • c2 char(10),
  • c3 char(10),
  • c4 char(10),
  • c5 char(10)
  • );
  • insert into test (c1, c2, c3, c4, c5) values ('a1', 'a2', 'a3', 'a4', 'a5');
  • insert into test (c1, c2, c3, c4, c5) values ('b1', 'b2', 'b3', 'b4', 'b5');
  • insert into test (c1, c2, c3, c4, c5) values ('c1', 'c2', 'c3', 'c4', 'c5');
  • insert into test (c1, c2, c3, c4, c5) values ('d1', 'd2', 'd3', 'd4', 'd5');
  • insert into test (c1, c2, c3, c4, c5) values ('e1', 'e2', 'e3', 'e4', 'e5');

得到的数据表如下:

  • mysql> select * from test;
  • +----+------+------+------+------+------+
  • | id | c1 | c2 | c3 | c4 | c5 |
  • +----+------+------+------+------+------+
  • | 1 | a1 | a2 | a3 | a4 | a5 |
  • | 2 | b1 | b2 | b3 | b4 | b5 |
  • | 3 | c1 | c2 | c3 | c4 | c5 |
  • | 4 | d1 | d2 | d3 | d4 | d5 |
  • | 5 | e1 | e2 | e3 | e4 | e5 |
  • +----+------+------+------+------+------+
  • 5 rows in set (0.00 sec)

然后查询数据并创建索引如下:

  • mysql> create index idx_c1_c2_c3_c4 on test(c1, c2, c3, c4);
  • Query OK, 0 rows affected (0.02 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> show index from test;
  • +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  • +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • | test | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
  • | test | 1 | idx_c1_c2_c3_c4 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
  • | test | 1 | idx_c1_c2_c3_c4 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
  • | test | 1 | idx_c1_c2_c3_c4 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
  • | test | 1 | idx_c1_c2_c3_c4 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
  • +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • 5 rows in set (0.00 sec)

接下来使用上面的表来进行索引的各类案例测试。

2. 简单测试

以下四种情况,索引都会被使用,并且根据查询字段个数的递增,使用到的索引个数越来越多:

  • mysql> explain select * from test where c1 = 'a1';
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 31 | const | 1 | 100.00 | NULL |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2';
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 100.00 | NULL |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 93 | const,const,const | 1 | 100.00 | NULL |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 124 | const,const,const,const | 1 | 100.00 | NULL |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)

3. 条件顺序变换

对比上面四种情况的最后一种,我们将c3 = 'a3'c4 = 'a4'的顺序对调:

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' and c3 = 'a3';
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 124 | const,const,const,const | 1 | 100.00 | NULL |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)

会发现测试结果也用到了四个索引,这是由于虽然顺序做了改变,但是MySQL查询优化器还是会对顺序进行调整以适应已有索引。

4. 存在>条件(1)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';
  • +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
  • | 1 | SIMPLE | test | NULL | range | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 93 | NULL | 1 | 20.00 | Using index condition |
  • +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

从key_len可以看出,使用了3个索引,这是由于c3字段的范围判断的排序操作也使用了索引。

5. 存在>条件(2)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';
  • +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
  • | 1 | SIMPLE | test | NULL | range | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 124 | NULL | 1 | 100.00 | Using index condition |
  • +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

该语句的条件会优化为c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 > 'a4',所以4个索引都会用到。

6. 存在order by(1)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 20.00 | Using index condition |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.04 sec)
  • ````
  • 只会用到c1和c2字段的索引c3字段的索引会用于order by的排序,没有出现Using filesort
  • # 7. 存在order by2
  • ```sql shownum="0" linefeed="0"
  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' order by c3;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 100.00 | Using index condition |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

只会用到c1和c2字段的索引,c3字段的索引会用于order by的排序,没有出现Using filesort。

7. 存在order by(3)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' order by c4;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 100.00 | Using index condition; Using filesort |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

只会用到c1和c2字段的索引,c1和c2与c4字段之间出现了索引断层,因此导致order by并没有用到索引,出现Using filesort。

8. 存在order by(4)

  • mysql> explain select * from test where c1 = 'a1' and c5 = 'a5' order by c2, c3;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 31 | const | 1 | 20.00 | Using index condition; Using where |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

会用到c1字段的索引,c2和c3字段的索引用于排序,没有出现Using filesort。

9. 存在order by(5)

  • mysql> explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3, c2;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 31 | const | 1 | 20.00 | Using index condition; Using where; Using filesort |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

用到了c1字段的索引,但order by c3, c2的顺序与索引顺序不同,导致出现Using filesort。

10. 存在order by(6)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' order by c2, c3;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 100.00 | Using index condition |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

会用到c1和c2字段的索引,由于c2 = 'a2'条件的出现,order by c2, c3条件相当于order by c3(因为c2字段已经确定为常量了,不需要排序),因此c3字段的索引也会用于order by操作的排序,没有出现Using filesort。

11. 存在order by(7)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2, c3;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 20.00 | Using index condition; Using where |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

会用到c1和c2字段的索引,c2和c3字段的索引用于order by排序,没有出现Using filesort。

12. 存在order by(8)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3, c2;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 20.00 | Using index condition; Using where |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

会用到c1和c2字段的索引,由于有常量c2的情况,c3字段会用于order by排序,没有出现Using filesort。

13. 存在order by(9)

  • mysql> explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3, c2;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 62 | const,const | 1 | 20.00 | Using index condition; Using where |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

与第9节的对比,由于有常量c2的情况,c3字段会用于order by排序,没有出现Using filesort。

14. 存在group by(1)

  • mysql> explain select c2, c3 from test where c1 = 'a1' and c4 = 'a4' group by c2, c3;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 31 | const | 1 | 20.00 | Using where; Using index |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)

到了c1字段的索引,c2和c3字段的索引用于group by需要的排序操作,所以会有Using index(分组之前必排序)。

15. 存在group by(2)

  • mysql> explain select c2, c3 from test where c1 = 'a1' and c4 = 'a4' group by c3, c2;
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------------------------------------+
  • | 1 | SIMPLE | test | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 31 | const | 1 | 20.00 | Using where; Using index; Using temporary; Using filesort |
  • +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------------------------------------+
  • 1 row in set, 1 warning (0.09 sec)

只到了c1字段的索引,c2和c3字段由于顺序与索引的顺序不一致,导致出现Using temporary和Using filesort。

16. 各类where条件

where语句 索引是否被用到
where c1 = ‘a1’ 使用到c1的索引
where c1 = ‘a1’ and c2 = ‘a2’ 使用到c1和c2的索引
where c1 = ‘a1’ and c2 = ‘a2’ and c3 = ‘a3’ 使用到c1、c2、c3的索引
where c2 = ‘a2’ 或 where c2 = ‘a2’ and c3 = ‘a3’ 或 where c3 = ‘a3’ 没有用到
where c1 = ‘a1’ and c3 = ‘a3’ 使用到c1, 但是c3不可以,中间断了
where c1 = ‘a1’ and c2 > ‘a2’ and c3 = ‘a3’ 使用到c1和c2的索引, c3的索引不能用在范围之后,c2后断了
where c1 = ‘a1’ and c2 like ‘a%’ and c3 = ‘a3’ 使用到c1、c2、c3的索引
where c1 = ‘a1’ and c2 like ‘%a’ and c3 = ‘a3’ 只用到c1
where c1 = ‘a1’ and c2 like ‘%a%’ and c3 = ‘a3’ 只用到c1
where c1 = ‘a1’ and c2 like ‘a%aa%’ and c3 = ‘a3’ 使用到c1、c2、c3的索引