MySQL的索引有哪些

一、索引是什么

       索引，在MySQL中也叫“键（key）”，是存储引擎用于快速找到记录的一种数据结构。如果把数据库的一张表比作一本书，那索引则是这本书的目录，通过目录，我们能快速找到我们想要的主题所对应的页码。索引的作用即类似于书的目录，帮助我们快速定位到相关数据行的位置。

       好的索引能使查询的性能提高几个数量级，而差的索引在大数据量的表中甚至会使性能急剧下降。“最优”的索引有时比一个“好的”索引性能要好两个数量级。

 

二、索引有哪些类型

       索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而非服务器层实现的，而不同的存储引擎的索引的工作方式并不一样，且不是所有的存储引擎都支持所有类型的索引。同时，值得一提的是，不同的存储引擎对同一类型的索引，其底层的实现一般是不同的。

      MySQL支持以下几种类型的索引。

      （1）B-Tree索引

      （2）哈希索引

      （3）空间数据索引（R-Tree）

      （4）全文索引

      （5）其他索引类别

      下面我将一一展开进行介绍。

 

三、 B-Tree索引

（一）B-Tree索引只是一个统称术语

       B-Tree索引是最常见的索引类型，它使用B-Tree数据结构来存储数据，大多数MySQL引擎都支持这种索引。（Archive引擎是一个例外：5.1之前Archive不支持任何索引，直到5.1才开始支持单个自增列AUTO_INCREMENT的索引。）

       在MySQL中，“B-Tree”只是一个术语的统称，因为不同的存储引擎可能使用的是其他存储结构来实现这种索引，但仅仅只是命名为“B-Tree”。例如，NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引；InnoDB则使用的是B+Tree结构存储这种索引。只是它们都将其命名为“B-Tree”。

（二）B-Tree索引在不同引擎中的差异

       不同的存储引擎使用B-Tree索引的方式也不同，性能也各有不同，各有优劣。下面拿MyISAM 和InnoDB进行对比。

 

表3-1 MyISAM和InnoDB中B-Tree的相关差异

	MyISAM	InnoDB
存储方式	前缀压缩技术	按照原数据格式
引用方式	通过数据的物理位置引用被索引的行	根据主键引用被索引的行

 

（三）InnoDB的B-Tree技术实现是B+Tree

       上面我们也提到，InnoDB的B-Tree索引从技术上来说实际上是B+Tree实现的，这种实现使得所有的值都是按照顺序存储的（所以很适合查找范围数据），并且每一个叶子页到根的距离相同。MyISAM使用的结构有所不同，但基本思想类似。

图3.1 建立在B-Tree结构上的索引（从技术上来说是B+Tree）

 

       B-Tree索引能够加快访问数据的速度，靠的就是上面这种数据结构。它使得存储引擎不再需要进行全表扫描来获取所需数据，取而代之的是从索引的根节点开始搜索。

根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么能找到对应的值，要么该记录不存在。叶子节点页有相应的指针，但叶子节点的指针不是指向其他的节点页，而是指向被索引的数据（不同引擎的“指针”类型不同）。

       这里值得一提的是，树的深度和表的大小直接相关，表的数据量越大，树的层数越多。

 

（四）创建一个多列索引

CREATE TABLE People (

      last_name    varchar(50)        not null,

      first_name   varchar(50)        not null,

      dob          date             not null,

      gender       enum(‘m’,‘f’)    not null,

      key(last_name,first_name,dob)

);

       索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

 

（五）B-Tree索引支持的查询类型

       MySQL的B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引可细分为如下几种类型。

     （1）全值匹配

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

       例如上面的People表的索引（last_name,first_name,dob）可以用于查找last_name=’Zeng’,first_name=’Chuang’,dob=’1996-01-01’的人。这就是使用了索引中的所有列进行匹配，即全值匹配。

     （2）匹配最左前缀

       可以只使用索引的第一个列进行匹配。

       例如可以用于查找last_name=’Zeng’的人，即用于查找姓为Zeng的人，这里只使用了索引的最左列进行匹配，即匹配最左前缀。

     （3）匹配列前缀

       可以只匹配某一列的值的开头部分。

       例如可以用于查找last_name LIKE ‘Z%’的人，即用于查找所有以Z开头的姓的人，这里只使用了索引最左列的前缀进行匹配，即匹配列前缀。

     （4）匹配范围值

       可以只适用索引的第一列查找符合某个范围内的数据。

       例如可以用于查找last_name BETWEEN ‘Qiu’ AND ‘Zeng’的人，即用于查找姓在Qiu和Zeng之间的人，这里只使用了索引最左列的前缀进行范围匹配，即匹配范围值。

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

       可以使第一列全匹配，第二列范围匹配。

       例如可以用于查找last_name=’Zeng’ AND first_name LIKE ’C%’的人，即用于查找姓是Zeng，名字以C开头的人，这里使用了索引的最左列精确匹配，第二列进行范围匹配，即精确匹配某一列并范围匹配另外一列。

     （6）只访问索引的查询

       查询只需访问索引，而无须访问数据行。

       例如select last_name, first_name where last_name=’Zeng’; 这里只查询索引所包含的last_name和first_name列，则无须读取数据行。

 

