MySQL二级索引中的主键——MySQL存在大量相同数据分页查询优化

adrninistrat0r

于 2022-07-25 21:34:51 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/a82514921/article/details/125983869

版权

MySQL 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

1. 前言

众所周知，MySQL InnoDB存储引擎的索引类型分为聚簇索引与二级索引，在二级索引中存储了主键列，通过二级索引查找整行数据时，需要先找到二级索引，再根据二级索引中的主键列从聚簇索引中查找整行数据。

以下主要分析，当存在大量相同数据时，分页查询如何通过二级索引中主键进行优化。

在实际场景中进行优化时，需要根据具体情况具体分析。

对于存在一些重复数据，大部分数据不相同的字段，直接使用该字段进行排序及分页查询即可，在分页查询时不需要使用主键进行排序，以下不考虑这种情况。

2. MySQL文档关于二级索引中主键的说明

Clustered and Secondary Indexes

参考https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html。

In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

在InnoDB中，二级索引中包含了主键列，以及二级索引中指定的列。InnoDB使用该主键值从聚簇索引中查找对应的行。

InnoDB Row Formats

参考https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html。

The nodes of a secondary index structure contain the values of index columns and primary key columns.

InnoDB的二级索引结构中包含二级索引中的列，以及主键列。

Use of Index Extensions

参考https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html。

InnoDB automatically extends each secondary index by appending the primary key columns to it.

This table defines the primary key on columns (i1, i2). It also defines a secondary index k_d on column (d), but internally InnoDB extends this index and treats it as columns (d, i1, i2).

InnoDB会自动对每个二级索引进行扩展，将主键列附加在它的后面。

例如某个表中的主键对应的列为(i1, i2)，还在(d)列上定义了二级索引，InnoDB在内部会对该二级索引进行扩展，将其当作包含(d, i1, i2)列的索引对待。

3. 存在大量相同数据的分页查询优化

3.1. 存在大量相同数据的分页查询的限制

对于存在大量相同数据的列，通常某个值的数量会远大于每次分页查询的数量，仅使用该列无法进行排序以实现分页查询，需要再引入一个数据唯一的字段用于排序。

3.2. 存在大量相同数据的分页查询优化方式

假如某个字段中存在大量相同的数据，在进行分页查询时，可以利用MySQL二级索引中包含主键的特性，将二级索引当作包含多个字段的索引(col, pk)使用，通过二级索引中的主键进行排序（不进行file sort），在分页查询时还需要避免深分页的问题。

通常情况下，仅使用二级索引中的主键即可高效地分页查询，不需要再使用其他字段。

4. 用于验证的数据库表结构及数据情况

使用的数据库版本为MariaDB 10.0.36。

使用以下数据库表进行验证：

