||
往期回顾
本篇将进一步深入介绍优化器相关的join优化
为更好的理解本篇内容需要提前看一下以下内容：
为了让读者对join优化 有更深的了解，章节里的sql例子，留了一些思考和动手的问题。可能大家得到的答案会不同，但探索未知的过程，方式应该是一样的。
MySQL可以使用Join Optimizations来改进上次分享过的join algorithms，或者决定如何执行部分查询。本次主要介绍三种经常用到的join Optimizations，更多的 join type 见下面的链接：（https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types）
通常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例子：
以下代码块注释中提到的知识点略多
##无论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;##case1 等值查询
SELECT *
FROM sakila.payment
WHERE staff_id = 1
OR customer_id = 318;
mysql> EXPLAIN
SELECT *
FROM sakila.payment
WHERE staff_id = 1
OR customer_id = 318\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_staff_id,idx_fk_customer_id
key_len: 1,2
ref: NULL
rows: 8069
filtered: 100
Extra: Using union(idx_fk_staff_id,idx_fk_customer_id); Using where
1 row in set, 1 warning (0.0008 sec)
mysql> EXPLAIN FORMAT=TREE
SELECT *
FROM sakila.payment
WHERE staff_id = 1
OR customer_id = 318\G
**************************** 1. row ****************************
EXPLAIN: -> Filter: ((sakila.payment.staff_id = 1) or (sakila.payment.customer_id = 318)) (cost=2236.18 rows=8069)
-> Index range scan on payment using union(idx_fk_staff_id,idx_fk_customer_id) (cost=2236.18 rows=8069)
1 row in set (0.0010 sec)
## case2 范围查询也能用到index merge 吗？执行计划 自己下去测试验证，
## 有主键参与后，和Intersection 章节的case2 执行计划中用到的索引个数有啥不同？
SELECT *
FROM sakila.payment
WHERE payment_id > 15000
OR customer_id = 318;SELECT *
FROM sakila.payment
WHERE customer_id < 30
OR rental_id < 10;
mysql> EXPLAIN
SELECT *
FROM sakila.payment
WHERE customer_id < 30
OR rental_id < 10\G
**************************** 1. row *****************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: index_merge
possible_keys: idx_fk_customer_id,fk_payment_rental
key: idx_fk_customer_id,fk_payment_rental
key_len: 2,5
ref: NULL
rows: 826
filtered: 100
Extra: Using sort_union(idx_fk_customer_id,fk_payment_rental);
Using where 1 row in set, 1 warning (0.0009 sec)
mysql> EXPLAIN FORMAT=TREE
SELECT *
FROM sakila.payment
WHERE customer_id < 30
OR rental_id < 10\G
**************************** 1. row *****************************
EXPLAIN: -> Filter: ((sakila.payment.customer_id < 30) or (sakila.payment.rental_id < 10)) (cost=1040.52 rows=826)
-> Index range scan on payment using sort_union(idx_fk_customer_id,fk_payment_rental) (cost=1040.52 rows=826)
1 row in set (0.0005 sec)多范围读取（MRR）优化旨在减少对辅助索引进行范围扫描所导致的随机I/O量。优化读取索引
首先，根据行id（InnoDB的聚集索引）对键进行排序，然后按行的存储顺序检索行。多量程读取优化
可以用于范围扫描和使用索引的等值连接。不支持虚拟生成列上的辅助索引。
使用InnoDB进行多范围读取优化的主要用例是用于没有覆盖索引的磁盘绑定查询（ disk-bound queries 另外一个层面对disk-bound 的优化，详细可见：https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html）。优化的效果取决于需要多少行以及存储器的查找时间。MySQL将会估算（estimate）是否有用。然而，成本估算在过于悲观而不是过于乐观的一面，因此可能有必要提供帮助优化器做出正确决策的信息。
有两个 optimizer switches 控制MRR优化
可以用MRR() 和NO_MRR() 两个optimizer switches 来控制表级别or 索引级别的 Multi-Range Read ，举个例子：
mysql> EXPLAIN
SELECT /*+ MRR(city) */
*
FROM world.city
WHERE CountryCode BETWEEN 'AUS' AND 'CHN'\G
**************************** 1. row *****************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: range
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: NULL
rows: 812
filtered: 100
Extra: Using index condition; Using MRR
1 row in set, 1 warning (0.0006 sec)
有必要使用MRR（）优化器提示或禁用基于MRR_cost_based的优化器开关。
示例中查询的估计行数太小，没有MRR的hint时，基于成本的优化无法使用MRR。
只能显示用hint来干预查询计划使用MRR。
当MRR的优化被使用时, MySQL需要用到random read buffer来存储indexes.
有一个参数可以影响MRR的性能 read_rnd_buffer_size.可以简单认为 BKA=BNL+MRR .这使得可以以与非索引连接类似的方式将连接缓冲区用于索引连接，并使用多范围读取优化来减少随机I/O的数量。BKA 用于大量 disk-bound 查询的场景。但是，没有明确的说明来确定优化何时有帮助，何时会导致性能下降。
可以借鉴一下国外知名dba在MySQL 优化方面的blog（http://oysteing.blogspot.com/2012/04/improved-dbt-3-results-with-mysql-565.html）
MRR 在最优时，查询耗时减少20%,最糟糕时查询耗时增加2/3。
BKA 主要的获益在一个相对较窄的查询范围，而其他查询的性能可能会降低，因此默认情况下禁用该优化。
（可以简单理解为 MySQL5.6时，bka优化带来的收益小于bka带来的成本开销）除非确定开启bka能来提升时，再用hint BKA() 来启用。session级别开启：
SET SESSION optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';一个使用BKA的例子
mysql> EXPLAIN
SELECT /*+ BKA(ci) */
co.Code, co.Name AS Country,
ci.Name AS City
FROM world.country co
INNER JOIN world.city ci
ON ci.CountryCode = co.Code\G
**************************** 1. row *****************************
id: 1
select_type: SIMPLE
table: co
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 239
filtered: 100
Extra: NULL
**************************** 2. row *****************************
id: 1
select_type: SIMPLE
table: ci
partitions: NULL
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: world.co.Code
rows: 18
filtered: 100
Extra: Using join buffer (Batched Key Access)
2 rows in set, 1 warning (0.0007 sec)注意看执行计划中Extra 的关键字 "Using join buffer",说明 join_buffer_size 会影响BKA 特性的性能。从全局怎么调整join_buffer_size，并又能充分利用上BKA，是一个极大的挑战。调优最常见的问题，搞定了A sql,又引出了其他问题，比如内存使用率过高。
MySQL 还自动支持其他join 优化，一旦对查询有性能帮助，优化器会自动选择他们，一般不需要手动。
了解一下其他join的优化方式，有助于我们在遇到sql性能问题时，可以适当给与优化器，一些有用的hint。
具体有哪些join 优化方式，可以查看explain 输出中的Extra 的内容说明。本文可能列举的不全，精力有限只做了一些简单的介绍，具体细节需要查看官网，以及大量的实践。
还有我们可以用哪些方法影响优化器，下篇文章再续。
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
