mysql分页优化

最新推荐文章于 2024-04-17 10:40:43 发布

一闪一闪满天星

最新推荐文章于 2024-04-17 10:40:43 发布

阅读量1.8k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_43513459/article/details/117382499

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

通常我们使用offset+limit的方式进行分页查询，然而随着数据表数据量越来越大，这种分页查询的方式性能也会随之降低。
本篇主要介绍如何对分页查询进行优化。

起因

offset+limit方式的分页查询，当数据表超过100w条记录，性能会很差。
主要原因是offset limit的分页方式是从头开始查询，然后舍弃前offset个记录，所以offset偏移量越大，查询速度越慢。

分页查询优化

使用子查询的方式，也是比较常用的方式，先使用子查询查询出分页所在的起始id的值
SELECT * FROM table WHERE id >= (SELECT id FROM table order by id limit 1000000, 1) LIMIT 10;
使用order by id可以在查询时使用主键索引。

但是这种方式在id为uuid的时候就会出现问题。可以使用where in的方式解决：
select * from table where id in (select id from table order by id limit 100000,10);

带条件的查询：
如果在分页查询中添加了where条件例如 type = 'a’这样的条件，sql变成：select * from table where id in (select id from table where type=‘a’ order by id limit 10000,10) 。
这种情况因为type没有使用索引也会导致查询速度变慢。但是只添加type为索引查询速度还是很慢,是因为查询的数据量太多了。这个时候考虑添加组合索引，组合索引的顺序要where条件字段在前，id在后，如 (type,id)，因为组合索引查询时用到了type索引，而type跟id是组合索引的关系，如果只select id ，那么直接就可以按组合索引返回id，而不需要再进行一次查询去返回id。

mysql推荐使用自增id作为数据表的主键，不要使用uuid作为数据表的主键。

使用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条件判断。

结论

mysql数据表记录数超过几十万时，使用limit进行分页，性能会比较差
mysql官方推荐不要使用uuid作为主键，而使用自增id作为主键。
mysql表的索引会影响查询的默认排序，并不绝对是按主键排序。
查询时一定要添加order by id
分布式情况下推荐使用带时间属性的自增长id（分布式自增长id算法）
性能：count()≈count(1)>count(id)>count(field)，因为mysql()做过优化，会自动选择成本最小的方式查询,前提是只有在Mysql5.6之后的版本才有优化https://blog.csdn.net/bocai_xiaodaidai/article/details/103238818
mysql官方关于count(1)和count()的说明：InnoDB handles SELECT COUNT() and SELECT COUNT(1) operations in the same way. There is no performance difference.
主备切换策略：可靠性优先策略，可用性优先策略。通常使用可靠性优先策略
在对数据库建索引，尽量选择区分度高的列建索引，使用组合索引代替多个单列索引，遵循左前缀原则等，建组合做因可以避免非聚簇索引二次查询（先查到索引，再根据索引关联的聚集索引也就是主键再查询另一个字段）
mysql in在查询的时候，如果查询的数据比较多，mysql会认为全部扫描速度更快会进行全表查询，在数据少的情况下会走索引。

reference

一闪一闪满天星

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
mysql分页优化

通常我们使用offset+limit的方式进行分页查询，然而随着数据表数据量越来越大，这种分页查询的方式性能也会随之降低。本篇主要介绍如何对分页查询进行优化。起因offset+limit方式的分页查询，当数据表超过100w条记录，性能会很差。主要原因是offset limit的分页方式是从头开始查询，然后舍弃前offset个记录，所以offset偏移量越大，查询速度越慢。分页查询优化使用子查询的方式，也是比较常用的方式，先使用子查询查询出分页所在的起始id的值SELECT * FROM tab
复制链接

扫一扫