索引简介

1. 索引基础

MySQL中，索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准：不同的存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。

2. B-Tree索引

B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。
假设有如下数据表：

CREATE TABLE People(
  last_name  varchar(50) not null,
  first_name varchar(50) not null,
  dob        date        not null,
  gender     enum('m','f')not null,
  key(last_name,first_name,dob)
);

数据表中有以下数据：

last_name	first_name	dob
Allen	Cuba	1960-01-01
Astaire	Angelina	1980-03-04
Barrymore	Julia	2000-05-16

前面描述的索引对如下类型的查询有效：

• 全值匹配：全值匹配指的是和索引中的所有列进行匹配，例如查找姓名为Allen Cuba、出生于1960-01-01的人
• 匹配最左前缀：即使用索引第一列，例如查找姓为Allen的人
• 匹配列前缀：只匹配某一列的值的开头部分，例如查找所有已A开头的姓的人（这里只使用了索引的第一列）
• 匹配范围值：查找姓在Allen 和 Barrymore之间的人（这里只使用了索引的第一列）
• 精确匹配某一列并且范围匹配另外一列：索引可以用于查询所有姓为Allen并且名字是字母C开头的人，即第一列last_name全匹配，第二列first_name范围匹配
• 只访问索引的查询：即查询只需要访问索引，而无须访问数据行。

索引数中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。一般来说，如果B-Tree可以按照某种方式查到值，那么也可以按照这种方式进行排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

B-Tree索引的限制
- 如果不是按照索引的最左列开始查找，则无法使用索引。
- 不能跳过索引中的列。
- 如果查询中有某个列的范围查询，则右边的所有列都无法使用索引优化查找。

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

3.哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引的所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算哈希码（hash code），哈希码是一个较小的值，并且不同的键值的行计算出来的哈希值不一样。哈希索引将所有的哈希值存储在索引中，同时在哈希表中保存指向每个数据行的指针。

索引自身需要存储对应的哈希值，索引结构十分紧凑，使得哈希索引速度非常快。

哈希索引的限制
- 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度非常快，所以大部分情况下这点对性能影响不明显
- 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序
- 哈希索引也不支持部分索引列的匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的
- 哈希索引只支持等值比较查询，包括=、IN()、<=>，不支持任何的范围查询
- 访问哈希索引非常快，除非有很多的哈希冲突
- 哈希冲突比较多时，索引维护操作代价会很高
哈希索引适合特定场景，而一旦适合哈希索引，性能提升将非常显著。

4.空间数据索引（R-Tree）

空间索引会从所有维度来索引数据。查询时，可以有效的使用的任意维度的来组合查询，mysql的GIS不完善，开源数据库中对GIS的解决方案做的比较好的是PostgreSQL的POSTGIS。

5. 全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文搜索和其他几类索引的匹配方式完全不一样。

6. 其他索引类别

还有很多第三方的存储引擎使用不同的类型的数据结构来存储索引。例如TokuDB使用分形索引（fractal tree index），既有B-Tree的很多优点，也避免了B-Tree的一些缺点。

7.索引的优点与选择

索引优点：
1. 索引大大减小了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

索引的选择：
索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单全表扫描更高效。对于中大型表，索引就非常有效。对于特大型的表，建立和使用索引的代价随之增长，这种情况下，需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录匹配，例如分区