MySQL--a＞? and b=? order by c该如何建索引效率最高

侯晓阳丶

已于 2024-08-20 16:21:45 修改

阅读量1k

点赞数 17

分类专栏： MySQL 文章标签： mysql 数据库 linux 测试用例 dba

于 2024-08-20 13:27:13 首次发布

本文链接：https://blog.csdn.net/h_3369/article/details/141355815

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

最近遇到一个这么一个问题，有一个语句，查询的where条件为，select a,b,c from table_name where a>xxx and b=xxx order by c；那这种情况该如何正确建造联合索引？有人说建联合索引(a,b,c)。也有说(a,c,b)、(b,a,c)、(b,c,a)甚至还有(b,c)。还各有各的说法，有说等值优先，又有说。开销最小的放在最后面。等等，可是到底应该如何建造联合索引，才能有最小的开销，最好的效率，看来只能实际测试一下了。

准备环境

1.先使用sysbench创建一个测试表，先写入100w条数据。

root # sysbench /usr/share/sysbench/oltp_update_index.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=sbtest --table-size=1000000 --tables=1 --threads=10 --report-interval=3 --time=20  prepare

2.先看一下表结构,和数据。

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4


mysql> select * from sbtest1 limit 10;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 504773 | 46953504341-24827434769-14900357301-22216603386-59015331152-64421594353-91063882286-20886127030-43555962287-78841524137 | 93637428753-19078512484-07499046167-90467648315-23800958676 |
|  2 | 383019 | 95718881762-40242328339-05770398751-81456272523-88750816056-12849082499-99426386575-67936738491-05967978445-48490321969 | 28062089678-53016824652-82043772113-00345737256-89531328849 |
|  3 | 503847 | 01038385872-50361705817-46318905997-76679583539-88685166918-98634648550-40413039627-46247204716-09242720804-66101780709 | 24034894203-99292672185-45043562307-83346840810-30785340141 |
|  4 | 499704 | 93953438013-23388274922-95983341481-57221053014-04363900317-67253124803-28970744159-73839965368-87044223191-51910643768 | 06048066221-15243579179-69120208226-58470567091-17594624310 |
|  5 | 504333 | 71261094016-71756798329-41630209368-07219841001-81466827148-40549506058-53017560040-16501082010-89928032477-46063916453 | 00213142226-22736525923-92307256421-88250677601-39977405598 |
|  6 | 503552 | 32753709874-02382056451-81693154231-27180260479-19599038117-11097832377-93022647238-28037030398-75145265570-28677929900 | 72055222201-67390314106-05261498936-44609701627-90239230652 |
|  7 | 406374 | 64005369708-01328903521-83628938492-58715179738-86878139875-08682365457-20667146951-39733142900-50703403349-70540498661 | 51830182144-85315866878-16093318939-33670981339-30608544097 |
|  8 | 499282 | 49820770300-84763632943-74879578402-29401740516-28978918607-99083669177-42065952807-09235614265-01166968728-26325800927 | 85626280648-53691844105-62670548340-12193503415-17217200408 |
|  9 | 501253 | 97138737797-22390438815-38895931199-24202958945-61107229413-58522735620-64288660669-68685322850-20820693122-52956464894 | 36554471751-65919787106-00809866023-48790163561-33909214861 |
| 10 | 497864 | 09547967219-39906583629-69077413839-69484437464-13418798478-47928808080-54901501970-58233953813-72479608919-63475855320 | 59346922262-65082899599-23058506484-38409416453-58642527076 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+

根据上方的条件，我们创造一条本案例一样的语句:select k,pad,c from sbtest1 where k>383019 and pad=‘93637428753-19078512484-07499046167-90467648315-23800958676’ order by c;也就是说这条语句只有一个结果集，比较直观一些，k代表a、pad代表b、c代表c。

没有索引的情况

从下方可以看出，没有索引必然是全表扫描，100w的数据，扫描大概98w行。时间耗时0.32s，Extra为Using where; Using filesort
Using where：表示不一定是没用到索引，而是就算有索引，但是where条件的需求满足不了，需要获取所需要的数据行，这种情况效率是有一些糟糕的。
Using filesort：这个表示本次查询使用到了额外的排序，就是当索引中无法完成排序，就需要在临时文件中进行额外的排序，因为我们语句中有order by，如果需要排序的数据比较多，这种情况也是比较糟糕的。

mysql> select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
1 row in set (0.32 sec)

mysql> explain select k,c,pad from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986328 |     3.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

增加索引(a,b,c)

从下方测试可以看出，加了索引确实好了一点，执行时间0.06s，变成了索引范围扫描（range），但是还是用到了额外的排序，而且扫描行数为20多w。
Using index：表示索引覆盖，因为我们是select a,b,c而不是select * 所以可能会用到索引覆盖，不知道索引覆盖是什么的可以看我另外一篇文章有详细介绍。

https://blog.csdn.net/h_3369/article/details/141357887?spm=1001.2014.3001.5502

mysql> alter table sbtest1 add index id_test(k,pad,c);
mysql> explain select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+------------------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | id_test       | id_test | 244     | NULL | 256480 |    10.00 | Using where; Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
1 row in set (0.06 sec)

增加索引(a,c,b)

从下方结果来看，(a,c,b)和(a,b,c)基本上区别不大…

mysql> alter table sbtest1 add index id_test(k,c,pad);
mysql> OPTIMIZE TABLE sbtest1;
mysql> select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
1 row in set (0.05 sec)

