MySQL LIMIT X, X를 늘리면 Y가 느려집니다.

MySQL LIMIT X, X를 늘리면 Y가 느려집니다.

나는 약 600,000개의 목록이 있는 DB를 가지고 있는데, 페이지가 있는 페이지에서 이것들을 검색하는 동안, 기록을 제한하기 위해 이 쿼리를 사용합니다.

SELECT file_id, file_category FROM files ORDER BY file_edit_date DESC LIMIT 290580, 30

에서 LIMIT 0, 30몇 ms 안에 로딩되며, 에 대해서도 마찬가지입니다.LIMIT 30,30,LIMIT 60,30,LIMIT 90,30하는 데 약 즉, 입니다. 하지만 페이지 끝으로 갈수록 쿼리를 실행하는 데 1초 정도 걸립니다.

인덱스는 관련이 없을 수 있으며, 이를 실행하면 다음과 같이 발생합니다.

SELECT * FROM `files` LIMIT 400000,30

왜 그런지 모르겠습니다.이것을 개선할 방법이 있습니까?

더 나은 해결책이 없는 한, 모든 레코드를 로드하고 PHP 페이지에서 루프를 하여 레코드가 페이지 범위 내에 있는지 확인하고 인쇄하는 것이 나쁜 관행일까요?

서버는 16GB 램을 갖춘 i7이며 MySQL Community Server 5.7.28, 파일 테이블은 약 200MB입니다.

중요하다면 여기 my.cnf가 있습니다.

query_cache_type = 1

query_cache_size = 1G

sort_buffer_size = 1G

thread_cache_size = 256

table_open_cache = 2500

query_cache_limit = 256M

innodb_buffer_pool_size = 2G

innodb_log_buffer_size = 8M

tmp_table_size=2G

max_heap_table_size=2G

다음 인덱스를 추가하면 성능이 향상됩니다.

CREATE INDEX idx ON files (file_edit_date DESC, file_id, file_category);

MySQL을 사용하는 경우, 일부 오프셋에서 레코드 수를 검색하기 위해 단일 인덱스 스캔만 필요합니다.인덱스가 전체 쿼리를 포함할 수 있도록 select 절에 열을 포함합니다.

LIMIT는 결과 집합의 크기를 줄이기 위해 개발되었으며, 인덱스를 사용하여 결과 집합을 주문하면 옵티마이저에서 사용할 수 있습니다.

.LIMIT x,n서버는 결과를 제공하기 위해 x+n개의 행을 처리해야 합니다.x 값이 높을수록 더 많은 행을 처리해야 합니다.

다음은 열 a에 고유한 인덱스를 갖는 간단한 표에서 나온 설명 출력입니다.

MariaDB [test]> explain select a,b from t1 order by a limit 0, 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 4       | NULL | 2    |       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

MariaDB [test]> explain select a,b from t1 order by a limit 400000, 2;
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 4       | NULL | 400002 |       |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
1 row in set (0.00 sec)

위 문을 실행할 때(설명 없음) LIMIT 0의 실행 시간은 0.01초이며, LIMIT 400000 0.6초입니다.

MariaDB는 하위 쿼리에서 LIMIT를 지원하지 않으므로 SQL 문을 두 개의 문으로 나눌 수 있습니다.

첫 번째 문은 ID를 검색하고(인덱스 파일만 읽어야 함), 두 번째 문은 첫 번째 문에서 검색한 ID를 사용합니다.

MariaDB [test]> select a  from t1 order by a limit 400000, 2;
+--------+
| a      |
+--------+
| 595312 |
| 595313 |
+--------+
2 rows in set (0.08 sec)

MariaDB [test]> select a,b from t1 where a in (595312,595313);
+--------+------+
| a      | b    |
+--------+------+
| 595312 | foo  |
| 595313 | foo  |
+--------+------+
2 rows in set (0.00 sec)

