基础优化-索引及执行计划（七）

1. 执行计划分析

1.1 执行计划

select * from t1 where name='zs';
执行计划分析：分析的是优化器按照内置的cost计算算法（模型），最终选择后的执行计划。

优化器毕竟是一个代码，有可能会出先偏差的时候，这个时候就需要管理员读懂优化器，最终得出的结论合不合理。需要有一些评估的标准

对于计算机来讲，代价（cost）是资源，消耗的资源越多，代价越高（IO，CPU，MEM）

1.2 查看执行计划

（1） 查看执行计划

-- 查看执行计划，explain、desc这两种方式都可以
mysql> explain select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 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  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

mysql> desc select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 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  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

（2）explain(desc)使用场景（面试题）

题目意思:  我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist;  获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

1.3 执行计划显示结果的认识

table： 此次查询涉及到的表
type： 查询类型，全表扫描或者索引扫描
possible_keys：可能用到的索引
key：最后选择的索引
key_len：索引覆盖长度
rows：此次查询需要扫描的行数
extra：额外的信息

1.4 输出信息介绍

1.4.1 table

此次查询涉及到的表，针对一个查询中多个表时，精确到问题表。
desc select country.name ,city.name from city join country on city.countrycode=country.code where city.population='CHN';

1.4.2 type查询类型

（1）全表扫描：不用任何的索引。ALL。
查询结果是需要把整个索引树扫描一遍才能得到的这种情况，成为全索引扫描，代价高
例如：

mysql> desc select * from city;
mysql> desc select * from city where 1=1;
mysql> desc select * from city where countrycode like '%ch%';
mysql> desc select * from city where countrycode not in ('CHN','USA');
mysql> desc select * from city where countrycode != 'CHN';

（2）索引扫描
index < range < ref < eq_ref < const(system)
索引扫描从左到右索引效率增高

index： 全索引扫描

mysql> desc select conuntrycode from world.city;

range： 索引范围查询（> < >= <= like in or between and）

mysql> desc select * from city where id<10;
mysql> desc select * from city where coutrycode like 'CH%';
mysql> desc select * from city where countrycode in ('CHN','USA');
--> 如果效率不高可以改写成 union all 
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';

-- 特殊情况：查询条件为主键时
mysql> desc select * from city where id != 10;
mysql> desc select * from city where id not in (10,20);

ref： 辅助索引等值查询

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

eq_ref： 多表连接中，非驱动表连接条件是主键或唯一键。

-- 下面的B就是非驱动表，B的yy列如果是主键列或唯一键，那么索引类型就是ref
A join B on A.xx=B.yy
-- 进行测试
desc select country.name,city.name
from city join country
on city.countrycode=country.code
where city.popualtion='CHN';

const(system)： 聚簇索引等值查询（只有一行数据的时候是system，不常见）

mysql> desc select * from city where id=10;

1.4.3 possible_keys和key

possible_keys：可能会走的索引，所有和此次查询有关的索引
key：此次查询选择的索引。

1.4.4 key_len

key_len：联合索引覆盖长度
对于联合索引index(a,b,c)，我们希望将来的查询语句，对于联合索引应用越充分越好
key_len可以帮助我们判断，此次查询走了联合索引的几部分。
比如下面的这些结论，并不是随便说的，咱们也可以用key_len来验证下面的结论的正确性。

-- 创建idx(a,b,c)联合索引，等价于a  ab  abc这三个索引都被创建了
idx(a,b,c) ---> a  ab  abc 

-- 全部覆盖（也可以是and or in等作为条件），99%的情况下走索引
select * from t1 where  a=  and  b=  and  c=
select * from t1 where  a in  and b in and c in
select * from t1 where  b=  and c=  and  a= # 虽然a放在最后面，但是优化器会自动把a放到前面，来满足联合索引的查询条件

-- 部分覆盖（也可以是and or in等作为条件），部分索引只要查询的条件只包含a  ab  abc就走索引，其他的就不走索引了
select * from t1 where a= and b=  # 走索引
select * from t1 where a=         # 走索引
select * from t1 where a= and c=  # 不走索引
select * from t1 where a= and b > < >= <= like and c= # 遇到不等值，不等值后面的索引就不生效了。索引整体不走索引
select xxx from t1 where a order by b  # 走a、b的索引，要看执行循序
select xxx from t1 where c order by a  # 不能走索引
-- 不覆盖
bc  b  c

