MySQL limit分页查询优化实践

我就是我 2023-10-07 14:44 124阅读 0赞

1. 前言

以下测试使用MariaDB，版本为10.0.36，InnoDb版本为5.6.39-83.1。

以下分页查询优化针对OLTP交易场景。

以下对索引的分析方法可参考“MySQL索引分析除了EXPLAIN还有什么方法”。

2. limit offset分页查询的问题

使用limit offset方式进行分页查询时，存在以下问题：

  • 需要读取的记录数太多

当使用limit offset方式进行分页查询时，偏移量对应的行数也会被读取，不会被跳过，当偏移量较大时，读取效率较低。

例如查询语句使用“limit offset, row_count”，则查询时需要读取的行数至少为offset + row_count。

  • 需要回表的记录数太多

此外，在进行查询时，通常会使用二级索引作为条件，在获取整行数据时，需要回表，回表的记录数至少为offset + row_count。

3. 测试用数据库表说明

以下用于测试的数据库表为test_table_log、test_table_log2，两个表的字段相同，索引不同，如下所示：

  1. CREATE TABLE IF NOT EXISTS test_table_log
  2. (
  3.    id             varchar(32) NOT NULL COMMENT 'id',
  4.    type           varchar(20) NOT NULL COMMENT 'type',
  5.    cust_info      varchar(20) NOT NULL COMMENT 'cust_info',
  6.    status         varchar(2) NOT NULL COMMENT 'status',
  7.    other1         varchar(20) NOT NULL COMMENT 'other1',
  8.    other2         varchar(20) NOT NULL COMMENT 'other2',
  9.    create_time    DATETIME(3) NOT NULL COMMENT 'create_time',
  10.    update_time    DATETIME(3) NOT NULL COMMENT 'update_time',
  11.    PRIMARY KEY(id)
  12. )
  13. COMMENT = '测试流水表';
  15. CREATE TABLE IF NOT EXISTS test_table_log2 like test_table_log;
  17. alter table test_table_log add INDEX idx_ttl_type(type);
  18. alter table test_table_log add INDEX idx_ttl_cust(cust_info);
  19. alter table test_table_log add INDEX idx_ttl_status(status);
  20. alter table test_table_log add INDEX idx_ttl_time(create_time);
  22. alter table test_table_log2 add INDEX idx_ttl2_all(cust_info,create_time,type,status);
  23. alter table test_table_log2 add INDEX idx_ttl2_time(create_time);

以上两个表行数为500万行，数据相同，type范围为[‘1’,‘10’]，cust_info范围为[‘1000’,‘2000’]，status范围为[‘1’,‘3’]，other1固定为’1’，other2范围为[‘1000’,‘2000’]，create_time、update_time可看作插入时的时间。

以下进行测试时，在执行查询操作前，会重启数据库，防止MySQL在查询时使用缓冲池中的数据。

4. 分页查询常见场景

分页查询常见场景包括但不限于以下场景：

  • 使用联合索引查询

进行分页查询时，当查询语句中的WHERE、ORDER BY等条件涉及多个字段时，需要使用联合索引查询。

例如查询时需要限定客户信息（或其他区分度高的字段，以下统称“客户信息”）、时间范围，并根据时间进行排序。如客户查询自己的交易记录。

  • 使用单列索引查询

进行分页查询时，当查询语句中的WHERE、ORDER BY等条件只涉及一个字段时，需要使用单列索引查询。

例如查询时仅需要限定时间范围，并根据时间进行排序（不限定客户信息）。如内部管理系统查询，或对账时获取前一天交易数据的处理。

5. limit offset分页查询耗时对比

  • 使用联合索引查询

示例语句如下所示：

  1. select * from test_table_log2
  2. where cust_info='1881' and create_time>'2000-01-01' 
  3. order by create_time 
  4. limit 0,100;

当limit的offset增大时，查询耗时及检查行数变化如下所示：































limit offset Query_time Rows_examined
0 0.074398 100
1000 0.590641 1100
2500 1.319687 2600
4500 2.348282 4600
  • 使用单列索引查询

示例语句如下所示：

  1. select * from test_table_log2
  2. where create_time>'2000-01-01'
  3. order by create_time 
  4. limit 10000,100;

当limit的offset增大时，查询耗时及检查行数变化如下所示：




































