day04-索引

索引

索引介绍

索引相当于一本书中的目录.起到优化查询(where order by  group by ....)目的.
	## 使用索引就是相当于不走全表查找，当然如果只是小表格，则无所谓，但是当数量大量的时候，就展示了索引的价值。
	## 在一张表中，是可以创建N多张表的，只要名字不重复就行，但是也会出现索引冗余的问题

索引类型

Btree    : 平衡多叉树
Rtree    ：空间树索引
Hash     ：HASH索引
Fulltext ：全文索引

## btree细分
聚簇索引
辅助索引: 单列,联合,前缀..
唯一索引

索引查看

desc world.city;
	## key值里就是索引的类型
	
PRI     ---> 主键索引(聚簇索引)
MUL     ---> 辅助索引
UNI     ---> 唯一索引    

查看一个表中关于索引的详细信息：
show index from world.city;

Table      : 表名
Key_name   : 索引名
Column_name: 列名
Cardinality :基数 ,不重复的值的个数(根据统计信息,采样获得的错略值).越大越好.
量化方法: Cardinality/总行数  建议 80% 以上 
	## 所谓不重复，就是不出现多行一值的现象。
		因为重复的值越多，在做过滤的时候，效果就会越差，需要的io数就越多，磁盘的性能就越不好。
		这里边，一般一个表的行数在4076行。

辅助索引创建，删除

1. 普通单列
2.联合索引
	idx(a,b)
	叶子节点：  
		 id+a+b ,按照a和b进行排序，生成叶子节点
	枝节点和根节点： 
		 只会包含最左列（a列）的范围+指针
    注意： 最左原则
			1. 建索引，最左列重复值少的。
			2. 查询条件中，必须包含最左列。
3. 唯一索引
	unique key 

4. 前缀索引
	idex(test(10))

## 操作；
1. 单列索引
mysql> alter table world.city add index i_pop(population);

2. 联合索引 (sql_in_index)
mysql> alter table world.city add index i_c_p(countrycode,population);
	## 使用的是同一个索引名，删除的时候，也是直接删除两个。在选择12顺序的时候，Cardinality大小
	
3. 前缀
mysql> alter table world.city add index i_name(name(10));
	## 一般是作用在你的列名很长的，但是又用不到那么多，所以就截取一半。最左原则
	
4. 主键索引
mysql> create table aa (id int);
mysql> alter table aa modify id int not null primary key auto_increment;

5. 唯一索引
mysql> alter table aa add telnum char(11);
mysql> alter table aa add unique index i_tel(telnum);

## 删除索引
1. 删除索引
mysql> alter table aa drop index i_tel;  


索引建立之前压测:
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 -uroot -p123 -verbose

添加索引之后，在进行压力测试。

Btree的查找算法：

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

2 擅长范围查找
讲究快速锁定范围. 
B+tree,加入了双向指针(头尾相接),进一步增强范围查找.减少对于ROOT和NON-LEAF的访问次数.


3 构建过程 

叶子: 先将数据排序,生成叶子节点.
枝  : 保存叶子节点的范围(>=1 <5)+指针(→)
根  : 保存枝节点范围+指针

叶子节点和枝节点都有双向指针.

MySQL 索引如何应用Btree算法

## 名词：
page:  数据页,默认16KB 
extent: 区(簇),默认是1MB,连续的64Pages
IOT   : 索引组织表.MySQL在存数据时是按照索引(聚簇索引)组织和存储.

## 聚簇索引（主键索引）
前提： 
	0. InnoDB存储引擎的表才会有聚簇索引。
	1. 有主键，主键就是聚簇索引
	2. 没有主键，选择唯一键作为聚簇索引
	3. 生成一个隐藏列(DB_ROW_ID,6字节)，作为聚簇索引
	
作用： 
	1. 聚簇（区）索引，组织表（IOT）： 所有数据在插入时，都按照ID（主键）属性，在相邻数据页上有序存储数据。
		## 区与区之间是不一定连续的，但是同一个区的数据是可以保证物理上的有序的。
	2. 加快存储数据，加快通过索引作为查找条件的查询。
	
