MySQL百万级数据量分页查询方法及其优化建议

MySQL百万级数据量分页查询方法及其优化建议,第1张

offset+limit方式的分页查询,当数据表超过100w条记录,性能会很差。

主要原因是offset limit的分页方式是从头开始查询,然后舍弃前offset个记录,所以offset偏移量越大,查询速度越慢。

比如: 读第10000到10019行元素(pk是主键/唯一键).

使用order by id可以在查询时使用主键索引

但是这种方式在id为uuid的时候就会出现问题。可以使用where in的方式解决:

带条件的查询:

如果在分页查询中添加了where条件例如 type = 'a’这样的条件,sql变成 :

这种情况因为type没有使用索引也会导致查询速度变慢。但是只添加type为索引查询速度还是很慢,是因为查询的数据量太多了。这个时候考虑添加组合索引,组合索引的顺序要where条件字段在前,id在后,如 (type,id),因为组合索引查询时用到了type索引,而type跟id是组合索引的关系,如果只select id ,那么直接就可以按组合索引返回id,而不需要再进行一次查询去返回id

使用uuid作为主键不仅会带来性能上的问题,在查询时也会遇到问题。

因为在使用select id from table limit 10000,10 查询id数据时,默认是对id进行排序,返回的是排序后的id结果,如果我们想按插入顺序查询结果,这样查询出来的结果就与我们的需求不相符。

聚集索引跟非聚集索引:聚集索引类似与新华字典的拼音,根据拼音搜索到的信息都是连续的,可以很快获取到它前后的信息。非聚集索引类似于部首查询,信息存放的位置可能不在一个区域。对经常使用范围查询的字段考虑使用聚集索引。

InnoDB中索引分为聚簇索引(主键索引)和非聚簇索引(非主键索引),聚簇索引的叶子节点中保存的是整行记录,而非聚簇索引的叶子节点中保存的是该行记录的主键的值。

如果您的表上定义有主键,该主键索引是聚集索引。

如果你不定义为您的表的主键时,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚集索引。

如果没有这样的列,InnoDB就自己产生一个这样的ID值,

优先选index key_len小的索引进行count(*),尽量不使用聚簇索引

在没有where条件的情况下,count(*)和count(常量),如果有非聚簇索引,mysql会自动选择非聚簇索引,因为非聚簇索引所占的空间小,如果没有非聚簇索引会使用聚集索引。count(primary key)主键id为聚集索引,使用聚集索引。有where条件的情况下,是否使用索引会根据where条件判断。

目前公司的订单表有100多万条,使用订单号查询数据时,所需时间大多要10-30秒不等,查看了慢查询日志,发现有的订单查询竟然耗时65秒

我查看了原有的查询语句,发现where后面跟了or查询,虽然3个or都索引,使用explain分析查询结果,发现要扫描近70万行,几乎是全盘扫描一遍,只为获取最多3条数据,效率实在是低下

这3个字段均设置了索引,但or在这个语句中,使索引失效了(主要看最后几行)

使用union all代替or查询,也就是说把3个字段的查询分别做查询,将结果使用union all连接在一起,这样单次查询可以用到索引,效率大大提高

先看一下分析结果

简要的sql语句,查询结果不超80ms


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/9978700.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-05-04
下一篇 2023-05-04

发表评论

登录后才能评论

评论列表(0条)

保存