MySQL基础优化-索引及执行计划

1.什么是索引？
相当于一本书的目录。优化查询
2.MySQL 支持的索引类型（算法）

Btree : 平衡多叉树
Rtree : 空间树索引
Hash  : HASH索引
fulltext: 全文索引

3.数据查找算法介绍

二叉树
红黑树
Btree: Blance Tree

BTree 的查找算法（见图）

B-Tree：每次查询都从根节点开始，不能从枝节点或者叶子节点之间直接转换
B+Tree：实际上是在枝节点上添加了双向指针信息，从而减少对根节点的IO消耗
B*tree  ：实际上是在非根节点上添加了双向指针信息，从而减少对根节点和枝节点的IO消耗

B6E99DF40DF7A76F6F1DA9947B62E5AF.jpg

MySQL Btree 索引的应用
5.1聚簇索引（聚集索引、主键索引）
前提:
0>InnoDB存储引擎的表才会有聚簇索引
1>有主键，主键就是聚簇索引
2>没有主键，选择唯一键作为聚簇索引
3>生成一个隐藏列（DB_ROW_ID,6字节），作为聚簇索引

作用：
1. 聚簇（区）索引，组织表（IOT）：所有数据在插入时，都按照ID（主键）属性，在相邻数据页上有序存储数据。
2. 加快存储数据，加快通过索引作为查找条件的查询。

参考：

    https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

聚簇索引构建过程（见图）

1.png

叶子节点：
由于存储数据时，已经按照ID顺序在各个数据页中有序存储了，所以《原表数据》所在数据页被作为叶子节点。
内部节点（非叶子节点--->枝节点）：
获取叶子节点ID范围+指针。
根节点：
获取非叶子节点 ID范围+指针

5.2辅助索引
构建过程：
1. 叶子节点构建：
提取索引列值+ID ，进行从小到大排序（辅助索引列值），存储到各个数据页，作为叶子节点。
2. 非叶子节点（internel node ）
提取下层的辅助索引列值范围+指针。
3. 根节点：
提取下层节点的范围+指针。
对于查询的优化：

    1. 通过辅助索引列，进行条件查询，根据辅助索引BTREE快速锁定条件值对应的ID。
    2. 通过得出的ID值，回到聚簇索引继续查询到具体的数据行（回表）。

2.png

辅助索引分类
6.1. 普通单列
6.2. 联合索引
idx(a,b)提取出来再排序，先按a列排，如果a列用相同的多个值的话，就按照b列的值进行排列（先拿最左列排）
叶子节点：
id+a+b ,按照a和b进行排序，生成叶子节点
枝节点和根节点：
只会包含最左列（a列）的范围+指针（最左原则）
注意：最左原则
1. 建索引，最左列重复值少的。
2. 查询条件中，必须包含最左列。
6.3. 唯一索引
unique key

6.4. 前缀索引
idex(test(10))

索引树高度影响因素
7.1 列值长度
前缀索引。
7.2 数据量
分区表。
定期归档表。
分布式架构：分库、分表。
7.3 数据类型
定长：char(20)
变长：varchar(20)

回表问题的探讨？
什么是回表？
辅助索引查找完成----> 聚簇索引查询过程。

回表会带来的问题？
IO增多：量、次数

如何减少回表？
使用唯一值索引查询
联合索引
覆盖索引：辅助索引完全覆盖到查询结果

索引应用
9.1 压测

mysql> source /root/t100w.sql    #上传数据
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';    #创建一个远程登陆用户

进行压测

shell> 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 -h10.0.0.51  -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

9.2 查询表的索引

#查看表索引的三种方法
desc t100w;  
-----
 Key 
-----
PK     --> 主键（聚簇索引）     
MUL    --> 辅助索引   
UK     --> 唯一索引  
     
mysql> show index from t100w;
mysql> show creat table city;

9.3 创建索引
9.3.1 单列辅助索引

select * from test.t100w where k2='780P'
优化方式： 
alter table 表名 add index 索引名(列名);   
alter table t100w add index idx_k2(k2);

9.3.2 联合索引创建

mysql> alter table t100w add index idx_k1_num(k1,num);

创建联合索引的时候要根据最左原则进行创建
对比k2 和 k1 的重复值，少的放在最所测，优化效果更佳

select count(distinct k1) from t100w;
select count(distinct k2) from t100w;

9.3.3 前缀索引创建

#判断前缀长度多少合适：
select count(distinct(left(name,5)))  from city ;
select count(distinct name)  from city ;
#创建前缀索引
mysql> alter table city add index idx_n(name(5));