key_len的计算： idx（a,b,c）
假设，某条件查询可以完全覆盖三列联合索引。例如：
select * from t1 where a= and b= and c=
key_len= a长度 + b长度 + c长度

长度：列的最大存储值字节长度
长度受到：数据类型，字符集影响

数字：
				not null	没有not null（会专门占用一个字节记录空还是非空） 

	tinyint		1				1+1
	int			4				4+1
	bigint      8				8+1
key_len:
a int not null ----> 4
a int		   ----> 5


字符： utf8 ----> 一个字符最大占3个字节
     （utf8mb4 ---> 一个字符最大占4个字节）
				not null	没有not null（会专门占用一个字节记录空还是非空） 
	char(10）	3*10 		3*10+1
	varchar(10) 3*10+2		3*10+2+1
	
小提示：varchar会留一个或两个字节存储字符串长度，key_len算的最大预留长度，所以是在基础上加2	

b(列) char(10) not null   30
b char(10) 				  31
c varchar(10) not null	  32
c varchar(10) 			  33


举个栗子：
create table t1(
a int not null,
b int,
c char(10) not null,
d varchar(10)
)charset = utf8mb4

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

问：查询中完全覆盖到4列索引，key_len是多少？（92）
create table t1(
a int not null,       4
b int,				  5
c char(10) not null,  40
d varchar(10)		  43
)charset = utf8mb4
-- 验证查看key_len的值
msyql> desc select * from t1 where a=1 and b=1 and c='a' and d='a';
mysql> desc select * from t1 where a=1 and b=1 and c='a';
mysql> desc select * from t1 where a=1 and b=1;
mysql> desc select * from t1 where a=1;
 

小提示：
（1）ch% 走的是最左前缀索引，%ch% 这样不走索引的原因是，最左边的%是不能进行排序
（2）not in != 这样不走索引的原因：不是确定的值，无法走索引树进行匹配
但是主键的!=可以走索引，自动转化成大于小于这种形式，走的range类型索引
（3）utf8 一个字符最大占3个字节，utf8mb4 一个字符最大占4个字节

1.4.5 extra

using filesort（不正常的现象）： 表示此次查询使用到了文件排序，说明在查询中的排序操作 order by group by distinc...

解决方法： 像 order by group by distinc...这样也走索引，可以创建联合索引，使其生效。（与where条件一块做联合索引，这样才有效果）

结论: 
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

Using index condition（正常的现象）： 索引覆盖

举个栗子：

-- extra: using filesort（不正常的现象）
mysql> desc select * from city where countrycode='CHN' order by population;
-- 增加联合索引，使其order by的索引也进行生效
mysql> alter table city add index idx_c_p(countrycode,population);
-- 再次查询执行计划，就不会再有extra: using filesort
mysql> desc select * from city where countrycode='CHN' order by population;

2. 索引应用规范

业务
1.产品的功能
2.用户的行为
"热"查询语句 --->较慢--->slowlog
"热"数据

2.1 建立索引的原则（DBA运维规范）

-- 2.1.0 说明
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上
创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

-- 2.1.1 (必须的) 建表时一定要有主键,一般是个无关列
(必须的) 建表时一定要有主键,一般是个无关列

-- 2.1.2 选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) 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等操作的字段
排序操作会浪费很多时间。
where  A B C      ----》 A  B  C
in 
where A   group by B  order by C
A,B，C

如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

-- 2.1.4 尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。

-- 2.1.5 限制索引的数目
索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

-- 2.1.6  删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

-- 2.1.7 大表加索引,要在业务不繁忙期间操作

-- 2.1.8 尽量少在经常更新值的列上建索引

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

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

-- 2.2.1 没有查询条件，或者查询条件没有建立索引
select * from tab;       全表扫描。
select  * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
（2）
select  * from  tab where name='zhangsan'          name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引

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