构建过程：
	1. 叶子节点 ： 
		由于存储数据时，已经按照ID顺序在各个数据页中有序存储了，所以《原表数据》所在数据页被作为叶子节点。
	2. 内部节点（非叶子节点）： 
   		获取叶子节点ID范围+指针。
	3. 根节点： 
   		获取非叶子节点 ID范围+指针

	## 辅助索引
	构建过程： 
	1. 叶子节点构建： 
		提取索引列值+ID ，进行从小到大排序（辅助索引列值），存储到各个数据页，作为Leaf node。
	2. 非叶子节点（internel node ）
		提取下层的辅助索引列值范围+指针。
    3. 根节点： 
		提取下层节点的范围+指针。
		
对于查询的优化：
	1. 通过辅助索引列，进行条件查询，根据辅助索引BTREE快速锁定条件值对应的ID。
	2. 通过得出的ID值，回到聚簇索引继续查询到具体的数据行（回表）。
	
	## 联合索引
	1. 构建过程
			叶子节点: 获取ID+name+age ,按照name和age组合排序. 将有序的值存储到连续的数据页中.
			枝节点  : 获取叶子节点name列值范围+指针.
			根节点  : 获取枝节点 name值的范围+指针.

	2. 如何提供查询优化
		例如:
			where name = and age= 

		1. 按照name条件值,扫描根节点和枝节点,找到叶子结点.
		2. 根据叶子节点内容在做age 条件过滤,最终获得ID
		3. 回表查询,根据ID扫描聚簇索引,最终得到数据页.


	3. 联合索引的最左原则
		1. 建立联合索引时,选择基数大的作为最左列.
		2. 查询条件中必须包含索引中的最左列

彩蛋：如何判断列的基数

## 通过查询所有行，并去重复。得不重复行数。
select count(distinct num) from t100w;
select count(distinct k1) from t100w;

回表的问题

1 什么是回表查询?
从辅助索引或者联合索引扫描完之后,再回到ID聚簇索引扫描的过程，就叫做回表。

2 回表会带来什么影响?
	a. IO/增积 增多 
	IO 指标?
	IOPS?    ---> 每秒IO的次数,定值.（就是所谓的并发数。一旦超过上限，就会出现等待的状态）
	吞吐量?  ---> 300M/s 读写速度

	b. 随机IO （其实就是没有办法走顺序，比如在回表的时候，只能从头开始查找）


3 怎么减少回表?
	a. 索引覆盖 ## 辅助索引里，包含了我所有想要查询的值，我不需要回表就能直接找到
	b. 精细化查询条件+合理的联合索引
	c. 调整优化器算法.

索引树高度

一般3层B+tree,可以存储2000w左右数据.建议4层以内
1 影响因素
	数据行数多
	索引长度过长
	主键值过长

2 解决方案
	a. 分库分表(分区表,中间件),数据归档(pt-archiver)
	b. 数据类型合适简短的,前缀索引
	c. 规划简单主键.

执行计划

	## 其实就是在真正执行的前边，做一次测试。
1 explain: 
	SQL在执行时,优化器优化后,选择的cost最低的执行方案.

2 获取
	desc select * from t100w where  k1='aa';
	explain select * from t100w where  k1='aa';	
	
3 分析获取的内容
	table : sql语句操作的表.多表时有意义.

	type  : 查找类型,全表\索引

	possible_keys : 可能会用的索引 

    key   : 最终选择的索引 

	key_len : 索引覆盖长度.联合索引有意义

	rows   : 估算值,要扫描的行数

	Extra  : 额外信息
	
