MySQL之索引一、索引索引简单理解就是目录，用于缩小查找范围，快速找到想要的数据的一种方式；在现实生活中也很常见，比如

一、索引

索引简单理解就是目录，用于缩小查找范围，快速找到想要的数据的一种方式；

在现实生活中也很常见，比如我们常用的字典前面会有一个字母目录，通过这个字母目录我们就能快速定位我们想要查询的单次，而无需一页一页的去翻；

1、索引种类

从逻辑上来说，可以分为如下几类：

主键索引

InnoDB存储引擎中，每张表都存在一个主键，主要分为三种情况：
- InnoDB表中申明了主键；
- 没有申明主键，但是表中有非空的唯一索引；
- 上述两个都没有，则InnoDB会生成一个6字节的rowid作为主键；
主键索引不允许为NULL；
普通索引

最基础的索引，没有任何限制；
唯一索引

有唯一性约束的普通索引，可以为NULL；
复合索引

有多个列组合而成的普通索引，这里有一个很重要的原则：最左前缀原则；
前缀索引

对字符类型的前几个字符或二进制类型的前几个字节建立索引；
倒排索引

倒排索引用来存储在全文搜索下某个单词在文档中的映射关系，通过倒排索引可以根据单词快速获取包含这个单次的文档列表；

从物理存储上来说，可以分为如下几类：

聚集索引（聚簇索引）

InnoDB是索引组织表，索引顺序与数据存储顺序一致，叶子节点存放的是数据行；
非聚集索引（非聚簇索引）

非聚集索引的叶子节点存放的是聚集索引的键值；

2、索引的数据结构

常见的用于实现索引的数据结构有哈希表、有序数组、树；

1、哈希表

哈希表是一种k-v的结构，那么k就是经过hash()计算之后的索引值；正常情况下，查询的时间复杂度是O(1)，但是如果发生了哈希冲突，就会采用拉链法进行解决，那么查询的时间复杂度就取决于链表的长度了，时间复杂度就成了O(n)；

一般情况下，为了降低哈希冲突，会要求hash()计算之后的k尽量离散，因此索引值经过hash()之后的值是无序的，那么对于范围查找，也就意味着离散访问，性能很差；

**因此哈希表只适用于等值查询，在InnoDB中，虽然不支持显式的哈希索引，但是提供了自适应哈希索引，会对一些等值查询的热点数据建立自适应哈希索引，无法人为干预； **

2、有序数组

有序数组顾名思义是一个按照k的顺序进行排序的数组，那么在进行等值查询的通过二分查找法查询，时间复杂度为O(LogN)，相比哈希表来说逊色不少；

有序数组因为其天然有序性，对于范围查找和排序相较于哈希表有着不错的表现；

但是有序数组为了维护其有序性，当有新的数据插入的时候，必须将比新元素大的值都向后移动一个单位，这种维护索引的代价是巨大的；

因此有序数组只适用于经历初始化之后就不再更改的数据；

3、树

1、二分搜索树

既然哈希表和有序数组都有着各自缺陷，那么有没有一种数据结构能够兼容两者呢？这时二分搜索树就排上用场了，二分搜索树的写和读的时间复杂度都是O(LogN)级别的；

但是二分搜索树也存在两个问题：

斜树，斜树其实就是退化成了有序链表，也就意味着二分搜索树的性能是不稳定的；
树的高度，当数据量越来越大时，由于只有左右两个子节点，就会导致的树的高度越来越高；

2、AVL树

鉴于二分搜索树的斜树情况，于是考虑到了使用AVL树，所谓的AVL树就是在二分搜索树的基础上规定左右子树的高度差的绝对值不允许超过1；也就是避免斜树的出现，它是通过右右型-左旋和左左型-右旋的机制来实现的；

虽然AVL解决了斜树问题，但是无法解决树的高度问题；因为对于InnoDB来说，树的高度直接决定了磁盘IO的次数，IO的次数又直接决定了InnoDB的性能，因此AVL树也不适合作为索引的数据结构；

4、MySQL索引

MySQL中的索引结构都是采用B+树实现的，无论是MyISAM还是InnoDB；

1、InnoDB

在InnoDB中每张表有且只能有一个聚集索引，但是可以有多个非聚集索引；

1、聚集索引

InnoDB中聚集索引的B+树存储结构：

特点：

关键字的数量和路数相同；
B+树的根节点和非叶子节点都不会存储数据，只有叶子节点才会存储数据；搜索到关键字并不会直接返回，会到最后一层的叶子节点；
B+树中不仅页是通过双向链表连接的，每个页中的记录也都是通过双向链表连接的；
它是根据左闭右开[)的区间来检索数据；
性能很稳定（一般都是1~3次IO）

注意：索引只能索引到页，拿到具体的行记录是通过遍历链表拿到的，但是因为索引到页之后，页会被加载到缓冲池中，那么在缓冲池中的链表遍历速度就很快了，性能影响可以不计；

举个例子：

假设一条记录是1K，那么一个叶子节点（一页）就可以放16条数据；

