1- 索引原理及应用
1. 介绍
索引相当于一本书的目录，可以优化查询。
- 索引查找算法
1 —》 100 盒子
谁最快猜到数字，礼品归谁。
我会给大家提示。- 遍历
- 二分法 —> 二叉树 —> 红黑树 —> Balance Ttree(平衡多叉树，简称为BTREE)
- BTREE查找算法演变
3.1 B-TREE ： 普通 BTREE
3.2 B+TREE ： 叶子节点双向指针
3.3 B++TREE（B*TREE）：枝节点的双向指针
2-MySQL中如何使用BTREE
==聚簇（区）索引（集群索==引，聚集索引）
前提：
1、 如果表中设置了主键（例如ID列），自动根据ID列生成索引树。
2、 如果没有设置主键，自动选择第一个唯一键的列作为聚簇索引
3、 自动生成隐藏的聚簇索引。
4.1.2 建议： 在建表时，显示的创建主键，最好是数字自增列3-功能
录入数据时，按照聚簇索引组织存储数据，在磁盘上有序存储数据行。
2. 加速查询。基于ID作为条件的判断查询。 4 btree构建过程
a. 叶子节点： 存储数据行时就是有序的，直接将数据行的page作为叶子节点（相邻的叶子结点，有双向指针）
b. 枝节点 ： 提取叶子节点ID的范围+指针，构建枝节点（相邻枝节点，有双向指针）
c. 根节点 ： 提取枝节点的ID的范围+指针，构建根节点
2-辅助索引
==需要人为创建辅助索引，将经常作为查询条件==的列创建辅助索引，起到加速查询的效果。
1- btree 构建过程
a. 叶子节点：提取主键（ID）+辅助索引列，按照辅助索引列进行从小到大排序后，生成叶子节点。（相邻的叶子结点，有双向指针）
b. 枝节点 ：提取叶子节点辅助索引列的范围+指针，构建枝节点（相邻枝节点，有双向指针）
c. 根节点 ：提取枝节点的辅助索引列的范围+指针，构建根节点
2-功能
按照辅助索引列，作为查询条件时。
1. 查找辅助索引树，得到ID值
2. 拿着ID值回表（聚簇索引）查询
5. 使用索引考虑的事项
5.1 回表是什么？ 回表会带来什么问题？ 怎么减少回表？
a. 按照辅助索引列，作为查询条件时，先查找辅助索引树，再到聚簇索引树查找数据行的过程。
b. IO量多、IO次数多、随机IO会增多
c. 减少回表：
1. 辅助索引能够完全覆盖查询结果，可以使用联合索引。
2. 尽量让查询条件精细化，尽量使用唯一值多的列作为查询条件
3. 优化器：MRR（Multi-Range-Read）， 锦上添花的功能。
mysql> select @@optimizer_switch;
mysql> set global optimizer_switch='mrr=on';
功能：
1. 辅助索引查找后得到ID值，进行自动排序
2. 一次性回表，很有可能受到B+TREE中的双向指针的优化查找。3-索引树高度的影响因素？ 如何解决？
a. 高度越低越好
b. 数据行越多，高度越高。
1. 分区表。一个实例里管理。
2. 按照数据特点，进行归档表。
3. 分布式架构。针对海量数据、高并发业务主流方案。
4. 在设计方面，满足三大范式。
c. 主键规划：长度过长。
1. 主键，尽量使用自增数字列。
d. 列值长度越长，数据量大的话，会影响到高度。
1. 使用前缀索引
100字符 只取前10个字符，构建索引树。
e. 数据类型的选择。
选择合适的、简短的数据类性。
例如：
1. 存储人的年龄 ，使用 tinyint 和 char(3)哪个好一些
2. 存储人名，char(20)和varchar(20)的选择哪一个好。
a. 站在数据插入性能角度思考，应该选：char
b. 从节省空间角度思考,应该选：varchar
c. 从索引树高度的角度思考，应该选：varchar
建议使用varchar类型存储变长列值。
3-索引应用
压测
source /root/t100w.sql
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
--concurrency=100 ： 模拟同时100会话连接
--create-schema='test' ： 操作的库是谁
--query="select * from test.t100w where k2='780P'" ：做了什么操作
--number-of-queries=2000 ： 一共做了多少次查询
Average number of seconds to run all queries: 719.431 seconds
Minimum number of seconds to run all queries: 719.431 seconds
Maximum number of seconds to run all queries: 719.431 seconds
2-查询表的索引
desc t100w;
-----
Key
-----
PK --> 主键（聚簇索引）
MUL --> 辅助索引
UK --> 唯一索引
mysql> show index from t100w;
3 创建索引
3.1 单列辅助索引
select * from test.t100w where k2='780P'
优化方式：
语法：
alter table 表名 add index 索引名(列名);
alter table t100w add index idx_k2(k2);
6.3.2 联合索引创建
mysql> alter table t100w add index idx_k1_num(k1,num);
6.3.3 前缀索引创建
判断前缀长度多少合适：
select count(distinct(left(name,5))) from city ; #distinct 江name 取出来去重复
select count(distinct name) from city ;
创建前缀索引
mysql> alter table city add index idx_n(name(5));
4- 删除索引
alter table city drop index idx_n;4-执行计划获取和分析
命令介绍: 获取语句的执行计划工具。
- ==explain==
desc
1- 使用方法
mysql> desc select * from city where countrycode='CHN';
mysql> explain select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2-执行计划信息介绍
table ：此次查询访问的表
type ：索引查询的类型（ALL、index、range、ref、eq_ref、const(system)、NULL）
possible_keys ：可能会应用的索引
key : 最终选择的索引
key_len ：索引覆盖长度，主要是用来判断联合索引应用长度。
rows ：需要扫描的行数
Extra ：额外信息3-type信息详解
ALL 没有使用到索引
a. 查询条件没建立索引
mysql> desc select * from city where district='shandong';
b. 有索引不走
mysql> desc select * from city where countrycode!='CHN';
mysql> desc select * from city where countrycode not in ('CHN','USA');
mysql> desc select * from city where countrycode like '%CH%';
7.4.2 index 全索引扫描
mysql> desc select countrycode from city;
7.4.2 range 索引范围扫描
会受到： B+TREE额外优化，叶子节点双向指针
mysql> desc select * from city where id<10;
mysql> desc select * from city where countrycode like 'CH%';
以下两种查询，大几率受不到叶子节点双向指针优化。
mysql> desc select * from city where countrycode in ('CHN','USA');
mysql> desc select * from city where countrycode='CHN' or countrycode='USA';
建议： 如果查询列重复值少的话，我们建议改写为 union all
desc
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
4-ref 辅助索引等值查询
desc select * from city where countrycode='CHN';5-eq_ref : 多表连接查询中
eq_ref : 多表连接查询中，非驱动表的连接条件是主键或唯一键时。
mysql> desc select city.name,country.name
from city
left join country
on city.countrycode=country.code where city.population<100;6- const(system)： 主键或唯一键等值查询
mysql> select * from city where id=1;
7.4.6 NULL
mysql> desc select * from city where id=1000000000000000;
5-key_len
用来判断联合索引应用的部分。
例如：
idx(a,b,c)
我们希望应用联合索引的部分越多越好
1.2 如何计算
key_len=a+b+c
列的key_len长度，按照每列的最大预留长度来做的计算。
create table t1 (
id int,
a int ,
b char(10),
c varchar(10))最大存储预留长度（字节）:
-------------------------------------------------------------------------------
数据类型 ： 占用字节量 有not null 没有Not Null
-------------------------------------------------------------------------------
数字类型：
tinyint : 1字节 1 1+1
int ： 4字节 4 4+1
bigint ： 8字节 8 8+1
-------------------------------------------------------------------------------
字符串类型：
utf8:
char(10) ： 10*3字节 =30 30 30+1
varchar(10) ： 10*3+2字节=32 32 32+1
-------------------------------------------------------------------------------
utf8mb4:
char(10) ：10*4字节 =40 40 40+1
varchar(10) ：10*4字节+2 =42 42 42+1
-------------------------------------------------------------------------------
use test;
create table test (
id int not null primary key auto_increment,
a int not null , # 4
b int , # 5
c char(10) not null , # 40
d varchar(10), # 43
e varchar(10) not null # 42
)engine=innodb charset=utf8mb4;
alter table test add index idx(a,b,c,d,e);
5个列覆盖：
4+5+40+43+42=134
4个列覆盖：
4+5+40+43=92
3个列覆盖：
4+5+40=49
2个列覆盖：
4+5=9
应用1个列：
4
1.3 测试：
mysql> desc select * from test where a=10 and b=10 and c='a' and d='a' and e='a';
mysql> desc select * from test where a=10 and b=10 and c='a' and d='a';
mysql> desc select * from test where a=10 and b=10 and c='a';
mysql> desc select * from test where a=10 and b=10;1-联合索引应用细节
1.4.1 条件：
联合索引应用要满足最左原则
a. 建立联合索引时，选择重复值最少的列作为最左列。
b. 使用联合索引时，查询条件中，必须包含最左列，才有可能应用到联合索引。
1.4.2 联合索引不同覆盖场景mysql> alter table t100w add index idx(num,k1,k2);
num : 5
k1 : 9
k2 : 17
a. 全部覆盖 (key_len:31)
mysql> desc select * from t100w where num=913759 and k1='ej' and k2='EFfg';
mysql> desc select * from t100w where k1='ej' and k2='EFfg' and num=913759 ;
mysql> desc select * from t100w where num=913759 and k1='ej' and k2 in('EFfg','abcd');
mysql> desc select * from t100w where num=913759 and k1='ej' and k2 like 'EF%'
说明：
(a,b,c)
a= and b= and c=
b= and c= and a=
b. 部分覆盖
idx(a,b,c)
where a = and b =
where b = and a =
where a =
where a = and b> < >= <= in like between and and c=
例如：
mysql> desc select * from t100w where num=913759 and k1>'zz' and k2='EFfg';
如何优化？
(num,k1,k2) ----> (num,k2,k1)
mysql> desc select * from t100w where num=913759 and k2='EFfg' and k1>'zz' ;
c. 完全不覆盖
idx(a,b,c)
where b c
where b
where c
1.5. extra 额外的信息
using filesort ---> group by \ order by \distinct \ union all
mysql> desc select * from city where countrycode='CHN' order by population;
2-应用场景
数据库慢：
a. 应急性的慢。
show full processlist; ----> 慢语句 ----> explain SQL ---> 优化索引、改写语句
b. 间歇性慢。
slowlog ----> 慢语句 ---> explain SQL ---> 优化索引、改写语句
3-索引应用规范
2.1 建立索引的原则（DBA运维规范）
2.1.0 说明
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
2.1.1 (必须的) 建表时一定要有主键,一般是个无关自增列数字列。
1. 降低索引树高度。
2.1.2 选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
4-优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
2.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，
排序操作会浪费很多时间。
2.1.4 尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。
6-限制索引的数目
索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用
2.1.6 删除不再使用或者很少使用的索引(percona toolkit)
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
2.1.7 大表加索引,要在业务不繁忙期间操作
推荐使用PT-OSC
2.1.8 尽量少在经常更新值的列上建索引
总结： 建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc
6-不走索引的情况
2.2.1 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
2.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。
100w num 有索引
select * from t100w where num>1 ----> 全表
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。
可以通过精确查找范围，达到优化的效果。
1000000
>500000 and < 600000
2.2.3 索引本身失效，统计信息不真实（过旧）
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? --->索引失效,统计数据不真实
innodb_index_stats
innodb_table_stats
mysql> ANALYZE TABLE world.city;
2.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询
2.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| telnum | varchar(20) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>
2.2.6 <> ，not in 不走索引（辅助索引）
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in 可以修改成union all
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'
2.2.7 like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%类的搜索需求，可以使用elasticsearch 或者 mongodb 专门做搜索服务的数据库产品
7-彩蛋(扩展)：优化器针对索引的算法
``
3.1 自优化能力：
3.1.1 MySQL索引的自优化-AHI（自适应HASH索引）
MySQL的InnoDB引擎，能够创建只有Btree。
AHI作用：
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。
3.1.2 MySQL索引的自优化-Change buffer
比如insert，update，delete 数据。
对于聚簇索引会立即更新。
对于辅助索引，不是实时更新的。
在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。
3.1.3 8.0 版本索引的新特性
a. 不可见索引。invisable/visable index
针对优化器不可见。但是索引还在磁盘存在，还会自动维护。
对于索引维护时，不确定索引是否还有用。这时可以临时设定为invisable。
b. 倒序索引。
select * from t1 where c = order by a ASC , b desc ;
idx(c,a, b desc )
3.2 可选的优化器算法-索引
3.2.1 优化器算法查询
select @@optimizer_switch;
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on
3.2.2 如何修改?
1. my.cnf
optimizer_switch='batched_key_access=on'
2. set global optimizer_switch='batched_key_access=on';
3. hints 了解一下
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
3.2.3 index_condition_pushdown （ICP）
介绍： 索引下推 ，5.6+ 加入的特性
idx(a,b,c)
where a = and b > and c =
作用： SQL层做完过滤后，只能用a,b的部分辅助索引，将c列条件的过滤下推到engine层，进行再次过滤。排除无用的数据页。
最终去磁盘上拿数据页。
大大减少无用IO的访问。
测试1: ICP开启时
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=ON'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
测试2:ICP关闭时：
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=OFF'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
测试3:优化索引
idx(k2,k1)
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
具体参考 ：
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/
3.4 MRR : Multi Range Read
3.4.1 作用： 减少回表。
3.4.2 开关方法：
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
3.4.3 区别
具体参考 ：
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://mariadb.com/kb/en/multi-range-read-optimization/
压力测试：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from world.city where name in ('Aachen','Aalborg','Aba','Abadan','Abaetetuba')" engine=innodb --number-of-queries=20000 -uroot -p123 -verbose
3.5 SNLJ
例子：
A join B
on A.xx = B.yy
伪代码：
for each row in A matching range {
block
for each row in B {
A.xx = B.yy ，send to client
}
}
以上例子，可以通过 left join 强制驱动表。
3.6 BNLJ
在 A和B关联条件匹配时，不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了， CPU消耗，减少了IO次数
In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)
3.7 BKA
主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式：
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。
8-MRR
作用： 减少回表。减少随机IO。开关方法
set global optimizer_switch='mrr=on,mrr_cost_based=off';区别
具体参考 ：
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://mariadb.com/kb/en/multi-range-read-optimization/
压力测试：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from world.city where name in ('Aachen','Aalborg','Aba','Abadan','Abaetetuba')" engine=innodb --number-of-queries=20000 -uroot -p123 -verbose
SNLJ 普通嵌套循环连接
例子：
A join B
on A.xx = B.yy
where
伪代码：
for each row in A matching range {
block
for each row in B {
A.xx = B.yy ，send to client
}
}例子：
mysql> desc select * from teacher join course on teacher.tno=course.tno;优化器默认优化规则：
1. 选择驱动表
默认选择方式（非驱动表）：
按照on的条件列，是否有索引，索引的类型选择。
1. 在on条件中，优化器优先选择有索引的列为非驱动表。
2. 如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。
for each row in course matching range {
join buffer
block
for each row in teacher {
course.tno = tracher.tno ，send to client
}
}
关于驱动表选择的优化思路：
理论支撑：
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
注： 可以通过 left join 强制驱动表。
9-BNLJ
作用：
在 A和B关联条件匹配时，不再一次一次进行循环。采用块循环连接。
A表中需要关联的数据，现在join buffer缓冲
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了CPU消耗，减少了一部分IO消耗。
触发条件：非驱动表的连接条件有辅助索引。In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)
10-BKA
主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。开启方式：
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。总结： 多表连接优化
0. 驱动表选择
A join B on A.x=b.y
0.1. 让优化器自己决定：
（1）. 在on条件中，优化器优先选择有索引的列为非驱动表。
（2）. 如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。
0.2. 自主选择：
left join强制驱动表
0.3. 关于驱动表选择的优化思路：
理论支撑：
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
1. 情景一： 触发SNL的情况
非驱动表，关联条件 如果没有任何索引的话，只能默认使用SNL算法
代价较高，建议做合理优化。
例如：将非驱动表关联条件建立索引。
（1）主键或唯一键，会自动使用eq_ref算法进行执行查询。
（2）辅助索引，默认会采用BNL。如果开启BKA，会走BKA。
2. 情景二： 触发BNL的情况
非驱动表，连接条件如果有普通索引。会默认会用BNL。
3. 情景三： 触发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
最终结论：不管是优化单表或多表，重点是在于索引和语句本身优化。
