孙某某的运维之路

孙富阳， 江湖人称没人称。多年互联网运维工作经验，曾负责过孙布斯大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型博客网站运维工作。

1.索引介绍

索引相当于一本书中的目录，可以加速查询（select,update,delete），但是过多的索引会导致insert变得很慢。

2.索引的种类

Btree（平衡多叉树）：b-tree b+tree（b*tree），优点：擅长范围查找
HASH：优点，适合随机的等值查询
Rtree听听这个名词就行，不必了解

3.Btree的细分

聚簇索引：主键索引
辅助索引：除了主键之外的索引
单列
联合
唯一
前缀

4.索引的管理

##索引建立之前压测看看效果：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='VWLM'" engine=innodb --number-of-queries=2000 -verbose

添加索引后压测试试
mysql> alter table test.t100w add index idx_k2(k2);

1.查询索引

mysql> desc world.city;

查看更详细的索引
mysql> show index from world.city;

PS:建立索引前如何计算索引的基数Cardinality

实际上就是统计去重后的列数
select count(distinct countrycode) from city;
distinct 是去重的固定写法 countrycode是要对那列去重
然后统计表的总行数
select count(*) from city;

2.建立索引

1.单列索引

mysql> alter table world.city add index i_name(name);

2.联合索引

mysql> alter table world.city add index i_d_p(district,population);

3.前缀索引

mysql> alter table city add index i_n(name (10));

4.唯一索引

mysql> alter table tt add unique index ui(telnum);

3.删除索引

可以先查一下索引的名字
mysql> show index from world.city;
mysql> alter table world.city drop index i_d_p;

4.彩蛋 8.0新特性不可见索引

invisible index 不可见索引（隐藏索引，不知道索引还有没有用，就先把索引隐藏起来，不让业务看到）

查询一下索引是否可见
mysql> select INDEX_NAME,IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='world' AND table_name='city';
参考网址：https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
修改为不可见
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
修改为可见
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

5.B+tree索引查找算法介绍

1.平衡

不管查找哪个数，需要查找次数理论上是相同的，对于一个三层的b树来讲，理论上查找每个值都是三次IO

2.擅长范围查找

讲究快速锁定范围。
B+tree，加入了双向指针（头尾相接），进一步增强范围查找，减少对于ROOT和NON-LEAF的访问次数。

3.构建过程

叶子：先将数据排序，生成叶子节点。
枝：保存下层叶子节点的范围（>=1 <5）+指针（）
根：保存枝节点范围+指针
叶子节点和枝节点都有双向的指针。

6.MySQL中如何应用B+TREE

1.名词解释

区（簇）：连续的64pages，默认是1M存储空间。
page页：16KB大小，MySQL中最小的IO单元。

2.IOT 组织表

数据应该安装索引结构有序（顺序）组织和存储数据。
MySQL中使用聚簇索引组织存储数据。

3.聚簇（区）索引

1.构建条件

1.如果表中有主键，主键就被作为聚簇索引
2.如果没有主键，第一个不为空的唯一键
3.什么都没有怎么办，自动生成一个6字节的隐藏列，作为聚簇索引

2.如何形成B树结构

叶子节点：聚簇索引组织表，存数据时，已经是按照ID列有序存储数据到各个连续的数据页中，原表数据存储结构就是叶子节点。
枝节点：叶子节点中ID范围+指针
根节点：枝节点ID范围+指针

3.优化了哪些查询

只能优化基于ID作为条件，索引单纯使用ID列查询，很局限

4.辅助索引

1.构建条件

需要人为的按照需求创建辅助索引

2.如何形成B树结构

叶子节点：将辅助索引的列值+ID列提取出来，按照列值进行排序，存储至叶子节点
枝节点：存储叶子节点的列值范围+指针
根节点：存储枝节点的范围+指针

3.优化了哪些查询

如果查询条件使用了name列，都会先扫描辅助索引，获得ID，再回到聚簇索引（回表），按照ID进行聚簇索引扫描，最终获取到数据行

5.联合辅助索引

1.如何形成辅助联合索引