9.4 删除索引

#语法：alter tabel 表名  drop index 索引名；
alter table city drop index idx_n;

执行计划查看和分析
10.1 什么是执行计划?
优化器优化后的“执行方案”。

10.2 作用？
a. 语句执行之前，通过执行计划，防患于未然。
b. 对于有性能问题的语句，进行分析。得出优化方案。

10.3 获取SQL的执行计划。
Select 、 update 、 delete

#查看执行计划的两种方法
mysql> explain select * from test.t100w where num=279106  and k2='VWtu';
mysql> desc select * from test.t100w where num=279106  and k2='VWtu';
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | ix_k2,idx,idx1 | idx1 | 22      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+

10.4 执行计划介绍

table          ： 操作的表
type           ： 查询索引的类型（ALL、index、range、ref 、eq_ref、const(system)）
possible_keys  :  可能会走的。
key            ： 最终选择的索引。
key_len        ： 联合索引覆盖长度。
rows           ： 此次查询需要扫描的行数(预估值)。
Extra          ： 额外信息。

10.5 type 详解
10.5.1 ALL 全表扫描
a. 查询条件没有建索引

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  |     | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

5 rows in set (0.00 sec)

mysql> desc select * from t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

b. 有索引，但查询语句不能走的情况。

mysql> alter table t100w add index idx(k2);
mysql> desc select * from t100w where k2 like '%80P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

mysql> desc select * from t100w where k2 != '780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |


mysql> desc select * from t100w where k2 not in ('780P');
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

10.5.2 index 全索引扫描

mysql> desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | index | NULL          | idx  | 17      | NULL | 986679 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+

10.5.3 range 索引范围扫描

mysql> desc select * from city where id<100;
mysql> desc select  * from city where countrycode like 'CH%';
mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select  * from world.city where countrycode in ('CHN','USA');

union all 改写:

desc 
select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA';

注意： 
    如果重复值过多的话，可能改写的效果不佳。

通过压测： 判断改写效果。

 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from world.city where countrycode in ('CHN','USA'); " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose

 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA'; " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose

10.5.4 ref : 辅助索引等值查询

mysql> desc select  * from city where countrycode='CHN';

10.5.5 eq_ref: 多表连接
非驱动表的连接条件是主键或唯一键。是多表连接中性能最好的查询方法。
拿结果集小的作为i驱动表

mysql> desc select a.name,b.name from city as a  join  country as b on a.countrycode=b.code where a.population<100;
+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys   | key     | key_len | ref                 | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
|  1 | SIMPLE      | a     | NULL       | range  | CountryCode,idx | idx     | 4       | NULL                |    1 |   100.00 | Using index condition |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY         | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL                  |
+----+-------------+--

#left join 强制左表为驱动表
mysql> desc select a.name,b.name from city as a  left join  country as b on a.countrycode=b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                | 4188 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

10.5.6 const(system)
主键或唯一键等值查询

10.6 key_len 说明
10.6.0 计算方式
a. 介绍：
索引的应用长度
b. 作用：
判断联合索引的覆盖长度。
idx(a,b,c)

c. 如何计算key_len
总长度的计算：
a+b+c

d. 每个索引列占用多长？
每个列key_len,是这个列的《最大》预留长度。
影响因素：
1. 数据类型
2. not null
3. 字符集(字符串类型)

3.png

计算

create table test (
id int  not null primary key auto_increment,
a int not null ,
b char(10) not null ,
c char(5) ,
d varchar(20) not null ,
e varchar(10)
)engine=innodb charset=utf8mb4;

alter table test add index idx(a,b,c,d,e);


4+40+21+82+43=190

计算方法

mysql> desc select * from test where a=1 ;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 44      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa' and c='aa' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 65      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 147     | const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' and  e='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 190     | const,const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+

10.6.1 联合索引应用细节

a. 联合索引全覆盖
idx(num,k1,k2) 索引相当于index(num,k1)、index(num)

mysql> desc select * from t100w  where num=641631  and k1='At'  and k2='rsEF';
mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2='rsEF';
mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2 like 'rsE%';

b. 部分覆盖

mysql> desc select * from t100w  where num=641631  and k1='At' ;
mysql> desc select * from t100w  where   k1='At'  and num=641631 ;
#中间缺了k1所以只走num,后面就不能走索引了k2列走不到索引
mysql> desc select * from t100w  where  num=641631  and  k2 like 'rsE%';
mysql> desc select * from t100w  where num=641631  and k1 > 'AZ'  and k2='rsEF';
mysql> desc select * from t100w  where num=641631  and k1 != 'AZ'  and k2='rsEF';

