数据库索引浅析

数据库索引

众所周知,索引是用来提高sql语句的查询效率的,那么索引到底是什么,我们该在什么时候建立索引,该如何取建立一个好的索引呢?

索引的原理

索引是一种数据结构,来看一下几个最基本的算法,比如我要查找一个id = 8888的用户,
select * from users where id=8888 limit 1;
首先想到的就是顺序查找,直到查到一条id为1 的元素为止,显而易见,这个算法的复杂度是O(n),是很糟糕的,理论上当n越大,开销越大。进而想到有些比较好的算法,二叉树,二分查找,但是都是有要求的,二叉树只能针对二叉树的数据结构起作用,二分查找也只能对有序的数据结构起作用,而数据库本身可以维护着一套符合特定算法的数据结构,可以用某种方式指向数据的数据结构,而我们使用的mysql采用的是B+树的方式建立所索引的。

a4a89204-44ec-38f0-a011-dad4f05acfdf
如图所示,是一棵高度为2的B+树,也可以很清晰的看到其特点
1.所有的记录都存放在叶子节点
2.所有的数据都按顺序排列(到最后通过二分查找获取数据)
3.叶子结点通过双向链起来,便于定位插入新的叶子结点
为什么说它优秀呢?通常数据库索引的消耗来源于磁盘io,而B+树,一个叶子节点对应着一个页,理论上,只需要读取m次(树的高度)磁盘,就可以得到数据所在节点

当产生一个新的元素,存在两种情况
1.插入所在页的叶子结点没满,直接插入
2.当叶子结点已满,会将此叶子结点一分为二,在父节点增加一个新的结点,因此这种方式不会影响兄弟结点

索引的类型

1.普通索引
正常的索引,没有限制
2.唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。
坑:如果一开始没有想好该字段建唯一索引后,由于表中已经存储了大量的数据,这时候就需要考虑去除重复项的事情了,而唯一索引有条语句
alert ignore table wx_fans add unique index(openid)是可以帮你省却很多操作的
3.全文索引
与普通索引不同,适合于大字段处理,举个例子,搜索一段文字,你不需要把全文都输入查询,只需要输入关键字
4.组合索引
较为复杂点,多经常有多个限制条件的sql语句适用,比如搜索来源为4,访问途径是2的用户
select user_id from users where from=4 and mtype=2 limit 100;
这时候可以建立 (from,mtype)的组合索引,比2个索引分开建立更有效率,但需要注意的是mysql有最左前缀的组合,所以只有from,mtype或者from查询的时候才能使用索引,而单mtype是无法享受的

什么情况建立索引?

1.对经常使用where查询的字段设置索引
2.数据库不要出现null值,因为索引是无法检索null值所在的列的
3.短索引(没用过),意思就是去某个字段的前几个字符建立索引
4.对数据量很大,但是重复值很多的不要去建立索引
5.索引的数据类型最好是简单而又小的,比如int型

发表评论