我有一个上有唯一索引的表（col1，col2，col3）
我对这张table有个疑问

...
left join my_table t on t.col1=x.col1 and t.col2=x.col2 and t.col3 in (1,2,3)

当col3的列表只有一个项目时，explain显示 eq_ref join ```
+----+-------------+----------+------------+--------+---------------+-----+---------+---------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+--------+---------------+-----+---------+---------------------+------+----------+-------+
| 1 | PRIMARY | my_table | NULL | eq_ref | veh | veh | 8 | x.col1,x.col2,const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+--------+---------------+-----+---------+---------------------+------+----------+-------+

但是对于两个或更多的项目，索引不再使用

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+------------------------------------------------+
| 1 | PRIMARY | my_table | NULL | ALL | veh | NULL | NULL | NULL | 13705314 | 100.00 | Range checked for each record (index map: 0x3) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+------------------------------------------------+

col3上的一个额外的非唯一索引没有帮助
如何设置这样一个应用程序可以使用的索引 `join` 条件？
或者如何重写查询？
编辑：附加信息

explain format=json
select x.*,ifnull(group_concat(t.col4 separator ','),'') col4
from
(select col1,col2,total from tmp_data limit 10) x
left join my_table t on t.col1=x.col1 and t.col2=x.col2 and t.col3 in (38,85)
group by x.col1,x.col2
order by x.total desc

{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "411271.71"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"grouping_operation": {
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "x",
"access_type": "ALL",
"rows_examined_per_scan": 10,
"rows_produced_per_join": 10,
"filtered": "100.00",
"cost_info": {
"read_cost": "10.50",
"eval_cost": "2.00",
"prefix_cost": "12.50",
"data_read_per_join": "240"
},
"used_columns": [
"col1",
"col2",
"total"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "12.50"
},
"table": {
"table_name": "tmp_data",
"access_type": "ALL",
"rows_examined_per_scan": 10,
"rows_produced_per_join": 10,
"filtered": "100.00",
"cost_info": {
"read_cost": "10.50",
"eval_cost": "2.00",
"prefix_cost": "12.50",
"data_read_per_join": "5K"
},
"used_columns": [
"col1",
"col2",
"total"
]
}
}
}
}
},
{
"table": {
"table_name": "t",
"access_type": "ALL",
"possible_keys": [
"veh",
"col3"
],
"rows_examined_per_scan": 13705314,
"rows_produced_per_join": 320380,
"filtered": "100.00",
"range_checked_for_each_record": "index map: 0x3",
"cost_info": {
"read_cost": "347183.21",
"eval_cost": "64076.00",
"prefix_cost": "411271.71",
"data_read_per_join": "7M"
},
"used_columns": [
"col1",
"col2",
"col3",
"col4"
]
}
}
]
}
}
}
}

create table tmp_data
(col1 mediumint(1) unsigned not null default 0,
col2 mediumint(1) unsigned not null,
total decimal(11,0) not null default 0,
key veh (col1,col2) using hash
) engine=memory default charset=latin1 collate=latin1_general_ci

create table my_table
(col1 mediumint(1) unsigned not null,
col2 mediumint(1) unsigned not null,
col3 smallint(1) unsigned not null,
col4 text collate latin1_general_ci,
unique key veh (col1,col2,col3),
key col3 (col3)
) engine=myisam default charset=latin1 collate=latin1_general_ci