叶子节点：所有辅助索引+ID 并排序，生成叶子节点
枝节点：存储最左列叶子节点辅助索引范围+指针
根节点：存储枝节点范围+指针

2.优化了哪些查询

查询条件中必须包含最左列条件，先通过左列条件扫描联合索引的根节点和枝节点，得到叶子节点范围，再拿右侧辅助索引过滤一次。得到更精确的ID值，理论上减少回表次数

3.结论

建立联合索引时。选择基数大（重复值少）作为最左列，
查询条件中必须要包含最左列条件

7.索引树高度

一般建议3-4层为佳，3层B树，2000w+行数据
a.数据行多会导致高度过高
表分区
定期归档：一般按照时间字段，定期归档到历史库中。pt-archiver工具
分库分表：分布式
b.索引列长度过长会导致高度过高
前缀索引
c.数据类型会导致高度过高
足够
简短
合适

8.回表问题

1.回表是什么

辅助索引扫描之后，得到ID，再回到聚簇索引查找的过程

2.回表会带来什么问题

IO：次数和量会增加。
IOPS：1000次/s,达到后会影响性能
吞吐量：300M/s,达到后会影响性能

3.怎么减少回表

建索引使用联合索引（覆盖），尽可能将查询条件中的数据包含在联合索引中。
精细化查询条件（业务方面使用>and<,limit减少查询范围）
查询条件要符合联合索引的规则，覆盖的列越多越好

9.索引自优化AHI（自适应hash索引）\change buffer

AHI:索引的索引，为内存中的热点索引页，做了一个HASH索引表，能够快速找到需要的索引页地址
change buffer：对于辅助索引的变化，不会立即更新到索引中。暂存至change buffer。

10.执行计划分析

1.执行计划是什么

优化器（算法）最终得出的，代价最低的，SQL语句的执行方案

2.为什么要分析执行计划

场景一：分析比较慢的语句。
场景二：新上线业务，可能会包含很多select update delete…,提前发现问题。

3.如何抓取执行计划

a.抓取目标
select update delete
b.方法
desc select * from world.city where countrycode='CHN';

4.如何分析执行计划

table：操作的表
type：操作 类型（全表\索引），ALL index range ref eq_ref const(system)
possible_key:有可能用的索引
key:真正要用是哪个索引
key_len:索引覆盖长度（联合索引）
rows：预估需要扫描的行数
Extra:using where using index using index condition using filesort sort using temp

5.执行计划type详述

a.ALL 全表扫描
mysql> desc select * from world.city;
mysql> explain select * from world.city where countrycode!='chn';
mysql> explain select * from world.city where countrycode like '%ch';
mysql>explain select * from world.city where countrycode not in ('chn');
以上语句在生产中尽量不要出现
b.index 全索引扫描
需要扫描整颗索引树，才能得到想要的结果。效率也比较低
mysql> explain select id ,countrycode from world.city;
c.range索引范围
是我们应用索引优化的底线，也是应用最多的
mysql> explain select id from world.city where id > 10;
mysql> explain select id from world.city where countrycode in ('chn','usa');
d.ref 辅助索引等值查询
mysql> desc select * from world.city where countrycode in ('chn') union all select * from world.city where countrycode in ('usa');
理论上ref比range好，但是实际效果差别不是太大
主要看索引列的重复值多少，基数少（重复值多）不建议将in修改为union all，加上压测结果，最终评估是否需要修改union all
e.eq_ref:非驱动表，连接条件是主键或唯一键

mysql> alter table world.city add index idx_p(population);

加完索引后可以看到type已经变成了range，是最低可接受的类型
###补充：
多表连接时，小结果集的表驱动大表，
优化会自动判断查询语句中的谁作为驱动表更合适，有可能会出现选择错误
我们可以通过left join 强制驱动表干预执行计划。
####如何判断一条语句中，谁是驱动表
#1优化器判断：使用explain或者desc
#2人为判断，统计结果行数
#3压测
f.const(system)
效率最高几乎看不到，使用主键索引做等值查找
mysql> explain select * from world.city where id =1;

6.key_len详解

