索引和执行计划

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

总结：索引，叶--->枝--->根（数据页page），最左原则：联合索引以开头字母排序，如果使用%则无法排序。回表:辅助索引到聚簇索引的过程。

1.MYSQL索引介绍

1.1 索引是什么？

索引相当于一本书的目录。帮助我们快速找到需要内容的页码。

索引可以帮我们快速找到所需要行的数据页码。起到优化查询的功能。

1.2 MYSQL索引类型

Btree索引 *****

Rtree索引

HASH索引

FULLtext全文索引

GIS地理位置索引

2. B+tree 结构

2.1 介绍

遍历---->二叉树---->平衡二叉树---->Balance Tree

2.2 Btree查找算法引入

2.3 BTREE种类

B-Tree ---->B+tree(叶子节点相互联系)--->B*Tree

3.MYSQL B+TREE索引结构构建过程。

3.1 聚簇索引BTREE结构（InnoDB独有）

区 ====> 簇

构建前提:

1.建表时，指定了主键列，MYSQL InnoDB 会将主键作为聚簇索引列，比如ID not null primary key。

2.没有指定主键，会自动选择唯一键（unique）的列，作为聚簇索引。

3.以上都没有，生成隐藏聚簇索引。

作用：

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

 

3.2辅助索引BTREE结构

3.2.1 说明

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

3.2.2 作用

优化非聚簇索引列之外的查询条件的优化

3.2.3 辅助索引的BTREE的构建过程

3.2.4 辅助索引的细分

（1） 单列索引---条件只是一个

（2） 联合索引

说明：使用多列组合一个索引。

联合索引，注意最左原则。

1.查询条件中，必须要包含最左列，上面例子就是a列

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= and c=

select * from t1 where a=

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

（3）前缀索引（意思相当于目录标题不能太长，太长的话去前面几个字符串）

前缀索引是针对于，我们所选择的索引列值长度过长，会导致索引树高度增高。所以可以选择大字段的前面部分字符作为索引生成条件。

会导致索引应用时，需要读取更多的索引数据页。

MYSQL中建议索引树高度3-4层。

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

1.索引字段较长： 前缀索引

2.数据行过多 ： 分区表，归档表（pt-archive），分布式架构 （大企业）

3.数据类型 ： 选择合适的数据类型。

5.索引的管理命令

5.1 什么时候创建索引？

并不是将所有列都建立索引。不是索引列越多越好。

按照业务语句的需求创建合适的索引。

将索引建立在，经常 where group by order by join on ....的条件。

为什么不能乱建索引？

1.如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新，会阻塞很多正常的业务更新的请求。

2.索引过多，会导致优化器选择出现偏差。

5.2 管理命令

1.查询表的索引情况

mysql>desc city;

key:PRI 聚簇索引 mul：辅助索引 union：唯一索引

mysql>show index from city;

2.建立索引

分析业务语句：

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

mysql>alter table city add index idx_na(name);（单列索引）

mysql>alter table city add index idx_n_c(name,countrycode);(联合索引)

mysql>alter table city add index idx_d(district(5));(前缀索引)；

mysql>show index from city;

3.删除索引

mysql>alter table city drop index idx_na;

mysql>alter table city drop index_d;

mysql>alter table city drop index_n_c;

5.3 压力测试

1.导入100w的测试表

2.压测命