索引是存储引擎用于快速找到纪录的一种数据结构。常见的索引有：

B-Tree索引

B-Tree索引的具体实现取决于存储引擎，比如MyISAM引擎的B-Tree索引使用了前缀压缩技术，让索引更小，而InnoDB则按照原数据格式进行存储。这里讲一下MyISAM的B+树索引和InnoDB的B+树索引有什么区别。在MyISAM中一张表由三个文件组成.frm文件存储表结构.myi和.myd存储索引和数据。MyISAM的主键索引树中叶子节点保存了主键值和指向列值的文件指针，二级索引也是如此，也就是说不管在查询中利用到了主键索引还是二级索引，都可以直接拿到文件指针，进而取到数据。InnoDB采用聚簇索引，即数据和索引保存在同一个文件中，所以InnoDB表只包含.frm文件和.ibd文件。它的主键索引树的叶子节点保存了主键值和对应的全部数据。二级索引树的叶子节点包含该条索引的列值和主键，所以在利用到二级索引的时候需要再查一遍主键索引树才能拿到数据（如果查询的是主键值则不需要这一步）。采用聚簇索引的好处有：

  • 可以把相关数据都保存在一起，减少了I/O次数。
  • 数据访问更快，索引和数据都在一个B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引更快。
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点在于：

  • 聚簇索引最大限度的提高了IO密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没那么重要了，聚簇索引也没什么优势了。
  • 插入速度严重依赖插入顺序，按照主键的插入是加载到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
  • 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行，或者主键被更新导致更新导致需要移动行的时候，可能面临“页分裂（page split）”的问题。当行的主键要求必须将这一行插入到某个已满的页中时，存储引擎会将该也分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
  • 二级索引（非聚簇索引）可能比想象的要更大。因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找而不是一次。

B-Tree索引适合用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于最左前缀的查找。

哈希索引

​ 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希码，哈希索引将所有的哈希码存储在索引汇总，同时在哈希表中保存指向每个数据行的指针。哈希索引的数据结构如下：

槽（索引列的哈希值） 值（文件指针）
1234 指向第1行的指针
3421 指向第2行的指针
6432 指向第3行的指针

因为索引自身之存储对应的哈希值，所以索引的结构非常紧凑，这也让哈希索引的速度非常快。但是哈希索引有以下限制：

  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
  • 哈希索引不是按照索引值的顺序的，所以也就无法用于排序。
  • 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。比如，在数据列（A,B）上简历哈希索引，如果查询只有数据列A则无法使用该索引。
  • 哈希索引只支持等值比较查询，包括=，IN，<=>。也不支持任何范围查询。
  • 访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，直到找到所有符合条件的行。

空间数据索引（R-Tree)

​ 该索引用于存储地理数据。

全文索引

​ 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的是，而不是简单的WHERE条件操作。

创建高性能索引

​ 在大多数情况下选择选择性强的列作为索引的第一列，选择性指的是，不重复的索引值和数据表的纪录总数（#T），范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找的时候过滤更多的行。对于BLOB、TEXT很长的VARCHAR类型应当使用SUBSTRING（）来取它们的一部分作为索引。

InnoDB行锁需要注意的问题（间隙锁）

​ InnoDB只有在访问行的时候才会加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤所有不需要的行时才有效。

1
2
3
4
SET AUTOCOMMIT = 0
BEGIN;
SELECT actor_id FROM actor WHERE actor_id < 5
	AND actor_id <> 1 FOR UPDATE;

假设数据库中只有id为24的行，但这实际上对14的行都加了排他锁，InnoDB会锁住第一行，这是因为MySQL为该查询选择的是索引范围扫描。