a.介绍
（联合）索引覆盖长度
idx(a,b,c)--->假设a(10),b(20),c(30)
explain 10 说明只用了a列的索引，如果是60就说明3列的索引都用到了
b.如何计算索引列的key_len
key_len和每个列的最大预留长度有关.
数据类型 字符集utf8mb4 没not null
tinyint      1             1
int          4             1
char（10）   4*10          1
varchar（10）4*10+2        1
c．联合索引应用细节
idx(a,b,c)
完全覆盖：
		a= and b= and c=
		a= and c= and b= 等值顺序打乱的
		a= and b= and c范围
部分覆盖：
		a= and b= 走ab索引
		a=   走a索引
		a= and c= 走a索引
		a= and b范围 and c= 只能走到b这个索引
		a范围 and b= and c= 只能走到a这个索引
完全不覆盖
b
c
bc
cb都不走索引，因为从左到右的原则
优化案例：
idx(k1,num,k2)
mysql>desc select * from t100w where k1=’Vs’ and k2=’mnij’ and num <27779
优化方式：修改索引为idx(k1,k2,num)

7.extra详解

   Using index 正常状态，最优质的，使用了索引覆盖扫描
   Using where 使用where回表扫描数据行，说明目标表的索引没有设计好
解决办法：
a.table---》获取到出问题的表
b.看原始查询语句中的条件列
c.查询列的索引情况
d.按需优化索引

   Using index condition

Using filesort###使用了额外的排序（没有索引）
a.table---》获取到出问题的表
b.看原始查询语句中order by group by distinct
c.查询列的索引情况
d.按需优化索引
查看冗余索引
mysql> select table_schema,table_name ,redundant_index_name,redundant_index_columns from sys.schema_redundant_indexes;

   Using temp
a 条件范围是不是过大
b.having order by 额外排序
c.子查询
大几率开发需要改写语句了

PS:关于索引的优化器算法：ICP\MRR

ICP ：index condition pushdown

优化器算法：
a.查询优化器算法
select @@optimizer_switch;
修改算法（不建议修改）
set global optimizer_switch=’xxx算法（从上一个语句获取）=off’
ICP全称：index_condition_pushdown

MRR

参考官网：
https//dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html

11.索引应用规范

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

（1）必须要有主键，无关列。
（2）经常做为where条件列 order by group by join on ，distinct的条件（业务：产品功能+用户行为）
（3）最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引
（4）列值长度较长的索引列，我们建议使用前缀索引###计算基数的命令mysql> select count(distinct left(name,19)) from world.city;
（5）降低索引条目，一方面不要创建没用索引，不常使用的索引清理
（6）索引维护要避开业务繁忙期，建议用pt-osc
（7）联合索引最左原则

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

1没有查询条件，或者查询条件没有建立索引
select * from t1；
select * from t1 where id=1001 or 1=1；
2查询结果集是原表中的大部分数据，应该是15-25%以上。
查询的结果集，超过了总数行数25%，优化器觉得就没必要走索引了。
MySQL的预读功能有关。
可以通过精确查找范围，达到优化的效果limit 或者范围。
1000000 >500000 and
3索引本身失效，统计信息不真实（过旧）
索引有自我维护的能力
对于表内容变化比较频繁的情况下，有可能会出现索引失效
一般是删除重建
现象：
有一条select语句平常查询时很快，突然有一天很慢，会是什么原因
select？ ---》索引失效，统计数据不真实
Innodb_index_stats
Innodb_table_stats
Mysql>Analyze table world.city;
4查询条件使用函数在索引列上，或者对索引列进行运算，运算包括（+，-，*，/，！等）
例子：
错误的例子：select * from test where id-1=9 ###有运算
正确的写法：select * from test where id=10
5隐式至转换导致索引失效。这一点应当引起重视，也是开发中经常会犯的错误
desc select * from b where telnum=110;
desc select * from b where telnum=’110’;
因为telnum列使用的是字符串列，110会先转换成字符串在进行查找
6<>,not in 不走索引（辅助索引）
7like “%_”百分号在前面不走