Schema的优化和索引 - 索引的基础

　　索引是数据结构。它能帮助MySQL更有效率的获取数据。它对于好的性能是决定性的因素，但是人们常常忘记使用它们或者没有正确的理解它们，因此在现实的例子中，索引总是引起性能问题。这就是我们把索引放在这本书的较早部分的原因。甚至把索引这章放在语句优化之前。

　　索引（在MySQL中也叫做键（keys））在数据越来越大的时候，变的格外重要。数据量小，没有高的负载的情况下，在没有索引的情况下，数据库也可以表现的很好，但是随着数据量的增大，性能下降还是非常厉害的。

　　最简单的理解MySQL索引的工作方式就是思考下这本书的索引。你可以通过此书的索引，很容易找到你需要的内容。并且可以知道内容的页码。

　　MySQL使用索引的方式也是相类似的。它查找了值的索引数据结构。当它找到一个匹配，它就能找到包含这个匹配的行。假如你运行一下的语句：

　　mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5;

　　在actor_id上会有一个索引，所以MySQL使用索引来查找actor_id为5的行。换句话说，它通过索引来查找值，并且返回包含这些指定值的任意的行。

　　一个索引包含的值是来自于一个表中指定的列或者一些列。如果你索引超过1列的话，列的顺序就尤为重要了。因为MySQL只高效的搜索最左边前缀的索引。你将会看到，在两个列上创建一个索引和为两个列分别创建单独的索引是不同的。

　　索引的类型

　　索引有许多类型，它们都是针对不同的用途而被设计的。索引是在存储引擎中实现的，而不是服务器。因此它们并没有标准化：在每个引擎中，索引工作的方式都有所不同，并且并不是所有的存储引擎支持所有种类的索引。即使这些存储引擎支持索引的类型相同，在底层的实现也是有所不同。

　　让我们来看看MySQL所支持的索引类型，它们的优势和缺点。

　　B-Tree索引

　　当人们讨论索引而没有说明它的类型的时候，往往指的是B-Tree索引。它用B-Tree数据结构存储了数据。大部分MySQL存储引擎都支持这种类型。Archive引擎是个例外：MySQL5.1之前还不支持索引，现在可以支持对AUTO_INCREMENT列添加一个索引了。

　　对于这些索引，我们使用术语B-TREE，那是因为在CREATE TABLE和其他语句中MySQL都是这么使用它们的。然而，存储引擎内部可能使用不同的存储结构。比如，NDB集群存储引擎使用T-TREE的结构来实现这些索引的。

　　存储引擎在硬盘上存储B-TREE索引的方式也各不相同。这些方式会影响性能。举个实例，MyISAM使用前缀压缩技术使索引变得更小。然而InnoDB并没有压缩索引，因为它的一些优化功能不能使用压缩的索引。MyISAM还通过行所存储的物理位置来引用这个已索引的行，但是InnoDB是通过主键值来引用它们的。每种方式都有各自的优点和缺点。

　　B-Tree的普通思路是，有序的存储所有的值，并且每个叶的页面(leaf page)和根节点都是相同的距离。下面的图是抽象的B-Tree索引的展现。这和InnoDB索引的工作方式有点相吻合。（InnoDB使用的是B+TREE结构）。MyISAM使用不同的结构，但是概念上是相似的。

　　Hash索引

　　Hash索引是建立在Hash table至上的，并且它只对准确查找有效，也就是说，必须查找索引上的每一个列。对于每个行，存储引擎计算了索引列的Hash code。这个hash code是非常小，并且对于其他行不同的数值，这些Hash code也是不相同的。在索引中存储了hash code并且在hash table中存储了一个指针指向每一行。

　　在MySQL中，只有Memory存储引擎支持显式的Hash索引。虽然Memory表也可以使用B-Tree索引，但是它们是默认的索引类型是Hash索引。Memory引擎支持非唯一的Hash索引，这在数据库领域中是不寻常的。如果多个值有相同的Hash code，那么索引就会在在相同的hash table的实体中，使用一个linked list保存这些值所属行的指针。

　　说一个例子吧，假设表结构如下

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

　　包含的数据如下：

mysql> SELECT * FROM testhash;
+--------+-----------+
| fname | lname |
+--------+-----------+
| Arjen | Lentz |
| Baron | Schwartz |
| Peter | Zaitsev |
| Vadim | Tkachenko |
+--------+-----------+

　　假设我们有一个hash的函数叫f(),它会返回如下值（这些都是例子，并不是真实的值）：

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

　　索引的数据结构为

Slot Value
2323 Pointer to row 1
2458 Pointer to row 4
7437 Pointer to row 2
8784 Pointer to row 3

　　空间（R-TREE）索引

　　MyISAM支持空间索引。你可以把它和地图类型一起使用。比如GEOMETRY。不像B-Tree索引，它并不要WHERE条件来操作左边前缀的索引。它通过所有的维度来索引数据。因此，查找可以有效地使用任意维度的组合。然而你必须要使用MySQL GIS函数。比如MBRCONTAINS( )。

　   全文索引

　　FULLTEXT是MyISAM表的一个特殊的索引类型。它在文本中找到一个关键词，而并不是在索引中直接比较着两个值。Full-text搜索不同于其他类型的匹配。它有许多细微的差别。比如stopwords，stemming,plurals以及Boolean searching。

　　这些基本都和搜索引擎相关了。

　　给一个列加全文索引并不能消除B-Tree索引的值。全文索引是匹配而不是WHERE条件的操作。

　　我们会在全文搜索中，详细讨论。