GROUP BY
GROUP BY操作要求讀取的行有序，或通過(guò)臨時(shí)表來(lái)緩存聚合過(guò)程的中間產(chǎn)物逃片。這意味著 MySQL 可以用索引來(lái)執(zhí)行 GROUP BY：
- 松散索引掃描屡拨。如果 GROUP BY 的列有索引， MySQL 可以從頭到尾掃描索引褥实，避免了產(chǎn)生中間產(chǎn)物呀狼。這是推薦的方式，因?yàn)槿绻麤](méi)有高選擇性的條件损离，創(chuàng)建的臨時(shí)表可能會(huì)很大哥艇。
- 過(guò)濾行。索引可以用于確定將被存入臨時(shí)表的行草冈，之后就會(huì)在臨時(shí)表中被聚合她奥。
- 過(guò)濾和排序的組合。當(dāng)用于過(guò)濾行的索引已經(jīng)給數(shù)據(jù)排好序時(shí)就會(huì)應(yīng)用這項(xiàng)優(yōu)化怎棱。
例子24：使用松散索引掃描的 GROUP BY
EXPLAIN FORMAT=JSON
SELECT count(*) as c, continent FROM Country GROUP BY continent;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "56.80"
},
"grouping_operation": {
"using_filesort": false, # 執(zhí)行了排序操作
"table": {
"table_name": "Country",
"access_type": "index", # 使用索引
"possible_keys": [
"PRIMARY",
"p",
"c",
"p_c",
"c_p",
"Name"
],
"key": "c",
"used_key_parts": [
"Continent"
],
"key_length": "1",
...
}
}
}
}
例子25：使用索引哩俭，然后排序的 GROUP BY
EXPLAIN FORMAT=JSON
SELECT count(*) as c, continent FROM Country WHERE population > 500000000 GROUP BY continent;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.81"
},
"grouping_operation": {
"using_temporary_table": true, # 使用了臨時(shí)表
"using_filesort": true, # 執(zhí)行了排序
"cost_info": {
"sort_cost": "2.00"
},
"table": {
"table_name": "Country",
"access_type": "range", # 范圍訪問(wèn)方式
"possible_keys": [
"PRIMARY",
"p",
"c",
"p_c",
"c_p",
"Name"
],
"key": "p",
"used_key_parts": [
"Population"
],
"key_length": "4",
...
"attached_condition": "(`world`.`Country`.`Population` > 500000000)"
}
}
}
}
例子26：使用索引，然后過(guò)濾和排序的 GROUP BY
EXPLAIN FORMAT=JSON
SELECT count(*) as c, continent FROM Country WHERE continent='Asia' GROUP BY continent;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "11.23"
},
"grouping_operation": {
"using_filesort": false, # 執(zhí)行了排序
"table": {
"table_name": "Country",
"access_type": "ref", # 索引 ref 訪問(wèn)方式
"possible_keys": [
"PRIMARY",
"p",
"c",
"p_c",
"c_p",
"Name"
],
"key": "c",
"used_key_parts": [
"Continent"
],
"key_length": "1",
"ref": [
"const"
],
...
}
}
}
}
UNION
MySQL 并不對(duì)UNION應(yīng)用特殊的優(yōu)化拳恋，而是像語(yǔ)意一樣把兩個(gè)查詢結(jié)果合并起來(lái)并去重凡资。在例子27中可以看到，去重是在一個(gè)中間臨時(shí)表中進(jìn)行的。使用了 UNION 的查詢隙赁，其所有執(zhí)行計(jì)劃都會(huì)用到臨時(shí)表垦藏，因此沒(méi)有關(guān)于它的查詢代價(jià)優(yōu)化。
UNION 的簡(jiǎn)單例子：
SELECT * FROM City WHERE CountryCode = 'CAN'
UNION
SELECT * FROM City WHERE CountryCode = 'USA'
假想的優(yōu)化：
SELECT * FROM City WHERE CountryCode IN ('CAN', 'USA')
子查詢和視圖對(duì)同一個(gè)表的多次訪問(wèn)可以被合并為一次訪問(wèn)伞访，而 UNION 不同掂骏，MySQL 不會(huì)對(duì)它做類似的優(yōu)化，也不會(huì)判斷是否已無(wú)重復(fù)而把UNION重寫成UNION ALL厚掷。許多情形被留給了熟練的優(yōu)化者弟灼，通過(guò)應(yīng)用或修改語(yǔ)句，去手動(dòng)優(yōu)化查詢和提升性能冒黑。
例子27：使用 UNION 查詢田绑，需要用到臨時(shí)表
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode = 'CAN'
UNION
SELECT * FROM City WHERE CountryCode = 'USA'
{
"query_block": {
"union_result": {
"using_temporary_table": true, # 需要用到臨時(shí)表
"table_name": "<union1,2>", # 合并兩個(gè)查詢的結(jié)果
"access_type": "ALL",
"query_specifications": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "58.80"
},
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode"
],
"key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 49,
"rows_produced_per_join": 49,
"filtered": "100.00",
"cost_info": {
"read_cost": "49.00",
"eval_cost": "9.80",
"prefix_cost": "58.80",
"data_read_per_join": "3K"
},
...
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "129.80"
},
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode"
],
"key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 274,
"rows_produced_per_join": 274,
"filtered": "100.00",
"cost_info": {
"read_cost": "75.00",
"eval_cost": "54.80",
"prefix_cost": "129.80",
"data_read_per_join": "19K"
},
...
}
}
}
]
}
}
}
UNION ALL
UNION ALL相對(duì)UNION只有一個(gè)不同：不會(huì)執(zhí)行去重。這意味著某些情況下 MySQL 能夠不使用臨時(shí)表抡爹，將查詢結(jié)果一并返回掩驱。
UNION ALL 查詢中臨時(shí)表總是會(huì)創(chuàng)建，是否需要使用可以在 EXPLAIN 中看到冬竟。例子28展示了添加 ORDER BY 后欧穴，UNION ALL 變得需要使用臨時(shí)表。
例子28：不使用臨時(shí)表的 UNION ALL
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode = 'CAN'
UNION ALL
SELECT * FROM City WHERE CountryCode = 'USA';
{
"query_block": {
"union_result": {
"using_temporary_table": false, # 不需要使用臨時(shí)表
"query_specifications": [
...
}
例子29：使用臨時(shí)表的 UNION ALL泵殴，原因是有 ORDER BY
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode = 'CAN'
UNION ALL
SELECT * FROM City WHERE CountryCode = 'USA' ORDER BY Name;
{
"query_block": {
"union_result": {
"using_temporary_table": true, # 使用臨時(shí)表
"table_name": "<union1,2>",
"access_type": "ALL",
"query_specifications": [
...
}