4.细分
	## type 输出的分析
	ALL 全表扫描
		1.没主键索引，或者没加where判断
			 desc  select * from t100w ;
			 
		2.使用没有建索引的索引列进行查询
			desc  select * from t100w  where id=10;
			
		3.做全局搜索的时候
			desc select * from t100w where k1 like '%a%';
		4.做不等于时，是需要全局扫描
			desc select * from city where countrycode not in ('CHN','USA');
			## 但如果是主键，或者唯一建的时候，是走索引的.
			
	index 全索引扫描
		1.搜索某一列内容的时候。
			desc select name from city;
			
	range 索引范围扫描（>,<,>=,<=,in,or）
		mysql> desc select * from world.city where id<10;
		mysql> desc select * from world.city where countrycode like 'CH%';
		mysql> desc select * from world.city where countrycode in ('CHN','USA');
		
	ref 辅助索引的等值查询
		desc select * from world.city where countrycode='CHN'
		union all  select * from world.city where countrycode='USA';
			## 用union all 走ref索引，和上边的range索引里走in的，是需要通过测试才知道实际生产中更便利。
			
	eq_ref 多表连接时，非驱动表的连接条件是主键或者唯一建
		## 非驱动表，就是多表中两条for循环的里边那层循环。选择非驱动表，是优化器会在多表连接中，找寻那个是唯一建或者主键的索引列作非驱动表。
		desc select * from a join b  on a.id=b.aid
		desc select a.name,b.name from city as a  join  country as b on a.countrycode=b.code where a.population<100;
		desc select a.name,b.name from city as a  left join  country as b on a.countrycode=b.code;
		
	const(system) 主键或唯一建的等值查询
		desc select  * from city where id=10;
	
	null 空
		desc select  * from city where id=1000000;
			根本就没有这一行的数据。
------------------------------------------------------------------------------
------------------------------------------------------------------------------

key_len  索引的应用长度  ## 说白了就是到底能够使用几个索引，或者是覆盖到几个索引)
	## 作用: 判断联合索引的覆盖长度。
		idx(a,b,c)
	## 计算：
		a+b+c
	## 每个索引列占用多长？ 
每个列key_len,是这个列的《最大》预留长度 。 
影响因素： 
		1. 数据类型 
		2. not null 
		3. 字符集(字符串类型)

数字类型： 
           最大长度         key_len(not null)       key_len(not null)     
int           4                 4                        4+1

tinyint       1                 1                        1+1 


字符串类型 (utf8): 
                最大长度       key_len(not null)     key_len(not null)
char(10)          30              30                    30+1 

varchar(10)       30+2            30+2                  30+2+1


字符串类型 (utf8mb4): 
                最大长度       key_len(not null)     key_len(not null)
char(10)          40              40                    40+1 

varchar(10)       40+2            40+2                  40+2+1


create table test (
id int  not null primary key auto_increment,
a int not null ,
b char(10) not null ,
c char(5) ,
d varchar(20) not null ,
e varchar(10)
)engine=innodb charset=utf8mb4;

alter table test add index idx(a,b,c,d,e);
a=4
b=10x4
c=5x4+1
d=20x4+2
e=10x4+2+1

	## 联合索引应用细节
a. 联合索引全覆盖 
idx(num,k1,k2) 

mysql> desc select * from t100w  where num=641631  and k1='At'  and k2='rsEF';
mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2='rsEF';
mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2 like 'rsE%';

b. 部分覆盖
mysql> desc select * from t100w  where num=641631  and k1='At' ;
mysql> desc select * from t100w  where  k1='At'  and num=641631 ;
mysql> desc select * from t100w  where  num=641631  and  k2 like 'rsE%';
mysql> desc select * from t100w  where num=641631  and k1 > 'AZ'  and k2='rsEF';
mysql> desc select * from t100w  where num=641631  and k1 != 'AZ'  and k2='rsEF';

c. 完全不覆盖（就是不走最左索引） 
mysql> desc select * from t100w  where k1='At'  and     k2 like 'rsE%';


d. 在多子句 必须得使用联合索引
where a   order by  b 
where a   group by  b   order by   xxx



	## Extra 额外信息
	using where  :  此次查询中有部分条件是没有走索引的。
		desc select id  from world.city where id>10; ## 这个直接走的是叶节点的范围指针
	如果出现以上信息，说明where 条件，索引设计问题或者语句有问题。但也需要压测。
	
	filesort   : 查询中出现了额外的排序
		只要涉及到去重复的，都要做排序。如果出现这样的额外信息，证明order by ,group by 并没有正常的走向索引。
		## 使用这条查询语句，发现出现filesort。相同与order by 的并没有走上索引
	desc select * from city where countrycode='USA' order by population limit 10;
			## 思路：1.当我在这个population列添加了索引，并没有生效，这是因为这条语句，是先进行了where判断筛选之后，又进行了order by 的全局排序，所以并没有走上索引。
			## 所以这个时候就需要建联合索引，来达到目录。
		alter table city add index id_inx(CountryCode,Population);
			然后在执行之后，就没有这个情况了。