假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引
select * from tab  where id>500000;
如果业务允许，可以使用limit控制。
怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

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

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


-- 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

-- 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
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 专门做搜索服务的数据库产品

小提示：（对上面的内容进行总结）
（1）5.7以前可以查看mysql.innodb_index_stats简单的评估一下哪些索引用的频率比较高。5.7中可以查看视图sys.schema_unused_indexes可以看到哪些索引没有被使用过。也可以使用第三方工具percona-toolkit可以收集不怎么常用的索引，然后区删除这些索引。

（2）统计信息： mysql.innodb_index_stats mysql.table_stats存放的是表和索引的统计信息
这两个表不是实时更新的，统计信息表和真实的数据不准确很有可能造成索引的失效
办法： 删除重建索引或者 optimize table city;

（3）隐式转换：int类型转换为char类型—>应用了函数进行转换，索引不走索引
telnum列是字符串类型，并且telnum设置了索引
select * from a where telnum=110 # int类型转换为char类型
select * from a where telnum=‘110’
上面两个查询结果一样，但是第一个不走索引（应用了函数进行转换）

（4）可以使用sql_yog来查找重复的索引，并且删除它

3. 优化器针对索引的算法

扩展内容： 优化器针对索引的算法
AHI和Chage buffer默认自己就优化
优化器算法介绍：

-- 查看优化器算法
mysql> 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

如何使算法生效？
（1）my.cnf 配置文件中设置
（2）set global optimizer_switch='batched_key_access=off'
（3） hints 使单条的sql语句生效（了解即可）

3.1 MySQL索引自优化-AHI

MySQL的InnoDB引擎，能够创建只有Btree。
AHI（Adaptive Hash Index）：自适应的hash索引表

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

小提示：
（1）索引页在内存中是没有没有规律的比较杂乱，不便于内存读取数据的，不清楚哪些经常使用和不经常使用的。mysql会在内存中经常使用的索引页生成一个内存的hash表（AHI），使得我们访问热的数据页的时候更加迅速。索引AHI可以理解索引页的索引，作用就是快速找到内存中的索引页
（2）自适应hash索引是innodb独有的，其他引擎是没有的

3.2 MySQL索引自优化-Chage buffer

第四部分：索引问题总结中有详细说明

比如insert，update，delete数据
对于聚簇索引会立即更新的
对于辅助索引，不是实时更新的
在innodb内存机构中，加入了insert buffer（会话），现在版本叫change。
change buffer功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert的数据，会在内存中进行merge（合并）操作，此时辅助索引就是最新的。

小提示：
（1）change buffer不能调太大，浪费内存，因为一个用户连接就会分配一个。

3.3 ICP索引下推算法

ICP：索引下推

作用： 解决了联合索引只能部分应用情况。为了使减少没必要的数据页被扫描。
将不走索引的条件，在engine层取数据之前先做c的二次过滤。
index(a,b,c)
select * from t1 where a=xx and c=xx
在server-sql层先做a列过滤条件的索引优化
在将c列过滤下推到engine层在做过滤

举个栗子：

正常的执行逻辑：选择B方案，走索引查询

下图中select * from t1 where a=xx and c=yy有联合索引index(a,b,c)根据key_leng的结论，只走了a的索引，这时sql层分析的结果是遍历a列所有的数据页，然后告知引擎层。又因为有了IPC，索引取数据的需求直接下推到引擎层，引擎层发现c也有索引，索引拿着c的条件在进行一次过滤，最终得结果需要读取哪些数据页，加载到内存，最后从这些数据也中得出最终结果。 就比如下图中如果没有ICP就要从磁盘中读10个数据页到内存，如果有了数据页就从磁盘中读两个数据页加载到内存中。

作用： 减少无关数据页的扫描，如果没有IPC会读取有关a的所有数据页加载到内存中
结论： 就是在sql层和存储引擎曾经历了两次过滤，最终得出读取的数据页，在加载到内存中。
注意： 跟联合索引不一样，只是经历了两次过滤。索引的应用在SQL层，解析优化的时候会应用到，引擎层就是把索引页加载起来，不做过滤。但是有了ICP之后，即在sql层做过滤又在引擎层做过滤（如果不走索引，引擎拿着sql层给出的方案去找数据页）