limit offset Query_time Rows_examined
10000 0.104957 10100
100000 0.409859 100100
500000 1.650940 500100
2000000 8.355292 2000100
4000000 12.438349 9000100

以上指定“limit 4000000,100”时，EXPLAIN查看显示未使用索引idx_ttl2_time进行查询，进行全表扫描，使用filesort。

6. limit offset分页查询优化思路

针对limit offset分页查询的问题，可以通过以下两种方式进行优化：

6.1. 使用join减少回表次数

  • 优化方法

使用join，首先查询满足条件记录的主键，再与原表进行join，查询以上主键对应的整行数据，可以减少查询时需要回表的记录数。

  • 优化示例

示例如下：

  1. select b.* from
  2. (
  3. select id from test_table_log2
  4. where cust_info='1881' 
  5. order by create_time 
  6. limit 4000,10
  7. )as a inner join test_table_log2 as b
  8. on a.id=b.id;

最后可以省略“order by a.create_time”，减少一次不必要的filesort排序操作。因为查询test_table_log2表的主键id字段执行的查询操作结果是按create_time排序的，inner join后的结果也是以create_time排序。以上查询操作对create_time进行升序或降序排序时，上述结论均成立。

升序排序与降序排序均支持。

  • 优化效果

以上示例中，通过test_table_log2表的二级索引idx_ttl2_all(cust_info,create_time,type,status)，根据cust_info、create_time字段查询满足条件记录的主键id字段，该阶段的查询不需要回表。

当查询到满足条件的10条记录的主键id字段后，从test_table_log2表查询整行的数据，该阶段的查询需要回表，即有10条记录查询时需要回表。

优化前的SQL语句“select * from test_table_log2 where cust_info=‘1881’ order by create_time limit 4000,10;”，有4010条记录查询时需要回表。

对比可知，上述优化方法可以减少回表次数，提高查询效率。

  • 存在的限制

以上优化方法存在以下限制：

查询条件中（包括WHERE、ORDER BY、GROUP BY等）不能出现使用的索引字段或主键之外的其他字段，否则查询主键的过程中需要回表，优化会失效。

例如上述示例中，查询条件中只能出现idx_ttl2_all索引包含的cust_info、create_time、type、status字段，及主键id字段，否则优化会失效。

使用join减少回表次数，不能减少查询需要检查的行数。支持非连续/连续分页时查询。查询返回的行数固定（最后一次查询除外）。

6.2. 限定查询起始数据，不使用offset

  • 优化方法

每次查询时，需要将上次查询的最后一条记录作为本次查询的起始范围（首次查询不需要指定）。

  • 优化示例

    select * from test_table_log2
    where cust_info=’1881’ and create_time>’2020-03-10 12:29:06.000’
    order by create_time
    limit 10;

以上指定的时间，为上一次查询的最后一条记录的时间。每次查询时需要记录上一次查询最后一条记录的对应数据。

升序排序与降序排序均支持。

  • 优化效果

以上示例中，limit不需要指定offset，每次分页查询需要检查的行数接近（与实际数据分布有关）。

假如查询条件可以完全通过索引字段完成，则只需要读取二级索引中的10条记录，再对10条记录进行回表即可。

假如查询条件中包含了不在索引中的字段，则需要读取二级索引中的n条记录，再对n条记录进行回表，找到满足条件的10条记录（n大于等于10，与实际查询条件有关）。

  • 存在的限制

限定查询起始数据，不使用offset，可以减少查询需要检查的行数。仅支持连续分页时查询，不支持非连续分页查询。查询返回的行数可能不固定。

某个时间点的数据可能分布在多个分页中，需要特殊处理，如下文所示。

6.2.1. 某个时间点的数据分布在多个分页处理

需要考虑某个时间点的数据分布在多个分页的情况，需要确保查询时不漏数据，也不多数据。

例如每次查询10条记录，上一次查询的后x条记录，和下一次查询的前y条记录，都属于同一个时间点。

可以采用以下两种方式进行处理：

  • 分页查询时进行补充

查询条件指定“[时间] 大于 [上一次查询的最后一条记录的时间]”（或小于），可能导致查询丢失数据，需要进行补充。

每次查询完毕后，若结果非空，再查询本次查询的最后一条最后一条记录的时间对应的全部记录，若最后一条记录等于当前查询的最后一条记录，则不需要进行补充；若有多条记录，则将剩余数据拼接到返回列表尾部。

