MySQL的索引及索引优化

创建高性能的索引

索引是 存储引擎用于快速找到记录的一种数据结构。 索引优化应该是对查询性能优化最有效的手段。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。

索引基础

如果想在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询：

SELECT first_name FROM actor WHERE actor_id = 5;

如果在actor_id列上建有索引，则MySQL将使用该索引找到actor_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两列的索引，和创建两个只包含一列的索引是大不相同的。

索引类型

B-Tree索引

B-Tree索引使用B-Tree数据结构来存储数据。存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。

例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如 MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

B-Tree索引能够加快访问数据的速度，因为存储引擎不在需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。

假设有如下数据表：

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

对于表中的每一行数据，索引中包含了name和birthday列的值

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。这些索引对如下类型的查询有效：

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

下面是关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面的例子中无法用于查找名字，也无法查找某个特定生日的人。也无法查找姓氏中以某个字母结尾的人
• 不能跳过索引中的列。即无法查找姓为张并且在某个特定日期出生的人。如果不指定名则只能使用索引的第一列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如，查询 WHERE last_name = ‘张’ AND first_name LIKE ‘S%’ AND birthday = ‘2000-01-01’，这个查询只能使用索引的前两列，因为Like是一个范围查找。如果范围查找列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

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

哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

在MySQL中，只有Memory引擎显式支持哈希索引，这也是Memory引擎的默认索引类型，同时也支持B-Tree索引。

假设有下表：

CREATE TABLE testHash (
	fname VARCHAR(50) NOT NULL,
  	lname VARCHAR(50) NOT NULL,
  	KEY USING HASH(fname)
)ENGINE=MEMORY;

且有如下几条数据：

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

假设索引使用的哈希函数 f()，它返回下面的值（非真实数据）：

f(‘Arjen’)=2323、f(‘Baron’)=7437、f(‘Peter’)=8784、f(‘Vadim’)=2458

则哈希索引的数据结构如下：

槽（Slot）	值（Value）
2323	指向第1行的指针
2458	指向第4行的指针
7437	指向第2行的指针
8784	指向第3行的指针

执行如下查询：

SELECT lname FROM testHash WHERE fname='Peter'

MySQL首先计算’Peter’的哈希值，并使用该值寻找对应的记录指针。f(‘Peter’)=8784，所以MySQL在索引中查找8784，找到指向第3行的指针，比较第3行的值是否为’Peter’，以确保正确查找。

因为索引自身只需存储对应的哈希值，所以索引的结构是否紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
• 哈希索引只支持等值比较查询，包括 =、IN()、<=>。也不支持任何范围查询
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，哈希冲突越多，代价越大

InnoDB引擎有一个特殊的功能**“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。**

如果需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身就很长。一般情况下的查询：

SELECT id FROM url WHERE url="http://www.mysql.com";

若删除原来的URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做索引，如：

SELECT id FROm url WHERE url="http://www.mysql.com"
	AND url_crc=CRC32("http://www.mysql.com")

这样做的性能非常高，因为MySQL优化器会使用这个选择 性很高而体积很小的基于url_crc列的索引来完成查找（案例中的索引值为1560514994）。即使有多个记录相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。这样实现的缺陷是需要维护哈希值，可以手动维护，也可以触发器实现。

如果数据表非常大，CRC32()会出现大量的哈希冲突。而处理哈希冲突的方式就是当使用哈希索引进行查询的时候，必须在WHERE子句中包含常量值，即url_crc = CRC32(“xxx”)的部分。

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。

必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。

全文索引

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

在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通额WHERE条件操作。

索引的优点

索引可以让服务器快速地定位到表的指定位置，索引还有其他的附加作用。

B-Tree索引按照顺序存储数据，所以可以用来做ORDER BY和GROUP BY操作。

总结索引的优点：

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

索引是最好的解决方案吗？

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，可以使用分区技术直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。

如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到地某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录”那个用户信息存储在那个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。