order 절 및 더 큰 제한 Maria가 있는 인덱스를 사용하지 않는 단순 SQL 쿼리DB

order 절 및 더 큰 제한 Maria가 있는 인덱스를 사용하지 않는 단순 SQL 쿼리DB

데이터베이스가 대량의 데이터를 선택할 때 인덱스를 고려하지 않는 문제를 발견했습니다.

주문과 함께 간단한 선택 조항입니다.

이 쿼리(또는 제한이 백만 미만인 다른 쿼리)

SELECT * 
FROM mon_stat_detail 
WHERE 1 
ORDER BY id DESC
LIMIT 500000

열 ID의 인덱스(btw. 기본 인덱스가 아닌 고유 인덱스)를 적절하게 사용합니다.

이 쿼리가 실행되는 동안

SELECT *
FROM mon_stat_detail 
WHERE 1 
ORDER BY id DESC
LIMIT 1000000

파일 정렬을 사용하고 있습니다.

테이블은 꽤 크고, 약 6천만 개가 있습니다.기록.

파일 정렬을 사용하면 15분이 소요되며 파일 정렬로 인해 디스크에 20GB 이상의 데이터가 생성됩니다.

그러나 동일한 쿼리에 인덱스를 강제 적용하는 경우

SELECT * 
FROM mon_stat_detail FORCE INDEX (id_2) 
WHERE 1 
ORDER BY id DESC
LIMIT 1000000

그것은 그것을 사용하고 있고 예상대로 몇 초밖에 걸리지 않습니다.

왜 이런 일이 일어나는지 아십니까?이 인덱스를 이렇게 간단한 쿼리에 적용해야 하는 이유는 무엇입니까?

(스키마를 줄였습니다.)

CREATE TABLE mon_stat_detail (
    id int(16) unsigned NOT NULL AUTO_INCREMENT, 
    sensor_id int(10) unsigned NOT NULL, 
    time datetime NOT NULL, 
    … other about 10 columns ... 
    PRIMARY KEY (sensor_id,time), 
    UNIQUE KEY id_2 (id), 
    … some more indexes and FK …
) ENGINE=InnoDB AUTO_INCREMENT=550579790 DEFAULT CHARSET=utf8

데이터베이스:

• 서버 버전: 10.1.48-MariaDB-0+deb9u2 - Debian 9.13
• 프로토콜 버전: 10

• 목표가 백만 행을 가져오는 것이라면 다음을 제외합니다.ORDER BY.

• 목표가 마지막 100만 행을 가져오는 것이고 다음과 같은 간격이 없는 경우id그 다음에 사용

    WHERE id > (SELECT MAX(id) FROM mon_stat_detail) - 1000000
    ORDER BY id  ASC    -- note; and no LIMIT is needed
  

• 공백이 있을 수 있는 경우 선택 전에 이 작업을 실행하면 다음과 같은 도움이 되는지 확인할 수 있습니다.

    ANALYZE TABLE mon_stat_detail;
  

InnoDB의 경우 쿼리 계획의 기반이 되는 "통계"를 빠르게 새로 고칩니다.

언급URL : https://stackoverflow.com/questions/67191340/simple-sql-query-not-using-index-with-order-clause-and-larger-limit-mariadb