索引的数据结构
数据库索引是数据库为了快速访问的一种数据结构。是一种排好序的数据结构。
索引是有序的,是索引非常重要的特点,索引的高效几乎都是建立在有序的基础上。
以下是常见的几种数据结构,在磁盘搜索下效率分析:
1、有序数组,有序的数组可以使用折半查找法,可以快速找到数据,也可以进行随机访问。但是数据库的需要满足随机插入和删除, 在有序数组中间插入数据需要移动大量数据,代价极大。
2、二叉树,有序的数据,查询快,插入也快。 但数据库的数据是递增插入的,最早插入的主键是最小的,一旦最小的数据成了树的根节点,后面的数据会一直在树的右侧,最终变成一个链表。
3、自平衡二叉树(红黑树),相对于二叉树,它会自动平衡,解决了变成链表的问题,可以降低树的高度,如果数据库有100w数据时,红黑树的高度至少达到了20层,此时查询一个数据要操作20次,性能仍然不够;并且相邻数据可能会被分的很远,会消耗磁盘操作,无法预读。
图中总数12,查询12的节点,需要4次操作; 同时11节点和12节点,虽然逻辑上是顺序的, 但是结构上分的很远。
4、B树,以页的形式代替了树节点,每页可以存放很多树节点,有效的降低了树的高度。B树中,树上所有节点都挂载了data数据,若每页16KB,每个节点data占1KB,则每页最大分配16个节点,若存放100万数据,高度为5;1000万高度为6;按索引查询速度已经很快了,若是进行全表扫描,或是范围查询,就需要多次遍历树,效率非常差。
每个节点的查询复杂度是不同的,若要查找2-7范围内的数据,需要多次访问树。
5、B+树,对B树进行了进一步优化,所有数据存放在叶子节点,非叶子节点是从数据集中冗余出索引数据组成了B树结构。索引非常小,按long类型索引计算每个索引只占8字节,指向下一节点指针占6字节,以16K的页计算,每页可存放1170个节点。高度为3时就可以存放两千万数据。由于数据全在叶子节点,每个数据的查询复杂度是固定的。并且叶子层,页之间增加了指针,可以避开树,直接从叶子节点访问。
1-12每个数据的查询复杂度都是2,当查询3-7时,只需要先通过树找到3,然后通过指针横向遍历即可。
innodb在页之间增加了双向指针,可以很好的逆向遍历。
聚集索引与非聚集索引
聚集索引,主键构成了B+树,叶子节点存放了整行数据,可以完整表达数据库。
非聚集索引,索引构成了B+树,叶子节点只存放了主键与索引,只能通过回聚集索引查询,才能取到数据行。 非聚集索引不存放完整数据的原因:为了一致性和减少存储,没有存储整行数据(myisam仅支持非聚集索引,索引与数据分文件存储)
主键索引必定是聚集索引
非聚集索引除了B+,还有hash索引,通过hash组织的索引结构,查询速度快,使用较少的原因是只能适用于= in查询,无法范围查询,而B+树的叶子节点是双向链表,可以快速范围查询
递增插入和中间插入的影响
B+树插入的节点若是自增的,只需一直往后加页即可。若插入的节点不是递增的, 则会对前面的页进行拆分,并且进行树平衡,影响性能。
联合索引
联合索引,由多个字段构成的索引;在构件树时,mysql先用最左侧字段的大小构件树, 当两个索引最左侧一致时,在用左二字段进行排序构件,以此类推。
最左前缀原则:按照构建索引树的特点,在查询时,首先使用最左侧字段进行匹配,当最左侧一致时,再匹配第二个,这样才能保证索引的有序性,若不顺序使用第二个字段匹配,第三第四字段将无法利用索引,因为后续数据无法保证有序。
因为索引的基本原则是有序性,只要确定前一列后,才能保证下一个列的有序性,所以才会有左前缀原则
当要创建的索引很多时,尽量按联合索引创建。