选择驱动表
默认选择方式（非驱动表）：
按照on的条件列，是否有索引，索引的类型选择。
1. 在on条件中，优化器优先选择有索引的列为非驱动表。
2. 如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。
for each row in course matching range { block for each row in teacher { course.tno = tracher.tno ，send to client } }
关于驱动表选择的优化思路：
理论支撑： mysql> desc select * from city left join country on city.countrycode=country.code ; +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL | | 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) 查询语句执行代价： mysql> desc format=json select * from city join country on city.countrycode=country.code ; ··· { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5231.03" }, "nested_loop": [ { "table": { "table_name": "country", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 239, "rows_produced_per_join": 239, "filtered": "100.00", "cost_info": { "read_cost": "6.00", "eval_cost": "47.80", "prefix_cost": "53.80", "data_read_per_join": "61K" }, "used_columns": [ "Code", "Name", "Continent", "Region", "SurfaceArea", "IndepYear", "Population", "LifeExpectancy", "GNP", "GNPOld", "LocalName", "GovernmentForm", "HeadOfState", "Capital", "Code2" ] } }, { "table": { "table_name": "city", "access_type": "ref", "possible_keys": [ "CountryCode" ], "key": "CountryCode", "used_key_parts": [ "CountryCode" ], "key_length": "3", "ref": [ "world.country.Code" ], "rows_examined_per_scan": 18, "rows_produced_per_join": 4314, "filtered": "100.00", "cost_info": { "read_cost": "4314.36", "eval_cost": "862.87", "prefix_cost": "5231.03", "data_read_per_join": "303K" }, "used_columns": [ "ID", "Name", "CountryCode", "District", "Population" ] } } ] } } | ···
实践检验：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
注：可以通过 left join 强制驱动表。
总结：多表连接优化
驱动表选择
A join B on A.x=b.y
让优化器自己决定
在on条件中，优化器优先选择有索引的列为非驱动表。 如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。
自主选择
left join强制驱动表
关于驱动表选择的优化思路
理论支撑： mysql> desc select * from city join country on city.countrycode=country.code ; mysql> desc select * from city left join country on city.countrycode=country.code ; 查询语句执行代价： mysql> desc format=json select * from city join country on city.countrycode=country.code ; mysql> desc format=json select * from city left join country on city.countrycode=country.code ;
实践检验：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
驱动情景
情景一：触发SNL的情况
非驱动表，关联条件 如果没有任何索引的话，只能默认使用SNL算法 代价较高，建议做合理优化。 例如：将非驱动表关联条件建立索引。 （1）主键或唯一键，会自动使用eq_ref算法进行执行查询。 （2）辅助索引，默认会采用BNL。如果开启BKA，会走BKA。
情景二：触发BNL的情况
非驱动表，连接条件如果有普通索引。会默认会用BNL
情景三：触发BKA的情况
非驱动表，连接条件如果有普通索引。 默认是关闭的，启动方式： mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off'; mysql> set global optimizer_switch='batched_key_access=on';
选择 BNL和BKA算法的判断思路
理论支撑： mysql> desc select * from city join country on city.countrycode=country.code ; mysql> desc select * from city left join country on city.countrycode=country.code ; 查询语句执行代价： mysql> desc format=json select * from city join country on city.countrycode=country.code ; mysql> desc format=json select * from city left join country on city.countrycode=country.code ;
实践检验：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