假设索引为bigint类型，占用8个字节，InnoDB源码中指针占用6个字节，那么也就意味着一个非叶子节点可以放16*1024/(8+6)=1170个索引；也就说一个非叶子节点可以有1170条路；

那么深度为2的B+树，可以存放的数据量为：1170 * 1170 * 16=21902400；

在查找数据的时候一次页的查找代表一次IO，也就是说一张2000万的表，最多只需要访问3次磁盘；

所以InnoDB中B+树的深度一般为1~3层，就能满足千万级别的数据存储；

2、非聚集索引

非聚集索引和聚集索引的最大的区别就是聚集索引存放的是索引和行记录，而非聚集索引存放的是索引和聚集索引的关键字的值；

通过这张图可以看出来，通过非聚集索引查找行记录的时候首先通过非聚集索引拿到聚集索引的索引，然后再通过聚集索引的索引去聚集索引中查找具体的行记录；

相对于直接通过聚集索引查找行记录，存在以下两个个问题

有时会多一个回表的过程；
非聚集索引拿到的聚集索引的索引是离散的，在回表的时候是离散访问的；

针对以上两个个问题，MySQL也提供了对应的优化手段：覆盖索引，MRR；

3、覆盖索引

回表：当查询语句需要查询除了辅助索引以及聚集索引的索引字段以外的其他字段时，需要通过查找聚集索引来获取其他的字段，这个过程就叫回表；

根据以上回表的定义，如果想要避免回表，那么是不是我们在查询字段的时候只查询辅助索引字段和聚集索引的索引字段时就不需要回表了？

举个例子：

create table user(
	id int(11) not null auto_increment primary key,
	name varchar(20) not null,
	age int(11) default null,
	index name(name)
)engine=innodb;

现在这个表有一个主键索引，一个辅助索引name；

未使用覆盖索引的情况：

使用索引覆盖的情况：

Extra列的Using index就代表使用了覆盖索引，可以看出来只要查询的字段在非聚集索引的覆盖范围内，就不会去回表；

覆盖索引减少了IO次数，减少了数据的访问量，可以大大的提升查询效率；

我们经常看到的尽量不要使用select * 也有这部分原因；

4、MRR

对于辅助索引来说，它的索引值是有序的，但是当需要回表查询的时候，需要通过辅助索引拿到聚集索引的关键字的值，那么拿到的这个值就无序的，也就意味着对聚集索引的访问是离散访问；MRR优化，就是为了解决通过辅助索引查询回表时的离散访问的问题；

MRR（Multi-Range Read）优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问；MRR通常适用于范围查询和JOIN查询；

原理：

将查询得到的辅助索引的索引和聚集索引的索引值放在一个缓存中，这时缓存中的数据是根据辅助索引的索引值排序的；
将缓存中的数据根据聚集索引的索引值进行排序；
根据排序后的顺序访问聚集索引；

举个例子：

查看MySQL的某些优化是否开启：

select @@optimizer_switch;

mrr=on：表示开启MRR；
mrr_cost_based=on：表示是否通过cost based的方式选择是否启用MRR；

总是开启MRR：

set session optimizer_switch="mrr=on,mrr_cost_based=off";

5、索引下推（ICP）

在MySQL5.6以前没有索引下推优化的时候，通过辅助索引查找记录时，首先根据辅助索引拿到聚集索引，然后回表拿到行记录，最后在Server层根据where条件进行过滤；在支持索引下推之后，存储引擎层会在取出索引的同时判断是否可以进行where条件的过滤；这样优化之后，在某些查询下，可以大大减少回表的次数，从而提高数据库的整体性能；

索引下推（Index Condition Pushdown），简单理解就是将原本在Server层的过滤下放到存储引擎层；

注意：索引下推只适用于非聚集索引；

举个例子：