没有ICP的时候：select * from t1 where a= and c= 先把基于a条件的所有数据页全过滤出来，然后在c的条件在遍历一遍
有ICP的时候：select * from t1 where a= and c= SQL层虽然说需要把提取a条件的数据页出来 ，先不着急取出来，因为有了索引下推，发现c列也是有索引的，引擎层在拿c列的条件，根据综合条件，最后再从磁盘上提取出来。

有了ICP之后：

官方图：

3.4 MRR

设置使用MRR（multi-range read）
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off'

辅助索引 - - - 回表 - - -> 聚簇索引
有了MRR就转换为：
辅助索引 - - - sort id - - 回表 - -> 聚簇索引

小提示：
（1） 默认mrr_cost_based=on是开启的，如果使用mrr，需要把这关闭mrr_cost_based=off，这样大概率才走mrr。
（2） 没有mrr之前，辅助索引查询有多个name=‘zs’ 但是id不同，这时需要进行聚簇索引，一个id一个id的进行回表，导致回表次数增多会产生随机IO
（3）有个mrr之后，回表查询之前，先把ID值在缓冲区里排个序，在同一个数据页中的一块查询，这样可以检查随机IO（直接简洁理解在缓冲区的id可以一次性的把数据拿出来，减少回表）
（4）辅助索引拿到一个id值就到聚簇索引中回表一次。辅助索引查到的id放到缓冲区，排个序然后一次性的到聚簇索引中回表

官方图：
（1）没有mrr的模型图。左侧是辅助索引，右侧是聚簇索引。

（2）有mrr的模型图，中间多了个缓冲区。

3.5 SNLJ

NLJ（Nested-Loop Join Algorithms）
SNLJ（Simple NLJ） 没有任何索引的情况下走这个算法，双循环嵌套查询匹配。

直接举例子：

-- 多表连接查询
A join B on A.xx = B.yy where ...
-- 伪代码(嵌套for循环)
for each row in A matching range{
	for each row in B{
		A.xx = B.yy, send to client
	}
}
-- 以上栗子，可以通过left join 强制驱动表（把行数少的表做为驱动表）

3.6 BNLJ

BNLJ（Block Nested-Loop Join）MySQL 5.7版本默认是开启的

在A和B关联条件匹配时，不在一次一次进行循环。而是采用一次性驱动表的关联值和非驱动表匹配，一次性返回结果。主要优化：减少了CUP的消耗，减少IO的次数。

外层循环（A表）是驱动表，where 没有过滤条件或者where没有索引的话，也会对A表进行全表扫描，然后和B表进行关联。如果有过滤条件，先把A表过滤出来的条件放到join buffer中，然后在一次性的去查询。

如果有索引的情况，在辅助索引读取的值放到join buffer中，然后在一次性去另一张表进行去匹配，尽可能减少循环的次数。不是逐行进行匹配了，而是放到一堆，然后一次性的进行匹配，减少的关联的次数，循环的次数。（缺点还是会重复读取数据页，有随机io产生，索引出现了下面的BKA算法）

3.7 BKA

BKA（Batched Key Access joins）
使用BAK的前提：非驱动表（上面栗子中的B表就是非驱动表）的关联列有辅助索引（主键唯一键不需要这个算法，因为查出来的数据就是唯一，本身就是顺序的，不需要额外的排序）

BKA这种算法会依赖于mrr的算法（BNLJ+NRR）
BNLJ 在连表时有个join buffer缓冲区，一堆一堆的去查询。
NRR（辅助索引和聚簇索引之间的关系），在聚簇索引的时候做了个排序

开启方式：

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

如果两条数据在一个数据页上，那么就读一次数据页就可以读取到两条数据了
可以减少io的次数（如下图如果没有sort，可能读取最大次数六次，有了sort就只读四次）

4. 索引问题总结

问题一： 回表查询详细解释

