谈谈关于mysql索引的理解

索引

我们在学习java中用来表示数组的下标例如定义一个变量int i 这就表示一个索引,因为索引的英文单词是index,索引也可以称为是书的目录,它可以方便我们查询自己所需要的内容,通过索引我们可以快速找到自己的需求.此时引出了索引的概念,在数据库中.

关于索引的相关操作

有了索引的概念,我们就可以通过使用索引去查询我们的需求,在数据库中可以通过使用unique 和

primary key, foreign key.自动对一个表的列创建索引.当然我们也可以手动的去创建一个索引,

使用索引进行查询

当我们使用索引进行查询时,一定要确保这列有索引在进行查询,这样才会提高查询效率,如果牛头不对马尾,例如name列有索引,id列没有索引,但是你对id列进行查询,那么就跟select的效果一样,遍历整个列,时间复杂度为O(N),这个是在硬盘上的查询,时间复杂度要比在内存上复杂好几倍,所以一定要注意,在使用索引查询时.利用索引查询时可能会影响增删改的效率,可能会快也可能会慢,当然索引的利还是大于弊的.

索引的三大操作及注意事项

create index 索引名 on 表名(列);创建索引时一定要提前规划好自己的需求,对一个表中的那几列需要进行索引,可以更方便的查找,如果一个表已经创建好了,需要对有的列进行添加索引的操作,是非常危险的操作,一定要小心慎重,针对空表,数据量小的数据库进行创建索引操作,不会有太大危险,如果是数据量特别大的表,会产生大量的IO操作,直接把数据库给搞挂了.万一有一个表,没有提前创建索引,现在又有很多的数据,非加索引不可,那么我们可以重新创建一个数据库,然后将这个数据库的数据导入到新数据库中,然后切换程序访问新数据库        

drop index 索引名 on 表名.删除在数据库中不用多说,是一项特别危险的操作.所以注意事项就是要确保一般不要删除.

show index from 表名

索引背后的数据结构(B+树)

索引是特定的数据结构在硬盘中存储,索引是为了方便查询,提高查询效率,那么我们思考一下在学过的数据结构中那些数据结构可以快速查询的,那么我们可以使用B+树来作为索引的数据结构,B+树专门为数据库量身定制的数据结构,那么介绍B+树前,我们需要先了解B树,B树还可以进行优化,此时我们引出了B+树的概念,B+树也是N叉搜索树,B+树与B树的区别:B+树有N个key,划分出N个区间,例如8,15一个区间为小8的,另一个区间为8-15之间,15是这个范围的最大值,而小于8的这个区间会直接把8落下来作为该区间的最大值,8-15这个区间同理,15为最大值.并且父节点的key的值,会在下面子节点再次出现(以子结点的最大值出现),按照上述方法排序,叶子节点就构成了整个树数据的全集,B+树把叶子结点像链表一样首尾相连(类似于双向链表),此时进行"范围查询"就会特别方便

优势B+树 

1.N叉搜索树,高度比较低,此时硬盘IO次数比较少.

2.叶子结点是全集,并且用链表结构链接,非常适合范围查询.

3.B+树,所有的查询都要落到叶子结点,任何一次查询经历的IO次数和比较次数差不多,稳定.如果是B树,可能会一下找到也可能寻找多次.不稳定.

4.由于B+树,叶子结点是全集,非叶子结点不需要存储数据行,只需要存储索引列的key,使得非叶子结点消耗空间少

数据库中的表只是一个逻辑上的结构.在物理上并非一个真正意义的表,在物理意义上就是通过B+树来组织的,表中的每一行都是挂在B+树的叶子结点的,非叶子结点只需存储一个key的值,可以把这些非叶子结点的key的值存放在内存中,可以加快查询速度,减少硬盘IO次数

B树

B树是一个N叉搜索树,他的一个结点保存了N个key,就能延伸出N+1个子树,但是我们使用索引是为了方便查找,在指定范围内进行查找,每个结点可以有多个子树(树的度为N),这样就可以降低树的高度,每个结点不是只存一个key了,可以存多个key,会使树的高度降低了,每个结点的比较次数变多了,和每个结点进行比较时,先进行一次硬盘IO, 把数据读取出来,然后在内存上进行比较.目的是为了减少硬盘IO的次数(核心问题)

为什么其他的数据结构不能作为数据库索引的数据结构呢?

其中二叉搜索树和哈希表,是可以查询的.二次搜索树的时间复杂度为O(N),哈希表的时间复杂度近似O(1),但是二叉搜索树和哈希表都不适合给数据库做索引,因为二叉搜索树最大的问题就在于:储存的元素特别多,树的高度就会变得很高,那么比较的次数就会变多,如果我们是在内存中多比较两次无伤大雅,但是如果在硬盘上多进行两次比较那么是特别伤的,会进行多次IO操作,体现不出高效,而哈希表只能查询相等的情况,不能进行< > 的比较,也不可以like模糊查询,因为哈希表是通过哈希函数,将查询的key映射成数组下标的,没有等价关系key1 < key2 =>不能推出 hash(key1) < hash(key2)