学习mysql的第四天--之mysql索引

1.索引是什么？

索引是一种特殊的表，由数据表中的一列或多列组合而成，可以快速查询数据表中有某一特性值的记录；目的是为了优化查询

2.Mysql索引管理

（1） 建立索引前压测

> 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

这个命令是使用 mysqlslap 工具来模拟 MySQL 数据库的负载和性能测试。mysqlslap 是 MySQL 的一个命令行客户端负载模拟工具，它可以模拟多个客户端同时执行查询来测试服务器的性能。下面是命令参数的解释：
–defaults-file=/etc/my.cnf：指定 MySQL 配置文件的路径，mysqlslap 会从该文件中读取配置。
–concurrency=100：设置并发执行查询的客户端数量为 100。
–iterations=1：设置每个客户端执行测试的次数为 1 次。
–create-schema=‘test’：在测试之前创建名为 test 的数据库。
–query=“select * from test.t100w where k2=‘VWlm’”：指定要执行的查询，这里是从 test 数据库的 t100w 表中选择 k2 列值为 ‘VWlm’ 的所有记录。
engine=innodb：指定测试的存储引擎为 InnoDB。
–number-of-queries=2000：设置总共要执行的查询数量为 2000 次。
-verbose：输出详细的测试结果。

（2） 查询索引

 desc city;

PRI : 主键索引
MUL : 普通索引
UNI : 唯一索引

 show index from city;

Table 表名
Key_name 索引名
Column_name 列名
Cardinality 基数(选择度),位置值的多少
Cardinality 建立索引之前,基数如何计算的?
select count(distinct countrycode) from city;–用于计算某个列中不同值的数量。

Cardinality：基数或选择度，表示索引中预计不同值的数量。这个数字是估计值，由 MySQL 的查询优化器用来计算查询的效率。基数越大，意味着列中的重复值越少，索引的效率可能越高。

（3） 创建索引

alter table city add index i_name(name);

–这条命令在 city 表的 name 列上添加了一个名为 i_name 的普通索引。这将帮助加快针对 name 列的搜索操作。

alter table city add index i_d_p(distinct,population);

–这条命令尝试在 city 表的 population 列上添加一个索引，使用 DISTINCT 关键字。DISTINCT 关键字用于创建一个索引，该索引仅包含列中不同值的子集。这有助于减少索引的大小，提高查询效率。但请注意，并非所有数据库系统都支持 DISTINCT 索引（MySQL 8.0.13+ 支持）

alter table city add index i_x(name(10));

–这条命令在 city 表的 name 列上创建了一个前缀索引，只索引列的前10个字符

alter table t1 add unique index i_a(a);

–这条命令在名为 t1 的表的 a 列上添加了一个名为 i_a 的唯一索引。

3.索引结构类型

Btree 索引
Rtree索引
HASH索引
Fulltext全文索引
GIS 地理位置索引

（1）B Tree 结构

A. 演变过程
遍历------> 二叉树(二分法)------------> 平衡二叉树----------------- > b tree
二叉树就是二分法，它在查找数据时对于末端的数据查询次数多，不公平，此时就有了btree

B. Btree查找方法

把数据按照一定的顺序存到btree的叶子、枝、根节点上，在枝节点上存放对应2个叶子节点的范围，再根节点上存到枝节点的范围，这每次查询数据时，先去根节点找到所在枝子节点，再去找到叶子节点，这样就保证了平衡

（2） btree种类

种类：b tree ，B+Tree -----增强—>B*Tree
B tree缺点：在查找时每次都要查3次，并且有的数据页有遍历多次的情况，针对这个缺点就有了b+tree

（3） B + tree结构

B+tree是对btree的优化，B+tree所有数据都在叶子节点上 ；它是在叶子节点上添加了双向指针，叶子不仅存的是自己的数，还存储相邻2个叶子节点的数据范围；叶子节点形成了一条有序链表。如果要查询20~56.只需要找到20这个起始节点，然后顺序遍历，不再重复的访问根节点和枝节点了。

B* tree：innodb引擎它是在枝节点之间添加的双向指针

mysql索引分类：聚簇索引和辅助索引

（4） B*tree

