복합 인덱스 및 영구 열을 사용한 MySQL 쿼리 최적화

복합 인덱스 및 영구 열을 사용한 MySQL 쿼리 최적화

다음 쿼리는 MariaDB 10.0.28에서 실행되고 있으며, 약 17초 정도 소요되며, 상당한 속도를 낼 수 있을 것으로 예상됩니다.

select series_id,delivery_date,delivery_he,forecast_date,forecast_he,value 
from forecast where forecast_he=8 
AND series_id in (12142594,20735627,632287496,1146453088,1206342447,1154376340,2095084238,2445233529,2495523920,2541234725,2904312523,3564421486) 
AND delivery_date >= '2016-07-13' 
AND delivery_date < '2018-06-27' 
and DATEDIFF(delivery_date,forecast_date)=1

속도를 높이기 위한 첫 번째 시도는 영구 열을 (datediff(delivery_date, detail_date)로 만들고 영구 열을 사용하여 인덱스를 재구축한 다음 쿼리를 수정하여 날짜 계산을 forecast_delivery_date=1로 대체했습니다.

> describe forecast;
+-------------------------+------------------+------+-----+---------+------------+
| Field                   | Type             | Null | Key | Default | Extra      |
+-------------------------+------------------+------+-----+---------+------------+
| series_id               | int(10) unsigned | NO   | PRI | 0       |            |
| delivery_date           | date             | NO   | PRI | NULL    |            |
| delivery_he             | int(11)          | NO   | PRI | NULL    |            |
| forecast_date           | date             | NO   | PRI | NULL    |            |
| forecast_he             | int(11)          | NO   | PRI | NULL    |            |
| value                   | float            | NO   |     | NULL    |            |
| forecast_delivery_delta | tinyint(4)       | YES  |     | NULL    | PERSISTENT |
+-------------------------+------------------+------+-----+---------+------------+

> show index from forecast;
+----------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| forecast |          0 | PRIMARY              |            1 | series_id     | A         |       35081 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          0 | PRIMARY              |            2 | delivery_date | A         |      130472 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          0 | PRIMARY              |            3 | delivery_he   | A         |     1290223 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          0 | PRIMARY              |            4 | forecast_date | A         |     2322401 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          0 | PRIMARY              |            5 | forecast_he   | A         |    23224016 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          1 | he_series_delta_date |            1 | forecast_he   | A         |       29812 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          1 | he_series_delta_date |            2 | series_id     | A         |       74198 |     NULL | NULL   |      | BTREE      |         |               |
| forecast |          1 | he_series_delta_date |            3 | delivery_date | A         |      774133 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

이것은 실행 시간에서 2초 정도 떨어진 것 같습니다만, 이 속도를 크게 높일 수 있는 더 좋은 방법이 없을까요?버퍼 사이즈를 조정해 보았습니다만, 크게 잘못 설정되어 있는 것은 아닌 것 같습니다.

>show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+


Total table size:
+----------+------------+
| Table    | Size in MB |
+----------+------------+
| forecast |    1547.00 |
+----------+------------+

EXPLAIN:
+------+-------------+----------+-------+------------------------------+----------------------+---------+------+--------+-----------------------+
| id   | select_type | table    | type  | possible_keys                | key                  | key_len | ref  | rows   | Extra                 |
+------+-------------+----------+-------+------------------------------+----------------------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | forecast | range | PRIMARY,he_series_delta_date | he_series_delta_date | 11      | NULL | 832016 | Using index condition |
+------+-------------+----------+-------+------------------------------+----------------------+---------+------+--------+-----------------------+

만약 당신이 말할 것이라면

AND forecast_delivery_delta=1

그러면 최적 지수는 다음 두 개로 시작하는 지수입니다.=열:

(forecast_he, forecast_delivery_delta,    -- in either order
 series_id,           -- an IN might work ok next
 delivery_date)       -- finally a range

컬럼을 붙이는 것은 일반적으로 쓸모가 없다.delivery_date)는, 최종 이외의 범위에서 테스트되고 있습니다.

단, 이 인덱스는 predict_delivery_delivery <= 2>라고 하면 잘 작동하지 않습니다.이제 이 값은 "범위"가 되며 인덱스에서 그 다음으로는 필터링에 사용되지 않습니다.그래도 만약을 위해 적은 수의 다른 인덱스를 갖는 것이 가치가 있을 수 있습니다.=범위 내 또는 그 반대입니다.

그리고 증가innodb_buffer_pool_sizeRAM의 약 70%(4GB가 넘는 RAM을 탑재하고 있는 경우)

언급URL : https://stackoverflow.com/questions/51156814/mysql-query-optimization-with-compound-index-and-persistent-column