《高性能MySQL》读书笔记——第五章：创建高性能的索引

1 索引基础

索引是存储引擎用于快速找到记录的一种数据结构。也可以叫做键（key）
在Mysql中，存储引擎在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。如下例

SELECT first_name FROM sakila.actor WHERE actor_id = 5;

如果在actor_id列上建有索引，则Mysql根据索引找到actor_id为5的行。

Mysql先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。所以列的顺序也很重要。因为Mysql只能高效的使用索引的最左前缀列。创建多个列的索引和单列索引区别很大。

1.1 索引的类型

索引有很多种类型，在不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

1.1.1 B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。

实际上很多存储引擎使用的是B+Tree，即每-一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

大多数MySQL引擎都支持这种索引。Archive 引擎是一个例外: 5.1之前Archive不支持任何索引，直到5.1才开始支持单个自增列(AUTO INCREMENT)的索引。
我们使用术语“B-Tree" ,是因为MySQL在CREATE TABLE和其他语句中也使用该关键字。
不过，底层的存储引擎也可能使用不同的存储结构，例如，

• NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引，即使其名字是BTREE ;
• InnoDB则使用的是B+Tree,各种数据结构和算法的变种不在本书的讨论范围之内。
  存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，
• MyISAM使用前缀压缩技术使得索引更小；再如MyISAM索引通过数据的物理位置引用被索引的行
• 但InnoDB则按照原数据格式进行存储；而InnoDB则根据主键引用被索引的行。
  B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下图展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。
  
  B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。
  叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页(不同引擎的“指针”类型不同)。上图中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。
  B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在 一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率会非常高。

1.1.2 哈希索引

哈希索引是基于哈希表实现的，只有精准索引索引列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希值，所有的哈希值都存储在索引中，同时在哈希表中保存只想每个行的指针。
如果存在哈希值相同的情况，即“冲突”，会形成链表分别存放各自行的指针。
如下例：


上例每个槽的编号顺序是顺序的，但是值中的行的顺序是不一致的。
如下执行查询语句：

SELECT lname FROM testhash WHERE fname = 'Peter'

查询步骤：

1. 计算出‘Peter’的哈希值；
2. 并使用该值找到对应的记录指针；
3. 根据该指针找到对应行中对应值是否为‘Peter’
4. 如果相等即查询成功。

哈希索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引的值是根据指针而不是，所以不能用来排序。
• 哈希索引使用的是索引列的全部内容来计算哈希值的。所以不支持部分索引列匹配查找。
• 哈希表只支持查询条件为点，而不是范围的比较查询，例如WHERE price > 100是不行的。

哈希索引的优化：

• 减少冲突

支持哈希索引的引擎：Memory，NDB
InnoDB的自适应哈希索引：当InnoDB注意到某些索引值被使用的过于频繁，它会在内存中基于B树索引之上再创建一个哈希索引，这样就可以使用哈希索引的优点：快速的哈希查找。**注意：**这些操作都是InnoDB自己完成的功能，用户无法配置但是可以完全关闭该功能。

1.1.2.1 创建自定义哈希索引

**目的：**如果存储引擎不支持哈希索引，可以模拟InnoDB来创建哈希索引。
**思路：**在B树基础上创建一个伪哈希索引。这个树使用的哈希值而不是键本身进行索引查找。需要做的就是在查询的WHERE子句中手动指定使用哈希函数。
示例：
例：存储大量URL，并需要根据URL进行搜索查找。

# 正常情况下
SELECT id FROM url WHERE url="http://www.mysql.com"

CREATE TABLE pseudohash (
id int unsigned NOT NULL auto_increment,
url varchar(255) NOT NULL,
url_CIC int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY(id)
);