一文弄清数据库索引及其原理

导读：数据库索引是一个不可忽视的点，索引建的好可以让查询速度变快，但是相反不适当的建立索引反而会带来不利的影响，本文将通过四个问题来讨论索引概念以及如何合理使用索引，希望对各位有所帮助。以下是四个问题

• 为什么要给表加上主键？
• 为什么加索引后会使查询变快？
• 为什么加索引后会使写入、修改、删除变慢？
• 什么情况下要同时在两个字段上建索引

为什么要加主键？

一个没加主键的表它里面的数据无序的存储在磁盘上，如果当数据量大时想要查询其中的某一条数据逐个遍历速率很慢。如果给表上了主键，那么表在磁盘上的存储结构就会从整齐排列的结构转变成了平衡树状结构，通过对这种结构结合高效率算法进行查询速率就会变快（这里所说的主键也就是索引）。

索引是怎么使查询变快？

• B+平衡树结构（B树又称“B树”的变体，结点之间多了链表连接）
• 高效率算法

索引就是通过事先排好序构建平衡树结构，从而在查找时可以应用二分查找等高效率的算法。如像顺序查找则复杂度为O(n)，当排序后使用二分查找法则负责度为O(log2n)，当n值很大时两者效率差距将很明显。（这里涉及到了B+平衡树结构和二分查找法，这里不做具体的讲解。想了解的朋友可以去阅读相关的资料）

索引为什么会使进行增、删等操作变慢

索引可以提高读取性能，但是也会降低写性能。 增、删数据等操作都会破坏了平衡树的平衡性。 因此，在每次数据改变时 DBMS必须去重新梳理树的结构以确保它的正确，这样便带来性能的开销。

什么情况下要建立双索引

这里我们先引入两个名词

• 聚集索引：一个表中只能够有一个聚集索引（主键），因为主键的作用就是把表数据格式转换成平衡树的格式。
• 非聚集索引：也是采用平衡树结构，每次字段建一个新索引时字段中数据就会被复制一份出来用于生成索引，结点里包含了字段值以及主键值。 因此给表添加索引会增加表的体积占用磁盘存储空间。

区别：通过聚集索引可以直接查到需要查找的数据。 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。也就是说使用非聚焦索引最终也是通过聚焦索引来搜索得到目标数据，且建立过多的索引导致DBMS需要维护多个数据结构导致性能下降。

那么什么情况下才要建立双索引？

下面通过两个demo对比来讲解

demo1 ：

//建立year为非聚集索引

create index index_name_year on USER_INFO(name,year)；

//通过year来查找name，需要先查找year平衡树获取到主键，再使用主键在聚集索引里找到目标数据值

select name from userInfo where year = ’12’；

demo2:

//建立以name和year字段建立双索引

create index index_name_year on USER_INFO(name,year)；

//通过year查找name

select name from userInfo where year = ’12’

//这个时候会先查非聚集索引year平衡树，当发现子叶结点里除了有主键外还有name字段值也在里面，就可以直接返回值即可而不用再去通过聚集索引。

总结

索引是通过平衡树和算法提高了查询的效率，但是因为写操作会破坏树平衡导致DBMS需要重新梳理树等原因所以索引会导致写效率降低。但是一切都需要结合实际场景合理应用聚集索引、非聚焦索引、双索引等才能发挥出索引的强大作用。

感谢您的阅读，如果喜欢本文欢迎关注和转发，本头条号将持续分享IT技术知识。对于文章内容有其他想法或意见建议等，欢迎提出共同讨论共同进步。