索引及执行计划

索引及执行计划

索引

	1.索引就像一本书的目录，它能够让你更快的找到自己想要的内容
	2.索引让获取的数据更有目的性，从而提高数据库检索数据的性能
# 给指定的字段排序，添加索引，但是索引并不是越多越好，也不是每个字段都必须加索引

索引的类型

1.BTREE
	B+树索引
2.HASH
	HASH索引
3.FULLTEXT
	全文索引
4.RTREE
	R树索引

Btree算法

三路Btree

根节点
支节点
叶子节点

• Btree

• B+tree

# B+tree和Btree的区别
1.B+tree优化了范围查询
2.B+tree在叶子节点上添加了相邻的指针

• B*tree

索引管理

	索引建立在表的列(字段)上，在where后面的列建立索引才会加快查询速度

索引分类

主键索引
唯一键索引
普通索引
	前缀索引
	联合索引

创建索引

# 创建普通索引
	alter table 表名 add index 索引名(字段);
	alter table stu add index idx_name(name);
	
# 查看索引
	show index from 表名;
	desc 表名;
# 索引类型
	PRI：主键索引
	UNI：唯一键索引
	MUL：普通索引
# 删除索引
	alter table 表名 drop index 索引名;
	alter table stu drop index idx_name;

# 添加主键索引
	altre table 表名 add primary key(字段);
	alter table stu add primary key(name);
# 删除主键索引
	alter table 表名 drop primary key;
	
# 添加唯一键索引
	alter table 表名 add unique key 索引名(字段);
	alter table stu add unique key uni_name(name);
	## 添加唯一键要求：该字段的数据不能有重复名
# 删除唯一键索引
	alter table 表名 drop index 索引名
	alter table stu drop index uni_name;
# 判断是否可以在该字段上创建唯一键
1.先统计该字段总共有多少行
select count(name) from stu;
2.再统计，去重后，该字段有多少行
select count(distinct(name)) from stu;
3.查看两个数据的结果是不是一样的，如果是一样的则可以创建唯一键索引，如果两个数值不一样，则无法创建唯一键索引

前缀索引(给某一字段数据内容特别长的列，创建前缀索引)

# 普通前缀索引的创建
	alter table 表名 add index 索引名(字段(数字))
	alter table stu add index idx_name(name(3));
# 唯一键索引前缀索引创建
	alter table 表名 add unique key 索引名(字段(数字))

注：	
1.避免对大列(数据长的列)建索引
2.如果要建，那么用前缀索引

联合索引(将多个字段，做成一个索引)

# 联合索引的查询顺序要和创建是的顺序一致，才可以提高效率

# 普通联合索引创建
	alter table 表名 add index 索引名(字段1,字段2...)
	alter table stu add index idx_all(id,name);
	
# 主键联合索引创建
	alter table 表名 add primary key (字段1，字段2...)
	alter table stu add primary key (id,name);
	
# 唯一键联合查询
	alter table 表名 add unique key 索引名(字段1,字段2...)
	alter table stu add unique key unq_all(id,name);
	
## 索引无法直接修改，删除索引后重新创建

索引效率查询

## explain(执行计划)：检测一个SQL语句的执行效率，将explain加在要执行的sql语句之前即可，desc同explain。查看SQL语句的执行效率主要看type列
例：
	explain select * from stu;
效率：
	全表扫描
	索引扫描

全表扫描

ALL：最好不要出现ALL除非
	1.业务确实要获取所有的数据
	2.不走索引导致的全表扫描
		没索引
		索引创建有问题
		sql语句有问题
		索引损坏

索引扫描

index：全索引扫描，将创建索引的列的全部数剧都查询出来
例：
	explain select id from stu;

range：范围查询，一般来说，一条SQL语句，只要达到该级别即可
例：
	explain select * from stu where id>2;

ref：唯一键索引的前缀扫描或者非唯一索引扫描(精确查询)
例
	explain select * from stu where id=1;
eq_ref：连表查询，传统连接,join on
例：
	explain select stu.name,stude.id from stu join stude on stu.id=stude.id and stu.id='4';
	
const、system：主键精确查询
例：
	explain select * from stu where name='jd';

null：不进行表的扫描，一个不存在的条件

索引建立的原则

	索引是为了提升查询的效率，索引的创建不是每一个字段都要创建，也不是越多越好，要根据用户的喜好来创建，被查询的越多的字段，才要创建索引

1.索引首要选唯一键索引
	判断是否可以创建唯一键索引
	select count(需要创建唯一键索引的字段) from 表名;
	select count(distinct(需要创建唯一键索引的字段)) from 表名;
2.其次，如果重复值较多，无法创建唯一键索引，就创建联合索引
	经常需要用order by、croup by、distinct和union等操作的字段，排序操作会浪费很多时间，就为它建立索引，可以有效的避免排序操作
3.为经常作为查询条件的字段，创建普通索引
4.尽量能使用前缀索引的就用前缀索引
	减少创建索引的排序时间
	增加查询的效率
5.索引的数量不是越多越好
	索引的数量不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦，越多的索引会使更新表变的很慢
6.删除不再使用或者很少使用的索引
	表中的数据被大量的更新或者数据的使用方式被改变后，原有的一些索引可能不再被需要，此时，应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

不走索引的原因及优化方案

1.全表查询，使用select * 查询没有加条件
	在后面加上where 条件来约束

2.查询结果集是原表中的大部分数据，超过25%以上
	优化方案
	使用limit优化
	explain select * from world.city where countrycode <>'CHN' limit 10;

3.条件本身做运算
	explain select * from world.city where id-1=9;
	优化方案
	不能使用字段来做计算
	explain select * from world.city where id=9-1;

4.隐式转换导致索引失效
	优化方案
	1.查看字段的创建数据类型
	2.在查询的时候，一定要按照字段的数据类型查询
	3.如果是字符串，就加上引号
	4.如果是整型，就不加引号

5.不等于或者not in
	使用不等于或者not in 会影响到结果集
	优化方案
	使用limit优化

6.使用like模糊查询%在前面
	explain select * from world.city where countrycode like 'HN%';
	优化方案
	尽量不要使用%号在前面的SQL语句，尽量将%放在后面
	如果非要用，那就使用搜索引擎式数据库：elasticsearch

7.使用联合索引查询数据
	不走索引时，要根据联合索引创建的顺序来查询

8.索引损坏或失效
	删了重新创建