MySQL limit分页查询优化实践

最新推荐文章于 2023-08-17 11:51:46 发布
最新推荐文章于 2023-08-17 11:51:46 发布
阅读量707 收藏 2
点赞数 2
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a82514921/article/details/108070062
版权

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，两个表的字段相同，索引不同，如下所示：

CREATE TABLE IF NOT EXISTS test_table_log
(
   id             varchar(32) NOT NULL COMMENT 'id',
   type           varchar(20) NOT NULL COMMENT 'type',
   cust_info      varchar(20) NOT NULL COMMENT 'cust_info',
   status         varchar(2) NOT NULL COMMENT 'status',
   other1         varchar(20) NOT NULL COMMENT 'other1',
   other2         varchar(20) NOT NULL COMMENT 'other2',
   create_time    DATETIME(3) NOT NULL COMMENT 'create_time',
   update_time    DATETIME(3) NOT NULL COMMENT 'update_time',
   PRIMARY KEY(id)
)
COMMENT = '测试流水表';

CREATE TABLE IF NOT EXISTS test_table_log2 like test_table_log;

alter table test_table_log add INDEX idx_ttl_type(type);
alter table test_table_log add INDEX idx_ttl_cust(cust_info);
alter table test_table_log add INDEX idx_ttl_status(status);
alter table test_table_log add INDEX idx_ttl_time(create_time);

alter table test_table_log2 add INDEX idx_ttl2_all(cust_info,create_time,type,status);
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分页查询耗时对比

  • 使用联合索引查询

示例语句如下所示：

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

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

limit offsetQuery_timeRows_examined
00.074398100
10000.5906411100
25001.3196872600
45002.3482824600
  • 使用单列索引查询

示例语句如下所示：

select * from test_table_log2
where create_time>'2000-01-01'
order by create_time 
limit 10000,100;

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

limit offsetQuery_timeRows_examined
100000.10495710100
1000000.409859100100
5000001.650940500100
20000008.3552922000100
400000012.4383499000100

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

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

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

6.1. 使用join减少回表次数

  • 优化方法

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

  • 优化示例

示例如下：

select b.* from
(
select id from test_table_log2
where cust_info='1881' 
order by create_time 
limit 4000,10
)as a inner join test_table_log2 as b
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:00a
00:00:01b
00:00:01c
00:00:01d
00:00:02e
00:00:03f

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

  • 分页查询时进行去重

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

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

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

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

示例如下：

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

分页查询数量为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字段分别具有单列索引。

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

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

select * from test_table_log2
where cust_info='1881' and create_time>'2000-01-01' 
order by create_time 
limit 100,10;
7.1.1.2. 执行计划对比
  • 使用单列索引
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_logrefidx_ttl_cust,idx_ttl_timeidx_ttl_cust22const10556Using index condition; Using where; Using filesort
  • 使用联合索引
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL10110Using index condition

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

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

升序排序：

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

降序排序：

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’的记录行数。

  • 使用联合索引

升序排序：

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

降序排序：

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，查询条件导致需要回表。

select b.* from 
(
select id from test_table_log2
where cust_info='1881' and create_time>'2000-01-01' and other1='1'
order by create_time 
limit 4000,10
)as a inner join test_table_log2 as b
on a.id=b.id;
7.1.2.2. 执行计划对比
  • 使用联合索引查询，优化前
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL10110Using index condition
  • 使用联合索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARY<derived2>ALLNULLNULLNULLNULL4010
1PRIMARYaeq_refPRIMARYPRIMARY34b.id1
2DERIVEDtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL10110Using where; Using index
  • 使用联合索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARY<derived2>ALLNULLNULLNULLNULL4010
1PRIMARYaeq_refPRIMARYPRIMARY34b.id1
2DERIVEDtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL10110Using 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%的记录。

select * from test_table_log2
where cust_info='1881' and create_time>'2020-03-10 12:29:06.000' and other2>'1900'
order by create_time 
limit 10;
7.1.3.2. 执行计划对比
  • 使用联合索引查询，优化前
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL10110Using index condition
  • 使用联合索引查询，查询条件不会导致回表，仅支持连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL1210Using index condition
  • 使用联合索引查询，查询条件会导致回表，仅支持连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_all,idx_ttl2_timeidx_ttl2_all29NULL1210Using 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，查询条件导致需要回表。

