MySQL 성능 최적화: datetime 필드별 순서

MySQL 성능 최적화: datetime 필드별 순서

블로그 게시물이 약 100,000개에 달하는 테이블이 있으며, 1:n 관계를 통해 50개의 피드가 있는 테이블에 연결되어 있습니다.게시물 테이블의 datetime 필드에 따라 정렬된 select 문을 사용하여 두 테이블을 모두 쿼리하면 MySQL은 항상 filesort를 사용하므로 쿼리 시간이 매우 느립니다(1초 이상).이것은 다음과 같은 스키마입니다.postings표(simpl):

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| feed_id             | int(11)      | NO   | MUL | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| is_active           | tinyint(1)   | NO   | MUL | 0       |                |
| link                | varchar(255) | NO   | MUL | NULL    |                |
| author              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| excerpt             | text         | NO   |     | NULL    |                |
| long_excerpt        | text         | NO   |     | NULL    |                |
| user_offtopic_count | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

그리고 여기 있습니다.feed표:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| type        | int(11)      | NO   | MUL | 0       |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| website     | varchar(255) | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

여기 실행에 1초 이상 걸리는 쿼리가 있습니다.참고하시기 바랍니다.post_date필드에 인덱스가 있지만 MySQL은 이를 사용하여 게시물 테이블을 정렬하지 않습니다.

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    (`postings`)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15  

의 입니다.explain extended이 쿼리의 명령어는 MySQL이 filsort:를 사용하고 있음을 보여줍니다.

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
| id | select_type | table    | type   | possible_keys                         | key       | key_len | ref                      | rows  | Extra                       |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
|  1 | SIMPLE      | postings | ref    | feed_id,is_active,user_offtopic_count | is_active | 1       | const                    | 30996 | Using where; Using filesort |
|  1 | SIMPLE      | feeds    | eq_ref | PRIMARY,type                          | PRIMARY   | 4       | feedian.postings.feed_id |     1 | Using where                 |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

를 할 때.order bypart, MySQL은 filesort합니다를 .MySQL이 인덱스를 이용하여 데이터를 정렬하고 선택할 수 있도록 이 쿼리를 최적화하는 방법에 대한 아이디어가 있으면 알려주세요.이미 몇 개의 블로그 게시글에서 제안한 대로 분야별로 모든 위치/주문에 대한 통합 인덱스를 만드는 등 몇 가지 시도를 해보았지만 이 또한 효과가 없었습니다.

합성 지수를 다음 중 하나postings (is_active, post_date)(순서대로)

필터링할 때 둘 다 사용됩니다.is_active주문은 다음과 같습니다.post_date.

MySQL보여야 할REF이 인덱스를 통한 액세스 방법EXPLAIN EXTENDED.

당신이 가지고 있는 것은RANGE필터링 조건오버user_offtopic_count, 필터링 및 다른 필드별 정렬에서 모두 이 필드에 인덱스를 사용할 수 없는 이유입니다.

선택의 폭에 따라user_offtopic_count(즉, 몇 개의 행이 다음을 만족합니까?)user_offtopic_count < 10), 에 대한 인덱스를 만드는 것이 더 유용할 수 있습니다.user_offtopic_count그리고 포스트_dates가 정렬되도록 합니다.

이 작업을 수행하려면 합성 지수를 생성합니다.postings (is_active, user_offtopic_count)그리고 확실하게 해주세요.RANGE이 인덱스를 통한 접근 방법이 사용됩니다.

어떤 인덱스가 더 빠를지는 데이터 분포에 따라 달라집니다.두 인덱스를 모두 만듭니다.FORCE그들과 어떤 것이 더 빠른지 알아보세요.

CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);
CREATE INDEX ix_active_date ON postings (is_active, post_date);

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_offtopic)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show RANGE access with few rows and keep the FILESORT */

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_date)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show REF access with lots of rows and no FILESORT */

MySQL에는 디스크의 레코드를 정렬하는 오래된 파일 정렬 알고리즘과 메모리에서 작동하는 새로운 버전의 두 가지 파일 정렬 알고리즘이 있습니다.

조인의 첫 번째 테이블에서 인덱스를 사용하여 쿼리를 정렬할 수 없는 경우 파일 정렬을 수행해야 합니다.고정 너비 형식으로 변환된 정렬 전에 설정된 결과가 정렬 버퍼 OR보다 크거나 텍스트 필드가 포함되어 있는 경우 디스크의 파일 속도가 느린 알고리즘(쿼리에 텍스트 필드가 있으므로 두 번째 조건이 충족됨)을 사용해야 합니다.

MySQL이 is_active 열을 사용하기로 결정한 이유는 표면적으로 열이 다른 조인 및 조건으로 계속 진행되기 전에 행을 제거하는 데 가장 선택적이라고 생각하기 때문입니다.post_date, feed_id 및 where 조건의 열(is_active, user_offtopic_count, post_date, feed_id)을 사용하여 복합 인덱스를 만드는 것을 먼저 제안합니다.

또한, MySQL에 인덱스 기능이 적용되어 있는 경우 MySQL은 인덱스를 사용하지 않는다는 점을 기억하는 것이 중요합니다.

당신은 또한 별칭 게시물을 시도해 보아야 합니다.다른 것으로 post_date.이렇게 하면 MySQL이 변경되지 않은 열로 순서를 정하도록 지시하고, 유닉스 타임스탬프를 선택할 수 있습니다.

[MySQL v8에서 사용] ORDER BY 절을 사용할 때 큰 차이를 만들기 위해 합성 인덱스에서 열의 순서를 찾았습니다.쿼리가 열에 복합 인덱스를 사용하고 있었습니다.SurveyId,IgnoreResponse,EndDate(순으로) 그리고 1.5초 이상의 쿼리 시간을 반환하고 있었습니다.

SELECT JsonResponseV3, SurveyId
FROM survey
WHERE (
    (IgnoreResponse=0 OR IgnoreResponse IS NULL)
    AND
    (SurveyId = 'SV_xy3')
)
ORDER BY EndDate ASC LIMIT 25 -- where EndDate is a date time field

(MySQL WorkBench 스크린샷 아래와 같이) 인덱스 변경 후 이동EndDate색인화된 첫 번째 열)EndDate,SurveyId,IgnoreResponse쿼리 시간이 0.016초로 떨어졌습니다.

(참고: 인덱스를 다음과 같이 되돌린 경우EndDate색인화된 마지막 열이며 쿼리 시간은 1.5초 이상으로 돌아갑니다.)

언급URL : https://stackoverflow.com/questions/714950/mysql-performance-optimization-order-by-datetime-field