（六）B-Tree索引的限制

       根据上面介绍的B-Tree索引支持的查询类型，我们可以知道，它同样会存在一些限制。

     （1）只能按照索引的最左列开始查找。

       例如People表中的索引无法用于查找first_name为’Chuang’的人，也无法查找某个特定生日的人，因为这两个列都不是最左数据列。

     （2）只能按照索引最左列的最左前缀进行匹配。

       例如People表中的索引无法查找last_name LIKE ‘%eng’的人，虽然last_name就是此索引的最左列，但MySQL索引无法查找以‘eng’结尾的last_name的记录。

     （3）只能按照索引定义的顺序从左到右进行匹配，不能跳过索引中的列。

       例如People表中的索引无法用于查找last_name=’Zeng’ AND bod=’1996-01-01’的人，因为MySQL无法跳过索引中的某一列而使用索引中最左列和排在末尾的列进行组合。如果不指定索引中中间的列，则MySQL只能使用索引的最左列，即第一列。

     （4）如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

       例如有这样一个查询：where last_name=’Zeng’ AND first_name LIKE ’C%’ AND dob=’1996-01-01’; 这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件，则first_name后面的索引列都将失效。（优化点：尽量不要在索引列中使用LIKE等范围条件，改用多个等于条件来替代，保证后面的索引列能生效。）

       阅读到这里，我们应该明白了索引列的顺序是多么的重要，上面的这些限制都和索引列的顺序有关。在性能优化时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

 

四、哈希索引

（一）哈希索引是什么

       哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。

       对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，并在哈希表中保存指向每个数据行的指针。

图4.1 hash索引图解

（二）不同存储引擎对哈希索引的支持

表4-1 各存储引擎对哈希索引的支持情况概览

	Memory引擎	NDB集群引擎	InnoDB引擎
是否支持哈希索引	显示支持，是Memory引擎表的默认索引类型（也支持B-Tree索引）	支持唯一哈希索引，所起作用特殊	自适应哈希索引（adaptive hash index）

（1）   Memory引擎的哈希索引

       Memory引擎不仅支持唯一哈希索引，还支持非唯一哈希索引。非唯一哈希索引指的是：如果多个列的哈希值相同，索引会以链表的方式存放多个指向不同记录的指针到同一个哈希条目中。

图4.2 链表方式存放哈希索引 

 

       使用Memory引擎在建表时创建哈希索引

CREATE TABLE testhash （


    fname VARCHAR(50) NOT NULL,


    Lname VARCHAR(50) NOT NULL,


    KEY USING HASH(fname)


） ENGINE=MEMORY;

（2）   NDB集群引擎的哈希索引

        后续将深入阅读官方文档，理解NDB集群引擎中哈希索引的作用：https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-ndbd-definition.html

 （3）   InnoDB引擎的哈希索引

        InnoDB引擎有一个特殊的功能叫“自适应哈希索引（adaptive hash index）”：当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，让B-Tree索引也具有哈希索引的一些优点，如快速的哈希查找。

       “自适应哈希索引”是一个完全自动的、内部的行为，用户无法控制或配置，不过如果有必要，完全可以关闭该功能。

 （三）哈希索引的优势、限制及适用场景

 （1）优势：哈希索引查找的速度非常快。

        原因：索引自身只需存储对应的哈希值，使得索引的结构十分紧凑。

 （2）哈希索引的限制

        1）不能使用索引中的值来避免读取行。因为哈希索引只包含哈希值和行指针，而不存储字段值。

        2）不能用于排序。因为哈希索引数据不是按照索引值顺序存储的。

        3）不支持部分索引列匹配查找。因为哈希索引必须使用索引列的全部内容来计算哈希值。

        4）只支持等值比较查询（包括=、IN()、< = >），不支持任何范围查询（如where price > 100)。

        5）当出现哈希冲突时（不同的索引列值却有相同的哈希值），访问速度会变慢。因为存储引擎必须遍历链表中所有的行指针，逐行进行比较，知道找到所有符合条件的行。

        6）若哈希冲突很多，一些索引维护操作的代价也会很高。如：在某个哈希冲突很多的列上建立哈希索引，当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用。冲突越多，代价越大。

 （3）哈希索引的适用场景

        因为上面的这些限制，使得哈希索引适用的场景比较有限。而一旦适用哈希索引，则它带来的性能提升将非常显著。

        如，在数据仓库应用中有一种经典的“星型”schema，需要许多关联才能建立查找表，哈希索引就非常适合查找表的需求。

