美文网首页
MySQL实战宝典 索引调优篇 10 组合索引：用好，性能提升不

MySQL实战宝典 索引调优篇 10 组合索引：用好，性能提升不

作者: 逢春枯木 | 来源:发表于2021-06-16 15:14 被阅读0次

在一个列上进行索引排序和使用比较简单，在实际业务中，我们还会遇到很复杂的场景，比如对多个列进行查询。这时可能会要求用户创建多个列组成的索引，称作组合索引，如列a和列b创建组合索引，但究竟是创建(a,b)索引，还是(b,a)的索引，结果却是完全不同的。

接下来就来学习以下更贴近业务实战的组合索引的创建和使用，进一步提升系统的性能。

组合索引(Compound Index)

组合索引是指由多个列组合而成的B+树索引，这和我们之前介绍的B+树索引的原理完全一样，只是之前是对一个列排序，现在是对多个列排序。

组合索引既可以是主键索引，也可以是二级索引：

组合索引

组合索引只是排序的键值从1个变成了多个，本质还是一棵B+树索引。但是你一定要意识到(a,b)和(b,a)这样的组合索引的排序结果是完全不一样的。而索引的字段变多了，设计上更容易出问题：

排序

对组合索引(a,b)来说，因为其对列a、b做了排序，所以它可以对下面几个查询进行优化：

SELECT * FORM t WHERE a = ?;
SELECT * FROM t WHERE a = ? AND b = ?;
SELECT * FROM t WHERE b = ? AND a = ?; -- 也可以使用组合索引（a,b）
SELECT * FROM t WHERE a = ? ORDER BY b DESC;

但是下面的SQL无法使用组合索引(a,b)，因为(a,b)排序并不能退出(b,a)排序：

SELECT * FROM t WEHRE b = ?;

组合索引设计实战

避免额外排序

在真实的业务场景中，你会遇到根据某个列进行查询，然后按照时间排序的方式逆序展示。

比如在微博业务中，用户的微博展示就是根据用户ID查询除用户订阅的微博，然后根据时间逆序展示；又比如电商业务中，用户订单列表页就是根据用户ID查询出用户的订单信息，然后根据购买时间进行逆序展示。

现有如下orders表的设计：

