MySQL学习笔记（四）索引-下篇

MyISAM索引实现

B+树索引

前面了解过，MyISAM存储引擎的行数据都存放在MYD文件中，索引文件存放于MYI文件中。由于索引与行记录分开存储，所以MyISAM的索引都是辅助索引，也就是非聚集索引（UnClustered Index）。

MyISAM的B+树索引也分主键索引和普通索引两种，主键索引和普通索引的区别是，其值必须唯一且不能为NULL值。两种索引的叶节点存储的键值是MYD文件中数据的具体物理地址。MyISAM的B+树索引结构如下图所示：

MyISAM的B+树索引

还是拿下面这个表举例，定义如下一个表：

CREATE TABLE t (

id PK,

name KEY,

sex,

flag

)ENGINE=MyISAM;

表中同样包含四条记录：

1，dan，f，A

3，alice，m，B

5，helen，m，A

9，frank，f，C

其B+树索引构造如下图所示。主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。主键索引的叶子节点，存储主键，与对应行记录的指针；普通索引的叶子结点，存储索引列，与对应行记录的指针。可见，使用普通索引同样能够找到行记录，所以MyISAM表中可以没有主键。

一些索引类型介绍

主键索引

在主键上创建的索引，每张表只有唯一一个主键索引。从前面的介绍可知，InnoDB的主键索引是聚集索引，MyISAM的主键索引是非聚集索引。

普通索引

除主键索引外建立的都可算是普通索引，是最基本的索引类型，没有任何关键字定义的特殊限制。比如给表t增加一个建立在a列上的普通索引idx_1：

ALTER TABLE t ADD INDEX idx_1(a);

唯一索引

增添了唯一性约束的普通索引，即索引值必须唯一，但可以为空值。系统会在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。主键索引一定是唯一性索引，唯一性索引并不一定就是主键。一个表中可以有多个唯一性索引。

建立唯一索引只需添加关键字UNIQUE即可，比如给表t增加一个建立在b列（b必须是unique列）上的索引idx_2：

ALTER TABLE t ADD UNIQUE INDEX idx_2(b);

联合索引

之前讨论的索引都是建立在一个列上的，联合索引指的是对表中的多个列建立的索引。联合索引本质上还是一颗B+树，不同的是联合索引的键值数量不是1，而是大于等于2.

比如给表t增加一个建立在a和b两列上的联合索引idx_3：ALTER TABLE t ADD UNIQUE INDEX idx_3(a,b)。 idx_3的索引树示例如下，每个节点的键值个数为2. 可以看到，键值一样是排序的，我们通过叶节点可以逻辑上顺序读取所有数据，即：(1,1)，(1,2)，(2,1)，(2,4)

，(3,1)，(3,2)。数据按(a,b)的顺序进行存放。

联合索引结构树

由于在索引idx_3中，数据是按(a,b)的顺序进行存放的，对于查询SELECT * FROM t WHERE a=xx AND b=xx 时，可以使用idx_3；对于单个的a列查询SELECT * FROM t WHERE a=xx，也使用idx_3。但是对于b列的查询SELECT * FROM t WHERE b=xx， idx_3则不适用了，因为叶节点上的b值依次为1，2，1，4，1，2，不是顺序的，idx_3索引树不能快速找出全部b=xx的行记录。这说明引用列的顺序对于联合索引非常重要。

从上图还可以看出联合索引的另一个特点，能对第二个键值排序。对于a=1的行记录，idx_3的逻辑存储顺序是(1,1)，(1,2)。如果查询里面有针对b列排序的需求，优化器会选择该联合索引来避免额外的排序操作，提高查询效率。比如我们创建这样一个表：

CREATE TABLE t(

a INT(4),

b INT(4),

c INT(4)

);

然后添加两个索引进行测试，普通索引idx_a和联合索引idx_ab：

ALTER TABLE t ADD INDEX idx_a(a);

ALTER TABLE t ADD INDEX idx_ab(a,b);

添加一些数据：

开始查询测试。如果只对于a列执行查询SELECT * FROM t WHERE a=2，可以看到虽然possible keys这里有2个索引可供使用，但优化器选择了普通索引idx_a。因为该叶节点只包含a单个键值，因此在一个数据页中能存放的记录应该更多。

如果执行查询SELECT * FROM t WHERE a=2 ORDER BY b DESC，可以看到优化器这次选择了联合索引idx_ab。因为这个索引中b已经顺序排列好了。如果我们在执行这个查询时强制使用idx_a，查看执行计划如下：