是否需要补充与实际数据有关，每次分页查询需要查询两次数据库，返回数据记录数可能比分页数量多。

示例如下：


































交易时间 主键
00:00:00 a
00:00:01 b
00:00:01 c
00:00:01 d
00:00:02 e
00:00:03 f

分页查询数量为3，第一次查询到主键为a、b、c记录，最后一条记录的时间为00:00:01，该时间还存在另一条主键为d的记录未查询到，需要再次查询时间为00:00:01的记录，排除主键为c及之前的记录，将剩余记录拼接到返回列表尾部。下次查询时间大于00:00:01的记录。

  • 分页查询时进行去重

查询条件指定“[时间] 大于等于 [上一次查询的最后一条记录的时间]”，会导致查询到重复的数据，需要进行去重。

每次查询完毕后，若结果非空，从查询结果列表头部开始遍历，查找主键等于上一次查询的最后一条记录主键的记录，将此之前的记录全部删除

每次分页查询需要只查询一次数据库，但需要对查询结果进行去重，返回数据可能比分页数量少。

以上处理的前提条件是，每次查询的分页数量，需要大于单位时间内（与时间精度有关，例如一秒内或一毫秒内）可能出现的记录数量，否则查询到对应记录时可能出现死循环。

示例如下：


























交易时间 主键
00:00:00 a
00:00:01 b
00:00:01 c
00:00:02 d

分页查询数量为3，第一次查询到主键a、b、c记录，最后一条记录的时间为00:00:01，第二次查询时间大于等于该时间的记录，查询到主键为b、c、d的记录，需要删除列表中主键为c及之前的记录再返回。下次查询时间大于等于00:00:02的记录。

7. limit offset分页查询优化方法

7.1. 使用联合索引进行查询

7.1.1. 选择单列索引还是联合索引

当查询语句中的WHERE、ORDER BY等涉及多个字段时，需要为相应的字段创建联合索引。

例如当需要分页查询指定客户信息在指定时间段的记录并通过时间排序时，需要为客户信息与时间字段创建联合索引，而不是创建多个单列索引。

原因如下：

  • 减少检查范围

使用单列索引只有一个字段可以通过索引进行查找；使用联合索引可以使查询条件中的字段都能通过索引进行查找，可以减少查询时的检查范围。

  • 避免filesort排序

使用单列索引时，排序条件可能需要使用filesort；使用联合索引时，排序可以通过索引完成，可以避免filesort排序。

以下为上述情况使用单列索引与联合索引的对比。

7.1.1.1. SQL语句
  • 使用单列索引

以下cust_info、create_time字段分别具有单列索引。

  1. select * from test_table_log
  2. where cust_info='1881' and create_time>'2000-01-01' 
  3. order by create_time 
  4. limit 100,10;
  • 使用联合索引

以下cust_info、create_time字段具有联合索引。

  1. select * from test_table_log2
  2. where cust_info='1881' and create_time>'2000-01-01' 
  3. order by create_time 
  4. limit 100,10;
7.1.1.2. 执行计划对比
  • 使用单列索引





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log ref idx_ttl_cust,idx_ttl_time idx_ttl_cust 22 const 10556 Using index condition; Using where; Using filesort
  • 使用联合索引





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 10110 Using index condition

根据以上对比可知，当查询语句中的WHERE、ORDER BY等涉及多个字段时，使用单列索引进行查询时，需要通过filesort进行排序；使用联合索引时不需要。

7.1.1.3. 执行耗时对比
  • 使用单列索引

升序排序：

  1. Query_time: 2.527070  Lock_time: 0.001380  Rows_sent: 10  Rows_examined: 5332

降序排序：

  1. Query_time: 2.445330  Lock_time: 0.001651  Rows_sent: 10  Rows_examined: 5332

以上Rows_examined结果5332，等于5222+100+10，5222为test_table_log表中cust_info=’1881’的记录行数。

  • 使用联合索引

升序排序：

  1. Query_time: 0.071628  Lock_time: 0.001535  Rows_sent: 10  Rows_examined: 110

降序排序：

  1. Query_time: 0.067902  Lock_time: 0.001456  Rows_sent: 10  Rows_examined: 110

以上Rows_examined结果110，等于100+10。

根据以上对比可知，使用联合索引进行查询时，检查的行数更少，耗时更短。