select b.* from 
(
select id from test_table_log2
where create_time>'2000-01-01' and other1='1'
order by create_time
limit 1000000,10
)as a inner join test_table_log2 as b
on a.id=b.id;
7.2.1.2. 执行计划对比
  • 使用单列索引查询，优化前
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_timeidx_ttl2_time7NULL2363692Using index condition
  • 使用单列索引查询，查询条件不会导致回表，支持非连续/连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARY<derived2>ALLNULLNULLNULLNULL1000010
1PRIMARYaeq_refPRIMARYPRIMARY34b.id1
2DERIVEDtest_table_log2rangeidx_ttl2_timeidx_ttl2_time7NULL2363692Using where; Using index
  • 使用单列索引查询，查询条件会导致回表，支持非连续/连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARY<derived2>ALLNULLNULLNULLNULL1000010
1PRIMARYaeq_refPRIMARYPRIMARY34b.id1
2DERIVEDtest_table_log2rangeidx_ttl2_timeidx_ttl2_time7NULL2363692Using 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’，会过滤约一半的记录。

select * from test_table_log2
where create_time>'2020-03-10 12:22:54.000' and other2>'1900'
order by create_time
limit 10;
7.2.2.2. 执行计划对比
  • 使用单列索引查询，优化前
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_timeidx_ttl2_time7NULL2363692Using index condition
  • 使用单列索引查询，查询条件不会导致回表，仅支持连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_timeidx_ttl2_time7NULL2363692Using index condition
  • 使用单列索引查询，查询条件会导致回表，仅支持连续分页时，优化后
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest_table_log2rangeidx_ttl2_timeidx_ttl2_time7NULL2363692Using 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。

