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

深圳新闻 浏览(1809)

作者:《大神养成记》

6f7f44c7-7070-4e6c-8a6b-cee1ac3ec8fb

方法1:直接使用数据库提供的SQL语句

语句样式:在MySQL中,可以使用以下方法: SELECT * FROM表名LIMIT M,N适应场景:用于小数据量(元组/千)原因/缺点:全表扫描,速度会很慢而且某些数据库结果集返回不稳定(例如返回1,2,3,并返回2,1,3)。输出,其余的都被丢弃。

方法2:建立主键或唯一索引,)

语句样式:在MySQL中,可以使用以下方法: SELECT * FROM表名WHERE id_pk> (pageNum * 10)LIMIT M适应场景:为大数据量(数万元组)原因:索引扫描,速度很快。有些朋友建议使用:因为数据查询没有按照pk_id进行排序,因此会丢失数据,只有方法3

方法3:基于索引重新排序

Statement style: In MySQL, the following methods are available: SELECT * FROM table name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M Adaptation scene: applies to the case of a large amount of data (tens of thousands of tuples). The column object after ORDER BY is the primary key or unique so that the ORDERBY operation can be eliminated with the index but the result set is stable (stable meaning, see Method 1) Cause: Index scan, the speed will be very fast. But MySQL sort operation Only ASC has no DESC (DESC is fake, and will do real DESC in the future, expecting.).

Method 4: Use prepare based on index

The first question mark indicates pageNum, the second one? Represents the number of tuples per page

Statement style: In MySQL, the following methods are available: PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M Adaptation scene: Large data volume Cause: Index scan, speed will be very fast. The statement is a little faster than the general query.

Method 5: Use MySQL to support ORDER operations to quickly locate partial tuples using indexes to avoid full table scans

For example, reads the 1000th to 1019th row of tuples (pk is the primary key/unique key).

xxSELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

Method 6: Use 'Subquery/Connection+Index' to quickly locate the tuple and then read the tuple.

For example (id is the primary key/unique key, blue font variable)

Use the subquery example:

SELECT * FROM your_table WHERE id <=

(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc

LIMIT $pagesize

Take advantage of the connection example:

SELECT * FROM your_table AS t1

JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2

WHERE t1.id <=t2.id ORDER BY t1.id desc LIMIT $pagesize;

Mysql large data volume uses limit paging, as the page number increases, the query efficiency is lower.

Test experiment

1. Directly use the limit start, count paging statement, which is also used in my program:

xx从产品限制开始选择*,计数

当起始页面很小时,查询没有性能问题,我们分别看10,100,1000。

如下:

从产品限制10中选择*,20 0.016秒

从产品限制100中选择*,20 0.016秒

从产品限制1000中选择*,20 0.047秒

从产品限制10000,20 0.094秒

中选择*

我们已经看到,随着起始记录的增加,时间也会增加,这意味着分页语句限制与起始页码有很大关系,然后我们将起始记录更改为40w(即记录)一般左右)

从产品限制400000,20 3.229秒

中选择*

看看我们拍摄记录最后一页的时间

从产品限制866613,20 37.44秒

中选择*

很明显,这个时间是无法忍受的,就像具有最大分页页面的页面一样。

由此我们还可以总结两件事:

限制语句的查询时间与起始记录的位置成比例。 mysql的limit语句非常方便,但是有很多记录的表不适合直接使用。

2.极限分页问题的性能优化方法

使用表格的叠加索引来加速分页查询

我们都知道,如果使用索引查询的索引只包含那个索引列(覆盖索引),那么这种情况会非常快。

由于索引搜索具有优化算法,并且数据在查询索引上,因此无需查找相关数据地址,从而节省了大量时间。另外,Mysql中有一个相关的索引缓存,最好在并发性很高时使用缓存。

在我们的例子中,我们知道id字段是主键,并且自然包含默认的主键索引。现在让我们看看查询如何与叠加索引一起使用。

这次我们查询数据的最后一页(使用覆盖索引,只有id列),如下所示:

从产品限制866613,20 0.2秒

中选择ID

与37.44秒的所有列的查询相比,速度增加了约100倍

因此,如果我们要查询所有列,有两种方法,一种是id&gt;=的形式,另一种是使用join来查看实际情况:

SELECT * FROM产品WHERE ID&gt;=(从产品限制866613中选择id,1)限制20

查询时间是0.2秒!

另一种写作方式

SELECT * FROM product a JOIN(从产品限制中选择id 866613,20)b ON a.ID=b.id

查询时间也很短!

