MySQL中的索引

MySQL中常见的索引

InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引。

一、B+树索引

InnoDB中的索引按照B+树来组织的，数据都存放在B+树（为了快速检索数据而提出的一种数据结构）的叶子节点中。

聚集索引/聚簇索引

InnoDB中使用了聚集索引，就是将表的主键用来构建一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。这就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。

聚集索引的叶子节点就是数据页。也就是说数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过聚集索引能获取完整的整行数据。另一个优点就是：对于主键的排序查找和范围查找速度非常快。

对于没有定义主键时，MySQL会使用唯一索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

辅助索引/二级索引

聚簇索引只能搜索条件是主键值时才能发挥作用，因为B+树种的数据都是按照主键进行排序的。如果想要以别的列作为搜索条件，一般会建立多个索引，这些索引被称为辅助索引/二级索引。（每建立一个索引，就有一棵B+树，对于辅助索引(Secondary Index)，也称二级索引、非聚簇索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助引擎的书签就是相应行数据的聚集索引。

回表

回表简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据索引上保存的主键 ID 再次到数据块里获取数据。

辅助索引的存在并不影响数据在聚簇索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚簇索引）来找到一个完整的行记录。这个过程也被称为回表。就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树，一次是辅助索引，一次是聚集索引。

为什么还需要一次回表操作呢？直接把完整的用户记录放到辅助索引的叶子节点不好吗？如果把完整的用户记录放到叶子节点是可以不用回表的，但是占用空间大，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，浪费存储空间。而且每次对数据的变化要在所有包含数据的索引中全部都修改一次，性能非常低。

总之，回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。

什么时候使用全表扫描的方式？什么时候使用二级索引 + 回表的方式去执行查询呢？这就是查询优化器需要考虑的事情了，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

联合索引/复合索引

将表上的多个列组合起来进行索引称之为联合索引或复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。

注意：建立联合索引只会建立1棵B+树，多个列分别建立索引会分别以每个列建立B+树，有几个B+树，比如，index(note)、

index(b)，就分别对note，b两个列各构建了一个索引。

而如果是index(note,b)在索引构建上，包含了两个意思（最佳左前缀原则）：

1.先把各个记录按照note列进行排序

2.在记录的note列相同的情况下，采用b列进行排序

覆盖索引

多个列可以组合起来构建为联合索引，那辅助索引也可以由多个列组成。

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录（回表）。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以，覆盖索引并不是索引类型的一种。

二、哈希索引

InnoDB存储引擎除了综上所说的各种索引，还有一种自适应哈希索引，对于B+树的查找次数取决于B+树的高度，在生产环境中，B+树的高度一般为3、4层，故需要3、4次的IO查询。

索引在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常使用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引（Adaptive Hash Index，AHI），创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高不少。

InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，其自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。

SHOW ENGINE INNODB STATUS;

哈希索引只能用来搜索等值的查询，如 SELECT * FROM table WHERE XXX = XXXX。而对于其他查找类型，如范围查找，是不能使用哈希索引的，因此上面出现 non-hash searches/s的情况。通过hash searches: non-hash searches可以大概了解使用哈希索引后的效率。

innodb_adaptive_hash_index来考虑是禁用或启动此特性，默认AHI为开启状态。

三、全文索引

全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。例如：ElasticSearche、solr等就是全文检索引擎，底层都是基于Apache Lucene的。

例如上表为我国的行政区编码表，我们要查找包含"天"字的区，怎么办？使用 "SELECT 表字段 FROM 行政区表 WHERE address_name = '%天%' "，这就意味着要扫描库中的诗词全文字段，逐条对比，找出含有 "天" 的记录。基本上，数据库中一般的SQL优化手段你都用不上。如果数量少，还能接受，如果数量大点，会收到用户几千几万条投诉，更何况在互联网这种海量数据的情况下呢？如何解决，可以使用倒排索引。

倒排索引就是将文档中包含的关键字全部提取处理，然后将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字时，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。

可以如下存储：

要是查找有没有 "南" 呢？可以继续填写