CREATE TABLE test_pk_in_secondary_index (
  id varchar(32) NOT NULL COMMENT 'id',
  col1 int(11) NOT NULL COMMENT 'col1',
  col2 int(11) NOT NULL COMMENT 'col2',
  col3 int(11) NOT NULL COMMENT 'col3',
  PRIMARY KEY (id),
  KEY idx_pisi_col1 (col1),
  KEY idx_pisi_multi1 (col2, col3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试表';

以上表中的主键为id列，在字段(col1)上有单列索引，在字段(col2, col3)上有联合索引。

同一行的col1与col2列的值相同，从1到10随机取值，每种值对应的行数量约为50万，表中总记录数为500万。

col1列（即col2列）各种值的数量如下：

select col1, count(col1) as cc1 from test_pk_in_secondary_index group by col1;

| col1 | cc1    |
+------+--------+
|    1 | 499925 |
|    2 | 499505 |
|    3 | 500618 |
|    4 | 500964 |
|    5 | 499835 |
|    6 | 500168 |
|    7 | 499478 |
|    8 | 500171 |
|    9 | 499315 |
|   10 | 500021 |

col3列的值从1到6随机取值，值为1/6、2/5、3/4的数量比例约为1:5:44。

col2（col1）列值为6的记录中，col3列各种值的数量如下：

select col3, count(col3) as cc3 from test_pk_in_secondary_index where col2=6 group by col3;

| col3 | cc3    |
+------+--------+
|    1 |    483 |
|    2 |   2444 |
|    3 | 247764 |
|    4 | 246518 |
|    5 |   2499 |
|    6 |    460 |

5. 使用二级索引分页查询

5.1. 使用二级索引分页查询可能存在的问题

对单个数据库表查询出现慢SQL时，主要原因通常是查询时扫描行数过多，或者对大量记录进行了file sort。

由于索引中的字段不能加太多，且代码功能也会不断调整，因此查询条件中总是可能存在某些字段未创建索引（或不在使用的索引中）。在这种情况下，使用二级索引分页查询时可能存在以下问题：

5.1.1. 扫描行数无法控制

使用二级索引分页查询时，假如查询条件中存在不在索引中的字段，则相关的查询都需要回表才能完成。每次查询时的扫描行数，由查询条件中不在索引中字段的目标值比率及位置分布决定，可能因为扫描行数及回表次数太多导致慢SQL。

通过慢SQL日志可以查看查询耗时及扫描行数。

以下可以看到，执行相同的SQL语句，但查询条件中的字段值不相同时，查询的扫描行数及耗时可能会差别非常大，需要避免这种扫描行数无法控制的查询方式。

以下SQL语句的执行计划部分内容如下：

  select_type: SIMPLE
         type: ref
possible_keys: idx_pisi_col1
          key: idx_pisi_col1
          ref: const
        Extra: Using where

以下对用于验证的数据库表进行查询，查询条件中使用了col1、col3字段，查询时会使用二级索引idx_pisi_col1 (col1)，col3字段不在索引中。

不在索引中的字段在查询条件中目标值的比率较高

以下查询col1列值为6，col3列值为3，使用col1,id列升序排序的前1000条记录，查询耗时约为：0.07秒，扫描行数：1997。

select * from test_pk_in_secondary_index where col1=6 and col3=3 order by col1,id limit 1000;

col1列值为6的记录总数为500168，其中col3列值为3的记录总数为247764，比率约49.54%。

根据该比率计算，为了查询到1000条col3列值为3的记录，需要扫描的行数可能为1000*500168/247764，约为2018，与实际扫描行数1997接近。

不在索引中的字段，在查询条件中目标值的比率较高时，查询时扫描行数及回表次数可能会较少，查询耗时也较短。

不在索引中的字段在查询条件中目标值的比率较低

以下查询col1列值为6，col3列值为2，使用col1,id列升序排序的前1000条记录，查询耗时约为：4.12秒，扫描行数：199189。

select * from test_pk_in_secondary_index where col1=6 and col3=2 order by col1,id limit 1000;

col1列值为6的记录总数为500168，其中col3列值为2的记录总数为2444，比率约0.49%。

根据该比率计算，为了查询到1000条col3列值为2的记录，需要扫描的行数可能为1000*500168/2444，约为204651，与实际扫描行数199189接近。

不在索引中的字段，在查询条件中目标值的比率较低时，查询时扫描行数及回表次数可能会较多，查询耗时也较长。

不在索引中的字段在查询条件中目标值的数量小于分页查询数量

以下查询col1列值为6，col3列值为1，使用col1,id列升序排序的前1000条记录，查询耗时约为：12.08秒，扫描行数：500168。

select * from test_pk_in_secondary_index where col1=6 and col3=1 order by col1,id limit 1000;

col1列值为6的记录总数为500168，其中col3列值为1的记录总数为483，小于分页查询数量1000。

以上通过索引idx_pisi_col1 (col1)进行查询，扫描col1列值为6的记录。查询语句要求从中查找到col3列值为1的前1000条记录，但实际上其中col3列值为1的记录数量不到1000，因此通过以上索引将col1列值为6的所有记录扫描完毕后，查询才会结束。col1列值为6的数量为500168，与以上查询的实际扫描行数一致。

不在索引中的字段，在查询条件中目标值的数量小于分页查询数量时，需要查询在二级索引中字段等于目标值的全部数据，若相关的记录数量较大，很容易因为扫描行数与回表次数太多造成慢SQL。

5.1.2. 深分页问题

col1列值为6的记录中，col3列值为4的记录总数为246518。

以下查询col1列值为6，col3列值为4，使用col1,id列升序排序的前1000条记录，查询耗时约为：0.07秒，扫描行数：2062。

select * from test_pk_in_secondary_index where col1=6 and col3=4 order by col1,id limit 1000;

以下查询col1列值为6，col3列值为4，使用col1,id列升序排序，第200000行开始的前1000条记录，查询耗时约为：9.07秒，扫描行数：407976。

select * from test_pk_in_secondary_index where col1=6 and col3=4 order by col1,id limit 200000,1000;

可以看到，这是典型的深分页问题，随着分页查询指定的limit偏移量增大，查询时的扫描行数及耗时都大大增加。

5.2. 使用二级索引中的主键分页查询优化思路

以下的优化思路，与解决深分页问题的常规方式类似，在查询时会使用到二级索引中的主键字段。

在实际的场景中，假如以下col1字段对应的是记录的创建时间/修改时间等字段，则需要满足在进行查询时已经过去的时间段的数据不会有变化，否则以下方式查询的数据会有遗漏或多余。

在进行分页查询时，将每次的查询分为两步：

5.2.1. 第一步通过二级索引查询本次分页结束位置的主键值

以下为第一步：

在查询结果字段中，仅查询主键列。

在查询条件中，仅指定对应的二级索引中的字段等于所需的值，不指定其他字段；

在排序字段中，依次指定对应的二级索引中的字段，以及主键列；

在limit子句中指定查询偏移量为(分页数量-1)，查询数量为1。

以上处理的目的是，使优化器选择二级索引，查询指定偏移量下的主键值。在查询时的扫描行数在限定的范围内，没有深分页的问题；使用索引记录中字段值本身的顺序即可完成排序，避免了file sort。

由于以上查询的结果字段、查询条件及排序字段中，出现的字段都在二级索引中，且使用的顺序正确，因此不需要回表，仅通过二级索引就可以完成查询。

根据本次查询是否第一次查询/中间查询/最后一次查询，在处理时还存在以下区别：

第一次查询

假如本次查询是第一次查询，则在查询条件中不需要指定主键大于（或小于）某个值；

中间查询

假如本次查询是中间查询（不是第一次查询，也不是最后一次查询），则在查询条件中还需要指定主键大于（或小于）上次分页查询结束位置的主键；

最后一次查询

假如本次查询是中间查询，当查询结果为空时，说明已经是最后一次查询，此时需要再查询主键的最大或最小值，根据查询时的排序方向决定；

若以上查询结果为空，则说明已查询完毕，可结束分页查询；若以上查询结果非空，则返回查询到的主键的最大或最小值。

5.2.2. 第二步通过二级索引与主键值进行实际分页查询

以下为第二步：

在查询结果字段中，查询所有需要的列。

在查询条件中，指定对应的二级索引中的字段等于所需的值，主键字段小于等于（或大于等于）第一步查询到的主键值，以及其他字段的条件；

在排序字段中，依次指定对应的二级索引中的字段，以及主键列；

不需要指定limit子句。

以上处理的目的是，使优化器选择二级索引，查询二级索引中指定范围的数据。通过二级索引及主键字段限制查询时的扫描范围（及回表查询范围），在查询时的扫描行数在限定的范围内，没有深分页的问题；使用索引记录中字段值本身的顺序即可完成排序，避免了file sort。

假如是第一次查询，则在查询条件中不需要指定主键大于（或小于）某个值；假如不是第一次查询，则在查询条件中还需要指定主键大于（或小于）上次分页查询的最后一条记录的主键。

第二步查询结果若为空，不代表分页查询已查询完毕，需要根据第一步的查询结果判断是否已查询完毕。

5.3. 使用二级索引中的主键分页查询优化验证

以下对用于验证的数据库表进行分页查询，查询条件为col1列值为6，col3列值为1，通过col1,id字段升序排序，每次最多扫描1000行。

5.3.1. 首次查询处理

首次查询第一步

首次查询的第一步查询使用的示例SQL语句如下，执行时的扫描行数为1000：

select id 
from test_pk_in_secondary_index 
where col1=6 
order by col1,id 
limit 999,1;

执行计划部分内容如下：

  select_type: SIMPLE
         type: ref
possible_keys: idx_pisi_col1
          key: idx_pisi_col1
          ref: const
        Extra: Using where; Using index

以上查询到的主键值为“testtime1658619371num00000009538”。

首次查询第二步

首次查询的第二步查询使用的示例SQL语句如下，执行时的扫描行数为1000：

select * 
from test_pk_in_secondary_index 
where col1=6 and 
id<='testtime1658619371num00000009538' and 
col3=1 
order by col1,id;

执行计划部分内容如下：

  select_type: SIMPLE
         type: range
possible_keys: PRIMARY,idx_pisi_col1
          key: idx_pisi_col1
          ref: NULL
        Extra: Using index condition; Using where

5.3.2. 中间查询处理

中间查询第一步

中间查询的第一步查询使用的示例SQL语句如下，执行时的扫描行数为1000：

select id 
from test_pk_in_secondary_index 
where col1=6 and 
id>'testtime1658619371num00000009538' 
order by col1,id 
limit 999,1;

执行计划部分内容如下：

  select_type: SIMPLE
         type: range
possible_keys: PRIMARY,idx_pisi_col1
          key: idx_pisi_col1
          ref: NULL
        Extra: Using where; Using index

以上查询到的主键值为“testtime1658619373num00000019247”。

中间查询第二步

中间查询的第二步查询使用的示例SQL语句如下，执行时的扫描行数为1000：

select * 
from test_pk_in_secondary_index 
where col1=6 and 
id>'testtime1658619371num00000009538' and 
id<='testtime1658619373num00000019247' and 
col3=1 
order by col1,id;

执行计划与首次查询第二步相同，略。

5.3.3. 最后一次查询处理

最后一次查询第一步

与中间查询处理类似，略。

最后一次查询主键最大/最小值

最后一次查询在查询主键最大/最小值时，使用的示例SQL语句如下，执行时的扫描行数为0：

select max(id) 
from test_pk_in_secondary_index 
where col1=6;

执行计划部分内容如下：

  select_type: SIMPLE
         type: NULL
possible_keys: NULL
          key: NULL
          ref: NULL
        Extra: Select tables optimized away

最后一次查询第二步

与中间查询处理类似，略。

6. 使用联合索引进行分页查询

以下查询用于验证的数据库表，查询条件中使用了col2、col3字段，查询时可能使用联合索引idx_pisi_multi1 (col2, col3)。

6.1. 使用联合索引分页查询索引失效导致全表扫描问题

假如主键或二级索引为联合索引，则在查询条件及排序字段中指定索引相关字段时，需要按照字段在索引中的顺序使用，避免索引失效。

全表扫描

当二级索引为联合索引时，若查询条件中使用了二级索引中的部分字段，但排序时仅通过主键进行排序时，可能出现索引失效，进行全表扫描的问题。

select * from test_pk_in_secondary_index where col2=6 order by id limit 1;

如以上SQL语句，在查询条件中指定了索引idx_pisi_multi1 (col2, col3)中的第1个字段col2，但排序字段中仅使用了主键。通过以下执行计划可以看到，查询时使用主键索引进行全表扫描：

  select_type: SIMPLE
         type: index
possible_keys: idx_pisi_multi1
          key: PRIMARY
          ref: NULL
        Extra: Using where

执行以上需要全表扫描的SQL语句，查询耗时约为：0.02秒，扫描行数：18。

强制使用二级索引查询

强制以上SQL语句使用包含(col2, col3)列的二级索引idx_pisi_multi1查询：

select * from test_pk_in_secondary_index ignore index(PRIMARY) where col2=6 order by id limit 1;
select * from test_pk_in_secondary_index force index(idx_pisi_multi1) where col2=6 order by id limit 1;

执行计划部分内容如下：

  select_type: SIMPLE
         type: ref
possible_keys: idx_pisi_multi1
          key: idx_pisi_multi1
          ref: const
        Extra: Using where; Using filesort

执行以上强制使用二级索引查询的SQL语句，查询耗时约为：9.09秒，扫描行数：500169。

以上扫描行数接近test_pk_in_secondary_index表中col2列值为6的记录数500168。

6.2. 优化器为什么有时使用全表扫描

在以上示例中，查询条件使用联合索引idx_pisi_multi1 (col2,col3)的第一个字段col2，排序时使用主键，使用全表扫描确实比使用二级索引查询开销更小，速度更快：

强制使用二级索引进行查询，则需要扫描约50万行col2列值为6的记录，再进行file sort，这个操作的开销和耗时都会非常大；
使用全表扫描进行查询时，扫描行数仅为18，查询耗时也很短。

当通过limit指定了需要查询的行数时，若扫描行数不是非常多，且不需要进行file sort时，全表扫描的执行耗时不一定会很长。例如在性能很一般的Windows机器运行MariaDB，通过全表扫描查询，扫描行数约40万，执行耗时不超过0.3秒。

通常情况下，扫描相同行数的记录时，全表扫描比通过二级索引回表查询更快。优化器判断通过全表扫描比通过二级索引查询开销更小，速度更快时，可能会通过全表扫描查询。

但是，还是需要避免出现全表扫描。全表扫描的实际的扫描行数取决于查询条件中字段的目标值比率及位置分布，当扫描很多行时，查询时间也会变得很长，存在很大的风险。

6.3. 使用联合索引分页查询优化方式

对于以上使用联合索引分页查询索引失效导致全表扫描的问题，在不使用force index/ignore index强制使用或忽略索引的前提下，可通过修改查询条件与排序字段，间接引导优化器使用二级索引进行查询及排序，而不是进行全表扫描。

当查询条件与排序字段中使用了联合索引中的字段时，为了避免优化器进行全表扫描，查询条件中使用的联合索引中的字段，排序字段中使用的联合索引中的字段，以及查询条件结合排序字段后使用的联合索引中的字段，都需要满足最左匹配原则。

关于最左匹配原则，参考“Multiple-Column Indexes”https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html中的说明：

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

对于以上进行全表扫描的查询示例，可将联合索引idx_pisi_multi1 (col2,col3)中的字段都添加到查询条件及排序字段中，使优化器选择使用二级索引，进行查询及排序，避免file sort：

explain select * from test_pk_in_secondary_index where col2=6 order by col2,col3,id limit 1;
explain select * from test_pk_in_secondary_index where col2=6 order by col3,id limit 1;
explain select * from test_pk_in_secondary_index where col2=6 and col3=1 order by col2,col3,id limit 1;
explain select * from test_pk_in_secondary_index where col2=6 and col3=1 order by id limit 1;

以上SQL语句的效果相同，执行计划相同，部分内容如下：

  select_type: SIMPLE
         type: ref
possible_keys: idx_pisi_multi1
          key: idx_pisi_multi1
          ref: const
        Extra: Using where

以上查询耗时约为：0.03秒，扫描行数：1。