mysql limit offset 原理分析与使用

背景

一个行数为4亿条的表。

查询50000000~50000010行之间的数据。发现查询时间达到20s！！！

查询执行计划发现，需要进行全表扫描，没有索引。

但是，sbtest1这个表是有索引的

为什么mysql没有选择索引，而是全表扫描呢？

分析

mysql select 语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

https://dev.mysql.com/doc/refman/8.0/en/select.html

offset 实现

MySQL的limit m n工作原理就是先读取前面m+n条记录，然后抛弃前m条，读后面n条想要的，所以m越大，偏移量越大，性能就越差。
https://cloud.tencent.com/developer/article/1505252

解决

在网上找到一个解决办法：可以把limit的offset当做where条件，这样mysql直接走索引，通过B+树直接定位到offset位置。类似于看书用书签标记下看到哪里的思路。

查询执行计划发现的确如此

如果使用非聚簇索引，也可以达到相同的效果

引申

比如：订单表、商品表，如果想做到分页效果、并提高用户体验，选择这种优化会有很大的提升。

就好比openstack的开源项目trove模块就有此设计，通过marker参数来保证一直分页的offset。

marker的使用逻辑为：

• 本次查询的最后一条记录标记为next_marker，
• 将next_marker作为api结果的一部分返回给用户，
• 用户下次再请求这个api时，把next_marker赋值给marker传入进来

实现逻辑

• 由于trove支持多种数据库，所以mysql user这类url需要通过注册的方式添加
  
  https://github.com/openstack/trove/blob/master/trove/extensions/routes/mysql.py#L48

• def index方法是/users的controller层实现，在这里调用了models.Users.load(context)方法，第一个参数是context，这个参数是trove封装的上下文里面包含一些基本信息。
  
  https://github.com/openstack/trove/blob/master/trove/extensions/mysql/service.py#L64

• 从上面controller层，调用到了models.User.load(context)方法，经过几次转换，调用到client.list_users(marker)方法参数带有marker。调用流程Users.load() -> load_via_context() -> Users.load_with_client(context.marker) -> client.list_users(marker)
  
  https://github.com/openstack/trove/blob/master/trove/extensions/mysql/models.py#L182

• client.list_users的实现（manager层）
  
  https://github.com/openstack/trove/blob/master/trove/guestagent/datastore/manager.py#L833

• list_users的service层实现，注释很详细，就不解释了。
  https://github.com/openstack/trove/blob/master/trove/guestagent/datastore/mysql_common/service.py#L340