欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL性能优化[实践篇]-索引合并与复合索引

程序员文章站 2022-05-03 12:59:56
...

从上一篇创建索引的实践中，我们看到了索引给我们带来的性能提升是非常可观的。

我们上次创建的表结构非常简单，只有两三个字段，where子句查询条件只有一个字段。

实际应用场景中我们的表结构会更复杂，查询条件也会非常多。在多条件查询的情况下又如何才能用到索引呢，我们可以测试一下。

准备测试数据

创建表结构

create table tb_test(id int primary key auto_increment,
                     c1 char(1),
                     c2 char(1),
                     c3 char(1),
                     c4 char(1));

生成随机字符的函数

delimiter $$
# 生成随机字符串
create function rand_char() returns char(1)
begin
  declare CHARS char(52) default 'abcdefghijklmnopqrstuvwxyz';
  return substr(CHARS,floor(1+RAND()*52), 1);
end
$$

生成测试数据的存储过程

create procedure insert_tb_test(c int)
begin
  declare i int default 0;
  set autocommit = 0;
  repeat
    set i = i + 1;
    insert into tb_test(c1,c2,c3,c4) values(rand_char(),rand_char(),rand_char(),rand_char());
  until i = c end repeat;
  set autocommit = 1;
end
$$

delimiter ;

调用存储过程生成一百万条测试数据：

call insert_tb_test(1000000);

现在的查询场景是select * from tb_test where c1=? and c2=? and c3=? and c4=?

没有索引的情况下我们试着查询一次：

mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.61 sec)  # 0.61秒，很慢

我们可以为c1、c2、c3、c4分别创建一个索引或者为他们创建一个复合索引。

我们先来试试第一种方案。

多个单列索引

创建多个索引

alter table tb_test add index idx_tb_test_c1(c1);
alter table tb_test add index idx_tb_test_c2(c2);
alter table tb_test add index idx_tb_test_c3(c3);
alter table tb_test add index idx_tb_test_c4(c4);

创建索引后查询

mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.05 sec)  # 花了 0.05秒

mysql> explain select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: index_merge
possible_keys: idx_tb_test_c1,idx_tb_test_c2,idx_tb_test_c3,idx_tb_test_c4
          key: idx_tb_test_c2,idx_tb_test_c4,idx_tb_test_c1,idx_tb_test_c3  # 用上了四个索引
      key_len: 4,4,4,4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using intersect(idx_tb_test_c2,idx_tb_test_c4,idx_tb_test_c1,idx_tb_test_c3); Using where; Using index
1 row in set, 1 warning (0.00 sec)

我们在explain输出的内容上可以看到type为index_merge，并且附加信息上提示Using intersect(idx_tb_test_c2,idx_tb_test_c4,idx_tb_test_c1,idx_tb_test_c3);

这是MySQL5.0版后引入的的“索引合并”策略：它将每个索引查询的结果进行合并。

合并算法有三种intersect(交集)、union(并集)、sort_union(不常见)。

mysql> explain select * from tb_test where c1='A' and c2='B' or c3='C' and c4='D'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: index_merge
possible_keys: idx_tb_test_c1,idx_tb_test_c2,idx_tb_test_c3,idx_tb_test_c4
          key: idx_tb_test_c2,idx_tb_test_c1,idx_tb_test_c4,idx_tb_test_c3
      key_len: 4,4,4,4
          ref: NULL
         rows: 2629
     filtered: 100.00
        Extra: Using union(intersect(idx_tb_test_c2,idx_tb_test_c1),intersect(idx_tb_test_c4,idx_tb_test_c3)); Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_test where c1='A' and c2='B' and c3>'C' and c4<'D'\G # 范围查询
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: index_merge
possible_keys: idx_tb_test_c1,idx_tb_test_c2,idx_tb_test_c3,idx_tb_test_c4
          key: idx_tb_test_c2,idx_tb_test_c1
      key_len: 4,4
          ref: NULL
         rows: 1300
     filtered: 25.00
        Extra: Using intersect(idx_tb_test_c2,idx_tb_test_c1); Using where # 范围查询的列不会进行索引合并
1 row in set, 1 warning (0.00 sec)

