범위를 포함할 때 인덱스에서 먼저 높은 카디널리티 열을 선택하십시오.

범위를 포함할 때 인덱스에서 먼저 높은 카디널리티 열을 선택하십시오.

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

테이블에는 100만 개의 행이 있다.파일 시간은 대부분 다릅니다.의 수는 한정되어 있다.ext가치.그렇게,filetime카디널리티가 높고ext카디널리티가 훨씬 낮습니다.

쿼리에는 다음 두 가지가 모두 포함됩니다.ext그리고.filetime:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

그 두 가지 지수 중 어느 것이 더 좋습니까?그리고 왜?

일단 해보자FORCE INDEX둘 중 하나를 고르다ef또는fe타이밍이 너무 짧아서 어느 쪽이 빠른지 명확하게 알 수 없지만, 「DESPLINE은 다음과 같은 차이를 나타내고 있습니다.

범위 강제 설정filetime(주:의 순서WHERE영향이 없습니다.)

mysql> EXPLAIN SELECT COUNT(*), AVG(fsize)
    FROM files FORCE INDEX(fe)
    WHERE ext = 'gif' AND filetime >= '2015-01-01'
                      AND filetime <  '2015-01-01' + INTERVAL 1 MONTH;
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | files | range | fe            | fe   | 14      | NULL | 16684 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+

낮은 카디널리티 강제 적용ext첫 번째:

mysql> EXPLAIN SELECT COUNT(*), AVG(fsize)
    FROM files FORCE INDEX(ef)
    WHERE ext = 'gif' AND filetime >= '2015-01-01'
                      AND filetime <  '2015-01-01' + INTERVAL 1 MONTH;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | files | range | ef            | ef   | 14      | NULL |  538 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

분명히,rows말한다ef더 좋아요.Optimizer 트레이스를 확인해 보겠습니다.출력물이 꽤 부피가 크네요. 재미있는 부분만 보여드릴게요.아니요.FORCE이 필요합니다.트레이스에는 양쪽 옵션이 표시되어 있습니다.더 좋은 옵션을 선택합니다.

             ...
             "potential_range_indices": [
                ...
                {
                  "index": "fe",
                  "usable": true,
                  "key_parts": [
                    "filetime",
                    "ext",
                    "did",
                    "filename"
                  ]
                },
                {
                  "index": "ef",
                  "usable": true,
                  "key_parts": [
                    "ext",
                    "filetime",
                    "did",
                    "filename"
                  ]
                }
              ],

...

              "analyzing_range_alternatives": {
                "range_scan_alternatives": [
                  {
                    "index": "fe",
                    "ranges": [
                      "2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
                    ],
                    "index_dives_for_eq_ranges": true,
                    "rowid_ordered": false,
                    "using_mrr": false,
                    "index_only": false,
                    "rows": 16684,
                    "cost": 20022,               <-- Here's the critical number
                    "chosen": true
                  },
                  {
                    "index": "ef",
                    "ranges": [
                      "gif <= ext <= gif AND 2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
                    ],
                    "index_dives_for_eq_ranges": true,
                    "rowid_ordered": false,
                    "using_mrr": false,
                    "index_only": false,
                    "rows": 538,
                    "cost": 646.61,               <-- Here's the critical number
                    "chosen": true
                  }
                ],

...

          "attached_conditions_computation": [
            {
              "access_type_changed": {
                "table": "`files`",
                "index": "ef",
                "old_type": "ref",
                "new_type": "range",
                "cause": "uses_more_keyparts"   <-- Also interesting
              }
            }

와 함께fe(범위 열 먼저), 범위를 사용할 수 있지만, 16684 행의 피싱에 의한 스캔을 추정했습니다.ext='gif'.

와 함께ef(낮은 카디널리티ext먼저) 지수의 두 열을 모두 사용하고 BTree에서 더 효율적으로 드릴다운할 수 있다.그런 다음 추정된 538개의 행을 찾았습니다. 이 행들은 모두 쿼리에 유용하며 추가 필터링은 필요하지 않습니다.

결론:

• INDEX(filetime, ext)첫 번째 열만 사용했습니다.
• INDEX(ext, filetime)두 열을 모두 사용했습니다.
• 관련 컬럼을 삽입하다=는 카디널리티에 관계없이 인덱스에서 먼저 테스트합니다.
• 쿼리 계획이 첫 번째 '범위' 열을 초과하지 않습니다.
• "가진성"은 복합 색인 및 이런 유형의 쿼리와 무관합니다.

("인덱스 조건 사용"은 Storage Engine(InnoDB)이 필터링에 사용된 열 이외의 열을 사용하는 것을 의미합니다.)

언급URL : https://stackoverflow.com/questions/50239658/higher-cardinality-column-first-in-an-index-when-involving-a-range