索引简要小结

简介及优势

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引类型

主要分为聚簇索引和辅助索引

• 聚簇索引：表记录的排列顺序和与索引的排列顺序一致，按照主键建立索引，唯一
• 辅助索引：按照其他列建立索引，不唯一

另一种分类方式：

• 普通索引：
• 唯一索引：索引列不包含重复元素，可以为null
• 全文索引：
• 复合索引：最左前缀

索引的实现机制

B+树

• B+树优势
  
  • B+树是一个N叉排序树，B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。
  • 与B树相比的优势
    
    • B+树的磁盘读写代价更低：内部节点不包含具体的指针，相同的存储空间可以存储更多的关键字
    • B+树的查询效率更加稳定：所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
• B+树的实现机制
  
  • Dense Index：为每一个键添加一个指针指向键所在的存储空间
  • Sparse Index：对已经有序的存储块建索引，将每个存储块的第一行数据拿出来建立索引

索引的使用限制

• 不使用索引的情况
  
  • 隐式转换导致索引失效
  • 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
  • 使用 <> 、not in 、not exist、!=
  • like “%_” 百分号在前
  • 单独引用复合索引里非第一位置的索引列
  • *
• 索引失效的情况
  
  • 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）
  • 统计信息失效 需要重新搜集统计信息
  • 索引本身失效 需要重建索引

参考：
由浅入深理解索引的实现
索引失效原因总结