MySQL索引
索引是这样工作的：先在索引中找到对应的值，然后根据匹配的索引记录找到对应的行数据。例如此查询，select nick_name from user_info where user_id = 100;如果user_id列上建有索引，MySQL将使用该索引找到user_id为100的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。
一、B-Tree索引
谈及索引，如果没有明确指定，默认是B-Tree索引。
B-Tree索引是有序的，适用于全键值、键值范围或键最左前缀查找。
假设有如下数据表：
create table blog_user (
last_name varchar(40) not null,
first_name varchar(40) not null,
dob date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
);
全值匹配：
select * from blog_user where last_name = 'Allen' and first_name = 'Kim' and dob = '2019-10-16';
匹配最左前缀：
select * from blog_user where last_name = 'Allen';
匹配列前缀：
select * from blog_user where last_name like 'A%';
匹配范围值：
select * from blog_user where last_name between 'Allen' and 'Barrymore';
精确匹配某一列并且范围匹配另外一列：
select * from blog_user where last_name = 'Allen' and first_name like 'K%';
只访问索引的查询：
select last_name, first_name, dob from blog_user where last_name like 'A%'; //查询只需要访问索引，无需回表
错误使用索引的例子：
select * from blog_user where first_name = 'Kim'; //未按照索引列的最左边开始查找
select * from blog_user where last_name = 'Allen' and dob = '2019-10-16'; //跳过了first_name索引列
Select * from blog_user where last_name = 'Smith' and first_name like 'J%' and dob = '2019-10-16'; //如果查询中有某个列的范围查询，则其右边的列都无法使用索引查找。这个查询只能使用索引的前两列。
索引同样适用于order by操作。
二、Hash索引
Hash索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引计算一个哈希码，哈希码是一个比较小的值，并且不同键值的行计算出来的哈希码也不一样。Hash索引将所有哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。假如有哈希冲突，会使用链表解决冲突。
InnoDB不支持Hash索引，Memory支持。Hash索引只包含Hash值和行指针，不储存字段值，所以不能使用索引中的值来避免读取行（回表），不过访问内存中行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
假设有如下数据表：
create table blog_hash (
last_name varchar(40) not null,
first_name varchar(40) not null,
key using hash(first_name)
) engine = memory;
select * from blog_hash where first_name = 'Peter';
MySQL先计算’Peter’的哈希值，并使用该值寻找对应的记录指针，最后比较指针指向的行的值是否为’Peter’，以确保就是要查找的行。
Hash索引的限制：
- Hash索引是无序的，所以无法用来排序。
- Hash索引不支持部分索引列匹配查找。
- Hash索引只支持等值比较查询，不支持范围查询。
- 哈希冲突很多的话会影响性能。
三、空间索引
MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。
四、全文索引
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样，不能用where匹配。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于match against操作，而不是普通的where条件操作。
索引的优点：
- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机I/O变为顺序I/O。
参考资料：《高性能MySQL第三版》