c. 完全不覆盖

#索引遵循最左原则的，这里没有num列，所以不会走索引的
mysql> desc select * from t100w  where k1='At'  and     k2 like 'rsE%';

d. 在多子句必须得使用联合索引

where a   order by  b 
where a   group by  b   order by   xxx

10.7 Extra
using where : 此次查询中有部分条件是没有走索引的。
如果出现以上信息，说明where 条件，索引设计问题或者语句有问题。
using filesort : 出现文件排序，order by 、 group by 、 distinct ...

索引应用规范总结
11.1 建立索引的原则（DBA运维规范）

(1) 必须要有主键,无关列。
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc
(7) 联合索引最左原则

11.2 不走索引的情况（开发规范）

11.2.1 没有查询条件，或者查询条件没有建立索引
select * from t1 ;
select * from t1 where id=1001 or 1=1;

11.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。

可以通过精确查找范围，达到优化的效果。
1000000

大于 > 500000 and

11.2.3 索引本身失效，统计信息不真实（过旧）
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:

有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,统计数据不真实

innodb_index_stats  
innodb_table_stats  
结局方法
#1.立即更新统计信息为最新的
# mysql> ANALYZE TABLE  表名
mysql> ANALYZE TABLE world.city;  
#2.重建索引

11.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

算术运算  函数运算  子查询 都会导致索引失效或者不走索引

11.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
11.2.6 <> ，not in 不走索引（辅助索引）
11.2.7 like "%_" 百分号在最前面不走

12. 彩蛋(扩展)：优化器针对索引的算法

12.1 自优化能力：
12.1.1 MySQL索引的自优化-AHI（自适应HASH索引）
a. 限制
MySQL的InnoDB引擎，能够手工创建只有Btree。
AHI 只有InnoDB表会有，MySQL自动维护的。

AHI作用： 
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。

4.png

参考文章：

https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

12.1.2 MySQL索引的自优化-Change buffer
限制：
比如insert，update，delete 操作时会使用change buffer。
对于聚簇索引会直接更新叶子节点。
对于辅助索引，不是实时更新的。
insert into t1 (id,name,age) values(33,'d',18)

在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

参考：
https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

12.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)

12.2 可选的优化器算法-索引

12.2.1 优化器算法查询

select @@optimizer_switch;        #查询默认优化器算法=show variables like '%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

12.2.2 如何修改?

my.cnf

#修改配置文件
optimizer_switch='batched_key_access=on'

#在线修改
 set global optimizer_switch='batched_key_access=on';

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

12.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)

#开启ICP参数
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 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 1.114 seconds
    Minimum number of seconds to run all queries: 1.114 seconds
    Maximum number of seconds to run all queries: 1.114 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20


Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 6.945 seconds
    Minimum number of seconds to run all queries: 6.945 seconds
    Maximum number of seconds to run all queries: 6.945 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200

测试2:ICP关闭时：
idx(k1,k2)

#开启ICP参数
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 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 3.125 seconds
    Minimum number of seconds to run all queries: 3.125 seconds
    Maximum number of seconds to run all queries: 3.125 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20


Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 31.102 seconds
    Minimum number of seconds to run all queries: 31.102 seconds
    Maximum number of seconds to run all queries: 31.102 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200

具体参考：
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/

12.4 MRR : Multi Range Read

12.4.1 作用：理论上减少回表。

辅助索引扫描后，得到聚簇索引值，统一缓存到read_rnd_buffer，进行排序，再次回表。

12.4.2 开关方法：

mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';

12.4.3 区别
具体参考：
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://mariadb.com/kb/en/multi-range-read-optimization/

压力测试： 
alter table world.city add index idx_n(name);
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

no-mrr: 
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 2.909 seconds
    Minimum number of seconds to run all queries: 2.909 seconds
    Maximum number of seconds to run all queries: 2.909 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200


mrr: 
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 3.384 seconds
    Minimum number of seconds to run all queries: 3.384 seconds
    Maximum number of seconds to run all queries: 3.384 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200

优化没有最佳实践。

12.5 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;

优化器默认优化规则：

选择驱动表

默认选择方式（非驱动表）： 
    0. 结果集小的表作为驱动表
    按照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 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 强制驱动表。

12.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)

12.7 BKA
主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式：
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。

MySQL基础优化-索引及执行计划

11.2 不走索引的情况（开发规范）

12. 彩蛋(扩展)：优化器针对索引的算法

12.4.1 作用： 理论上减少回表。

优化没有最佳实践。

12.4.1 作用：理论上减少回表。