7.1.2. 支持非连续/连续分页时

以下为使用联合索引进行查询，支持非连续/连续分页时的优化方法。

7.1.2.1. SQL语句

以下指定的时间条件不会过滤掉数据，仅起示例作用。

  • 使用联合索引查询，优化前

    select * from test_table_log2
    where cust_info=’1881’ and create_time>’2000-01-01’
    order by create_time
    limit 4000,10;

  • 使用联合索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    select b.* from
    (
    select id from test_table_log2
    where cust_info=’1881’ and create_time>’2000-01-01’
    order by create_time
    limit 4000,10
    )as a inner join test_table_log2 as b
    on a.id=b.id;

  • 使用联合索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

以下查询条件中使用了不在idx_ttl2_all(cust_info,create_time,type,status)索引中的列other1，查询条件导致需要回表。

  1. select b.* from 
  2. (
  3. select id from test_table_log2
  4. where cust_info='1881' and create_time>'2000-01-01' and other1='1'
  5. order by create_time 
  6. limit 4000,10
  7. )as a inner join test_table_log2 as b
  8. on a.id=b.id;
7.1.2.2. 执行计划对比
  • 使用联合索引查询，优化前





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 10110 Using index condition
  • 使用联合索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后





















































id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4010
1 PRIMARY a eq_ref PRIMARY PRIMARY 34 b.id 1
2 DERIVED test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 10110 Using where; Using index
  • 使用联合索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后





















































id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4010
1 PRIMARY a eq_ref PRIMARY PRIMARY 34 b.id 1
2 DERIVED test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 10110 Using index condition; Using where

执行计划差别不大。

7.1.2.3. 查询操作耗时对比

  • 使用联合索引查询，优化前

    Query_time: 1.618924 Lock_time: 0.001202 Rows_sent: 10 Rows_examined: 4010

  • 使用联合索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    Query_time: 0.034697 Lock_time: 0.002150 Rows_sent: 10 Rows_examined: 4030

  • 使用联合索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

    Query_time: 1.999658 Lock_time: 0.001696 Rows_sent: 10 Rows_examined: 4030

根据以上对比可知，使用联合索引查询，支持非连续/连续分页时，当查询条件不会导致回表时，优化后可以缩短查询耗时并减少检查行数；当查询条件会导致回表时，优化后无效果。

7.1.2.4. 查询语句读取索引次数对比

  • 使用联合索引查询，优化前

    Handler_read_key | 1
    Handler_read_next | 4009

  • 使用联合索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    Handler_read_key | 11
    Handler_read_next | 4009
    Handler_read_rnd_next | 11

  • 使用联合索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

    Handler_read_key | 11
    Handler_read_next | 4009
    Handler_read_rnd_next | 11

查询语句读取索引次数差别不大。

7.1.2.5. InnoDB缓冲池中索引页与记录数对比

  • 使用联合索引查询，优化前

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 3692 | 735922 |
    | testdb.test_table_log2 | idx_ttl2_time | 23 | 8079 |
    | testdb.test_table_log2 | idx_ttl2_all | 17 | 5193 |

  • 使用联合索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | idx_ttl2_time | 23 | 8079 |
    | testdb.test_table_log2 | idx_ttl2_all | 17 | 5193 |
    | testdb.test_table_log2 | PRIMARY | 13 | 2865 |

  • 使用联合索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 3692 | 735922 |
    | testdb.test_table_log2 | idx_ttl2_time | 23 | 8079 |
    | testdb.test_table_log2 | idx_ttl2_all | 17 | 5193 |

根据以上对比可知，使用联合索引查询，支持非连续/连续分页时，优化后若查询条件不会导致回表，InnoDB缓冲池中主键对应的索引页与记录数明显减少，即减少了回表次数。

7.1.3. 仅支持连续分页时

以下为使用联合索引进行查询，仅支持连续分页时的优化方法。

为了简化查询操作，以下查询不考虑某个时间点的数据分布在多个分页内的情况。

7.1.3.1. SQL语句

  • 使用联合索引查询，优化前

    select * from test_table_log2
    where cust_info=’1881’ and create_time>’2000-01-01’
    order by create_time
    limit 4000,10;

  • 使用联合索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    select * from test_table_log2
    where cust_info=’1881’ and create_time>’2020-03-10 12:29:06.000’
    order by create_time
    limit 10;

  • 使用联合索引查询，查询条件会导致回表，仅支持连续分页时，优化后

