MySQL都有哪些索引呢？

索引基础

SELECT first_name FROM 表 WHERE id=5;

对于上面这个查询，如果id列上有索引。则MySQL将使用该索引找到id=5的行，也就是说，mysql现在索引上按值查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。

索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的。

下面介绍mysql支持的索引类型：

B-Tree索引

当我们在谈论索引的时候，我们在谈论什么呢？:joy:皮一下

当我们在谈论索引的时候，如果没有特别的指明类型，那么多半都是B-Tree索引，他使用B树数据结构来存储数据。当然更多的使用的是B+树的数据结构，你比如说Innodb。

B树索引为什么能够加快数据的访问速度呢，这就打到我的甜点位了，因为存储引擎不需要进行全表扫描了呀，取而代之的是从索引的根节点开始进行搜索，并且每个子节点都有子节点页的上限和下限，最终引擎要么就是找到，要么就是找不到。还有一个点B+树是所有节点信息都在叶子节点保存着呢

请注意：索引对多个值进行排序的依据是你在建表语句中定义索引时列的顺序。

你比如说如下的数据表：

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,frist_name,dob)
);

:cake:咱就是说，这个key，我又想提几句

MySQL中有四种Key: Primary Key, Unique Key, Key 和 Foreign Key。

除了Foreign Key最好理解外，其他的都要区分一下。

剩下的三种都要在原表上建立索引。

Primary Key和Unique Key之间的区别网上说的最多。Primary Key的提出就是为了唯一标示表中的字段，就像我们的身份证号一样。此外，所有字段都必须是not null的Unique Key则是为了保证表中有些字段是唯一的。比如有些单位领导叫“张三”，所以下面招人的时候是决不可招一个有同样名字的。

至于Key吗，网上说的比较少。其实某个字段标记为Key，是不能保证这个字段的值在表中是唯一出现的。它的目的就是建立索引。

之前所述的索引对下面的类型的查询都是有效的：

• 全值匹配

  全值匹配指的是和索引中的所有列进行匹配，例如你要查找一个姓名，出生日期的信息，就可以完全走索引。

• 匹配最左前缀、

  就是查找last_name 的信息可以走索引。

• 匹配列前缀

​ 也可以值匹配某一列的值的开头部分。比如说查找所有以J开头的last_name的人，就是可以使用索引的第一列。

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

​ 就是这个索引我们可以第一列last_name 全匹配，但是first_name进行一个范围匹配，这样的操作

• 只访问索引的查询

  B树通常是支持只访问索引的查询，即查询只需要访问索引，而无须访问数据行。

因为呀，这个索引树中的节点是有序的，所以处理按值查找外，索引还可以用于查询中的order by 操作，一般来说，

如果B树可以按照某种方式查找到值，那么也可以按照这种方式用于排序，所以，如果orderby 子句满足我们刚刚上面提到的查询类型，那么这个索引也可以满足对应的排序需求。

:kick_scooter:下面呢，我就要你介绍一些B树索引的限制了：

• 如果不是按照索引的最左列开始查找，则无法使用索引。（比如姓氏以某个字母结尾的这种也是无法使用索引的）

• 不能跳过索引的列，就像上面的例子，你不能跳过first_name 去查找last_name 和dob

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。、

  WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'
  

  这个查询就只能使用索引的前两列，因为like是一个范围条件。

  所以，如果范围查询列值的数量有限，那么就可以通过使用多个等于条件来代替范围条件。

哈希索引

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

在MySQL中只有memory引擎显示支持哈希索引。这也是memory引擎表的默认索引类型。

select name FROM 表  WHERE name=‘peter’;

哈希索引查询过程：

MySQL先计算’peter’的哈希值,并使用该值寻找对应的记录指针。f（peter）=8784 。所以mysql在索引中查找8784。就可以找到一个指向表的第三行的指针，最后一步，比较第三行的值是不是peter，以确保就是要查找的行。

因为索引只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度十分快。

但是

哈希索引，也是有他自己的限制。

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引的数据并不是按照哦索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。
• 哈希索引只支持等值比较查询，不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突。当哈希冲突出现的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较。直到找到所有符合条件的行。
• 如果哈希冲突特别多的时候，一些索引维护的操作的代价也会特别高。

创建自定义哈希索引

如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，这样同样可以使用一些哈希索引的便利。

思路：

在b树的基础上创建一个伪哈希索引，这和真正的哈希索引不是一回事，因为还是使用b树进行查找，但是他使用的是哈希值不是键本身进行索引查找，你需要做的是在查询的WHERE子句中手动指定使用哈希函数。

比如：

我们下面这个查询url的语句：

select id FROM url where url='www.baidu.com';

如果我们删除原来url列上的索引，而新增一个url_crc列，使用crc32做哈希，就如下面:

select id FROM url where url='www.baidu.com'
AND url_crc=CRC32('www.baidu.com');

你这样做，他的性能就很高，有多高，有三四层楼那么高。

因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。即使多个记录有相同的索引值，查找依旧很快，只需根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回的对应行。

当然这种实现的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。

如果使用这种方式，你也切记不要使用sha1（）和md5（）作为哈希函数因为这两函数计算出来的哈希值是特别长的字符串，会浪费大量空间，比较时也会比较慢。

处理哈希冲突

就像上面那个哈希索引查询，你是必须包含常量值的在WHERE子句中，不然哈希值冲突你就没法搞了呀：

select id FROM url where url='www.baidu.com'
AND url_crc=CRC32('www.baidu.com');

空间索引

myisam表支持空间索引，可以用作地理数据存储。和b树索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效的使用任意维度来组合查询。必须使用MySQL的GIS相关函数如mbrcontains（）等来维护数据。

全文索引

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

其他索引类别

还有许多第三方存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分形树索引。这是一类较新开发的数据结构，既有b树的很多优点，也避免了b树的一些缺点。

索引的优点

索引可以让服务器快速的定位到表的指定位置，但是这并不是索引的唯一作用。

最常见的b树索引，按照顺序存储数据，所以mysql可以用来做order by和group by 操作。因为数据是有序的，所以b树也会将相关的列值存储在一起。最后 ，因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询：

总而言之，言而总之，有着如下的优点：

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

:question:思考一个问题，索引时最好的解决方案吗？

其实不见的，对于非常小的表，大多是扫描全表是更加高效的，对于中到大型表索引就很有效，对于特大型表，建立和使用索引的代价将随之增长，这种情况下就要使用分区技术了，这个我们后面的文章介绍。