在某些时候索引合并是一种优化策略，但实际上更多时候它也说明表上的索引建的很糟糕：

  • 当出现using intersect(多个AND条件)，更应该创建相关列的复合索引，而不是多个独立的单列索引。
  • 当出现using union时，通常会耗费大量的CPU和内存资源用在缓存、排序、合并等操作上。特别是有的单列索引的选择性不高，需要合并扫描返回大量的数据。
  • 更重要的是，MySQL优化器不会将这些操作算入“查询成本”，优化器只关心随机页面的读取。从而低估查询成本，可能导致执行计划还不如直接进行全表扫描。

如果在explain中看到index_merge，应该好好检查表结构和查询语句是否是最有的。

也可以通过optimizer_switch变量来关闭索引合并功能(具体参考Switchable Optimizations)。

或者使用force index、ignore index提示优化器强制使用或忽略某些索引(具体参考Index Hints)。

复合索引

删除之前的单列索引：

 drop index idx_tb_test_c1 on tb_test;
 drop index idx_tb_test_c2 on tb_test;
 drop index idx_tb_test_c3 on tb_test;
 drop index idx_tb_test_c4 on tb_test;

创建复合索引：

alter table tb_test add index idx_tb_test_c1_c2_c3_c4(c1,c2,c3,c4);

创建索引后查询：

mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
Empty set (0.00 sec)  # show profiles 显示只有0.00042275秒

mysql> explain select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4 # 用到的是复合索引
      key_len: 16  # 索引长度为16，用到了复合索引的所有列
          ref: const,const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
# 索引的使用与where条件的顺序没有任何关系，解析器将SQL解析成树后，这几个条件的层级是一样的
mysql> explain select * from tb_test where c3='A' and c1='B' and c4='C' and c2='D'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4
      key_len: 16
          ref: const,const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

很明显复合索引的速度和之前不是一个量级的。

选择合适的索引顺序

正确的索引顺序对查询性能也很重要。但是选择合适的索引顺序要从多方面考虑。

1. 范围查询放在索引最后面

mysql> select * from tb_test where c1='A' and c2<'B' and c3='C' and c4='D'\G
Empty set (0.01 sec)  # show profiles显示消耗了0.01148575秒

mysql> explain select * from tb_test where c1='A' and c2<'B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: range  # 范围查询
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4  # 用到了复合索引
      key_len: 8   # 但是只用到了索引的一半
          ref: NULL
         rows: 18572
     filtered: 1.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上面这个查询只用到了索引的一半，其中c1列精确匹配，c2列范围查询。

只针对这个SQL语句，我们需要把索引的顺序调整为c1_c3_c4_c2。

2. 索引列顺序尽量匹配order by子句的排序列顺序

之前说过B+树本身是有序的，使用B+树排序可以避免filesort。

mysql> explain select * from tb_test where c1='A' and c2='B' order by c4,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ref  # 精确匹配
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4  # 使用的复合索引
      key_len: 8    # 索引前两列精确匹配
          ref: const,const
         rows: 382
     filtered: 100.00
        Extra: Using where; Using index; Using filesort # 外排序
1 row in set, 1 warning (0.00 sec)

针对这个查询，索引的顺序应该设计为c1_c2_c4_c3。

