MySQL 페이징 쿼리를 최적화하는 방법

페이징 쿼리 최적화 방법: 1. 하위 쿼리 최적화, 페이징 SQL 문을 하위 쿼리로 다시 작성하면 성능 향상을 얻을 수 있습니다. 2. ID 제한 최적화, 쿼리된 페이지 수와 쿼리된 레코드 수를 기반으로 쿼리된 ID의 범위를 계산한 다음 "와 사이의 ID" 문을 기반으로 쿼리할 수 있습니다. 3. 인덱스 재정렬을 기반으로 최적화하고, 인덱스를 통해 관련 데이터 주소를 찾고, 전체 테이블 스캔을 방지합니다. 4. 지연된 연관 최적화의 경우 JOIN을 사용하여 먼저 인덱스 열에 대한 페이징 작업을 완료한 다음 테이블로 돌아가서 필요한 열을 얻을 수 있습니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

페이징 쿼리의 효율성은 데이터 양이 많아 프런트엔드 응답과 사용자 경험에 영향을 미칠 때 특히 중요합니다.

페이징 쿼리 최적화 방법

1. 하위 쿼리 최적화 사용

이 방법은 먼저 오프셋 위치에서 ID를 찾은 다음, ID가 거꾸로 쿼리되는 경우에 적합합니다. 증가하고 있습니다.

하위 쿼리 최적화 원칙: https://www.jianshu.com/p/0768ebc4e28d

select * from sbtest1 where k=504878 limit 100000,5;쿼리 프로세스:

먼저 인덱스 리프 노드 데이터를 쿼리한 다음 리프의 기본 키 값에 따라 클러스터링합니다. node 인덱스에 대한 쿼리에 필요한 모든 필드 값입니다. 아래 그림의 왼쪽과 같이 인덱스 노드를 100005번 쿼리하고, 클러스터링된 인덱스 데이터를 100005번 쿼리하고, 마지막으로 처음 100000개 항목 중 결과를 필터링하고 마지막 5개 항목을 제거해야 합니다. MySQL은 클러스터형 인덱스의 데이터를 쿼리하는 데 많은 랜덤 I/O를 소비하며, 100,000번의 랜덤 I/O로 쿼리된 데이터는 결과 집합에 나타나지 않습니다.

처음에는 인덱스를 사용하므로 먼저 인덱스 리프 노드를 따라 필요한 마지막 5개 노드까지 쿼리한 다음 클러스터형 인덱스의 실제 데이터를 쿼리하는 것이 좋습니다. 위 그림의 오른쪽 프로세스와 유사하게 5개의 임의 I/O만 필요합니다. 이 방법은 먼저 오프셋 위치에서 ID를 찾은 다음 나중에 쿼리하는 방법입니다. 이 방법은 ID가 증가하는 상황에 적합합니다. 아래와 같이

mysql> select *  from sbtest1 where k=5020952 limit 50,1;
mysql> select id  from sbtest1 where k=5020952 limit 50,1;
mysql> select * from sbtest1 where k=5020952 and id>=( select id  from sbtest1 where k=5020952 limit 50,1) limit 10;
mysql> select * from sbtest1 where k=5020952 limit 50,10;

서브쿼리 최적화에서는 술어에 있는 k의 인덱스 유무가 쿼리 효율에 큰 영향을 미칩니다. 위 문장은 인덱스를 사용하지 않고 전체 테이블 스캔에 24.2초가 걸리지만 인덱스를 사용한 후에는 0.67초 밖에 걸리지 않습니다.

mysql> explain  select * from sbtest1 where k=5020952 and id>=( select id  from sbtest1 where k=5020952 limit 50,1) limit 10;
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
|  1 | PRIMARY     | sbtest1 | NULL       | index_merge | PRIMARY,c1    | c1,PRIMARY | 8,4     | NULL  |   19 |   100.00 | Using intersect(c1,PRIMARY); Using where |
|  2 | SUBQUERY    | sbtest1 | NULL       | ref         | c1            | c1         | 4       | const |   88 |   100.00 | Using index                              |
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
2 rows in set, 1 warning (0.11 sec)

그러나 이 최적화 방법에는 제한 사항도 있습니다.

• 이 방법을 사용하려면 기본 키 ID가 연속적이어야 합니다.

• Where 절에서는 다른 조건을 추가할 수 없습니다

