在某些场景中，需要对派生表生成临时结果集进行materialized，如果该临时结果集中包含索引键，那么查询有可能通过该索引键来进行优化。
如对下面查询：
SELECT
T2.purpose_code,
T1.instance_count
FROM `assets_cluster` AS T2
STRAIGHT_JOIN(
SELECT
cluster_id,
COUNT(1) AS instance_count
FROM `assets_instance`
GROUP BY `cluster_id`
HAVING COUNT(1)>1
) AS T1
ON T1.cluster_id=T2.cluster_id
对应查询在执行计划为：
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: T2
partitions: NULL
type: index
possible_keys: PRIMARY
key: idx_purpose_code
key_len: 387
ref: NULL
rows: 684
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 8
ref: exps_db.T2.cluster_id
rows: 10
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: assets_instance
partitions: NULL
type: index
possible_keys: ForeignKey_cluster_id
key: ForeignKey_cluster_id
key_len: 8
ref: NULL
rows: 1727
filtered: 100.00
Extra: Using index
对应的执行计划JOSN为：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "10397.97"
},
"nested_loop": [
{
"table": {
"table_name": "T2",
"access_type": "index",
"possible_keys": [
"PRIMARY"
],
"key": "idx_purpose_code",
"used_key_parts": [
"purpose_code"
],
"key_length": "387",
"rows_examined_per_scan": 851,
"rows_produced_per_join": 851,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "10.00",
"eval_cost": "170.20",
"prefix_cost": "180.20",
"data_read_per_join": "15M"
},
"used_columns": [
"cluster_id",
"purpose_code"
]
}
},
{
"table": {
"table_name": "T1",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"cluster_id"
],
"key_length": "8",
"ref": [
"exps_db.T2.cluster_id"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 8514,
"filtered": "100.00",
"cost_info": {
"read_cost": "8514.81",
"eval_cost": "1702.96",
"prefix_cost": "10397.97",
"data_read_per_join": "199K"
},
"used_columns": [
"cluster_id",
"instance_count"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "372.20"
},
"grouping_operation": {
"using_filesort": false,
"table": {
"table_name": "assets_instance",
"access_type": "index",
"possible_keys": [
"ForeignKey_cluster_id"
],
"key": "ForeignKey_cluster_id",
"used_key_parts": [
"cluster_id"
],
"key_length": "8",
"rows_examined_per_scan": 1771,
"rows_produced_per_join": 1771,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "18.00",
"eval_cost": "354.20",
"prefix_cost": "372.20",
"data_read_per_join": "5M"
},
"used_columns": [
"instance_id",
"cluster_id"
]
}
}
}
}
}
}
]
}
}由于查询中使用STRAIGHT_JOIN关键词，将assets_cluster作为外表，派生表结果集作为内表，由于内表上包含cluster_id的索引(auto_key0)，因此采用NEST LOOP方式循环外表的每行记录对内表做KEY LOOKUP查询。