CREATE TABLE `orders` (
  `O_ORDERKEY` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`),
  KEY `ORDERS_FK1` (`O_CUSTKEY`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`O_CUSTKEY`) REFERENCES `customer` (`C_CUSTKEY`)
) ENGINE=InnoDB DEFAULT

当用户查看自己的订单信息，并且需要根据订单时间排序查询时，可使用下面的SQL：

SELECT * FROM orders 
WHERE o_custkey = 147601 ORDER BY o_orderdate DESC;

但由于上述表结构的索引设计时，索引orders_fk1仅对o_custkey排序，因此在取出用户147601的数据后，还需要一次额外的排序才能得到结果，可通过命令EXPLAIN验证：

mysql> EXPLAIN SELECT * FROM orders  WHERE o_custkey = 147601 ORDER BY o_orderdate DESC;
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | orders | NULL       | ref  | orders_fk1    | orders_fk1 | 4       | const |   19 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN 输出结果中可以看到，SQL 语句的确可以使用索引 orders_fk1，但在 Extra 列中显示的 Using filesort，表示还需要一次额外的排序才能得到最终的结果。

在 MySQL 8.0 版本中，通过命令 EXPLAIN 的额外选项，FORMAT=tree，观察得更为明确：

mysql> EXPLAIN FORMAT=tree SELECT * FROM orders  WHERE o_custkey = 147601 ORDER BY o_orderdate DESC;
+---------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------+
| -> Sort: orders.O_ORDERDATE DESC  (cost=20.89 rows=19)
    -> Index lookup on orders using orders_fk1 (O_CUSTKEY=147601)
 |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

上述SQL执行计划显式进行Index lookup索引查询，然后进行Sort排序，最终得到结果。

由于已对列o_custkey创建索引，因此上述SQL语句并不会执行的特别慢，但是在海量的并发业务访问下，每次SQL执行都需要排序就会对业务的性能产生非常明显的影响，比如CPU负载变高，QPS降低。

要解决这个问题，最好的办法：在取出结果时，已经根据字段o_orderdate排序，这样就不用额外的排序了

为此我们在orders表上创建新的组合索引idx_custkey_orderdate,对字段(o_custkey,o_orderdate)进行索引：

ALTER TABLE orders ADD INDEX idx_custkey_orderdate(o_custkey,o_orderdate);

这时再进行之前的SQL，根据时间展示用户的订单信息，其执行计划为：

mysql> EXPLAIN FORMAT=tree SELECT * FROM orders WHERE o_custkey=1 ORDER BY o_orderdate DESC;
+---------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                       |
+---------------------------------------------------------------------------------------------------------------+
| -> Index lookup on orders using idx_custkey_orderdate (O_CUSTKEY=1; iterate backwards)  (cost=12.10 rows=11)
 |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，这时优化器使用了我们新建的索引idx_custkey_orderdate，而且没有了Sort排序第二个过程。

避免回表，性能提升10倍

之前，已经了解了回表的概念：即SQL需要通过二级索引查询得到主键值，然后再根据主键值搜索主键索引，最后定位到完整的数据。

但由于二级组合索引的叶子节点，包含索引键值和主键值，若查询的字段在二级索引的叶子节点中，则直接返回结果，无需回表。这种通过组合索引避免回表的技术也称为索引覆盖(Covering Index)。

如下面的SQL语句：

mysql> EXPLAIN  SELECT o_custkey,o_orderdate,o_totalprice  FROM orders WHERE o_custkey = 147601;
+----+-------------+--------+------------+------+----------------------------------+------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys                    | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------------------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | orders_fk1,idx_custkey_orderdate | orders_fk1 | 4       | const |   19 |   100.00 | NULL  |
+----+-------------+--------+------------+------+----------------------------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

执行计划显示上述SQL会使用到orders_fk1索引，但是，由于组合索引的叶子节点只包含（o_custkey，o_orderdate，_orderid），没有字段 o_totalprice 的值，所以需要通过 o_orderkey 回表找到对应的 o_totalprice。

再通过 EXPLAIN 的额外选项 FORMAT=tree，查看上述 SQL 的执行成本：

mysql> EXPLAIN  FORMAT=tree SELECT o_custkey,o_orderdate,o_totalprice  FROM orders WHERE o_custkey = 147601;
+--------------------------------------------------------------------------------------+
| EXPLAIN                                                                              |
+--------------------------------------------------------------------------------------+
| -> Index lookup on orders using orders_fk1 (O_CUSTKEY=147601)  (cost=20.89 rows=19)
 |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果想要避免回表，可以通过索引覆盖技术，创建(o_custkey,o_orderdate,o_totalprice)的组合索引：

ALTER TABLE orders ADD INDEX idx_custkey_orderdate_totalprice(o_custkey,o_orderdate,o_totalprice);

然后再次通过命令 EXPLAIN 观察执行计划：

mysql> EXPLAIN   SELECT o_custkey,o_orderdate,o_totalprice  FROM orders WHERE o_custkey = 147601;
+----+-------------+--------+------------+------+-------------------------------------------------------------------+----------------------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                                                     | key                              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------------------------------------------------+----------------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | orders_fk1,idx_custkey_orderdate,idx_custkey_orderdate_totalprice | idx_custkey_orderdate_totalprice | 4       | const |   19 |   100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------------------------------------------------+----------------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到，这时优化器选择了新创建的组合索引 idx_custkey_orderdate_totalprice，同时这时Extra 列不为 NULL，而是显示 Using index，这就表示优化器使用了索引覆盖技术。

再次观察 SQL 的执行成本：

mysql> EXPLAIN FORMAT=tree  SELECT o_custkey,o_orderdate,o_totalprice  FROM orders WHERE o_custkey = 147601;
+-----------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| -> Index lookup on orders using idx_custkey_orderdate_totalprice (O_CUSTKEY=147601)  (cost=2.94 rows=19)
 |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到 cost 有明显的下降，从20.89 下降为了 2.94，性能提升巨大。我们来看下这条SQL的输出：

mysql> SELECT o_custkey,o_orderdate,o_totalprice  FROM orders  WHERE o_custkey = 147601;
+-----------+-------------+--------------+
| o_custkey | o_orderdate | o_totalprice |
+-----------+-------------+--------------+
|    147601 | 1992-05-11  |    109262.70 |
|    147601 | 1992-05-20  |      4419.68 |
|    147601 | 1993-01-14  |    208550.55 |
|    147601 | 1993-07-12  |    309815.22 |
|    147601 | 1993-10-15  |     60391.27 |
|    147601 | 1994-04-25  |    145497.64 |
|    147601 | 1994-08-11  |    130362.83 |
|    147601 | 1994-11-11  |     85054.05 |
|    147601 | 1994-12-05  |    223393.31 |
|    147601 | 1995-03-28  |    220137.39 |
|    147601 | 1995-10-05  |    126002.46 |
|    147601 | 1996-01-02  |    191792.06 |
|    147601 | 1996-02-02  |    180388.11 |
|    147601 | 1996-04-13  |     18960.24 |
|    147601 | 1996-10-09  |    294150.71 |
|    147601 | 1997-01-22  |     19440.08 |
|    147601 | 1997-02-18  |     75159.87 |
|    147601 | 1997-10-01  |    214565.88 |
|    147601 | 1998-02-16  |    131378.46 |
+-----------+-------------+--------------+
19 rows in set (0.00 sec)

可以看到，执行一共返回 19 条记录。这意味着在未使用索引覆盖技术前，这条 SQL 需要总共回表 19 次， 每次从二级索引读取到数据，就需要通过主键去获取字段 o_totalprice。

在使用索引覆盖技术后，无需回表，减少了 19 次的回表开销，

如果你想看索引覆盖技术的巨大威力，可以执行下面这条 SQL：

SELECT o_custkey,SUM(o_totalprice) 
FROM orders GROUP BY o_custkey;

这条 SQL 表示返回每个用户购买订单的总额，业务侧可以根据这个结果对用户进行打标，删选出大客户，VIP 客户等。

我们先将创建的组合索引 idx_custkey_orderdate_totalprice 设置为不可见，然后查看原先的执行计划：

mysql> EXPLAIN SELECT o_custkey,SUM(o_totalprice)  FROM orders GROUP BY o_custkey;
+----+-------------+--------+------------+-------+----------------------------------+------------+---------+------+---------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys                    | key        | key_len | ref  | rows    | filtered | Extra |
+----+-------------+--------+------------+-------+----------------------------------+------------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | index | orders_fk1,idx_custkey_orderdate | orders_fk1 | 4       | NULL | 5583704 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+----------------------------------+------------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN FORMAT=tree SELECT o_custkey,SUM(o_totalprice)  FROM orders GROUP BY o_custkey;
+---------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------+
| -> Group aggregate: sum(orders.O_TOTALPRICE)
    -> Index scan on orders using orders_fk1  (cost=605857.65 rows=5583704)
 |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，这条 SQL 优化选择了索引 ORDERS_FK1，但由于该索引没有包含字段o_totalprice，因此需要回表，根据 rows 预估出大约要回表 5583704次。

同时，根据 FORMAT=tree 可以看到这条 SQL 语句的执行成本在 605857.65，对比前面单条数据的回表查询，显然成本高了很多。

所以，执行这条 GROUP BY的SQL，总共需要花费 12.35 秒的时间:

mysql> SELECT o_custkey,SUM(o_totalprice)  FROM orders GROUP BY o_custkey;
...
399987 rows in set (1 min 11.38 sec)

再来对比启用索引覆盖技术后的 SQL 执行计划情况：

mysql> EXPLAIN SELECT o_custkey,SUM(o_totalprice)  FROM orders GROUP BY o_custkey;
+----+-------------+--------+------------+-------+-------------------------------------------------------------------+----------------------------------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys                                                     | key                              | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+-------+-------------------------------------------------------------------+----------------------------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | index | orders_fk1,idx_custkey_orderdate,idx_custkey_orderdate_totalprice | idx_custkey_orderdate_totalprice | 14      | NULL | 5583704 |   100.00 | Using index |
+----+-------------+--------+------------+-------+-------------------------------------------------------------------+----------------------------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到，这次的执行计划提升使用了组合索引 idx_custkey_orderdate_totalprice，并且通过Using index 的提示，表示使用了索引覆盖技术。

mysql> SELECT o_custkey,SUM(o_totalprice)  FROM orders GROUP BY o_custkey;
...
399987 rows in set (2.04 sec)

再次执行上述 SQL 语句，可以看到执行时间从之前的 1 min 11.38 sec 秒缩短为了 2.04 sec 秒，SQL 性能提升了 10 倍多。

这就是索引覆盖技术的威力，而且这还只是基于 orders 表总共 600 万条记录。若表 orders 的记录数越多，需要回表的次数也就越多，通过索引覆盖技术性能的提升也就越明显。

总结

组合索引也是一棵B+树，只是索引的列由多个组成，组合索引既可以时主键索引，也可以是二级索引。以下是组合索引的三大优势：

  1. 覆盖多个查询条件，如(a,b)索引可以覆盖a=？或者a=? and b=?

  2. 避免SQL额外的排序，提升SQL性能，如WHERE a = ？ORDER BY b这样的查询条件

    SELECT query,rows_sorted FROM sys.x$statement_analysis WHERE db NOT IN ('sys','mysql') AND rows_sorted!=0
-- 用以查询是否存在可以使用组合索引优化的SQL

  3. 利用组合索引包含多个列的特性，可以实现索引覆盖技术，提升SQL的查询性能，用好索引覆盖技术，技能提升10倍不是难事。

相关文章

网友评论

      本文标题：MySQL实战宝典 索引调优篇 10 组合索引：用好，性能提升不

      本文链接：https://www.haomeiwen.com/subject/yptfyltx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|MySQL实战宝典 索引调优篇 10 组合索引：用好，性能提升不|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！