内连接mysql优化_Join 多表连接 - 非官方 MySQL 8.0 优化指南 - 学习笔记

MySQL 执行连表时使用了嵌套循环连接算法。它不支持像哈希、归并排序连接，这些在竞品数据库中可能有的特性，这让 MySQL 没那么适合做分析统计和数据仓库风格的查询。然而，MySQL 优化器的缓存策略可以减轻嵌套循环连接中的最坏情况。

嵌套循环连接

例子23 展示了 “国家、城市、语言” 3 个表的连表。执行这个查询的完整过程如下：

优化器必须先确定一个主表国家，以及用于连接剩下的表“城市、语言”的索引。

执行开始于对第一个表一行行遍历。对于满足条件 “Country.Continent=’Asia’” 的每一行，将查询下一个表城市。

对于城市表中满足条件的每一行，将查询最后一个表语言，应用过滤条件 “IsOfficial = T”。

例子23：嵌套循环连接 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')"

}

}

]

}

}

当工作量可以在连表前就得以减少时，嵌套循环连接算法的效果最好。这意味着 “最好的情形” 是主表有许多高选择性的查询条件。

最坏的情形可以是查询条件散布在各个表里，而且索引在全部表被连起来之前没能减少足够多的工作量。应对这种情况，通常的做法是反范式设计。

通过冗余一列数据到主表中，就可以添加和使用复合索引，在访问和连接其他表之前就能过滤。

内连接

INNER JOIN在语义上要求一行数据存在于 JOIN 的左边和右边。带着这个语义，去想 MySQL 能够以任意顺序连接这两个表，优化器的职责就是采取代价最低的顺序。

左连接 和 右连接

LEFT JOIN语义上意味着一行数据是否存在于右边是可有可无的，而RIGHT JOIN就意味着左边是可有可无的。由于其中一边是可有可无的，执行计划会先从必须存在的那一边开始。因为顺序是既定的，优化器不能像内连接那样，考虑所有可能的连表顺序。因此，半连接是相对慢一些的。

条件扇出过滤器

从 MySQL 5.7 开始，优化器开始考虑主表之外的过滤效果。这种条件过滤condition_filtering用于改善从表的连接顺序。

条件过滤非常适于直方统计图，如果没有使用条件过滤，就会采用更简单的探索式方法，在不平衡或偏斜的数据中会不准确。条件过滤也能更好地利用索引中的统计信息。