这是我的表定义,目前没有任何特殊索引:
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_name
and 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'))"
}
}
}
}
那么哪个解释更好呢?我会说第一个是因为使用索引条件(没有“where”部分)。有什么办法可以避免文件排序吗?
position
给定多列 B 树索引的结构,在使用 IN on 时进行排序是不可行的category_id
。但正如测试数据所表明的那样,position
它不是“全局的”,但似乎只对给定的有意义category_id
。因此,正如我在评论中建议的那样,可以改为ORDER BY (category_id, position)
- 可以在这些列上使用双列索引来获取它而无需文件排序。http://sqlfiddle.com/#!9/aabaa/7由于 longtext 列,不可能将其转换为仅索引扫描(文本列只能在前缀上建立索引,而不是在整个值上建立索引,因为索引键长度是有限的)。
使用 varchar 字段作为主键有一些缺点,但它本身并不是“错误的”,所以只是一个小建议 - 在您打算使用一些整数 ID 时是否有意义?