005 mysql之如何创建高性能索引

前言：知识点大部分来自于《高性能MySQL》，大家有空的话可以知己去看原著第五章。

一.索引基础

索引类型：大致有四种。

a.B-free索引

B-Tree所有的值都是按顺序来存储的，并且每一个叶子页到根的距离相同。InnoDB的B+Tree的如图：
B-Tree是按照顺序来存储的，所以很适合查找范围数据。
B-Tree对如下的查询有效：

• 全值匹配：和索引中所有列进行匹配，例如前面提到的所以可用于查询姓名为CuBaAllen，出生日期为1960-01-01的人。
• 最左匹配：可用于查找所有姓为Allen的人，即只使用到索引的第一列。
• 匹配列前缀：可以只匹配某一列的开头部分。
• 匹配范围值：可用于查找在Allen和Barrymore之间的人。
• 精确匹配某一列并范围匹配另一个列：
• 只访问索引：即查询只需要访问索引，无须访问数据行，即索引覆盖。
• 查询还可以用在ORDER BY操作，前提是ORDER BY子句满足前面列出的集中查询类型。

B-Tree索引的限制：

• 必须是最左匹配
• 不能跳过索引中的列（联合索引）：例如联合索引（A，B，C），不能只用B来索引
• 范围查询某个列时，则其右边的列都无法使用索引

b.哈希索引

基于哈希表实现，只有精确匹配索引所有列。对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希码是一个较小的值，不同的键值算出的哈希码不一样。哈希索引将所有的哈希码存储在索引中，同时哈希表中保存每个数据行的指针。因为索引自身只需要存储对应的哈希值，所以索引十分紧凑，所以哈希索引查找非常快。
限制：

• 哈希索引只保存哈希码和行指针，不能只用索引覆盖。
• 无法用于排序。
• 无法支持部分匹配。
• 只支持等值比较，包括=，IN()，<=>，不支持范围查询。
• 哈希码可能有冲突，此时行指针指向一个链表，当出现冲突时，存储引擎必须遍历链表的所有行进行比较。
• 哈希冲突很多的话，一些索引维护代价很会很高

创建自定义的哈希索引：在B-Tree上创建一个伪哈希索引，比如现在要存储一个URL，如果将URL当做索引，索引会很大，所以我们可以将URL计算成一个哈希值，将哈希值当做B-Tree的索引，可以使用CRC32做哈希。

c.空间索引

MyISAM支持空间索引。和B-Tree不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。

d.全文索引

全文索引是一种特殊索引。是查找列的关键字，而不是比较索引中值，全文索引和其他索引匹配方式完全不一样，如停用词，词干，复数，布尔搜索等。全文搜索更类似搜索引擎做的事，而不是简单的where，在相同的列上创建全文索引和基于值得B-Tree不会冲突，全文索引适用于match against操作。

二.索引优点

最常见的是B-Tree索引，所有的值都是顺序存储，所以可以用来做ORDER BY 和 GROUP BY等操作。因为B-Tree存储实际的列值，所以某些查询只需要索引上的值就够了，不需要回表查询，速度非常快。
总结下来索引有三个优点：

• 索引大大减小了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机IO变为顺序IO

这里引进一个三星索引的概念，来自于《数据库索引的设计与优化》
三星索引：

• 索引将相关的记录放到一起（一星）
• 如果索引中的数据顺序和查找的排序顺序一致（一星）
• 如果索引中的列包含了查询中需要的全部列（一星）

三.高性能的索引策略

这里主要讲B+free索引

1.独立的列

索引列不能是表达式的一部分，例如下面的两个查询就用不到索引

select * from patients where age + 1 = 100
select * from patients where left(createtime, 10) = '2018-12-25'

2.前缀索引和索引选择性

对于BLOB和TEXT以及很大的VARCHAR，MySQL不允许将整个值当做索引，所以可以将值的前缀部分当做索引。
选择适应长度的前缀很关键。可以如下做参考：

select 
count(distinct left(word,3)) / count(*) as sel3,
count(distinct left(word,4)) / count(*) as sel4,
count(distinct left(word,5)) / count(*) as sel5,
count(distinct left(word,6)) / count(*) as sel6,
count(distinct left(word,7)) / count(*) as sel7,
count(distinct left(word,8)) / count(*) as sel8,
count(distinct left(word,9)) / count(*) as sel9,
count(distinct left(word,10)) / count(*) as sel10,
count(distinct left(word,11)) / count(*) as sel11,
count(distinct word) / count(*) as selall
from xpatientindexs;

查询结果：
从结果我们可以看出，当前缀长度为11时已经很接近全值作为索引的选择性了。
但是前缀索引有一个缺点：无法用索引来ORDER BY和GROUP BY，也无法使用索引覆盖。
与前缀索引相对于的是后缀索引，但是MySQL原生不支持后缀索引，可以将字符串反转来存储，然后再做成前缀索引。
这里我们举个例子：身份证一般是前面是省区信息，后面才是个人信息。这时候如果前缀索引的话，选择性就会很小。这时候如果拿后面机会来做索引，选择性就会很大，你见过几个身份证后面和你一样的人。这时候应该倒叙存储，然后拿前几位来做索引。

3.多列索引

为每个列单独创建索引，例如：

// name和doctorid都单独建了索引
select * from patients where name = '方%' or doctorid = 477;

在老版本MySQL中会全表扫描，而在MySQL5.0及以上，查询可以使用两个单列索引扫描，然后将结果合并。
这个索引合并算法有三个变种：

• OR的联合（union）
• AND的相交（intersection）
• 联合及相交

索引合并策略是一种优化策略，但是也说明表的索引建得很糟糕。

4.选择合适的索引顺序

在不需要考虑排序和分组的情况下，选择性高的列优先在前面，但是也要结合实际情况。
现在要将diseaseid和doctorid做联合索引

select
  count(distinct diseaseid) / count(*) as disease,
  count(distinct doctorid) / count(*) as doctor
from patients;

结果：

doctorid的选择性明显比diseaseid高很多，这时候联合索引的顺序应该是：(doctorid,diseaseid)。

5.索引覆盖

当发起一个被索引覆盖的查询，在EXPLAIN的Extra列可以看到Using index，例如patients上有doctorid索引：select doctorid from patients;时就会索引覆盖，速度非常快，直接从索引上获取数据，不需要回表查。

explain select doctorid
from patients
where doctorid in (477,33);

如果Explain中的Extra出现了Using index则表示索引覆盖，不需要回表查询数据。

6.使用索引扫描来排序

MySQL有两种方式生成有序的结果：排序；按索引顺序扫描；
如果EXPLAIN出来的type列的值为“index”，这说明MySQL使用了索引来排序。

explain select doctorid from patients

排序没用到索引：

explain select * from patients order by doctorid, diseaseid

排序用到索引：

alter table patients add index idx_doctorid_diseaseid (doctorid,diseaseid);
explain select * from patients order by doctorid, diseaseid limit 100;

7.冗余和重复索引