老男孩-标杆班级-MySQL-lesson04-索引及执行计划 补充笔记

https://www.jianshu.com/p/edd2e20aeaee

视屏 https://www.bilibili.com/video/BV157411K7sf?p=56&spm_id_from=pageDriver

1 BTree 算法

1.1 B-Tree

1.2 B+Tree

1.3 B*Tree

2 Mysql B+Tree 索引构建过程

2.1聚簇索引BTREE 结构

区 extent ====》 簇 64 pages ====》 1M

Innodb 是 聚簇类表
构建前提：
1.建表时，指定了主键列，mysql InnoDB 会将主键作为聚簇索引列，
2. 没有指定主键，自动选择唯一键(unique)的列，作为聚簇索引
3. 以上都没有，生成隐蔽聚簇索引

作用：
有了聚簇索引后，将来插入的数据行，，在同一个区内，都会按照ID值的顺序，有序在磁盘存储数据。

2.2辅助索引 BTREE结构

   2.2.1  说明
    使用普通列作为条件构建的索引
    
   2.2.2 作用
   手工创建， 优化非聚簇索引列之外的查询优化
   
   2.2.3  辅助索引的细分
    单列索引
    联合索引
    前缀索引 

    联合索引：
       1. 最左原则  idx（a,b,c ）
           1.1 查询条件中，必须包含最左列，上面例子就是 a 列
           1.2  建立联合索引时，一定要选择重复值最少的列，作为最左列
		例如：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 = 
		 select * from t1 where a = and b  =
		 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
		不覆盖： bc , b , c
	
	2.2.4 前缀索引
	 前缀索引时针对于，我们所选择的索引列值长度过长，会导致索引树高度增加。会导致索引应用时，需要读取更多的索引数据 页， 所以可以选择大字段的前面部分字段作为索引生成条件
	  mysql 中建议索引树高度  3-4 层。 （1000表）
	  
   2.2.5  B+tree 索引树高度影响因素
		1.索引字段较 长： 前缀索引
		 2. 数据行过多：   分区表， 归档表（pt-archive）, 分布式架构（大企业）
		 3. 数据类型： 选择合适的数据类型

2.3索引的管理命令

	   1.什么时候创建索引
	      按照业务语句的需求创建合适的索引。 并不是将所有列都创建索引，不是索引越多越好
		 将索引建立子在 where group by order by join on  条件
		 为什么不能乱建索引？ 
		      a.如果冗余索引过多，表的数据变化的时候，很可能会导致缩影频繁更新。
		      b. 索引过多， 会导致优化器选择出现偏差
		2. 建立索引
		       1.查询索引  desc city;
		           key: pri 聚簇索引，   mul 辅助索引    uni  唯一索引
                2. 建立索引
		             分析业务语句：
		             mysql> alter city add index  idx_na (name);
					  前缀索引：alter table city add index idx_d(district(5));
						
                3. 删除索引
		           mysql> alter table city drop idx_na;
                4. 查看索引
                   mysql>  show in100dex from city;
      3.  压力测试
           1. 导入100 万的测试表
                  source   t100w.sql
	
		    2. 压测命令

相关问题：
1. 更新数据是，会对索引有影响吗？数据的变化会是索引实时更新吗？
比如insert update delete 数据。
对于聚簇索引会立即更新
对于辅助索引，不是实时更新的
在InnoDB内存结构中，加入 insert buffer（会话），现在版本叫chage
2. 怎样知道用户访问的时候，走了我们设置的索引，遇到双11那种大量访问时， 索引设置不及时，应该如何知道用户经常访问的数据信息是那些？
双11的时候，提前把热点数据灌入 Tair集群中
slowlog 查看

2.4执行计划分析

2.4.1 什么是执行计划

   分析的是优化器最终内置的cost计算算法，最终选择后的执行计划
   cost?

2.4.2 查看执行计

   DESC SELECT * FROM student;
EXPLAIN SELECT * FROM student;

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

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

2.4.4 输出信息的介绍

2.4.4.1 table

	此次查询涉及到的表，针对一个查询中的多个表时，精确到问题表

2.4.4.2 type

全表扫描：不用任何的索引。 ALL.

例如：
mysql> select * from city;
mysql> select * from city where 1=2
mysql> select * from city where countrycode like '%ch%';
mysql> select * from city where countrycode not in('chn', 'usa');

索引扫描： index, range, ref, eq_ref, const(system)

index: 全索引扫描
mysql> desc select countrycode from city;
range： 索引范围查询： >< >= <=  in or between and
 select  * from city where countrycode in ('usa', 'chn')
 in 改为  union all
 select * from city where countcode = 'usa' union all select * from city where countrcode = 'chn'
 
 特殊情况：
  查询条件为主键时，
  desc select * from city where id != 10  range
  

ref: 辅助索引等值查询

mysql> desc select * from city countycode = 'chn'

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

A join B on a.xx = b.yy
mysql> desc select county.name city.name from city join country on city.countrycode = country.code
where city.polulation = 'CHN';

const(system): 聚簇索引等值查询

msyql> select * from city where id =10

2.2.4.3 possible_keys , key

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

2.4.4.4 key_len 联合索引覆盖长度

a. 介绍
对于联合索引index(a,b.c), 我们希望将来的查询语句，对于联合索引应用越充分越好。

例如：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 = 
			 select * from t1 where a = and b  =
			 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
			不覆盖： bc , b , c

b. key_len 的计算： idx(a,b,c)

长度指的是什么？
长度受到： 数据类型， 字符集， 影响
长度指的是， 列的最大储值字节长度

数据：
      not null             没有not null
 		tinyint  1         1+1
 		int     4            4+1
 		bigint   8         8+1

字符： utf-8 ------> 一个字符占3个字节
  			not null      没有not null
  char(10)  3*10             3*10 +1
  varchar(10) 3*10+2         3*10+2+1
utf8mb4 -----> 一个字节占 4 个字节

2.4.4.5 extra

using filesort : 表示此次查询使用到了文件排序，说明在查询中的排序操作： order by  group by distinct ..

2.5 索引的应用规范

2.5.1 建立索引的原则（DBA云维规范）

2.5.1.1 （必须的）建表时一定要有主键，一般是个无关列

2.5.1.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录

优化方案
a.如果非得使用重复值较多的列作为查询条件（例如：男 女），可以将表逻辑拆分
b. 可以将次列和其他的查询类，做联合索引

2.5.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 

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

在简书上 https://www.jianshu.com/p/8e91db776803

2.6扩展： 优化器针对索引的算法

2.6.1 MySql 索引的自优化 -AHI (自适应HASH 索引)

Mysql 的InnoDb引擎， 能够创建只有Btree
AHI作用： 自动评估“热”的内存索引page , 生成HASH 索引表,帮助InnoDB快速读取索引页，加快碎银读取的读

2.6.2 MySql索引的自优化 -change buffer

2.6.3 ICP 索引下推

2.6.4 MRR multi range read