51-143=>88
1.BTree种类：
1.1 B-Tree
1.2 B+Tree
1.3 B*Tree
2.B+Tree构建：
2.1聚簇索引B+Tree结构（InnoDB独有）
区----簇----64个pages----1MB
作用：
有了聚簇索引之后，将来插入的数据行，在同一个区内，都会按照ID值的顺序，有序在磁盘存储数据。
MySQL InnoDB表------聚簇索引组织存储数据表
构建前提：
1.建表时，指定了主键列，MYSQL InnoDB会将主键作为聚簇索引列
2.没有主键，自动选择唯一键的列，作为聚簇索引
3.以上都没有，生成隐藏聚簇索引
2.2辅助索引
使用普通列作为条件构建的索引，需要人为创建。
单独提取辅助索引列的所有值和主键列的所有值
最后需要进行一个回表的操作（目标得到的信息在辅助索引表里不全）
作用：优化非聚簇索引列之外的查询条件的优化
辅助索引的类型：
（1）单列索引
（2）联合索引
最左原则，多列构建同一个索引，idx(a,b,c)
1.查询条件中，必须要包含最左列，上面的列就是a列
2.建立联合索引时，一定要选择重复值少的列，作为最左列
全部覆盖：
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，不然不能用联合索引)
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=
不覆盖：
bc
b
c
（3）前缀索引
是针对于所选择的索引列值长度过长，会导致索引树高度增高
可以选择大字段的前面部分字符作为索引生成条件
导致索引应用时，需要读取更多的索引数据页
MYSQL索引树高度3-4层
B+Tree索引树高度影响因素：
1.索引字段较长： 前缀索引
2.数据行过多 ：分区表-----归档表（pt-archive、pt-osc）-----分布式架构
3.数据类型：可变字符长度类型，enumber自己内部有优化
索引的管理命令
什么时候创建索引？
按照业务需求建立合适的索引，并不是所有的列都建立索引，不是索引越多越好
将索引建立在经常做where group by order by join on的条件上
为什么不能乱建索引？
1.表内元素更新的时候，会带动索引频繁更新，会阻塞很多正常的业务更新需求
2.索引过多，会导致优化器选择出现偏差
管理命令：
1.查询表的索引情况：
desc city
PRI聚簇索引 MUL辅助索引 UNI唯一索引
show index from city
2.建立索引
分析业务语句：
select * from city where name='wuhan'
所以选择建立索引为：
alter table city add index idx_na(name);
3.删除索引
alter table city drop index idx_na;
创建完成后，查询的时候自动使用
也可以用explain+语句，显示执行流程，找到是否用到ref ，就是索引
压力测试
1.导入100w数据表：
source t100w.sql
2.压力测试命令：
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-querie=200 -uroot -p123 -verbose
3.建立合适索引
use test
alter table t100w add index idx_k2(k2)
联合索引创建
alter table city add index idx_n_c(name,country)
前缀索引创建
alter table city add index idex_d(district(5)) 匹配前5个字符，筛选索引，前缀索引的作用是降低B+Tree树的高度，特别是对于大字段的数据
执行计划分析
1.什么是执行计划？
select * from t1 where name='zs';
分析的是优化器按照内置的cost计算算法，最终选择后的执行计划。
cost?
IO,CPU,MEM
2.查看执行计划
explain select * from world.city;
desc select * from world.city;
3.执行计划显示结果分析
table 查询涉及到的表，多表查询的时候要看
type 查询类型：全表扫，索引扫（判断索引是不是合理）
possble_keys 可能用到的索引
key 最后选择的索引
key_len 索引的覆盖长度（评估联合索引的时候用）
rows 此次查询需要扫描的行数
Extra 额外的信息
##4.输出信息介绍：
table 查询涉及到的表，多表查询的时候要看
desc select country.name,city.name
from city join country
on city.countrycode=country.code
where city.population='CHN'
type 查询类型：全表扫，索引扫（判断索引是不是合理）
全表扫描：不用任何的索引。ALL
生产过程中尽量不要使用
desc select * from city;
desc select * from city where 1=1;
desc select * from city where countrycode like '%ch%';
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:索引范围查询：><=<=>
desc select * from city where id<10
desc select * from city where countrycode like 'CH%'
like语句之后的左侧必须是确认值，不能是百分号
desc select * from city where countrycode in ('chn','usa');
--------------->改写为 union all
改写之后会变成两条过程显示，而且索引等级提升至ref
select * from city where countrycode='chn'
union all
select * from city where countrycode='usa';
特殊情况：查询条件为主键时：
使用不等于的表示，是走range的
desc select * from city where id!=10;
desc select * from city where id not in (10,20);
ref:辅助索引的等值查询，就是查询语句里面是=
select * from city where countrycode='chn'
eq_ref:针对多表链接中，非驱动表链接条件是主键或唯一键
A join B on A.xx=B.yy
const(system):聚簇索引等值查询
select * from city on id=10;
possble_keys 可能用到的索引,所有和此次查询有关的索引
key 最后选择的索引
key_len 索引的覆盖长度（评估联合索引的时候用）
（1）介绍：
对于联合索引index(a,b,c)，希望将来的查询对联合索引用的越充分越好。
key_len可以帮助我判断，此次查询，走了联合索引几及部分
全部覆盖：
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，不然不能用联合索引)
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=
不覆盖：
bc
b
c
（2）key_len 计算 idx(a,b,c)
假设某条可以完全覆盖三列联合索引，例如：
select * from t1 where a= and b= and c=
key_len=a长度+b长度+c长度
长度指的是什么？
长度受到：数据类型，字符集影响
长度--------列的最大储值字节长度
数字：
not null 没有not null(会增加一个单独的位置记录是否非空)
inyint 1 1+1
int 4 4+1
bigint 8 8+1
key_len
a int not null ---------->4
a int ---------->5
字符：
utf8 1个字符最大占3个字节
not null 没有not null
char(10) 310 310+1
verchar(10) 310+2 310+2+1
占用1-2个字节储存字符长度
create table t1(
a int not null,
b int,
c char(10) not null,
d varchar(10)
)charset=utf8mb4
index(a,b,c,d)
如果查询完全覆盖到4列索引，key_len是多少？
4
5=4+1
40=410
43=410+2+1
extra
using filesort:表示此次查询使用到了 文件排序，说明在查询中的排序操作：
order by
group by
distinct
show index from city
alter table city add index idx_c_p(country,population)
索引应用规范：
建表的时候主键可以用无关列，数字列，值小一点
选择唯一性比较好的列，更快速的通过索引来确定某条记录
联合索引：最左原则
优化方案：
1.如果非要使用重复值较多的列作为查询条件，可以将逻辑表拆分
2.可以将此列和其他的查询类，做联合索引
限制索引数目：
产生问题：
1.占空间
2.改条目，索引越多，影响越大
3.优化器负担重
percona-toolkit中有工具专门分析索引是否有用
优化器到15-30%就直接上全局扫描了
数据库的预读能力有关，减少IO参数控制
表比较小，就可以上到50%
分批次读取，不要全局扫描
索引，报表都有自我维护的能力
内容变化频繁，统计信息不准确，过旧。有可能会出现索引失效
一般是删除重建
现象：
有一条select语句平常查询的很快，突然有一天就变慢了。
不是实时更新表，但是数据已经改变，导致统计信息不准确
innodb_index_stats
innodb_index_stats
optimize table city;可以暂时缓解这个问题
不走索引
1.出现隐式转换(你是原来的数据设定为字符型，但是输入整型，会自动转化为字符型，之后用整型查询是不走索引的)
2.not in
3.< > in or
or in可以改写成union all可能会提升性能
expalain select * from telab where telnum in ('119','110');
改写成为
explain select * from teltab where telnum='110'
union all
select * from teltab where telnum='119'
4.like'%_'百分号在前面不走索引
%z%类的搜索需求，可以使用elasticearch 或者mongodb专门做搜索服务的数据库产品
优化器针对索引的算法
1.MySQL索引的自优化-AHI（自适应哈希索引）：
自动统计索引页的使用情况，自动评估热的内存索引page，
生成hash索引表，帮助innodb快速读取索引页。（索引的索引）
MySQL的innodb引擎只能设置btree格式
2.MySQL索引的自优化-Change buffer
对于insert ,update,delete数据，对于聚簇索引会立即更新。
对于辅助索引，不是实时更新的，在innodb内存结构中，加入 insert buffer，现在版本叫Change buffer
功能：
临时缓冲辅助索引需要的数据更新
当我们需要查询新的inset数据时候，会在内存中进行merge合并操作，此时辅助素银就是最新的
3.ICP ----- 索引下推到引擎层
没有ICP的情况下：
有ICP的情况下：
index(a,b,c)
select * from t1 where a= and c=
a=在server层做的筛选
c=在引擎层做的筛选（ICP下推到引擎层）
不然就要先过a条件再全盘过c条件
4.MRR
5.SNIJ
6.BNIJ
7.BKA(优秀的针对多表链接的优化方法)
如何修改？？？
select @@optimizaer_switch
1.my.cnf
2.set global optimizer_switch='batched_key_access=off';
京公网安备 11010502036488号