MySQL-索引

最近学习了MySQL的索引，汇总一下我学习的知识点，涉及的知识点比较多，篇幅太长了。现在先大体说一下，以后补上每个点的具体细节。

聚簇索引和非聚簇索引

聚簇索引：索引字段和其他非索引字段在相同文件上。

非聚簇索引：索引字段和其他非索引字段在不同的文件上。

在MyISAM中：后缀为MYI为索引字段文件，MYD为非索引字段文件。

而在InnoDB中，只有一个MYI类型文件，存储的是索引字段和非索引字段。

由于两种索引存储的方式是不同的，非聚簇索引在根据索引查询数据时，会访问两个文件来组成数据。而聚簇索引则直接一个文件就可以组成数据。根据IO访问的效率上来讲，聚簇索引是比非聚簇索引效率高的。

强调一下，InnoDB和MyISAM存储引擎都是针对数据表的行的，而非数据表。平时我们添加表时，默认使用某种存储引擎，其实是默认设置该表中的每行数据的存储引擎。

聚簇索引

结构

MySQL在众多的存储数据的结构当中选择的B+Tree这种结构，在你面试的时候，可能会有面试官会问你，为什么不用二叉树，为什么不用红黑树，为什么不用Hash表，为什么不用B-Tree,等等。我们来比较一下这几种树的区别：

二叉树：二叉搜索树是一种非平衡树，它将首个数据当做根节点，其余节点与父节点比较大小，比父节点小的话则放在左子树，比父节点大的话会放在右子树，如图

如果索引节点的数据是依次递增的，那么遍历一次数据则需要n次搜索，效率是比较差的。比如搜索6这个节点需要比较5次才能搜索到数据

红黑树：红黑树是一种二叉平衡树，它根据规则平衡数据，

a.每个节点要么红色要么黑色，根节点为黑色

b.如果一个节点是红的，则它的儿子是黑色的

c.从任一节点到其叶子的所有简单路径都包含相同数目的黑色节点

d.每个红色节点的两个子节点，一定都是黑色（叶子节点包含NULL）

再次将上面数据换成红黑树，结构如下：

搜索6这个节点，需要3次就能找到数据。但是，红黑树的存储是比较费时间的，插入数据后的平衡二叉树需要比较和移动节点，当存储海量数据时，存储效率不佳。

Hash表：hash表的结构如下：

优点：Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位

不足：