innodb引擎它是在枝节点之间添加的双向指针

3.B+tree聚簇索引构建过程

（1）构建聚簇索引的前提:

聚簇索引 B+ tree结构（innodb独有）“簇”就是区
建表时，指定了主键列， InnoDB会将主键作为聚簇索引列，比如ID not null primary key
若建表时没有指定主键，自动选择唯一键( unique key）的列，作为聚簇索引。
若都没有约束,会自动生成隐藏聚簇索引。是自动创建的

（2）构建索引的作用:

有了聚簇索引之后，将来插入的数据行，在同一个区内，都会按照Id值的顺序，有序在磁盘存储数据，将有索引的列的数据放到btree的叶子节点上

（3）构建聚簇索引的过程

首先当我们创建表时系统会自动创建索引时，若将id列加上索引后，系统会创建3个表来存储叶子节点，枝节点和根节点；枝节点存储的是叶子节点上id列的范围和页的编码，根节点存储叶子节点范围，若查询时，就会按照加了索引的列取查询数据，先查根节点，再去枝节点，再去查叶子节点，最后将数据加载到内存中；

4.辅助索引构建过程 B tree 结构

（辅助索引包括单列,联和,前缀）

（1）定义：

使用普通列作为条件构建的索引，需要人为创建

（2）作用：

用来优化聚簇索引列以外的普通列作为查询条件时用

构建：alter table t1 add index idx(name);

（3）辅助索引的构建过程

过程：当我们alter给普通列添加赋值索引时
第一步：首先系统会申请叶子节点的空间，存储辅助索引列和聚簇索引列，如name列和id列，会对辅助索引列进行排序
第二步：还会创建一个表存储枝节点，存储的是页编号和辅助索引列范围
第三步：在创建一个表存放根节点，存储的是枝节点的页码和枝节点中辅助列的范围

（4）查询过程

当我们select来根据辅助列查询数据时，先去根节点，再去枝节点，再去叶子节点在，在叶子节点上会取到对应的聚簇索引列的数据
此时，就会拿着聚簇索引列的数据去聚簇索引的根节点查询数据，最后在聚簇索引的叶子节点上将数据加载到内存中

（5）回表

当辅助索引查询到叶子节点上时，需要根据叶子节点上的聚簇索引列数据去聚簇索引的根节点上继续查询时，就叫回表

回表问题： IO : 次数和量会增加. IOPS : 1000次/s 吞吐量 : 300M/s

减少回表：
a. 建索引使用联合索引(覆盖),尽可能多将查询条件的数据包含联合索引中.
b. 精细查询条件(业务方面,> and < ,limit)
c. 查询条件要符合联合索引规则,覆盖的列越多越好.

（6）辅助索引分类

A). 单列辅助索引
单列就是上面查询的过程，

select * from t1 where name=s;

B). 联合辅助索引

构建过程：alter  table t1 add index idx_n_g(a,b)

当我们经常使用select * from t1 where name=s and gender=1; 这个已经查询时，可以添加联合索引

语句：Alter table t1 add idx(name,gender);

创建过程：首先会创建一个叶子节点表存放聚簇索引列、name列、gender列以及页码，再创建枝节点表存放创建联合索引最左边name列为枝节点，在申请表作为根节点；注意：枝节点和根节点只存放最左列的范围，不会存储gender列；

• 作用：当我们只需要name列和gender列数据时，就可以创建一个联合索引，主要是为了减少回表次数；
• 注意事项：在查询时必须包含最左列；创建索引时一定要将重复列少的列放到最左边

假如对字段 (a, b, c) 建立联合索引

1.如下查询语句可以使用到索引：

where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx
where a like 'xxx%'
where a > xxx
where a = xxx order by b
where a = xxx and b = xxx order by c
group by a

2.如下查询条件也会使用索引：

where a = xxx and c = xxx and b = xxx

虽然b和a的顺序换了，但是mysql中的优化器会帮助我们调整顺序。

3.如下查询条件只用到联合索引的一部分：

`where a = xxx and c = xxx`   

可以用到 a 列的索引，用不到 c 列索引。

`where a like 'xxx%' and b = xxx` 

