一、索引基础

1.1、索引类型

1）B-Tree索引

①InnoDB的B-Tree索引原理图：


mysql查询指定开头表名数量 mysql查询以什么开头_mysql查询以某些开头


B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。

②B-Tree索引适用的查询类型

演示示例：


mysql查询指定开头表名数量 mysql查询以什么开头_Memory_02


mysql查询指定开头表名数量 mysql查询以什么开头_Memory_03


B-Tree索引适用的查询类型：

  • 全值匹配 —— 和索引中的所有列进行匹配。例如查找Cuba Allen、出生于1960-01-01的人
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列 —— 例如查找所有姓为Allen，并且名字以字母K开头的人。第一列全匹配，第二列部分匹配。
  • 只访问索引的查询 —— 即查询只需要访问索引，而无须访问数据行。

B-Tree索引当然也有限制的查询类型：

  • 如果不是按照索引的最左列开始查找，则无法使用索引
  • 不能跳过索引中的列 —— 无法用于查找姓为Smith并且在某个特定日期出生的人，如果不指定名（first_name），则MySQL只能使用索引的第一列。
  • 如果某个查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。

这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

2）哈希索引

哈希索引基于哈希表实现。在MySQL中，只有Memory引擎显示支持哈希索引。

在MySQL中哈希索引解决哈希冲突的方法是拉链法（链表法）。

基于Memory引擎创建哈希索引的示例：


mysql查询指定开头表名数量 mysql查询以什么开头_MySQL_04


哈希索引的限制：

  • 哈希索引不是按照索引值顺序存储的，所以也就无法用于排序。
  • 哈希索引不支持部分索引列匹配查找，因为哈希索引始终使用索引列的全部内容来计算哈希值的。
  • 哈希索引只支持等值查询，不支持任何范围查询。
  • 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

InnoDB引擎有一个特殊的功能：“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

3）空间数据索引（R-Tree）

略

4）全文索引

略

5）其他索引类型

略

二、索引的优点

总结起来索引有如下三个优点：

  • 索引大大减少了服务器需要扫描的数据量
  • 索引（B-Tree索引）可以帮助服务器避免排序和临时表（ORDER BY和GROUP BY操作）
  • 索引可以将随机I/O变为顺序I/O（某些查询只使用索引就能够完成全部查询）