作者：是虎子呀
之前有过一次面试，关于mysql索引的原理及使用被面试官怼的体无完肤，立志要总结一番，然后一直没有时间（其实是懒……），准备好了吗？
数据库索引，是数据库管理系统(dbms)中一个排序的数据结构，它可以对数据库表中一列或多列的值进行排序，以协助更加快速的访问数据库表中特定的数据。通俗的说，我们可以把数据库索引比做是一本书前面的目录，它能加快数据库的查询速度。
思考：如何在一个图书馆中找到一本书？设想一下，假如在图书馆中没有其他辅助手段，只能一条道走到黑，一本书一本书的找，经过3个小时的连续查找，终于找到了你需要看的那本书，但此时天都黑了。
为了避免这样的事情，每个图书馆才都配备了一套图书馆管理系统，大家要找书籍的话，先在系统上查找到书籍所在的房屋编号、图书架编号还有书在图书架几层的那个方位，然后就可以直接大摇大摆的去取书了，就可以很快速的找到我们所需要的书籍。索引就是这个原理，它可以帮助我们快速的检索数据。
一般的应用系统对数据库的操作，遇到最多、最容易出问题是一些复杂的查询操作，当数据库中数据量很大时，查找数据就会变得很慢，这样就很影响整个应用系统的效率，我们就可以使用索引来提高数据库的查询效率。
目前大部分数据库系统及文件系统都采用b-tree或其变种b+tree作为索引结构， 我在这里分别讲一下。推荐看下：
即b树，注意（不是b减树），b树是一种多路搜索树。使用b-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。
b-tree有如下一些特征：
定义任意非叶子结点最多只有m个子节点，且m>2。
根结点的儿子数为[2, m]。
除根结点以外的非叶子结点的儿子数为[m/2, m]， 向上取整 。
每个结点存放至少m/2-1（取上整）和至多m-1个关键字；（至少2个关键字）。
非叶子结点的关键字个数=指向儿子的指针个数-1。
非叶子结点的关键字：k[1], k[2], …, k[m-1]，且k[i] <= k[i+1]。
非叶子结点的指针：p[1], p[2], …,p[m]（其中p[1]指向关键字小于k[1]的子树，p[m]指向关键字大于k[m-1]的子树，其它p[i]指向关键字属于(k[i-1], k[i])的子树）。
所有叶子结点位于同一层。
有关b树的一些特性：
关键字集合分布在整颗树的所有结点之中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找。
b树的搜索：从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复执行这个操作，直到所对应的节点指针为空，或者已经是是叶子结点。
例如下面一个b树，那么查找元素43的过程如下：
根据根节点指针找到18、37所在节点，把此节点读入内存，进行第一次磁盘io，此时发现43>37，找到指针p3。
根据指针p3，找到42、51所在节点，把此节点读入内存，进行第二次磁盘io，此时发现42<43<51，找到指针p2。
根据指针p2，找到43、46所在节点，把此节点读入内存，进行第三次磁盘io，此时我们就已经查到了元素43。
在此过程总共进行了三次磁盘io。
b+tree属于b-tree的变种。与b-tree相比，b+tree有以下不同点：
有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），即非叶子结点的子树指针与关键字个数相同。这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的非叶子结点可以看成是叶子节点的索引部分。
同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。
b+树的磁盘io代价更低：b+树非叶子节点没有指向数据行的指针，所以相同的磁盘容量存储的节点数更多，相应的io读写次数肯定减少了。
b+树的查询效率更加稳定：由于所有数据都存于叶子节点。所有关键字查询的路径长度相同，每一个数据的查询效率相当。
所有的叶子节点形成了一个有序链表，更加便于查找。
关于mysql的两种常用存储引擎myisam和innodb的索引均以b+树作为数据结构，二者却有不同（这里只说二者索引的区别）。推荐看下：
myisam使用b+树作为索引结构，叶节点叶节点的data域保存的是存储数据的地址，主键索引key值唯一，辅助索引key可以重复，二者在结构上相同。关注微信公众号：java技术栈，在后台回复：mysql，可以获取我整理的 n 篇 mysql 教程，都是干货。
因此，myisam中索引检索的算法为首先按照b+tree搜索算法搜索索引，如果要找的key存在，则取出其data域的值，然后以data域的值为地址，去读取相应数据记录 。因此，索引文件和数据文件是分开的，从索引中检索到的是数据的地址，而不是数据。
innodb也是用b+树作为索引结构，但具体实现方式却与myisam截然不同，首先，数据表本身就是按照b+树组织，所以数据文件本身就是主键索引文件。叶节点key值为数据表的主键，data域为完整的数据记录。
因此innodb表数据文件本身就是主键索引(这也就是myisam可以允许没有主键，但是innodb必须有主键的原因）。第二个与myisam索引的不同是innodb的辅助索引的data域存储相应数据记录的主键值而不是地址。换句话说，innodb的所有辅助索引都引用主键作为data域。
普通索引：（由关键字key或index定义的索引）的唯一任务是加快对数据的访问速度。
唯一索引：普通索引允许被索引的数据列包含重复的值，而唯一索引不允许，但是可以为null。所以任务是保证访问速度和避免数据出现重复。
主键索引：在主键字段创建的索引，一张表只有一个主键索引。
组合索引：多列值组成一个索引，专门用于组合搜索。
全文索引：对文本的内容进行分词，进行搜索。（mysql5.6及以后的版本，myisam和innodb存储引擎均支持全文索引。）推荐看下：。
主键自动建立唯一索引。
经常作为查询条件在where或者order by 语句中出现的列要建立索引。
查询中与其他表关联的字段，外键关系建立索引。
经常用于聚合函数的列要建立索引，如min()，max()等的聚合函数。
经常增删改的列不要建立索引。
有大量重复的列不建立索引。
表记录太少不要建立索引，因为数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果 。
建立联合索引的时候都会默认从最左边开始，所以索引列的顺序很重要，建立索引的时候就应该把最常用的放在左边，使用select的时候也是这样，从最左边的开始，依次匹配右边的。
可以保证数据库表中每一行的数据的唯一性。
可以大大加快数据的索引速度。
加速表与表之间的连接。
可以显著的减少查询中分组和排序的时间。
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大，其实建立索引就是以空间换时间。
表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了维护效率。
创建测试表index_test
使用python脚本程序通过pymsql模块，向表中添加十万条数据
我要评论import pymysql
def main():
# 创建connection连接
conn = pymysql.connect(host='localhost',
port=3306,
database='db_test',
user='root',
password='deepin',
charset='utf8')
# 获得cursor对象
cursor = conn.cursor()
# 插入10万次数据
for i in range(100000):
cursor.execute("insert into index_test values('haha-%d')" % i)
# 提交数据
conn.commit()
if __name__ == "__main__":
main()
在mysql终端开启运行时间监测：set profiling=1;
查找第1万条数据ha-99999
select * from index_test where name='haha-99999';
查看执行的时间：
show profiles;
为表index_test的name列创建索引：
create index name\_index on index\_test(name(10));
再次执行查询语句、查看执行的时间：
可以看出合适的索引确实可以明显提高某些字段的查询效率。
最后，感谢女朋友在生活中，工作上的包容、理解与支持 ！
推荐去我的博客阅读更多：
1.
2.
3.
4.
觉得不错，别忘了点赞+转发哦！
如对本文有疑问， 点击进行留言回复！！
解决IDEA中Maven依赖包导入失败报红问题(总结最有效8种解决方案)
IntelliJ IDEA配置java环境及解决IDEA不能直接运行单个JAVA文件的问题
IDEA中创建maven项目引入相关依赖无法下载jar问题及解决方案
网友评论