执行计划分析
什么是执行计划?
select * from city where sname='张飞';
分析的是优化器按照内置的代价计算模型算法，
最终选择后的执行计划。
cost ? 代价，成本
对于计算机来讲，代价是什么?
io,cpu,mem
查看执行计划
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 | 4046 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
执行计划显示结果的分析
table :此次查询涉及到的表
type :查询类型：全表扫，索引扫
possible_keys :可能会用到的索引
key :最后选择的索引
key_len :索引的覆盖长度
rows :此次查询需要扫描的行数
Extra :额外的信息
详细分析
table:此次分析所涉及到的表，多对查询时，定位到有问题的表
type 查询类型
全表扫描 ： 不会利用到所有的索引 ALL
例如:select * from city;
select * from city where sname = like '%y%';
select * from city where sname not in '张飞';
索引扫描 : index < range < ref < eq_ref < const(system)
index :全索引扫描
desc select contrycode from world ;contrycode有索引
range:索引范围查询: > < >= <= like in or between and
desc select countrycode from world.city where id > 10;
desc select countrycode from world.city where countrycode like 'F%';
desc select * from city where countrycode in ('USA','CHN');
特殊情况：查询条件为主键是可以走range 范围查询
!= 和 not in 会自动拆分为范围查询
ref : 辅助索引的等值查询
desc select * from city where countrycode = ('USA');
eq_ref :多表链接中，非驱动表的链接条件是主键或唯一键.
a join b on a.xx = b.yy
desc select c.name,l.name from city c join country l
on c.`CountryCode` = l.`Code` where c.`Name`='Shanghai';
const(system):聚簇索引等值查询。
desc select * from city where id=1;
possible_keys 可能用到的索引，所有和此次查询有关的索引情况
key:当次查询选择的索引
key_len :联合索引的覆盖长度
例如:index(a,b,c) 我们希望我们的查询，对联合索引，应用的越充分越好,
key_len可以帮我判断，此次查询走了联合索引的几部分.
假设，某条查询可以完全覆盖三列联合索引
select * from t1 where a= and b = and c =
ken_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个字节
not null 没有not null
char(10) 3* 10 3*10 +1
varchar(10) 3* 10 + 2(最多占用2个字节存储字符的长度) 3*10 + 2+ 1
b char(10) not null 30
b char(10) 31
c varchar(10) not null 32
c varchar(10) 33
字符: utf8mb4------->一个字符最大占4个字节
not null 没有not null
char(10) 4* 10 4*10 +1
varchar(10) 4* 10 + 2(最多占用2个字节存储字符的长度) 4*10 + 2+ 1
b char(10) not null 40
b char(10) 41
c varchar(10) not null 42
c varchar(10) 43
create table t1 (
a int not null ,
b int,
c char(10) not null,
d varchar(10) ) engine = innodb charset=utf8mb4;
alter table t1 add index idx_abcd(a,b,c,d);
desc select a,b,c,d from t1 where a= 1 and b = 3 and c = 'd' and d = '4';
1 SIMPLE t1 NULL ref idx_abcd idx_abcd 92 const,const,const,const 1 100.00 Using index
Extra
using filesort:表示此次查询使用到了文件排序操作，
说明此次查询的排序操作 order by group by distinct ...（可能有这些语句）
列子;
desc select * from city where countrycode='CHN' order by population ;
可以看到，extra出现了using filesort ，这时我们通过添加where条件字段和population字段的联合索引看是否能解决此问题；
alter table city add index idx_c_p(countrycode,population);
desc select * from city where countrycode='CHN' order by population ;
可以看到此问题已经解决。
索引应用规范
建立索引的原则（dba运维规范）
1.建表时一定要有主键，一般是个无关列
2.选择唯一性索引
3.在联合索引中，让唯一索引（重复值较少的列）作为最左边的键，可以更快速的来查询。
4.如果字段过长最好使用前缀索引.
5.索引数据不是更多更好
可能产生的问题：
1).磁盘空间增大
2).修改表时，对索引的重构和更新和麻烦，越多的索引，会使更新表的时间变慢
3).优化器的负担很重，会影响优化器的选择
6.删除不使用的或者很少使用的索引
(percona toolkit).py-duplicate-key-checker 工具可以查询到重复的索引
7.大表加索引，要在业务不繁忙的期间操作（8.0以前要锁表）
8.尽量少在经常更新值的列上加索引(update的列，这样容易索引失效)
9.建索引的原则：
1）必须要有主键，如果没有可以作为主键条件的列，创建无关列。
2）经常做为where条件列，order by group by join distinct的条件（业务：产品功能+用户行为）
3)最好使用唯一值多的列作为索引条件，如果索引列重复值较多，可以考虑使用联合索引。
4）列值长度较长的索引，我们建议使用前缀索引
5）降低索引条目，一方面不要创建没有用的索引，不常使用的索引清理，percona toolkit的工具.
6)索引维护要避开业务繁忙期
不走索引的情况(开发规范）
1 没有查询条件，或者查询条件没有建立索引
select * from tab; 全表扫描
select * from tab where 1=1 ;
在业务库中，特别是数据量比较大的表，
是没有全表扫描的需求。
对用户查看是非常痛苦的。
对服务器来讲是毁灭性的。
2.查询结果集是原表中的大部分数据，应该是15-30%,
查询的结果集，超过了总行数的25%，优化器就觉得没有必要
走索引。
原因是？？？ 跟数据库的预读能力有关，以及一些参数有关。彩蛋？？
解决办法，如果业务允许，进行分批次查询，或者进行分页查询。
limit 查询。
如果不允许，尽量不要把这部分数据存储在mysql，可以放到redis这种类似的
内存数据库中.
3。索引本身失效，统计数据不真实.
索引和表都有自我维护的能力。
对于表内容的变化比较频繁的情况下，统计信息不准确，过旧，有可能出现索引失效的情况。
解决办法：重建索引.
现象：有一条sql语句平时查询很快，突然很慢，会是什么原因。
表跟新频繁，统计信息过旧，导致索引失效.
查看表和索引的统计信息:
select * from innodb_table_stats; 表的统计信息
select * from innodb_index_stats; 索引的统计信息
不是实时跟新。
表的实时跟新解决办法:多删除点才可以跟新。
所以解决办法是，重建索引。
4.如果函数作用在索引上，或者对索引列进行运算，运算包括(+,-,* ,/,!等)
5.隐式转换导致索引失效，这一点应当引起重视
这里我测试字符串转数字不会失效，只有数字转字符串就会失效。
6.<>,not in 不走索引（辅助索引）(聚簇会走)
单独的> < in 又可能走索引，也可能不走.
7.like '%_' 百分号在前面不走索引。
如有这种大量的需求可以用mongodb数据库
扩展：优化器针对索引的算法
1mysql索引的自优化-AHI（索引页的索引,自适应hash索引）
mysql的innodb引擎，能够创建的只有btree。
ahi作用：自动评估热的内存索引page，生成一个hash索引表，加速
索引读取的速度。
2.mysql索引的自优化 -Change buffer
insert update delete 数据
对于聚簇索引会立即更新
对于辅助索引，不是实时更新
在innodb内存结构中，加入了insert buffer（会话）,现版本叫change buffer
change buffer的功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行mege 合并，此时辅助索引就是最新的。
3 ICP 索引下推
在引擎层再次根据索引进行过滤，减少磁盘的io。
例如 index(a,b,c) select * from t1 where a= and c=
在sql层只走部分索引a，到引擎层再次走索引过滤掉部分c的数据，
在进行磁盘数据读取，减少了磁盘的io.
所涉及的算法调优的参数:
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=on'
3）.hints 在语句里加入 /*+ */ 也就是oracle里面的hint
4.MRR muti range read
mrr = on mrr_cost_based = on(开启mrr需要关闭 mrr_cost_based)
set global optimizer_switch='mrr_cost_based=off';
默认的辅助索引查询，会多次回表，产生随机io。
mrr 缓存一部辅助索引的id，然后进行排序，利用rowid buffer，然后进行一次或者
少次数的回表，减少回表次数以及随机IO；提高服务器的性能。
辅助索引 回表 >> 聚簇索引
转换为
辅助索引 > sort id 回表 >聚簇索引
5.SNLJ 多表关联优化的算法
a join b on a.xx = b.y y where a.c = b.c
for each row in b {
for each row in b{
a.xx = b.xx send to client }
以上例子，可以通过left join 强制驱动表
6.BNLJ 多表关联优化算法
a join b on a.xx = b.y y where a.c = b.c
将a表满足条件的数据一次性（使用块）跟b表比对，并返回，减少了
循环的次数。提高了效率。a为驱动表.
when
7 BKA
BNLJ的基础上，也就是把a表的数据放到一个块中，然后在进行排序，然后在跟
b表进行关联，可以减少io次数，和随机io次数。优化非驱动表的关联辅助索引。
开启的方式 开启：MRR ，在开启BKA。