从InnoDB1.2.X版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本时5.6.x系列。不过MySQL从涉及支出就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多。比如，每张表只能有一个全文检索的索引，不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。所以MySQL的全文索引功能比较弱鸡。

四、索引在查询中的使用

索引在查询中作用到底是什么呢？在查询中发挥着什么作用呢？

1、一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。

2、一个SELECT查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。

五、高性能的索引创建策略

正确地创建和使用索引是实现性能查询的基础。

索引列的类型尽量小

在定义表结构的时候要显示的指定列的类型，以整数类型为例，有TINYINT、NEDIUMINT、INT、BIGINT。占用空间依次递增，此处的类型大小指的是该类型表示的数据范围大小。数据类型越小，在查询时的比较操作越快（CUP层面），数据类型越小，索引占用的存储空间就越少，在一个数据页就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这对于表的主键来说更加的适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点都会存储一份记录的主键值，如果主键使用更小的数据类型，意味着节省更多的存储空间和更高效的 I/O。

索引的选择性

创建索引应该选择 选择性/离散性 高的列。索引的 选择性/离散性 是指：不重复的索引值（也称为基数--cardinality）和数据表的总记录数（N）的比值，范围从1/N 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

很差的索引选择性就是列中的数据重复读很高，比如性别字段，不考虑政治正确的情况下，只有男和女两种可能。如果用性别字段这个索引，可能查出一般的数据来。

怎么算索引的选择性/离散性？

SELECT count(DISTINCT 字段名)/count() FROM 表名;

前缀索引

正对blob、text、很长的varchar字段，mysql不支持他们的全部长度，需建立前缀索引。

语法：Alter table tableName add key/index (column(X))

缺点：前缀索引是一种能使索引更小、更快的有效方法，但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

有时候后缀索引（suffix index）也有用途（例如，找到某个域名的所有电子邮件地址）。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。

SELECT COUNT(DISTINCT LEFT(字段名，截取长度))/COUNT(*) AS A，通过该SQL语句比较某字段中前缀长度应该取多长。从而找到合适的前缀长度。

LEFT函数用法：

LEFT(str,length);

LEFT()函数接受两个参数：

str是要提取子字符串的字符串。

length是一个正整数，指定将从左边返回的字符数。

如何创建前缀索引？例如找到的合适前缀长度是length

ALTER TABLE 表名 ADD  KEY (字段名(length));

建立前缀索引后查询语句并不需要更改：

SELECT * FROM 表名 WHERE 列名 = 'xxx';

只为用于搜索、排序或分组的列创建索引

只为出现在WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没不必要建立索引了，除非是需要覆盖索引，又或者为出现在ORDER BY 或 GROUP BY子句中的列创建索引。

例如，搜索：select 列名 from .... and ....

只为 条件中的列建立索引即可

排序：SELECT * FROM table ORDER BY 列名1,  列名2, 列名3; ----查询的结果需先按照列名1值排序，如果列名1值相同则按照列名2进行排序，如果列名2也是相同的则按照列名3进行排序。

多列索引

索引理解错误：为每个列创建独立的索引，或按照错误的顺序创建多列索引。

遇到最容易引起困惑的问题就是：索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。反复强调，在一个多列 B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和 DISTINCT等子句的查询需求。

所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要排序和分组时，将选择性最高的列放在前面通常更好。这时索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。

然而，性能不只是依赖于索引列的选择性，也和查询的条件有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高。

同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

三星索引

对于一个查询而言，一个三星索引可能是其最好的索引。

三星索引满足条件：

• 索引将相关的记录放到一起则获得一星（比重27%）
• 如果索引中的数据顺序和查找中的排序顺序一致则获得二星（排序星）（比重23%）
• 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）（比重50%）

这三颗星中第三颗星最重要。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多。

一星：

如果一个查询相关的索引行是相邻的或者至少相距足够靠近，必须扫描的索引片宽度就会缩至最短，也就是说，让索引片尽量变窄，也就是说索引的扫描范围越小越好。

二星（排序星）：

在满足一星的情况下，当查询需要排序，GROUP BY，ORDER BY，如果查询所需的顺序与索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可能影响性能的关键因素。

三星（宽索引星） ：

在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。