（1）Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。哈希索引只支持等值比较查询，包括＝、 IN 、<=>(注意<>和＜＝＞是不同的操作）。 也不支持任何范围查询，例如WHERE price > 100。　　

由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash索引无法被用来避免数据的排序操作。　　

由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;

（3）Hash索引不能利用部分索引键查询。　　

对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。

（4）Hash索引在任何时候都不能避免表扫描。　　

前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

B-Tree,首先看一下这种树的结构，它是一种多路自平衡搜索树

这种树结构是将索引存储在每个节点，插入索引时的操作和红黑树有些类似，寻找并且比较，然后平衡树。在数据库中B-Tree的每个索引节点下还带着每条索引对应的一部分数据，但是因为B-Tree叶子节点前后没有指针链接，因此用于范围查找时，需要重新返回根节点一级一级的向下查找，比较耗费性能。

B+Tree,B+树的索引数据是全部都放在叶子节点上，且叶子节点间有指针

B+Tree优势：

（1）B+Tree相较于B-Tree来说，上面的展示图看不出来，但是叶子节点间有循环指针，范围查找直接在叶子节点间查找，无需返回根节点。

（2）另外，B-Tree的父节点以及根节点都存储有指针、索引以及数据信息，占用内存比较大。而B+Tree的话，数据全部集中在叶子节点，父节点以及根节点只包含向下指针以及索引字段，占用内存较小。

（3）B+Tree支持范围查找。

（4）B+Tree可以存储海量数据。因为叶子节点有前后指针，因此存放数据无需移动数据，直接插入到指定位置即可。MySQL 官方对非叶子节点(如最上层h = 1的节点，B+Tree高度为3) 的大小是有限制的，通过执行

SHOW GLOBAL STATUS like 'InnoDB_page_size';

可以得到大小为16384，即16k大小

（1）第一层最大节点数为：16k / (8B + 6B) = 1170 (个)；

（2）第二层最大节点数也应为：1170个；

（3）第三层最大节点数为：16k / 1k = 16 (个)。

则，一张B+Tree的表最多存放1170 * 1170 * 16 ≈ 2千万。

索引分类

主键索引

用于标识当前表的主键，主键是唯一标识，且可以用于作别的表的外键。我们平时建表时通常会指定主键。但是建主键最好遵循以下规则：

（1）不要不建索引

因为索引的特性，可以定位到某个数据，支持范围查找等特点，因此即使不建立索引，MySQL会查找数据不重复的一列，如果有则以当前列为主键，如果没有，MySQL也会默认自动增加一列整型自增列来记录索引。虽然你看不到，但是并不代表这张表没索引。

（2）建索引最好整型自增

通过上面的B+Tree的分析，整型自增的索引的好处是：每次新增数据，都会在最后的叶子节点新增数据，不需要再变换位置等。但是在工作中，大家常用的就是UUID，我觉得可能是为了避免id冲突吧。但是每条记录的UUID为字符串数据，他们的地址并不是递增的，每次插入新的记录，都需要移动位置存放数据，性能不如整型自增。

唯一索引

用于标定当前表的某列数据的唯一性，和主键索引不同，它不能用作别的表的主键。当存储重复的索引值时，MySQL会抛错。

普通索引

为某列数据添加普通索引。

（1）模糊匹配的时候，字段前面加%之后，不会走索引

联合索引

以某几列数据为索引。联合索引使用时要符合以下原则才会走联合索引：

（1）最左前缀原则，以a,b,c这三列为联合索引，以下几种情况生效：a、ab、abc；以下几种情况不生效：b、c、bc

全文索引

为长度比较大的字段建立索引。主要用于某些存储引擎之类的。和like不同，like主要用于长度比较小的字段模糊查询。

（1）MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

（2）MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

（3）只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

Explain 工具：

接下来使用explain工具逐个分析各个索引的功能，explain各个字段说明：

id:执行顺序，比如我执行下面这条sql，就是按照id序号顺序执行的。相同的时候按照从上到下执行

explain select * from film 
where film_id in (
	select film_id from film_category 
	where category_id in(select category_id from category where name like '%Action%')
)

select_type:对应的sql是复杂查询还是简单查询，比如上面的sql就用到了两个查询类型

SIMPLE:简单；
PRIMARY:最外层
SUBQUERY:子查询
DERIVED:衍生查询（查询时衍生表）
UNION:union后面的查询

table:用到的表

type:访问类型，查找记录的性能排行：

system（const的特殊情况，只有一条数据符合） > const（常量查询） > eq_ref（主键索引） > ref（普通索引） > range（范围查询） > index（全索引） > all（遍历所有）

possible_keys：可能会用到的索引

key:一定用到的索引

key_len:索引长度，越长代表性能越好

key_len计算规则如下：
字符串
    char(n)：n字节长度
    varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+ 2
数值类型
    tinyint：1字节
    smallint：2字节
    int：4字节
    bigint：8字节
时间类型
    date：3字节
    timestamp：4字节
    datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

ref:这一列显示了在key列记录的索引中，表查找值所用到的列或常量

rows:遍历的记录数

Extra:查询可能会用到的额外信息

Using index:使用覆盖索引
Using where:使用where语句查询结果，查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行
优化的，首先是想到用索引来优化。

trace工具

trace工具也是用于检查sql性能的。在用explain检查sql之后，若某些字段加了索引，但是并未走索引时，可以使用trace工具查看，但是trace工具比较好性能。因此一般情况下都是关闭状态，打开检查完原因之后，需要关闭。举个例子：

为t_system_user表添加username的索引条件，该表有11637条数据。现在我模糊查询username以1开头的数据，查看执行计划，发现并没有走索引， type为All,全表扫描：

这个时候trace就派上用场了。我电脑上安装的是5.5版本，不支持trace，这个比较棘手了，等我把我老电脑的MySQL重装一下再补上。先说一下trace如何使用：

打开trace：

set optimizer_trace="enabled=on",end_markers_in_JSON=on;

之后执行查询语句，再执行查询trace的语句:

select * from t_system_user where username like '1%';
 SELECT * FROM information_schema.OPTIMIZER_TRACE;

就可以查询全文扫描和索引扫描耗费的成本。比较之后就可以看到哪种查询成本比较小了。