索引分析

  • 索引类型
  • B+Tree索引
  • 全值匹配
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围
  • 精确匹配某一列并范围匹配另一列
  • 只访问索引的查询
  • 索引的优点
  • 索引三星原则
  • 总结


索引类型

B+Tree索引

B-Tree索引按顺序存储的，并且每一个叶子页到根的距离相同,Innodb的树结构三个特征:

  • 非叶子节点只存储键值信息
  • 所有叶子节点之间都有一个链指针
  • 数据记录都存放在叶子节点中。

B+Tree索引类型

根据叶子结点存储的内容，可以分为主键索引和非主键索引:

  • 主键索引的叶子节点存的是整行数据。也被称为"聚簇索引"。
  • 非主键索引的叶子节点内容是主键的值，也被称为二级索引。

B+tree 结构图

mysql根据叶子节点id拼接全路径名称 mysql查询叶子节点_mysql

全值匹配

全值匹配指的是和索引中的所有列进行匹配，例如前几期提到的index(name,phone,age)联合索引，可以用于查找姓名为 gio 年龄 18 男性的人

匹配最左前缀

前面提到的索引可用于查找性别为男性的人，即使用了索引第一列

匹配列前缀

也可以只匹配某一列的值得开头部分，例如前面的索引 可用于查找所有以g开头的姓的人。这里也使用了索引第一列。

匹配范围

例如前面使用索引查找姓在 gio 和 gto之间的人，这里也只用了索引第一列

精确匹配某一列并范围匹配另一列

前面提到的索引也可用于查找所有姓为 gio，并且名字是以g字母开头的人，即第一列last_name全匹配，第二列 first_name 范围匹配

只访问索引的查询

B+tree 通常可以支持"只访问索引的查询",即查询只需要访问索引，而无须访问数据行。这就是覆盖索引。减少磁盘IO操作。

总结

因为索引树中的节点是有序的，所以除了按值查找以外，索引还可以用于查询中的 Order by操作(按顺序查找)。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以 如果order by 满足前面几种查找类型，那么也可以满足排序需求。

索引的优点

  • 索引大大减少了服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机I/O变为顺序I/O

索引三星原则

一星：索引将相关的记录放到一起则获得一星
二星：如果索引中的数据顺序和查找中的排列顺序一致则获得二星
三星：如果索引中的列包含了查询中需要的全部列则获得"三星"

总结

利用索引查询时，以下三个原则要记住：

  • 单行访问时很慢的。毕竟在机械硬盘中进行IO操作的，尽可能的在从存储中读取一个数据块尽可能多的获取所需要的行，使用索引可以创建位置引用以提升效率
  • 按照顺序访问范围数据时很快的。1. 顺序I/O 2.服务器按顺序取出 就不需要额外的排序了。
  • 索引覆盖访问查询时很快的。