咨询关于 order_by 和 group_by 的一些索引问题

[SjwNo1]

表结构

表名 label_file_bridges
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | int(32) | NO | PRI | NULL | auto_increment |

| label_id | int(32) | NO | MUL | NULL | |

| file_id | int(32) | NO | | NULL | |

| is_active | tinyint(1) | NO | | NULL | |
+------------+------------+------+-----+---------+----------------+

索引

PRIMARY id
idx_label_file_bridge_label_id_file_id_is_active (label_id, file_id, is_active)

sql

select file_id, group_concat(label_id) from label_file_bridges where label_id in (1,2,3,4,5,6,7,8,9,10) and is_active=1 group by file_id;

explain

id: 1
select_type: SIMPLE
table: label_file_bridges
partitions: NULL
type: index
possible_keys: idx_label_file_bridge_label_id_file_id_is_active
key: idx_label_file_bridge_label_id_file_id_is_active
key_len: 9
ref: NULL
rows: 48182
filtered: 10.00
Extra: Using where; Using index; Using filesort

疑问

表数据量百万级，以上是测试数据， 使用此 sql 速度还行，但是存在外层排序 filesort, 调试发现用 in + order_by 或者 in + group_by 都会有这个情况 请教一下这个 sql 或者表结构 ( 索引 ) 有可优化的地方吗，有可能避免 filesort 吗

[lpts007]

加 order by null 试过没

[fhsan]

order by label_id, file_id, is_active
group by file_id

[Egfly]

索引位置换一下 file_id 放在 label_id 前面就行

[SjwNo1]

@lpts007 试了 没有改变哦

[SjwNo1]

@fhsan 不行哈哈

[SjwNo1]

@Egfly 可以啦哈哈，原因是什么呢

[Egfly]

@SjwNo1

主要是 in 导致的。经过 label_id 的 in 查询之后的结果集不是有序的（虽然你 in 的数据是有序的，但是优化器并不知道）。所以需要使用临时表完成 group_by 操作。

如果你把 in 换成 label_id=xxx,应该也是不用 file_sort,可以直接走原索引，你可以 explain 看一下。

[SjwNo1]

@Egfly 懂了 谢谢

[zlowly]

是否可以考虑用表分区？
is_active 字段从名字上猜测是可变的，不适合分区。但这个 label_id 不知道它的数据是否是可以考虑进行分区？

[SjwNo1]

@zlowly 有在考虑分区，不过这张表堆积的相关业务太多了