MySQL 執(zhí)行連表時使用了嵌套循環(huán)連接算法。它不支持像哈希恼琼、歸并排序連接妨蛹，這些在競品數(shù)據(jù)庫中可能有的特性，這讓 MySQL 沒那么適合做分析統(tǒng)計和數(shù)據(jù)倉庫風(fēng)格的查詢晴竞。然而滑燃，MySQL 優(yōu)化器的緩存策略可以減輕嵌套循環(huán)連接中的最壞情況。
嵌套循環(huán)連接
例子23 展示了 “國家颓鲜、城市、語言” 3 個表的連表典予。執(zhí)行這個查詢的完整過程如下：
- 優(yōu)化器必須先確定一個主表
國家甜滨，以及用于連接剩下的表“城市、語言”的索引瘤袖。 - 執(zhí)行開始于對第一個表一行行遍歷衣摩。對于滿足條件 “Country.Continent='Asia'” 的每一行，將查詢下一個表
城市。 - 對于城市表中滿足條件的每一行艾扮，將查詢最后一個表
語言既琴，應(yīng)用過濾條件 “IsOfficial = T”。
例子23：嵌套循環(huán)連接 3 個表
EXPLAIN FORMAT=JSON
SELECT
Country.Name as Country, City.Name as Capital, Language
FROM
City
INNER JOIN Country ON Country.Capital=City.id
INNER JOIN CountryLanguage ON CountryLanguage.CountryCode=Country.code
WHERE
Country.Continent='Asia' and CountryLanguage.IsOfficial='T';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.42"
},
"nested_loop": [
{
"table": {
"table_name": "Country", # 表 1
...
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.20",
"prefix_cost": "0.00",
"data_read_per_join": "264"
}
}
},
{
"table": {
"table_name": "City", # 表 2
...
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.20",
"prefix_cost": "0.00",
"data_read_per_join": "72"
}
}
},
{
"table": {
"table_name": "CountryLanguage", # 表 3
...
"rows_examined_per_scan": 12,
"rows_produced_per_join": 6,
"filtered": "50.00",
"cost_info": {
"read_cost": "1.02",
"eval_cost": "1.20",
"prefix_cost": "3.42",
"data_read_per_join": "240"
}
"attached_condition": "(`world`.`CountryLanguage`.`IsOfficial` = 'T')"
}
}
]
}
}
當(dāng)工作量可以在連表前就得以減少時泡嘴，嵌套循環(huán)連接算法的效果最好甫恩。這意味著 “最好的情形” 是主表有許多高選擇性的查詢條件。
最壞的情形可以是查詢條件散布在各個表里酌予，而且索引在全部表被連起來之前沒能減少足夠多的工作量磺箕。應(yīng)對這種情況，通常的做法是反范式設(shè)計抛虫。
通過冗余一列數(shù)據(jù)到主表中松靡，就可以添加和使用復(fù)合索引，在訪問和連接其他表之前就能過濾建椰。
內(nèi)連接
INNER JOIN在語義上要求一行數(shù)據(jù)存在于 JOIN 的左邊和右邊雕欺。帶著這個語義，去想 MySQL 能夠以任意順序連接這兩個表棉姐，優(yōu)化器的職責(zé)就是采取代價最低的順序屠列。
左連接 和 右連接
LEFT JOIN語義上意味著一行數(shù)據(jù)是否存在于右邊是可有可無的，而RIGHT JOIN就意味著左邊是可有可無的谅海。由于其中一邊是可有可無的脸哀，執(zhí)行計劃會先從必須存在的那一邊開始。因為順序是既定的扭吁，優(yōu)化器不能像內(nèi)連接那樣撞蜂，考慮所有可能的連表順序。因此侥袜，半連接是相對慢一些的蝌诡。
條件扇出過濾器
從 MySQL 5.7 開始，優(yōu)化器開始考慮主表之外的過濾效果枫吧。這種條件過濾condition_filtering用于改善從表的連接順序浦旱。
條件過濾非常適于直方統(tǒng)計圖，如果沒有使用條件過濾九杂，就會采用更簡單的探索式方法颁湖，在不平衡或偏斜的數(shù)據(jù)中會不準(zhǔn)確。條件過濾也能更好地利用索引中的統(tǒng)計信息例隆。
