高性能MySQL知识总结（五）——创建高性能的索引

（一）、5.1索引基础

5.1.1 索引的类型

• 索引是在存储引擎层而不是服务器层实现的。所以索引没有统一的索引标准：不同存储引擎的索引的工作方式并不一样。

B-Tree索引

1. 当人们谈论索引的时候，如果没有特别指明类型，那么多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引。
2. 存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如，MyISAM索引通过数据的物理位置引用被引用的行，而InnoDB则根据主键引用被索引的行。
3. B-Tree通常意味着所有的值都是按顺序存储的，并且米一个叶子页到根的距离相同。图中展示了B-Tree索引的抽象表示。
   
4. B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图中并未画出）开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。
5. 叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点也。其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。
6. 可以使用B-Tree索引对的查询类型。B-Tree索引适用于全键值、键值范围或间前缀查找。其中键前缀查找只适合根据最左前缀的查找。前面所述的索引对如下类型的查询有效。
   

全值匹配

• 全值匹配指的是和索引中的所有列进行匹配

匹配最左全值匹配

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

匹配列前缀

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

匹配范围值

• 例如前面调到的索引可用于查找在Allen和Barrymore之间的人。这里也只使用了索引的第一列。

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

• 前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim、Karl等）的人。即第一列last_name全匹配，第二列first_name范围匹配。

只访问索引的查询

• B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

7. 因为索引树中的节点是有序的。所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作。只要ORDER BY 子句也满足前面的几种类型，那么索引就可以满足这个排序需求。
8. 下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如前面例子中无法匹配名字为Bill的人，也无法找到特定生日的人。因为无法精确匹配第一列以外的列。
• 不能跳过索引中的列。也就是如果不指定名(first_name)，则MySQL只能使用索引的第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如语句WEHERE last_name = ‘Smith’ AND fitst_name LIKE ‘J%’ AND dob = ‘1976-12-23’。这种语句中只能使用索引的前两列，因为这里LIKE是一个范围条件。

哈希索引

1. 哈希索引(hash index)基于哈希表实现，只有精确匹配所有列的查询才有效。
2. 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
3. 只有Memory引擎显式支持哈希索引。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
4. 假设有如下表：

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

INSERT INTO testhash VALUES
('Arjen','Lentz'),('Baron','Schwartz'),
('Peter','Zaitsev'),('Vadim','Tkachenko');

5. 哈希索引也拥有一些限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据不是按照索引值顺序存储的，所以无法用于排序
• 哈希索引不支持索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。例如，在数据列（A,B）上建立哈希索引，如果查询的数据只有A列，则无法使用该索引。
• 哈希索引只支持等值比较查询，包括=、IN()、<=>,也不支持任何范围查询
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却又相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行比较，直到符合条件。
• 如果哈希冲突很多的话，维护的代价较高。

6. InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再建立一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点。

空间数据索引

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

全文索引

1. 全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。
2. 在相同的列上同时建立全文索引和基于值的B-Tree索引不会有冲突。

（二）、5.2 索引的优点

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

（三）、5.3 高性能的索引策略

1. “独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。否则会导致MySQL无法使用索引。
2. 例如下面展示两种错误的用法

--两种错误的用法
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
--正确的用法
SELECT actor_id FROM sakila.actor WHERE actor_id = 4;

4. 我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧

5.3.2 前缀索引和索引选择性

1. 有时需要索引很长的字符串，这时需要耗费很大的空间。我们通常可以索引开始部分的字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。但是如果索引字符不够，也会导致一些多余的行出现。
2. 诀窍在于选择足够长的前缀保证较高的选择性，但是又不能太长.下面举一个例子。

• 从表city中生成一个实例表

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
UPDATE sakila.city_demo
SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1)
SELECT Count(*) FROM sakila.city_demo;

• 首先进行全值匹配：

SELECT COUNT(*) AS cnt, city AS pref
FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

• 然后从3个前缀字母开始：（会发现数目在变大）

SELECT COUNT(*) AS cnt, LEFT(city,3) AS pref
FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

• 然后从7个前缀字母开始：（会发现数目比3的时候要小一些）

SELECT COUNT(*) AS cnt, LEFT(city,7) AS pref
FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

3. 下面演示如何创建前缀索引：

