浅谈Mysql————索引

一、索引介绍

索引在数据库中是一种特殊的数据结构，用于对某一列的特定值快速检索出它的物理位置，从而快速找出它的位置，用来加快查询数据的机制，而不用你去遍历整张表所有的记录去查找这个数据。可以通过添加索引这个标识来快速确定数据位置，找到所需要的信息，类似于一本文章，一本书籍的目录。

二、索引的使用条件

因为数据库的每一次查询是保留在硬盘上的，比如select查询每一次查询都要通过表的遍历匹配相对应的结果从而查询出对应的结果，而每一次查询硬盘IO所花费的时间大概是十万次的内存访问时间，但由于增加了索引的存在，减少了表查询次数，相当于减少了IO的次数。

所以在数据量很大的情况下，可以添加适当的索引，增加查询的效率。但是索引也是有坏处的：

众所周知，索引是一种数据结构，每次增加修改查询索引都需要占用耗费一定的空间和时间的。所以说，索引的创建就要在表设计阶段进行创建，当数据量过大时，在数据库创建索引需要发挥大量的时间和空间，也可能会进行数据库卡死的情况，删除和修改也一样。

三、索引的使用

索引的创建分为自动创建和手动创建

1.手动创建索引

show index from 表名 --查看索引

create index 索引名字 on 表名(字段名) --创建索引

drop index 索引名字 on 表名(字段名) --删除索引

create table student (
	id int primary key auto_increment,
	name varchar(50),
	age int
);
create index age_index on student(age); 
show index from student;

2.自动创建索引

主键primary key约束，唯一unique约束和外键Foreigh key约束。

因为主键和唯一约束都是要求数据不能重复，涉及到非常频繁的查询就可以引入一个索引。外键也是因为子表的数据要保证在父表中存在，也是涉及到非常频繁都查询。所以也会自动添加索引。

特别注意：自动创建的索引不能删除。

四、索引原理理解

1.二叉搜索树和哈希表

既然索引是一种数据结构，那我们事先想一想我们所说的一些数据结构哪些能加快查询的？

顺序表？链表？栈？队列？这些其实都不可以，当然这里有两种比较快的查询的方式。

那就是二叉搜索数和哈希表，二叉搜索树时间复杂度最差的时候是为一条单链表时，时间复杂度为O（n），红黑树或者AVL树时可以达到O(log n)。

当然哈希表比二叉搜索数更快，时间复杂度为O(1)。

2.限制

但是这两种比较快一点的数据结构很遗憾不能作为索引：

2.1红黑树限制

(1)对于红黑树来说，可以完成精准匹配，能完成范围查询，也能完成模糊匹配，但是模糊匹配也是有一定限制的，比如说孙%’ 和‘%孙’ ，字符串比较大小是通过第一个字符比较大小的，如果知道第一个字符就可以缩小范围去匹配第二个字符；如果第一个字符不知道，那还得老老实实的去遍历一遍。

(2)还有一个问题，就是红黑树也是一个二叉树，最多有两个子树，但数据量过大时，它的高度会很高，每遍历一遍，硬盘IO会造成很多的开销，IO访问的次数就会更多

2.2哈希表限制

对于哈希表来说，只能进行精准查询，无法做到范围查询和模糊匹配。就例如

where id = 100  --精准匹配
where id > 100 and where id < 200 --范围匹配
where name like '孙%' --模糊匹配

所以这时候引入一种新的数据结构B+树，在此之前我们先来学习B树。

3.B树

B树又称B-（后面的-是连接符的意思），

B树是一种N叉树，如下所示

可以看出，B树的特点是n个节点有n+1个区域；B树遍历方式和二叉树相同，进行查询时，从根节点开始找判断当前要查的数据，在节点的哪个区间中再决定下一步往哪走。

B树为什么比红黑树要好。因为它不是二叉树，属于多分叉树，多个分叉在一个硬盘区域内，每次读取硬盘就可以读取到多个范围内的数值然后进行对比，再进行走哪条分叉路。这样做的好处就是可以减少树的高度（分叉多了树的高度就少了），减少读取硬盘的次数。

4.B+树

而B+树就在B树的上面改动了一下：

n个结点只有n个区域，删除了一个区域合并了最后一个区间的值。即每个节点上n个值中，最后一个就相当于当前子树的最大值。

所以每个节点的最大值都会重复出现，在最后的叶子节点中都会出现整个树的全集。

最后我们再用链表的方式把叶子节点串联起来，就可以很方便的找出遍历数据以及找出每一个数据集合中的子集，所以非常适合范围查询。相比之下，B树要通过多次回溯才能找到。

在实际情况中，当一个结点区间 key 的数量较多时，一个区间的 key 分裂成更多的子树；当一个区间内 key 的数量较少时，可能会将该区间内的结点合并到父结点所在的区间。

5.B树与B+树区别

相对于B数来讲，B+树会更加稳定，因为它所有的结果集都会落在叶子节点上， 无论查询哪个元素，整体的时间开销都是差不多的。而b树搜索的结果会落在根节点或者层次更高的节点上