以下查询条件中使用了不在idx_ttl2_all(cust_info,create_time,type,status)索引中的列other2，查询条件导致需要回表。

other2字段的范围为[‘1000,‘2000’]，以下查询条件指定other2>‘1900’，会过滤约90%的记录。

  1. select * from test_table_log2
  2. where cust_info='1881' and create_time>'2020-03-10 12:29:06.000' and other2>'1900'
  3. order by create_time 
  4. limit 10;
7.1.3.2. 执行计划对比
  • 使用联合索引查询，优化前





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 10110 Using index condition
  • 使用联合索引查询，查询条件不会导致回表，仅支持连续分页时，优化后





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 1210 Using index condition
  • 使用联合索引查询，查询条件会导致回表，仅支持连续分页时，优化后





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_all,idx_ttl2_time idx_ttl2_all 29 NULL 1210 Using index condition; Using where

根据以上对比可知，需要检查的行估计值rows在优化后减少。

7.1.3.3. 查询操作耗时对比

  • 使用联合索引查询，优化前

    Query_time: 1.843632 Lock_time: 0.001378 Rows_sent: 10 Rows_examined: 4010

  • 使用联合索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    Query_time: 0.024173 Lock_time: 0.001390 Rows_sent: 10 Rows_examined: 10

  • 使用联合索引查询，查询条件会导致回表，仅支持连续分页时，优化后

    Query_time: 0.060800 Lock_time: 0.001659 Rows_sent: 10 Rows_examined: 96

根据以上对比可知，使用联合索引查询，仅支持连续分页时，优化后可以缩短查询耗时并减少检查行数。

当查询条件会导致回表时，优化后也有效果，相比不会导致回表的情况，需要检查的行数更多，耗时更长（与数据实际分布有关）。

7.1.3.4. 查询语句读取索引次数对比

  • 使用联合索引查询，优化前

    Handler_read_key | 1
    Handler_read_next | 4009

  • 使用联合索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    Handler_read_key | 1
    Handler_read_next | 9

  • 使用联合索引查询，查询条件会导致回表，仅支持连续分页时，优化后

    Handler_read_key | 1
    Handler_read_next | 95

根据以上对比可知，使用联合索引查询，仅支持连续分页时，优化后查询语句读取索引次数明显变少。

7.1.3.5. InnoDB缓冲池中索引页与记录数对比

  • 使用联合索引查询，优化前

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 3694 | 736175 |
    | testdb.test_table_log2 | idx_ttl2_all | 39 | 12055 |
    | testdb.test_table_log2 | idx_ttl2_time | 23 | 8079 |

  • 使用联合索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | idx_ttl2_all | 29 | 8825 |
    | testdb.test_table_log2 | idx_ttl2_time | 20 | 7061 |
    | testdb.test_table_log2 | PRIMARY | 16 | 3088 |

  • 使用联合索引查询，查询条件会导致回表，仅支持连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 97 | 19190 |
    | testdb.test_table_log2 | idx_ttl2_all | 29 | 8825 |
    | testdb.test_table_log2 | idx_ttl2_time | 20 | 7061 |

根据以上对比可知，使用联合索引查询，仅支持连续分页时，优化后InnoDB缓冲池中二级索引及主键索引对应的索引页与记录数明显减少，即减少了查询过程中读取的行数及回表次数。

7.2. 使用单列索引查询

7.2.1. 支持非连续/连续分页时

以下为使用单列索引进行查询，支持非连续/连续分页时的优化方法。

7.2.1.1. SQL语句

  • 使用单列索引查询，优化前

    select * from test_table_log2
    where create_time>’2000-01-01’
    order by create_time
    limit 1000000,10;

  • 使用单列索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    select b.* from
    (
    select id from test_table_log2
    where create_time>’2000-01-01’
    order by create_time
    limit 1000000,10
    )as a inner join test_table_log2 as b
    on a.id=b.id;

  • 使用单列索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

以下查询条件中使用了不在idx_ttl2_time(create_time)索引中的列other1，查询条件导致需要回表。

  1. select b.* from 
  2. (
  3. select id from test_table_log2
  4. where create_time>'2000-01-01' and other1='1'
  5. order by create_time
  6. limit 1000000,10
  7. )as a inner join test_table_log2 as b
  8. on a.id=b.id;