-- 1. 数据库中的表长成什么样？
mysql用来存储数据行的逻辑结构，表的数据行最终存储到了很多的page上。
innodb存储引擎，会按照聚簇索引，有序的组织存储表数据到各个区的连续的页上。
这些连续的数据页，成为了聚簇索引的叶子节点。你可以认为聚簇索引就是原表数据。
所以回表即是，回聚簇索引

-- 2. 什么时候回表
辅助索引： 将辅助索引列值+ID主键值，构建辅助索引B树结构
用户使用辅助索引列作为查询条件查询时,首先扫描辅助索引的b树。
(1) 如果辅助索引能够完全覆盖我们的查询结果时，就不需要回表了。
(2) 如果不能完全覆盖到，只能通过得出的ID主键值，回到聚簇索引（回表）扫描

-- 3. 回表带来什么影响
(1) IO量级变大（查询的次数变多，IO也就变大了）
(2) IOPS会增大（每秒的IO次数会变大，每块磁盘瓶颈的点，有上限的，如果达到上线就要等待）
(3) 随机IO会增大（一个数据页跳到另一个数据页里面，有可能不在一个连续的区里面，不在连续的区里面，可能在磁盘读的时候就是随机性的）

-- 4. 怎么减少回表
(1) 将查询尽可能用ID主键查询
(2) 设计合理的联合索引
	 完全覆盖: select name,age,gender from t1 where name='zs' # name,age,gender可以创建一个联合索引
(3) 更精确的查询条件+联合索引
	 select * from t1 where name='zs' and addr='bj' # 精确的条件，减少回表的次数
(4) 优化器算法： MRR（扩展内容）

问题二： 更新数据时，会对索引有影响吗？数据的变化会使索引实时更新吗？

比如insert，update，delete数据
对于聚簇索引会立即更新的
对于辅助索引，不是实时更新的
在innodb内存机构中，加入了insert buffer（会话），现在版本叫change。
change buffer功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert的数据，会在内存中进行merge（合并）操作，此时辅助索引就是最新的。下面有详细解释。

详细解释：
ibd文件就是表空间文件（段），有很多数据页，有存数据的，有存聚簇索引、辅助索引的。下图中C代表聚簇索引（可以理解为表的数据行，叶子节点），S代表辅助索引（部分数据）

正常的读取流程：select * from t1 where name='zs'
首先会判断有没有针对这个列的索引，如果有的话就会把S数据页调到内存中去。如何需要回表，然后再把聚簇索引加载到内存中。（数据库静止的状态下，没有更新也没有删除）

如果新录入新数据，首先会更新聚簇索引的值，聚簇索引会被立即更新的。辅助索引的信息不想立即让他更新，因为将来对辅助索引列值更新的请求会更多一些。因为像主键这些无非加一些值，很快就完成了，在内存中然后写入磁盘。但是对辅助索引来说不是立即完成的，而是先把这些数据放到change buffer里面（独立区域）。S1: 更新的辅助索引的值

如果更新的辅助索引（此时在change buffer中）要读取怎么办那？先把idb中的S加载到内存中，然后S1于S做merge，然后在辅助索引，最后在聚簇索引（回表查询）。有了change buffer这个内存结构，使得我们呢的增删改这种操作，对于辅助列的值来讲，没有立即更新到磁盘的S区域里面去，索引可以达到减轻索引更新的频次。如果查询完成后，也会把S1存到磁盘的S区。

change buffer会话级别的，每个连接进来的都会分配一个change buffer，会有很多change buffer，每个用户都有 ，用完了就会回收。这个大小是可以控制的，如果大小不够的话，会立即更新索引树。可以通过增大change buffer可以优化insert
，大批量的insert，要插入100w行大事务，可以临时调大change buffer加快插入的过程。还有大批量的update、delete

问题三： 如何知道用户走了我们设置的索引？遇到双11那种大量访问时，索引不能及时设置，大厂如何解决这种问题的？

（1）slowlog日志可以收集，不走索引的语句
（2）双十一的时候，并发度太高，提前1-2周将热点商品数据，灌入到Tair（类似于redis,memcache）集群中。

问题四： msyql后台查询表数据，中文数据乱码，在sqlyog中显示正常

（1） Linux没有中文语言支持包
（2） 字符集设置有问题