MySQL8.0 优化器介绍（三）
- GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
- GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
- 作者： 奥特曼爱小怪兽
- 文章来源：GreatSQL社区原创
往期回顾
MySQL8.0 优化器介绍（一）
MySQL8.0 优化器介绍（二）
本篇将进一步深入介绍优化器相关的join优化
为更好的理解本篇内容需要提前看一下以下内容：
- 单表访问的方法，参考《MySQL 是怎样运行的：从根儿上理解 MySQL》第10章"单表访问方法"
- 更多select语句级别的优化细节 见（https://dev.mysql.com/doc/refman/8.0/en/select-optimization.html）
为了让读者对join优化 有更深的了解，章节里的sql例子，留了一些思考和动手的问题。可能大家得到的答案会不同，但探索未知的过程，方式应该是一样的。
join优化（Join Optimizations）
MySQL可以使用Join Optimizations来改进上次分享过的join algorithms，或者决定如何执行部分查询。本次主要介绍三种经常用到的join Optimizations，更多的 join type 见下面的链接：（https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types）
index merge
通常MySQL只会对每个表使用一个索引。但是，如果对同一表中的多个列在where后有条件限制，并且没有覆盖所有列的单个索引，无论选哪个索引都不是最佳的。对于这些情况，MySQL支持索引合并 (index merge)。select a,b,c from t where a=x1 and b=x2 and c=x3,这种情况下，建立一个多列的复合索引 index_abc 比使用 index merge +index_a+index_b+index_c 性能更好。
Index merge 支持三种算法 见下表
查询计划使用index merge 时，会在explain sql 的 access type 列 有"index_merge"，key 列会 包含所有参与merge的列， key_length 包含一个所用索引的最长关键部分的列表。举个Intersection例子：
Intersection
以下代码块注释中提到的知识点略多
##无论optimizer 是否选择 index merge 取决于index statistics.
## index statistics 是从哪个试图获得呢？mysql.innodb_index_stats 还是 information_schema.statistics
## 还是 information_schema.INNODB_SYS_TABLESTATS？
## 可以参考 https://www.cnblogs.com/ClassicMan/articles/15871403.html
## index_dive eq_range_index_dive_limit 这两个参数有什么作用？
##意味着即使返回相同STATEMENT_DIGEST_TEXT的sql查询语句, WHERE语句后面跟不同的值，得到的查询计划可能是不一样的 ##比如select * from people where name='唯一值'；
##select * from people where name='超级多的重复值'
## 同理index statistics 的改变会让同一个查询走不同的执行计划，
## 体现在 select a,b from t where a=1 and b=1 有时走了 index merges，有时没走。
CREATE TABLE `payment` (
`payment_id` smallint unsigned NOT NULL,
`customer_id` smallint unsigned NOT NULL,
`staff_id` tinyint unsigned NOT NULL,
`rental_id` int(DEFAULT NULL,
`amount` decimal(5,2) NOT NULL,
`payment_date` datetime NOT NULL,
`last_update` timestamp NULL,
PRIMARY KEY (`payment_id`),
KEY `idx_fk_staff_id` (`staff_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `fk_payment_rental` (`rental_id`)
) ENGINE=InnoDB;
## case1 等值查询
SELECT *
FROM sakila.payment
WHERE staff_id = 1
AND customer_id = 75;
mysql> EXPLAIN SELECT *
FROM sakila.payment
WHERE staff_id = 1
AND customer_id = 75\G
**************************** 1. row *****************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: index_merge
possible_keys: idx_fk_staff_id,idx_fk_customer_id
key: idx_fk_customer_id,idx_fk_staff_id
key_len: 2,1
ref: NULL
rows: 20
filtered: 100
Extra: Using intersect(idx_fk_customer_id,idx_fk_staff_id); Using
where 1 row in set, 1 warning (0.0007 sec)
mysql> EXPLAIN FORMAT=TREE
SELECT *
FROM sakila.payment
WHERE staff_id = 1
AND customer_id = 75\G
**************************** 1. row ****************************
EXPLAIN: -> Filter: ((sakila.payment.customer_id = 75) and (sakila.payment.staff_id = 1)) (cost=14.48 rows=20)
-> Index range scan on payment using intersect(idx_fk_customer_id,idx_fk_staff_id) (cost=14.48 rows=20)
1 row in set (0.0004 sec)
##注意"Index range scan on payment",两个等值查询条件，为啥触发了rang scan？
## case2 下面的sql范围查询也能用到index merge 吗？执行计划 自己下去测试验证
SELECT *
FROM sakila.payment
WHERE payment_id > 10
AND customer_id = 318;