adrninistrat0r
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql Limit 分页查询优化详解
09-10
主要介绍了Mysql Limit 分页查询优化的相关资料,非常不错，介绍的非常详细，具有参考借鉴价值，需要的朋友可以参考下
数据库刷题小结
upUpUP-ls的博客
01-03 183
数据库刷题limit和offset的用法 limit和offset的用法 数据库专题第二题 1.select* from test LIMIT 3 当limit后面跟一个参数的时候，该参数表示要取的数据的数量。表示直接取前三条数据 以下的两种方式均表示取2,3,4三条条数据。 1.select* from test LIMIT 1,3; 当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。 2.select * from test LIMIT 3 OFFSET 1;(在mysq
SQL Limit语句
hello_cmy的博客
03-09 3859
要检索查询返回的行的一部分，请使用LIMIT和OFFSET子句。 以下说明了这些子句的语法： SELECT column_list FROM table1 ORDER BY column_list LIMIT row_count OFFSET offset; 在这个语法中， row_count确定将返回的行数。 OFFSET子句在开始返回行之前跳过偏移行。 OFFSE...
在使用limit的offset比较大的时候,使用子查询优化sql,减少回表操作
奔跑的菜鸡
05-07 1398
在使用limit的offset比较大的时候,比如 从offeset=30000开始取5条数据 select * from t where a = ‘11’ limit 30000,5 有一张财务流水表，未分库分表，目前的数据量为9555695，分页查询使用到了limit，优化之前的查询耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，...
第二次因为没有查询主键而导致数据查询结果和返回的条数不同哭 伤心 大哭 不开心 流泪 桑心
weixin_43985693的博客
04-09 144
<select id="querySeq" resultMap="seq"> select sd.id,prd.item,prd.descn,emp.name,sd.prdno,sd.recdate,sd.seq,sd.bedno,sd.qty from scan_data sd INNER JOIN prdmoedl prd on sd.item=pr...
【MySQL】MySQL 用offset 和 limit 分页性能太差！
最新发布
wjianwei666的专栏
08-17 312
随着时代的进步，随着野心勃勃的企业想要变成下一个 Facebook，随着为机器学习预测收集尽可能多数据的想法的出现，作为开发人员，我们要不断地打磨我们的 API，让它们提供可靠和有效的端点，从而毫不费力地浏览海量数据。
mysql limit分页优化详细介绍
12-16
mysql limit分页优化 同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的。 网上也很多关于limit的五条优化准则，都是翻译自MySQL...
mysql limit分页优化方法分享
09-11
MySQL的优化是非常重要的。其他最常用也最需要优化的就是limit。MySQL的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。
mysql分页查询时，数据遗漏问题
WentingLeng的博客
12-18 1925
select param_name from table_name LIMIT 40,10 ; mysql做分页查询时，如果param_name 有相同值时，可能就会出现数据遗漏问题。 此时需要加上排序，order by id;  根据唯一值排序，确保顺序唯一。...
分页请求时，可能导致的数据重复，遗漏问题的几种解决方法
a2700419的博客
07-04 2384
分页请求时，可能导致的数据重复，遗漏问题的几种解决方法
Mysql——》优化limit分页
小仙~
07-11 415
在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。
MySQL分页查询limit优化的两种思路
Victor Lv的博客
04-11 5601
我们知道，limit分页，当翻到比较靠后的页时，查询效率会越来越慢。如这则帖子所述： 浅谈PageHelper插件分页实现原理及大数据量下SQL查询效率问题解决 博主这也是一种不错的思路。其实针对分页这个问题，了解其为什么慢就知道优化方法了，按我的理解如下（不一定说的很准确哈）： （1）以前select * from table order by id limit 100, 10这种方式，分页是怎么分的呢，是从磁盘拿整一个数据页到存储引擎缓冲区，比如 limit 100, 10 那就要按顺序先取出前100条
Mysql中limit的分页优化方案
@素素~的博客
03-01 143
Mysql中limit的分页优化方案
MySQL大数据量分页查询方法及其优化
龙cc的博客
02-06 742
—方法1: 直接使用数据库提供的SQL语句 —语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N —适应场景: 适用于数据量较少的情况(元组百/千级) —原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃. —方法2:
Limit 分页查询优化
憧憬的专栏
04-29 2008
1）简单的查询分页；分每页5条 limit [offset],[rows] select t.* from t_topic t LIMIT 90000,10; 对上面的mysql语句说明：limit 90000,10的意思扫描满足条件的90010行，扔掉前面的90000行，返回最后的10行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。文中还提到limit n性能是没问题的，因为只扫描n行。 2）建立id
mysql查询时offset过大影响性能的原因和优化
mameng1988的博客
02-15 2368
1 问题现状 起初我们采用最为简单的offset+limit分页查询方式，伪代码具体如下： List<Student> students = new ArrayList<>(); int limit = n; int offset = 0; do { students = SELECT * FROM [table] WHERE status = [status] AND gradeId = [id] LIMIT [limit] OFFSET [offset]; push
MySQL中的回表查询与索引覆盖：一次百万级别分页查询使用Limit 从90秒到0.6秒的优化
小兀的博客
04-06 4260
这里写目录标题事故现场解决方案提到的“回表查询”InnoDB的索引什么是回表查询怎么优化回表查询 事故现场 数据库使用的MySQL，有一个日志表，需要进行分页查询，于是很容易就想到了limit [offset偏移量] [count数量]这个查询方式,当我们偏移量比较小时,似乎是没什么问题 SELECT * FROM t_log WHERE type = 1 LIMIT 5, 50 查询时间:0...
limit入坑指南一：回表查询
Ethan_199402的博客
07-31 931
limit 原理 第一步.先查询offset+count条数据； 第二步.再抛弃前offset条数据 Limit效率高：数据库的数量很大，但是只需要查询一部分数据的情况。 　　高效率的原理是：避免全表扫描，提高查询效率。 上图员工表的查询即全表扫描，那么怎么避免全表扫描 加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。 imit的效率低：只使用limit来查询语句，并且偏移量特别大的情况 　　现在有一个订单表，数据量达到千万级表（真正不会这么查询，这里只是举例） 语句1
mysql limit查询优化_MySQL limit 分页查询优化（百万级优化）
weixin_42524762的博客
01-18 191
1)简单的查询分页；分每页5条 limit [offset],[rows]select t.* from t_topic t LIMIT 90000,10;2)建立id索引；查询索引idselect * from t_topic where id>=(select id from t_topic order by id limit 90000,1) limit 10;3)使用 between...
mysql分页查询limit优化
04-03
在MySQL中，如果WHERE条件中使用了索引列，那么limit语句的执行效率会大大提高。因此，在分页查询中，如果能使用索引来过滤数据，就能提高查询效率。例如： SELECT * FROM table WHERE index_column > 10 LIMIT 10;...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值