MySQL查詢WHERE IN + ORDER BY，哪個EXPLAIN更好以及如何避免文件排序？

這是我的表定義，暫時沒有任何特殊索引：

CREATE TABLE `filter` (
 `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `category_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `position` smallint(6) NOT NULL,
 `options` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:json_array)',
 PRIMARY KEY (`field_name`,`category_id`),
 KEY `IDX_702C956612469DE2` (`category_id`),
 CONSTRAINT `FK_702C956612469DE2` 
     FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

樣本數據：

+------------+-----------------------+-------------+----------+
| field_name |        options        | category_id | position |
+------------+-----------------------+-------------+----------+
| color      | {"label": "Color"}    |        1895 |        1 |
| material   | {"label": "Material"} |        1895 |        2 |
| color      | {"label": "Color"}    |        1896 |        1 |
| color      | {"label": "Color"}    |        1897 |        1 |
+------------+-----------------------+-------------+----------+

大多數時候，我使用 and 進行查詢，WHERE IN並且ORDER BY position只選擇field_nameand options：

EXPLAIN SELECT 
 field_name
 options
FROM 
 filter
WHERE 
 category_id IN ('1895', '1896', '1897') 
ORDER BY 
 position ASC

這給了我使用索引條件；使用 filesort，很糟糕，嗯？

{
  "query_block":{
     "select_id":1,
     "ordering_operation":{
        "using_filesort":true,
        "table":{
           "table_name":"filter",
           "access_type":"range",
           "possible_keys":[
              "IDX_702C956612469DE2"
           ],
           "key":"IDX_702C956612469DE2",
           "used_key_parts":[
              "category_id"
           ],
           "key_length":"98",
           "rows":5,
           "filtered":100,
           "index_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
        }
     }
  }
}

帶有關於category_id, position Using where 的索引；使用索引；使用文件排序：

{
  "query_block":{
     "select_id":1,
     "ordering_operation":{
        "using_filesort":true,
        "table":{
           "table_name":"filter",
           "access_type":"range",
           "possible_keys":[
              "IDX_702C956612469DE2",
              "IDX_CATEGORY_POSITION"
           ],
           "key":"IDX_CATEGORY_POSITION",
           "used_key_parts":[
              "category_id"
           ],
           "key_length":"98",
           "rows":5,
           "filtered":100,
           "using_index":true,
           "attached_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
        }
     }
  }
}

那麼哪個解釋更好呢？我會說第一個是因為使用了索引條件（沒有“位置”部分）。有什麼辦法可以避免文件排序？

position鑑於多列 B 樹索引的結構，在使用 IN on 時進行排序是不可行的category_id。但正如測試數據表明的那樣，position它不是“全球性的”，但似乎只對 given 有意義category_id。因此，正如我在評論中建議的那樣，可以ORDER BY (category_id, position)- 可以在這些列上使用兩列索引來獲取它而無需文件排序。http://sqlfiddle.com/#!9/aabaa/7

由於 longtext 列，不可能將其轉換為僅索引掃描（文本列只能在前綴上編制索引，而不是在整個值上編制索引，因為索引鍵長度是有限的）。

使用 varchar 欄位作為主鍵有一些缺點，但它本身並沒有“錯誤”，所以只是一個小建議 - 在您的設計中使用一些整數 id 是否有意義？

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