mysql优化（二）---索引

一、什么是索引

我们在看一本书的时候会先查看书的目录，如果想看特定的一个章节，只要在目录中找到该章节的页码，就快速定位到该章节的位置。
在mysql中存储引擎用类似的方法使用索引来快速查找到我们需要的数据。

二、索引的类型

索引有很多种类型，可以为不同的场景提供更好的性能。
在mysql中索引是在存储引擎层而不是在服务器层实现的。不同的存储引擎支持的索引类型也不一样。

1.B-Tree 索引

B-Tree索引中包含了定义索引列的值
B-Tree索引适合用于全键值、键值范围或键值前缀查找。
该索引是有序的，会根据定义索引的顺序、首字母、时间排列
假设有如下表：

create table user (
		last_name varchar(50) not null,
		first_name varchar(50) not null,
		birthday date not null,
		gender tinyint(1) not null,
		key(last_name,first_name,birthday)
);

user表中建立了3个列组成的联合索引。
对一下类型的查询有效：
全值匹配
可以和索引中的所有列进行匹配，例如上表中可以查询姓名为Cuba Allen 出生与1991-08-07的人。

匹配最左前缀
即匹配索引的第一列last_name

匹配列前缀
也匹配列的开头部分。例如可以查找以J开头的姓的人（匹配索引的第一列）

匹配范围值
因为索引是有序的，也可查找在某一个范围的数据

精确匹配某一列并范围匹配另一列
如可以查找所有姓为Allen，并且名字是字母K开头的人。

B-Tree索引的限制

• 如果不是按索引的最左列开始查找，则无法使用索引。上面的例子中不能直接查找first_name和birthday的人，因为第一索引列是last_name.
• 不能跳过索引中的列。上面的例子中的索引无法查找指定last_name和birthday的人，如果不指定first_name，则mysql只能使用索引的第一列
• 如果查询中有某个列是范围值，则右边的列无法使用索引
  如select * from user where last_name=‘sam’ and first_name like ‘J%’ and birthday=‘1976-01-08’;这个查询只能使用索引的前两列birthday无法走索引。

2.哈希索引

哈希索引是基于哈希表实现的，只有精确匹配所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，并存储在索引中，同时在哈希码中保存指向每个数据行的指针。
哈希索引的限制

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据不是顺序存储的，所以无法用于排序
• 哈希索引不支持部分索引列匹配查询，因为哈希索引是使用索引列的全部内容来计算哈希值的。如在数据（A，B）列上建立哈希索引，如果只查询A列数据，无法使用哈希索引。
• 哈希索引只支持等值比较查询，包括=、in()、<=>。不支持任何范围查询如>、<等。
• 如果索引列的值有很多相同的时候容易出现哈希冲突。

3.全文索引

全文索引是一种特殊的索引，它查找的是文本中的关键字。
全文索引更类似于搜索引擎，而不是简单的where条件匹配。