8.0新特性

1.扩展：
不可见索引
	## 当你不想使用这个索引的时候，而又怕删除之后再需要。这个时候就需要将这个索引隐藏起来，让优化器查询不到即可。使用的时候在打开
	alter table t1 alter index idx_name visible;
	alter table t1 alter index idx_name invisible;
	
倒叙索引
	where a order by b ,c desc  
	idx(a,b,c desc)
	

2.索引自优化
AHI   : 自适应的hash索引
a. 自适应,根据缓冲区中索引页的热度,自动生成HASH索引表
b. 快速锁定热点索引页在内存的地址.
	a. 限制
	MySQL的InnoDB引擎，能够手工创建只有Btree。
	AHI 只有InnoDB表会有，MySQL自动维护的。

	AHI作用： 
	自动评估"热"的内存索引page,生成HASH索引表，HASH也在内存中。
	帮助InnoDB快速读取索引页。加快索引读取的效果。
	相当与索引的索引。


MySQL索引的自优化-Change buffer
## 限制： 
比如insert，update，delete 操作时会使用change buffer。
对于聚簇索引会直接更新叶子节点。
对于辅助索引，不是实时更新的。
insert into t1 (id,name,age) values(33,'d',18)

在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。
	
	## 白话文解释：
		当数据进行插入的时候，聚簇索引回直接更新叶子的节点，page页中内存够，就直接写入内存，如果内存不够，就在开辟一个page
		但是当对于辅助索引的时候，我们会先将数据放入change buffer中，这样就不会发生更新表和锁表的操作，那么当change buffer 占用到buffer pool的25%。就会将一部分冷端的数据进行更新到磁盘中。
		在加载的时候，也会将辅助索引里的和change buffer 中的数据合并加载。

优化器算法：

a. 查询优化器算法:
mysql> 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,use_invisible_indexes=off,skip_scan=on,hash_join=on 

b. 设置优化器算法: 

mysql> set global optimizer_switch='index_condition_pushdown=off';

hits方式: 
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

配置文件: 
my.cnf



优化器算法；
	1.Index Condition Pushdown（ICP）索引下推
		## 是在索引层进行操作的，目的就是为了减少回表。
		where a= and b> and c=
		SQL层做完过滤之后，只能使用ab的部分索引，就会将c列的条件进行下推到引擎层，进行再次过滤，然后排除无用的数据页。也可减少回表。
		## SQL 层语句先将ab列的值放入内存中，过滤之后，会将c列的值继续进行回磁盘，进行匹配提取，然后在拿到内存，然后最终在得到值，然后在进行回表。
		
	## 例子：
	idx(k1,num,k2)
		关闭下推索引：
	set global optimizer_switch='index_condition_pushdown=off';
	desc select * from t100w where k1='Vs'  and num<27779 and k2='mnij';
		打开下推索引：
	set global optimizer_switch='index_condition_pushdown=on';
	desc select * from t100w where k1='Vs'  and num<27779 and k2='mnij';
		关闭下推索引，修改索引排序
	set global optimizer_switch='index_condition_pushdown=on';
	desc select * from t100w where k1='Vs'and k2='mnij'  and num<27779 ;
	
	结果通过压测才能得到最终结果，到底谁的更快，但是也需要看实际的生产。
	
	
	
	2. MRR  (multi range read) 多路顺序读： 对顺序扫描有效
		在辅助索引条件查询时，先扫描辅助索引，获取id值，放入read_buffer中，有MMR进行排序之后，在进行回表查询（## 这里有一点就是，如果排序出来的是顺序，就可以收到双向指针的优化，如果是很随机的，那可能效果还不如不用的好。） 
		mrr=on  cost_base=off
		mrr=on  cost_base=on
	[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' --query=" select *  from test.t100w where k1 between 'qq' and 'rr' " engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
		
		通过压力测试，进行得结果

索引应用规范

一： 建立索引的原则（DBA运维规范）
1) 必须要有主键,业务无关列。
2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
3) 最好使用唯一值多的列作为索引列,如果索引列重复值较多,可以考虑使用联合索引
4) 列值长度较长的索引列,我们建议使用前缀索引.
mysql> select count(distinct left(name,19)) from city;
	## 通过上述语句，可以去测试，到哪一个字符，得的基数值大概保持不变