（四）创建自定义哈希索引

（1）思路

在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。只需要在查询的WHERE字句中手动指定使用哈希函数。

（2）哈希索引查找实例

一张表中存储了大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容会很大。正常情况下会有如下查询：

SELECT id FROM url WHERE url=”http://www.mysql.com”;

若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

SELECT id FROM url WHERE url=”http://www.mysql.com” AND url_crc=CRC32(“http://www.mysql.com”);

这样做性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。及时有多个记录相同的索引值，查找仍然很快，因为MySQL优化器会先筛选出匹配的索引行记录，然后根据具体的url值进行比对，返回完全符合条件的行。

 （3）使用触发器维护哈希值

新增一列url_crc列之后需要维护这个哈希值。可以手动维护，也可以使用触发器实现。

首先创建如下表：

CREATE TABLE pseudohash (

  id  int  unsigned  NOT  NULL  auto_increment,

  url  varchar(255)  NOT NULL,

  url_crc  int unsigned  NOT NULL DEFAULT 0,

  PRIMARY KEY(id)

);

然后创建触发器：

DELIMITER //

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN

SET NEW.url_crc=crc32(NEW.url);

END;

//

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN

SET NEW.url_crc=crc32(NEW.url);

END;

//

DELIMITER ;

最后验证触发器如何维护哈希索引：

INSERT INTO pseudohash(url) VALUES(‘http://www.mysql.com’);
SELECT * FROM pseudohash;

UPDATE pseudohash SET url=‘http://www.mysql.com’ WHERE id=1;
SELECT * FROM pseudohash;

（4）规避使用SHA1()和MD5()

SHA1()和MD5()计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。SAH1()和MD5()是强加密函数，设计目标是最大限度消除冲突，但这里并不需要这样高的要求。

简单哈希函数的冲突在一个可以接受的范围，同时又能够提供更好的性能。

（5）处理哈希冲突

1）自定义哈希函数

如果表的数据量非常大，CRC32()会出现大量的哈希冲突，则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数，而不是字符串。一个简单的办法可以使用MD5()返回值的一部分来作为自定义哈希函数。这可能比自己写一个哈希算法的性能要查，但这样实现最简单：

SELECT CONV(RIGHT(MD5(‘http://www.mysql.com’),16),16,10) AS HASH64;

注：CONV(N,from_base,to_base)

N是要转换的数据，from_base是原进制，to_base是目标进制。

2）WHERE字句中包含常量值

当使用哈希索引进行查询的时候，必须在WHERE字句中包含常量值：

SELECT id FROM url WHERE url=“http://www.mysql.com” AND url_crc=CRC32(“http://www.mysql.com”);

　　因为所谓的“生日悖论”，出现哈希冲突的概率的增长速度可能比想象的要快得多。CRC32()返回的是32位的整数，当索引有93000条记录时出现冲突的概率是1%。

      如果不想查询具体值，例如只是统计记录数（不精确的），则可以不带入列值，直接使用CRC32()的哈希值查询即可。

      还可以使用如FNV64()函数作为哈希函数，这是移植自Percona Server的函数，可以以插件的方式在任何MySQL版本中使用，哈希值为64位，速度快，且冲突比CRC32()要少很多。

 

五、空间数据索引（R-Tree）

　　MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀索引。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。

      开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL和PostGIS。

 

六、全文索引

　　全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

 

七、其他索引类型

　　TokuDB使用形树索引（fractal tree index），这是一类较新开发的数据结构，既有B-Tree的很多优点，也避免了B-Tree的一些缺点。

　　还有InnoDB的聚簇索引、覆盖索引等。

      ScaleDB使用Patricia tries。

      其他存储引擎技术如InfiniDB和Infobright则使用了一些特殊的数据结构来优化某些特殊的查询。

 

参考文献

  [1]Baron Scbwartz, Peter Zaitsev, Vadim Tkacbenko. 高性能MySQL[M].第三版.北京：电子工业出版社, 2013:141-146