mysql 수천만 데이터 페이징 쿼리 성능 최적화

mysql 수천만 데이터 페이징 쿼리 성능 최적화_Mysql

小云云

풀어 주다： 2017-12-08 09:22:00

원래의

2396명이 탐색했습니다.

MySQL은 데이터 양이 많을 때 제한 페이징을 사용합니다. 페이지 수가 증가하면 쿼리 효율성이 떨어집니다. 이 기사는 mysql을 사용하여 수천만 개의 데이터로 페이징 쿼리를 수행할 때 저자의 성능 최적화 방법을 공유합니다. 매우 좋은 기사이며 모든 사람에게 도움이 되기를 바랍니다.

실험

1. Limit start, count 페이징 문을 직접 사용:

select * from order Limit start, countselect * from order limit start, count

当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条），如下：

select * from order limit 10, 20 0.016秒

select * from order limit 100, 20 0.016秒

select * from order limit 1000, 20 0.047秒

select * from order limit 10000, 20 0.094秒

로그인 후 복사

我们已经看出随着起始记录的增加，时间也随着增大，这说明分页语句limit跟起始页码是有很大关系的，那么我们把起始记录改为40w看下

select * from order limit 400000, 20 3.229秒

再看我们取最后一页记录的时间

select * from order limit 800000, 20 37.44秒

显然这种时间是无法忍受的。

从中我们也能总结出两件事情：

1）limit语句的查询时间与起始记录的位置成正比

2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

2.对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

select id from order limit 800000, 20 0.2秒

相对于查询了所有列的37.44秒，提升了大概100多倍的速度

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

SELECT * FROM order WHERE ID > =(select id from order limit 800000, 1) limit 20

查询时间为0.2秒，简直是一个质的飞跃啊，哈哈

另一种写法

SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id

시작 페이지가 작을 경우 성능 문제가 없습니다. 쿼리를 통해 각각 다음과 같이 10, 100, 1000, 10000(페이지당 20개의 레코드 사용)부터 시작하는 페이징 실행 시간을 살펴봅니다.

rrreee

우리는 시작 레코드가 늘어나면 시간도 늘어나는데 이는 페이징 문 제한이 시작 페이지 번호와 밀접한 관련이 있음을 보여주므로 시작 레코드를 40w로 변경하여 살펴보겠습니다

주문 제한에서 *를 선택하세요 400000, 20 3.229초 기록의 마지막 페이지를 가져오는 데 걸린 시간을 보세요

주문 제한 800000, 20 37.44초에서 *를 선택하세요분명히 이런 시간입니다 참을 수 없습니다.

이로부터 두 가지 결론을 내릴 수도 있습니다. 1) 제한 문의 쿼리 시간은 시작 레코드의 위치에 비례합니다.

🎜2) mysql의 제한 문은 매우 편리하지만 적합하지 않습니다. 레코드가 많은 테이블을 직접 사용하세요. 🎜🎜2. 제한 페이징 문제에 대한 성능 최적화 방법🎜🎜페이징 쿼리 속도를 높이려면 테이블의 커버링 인덱스를 사용하세요🎜🎜인덱스 쿼리를 사용하는 명령문에 해당 인덱스 열(커버링 인덱스)만 포함되어 있으면 이 상황은 신속하게 쿼리됩니다. 🎜🎜인덱스 검색에는 최적화 알고리즘이 있고, 쿼리 인덱스에 데이터가 있기 때문에 해당 데이터 주소를 찾을 필요가 없어 시간이 많이 절약됩니다. 게다가 MySQL에도 관련 인덱스 캐시가 있는데, 동시성이 높을 때 캐시를 사용하는 것이 좋습니다. 🎜🎜이 예에서는 id 필드가 기본 키라는 것을 알고 있으므로 기본 기본 키 인덱스가 자연스럽게 포함됩니다. 이제 커버링 인덱스를 사용한 쿼리가 어떻게 수행되는지 살펴보겠습니다. 🎜🎜이번에는 다음과 같이 마지막 페이지의 데이터를 쿼리합니다(커버링 인덱스 사용, id 열만 포함). 🎜🎜주문 제한에서 ID 선택 800000 , 20 0.2초🎜🎜전체 열을 쿼리하는 데 걸리는 37.44초에 비해 속도는 약 100배 향상됩니다🎜🎜그래서 모든 열도 쿼리하려면 두 가지 방법이 있는데 하나는 id> = 0.2초라는 형태는 단순히 질적 도약이군요 ㅎㅎ🎜🎜또 다른 표현으로🎜🎜SELECT * FROM order a JOIN (주문 제한 800000, 20에서 id 선택) b ON a.ID = b. id🎜🎜쿼리 시간도 매우 짧습니다. 🎜🎜아직 배우셨나요? 서둘러서 사용해 보세요. 🎜🎜관련 권장 사항: 🎜🎜🎜mysql 수천만 개의 데이터 쿼리🎜🎜🎜🎜mysql 수천만 개의 통계 비교_MySQL🎜🎜🎜🎜MySQL 백만 단위 페이징 최적화(Mysql 수천만 개의 빠른 페이징)_MySQL🎜🎜

위 내용은 mysql 수천만 데이터 페이징 쿼리 성능 최적화_Mysql의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!