5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
	## 不要将每一列上都添加上索引，这样不仅不会有效果，反而适得其反。更有效的，如果在大批量数据录入的时候，还会出现强制退出的问题（批量的录入数据，也会导致表锁和表延迟的状态，就会出现强制退出。）
select * from schema_unused_indexes 
where object_schema not in ('sys','mysql','information_schema','performance_schema');

select *  from sys.schema_redundant_indexes 
where table_schema not in ('sys','mysql','information_schema','performance_schema')\G

(6) 索引维护要避开业务繁忙期，建议用pt-osc。 
(7) 联合索引最左原则



二： 不走索引的情况（开发规范）
1 没有查询条件，或者查询条件没有建立索引
select * from t1  ;
select * from t1 where  1=1;

作业: 
SQL审核和审计. yearning.io  github, inception


2 查询结果集是原表中的大部分数据，应该是15-25%以上。
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。

可以通过精确查找范围，达到优化的效果。
1000000
>500000 and 



3 索引本身失效，统计信息不真实（过旧）

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,统计数据不真实

mysql库中的
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 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
mysql> desc select * from b where telnum=110;
	## 不加单引号，会将数字先转换为这列的属性。然后在换成数字，然后在进行查询
mysql> desc select * from b where telnum='110';


6 <> ，not in 不走索引（辅助索引）   ******

7 like "%_" 百分号在最前面不走


------------------------------------------------------------------
------------------------------------------------------------------

5 SNLJ 普通嵌套循环连接
例子： 
A  join  B 
on   A.xx = B.yy 
where  

伪代码：
for each row in A matching range {
	block 
    for each row in B {
      A.xx = B.yy ，send to client
    }
 
}


例子：   
mysql> desc select * from teacher join course on teacher.tno=course.tno;

优化器默认优化规则： 
1. 选择驱动表
默认选择方式（非驱动表）： 
	0. 结果集小的表作为驱动表
	按照on的条件列，是否有索引，索引的类型选择。
	1. 在on条件中，优化器优先选择有索引的列为非驱动表。
	2. 如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。
	       
for each row in course matching range {
	block 
    for each row in teacher {
      course.tno = tracher.tno ，send to client
    }
 
}


关于驱动表选择的优化思路： 
理论支撑：
mysql> desc   select * from city join country on city.countrycode=country.code ;
mysql> desc   select * from city left join country on city.countrycode=country.code ;


查询语句执行代价： 
mysql> desc   format=json select * from city join country on city.countrycode=country.code ;
mysql> desc   format=json select * from city left join country on city.countrycode=country.code ;

实践检验： 
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city  join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

注： 可以通过 left join 强制驱动表。



6 BNLJ
在 A和B关联条件匹配时，不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了， CPU消耗，减少了IO次数

In EXPLAIN output, 
use of BNL for a table is signified 
when the Extra value contains Using join buffer (Block Nested Loop)


7 BKA 
主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式： 
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。

rrency=100 --iterations=1 --create-schema=‘world’ --query=“select * from city join country on city.countrycode=country.code ;” engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

注： 可以通过 left join 强制驱动表。

6 BNLJ

在 A和B关联条件匹配时，不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了， CPU消耗，减少了IO次数

In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)

7 BKA

主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式：
mysql> set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
mysql> set global optimizer_switch=‘batched_key_access=on’;
重新登陆生效。