在Extra这一列里多了一个Using filesort，filesort是指排序，但并不是在文件中完成。可以对比执行以下命令观察：

执行查询SELECT * FROM t FORCE INDEX(idx_a) WHERE a=2 ORDER BY b DESC 时，增加了排序操作。而如果使用联合索引idx_ab，则不会增加额外的操作：

全文索引

全文索引是一种比较特殊的索引，主要应用场景是通过文本中关键字的匹配进行查询过滤，这是一种基于相似度的查询，而不是精确比较索引中的数值。当今互联网的搜索引擎也是应用了全文索引技术。MySQL从3.23.23版开始支持全文索引和全文检索。，支持全文索引的存储引擎有MyISAM，InnoDB在5.6以上版本也提供支持。

全文索引语法独特，没有索引也可以工作，如果有索引效率则更高。在相同的列上同时创建全文索引和基于值的B+树索引不会冲突。全文索引支持各种字符内容的搜索，包括VAR，VARCHAR和TEXT类型，也支持自然语言搜索和布尔搜索。虽然全文索引的实现较为复杂，在MySQL中使用也有很多限制，但依旧有广泛的应用范围。

来看一下全文索引的实现机制。它的作用对象是一个“全文集合”，可能是数据表中的一列，也可能是多列。具体的，对数据表的某一条记录，MySQL会将需要索引的列全部拼接成一个字符串，然后进行索引。这是一类特殊的B树索引，共有两层，第一层存放所有关键字，每个关键字对应的第二层，包含一组相关的“文档指针”。全文索引根据一些过滤规则，来索引文档对象中的词语：

1. 停用词列表中的词都不会被索引。默认的停用词根据通用英语的使用来设置，可以是用参数ft_stopword_file指定一组外部文件来使用自定义的停用词。

2. 对于长度大于ft_min_word_len的词语和长度小于 ft_max_word_len的词语，都不会索引。

全文索引并不会存储关键字具体匹配在哪一列，如果需要根据不同的列来进行组合查询，那么不需要针对每一列来建立多一个这类索引。

来看一个具体的例子。首先创建一个book表（InnoDB表，MySQL版本5.7）。

CREATE TABLE book(

id int(8) NOT NULL,

title char(50) NOT NULL,

author varchar(20) NOT NULL,

abstract text NOT NULL,

PRIMARY KEY(id),

FULLTEXT KEY char_title(title) WITH PARSER ngram,

FULLTEXT KEY varchar_author(author) WITH PARSER ngram,

FULLTEXT KEY text_abstract(abstract) WITH PARSER ngram

)DEFAULT CHARSET=utf8;

备注：因为InnoDB默认的全文索引parser适用于处理Latin字符集，特点是以空格作为分隔。但对于中日韩文等不以空格作为单词分隔的语言，全文索引需要借助额外的插件n-gram parser来帮忙。如果只需要索引英文词语，则不需要添加该插件。

然后在表中插入一些数据。

下一步，设置参数ft_min_word_len为1，保证每个单词都能被索引到。linux需要在my.cnf文件中设置该参数，Windows需要在my.ini文件中设置。

开始测试。使用SELECT * FROM book WHERE MATCH(列名) AGAINST(关键字) 查询语句，可以看到，添加了插件后，中英文词语都能被检索到。

同样，全文索引可以在多列上创建联合索引，每个列都必须是FULLTEXT KEY属性的。比如我们在title和abstract两列上添加一个全文索引，在查询时，MATCH子句必须要精确匹配两列。

ALTER TABLE book ADD FULLTEXT INDEX t_a(title,abstract);
SELECT * FROM book WHERE MATCH(title,abstract) AGAINST('abc');

总结

介绍了这么多索引的知识，现在来总结一下索引的优点。索引最基本的功能是帮助服务器快速地定位到表的指定位置。除此之外，根据创建索引的数据结构不同，索引也有不同的附加作用。

比如B+树索引，按照顺序存储数据，所以在执行ORDER BY等命令时能够省去额外的排序。总体来说索引的优点有：1. 大大减少MySQL需要扫描的数据量；2. 可以帮助服务器避免排序和临时表；3. 可以将随机IO变成顺序IO。

索引同样也有缺点。1. 创建索引文件本身会占据一定的磁盘空间。假设有种特殊的业务场景，需要创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果我们有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。2. 对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为在改动数据时，MySQL不仅要将这些操作写入数据文件，还要写入每个索引文件。当一张表拥有很多索引时，执行数据的更新操作将会变得缓慢。

可见在实际生产中，如何灵活高效地设计和选择高效的索引，又有另一个值得深入探讨的话题了。