优点 | 缺点 |
保证数据库表中每行数据的唯一性 | 创建和维护索引需要耗费时间,尤其是增删改的时候索引也要维护 |
加快检索速度 | |
加速表和表之间的连接 | 索引需要占用一定的内存空间,聚簇索引需要的空间更大。 |
使用索引,可以在查询过程中,使用优化隐藏器,提高系统性能 |
索引的数据结构:
在数据库中,主要有三类索引结构:B树索引、Hash索引和位图索引
B树索引(平衡树索引),MySQL、Oracle和SQL Server数据库都用的是B树索引,它具有一个或者多个分支,用于遍历;而最下层的叶子结点存储数据。
B-Tree:
B+Tree:非叶子结点只存储关键字信息,叶子结点之间有个链指针。
Hash索引:
使用Hash算法将数据转为定长的Hash值,与这条数据的行指针存入Hash表的对应位置,如果发生Hash碰撞,则在对应的Hash键下用链表存储。检索速度很快,O(1)
位图索引:
有点类似于枚举类型,这里不做赘述,适用于静态数据,不适合频繁更新。
B+树的优点:
内部结点只存放key,不存放value,所以在一页内存里可以获取更多key,便于迅速地范围查找。
在叶子结点层,所有结点有一条链相连,即使是全遍历,也只需要logN的时间到达最小的叶子结点,再进行N时间的顺序遍历。或者在找大于或小于某一关键字的数据,也可以通过底层链表遍历。因此查询效率非常稳定。
Hash索引:
精确迅速但无法范围查找;无法使用索引排序;不能模糊匹配以及最左前缀匹配;当遇到hash碰撞,查询效率可能会下降很快。
前缀索引:
只对索引的前几个字符进行索引,节省空间但会提高选择的成本。
最左前缀匹配原则:
在MySQL中,当建立了多列索引,会进行该原则,如果建立了(a,b,c)3列索引,已经对a、ab、abc建立了索引,where子句使用了最频繁的一列在最左边。
MySQL会一直向右匹配直到遇到范围查找(> < between like)就停止匹配。
添加索引原则:
二八定律,少查询使用和内容过大或过少的数据列不创建索引,修改和检索的性能是对立的,提高索引就不利于修改。
含有外键的数据列一定建立索引。
聚簇索引:
是一种数据的存储方式,数据和索引按照相同顺序排列。MySQL只有INNODB支持聚簇索引。
索引速度快,减少磁盘的IO,但是一个表一直能有一个,所以会在频繁使用、排序的字段上创建索引。