주의:저는 이제 막 강한 언어를 사용하려고 합니다.컴퓨터는 크고 빠르며 심지어 10년 전보다 더 큰 일을 처리할 수 있습니다.하지만, 여러분이 알고 있듯이, 한계가 있습니다.저는 여러분이 위협한 여러 가지 한계를 지적하고, 한계가 문제가 될 수 있는 이유를 설명하겠습니다.

설정

query_cache_size = 1G

끔찍합니다.표를 쓸 때마다 QC는 1GB를 스캔하여 해당 표에 대한 참조를 검색하여 QC의 항목을 삭제합니다.그것을 50M로 줄입니다.이것만으로도 전체 시스템의 속도를 높일 수 있습니다.

sort_buffer_size = 1G
tmp_table_size=2G
max_heap_table_size=2G

다른 이유로 나쁘다고 생각합니다복잡한 쿼리를 수행하는 여러 연결이 있는 경우, 각 연결에 많은 RAM을 할당할 수 있으므로 RAM을 씹어서 스왑할 수 있고 충돌할 수 있습니다.RAM의 약 1%보다 높게 설정하지 마십시오.

일반적으로 my.cnf에서 값을 맹목적으로 변경하지 마십시오.은입니다.innodb_buffer_pool_size, 데이터셋보다 커야 하지만 사용 가능한 RAM의 70%를 초과해서는 안됩니다.

모든 레코드를 로드합니다.

아야! MySQL에서 PHP로 모든 데이터를 이동하는 비용은 만만치 않습니다.PHP에 전달되면 엄청난 양의 데이터를 위해 설계되지 않은 구조로 저장됩니다. 400030(또는 600000) 행은 PHP 내부에 1GB가 들어갈 수 있습니다. 이 경우 "memory_limit"가 꺼지고 PHP가 충돌하게 될 수 있습니다. (좋아요, 오류 메시지와 함께 죽어갑니다.)이 제한을 높이는 것은 가능하지만 PHP는 MySQL을 메모리 밖으로 밀어내 스와핑하거나 스왑 공간이 부족할 수 있습니다.별꼴 다 보겠네!

오프셋

에 OFFSET 요? ?그는 거의 10,000페이지나 되는 겁니까?거미줄이 그를 덮고 있나요?

OFFSET예제에서 290580 행 이상을 읽고 밟아야 합니다.그것은 비용이 많이 듭니다.

해당 오버헤드 없이 페이지를 작성하는 방법은 http://mysql.rjweb.org/doc.php/pagination 을 참조하십시오.

한 번에 30개씩 600K개의 모든 행을 '크롤링'하는 프로그램이 있는 경우 해당 링크의 "종료 지점 기억"에 대한 팁이 이러한 용도에 매우 적합합니다."속도를 줄이지" 않습니다.

만약 당신이 뭔가 다른 것을 하고 있다면, 그것은 무엇입니까?

페이지 및 간격

문제 없어요.참고: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks 은 테이블 전체를 걷는 것을 더 목표로 합니다.앞으로 30번째 줄을 찾는 효율적인 방법에 초점을 맞추고 있습니다.(이것이 반드시 마지막을 기억하는 것보다 더 나은 것은 아닙니다.id.)

그 링크는 다음을 대상으로 합니다.DELETEing, but can easily be revised toSELECT'.

600K 행 테이블을 한 번에 30개 행씩 스캔하기 위한 몇 가지 수학:

내 링크: 600K 행이 터치되었습니다.앞을 내다보면 그 두 배가 됩니다.LIMIT 30,1두번째 링크에서 제시한 바와 같이

OFFSET ..., 30(600K/30)*600K/2 행 -- 약 60억 행을 터치해야 합니다.

(추론: 30을 100으로 변경하면 쿼리 속도가 빨라지지만 여전히 고통스러울 정도로 느려집니다.이는 제 접근 속도를 높이지는 못하겠지만, 이미 상당히 빠른 속도입니다.)

언급URL : https://stackoverflow.com/questions/60368516/mysql-limit-x-y-slows-down-as-i-increase-x