為什麼不使用候選複合索引？

考慮下表：

CREATE TABLE `entityusage_trackbyyear` (
 `track_id` binary(16) NOT NULL,
 `album_id` binary(16) DEFAULT NULL,
 `library_id` binary(16) DEFAULT NULL,
 `firstdateofinterval` date NOT NULL,
 `territory_id` binary(16) NOT NULL,
 `playbacks` int(10) unsigned DEFAULT 0,
 `userplaybacks` int(10) unsigned DEFAULT 0,
 `userdownloads` int(10) unsigned DEFAULT 0,
 `usersingledownloads` int(10) unsigned DEFAULT 0,
 PRIMARY KEY (`firstdateofinterval`,`track_id`,`territory_id`),
 KEY `album_id` (`firstdateofinterval`,`album_id`,`territory_id`),
 KEY `library_id` (`firstdateofinterval`,`library_id`,`territory_id`),
 KEY `territory_id` (`firstdateofinterval`,`territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以及以下查詢：

SELECT 
   BIN_TO_UUID(album_id) AS e,
   firstdateofinterval AS p,
   BIN_TO_UUID(territory_id) AS t,
   SUM(playbacks) AS c1,
   SUM(userplaybacks) AS c2,
   SUM(userdownloads) AS c3,
   SUM(usersingledownloads) AS c4
FROM
   entityusage_trackbyyear
WHERE
   firstdateofinterval >= '2018-01-01'
       AND firstdateofinterval <= '2018-09-30'
       AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

為什麼 MariaDB 堅持使用PRIMARY KEY索引，而不是作為兩個+library_id的複合索引的索引- 這是查詢條件中給出的列？firstdateofinterval``library_id

當我明確設置USE INDEX (library_id)它完全跳過使用索引。

我一定做錯了什麼！

*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: entityusage_trackbyyear
        type: range
possible_keys: PRIMARY,album_id,library_id,territory_id
         key: PRIMARY
     key_len: 3
         ref: NULL
        rows: 1218204
      r_rows: 685956.00
    filtered: 100.00
  r_filtered: 0.13
       Extra: Using where; Using temporary; Using filesort

• UUID_TO_BIN和BIN_TO_UUID是用於將 UUID 轉換為/從“人類可讀”表示轉換為緊湊binary(16)數據類型的函式。

你必須強制索引，優化器才會使用你想要的索引，否則你只給它一個提示

SELECT 
   BIN_TO_UUID(album_id) AS e,
   firstdateofinterval AS p,
   BIN_TO_UUID(territory_id) AS t,
   SUM(playbacks) AS c1,
   SUM(userplaybacks) AS c2,
   SUM(userdownloads) AS c3,
   SUM(usersingledownloads) AS c4
FROM
   entityusage_trackbyyear
   FORCE INDEX (territory_id)
WHERE
   firstdateofinterval >= '2018-01-01'
       AND firstdateofinterval <= '2018-09-30'
       AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

引用自：https://dba.stackexchange.com/questions/269612