mysql>  explain select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+------------------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | id_test       | id_test | 4       | NULL | 254162 |    10.00 | Using where; Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

增加索引(b,a,c)

从这里看效率已经很高了，扫描行数1行，但是索引是range范围扫描，并且依然有临时表排序。

mysql> alter table sbtest1 add index id_test(pad,k,c);
mysql> OPTIMIZE TABLE sbtest1;
mysql> select k,pad,c from sbtest1 where k>383019  and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
1 row in set (0.00 sec)

mysql> explain select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | id_test       | id_test | 244     | NULL |    1 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

增加索引(b,c,a)

此时可以看到，索引的效率已经非常高了，ref,const，唯一查询，并且没有临时排序，因为b值相等于c，所以无需排序，因为索引中本来就是有序无需再进行排序。

mysql> alter table sbtest1 add index id_test(pad,c,k);
mysql> explain select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ref  | id_test       | id_test | 240     | const |    1 |    33.33 | Using where; Using index |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
1 row in set (0.00 sec)

增加索引(b,c)

这里效率整体来看和(b,c,a)一样，而且同样没有额外的排序，但是因为我们是要查，slect a,b,c，所以如果不加a的索引，就用不到覆盖索引，相比较(b,c,a)多一次通过主键回表找a列数据的操作。但是因为是主键其实效率还好。因为建了(b,c,a)走到最后a的时候也是需要在辅助索引树中，进行for循环判断a列的。

mysql> drop index id_test on sbtest1;
mysql> alter table sbtest1 add index id_test(pad,c);
mysql> OPTIMIZE TABLE sbtest1;
mysql> explain select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ref  | id_test       | id_test | 240     | const |    1 |    33.33 | Using where                        |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select k,pad,c from sbtest1 where k>383019 and pad='93637428753-19078512484-07499046167-90467648315-23800958676' order by c;
1 row in set (0.00 sec)

B+tree索引概念

在解释之前，我们需要了解一下B+tree索引中比较重要的两个概念：

第一个：B+tree在创建索引时，是按照最左侧进行排序，例如(a,b,c)三列建造联合索引，先按照a排序，如过a的值一样，那就按照第二列b进行排序，b是相等的话，在按照c排序…
第二个：因为根据上方的排序方式，所以就导致了，B+tree的联合索引，在select查询时，如果查询条件，是范围查询如a>?或者a<?，就会导致右侧，也就是后面的where条件无法走索引。例如，索引(a,b,c)，查询条件为a>? and b=? c=?这样的话，后面的b和c就无法使用到索引了，因为B+tree遵循，最左原则，先按照索引查a，a在范围查找后，b和c在索引树中就是无序的，无序的自然也就没办法使用索引了。
请看下图，a,b两列建造索引，先按照a排序，所以a是有序的，b在a相同的情况下，b是有序的。

结论

(b,c,a)≈(b,c)>(b,a,c)>(a,c,b)≈(a,b,c)
根据上面的两个核心点，我们现在整理理解一下本篇文章的结论，为什么b,c没有建造a的索引，也能和b,c,a效率一样呢？

那是因为就算建造了a，也是用不上的，因为b是等值，c是做order排序，B+tree索引中的联合索引，是先排序再创建的，所以结果是有序的，无需再进行排序，也就没有了filesort。但走到最后判断a的时候，此时a是无序的，所以不满足使用B+tree索引的条件。

那在(b,c)索引中，最后是怎么获取a这列数据的？

b等值查询后，因为是等值，所以c也是有序的，无需排序，最后到a的时候，因为索引树中没有a这个列，所以需要通过主键id获取a列数据进行比对。因为是通过主键，效率其实还好。但是因为所需要查询的数据是select a,b,c所以在索引树中无法直接获取到所有数据，也就没了using index覆盖索引。

那在(b,c,a)索引中，最后是怎么获取a这列数据的？

前面查询的数据和(b,c)索引一样，到了最后a的时候，因为(b,c,a)索引中也有a这个列，所以可以在这个索引页中进行for循环，加if判断a的查询条件，看是否满足，判断完毕后返回数据，因为直接从索引树中获取到的数据，所以用到了using index覆盖索引。

如果不建造a列索引只是建造(b,c)效果也是差不多的，查到最后a的时候通过主键回表效率也不是很低，所以整体来说建造(b,c,a)或者(b,c)都可以，如果可以确认语句永远只查a,b,c三列，建造(b,c,a)没有问题，因为可以用到覆盖索引，否则为了存储空间和写入效率等，建造(b,c)就可以了。

小知识

前面提到了，当联合索引where条件，用到范围查询后，右侧也就是后面的条件因为是无序的，所以无法使用索引，这句话也对，但不全对，因为范围查询还包括>=、<=、BETWEEN、like。这些条件的后面，有可能也会用上索引，为什么这么说？

比如有(a,b)两列索引，a列数据为1、1、2、3、4，那此时可以发现a=1的数据有两条，这两条数据的b列一定是局部有序的，如果我的查询条件是a>=1 and b=?，如果是a>1查询条件，那肯定不会看到1这个结果，所以无法b无法走索引，但是如果是a>=1，那么结果中此时有两个1，这时候优化器就会把他看成a=1 and b=?，因为有相同的等值，结果集a列的两个1。那此时b列就是有序的，就能用到索引先过滤掉这一部分数据。所以还是有可能会用到索引的。