Mysql学习-第二章（组合索引）

组合索引是指多个列所组成的B+树索引，既可以是主键索引，也可以是二级索引组合，下图为一个索引图

组合索引（a,b）,(b,a)完全不同

示例，组合索引(a,b)，对列ab进行排序

SELECT * FROM table WHERE a = ?
SELECT * FROM table WHERE a = ？ AND b = ？

上述 SQL 查询中，WHERE 后查询列 a 和 b 的顺序无关，即使先写 b = ? AND a = ？依然可以使用组合索引（a，b）。

但是下面的 SQL 无法使用组合索引（a，b），因为（a，b）排序并不能推出（b，a）排序：

SELECT * FROM table WHERE b = ?

此外，同样由于索引（a，b）已排序，因此下面这条 SQL 依然可以使用组合索引（a，b），以此提升查询的效率：

SELECT * FROM table WHERE a = ？ ORDER BY b DESC

同样的原因，索引（a，b）排序不能得出（b，a）排序，因此下面的 SQL 无法使用组合索引（a，b）：

SELECT * FROM table WHERE b = ？ ORDER BY a DESC

业务索引

避免额外索引

业务需求：根据某个列进行查询，然后按照时间排序的方式逆序展示

比如在微博业务中，用户的微博展示的就是根据用户 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

其中：

• 字段 o_orderkey 是 INT 类型的主键；
• 字段 o_custkey 是一个关联字段，关联表 customer；
• 字段 o_orderdate、o_orderstatus、o_totalprice、o_orderpriority 用于描述订单的基本详情，分别表示下单的时间、当前订单的状态、订单的总价、订单的优先级。

在有了上述订单表后，当用户查看自己的订单信息，并且需要根据订单时间排序查询时，可通过下面的 SQL：

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

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

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

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

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

EXPLAIN FORMAT=tree 
SELECT * FROM orders
WHERE o_custkey = 147601 ORDER BY o_orderdate DESC 
*************************** 1. row ***************************
EXPLAIN: -> Sort: orders.O_ORDERDATE DESC  (cost=18.98 rows=19)
    -> Index lookup on orders using ORDERS_FK1 (O_CUSTKEY=147601)

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

由于已对列 o_custky 创建索引，因此上述 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，根据时间展示用户的订单信息，其执行计划为：

EXPLAIN FORMAT=tree 
SELECT * FROM orders
WHERE o_custkey = 147601 ORDER BY o_orderdate 
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on orders using idx_custkey_orderdate (O_CUSTKEY=147601)  (cost=6.65 rows=19)

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

避免回表

二级索引查询得到主键值，然后再根据主键值搜索主键索引，最后定位到完整数据。

二级组合索引的叶子节点中包含主键和索引键值，若查询的字段在二级索引的叶子节点中，则可以直接返回结果，无需回表，这种组合索引避免了回表的优化技术也称为索引覆盖

如下面的SQL语句：

EXPLAIN 
SELECT o_custkey,o_orderdate,o_totalprice 
FROM orders WHERE o_custkey = 147601\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_custkey_orderdate,ORDERS_FK1
          key: idx_custkey_orderdate
      key_len: 4
          ref: const
         rows: 19
     filtered: 100.00
        Extra: NULL

执行计划显示上述SQL会使用到之前新创建的组合索引 idx_custkey_orderdate，但由于组合索引的叶子节点只包含（o_custkey，o_orderdate，

_orderid），没有字段 o_totalprice 的值，所以需要通过 o_orderkey 回表找到对应的 o_totalprice。

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

EXPLAIN FORMAT=tree 
SELECT o_custkey,o_orderdate,o_totalprice 
FROM orders WHERE o_custkey = 147601\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on orders using idx_custkey_orderdate (O_CUSTKEY=147601)  (cost=6.65 rows=19)

cost=6.65 表示的就是这条 SQL 当前的执行成本。不用关心 cost 的具体单位，你只需明白cost 越小，开销越小，执行速度越快。

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

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

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

EXPLAIN 
SELECT o_custkey,o_orderdate,o_totalprice 
FROM orders WHERE o_custkey = 147601\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_custkey_orderdate,ORDERS_FK1,idx_custkey_orderdate_totalprice
          key: idx_custkey_orderdate_totalprice
      key_len: 4
          ref: const
         rows: 19
     filtered: 100.00
        Extra: Using index

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

再次观察 SQL 的执行成本，可以看到 cost 有明显的下降，从 6.65 下降为了 2.94：

EXPLAIN FORMAT=tree 
SELECT o_custkey,o_orderdate,o_totalprice 
FROM orders WHERE o_custkey = 147601\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on orders using idx_custkey_orderdate_totalprice (O_CUSTKEY=147601)  (cost=2.94 rows=19)

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

在使用索引覆盖技术后，无需回表，减少了 19 次的回表开销
知识点来自学习-姜承尧老师拉钩网教导内容。