《高性能MySQL》创建高性能的索引

创建高性能的索引

一.索引基础

select first_name from sakila.actor where actor_id = 5;

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

索引的类型 

B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引。Archive是个例外。

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，当InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

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

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。上图绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查询效率会非常高。

CREATE TABLE persion(
     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)
)
请注意，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。两个人的姓和名都一样，则根据他们的出生日期来排列顺序。

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

全值匹配

　　全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人

匹配最左前缀

 　　前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。

匹配列前缀

　　也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。

匹配范围值

　　例如查找姓Allen和Barrymore之间的人。

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

　　例如查找所有姓为Allen，并且名字开头是K开头的人

只访问索引的查询

　　B-Tree通常可以支持“只访问索引的查询”

因为索引树种的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，B-Tree可以按照某种方式查找，那么也可以按照这种方式用于排序。

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

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上边的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列不是最左列。
• 不能跳过索引中的列。例如上边的索引无法用于查找姓为Smith并且在某个特定日期出生的人
• 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。例如：where last_name = 'Smith' AND first_name like 'J%' AND dob = '1987-12-12'，这个查询只能使用索引的前两列。

哈希索引

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

在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

例子：
CREATE TABLE teshash(
   fname VARCHAR(50) NOT NULL,
   lname VARCHAR(50) NOT NULL,
   KEY USING HASH(fname)
)ENGINE=MEMORY;
假设索引使用的假想的哈希函数f()，它返回下面的值：
f('Arjen') = 222
f('Baron') = 333
则查询SELECT * FROM teshash WHERE fname = 'Baron';
MySQL先计算Baron的哈希值，并使用该值寻找对应的记录指针。 因为f('Baron')=333，所以MySQL在索引中查找333，可以找到对应行的指针，最后一步是比较值是否为Baron，以确保就是要查找的行。

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

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

因为这些限制，哈希索引只使用某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。举个例子，在数据仓库应用中有一种经典“星型”schema，需要关联很多查找表，哈希索引就非常适合查找表的需求。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。

思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事儿，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的Where字句中手动指定使用哈希函数。