索引的设计与使用

1,184 阅读4分钟

索引是数据库中用来提高性能最常用的工具,本文主教总结了mysql支持的索引类型,并简单介绍索引的设计原则。

索引的由来

  • 我们为什么需要索引呢?
    • 索引可以显著提高sql查询的性能
  • 索引是如何让selete性能的提升的呢?索引的步步演化!
    • 相信大家都提过“空间换时间”的经典名言,这句话广泛应用在计算机的各个领域。不错索引的本质是对已有数据结果的记忆化。那我们最先想到的是用是用什么来存储呢,有点经验的朋友就会想到二叉搜索树(BST
    • 但BST有一个缺点,在特定的条件下,它会退化成一个链表,这就和遍历无区别了。所以我们就自然而然地想到了平衡二叉树(AVL),AVL会自动维护左右子树地高度差(相差地绝对值为1),它能很好地解决BST的问题。
    • 但它又会带来一个新的问题,想要清楚这个问题需要了解——mysqlHASH将数据从磁盘读入内存的大小其实是有一个固定大小的(InnoDB默认16K,当然可以通过配置修改),那AVL树的节点会有16K吗?显然很难。一个数据与其两个子数据的地址能占多大的空间呢!如此而来我们需要一个新的结构来解决这个问题,此时引入B树(多路查找树)。它能存放N个数据及N+1的子节点完美解决了上述的问题。
    • 不过mysql实际上的BTREE索引是B+树。它与B树的区别在于B+树的数据完整存在于其叶子节点,叶子节点之间存在指针连接,非叶子节点只是起着分割数据的作用,类似于一节节火车车厢,非叶子节点是车厢号而数据就是车厢里的座位(数据有可能是完整的记录也可能是记录的id)。

索引的类别

  • 按照数据结构划分
    • BTREE索引——见名知其义,BTREE索引在存储上是以B+树的结构的逻辑结构存放的。它是对B树的一个PLUS优化版。因为B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低。几乎所有的存储引擎都支持BTREE
    • HASH索引——Hash索引的优点是当索引的重合度较低时(hash冲突较少)其查询的时间复杂度是O(1)拥有优异的性能,缺点是不能进行范围查找(只能进行精确查询),索引条件较为苛刻(有些存储引擎不支持,需要建立索引的字段必须是重合度较低的)
  • 前缀索引——只当某项字段的数据过于长时可以取其前缀作为索引,做到性能的最优
  • 聚集索引和非聚集索引——聚集索引指的是BTREE中叶子节点存储的是整条数据,而非聚合索引存储的是其unique的字段(一般是主键,当没有主键时会存储表中unique字段,若没有unique字段数据库会自动生成一个名叫row_id的字段)。当查询的字段不是或者包含索引字段时需要进行二次查询(因为只获得其索引的值无法获取到其他的列需要在主表进行二次查询,术语称之为回表)
  • 联合索引——mysql支持对多项列进行索引,它满足最左匹配原则。我理解的是即在B+树中的排序权重是从左往右变低的

设计索引的原则

  • 搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在WHERE子句中的列,或连接子句中指定的列。
  • 使用唯一索引。考虑某项列中值的分布。索引的基数越大,索引的效果越好。意思是该列的重复度低,值的选择有许多
  • 使用短索引。当需要建立索引的列其数据较大时,如TEXT,CHAR(200),那么就不要对整个列进行索引。考虑前缀索引
  • 利用最左匹配原则。
  • 不要过度建立索引。因为索引本质上也是一个数据文件,当一个表的索引变多时,一条数据的改变会导致所有索引表的数据都需要维护(可能会导致索引的重构)
  • 对于InnoDB的表,记录默认会按照一定的顺序储存,如果有明确定义的主键则按主键顺序保存。即前文所说的非聚集索引