CREATE TABLE employees(
emp_no int(11) NOT NULL,
birth_date date NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum('M','F') NOT NULL,
hire_date date NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (emp_no),
INDEX idx_lastname_age(last_name,age)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

关闭索引下推：

set session optimizer_switch="index_condition_pushdown=off";

Using where说明在Server层进行了条件过滤，上面的SQL流程是：

根据idx_lastname_age辅助索引拿到聚集索引；
回表到聚集索引查找行记录；
在Server层根据age>2的条件进行过滤；

开启索引下推（默认开启）：

set session optimizer_switch="index_condition_pushdown=on";

Using index condition代表使用了索引下推，上面的SQL流程是：

根据idx_lastname_age辅助索引拿到具体索引的同时根据age>2进行了过滤，因为age在辅助索引idx_lastname_age的范围内，经过这次过滤之后，拿到的聚集索引数量会减少；
回表查询聚集索引；

ICP的目标是减少了回表查询的数量，也就减少了IO次数；

6、最左前缀原则

最左前缀原则是在使用复合索引的时候必须要遵守的一个原则；

注意：最左前缀原则并不是指查询条件的顺序和复合索引的字段顺序一致，而是查询条件中是否包含复合索引中的最左列字段；

举个列子：

建立一个联合索引：

alter table employees add index idx_lastname_age_firstname(last_name,age,first_name);

和复合索引一样顺序的查询：

可以看到命中了索引idx_lastname_age_firstname；

和复合索引不一样的顺序的查询：

可以看到一样命中了idx_lastname_age_firstname；

7、执行计划（Explain）

官方文档

explain解释了一条SQL语句的执行情况，要想做好优化，一定要先会看到exlain；

explain支持的字段（官方表格）：

（1）id

id是每个SQL执行的唯一标识，同时也是SQL执行的顺序，值越大优先级越高，值相同的情况下，由优化器决定执行顺序；该值也可以为NULL，比如在UNION的情况下；

（2）select_type

select_type表示查询的类型，常见的类型有：

simple：简单的查询语句，不包括子查询和关联等；
subquery：包含在select或where中的第一个子查询；
primary：若查询语句中包含复杂的子查询，那么最外层会被标记为PRIMARY；
derived：衍生查询，结果被作为临时表；
union：SQL语句中包含UNION；

在union查询的时候，先执行union右边的SQL，再执行union左边的SQL，最后UNION RESULT；
union result：表示哪些表之间存在union，<union1,2>表示id为1和2的表进行了union；

（3）table

表示查询的表的名称，有可能是实际的表，也有可能是临时的表；

（4）type

type是执行计划中衡量SQL的重要依据，表示了SQL语句的访问类型；

性能从差到好：ALL、index、range、index_merge、ref、eq_ref、const、system；

除了ALL表示全表扫描，index以后的都使用了索引；

ALL

All表示全表扫描，意味着存储引擎找记录时未走索引，所以它是性能最差的一种访问；

在type为ALL的情况下，一般rows是整张表的所有行记录了；
index

index表示全索引的扫描，相较于ALL性能稍微好一点；

age包含在idx_lastname_age_firstname复合索引中，这个SQL查询了age字段，因此优化器选择了扫描这个idx_lastname_age_firstname非聚集索引；
range

range表示基于索引的范围扫描，所以性能要比index好；
index_merge

表示索引合并，它表示在查询时MySQL会使用多个索引；

MySQL在where语句中存在多个查询条件，并且其中存在多个字段可以分别使用到多个不同的索引，在这种情况下MySQL可以对多个索引树同时进行扫描，最后将它们的结果合并；
ref

ref表示索引访问，这种访问类型会出现在查询条件中以非聚集索引列的常量值进行查询的情况；
eq_ref

eq_ref通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果；一般是唯一性的索引（UNIQUE、PRIMAY）；
const

主键或唯一索引，表示只能查询到一条结果；
system

system是const的特例，即数据表中只有一条数据；

（5）possible_keys和key

possible_keys表示查询语句可以使用的索引；key表示查询语句真实使用的索引；

（6）key_len

key_len表示使用的索引的长度，计算方式：

整数类型、浮点类型、时间类型
- NOT NULL：字段类型字节长度；
- NULL：字段类型字节长度+1，因为需要一个是否为空的标记，这个标记需要占用1个字节；
字符类型
- varchar(n)
  - NOT NULL：n * [utf_8=3|gbk=2|latin=1]+2；
  - NULL：n * [utf_8=3|gbk=2|latin=1] + 1（NULL）+2；
- char
  - NOT NULL：n * [utf_8=3|gbk=2|latin=1]；
  - NULL：n * [utf_8=3|gbk=2|latin=1] + 1（NULL）；
- 因为varchar是变长字段，因此需要另外2个字节记录长度，而char是固定字段，无需记录长度，因此不需要另外两个2字节；是否为空的标识需要另外增加1个字节；

（7）ref

ref表示使用哪列或常量在索引中查找；

（8）rows

扫描行的预估值，一般来说越小越好；

（9）filtered

这个字段表示存储引擎返回的数据在Server层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比；

（10）Extra

执行计划给出的额外的信息说明；

Using index：使用了覆盖索引，避免回表；
Using index condition：使用了索引下推；
Using where：使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在Server层进行过滤（和是否使用索引没有关系）；
Using temporary：使用了临时表；
Using filesort：对结果进行外部排序，没有使用到索引排序
- 数据较少时在内存中排序，数据较多时在磁盘中排序；
- 尽量避免这种情况发生；

8、创建索引规则

建立索引的列，应该NOT NULL
- 虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，应该使用NOT NULL约束以及默认值；
在用于where条件判断、order排序、join on的字段上建立索引；
索引的个数不要过多，如果确实很多字段需要建立索引，建议创建合适的复合索引
- 浪费空间，更新变慢；
区分度低的字段不要建立索引，例如性别
- 扫描的行数太多，如果超过了行记录的30%左右，优化器根本不会走索引；
- 区分度计算：select count(distinct(col))/count(*) from table;
- show indexes from table查看Cardinality字段的值，该值表示了该索引字段在表中不重复的总量的预估值，这个值越接近行记录的总量，说明该索引效果越好；
频繁更新的值，不要作为主键或索引
- 页分裂；
复合索引把区分度高的字段放在前面