3. Composite index optimization method

How high can MySql performance be? MySql This database is definitely suitable for dba-level masters to play, generally do a little 10,000 news small system how to write can be achieved with xx framework can achieve rapid development. But the amount of data is 100,000 to 10 million, can his performance be so high? A small mistake may cause the entire system to be rewritten, and even the system will not function properly! Ok, not so much nonsense.

Speak with facts and see examples:

The data table collect ( id, title , info , vtype) is the four fields, where title is fixed length, info is text, id is gradual, vtype is tinyint, and vtype is index. This is a simple model of a basic news system. Now fill the data inside and fill in 100,000 news. Finally, collect is a record, and the database table occupies a hard 1.6G.

OK sql statement:

Select id, title from collect limit 1000,10;

Soon; basically 0.01 seconds is OK, then look at the following

Select id, title from collect limit 90000, 10;

Start paging, the result?

8-9 seconds to complete, my god statement:

Select id from collect order by id limit 90000,10;

Soon, 0.04 seconds is OK. why? Because using the id primary key for indexing is of course fast. The online change is:

xx选择id,title from title其中id&gt;=(从id限制90000,1中选择收集订单的id)限制10;

这是使用id索引的结果。但问题有点复杂,结束了。请看以下陈述

选择来自collect的id,其中vtype=1 order by id limit 90000,10;很慢,用了8-9秒!

当我到这里时,我相信很多人会像我一样,有一种崩溃的感觉! Vtype索引?怎么会慢? Vtype索引很好,你直接

从collect中选择id,其中vtype=1 limit 1000,10;

它非常快,基本上是0.05秒,但它高出90倍。从90,000开始,它是0.05 * 90=4.5秒。测试结果是8-9秒的数量级。

从这里开始,有些人提出了子表的想法。这与dis #cuz论坛的想法相同。这些想法如下:

创建一个索引表:t(id,title,vtype)并将其设置为固定长度,然后执行分页,分页结果然后转到collect以查找信息。这可行吗?我会在实验中知道它。

记录在t(id,title,vtype)中,数据表大小约为20M。使用

从t中选择id,其中vtype=1 order by id limit 90000,10;

不久。基本上0.1-0.2秒就可以完成。为什么会这样?我猜因为收集数据太多,所以分页必须走很长的路。限制与数据表的大小完全相关。实际上,这仍然是一个全表扫描,只是因为数据量很小,只有100,000快。好的,测试性能。添加10倍数据,t表将达到200 M以上,并且它是固定长度。或者只是查询,时间是0.1-0.2秒完成!表的性能没有问题?

错误!因为我们的限制仍然是90,000,所以很快。给一个大的,从900,000开始

从t中选择id,其中vtype=1 order by id limit 900000,10;

看结果,时间是1-2秒!为什么?

记录的长度是固定的,mysql应该能够计算出900,000的位置吗?但是,我们高估了mysql的记录会很慢,我相信这是真的,这与数据库设计有关!

MySQL无法突破100万的限制吗?当我达到100万页时,它确实是极限。

答案是:NO为什么不能突破100万,因为它不会被mysql设计。下面介绍非段方法,疯狂测试!一张表可以获得100万条记录,以及10G数据库,如何快速分页!

好的,我们的测试会回到收集表,测试结果如下:

30万数据,使用子表的方法,超过30万,他的速度会慢,你受不了了!当然,如果你使用子表+我的方法,它绝对是完美的。但是在使用这种方法之后,我可以在没有子表的情况下完美地解决它!

答案是:综合指数!一旦我设计了mysql索引,我就意外地发现索引名称可以被采用。您可以选择几个字段进来。有什么用?

开始

通过ID限制90000,10;

从收集订单中选择ID

因为索引如此之快,但是如果添加你不加索引的地方。持有尝试添加搜索(vtype,id)之类的索引的想法。

然后测试

从collect中选择id,其中vtype=1 limit 90000,10;

非常快! 0.04秒完成!

再次测试:

选择id,title from collect其中vtype=1 limit 90000,10;

很抱歉,8-9秒,没有采取搜索索引!

重新测试:搜索(id,vtype),或者选择id这个语句,也非常遗憾,0.5秒。

总结:如果有where条件,并且想要带有限制的索引,则必须设计一个索引,放在第一个位置,限制使用的主键放在第二位,并且只能选择主键!

完美解决了分页问题。您可以快速返回id并希望优化限制。根据这个逻辑,数百万级别的限制应该在0.0x秒内划分。看来mysql语句的优化和索引非常重要!