可以用到 a 列的索引，用不到 b 列的索引。

where a > xxx and b = xxx 

可以用到 a 列的索引，用不到 b 列的索引。

4.如下查询条件完全用不到索引

where b = xxx
where c = xxx
where a like '%xxx'			-- 不满足最左前缀
where d = xxx order by a	-- 出现非排序使用到的索引列 d 
where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等

C).前缀辅助索

前缀索引是针对于，我们所选择的索引列行的值长度过长，会占用页的空间，导致索引树高度增高,在索引应用时，需要读取更多的索引数据页，降低效率，所以可以选择大字段的前面部分字符作为索引生成条件。
MysQL中建议索引树高度3-4层。
注意：数字列不能用作前缀索引。

5.B+tree索引树高度影响因素

一般建议3-4层为佳,3层b树,2000w+.
索引字段较长: 前缀索引

数据行过多: 解决办法–> 分区表，归档表pt-archive，分布式架构（大企业>

数据类型: 选择合适的数据类型。

扩展：索引自优化AHI(自适应hash索引)\change buffer、

（1） MysQL索引的自优化-AHI(自适应HASH索引）
AHI : 索引的索引. 为内存中的热点索引页,做了一个HASH索引表,能够快速找到需要的索引页地址.

MysQL的InnoDB引擎，能够创建的只有Btree。
AHI作用:
自动去内存统计索引页的使用
因为它在内存中是杂乱的，所有为了我们更加方便的查看，就将这些经常使用的索引再次进行btree，就是索引的索引

（2）MysQL索引的自优化-change buffer

作用：
比如进行数据的增删改查时，聚簇索引会立即更新到磁盘，而辅助索引会先将数据保存到change buffer 中，当用户需要时直接从change buffer中拿去和 磁盘中旧的数据进行合并，这样就更新了辅助索引。
每个用户登录，都会分配一个临时的change buffer

6.索引的管理命令

（1）什么时候创建索引?

并不是将所有列都建立索引。不是索引越多越好。
按照业务语句的需求创建合适的索引。
将索引建立在，经常 where group by order by join on …查询的条件上。
大表加索引,要在业务不繁忙期间操作
尽量少在经常更新值的列上建索引

（2）为什么不能乱建索引?

如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新。会阻塞很多正常的业务更新的请求。
索引过多,会导致优化器选择出现偏差。

（3）查询表的索引情况

mysql> desc city;

表中索引位置：key
表中索引分类：PRI聚簇索引 MUL辅助索引 UNI唯一索引
查看详细索引：mysql > show index from city;
Column_name列：表示所有的列名

（4）创建索引 DDL语句

注意：创建索引一般都是创建的是辅助索引，不能是ID 、not null、 primary key、唯一键( unique key），因为这些在创建表时就会自动创建成聚簇索引
分析业务语句:

mysql> select * from city where  name='wuhan ' ;

创建语句：

mysql> alter  table  city  add  index  idx_na (name ) ;		

#将name作为索引，idx_na:是索引
创建联合索引

  Alter table city add index idx_n_c(name,country);

创建前缀索引

 `Alter table city add index idx_d(district(5));`    

就是给前5个字符创建索引

根据前缀索引查询：一般使用模糊查询

Select * from t1 where name=”abcdc%”；

创建唯一索引

alter table city add unique index idx_uni1(name);

（5）删除索引

mysql> alter table city drop index idx_na;    

（6）查询索引

Show index from 表；

Key_name列:索引名称
Column_name列:加索引的列名
查看查询语句是否走没走索引：

mysql> explain select * from city where name="wuhan";

扩展：
如何减少回表？
尽量使用id查询；设计合理的联合索引；
当数据修改后，主键索引和辅助索引的更新情况；
主键索引会立即更新

辅助索引不会立即更新，如图
当根据辅助索引插入数据后，会先将数据保存到changbuffer 中，当有查询数据在changbuffer 中的，会先将磁盘中的数据加载到内存，再将changbuffer 中的数据进行拼接，当查询线程结束后，就会一同写到磁盘中
每个用户登录，内存会自动分配一个change buffer给用户，当有大量的dml语句来时，我们可以通过调整change buffer的大小来加速数据更新。

7.执行计划分析

（1） 什么是执行计划

分析的是优化器按照内置的cost计算算法，最终选择后的执行计划。
执行计划就是一个查询语句，他查询的是按照什么索引去查询
cost ?
代价，成本。
于计算机来讲,代价是什么?
Io , CPU，MEM

（2）查看执行计划

Mysql> explain select * from wrold.city;
Mysql> desc select * from wrold.city;

（3）执行计划显示结果

Table 此次查询涉及到的表
Type 查询类型:全表扫,索引扫
possible_keys : 可能用到的索引
Key 最后选择的索引
key_len 索引覆盖长度
Rows 此次查询需要扫描的行数
Extra 额外的信息

（4）结果详解

a. Type 查询类型

索引扫描标识：index < range < ref < eq_ref < const (system)

• type= ALL全表扫描：不要任何的索引
  全表扫描：不要任何的索引 type= ALL
  分析：什么查询是all ===》 Like %ch% Not in !=

• type=Index : 全索引扫描
  需要扫描整颗索引树,才能得到想要的结果.

  desc select host from mysql.user;
  

  联合索引中,任何一个非最左列作为查询条件时:
  idx_a_b_c(a,b,c)
  SELECT * FROM t1 WHERE b
  SELECT * FROM t1 WHERE c

• type=Range：索引范围扫描 < > = >= <= like in or between and
  如：desc select * from city where id<10;
  特殊情况：查询条件为主键时,也是range
  mysql> desc select * from city where id<10;
  mysql> desc select * from city where id not in(‘12’) ;

• type=Ref ：辅助索引，等值查询
  Desc select * from city where countycode=’CHN’;

• type=eq_ref：多表连接中，非驱动表连接条件是主键或唯一键
  desc select country . name ,city. Name from city join country
  on city. countrycode =country.code where city.population=‘CHN’;

• type=const (system)：聚簇索引等值查询
  mysql> desc select* from city where id=10;

B. Possible_key :可能要走索引

C. Key ：此次查询选择的索引

D. key_len ：联合索引覆盖长度

介绍：联合索引覆盖长度
对于联合索引index（a,b,c）,我们希望将来的查询语句，对于索引应用约充分越好
例如：有a b c、 三个索引
Select * from t1 where a= and b= and c=;
Key_len =a长度+b长度+c长度
如何计算索引列长度：
长度索引受 数据类型 和 字符集影响，就是长度是按照索引列的数据类型大小定义的，还受utf8影响，且有没有not null也会影响数据长度

字段是数字：            
	数据类型	         有Not null长度      没有not  null 长度
       Tinyint         	1               1+1(单独用1个字节存储是空)
       Int          	4                4+1
       Bigint        	8                8+1

如果a字段是int，若有not null则长度为4，没有则为5

字段是字符：utf8占3个字节  utf8mb4占4个字节
数据类型              Not null        没有not  null 
Char（10）            3*10              3*10+1
Varchar（10）          3*10+2           3*10+2+1
Varchar+2:存储数据长度

E. extra:

Using filesort：表示查询需要进行额外的排序操作，但并没有使用索引。
using where 使用where回表扫描数据行,说明目标表的索引没有设计好.
优化：
a. table ----> 获取到出问题的表
b. 看原始查询语句中的where条件
c. 查询列的索引情况-----> show index from t1; d. 按需优化索引.
这样我们给where查询条件和排序的列设置一个联合索引

查看冗余索引

mysql> select table_schema,table_name , redundant_index_name , redundant_index_columns  from sys.schema_redundant_indexes;  

查询 sys.schema_redundant_indexes 视图用于识别数据库中的冗余索引。冗余索引是指在表中已经存在一个或多个索引，而这些索引包含了相同列的子集或相同列的不同组合，导致可以被其他索引替代的索引。
redundant_index_name：冗余索引的名称。
redundant_index_columns：冗余索引中包含的列的列表。

算法：优化器针对索引的算法

（1） ICP 索引下推 index Condition Pushdown

假如t1表：索引是Index（a,b,c）

select *  from t_user where name like 'L%' and age = 17;

不用索引下推的执行过程：

第一步：利用索引找出name带'L'的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
第二步：再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。
第三步：在server层判断age = 17,进行筛选，最终只留下 Lucy 用户的数据信息。

使用索引下推的执行过程：

第一步：利用索引找出name带'L'的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
第二步：在引擎层根据 age = 17 这个条件，对四条索引数据进行判断筛选，最终只留下 Lucy 用户的数据信息。
（注意：这一步不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选）
第三步：将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。
比较二者的第二步我们发现，索引下推的方式极大的减少了回表次数。

查看优化器算法：

Mysql> select @@optimizer_switch;

开启索引下推：

索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。默认情况下，索引下推处于启用状态。我们可以使用如下命令来开启或关闭。
set optimizer_switch='index_condition_pushdown=off';  -- 关闭索引下推
set optimizer_switch='index_condition_pushdown=on';  -- 开启索引下推

压测:开ICP 2000次语句压测 索引顺序不调整

mysqlslap --defaults-file=/etc/my.cnf \--concurrency=100 --iterations=1 --create-schema='test' \--query=" select * from t100w where k1='Vs'  and num<27779 and k2='mnij'" engine=innodb \--number-of-queries=2000 -uroot -p123 -verbose

如果要开启就用，加on
Set global optimizer_switch=’batched_key_access=on’;  打开
配置文件设置：
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
batched_key_access=off,

（2）MRR算法

mysql> select @@optimizer_switch;
mrr=on,mrr_cost_based=on

默认2个都是开启的，如果要应用mrr要将mrr_cost_based关闭；

mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';

目的：在辅助索引和聚簇索引之间建一个缓存区，在回表时，先按id排序放到缓存区中，一次性回表，是为了减少回表次数；前提是id能够尽量连续，如果差距很大则不能

（3）SNLJ算法 主要用于多表连接

A join b on a.xx=b.xx where
a表拿着数据去b表里逐行匹配
这样是不推荐的
如果数据过大会消耗内存，可以强制使用left join强制驱动表，减少逐行匹配

（4）BNLJ 是对SNLJ的优化

Snlj是a表中取到一个数据，然后器b中逐行匹配，底层代码就是for的嵌套循环，而bnlj是先将a表的数据全部放到join buffer缓存区中，在从缓冲区去b表中查询；主要优化内存和io

（5）BKA— batched_key_access

BNL+MRR:主要是来优化多表连接时，非驱动表是辅助索引时要回表查询时，先将顺序排好再去聚簇索引中查询数据
默认是关闭的
开启方式：先关mrr_cost_based，在开启bka
set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;

set global optimizer_switch=‘batched_key_access=on’;

重新登录生效

8.建索引原则（总结）

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列

(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)

(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引

(4) 列值长度较长的索引列,我们建议使用前缀索引

(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)

(6) 索引维护要避开业务繁忙期

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

（1）没有查询条件，或者查询条件没有建立索引

select * from tab; 全表扫描。
select * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。是没有全表扫描这种需求。

（2）查询结果集是原表中的大部分数据，应该是25％以上

查询的结果集（行），超过了总数行数25%，优化器觉得就没有必要走索引了。
解决：如果业务允许，可以使用limit控制。
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

（3） 索引本身失效，统计数据不真实

索引和表都有自我维护的能力。
对于表内容变化比较频繁的情况下，系统统计信息不准确，过旧，有可能会出现索引失效；一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? —>索引失效,， 统计数据不真实

mysql> select * from mysql.innodb_table_stats;查询系统统计表更新时间
mysql> select * from mysql.innodb_index_stats;查询系统统计表索引更新时间

Optimize table city ; 更新表

（4）查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：

错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

（5）隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

例子：
当我们定义的列telnum是字符串类型，而查询时使用数据类型查

询，虽然结果一样但是不走索引；
mysql> select * from tab where telnum=1333333; #不走
而要走索引就要按照字符串查询
mysql> select * from tab where telnum='1333333';#走索引

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

单独的>,<, in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit 、or、in 尽量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');  
改写成：EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

（7）like “%_” 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引

如果使用大量的 '%110%'模糊查询，尽量使用mongodb数据库