7.2.1.2. 执行计划对比
  • 使用单列索引查询，优化前





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_time idx_ttl2_time 7 NULL 2363692 Using index condition
  • 使用单列索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后





















































id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1000010
1 PRIMARY a eq_ref PRIMARY PRIMARY 34 b.id 1
2 DERIVED test_table_log2 range idx_ttl2_time idx_ttl2_time 7 NULL 2363692 Using where; Using index
  • 使用单列索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后





















































id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1000010
1 PRIMARY a eq_ref PRIMARY PRIMARY 34 b.id 1
2 DERIVED test_table_log2 range idx_ttl2_time idx_ttl2_time 7 NULL 2363692 Using index condition; Using where

执行计划差别不大。

7.2.1.3. 查询操作耗时对比

  • 使用单列索引查询，优化前

    Query_time: 3.168855 Lock_time: 0.001209 Rows_sent: 10 Rows_examined: 1000010

  • 使用单列索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    Query_time: 0.503691 Lock_time: 0.001643 Rows_sent: 10 Rows_examined: 1000030

  • 使用单列索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

    Query_time: 3.204216 Lock_time: 0.001957 Rows_sent: 10 Rows_examined: 1000030

根据以上对比可知，使用单列索引查询，支持非连续/连续分页时，当查询条件不会导致回表时，优化后可以缩短查询耗时并减少检查行数；当查询条件会导致回表时，优化后无效果。

7.2.1.4. 查询语句读取索引次数对比

  • 使用单列索引查询，优化前

    Handler_read_key | 1
    Handler_read_next | 1000009

  • 使用单列索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    Handler_read_key | 11
    Handler_read_next | 1000009
    Handler_read_rnd_next | 11

  • 使用单列索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

    Handler_read_key | 11
    Handler_read_next | 1000009
    Handler_read_rnd_next | 11

查询语句读取索引次数差别不大。

7.2.1.5. InnoDB缓冲池中索引页与记录数对比

  • 使用单列索引查询，优化前

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 3998 | 789397 |
    | testdb.test_table_log2 | idx_ttl2_time | 1922 | 770751 |

  • 使用单列索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | idx_ttl2_time | 2541 | 1019866 |
    | testdb.test_table_log2 | PRIMARY | 4 | 748 |

  • 使用单列索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 3966 | 782781 |
    | testdb.test_table_log2 | idx_ttl2_time | 1925 | 771965 |

根据以上对比可知，使用单列索引查询，支持非连续/连续分页时，优化后若查询条件不会导致回表，InnoDB缓冲池中主键对应的索引页与记录数明显减少，即减少了回表次数。

7.2.2. 仅支持连续分页时

以下为使用单列索引进行查询，仅支持连续分页时的优化方法。

为了简化查询操作，以下查询不考虑某个时间点的数据分布在多个分页内的情况。

7.2.2.1. SQL语句

以下为使用单列索引进行查询，仅支持连续分页时的优化方法。

  • 使用单列索引查询，优化前

    select * from test_table_log2
    where create_time>’2000-01-01’
    order by create_time
    limit 1000000,10;

  • 使用单列索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    select * from test_table_log2
    where create_time>’2020-03-10 12:22:54.000’
    order by create_time
    limit 10;

  • 使用单列索引查询，查询条件会导致回表，仅支持连续分页时，优化后

以下查询条件中使用了不在idx_ttl2_time(create_time)索引中的列other2，查询条件导致需要回表。

other2字段的范围为[‘1000,‘2000’]，指定条件other2>‘1900’，会过滤约一半的记录。

  1. select * from test_table_log2
  2. where create_time>'2020-03-10 12:22:54.000' and other2>'1900'
  3. order by create_time
  4. limit 10;
7.2.2.2. 执行计划对比
  • 使用单列索引查询，优化前





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_time idx_ttl2_time 7 NULL 2363692 Using index condition
  • 使用单列索引查询，查询条件不会导致回表，仅支持连续分页时，优化后





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_time idx_ttl2_time 7 NULL 2363692 Using index condition
  • 使用单列索引查询，查询条件会导致回表，仅支持连续分页时，优化后





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test_table_log2 range idx_ttl2_time idx_ttl2_time 7 NULL 2363692 Using index condition; Using where

