索引
什么是索引? 在我们日常学习的过程中离不开索引:寻找房间,寻找书的页数,寻人(依靠身份证号)等等关于索引的一切
在MySQL数据库中也有关于索引的引入:用来提高查询的速度
但其也有缺点:
1.目录本身要占据存储空间(硬盘)
2.索引能提高查询速度,可能会拖慢增删改的速度,在后续对数据进行增删改时,都是要同步更新索引的
索引的使用:通过SQL能够创建索引,查看索引,删除索引
1.查看索引
show index from 表名;
由于并没有创建索引,所以是空集合
在创建拥有主键字段的表时,就会创建索引
create table t (id int(10) primary key);
show index from t;
在unique和foreign key也都是自带索引的
外键约束,父表与子表之间相互约束,进行增删改数据操作时就会拥有索引,同时两表的索引就会修改
2.创建索引
例如,在开发中发现,经常会根据学生名字进行查询,就可以针对名字这一列创建索引,创建索引,都是根据具体的列来创建的,针对某一列创建索引之后,后续查询的时候,也必须得针对这一列条件查询,才能够通过索引来提速
如果给A列创建索引,实际上通过B列指定条件查询,索引无效的;字典,有拼音目录,也有笔画目录,也有部首目录,如果字典有残缺,只有拼音目录,拿着笔画,就差不了;一个表有很多列,每个列都允许分别创建索引
create index index_student_id on student(id);
//从此与id相关(外键)的表也相当于创建了索引
创建索引同时也是一件非常危险的事情,如果针对空的表或者表里的数据不多,此时创建索引无所谓;但是表很大,此时创建索引,就会引起大量的CPU/IO消耗,有可能把数据库搞挂了,此时数据库无法相应其他操作了;所以想要创建索引一定要在建表初期的时候将索引创建
如果表数据很多,此时又必须要创建索引,怎么办?此时可以将一台机器上的数据库中的某表创建索引,然后将要创建索引的表所在的服务器上的数据导入到另一台机器上,此时也就解决了问题
3.删除索引
create index 索引名 on 表名(列名);//创建索引
drop index 索引名 on 表名;//删除索引
drop index index_student_id on student;
索引的核心内容:底层的数据结构
索引,一定是引入了一些额外的数据结构,来增加查询的速度;默认情况下,进行条件查询就是遍历表,一条一条都带入条件,引入索引就是要通过其他的数据结构,加快了查询的速度,减少了遍历表的可能性
在我们所学的数据结构中,哪种结构可以快速查询数据?二叉平衡树,AVL树以及红黑树,哈希表,三者的效率逐渐增加,索引所引入的数据结构一定与树或者哈希表相关,但是哈希表无法进行范围内的查询,只能进行哈希函数key-value对应,所以一定是树相关的数据结构
红黑树主要有两个原因被pass:
1.红黑树要寻找符合范围内的数据,可能会一直回退
2.红黑树是二叉搜索树,当元素非常多的时候,就会使树的高度,变得比较高,树的高度越高,进行查询的效率就越低,高度每增加一层,比较次数就增加1,数据库的索引/数据都是保存在硬盘上的,上述的每次比较,就都需要一次硬盘IO操作了
因此,红黑树不太适合于大规模在硬盘上管理数据的场景
在红黑树相关的数据结构中,想要寻找范围查询也相当不容易,进行中序遍历来查找数据,不仅要查找第一个符合要求的数据,也要找到最后一个符合要求的数据,时间复杂度为O(N),所以红黑树也是不符合要求的,此时引入一种数据结构:
B/B+树
这个数据结构类似于二叉树,但是它是多叉树,本质上是一个N叉搜索树,每个节点上可以存储多个元素,延伸出多个子树,表示同样数量的数据,需要的节点就少了,对应树的高度也大大降低了
每个方框是一个节点,如果想查找某一范围内的数据,只需要在叶子节点使用二分查找即可,同时我们解释如何插入节点:首先有8,15,插入2,5,11,15,此时2,5在8左边,11,在15右边,插入1,3,6,13,此时1在2左边,3在2,5之间,6在8左边,13在11,15之间,再将数据复制到叶子节点,此时就是一个完整的B树
1.每个节点的key是有序排列的,比较多的时候可以直接二分查找
2.B树也会控制,某个节点上保存的key不会太多,如果插入更多的元素,就会使节点分裂
3.多个数据,都是放在一块连续的存储空间上,进行比较的时候,一次硬盘IO就能读出整个节点,就可以直接完成上述比较(进行多次比较,实际上只有一次硬盘IO)
其实数据库索引数据的最终形态是B+树,相当于B树的升级版
B+树同样也是N叉搜索树,上图就是B+树,B树也就是数据不会重复,8,15不会向下转移到叶子节点,B+树,会把叶子节点通过类似于链表这样的链式结构串起来,此时就可以通过上述链式结构非常方便的,遍历整个表中的所有数据,同时也非常方便进行范围查询
B+树相对于B树的优势
1.非常方便进行遍历和范围查询
2.当前任何一次查询操作,最终都是要落到叶子节点完成的
3.由于叶子节点是数据的全集,对应的,非叶子节点中都是重复出现的数据
就可以把表每一行的数据,最终都关联到叶子节点这一层,非叶子节点中只保存一个单纯的key值即可,另外B+树中存的是索引值而不是数据
如果你创建主键了,那么就按照B+树的索引结构来创建;若是未创建主键,那么就按照隐藏的主键构造来组织数据
总结:
- 对于插入、删除数据频率高的表,不适用索引
- 对于某列修改频率高的,该列不适用索引
- 通过某列或某几列的条件查询频率高的,可以对这些列创建索引
下一篇博客介绍MySQL中的事务!