第二章 Mysql专题之索引

1、索引原理

1.1、索引数据结构

MySQL的索引是B+ Tree结构
	B+ Tree是多路查找树，其每一个节点的孩子数可以多于两个，且每一个节点处可以存储多个元素;
	B+ Tree的高度一般都是2-4这个高度,IO读写次数不多;

索引:存储引擎用于快速查找记录的一种数据结构;
	索引是物理分页，可以加快检索速度;
	叶子节点包含所有索引字段和数据;

• 为什么使用B+树

B+树和B树的最主要区别在于:非叶子节点是否存储数据
	B树:非叶子节点和叶子节点都会存储数据
	B+树:只有叶子节点才会存储数据

1) B-树的关键字、索引和记录是放在一起的， B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
2) 在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

1.2、索引的基本原理

索引用来快速寻找那些具有特定值的记录，如果没有索引，一般来说执行查询时遍历整张表
索引的原理:把无序的数据变成有序的查询
	:1、把创建了索引的列的内容进行排序
	:2、对排序结果生成了倒排表
	:3、在倒排表内容上拼上数据地址链
	:4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

1.3、索引带数据库的性能影响

好处
	:1、索引可大大提高数据的查询速度

坏处
	:1、索引需要单独占物理空间
	:2、索引会降低数据删除/删除/修改的速度，因为在执行这些写操作时，好药操作索引文件

1.2、索引类型区分

聚簇索引:即主键索引,B+ Tree的叶子节点存储了整行数据
	:所有数据只和主键放在一起
非聚簇索引:即非主键索引,B+ Tree的叶子节点存储了数据的地址
	:非主键索引只存储数据地址

2、索引类型

2.1、主键索引

索引列中的值必须是唯一的，不允许有空值

2.2、普通索引

允许索引列中重复值和空值

2.3、唯一索引

索引列中的值必须是唯一的，但是允许为空值

2.4、组合索引

1、组合索引
	在多个列上建立索引，组合索引在数据库操作期间所需的开销更小，因为建立一颗索引树，可以替代多个单一索引;
	
2、最左前缀原则
	最左优先，即查询中使用到最左边的列，那么查询就会使用到索引;
	如果从索引的第二列开始查找，索引将失效;

2.5、索引建立的原则

适合建立
	:1、经常出现在where子句中的列
	:2、经常出现在连接子句中的列
	:3、尽量扩展索引，不要新建索引
    
不适合建立
	:1、经常更新字段不合适、
	:2、数据基数较小的表

3、索引分析

3.1、explain命令

explain select * from user where id;

• 查询结果

1、type：优化索引的重要字段，判断sql性能和优化下程序重要指标；
	取值类型:执行效率与下列次序成反比
		const:通过索引一次命中，匹配一行数据
		system:表中只有一行数据
		eq_ref:唯一性所秒索引，对于每个索引键，表中只有一条记录与之匹配
		ref:非唯一性索引扫描，返回每个匹配某个值的所有
		range:只检索给定范围的行，使用一个索引来选择行,一般用于betwwen、<、>
		index:只遍历索引树
		all:表示全表扫描

2、key:当前查询使用的真正的索引
3、key_len:查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用
4、rows:sql返回结果集需要扫描读取的行数，这个扫描行数越多，说明索引设置不对

3.2、回表查询

回表:数据库根据索引找到指定记录所在行时,还需要根据主键再次到数据块中获取数据；
	:两个查找过程影响效率

• 解决方式：覆盖索引

覆盖索引:覆盖了多个列的索引
	:将要查找的列建立组合索引,之后查询时就可以直接查询。

3.3、like模糊查询

• 面试问题：mysql在使用like模糊查询，索引能不能起作用？

--mysql在使用模糊查询事，索引时可以起作用的，只有把%字符写在后面才能起作用
select * from emp where name like '%0%'          --不起作用   
select * from emp where name like '0%'           --起作用
select * from emp where name like '%0'           --不起作用

3.4、null查询

• 面试问题：如果mysql表中一列含有null指，那么包含该列的索引是否有效？

null值不管是单独使用索引，还是组合索引都是有效的;

3.5、分页查询优化

• mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。
  • 优化方式：利用表的覆盖索引来加速分页查询

#那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：
	#id字段是主键，自然就包含了默认的主键索引，利用覆盖索引，只包含id列
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20