mysql> drop index idx_tb_test_c1_c2_c3_c4 on tb_test;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tb_test add index idx_c1_c2_c4_c3(c1,c2,c4,c3);
Query OK, 0 rows affected (7.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from tb_test where c1='A' and c2='B' order by c4,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ref  # 精确匹配
possible_keys: idx_c1_c2_c4_c3
          key: idx_c1_c2_c4_c3
      key_len: 8  # 索引前两列精确匹配
          ref: const,const
         rows: 382
     filtered: 100.00
        Extra: Using where; Using index  # 没有filesort
1 row in set, 1 warning (0.00 sec)

# 范围查询会导致后面的排序列用不上索引
mysql> explain select * from tb_test where c1='A' and c2<'B' order by c4,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: range
possible_keys: idx_c1_c2_c4_c3
          key: idx_c1_c2_c4_c3
      key_len: 8
          ref: NULL
         rows: 18572
     filtered: 100.00
        Extra: Using where; Using index; Using filesort  # 又变回filesort了
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_test where c1='A' and c2<'B' order by c2,c4,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: range
possible_keys: idx_c1_c2_c4_c3
          key: idx_c1_c2_c4_c3
      key_len: 8
          ref: NULL
         rows: 18572
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

3. 索引列顺序尽量匹配group by子句顺序

没有索引的情况下，group by子句执行filesort对数据进行排序，然后在进行分组。这个过程中可能会导致全表扫描并且创建中间临时表。

MySQL性能优化[实践篇]-索引合并与复合索引

和order by子句相比，group by多了创建中间表和分组的操作，group by子句也是需要排序的。

mysql> drop index idx_c1_c2_c4_c3 on tb_test;  # 删除索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tb_test\G
*************************** 1. row ***************************
        Table: tb_test
   Non_unique: 0
     Key_name: PRIMARY  # 只剩下主键索引了
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 900858
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> explain select c1,c2 from tb_test group by c1,c2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ALL  # 全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 998722  # 预估扫描99万多行数据
     filtered: 100.00
        Extra: Using temporary; Using filesort  # 临时表；文件排序
1 row in set, 1 warning (0.00 sec)

创建索引后查询：

mysql> alter table tb_test add index idx_tb_test_c1_c2_c3_c4(c1,c2,c3,c4);
Query OK, 0 rows affected (7.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c1,c2 from tb_test group by c1,c2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: range
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4
      key_len: 8  # 使用到索引的前两列
          ref: NULL
         rows: 727
     filtered: 100.00
        Extra: Using index for group-by # 使用索引执行group by子句
1 row in set, 1 warning (0.00 sec)

# 下面这个语句会用到c1,c2列进行精确匹配，c3,c4列用来分组
mysql> explain select count(c3) from tb_test where c1='A' and c2='B' group by c3,c4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4
      key_len: 8
          ref: const,const
         rows: 382
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

# 如果group by的顺序与索引顺序不一致,group by子句仍会filesort并创建中间表
mysql> explain select count(c3) from tb_test where c1='A' and c2='B' group by c4,c3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: ref
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4
      key_len: 8
          ref: const,const
         rows: 382
     filtered: 100.00
        Extra: Using where; Using index; Using temporary; Using filesort  # 中间表;文件排序
1 row in set, 1 warning (0.00 sec)

# 范围查询也会导致索引后面的列用不上索引进行group by
mysql> explain select count(c3) from tb_test where c1='A' and c2<'B' group by c3,c4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: range
possible_keys: idx_tb_test_c1_c2_c3_c4
          key: idx_tb_test_c1_c2_c3_c4
      key_len: 8
          ref: NULL
         rows: 18572
     filtered: 100.00
        Extra: Using where; Using index; Using temporary; Using filesort # 中间表;文件排序
1 row in set, 1 warning (0.00 sec)

4. 选择性最高的列放在索引最前面

关于选择索引顺序有个经验：将选择性最高的列作为索引的最前列。在某些场景下确实很有帮助，但通常不如避免随机IO和排序那么重要。(很多时候需要全方面的考虑索引列的顺序)。

当不考虑排序和分组的时候，将选择性最高的列放在索引最前列通常是非常好的。索引的顺序将会优化where条件的查找：这样设计的索引能最快的过滤出需要的行。

create table tb_user(id int primary key auto_increment, age tinyint, gender tinyint, name varchar(16));

比如用户表中有性别、年龄、姓名这几列。很明显将性别作为索引最前列是不可取的，因为性别总共就两种。

下节待续：

假设某个表有一个联合索引(c1,c2,c3,c4)，问以下查询中只能使用该联合索引的c1,c2,c3部分的有那些

  1. where c1=x and c2=x and c4>x and c3=x
  2. where c1=x and c2=x and c4=x order by c3
  3. where c1=x and c4=x group by c3,c2
  4. where c1=? and c5=? order by c2,c3
  5. where c1=? and c2=? and c5=? order by c2,c3

参考链接：

《高性能MySQL》

https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

相关标签： MySQL 索引优化

上一篇： Aop动态代理解决问题

下一篇： 模仿PHP str_split分割汉字成数组的方法函数

推荐阅读