2. Quality Optimization

이 방법은 데이터 테이블의 ID가 지속적으로 증가한다고 가정하고 쿼리된 페이지 수와 쿼리된 레코드 수를 기반으로 쿼리된 ID의 범위를 계산할 수 있습니다. 질문.

데이터베이스의 테이블 ID가 지속적으로 증가한다고 가정하면, 쿼리된 페이지 수와 쿼리된 레코드 수를 기준으로 쿼리된 ID의 범위를 계산한 다음, 문과 문 사이의 ID를 기준으로 쿼리할 수 있습니다. id의 범위는 페이징 공식을 통해 계산할 수 있습니다. 예를 들어 현재 페이지 크기가 m이고 현재 페이지 번호가 no1이면 페이지의 최대 값은 max=(no1+1)m-1입니다. 최소값은 min=no1m입니다. SQL 문은 min과 max 사이의 id로 표현될 수 있습니다.

select * from sbtest1 where id between 1000000 and 1000100 limit 100;

이 쿼리 방법은 쿼리 속도를 크게 최적화할 수 있으며 기본적으로 수십 밀리초 이내에 완료될 수 있습니다. id를 명확하게 알아야 한다는 한계가 있지만 일반적으로 페이징 쿼리의 비즈니스 테이블에는 기본 id 필드가 추가되어 페이징 쿼리에 많은 편의성을 제공합니다. 위의 SQL을 작성하는 또 다른 방법이 있습니다.

select * from sbtest1 where id >= 1000001 limit 100;

실행 시간의 차이를 확인할 수 있습니다.

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        6 | 0.00085500 | select * from sbtest1 where id between 1000000 and 1000100 limit 100                                         |
|        7 | 0.12927975 | select * from sbtest1 where id >= 1000001 limit 100                                                          |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

또한 in 메서드를 사용하여 쿼리할 수도 있습니다. 이 메서드는 여러 테이블이 연결되어 있는 경우 쿼리하는 데 자주 사용됩니다. 쿼리에 설정된 테이블 쿼리 ID:

select * from sbtest1 where id in (select id from sbtest2 where k=504878) limit 100;

쿼리에서 사용할 때 일부 mysql 버전에서는 in 절에서 제한 사용을 지원하지 않는다는 점에 유의하세요.

3. 인덱스 재정렬 기반 최적화

인덱스 기반 재정렬은 인덱스 쿼리의 최적화 알고리즘을 사용하여 인덱스를 통해 관련 데이터 주소를 찾아 전체 테이블 스캔을 방지하므로 많은 시간이 절약됩니다. 또한, Mysql에도 관련 인덱스 캐시가 있는데, 동시성이 높을 때 캐시를 사용하는 것이 더 좋을 것 같습니다. MySQL에서는 다음 문을 사용할 수 있습니다.

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

이 방법은 데이터 양이 많은 상황(수만 튜플)에 적합합니다. ORDER BY 뒤의 열 개체가 기본 키 또는 고유 인덱스인 것이 가장 좋습니다. , ORDER BY 작업에서 인덱스를 사용할 수 있지만 결과 집합은 안정적입니다. 예를 들어, 다음 두 문:

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        8 | 3.30585150 | select * from sbtest1 limit 1000000,10                                                                       |
|        9 | 1.03224725 | select * from sbtest1 order by id limit 1000000,10                                                           |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

인덱스 필드 ID에 대해 order by 문을 사용한 후 성능이 크게 향상되었습니다.

4. 지연 연관을 사용하여 최적화

위의 하위 쿼리 방법과 유사하게 JOIN을 사용하여 먼저 인덱스 열에 대한 페이징 작업을 완료한 다음 테이블로 돌아와 필요한 열을 얻을 수 있습니다.

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

从实验中可以得出，在采用JOIN改写后，上面的两个局限性都已经解除了，而且SQL的执行效率也没有损失。

5、记录上次查询结束的位置

和上面使用的方法都不同，记录上次结束位置优化思路是使用某种变量记录上一次数据的位置，下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。

select * from t5 where id>=1000000 limit 10;

6、使用临时表优化

使用临时存储的表来记录分页的id然后进行in查询

这种方式已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

【相关推荐：mysql视频教程】

위 내용은 MySQL 페이징 쿼리를 최적화하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!