mysql_4_2执行计划分析
标签（空格分隔）： mysql
什么是执行计划
select * from t1 where name=“asd”
对上面的语句执行解析，解析完了，判断使用什么方法去获取我们所需要的数据。
分析的是优化器按照内置的代价计算模型。最终确认的执行计划。
cost 代价、成本
对于计算机来讲，代价是什么》
IO、CPU、MEM
执行计划的查看
explain sql语句
desc sql语句
执行计划显示结果的分析
table 此次查询涉及的表
type 查询类型 ref 索引扫描 all 全表扫 range 链表索引 eq_ref
possible_keys 可能会用到的索引
key 最后选择的索引
key_len 索引的覆盖长度
rows 此次查询需要扫描的行数
Extra 额外的信息
查询优化器 可以帮忙优化sql 查看那个索引比较好
但是有的索引查找还不如全表扫描
type 查询类型
全表扫描 不走任何的索引 ALL
desc select * from city
desc select * from city where 1=1;
desc select * from countcode like '%aa%'
desc select * from city where countrycode not in ('chn','usa')
索引扫描
效率对比
index < range < ref < eq_ref < const(system)
index 全索引扫描
扫描整个索引树
desc select countrycode from world.city;
range 索引范围扫描
> < >= <= like in or between and
desc select * from city where id < 10;
desc select * from city where countrycode like 'ch%'
desc select * from city where countrycode in ('chn','usa');
改写为 union all
select * from city where countrycode='chn'
union all
select * from city where countrycpde='usa'
特殊情况: 主键in 和 not in
desc select * from city where id !=10 range
desc select * from city where id not in (10,20); range
ref 辅助索引等值查询
desc select * from city where countrycode ='chn'
eq_ref : 多表连接中 非驱动表连接条件三主键或唯一键
desc select country.name，city.name from city join country on city.countrycode = country.code where city.population=‘chn’
const(system)
聚集索引等值查询
desc select * city where id = 10;
possible_keys
possibles_keys 所有可能走的索引，所有和此次查询有关的索引。
key
key 此次查询选择的索引。
key_len 和联合索引覆盖长度有关的。
对于联合索引index（a,b,c），我们希望将来的查询语句，对于联合索引应用越充分越好。
key_len 可以帮助我们 判断，此次查询，走了联合索引的几部分
idx（a,b,c） -------> a ab abc
全部覆盖：
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 =
select * from t1 where a and b order by c
部分覆盖
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 * from t1 where a order by b
不覆盖
bc
b
c
key_len
key_len的计算 idx（a,b,c）
假设 某条查询可以完全覆盖三列联合索引，列如：
select * from t1 where a= and b= and c=
key_len = a长度? + b 长度？ + c长度？
长度值的是什么？
长度受到：数据类型,字符集 影响
长度指的是：列的最大储值字节长度
数字
tinyint 1 1+1
int 4 4+1
bigint 8 8+1
受not null 影响
key_len :
a int not null ----> 4
a int ----> 5
字符
受字符集影响 utf-8 一个字符占3个字节
utf8mb4 一个字符占4个字节
not null null
char（10） 3*10 3*10 + 1
varchar(10) 3*10 +2 3*10 + 1 +2
1 表示是否为null
2 表示空的是那些字段 更前面的信息对应
create table t1 (
a int not null, 4
b int , 5
c char(10) not null 40
d var(10) 40 + 2 + 1
)charset=utfmb4
key_len 92
extra
useing filesoft 表示此次查询使用到了 文件排序，说明在查询中的排序操作： order by group by distinct
use filesort --------》 联合索引
desc select * from city where country="" order by population
索引应用规范
为了提高索引的效率。在创建索引时，必须考虑在那些字段上创建索引和创建什么类型的索引
1.建表 需要一定要有主键，一般是个无关列
2.选择唯一性的索引
唯一性索引的值是唯一的，可以更快速的通过该索引引来确定某条记录。
3.优化方案
1.如果非得使用重复值较多的列作为查询条件 列入 男、女 可以将表逻辑拆分
2.可以将此列和其他的查询类，做联合索引
select count(*) from world.city
select count(distinct countrycode) from world.city
select count(distinct countrycode,population) from world.city;
为经常需要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
如果为其建立索引，优化查询
如果经常作为条件的列，重复值较多，可以创建联合索引。
尽量使用前缀来索引
如果前缀字段很长，最好使用值的前缀来索引。
限制索引的条目
索引的条目不是越多越好
可能产生的问题
1.每个索引都需要占用硬盘空间，索引越来越多，需要的磁盘空间就大。
2.修改表时，对于索引的重构和更新很麻烦，越多的索引，会使更新表变得浪费时间。
3.优化器的负担重 ，有可能影响优化器的选择。
percona-toolkit有个工具，专门分析索引是否有用
删除不再使用或者很少使用的索引
pt-duplicate-key-checker
表中的数据大量更新，或则数据的使用方法改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响。
大表加索引 要在业务不繁忙期间操作
尽量少在经常更新值的列上建索引
建索引原则
1.必须要有主键
2.经常作where条件列 order by group by join on distinct 的条件
3.最好使用唯一值多个列作为索引，如果索引列重复值多，考虑联合索引
4.列值长度较长的索引列，我们建议使用前缀索引。
5.降低索引条目，一方面不要创建没用的索引，不常使用的索引清理，percona toolkit
6.索引维护避开业务繁忙期间
不走索引的情况
没有索引条件，或则查询条件没有建立索引
select * from tab
select * from tab where 1=1;
在业务数据库中，特别是数据量特别大的表。
时没有全表扫描这种需求的
1.对用户查看是非常痛苦的。
2.对服务器来将是毁灭性的。
select * from tab；
sql改写成以下语句
select * from tab order by price limit 10; 需要在price列上建立索引
select * from tab where name ="zhangsan"
修改
1.换成有索引的列作为查询条件
2.将name列建立索引
查询结果集三原表中的大部分数据 应该是15-30%
查询的结果集，超过了总行数25%，优化器觉得就没有必要走索引了。与数据库的预读能力有关，以及一些参数有关。
列如 tab表 id.name id:1-100w id列有 辅助索引
select * from tab where id > 50000
如果业务允许，可以使用limit控制。
怎么改写？
结构业务判断，有没有更好的方式，如果没有更好的改写方案 尽量不要再mysql存放这个数据了，放进redis里面
索引本身失效 统计数据不真实
索引有自我维护能力 对于表内容变化比较频繁的情况下，统计信息不准确，过旧，有可能会出现索引失效。
innodb_index_stats innodb_table_stats mysql 库 不是实时更新的
一般是删除重建
现象
有一条select语句平时查询很快，突然有一天变满，什么原因
查询条件使用函数在索引列上，或则对索引列进行运算，运算包括+ - * / ! 等
列子：
错误的 select * from test where id-1 =9
正确的 select * from test where id =10
算术运算
函数运算
子查询
隐式转换导致索引失效，这一点应当重视，也是开发中经常会犯的错误。
查询的类型 和 实际的类型不一样 就会导致 隐式转换失败。
单独的> < in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或者in 尽量改成union 或则 union all
百分号 like "%_"在最前面的不走
EXPLAIN select * from teltab where telnum like '31%' 走range索引扫描
EXPLAIN select * from teltab where telnum like '%110' 不走索引
如果有%linux%类似的搜索需求，可以使用elasticsearch或则mongodb专门做搜索服务的数据库产品
扩展：优化器针对索引的算法
AHI
mysql索引的自优化-AHI 自适应HASH索引 HASH只能在mem引擎中才能被创建出来。
mysql的innodb引擎，能够创建的只有Btree
AHI作用：自动评估热度内存索引页，生成一个hash表，帮助Innodb快速读取索引页，加速索引读取的速度。相当于索引的索引
mysql索引的自优化-change buffer
比如insert，update，delete数据
对于聚集索引会立即更新。
对于辅助索引，不会实施更新。
在Innodb 内存结构中，加入了insert buffer 会话 ， 现在版本较change buffer。
change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert的数据 会在内存中进行merge合并操作，此时辅助索引就算最新的。
ICP 索引下推
INDEX(A,B,C)
SELECT * FROM T1 WHERE A= AND C=
sql层 判断 只能走A的 5.6之前 执行引擎就会照着优化器去找 但是有了ICP 将C列的过滤下推到引擎层 他会先把满足C的条件拿出来 在进行A判断 进行返回 减少IO
select 查询语句在sql层进行解析后，由优化器选择好方案，在进入引擎层后，由引擎层拿数据前进行过滤，过滤好再访问磁盘的ibd文件
算法切换
show variables like "%switch%"
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off |
ICP index_condition_pushdown
索引下推
解决了 联合索引只能部分应用情况
为了使减少没必要的数据页被扫描
将不走索引的条件，在engine层取数据之前先做c二次过滤
一些无关数据就会被提前过滤掉
INDEX(A,B,C)
SELECT * FROM T1 WHERE A= AND C=
BKA 跨表优化
1.如果修改？
1.my.cnf
2.set global optimizer_switch='index_condition_pushdown=on'
3.hints
MRR
MRR muti range read
经可能的减少随即io 减少回表
每拿到id相同的 都要会表查询
用非主键索引时 读多项索引 就会回多次表 不是一次取出来回表
mrr 把所有的索引先获取 写入缓冲区 然后在统一回表 建立连接与聚簇索引
ROWID BUFFER 需要排序 整理
辅助索引和聚集索引 回表的时候 拿到一个就回表一次 现在 转换为了 辅助索引 先sort id 再回表 --》 在到聚集索引
MRR_COST_BASED=off
SNLJ
SIMPLE NAST LOOP JOIN 简单循环
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 强制驱动表
BNLJ
块嵌套循环
将满足的行放进join buffer 里 在进行连接 减少循环的次数 不用像SNLJ一样一次一次的循环
默认开启 大部分的join都是走的BNLJ
主要优化了 cpu的消耗 减少了IO次数
BKA
依赖于mrr
把驱动关联的数据先拿过来， 再排序 排序之后在与另一张表进行关联
主要作用 是用来优化非驱动表关联列有辅助索引的。
BNL+MRR 的功能
开启方式
mrr = on
mrr_cost_based = off
batched_key_access=on
Innodb 行格式 1
dynamic
变长字段长度列表 NULL标志位 记录头信息 列1 数据 列2 数据
2个字节 1个字节
变长字段长度列表 记录了对于字段的真实长度 需要2个字节最大表示65535
所以一行 不能超过65535 对于varchar 65532为最大
一个页只能存16k 65535 查找需要跨页
每一行数据的格式类似于
11111
2&&&2 &代表空 举个例子
2e 使用NULL标志位10001 第一个和最后一个有值 更前面的信息对应 就可以还原
transaction_id 6个字节 事务ID 必要
roll_pointer 7个字节 回滚指针 必要
row_id 6个字节 行id 非必要
树的层数与数量
16 kb * 1024 = 16384
主键 bigint 8
索引 占6个字节
16384 / (6+8) = 1170
每个页可以存放1170 个 键值对 指针和页号
假设1行数据是1kb 16kb 可以存放16行数据
b+ 树 高度为2
1170 * 16 = 18720
b+ 树 高度为3
1170 * 1170 * 16 = 21,902,400
b+ 树 高度为4
1170 * 1170 * 1170 * 16 = 6,406,452,000