背景
一个行数为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