数据库索引

概念：索引就像一本书的目录，帮助数据库管理系统高效获取数据的数据结构；若不使用索引，则需要从第一条记录开始扫描，直到把所有的数据查找完，才能找到自己想要的数据。但有些情况使用索引反而使得效率更低。
索引是在引擎层实现的

什么情况下不要建立索引：（索引的价值是帮助你快速定位）

• 要查找的数据里很多重复数据（想要定位的数据很多）
• 数据量不到1000行

索引的种类：
功能逻辑上分：

• 普通索引
  是基础的索引，没有任何约束，主要用于提高查询效率。
• 唯一索引
  在普通索引的基础上增加了数据唯一性的约束，在一张数据表中可以有多个唯一索引。
• 主键索引（聚集索引）
  在唯一索引的基础上增加了不为空的约束（NOT NULL + QNIQUE),一张表只有一个主键索引
• 全文索引
  MYSQL自带的全文引擎只支持英文

物理实现分：

• 聚集索引
  按照主键（primary key）来排序存储数据,这样在查找行的时候非常有效
  一张表只能有一个聚集索引，因为数据本身只按一个顺序存储
• 非聚集索引（二级索引或者辅助索引）
  第一次先查找索引（索引项是按照顺序存储的）
  第二次找到索引对应的位置（主键的值）取出数据行（索引项指向的内容是随机存储的）
  （非聚集索引搜索两次索引树）

回表：从索引树回到主键索引树搜索的过程，我们称为回表。
聚集索引和非聚集索引的区别

• 聚集索引的叶子结点存储的就是我们要得数据记录，非聚集索引的叶子节点存储的是数据的位置。
• 一个表只能有一个聚集索引，因为只能有一种排序存储方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
• 使用聚集索引的时候，数据的查询效率高，但是对其数据进行插入、删除、更新操作，效率会比非聚集索引低。

字段个数分类：

• 单一索引
  索引列为一列时则为单一索引
• 联合索引
  多个列组合在一起形成的索引

索引的不足：

• 占用存储空间
• 降低数据库写操作的性能
• 时间消耗

常见的索引优化措施

• 覆盖索引：当前索引已经“覆盖了”我们的查询需求，我们称为覆盖索引。（select ID from T where k=3）
• select ID from T where k between 3 and 5（只需要搜索一张索引表K）
• select* from T where k betewwn 3 and 5(需要索引两张索引表（K，T）)
• 最左前缀
• 索引下推
• 先筛选不符合条件的值，排除再回表，因此可以减少回表数

索引数据结构比较

• 哈希表
  • 适合只有等值查询的场景（NoSQL引擎）
  • 范围查询是需要扫描整个区间，耗时
• 有序数组
  • 在等值查询和范围查询的场景中的性能都非常优秀
  • 但是在插入新的值的时候，会造成后面所有记录的挪动
  • 适用于静态存储引擎（比如2017年的城市人口信息）

• 为什么B+树比B树更适合文件系统索引
  • B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低
  • B+树的非叶子结点中只存放关键字的信息，没有存放关键字具体信息，因此结点更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
  • 由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

（以下为二级索引查询例子）

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT ‘’,
index k(k))
engine=InnoDB;

insert into T values(100,1, ‘aa’),(200,2,‘bb’),(300,3,‘cc’),(500,5,‘ee’),(600,6,‘ff’),(700,7,‘gg’);