ALTER TABLE sakila.city_demo ADD KEY (city(7));

5.3.3 多列索引

1. MySQL5.0和更新版引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
2. 例如表film_actor在字段fild_id和actor_id上各有一个单列索引，在新版本中这个查询能够同时使用两个单列索引进行扫描，并将结果进行合并。

SELECT film_id, actor_id FROM sakila.film_actor
WHERE actor_id = 1 OR film_id = 1;

4. 这种算法有三个变种：OR条件的联合(union)，AND条件的相交(intersection)，组合前两种情况的联合及相交。

5.3.4 选择合适的索引列顺序

1. 正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。
2. 索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY,GROUP BY和DISTINCT等子句的查询需求
3. 一条经验法则：将选择性最高的列放到索引列的最前列

5.3.5 聚簇索引

1. 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
2. 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。
3. 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
4. 下图展示了聚簇索引是如何存放的。叶子页存放了行的全部数据，节点页只存放了索引列。
   
5. InnoDB通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

InnoDBheMyISAM的数据分布对比

在InnoDB表中按主键顺序插入行

6. 如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。
7. 这种代理主键最好避免随机的聚簇索引，否则性能会十分糟糕。
8. 下图展示的是自增主键列的聚簇索引插入操作：
   
9. 下图展示的是随机主键列的聚簇索引插入操作：
   

5.3.6 覆盖索引

1. MySQL可以使用索引来直接获取列的数据，这样就不需要读取数据行。如果一个索引包含（或者说是覆盖）所有需要查询的字段的值，我们就称为“覆盖索引”。
2. 覆盖索引的好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量
• 因为索引是按照列值顺序存储的（至少在单个页内是如此的），所以对于I/O密集型的范围查询会比随机从硬盘读取每一行数据的I/O要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖与操作系统进行缓存，因此要访问数据要经过操作系统的调用，这会导致严重的性能问题。
• 覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免队逐渐索引的二次查询。
• 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

3. 当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的EXTRA列可以看到“Using index”的信息。例如，表sakila.inventory有一个多列索引(store_id,film_id)。MySQL如果只需要访问这两列，就可以使用覆盖索引进行查询。

   EXPLAIN SELECT store_id, film_id FROM sakila.inventory;
   

   
4. sakila.actor使用InnoDB存储引擎，并在last_name字段有二级索引，虽然该索引的列不包括主键actor_id，但也能够用于对actor_id做覆盖查询（覆盖索引在二级查询上的应用）

EXPLAIN SELECT actor_id,last_name FROM sakila.ACTOR WHERE last_name = 'HOPPER';

5.3.7 使用索引扫描来做排序

1. MySQL有两种可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为"index"，则说明使用了索引扫描来做排序。

2. 扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录都回表查询一次。这样比顺序扫描全表要更慢。

3. 只有当索引的列顺序和ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。

4. ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，无法使用索引排序。（如果前导列为常量时，可以不满足索引的最左前缀的要求）。例如下面的展示：

   EXPLAIN SELECT rental_id,staff_id FROM sakila.rental
   WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id;
   

5.3.8 压缩（前缀压缩）索引

1. MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。
2. MyISAM压缩每个索引块的方法是，先保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如索引块的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7，ance”这样的形式。
3. 压缩块使用更少的空间，代价是某些操作可能更慢。因为每个压缩前缀都依赖前面的值。

5.3.9 冗余和重复索引

1. MySQL允许在相同列上创建多个索引，无论是有意还是无意的。MySQL需要单独维护重复的索引。
2. 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。例如下面的代码，MySQL的唯一限制和主键限制都是通过索引来实现的。所以等于同时创建了三个索引。

CREATE TABLE test(
	ID INT  NOT NULL PRIMARY KEY,
	A INT NOT NULL,
	B INT NOT NULL,
	UNIQUE(ID),
	INDEX(ID)
)ENGINE=INNODB;

3. 冗余索引和重复索引有一些不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引了，因为这只是前一个索引的前缀索引，因此索引(A,B)也可以当做索引(A)来使用。
4. 表中的索引越多，插入速度就会越慢。

5.3.11 索引和锁

1. 索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看对性能都有好处。
2. InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量，但这只有当InnoDB在存储引擎能够过滤掉所有不需要的行时才有效。