执行计划差别不大。

7.2.2.3. 查询操作耗时对比

  • 使用单列索引查询，优化前

    Query_time: 3.092399 Lock_time: 0.001658 Rows_sent: 10 Rows_examined: 1000010

  • 使用单列索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    Query_time: 0.012751 Lock_time: 0.001463 Rows_sent: 10 Rows_examined: 10

  • 使用单列索引查询，查询条件会导致回表，仅支持连续分页时，优化后

    Query_time: 0.015397 Lock_time: 0.001618 Rows_sent: 10 Rows_examined: 100

根据以上对比可知，使用单列索引查询，仅支持连续分页时，优化后可以缩短查询耗时及检查行数。

当查询条件会导致回表时，优化后也有效果，相比不会导致回表的情况，需要检查的行数更多，耗时更长（与数据实际分布有关）。

7.2.2.4. 查询语句读取索引次数对比

  • 使用单列索引查询，优化前

    Handler_read_key | 1
    Handler_read_next | 1000009

  • 使用单列索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    Handler_read_key | 1
    Handler_read_next | 9

  • 使用单列索引查询，查询条件会导致回表，仅支持连续分页时，优化后

    Handler_read_key | 1
    Handler_read_next | 99

根据以上对比可知，使用单列索引查询，仅支持连续分页时，优化后查询语句读取索引次数明显变少。

7.2.2.5. InnoDB缓冲池中索引页与记录数对比

  • 使用单列索引查询，优化前

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | PRIMARY | 3885 | 768404 |
    | testdb.test_table_log2 | idx_ttl2_time | 1868 | 749047 |
    | testdb.test_table_log2 | idx_ttl2_all | 23 | 6915 |

  • 使用单列索引查询，查询条件不会导致回表，仅支持连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | idx_ttl2_time | 23 | 8210 |
    | testdb.test_table_log2 | idx_ttl2_all | 23 | 6915 |
    | testdb.test_table_log2 | PRIMARY | 6 | 1143 |

  • 使用单列索引查询，查询条件会导致回表，仅支持连续分页时，优化后

    | TABLE_NAME | INDEX_NAME | page_num | record_num |
    +——————————————+———————-+—————+——————+
    | testdb.test_table_log2 | idx_ttl2_time | 23 | 8210 |
    | testdb.test_table_log2 | idx_ttl2_all | 23 | 6915 |
    | testdb.test_table_log2 | PRIMARY | 10 | 1935 |

根据以上对比可知，使用单列索引查询，仅支持连续分页时，优化后InnoDB缓冲池中二级索引及主键索引对应的索引页与记录数明显减少，减少了查询过程中读取的行数及回表次数。

8. limit的其他说明

8.1. 查询排序使用/不使用limit顺序不同

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

LIMIT会影响执行计划，指定的查询在使用/不使用LIMIT时，返回行的顺序可能会不同。

如果需要保证查询在使用/不使用LIMIT时，返回的行的顺序相同，需要在ORDER BY子名中包含其他的列，以使顺序确定。例如在ORDER BY中除了原有用于排序的字段外，还指定主键字段。

8.2. 判断是否存在指定条件的记录

当需要判断是否存在指定条件的记录，但不需要知道具体数量时，可以使用limit 1代替count()方法，若存在多条满足条件的记录，可以减少查询时检查的行数。

8.3. 批量更新/删除时限定执行行数

参考 https://dev.mysql.com/doc/refman/5.6/en/update.html 、 https://dev.mysql.com/doc/refman/5.6/en/delete.html 。

UPDATE、DELETE语句支持使用limit限定行数，在对多表进行更新或删除操作时，不支持使用limit。

发表评论

表情：
评论列表 （有 0 条评论，124人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql分页查询limit

    前言 分布式，是程序员必备技能之一，在面试过程中属于必备类的，在工作中更是会经常用到。而Kafka是一个分布式的基于发布订阅的消息队列，目前它的魅力是无穷的，对于Kafk

    た 入场券た 入场券/ 0 赞/ 364 阅读

    相关 MySQL Limit 分页优化

    在系统中需要进行分页的时候，我们通常会使用Limit加上偏移量的方法实现，问题来了，在偏移量非常大的时候，例如可能是limit 10000,10这样的查询，这时MySQL需要查

    た 入场券た 入场券/ 0 赞/ 400 阅读