索引是 SQL 优化中最重要的伎俩之一，本文从根底到原理，带你深度把握索引。

一、索引根底

1、什么是索引

MySQL 官网对索引的定义为：索引（Index）是帮忙 MySQL 高效获取数据的数据结构，索引对于良好的性能十分要害，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查问性能优化最无效的伎俩了。索引可能轻易将查问性能进步好几个数量级。

艰深来讲，索引相似文章的目录，用来进步查问的效率。

2、索引分类

常见的索引类型有：主键索引、惟一索引、一般索引、全文索引、组合索引

2.1、主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引

create table a (  
    id int primary key auto_increment,  
    name varchar(20) not null default ''  
);  

这里 id 就是表的主键，如果当创立表时没有指定主键索引，也能够在创立表之后增加：

alter table table_name add primary key (column_name);

1.2、一般索引

用表中的一般列构建的索引，没有任何限度

create index 索引名 on table_name(column1);
alter table table_name add index 索引名(column1);

1.3、全文索引

全文索引次要针对文本文件，比方文章，题目。在 MySQL5.6 之前，只有 MyISAM 存储引擎反对全文索引，MySQL5.6 之后 InnoDB 存储引擎也反对全文索引。

create table c(  
    id int primary key auto_increment ,  
    title varchar(20),  
    content text,  
    fulltext(title,content)  
) engine=myisam charset utf8; 

insert into c(title,content) values  
    ('MySQL Tutorial','DBMS stands for DataBase ...'),  
    ('How To Use MySQL Well','After you went through a ...'),  
    ('Optimizing MySQL','In this tutorial we will show ...'),  
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),  
    ('MySQL vs. YourSQL','In the following database comparison ...'),  
    ('MySQL Security','When configured properly, MySQL ...'); 

1.4、惟一索引

见名知义，索引列中的值必须是惟一的，然而容许为空值。d 表中 name 就是惟一索引，相比主键索引，主键字段不能为 null，也不能反复

create table d(
    id int primary key auto_increment , 
    name varchar(32) unique
    ) 

1.5、组合索引

用多个列组合构建的索引，这多个列中的值不容许有空值。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

组合索引遵循“最左前缀”准则，应用时最好把最罕用作为检索或排序的列放在最左，顺次递加。组合索引相当于建设了 col1，col1col2，col1col2col3 三个索引，而 col2 或者 col3 是不能应用索引的。在应用组合索引的时候可能因为列名长度过长而导致索引的 key 太大，导致效率升高，在容许的状况下，能够只取 col1 和 col2 的前几个字符作为索引。
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

示意应用 col1 的前 4 个字符和 col2 的前 3 个字符作为索引

3、索引机制浅析

咱们这里先简略分析一下索引的机制，为接下来的深刻做一些铺垫。

3.1、索引放慢查问的原理

传统的查询方法，是依照表的程序遍历的，不管查问几条数据，MySQL 须要将表的数据从头到尾遍历一遍。

在咱们增加完索引之后，MySQL 个别通过 BTREE 算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，应用可能大幅地查问的效率的折半查找的形式，找到相应的键从而获取数据。

3.1、索引的代价

创立索引是为产生索引文件的，占用磁盘空间。索引文件是一个二叉树类型的文件，可想而知咱们的 DML 操作 ((数据操作语言, 对表记录的(增、删、改) 操作)同样也会对索引文件进行批改，所以性能会相应的有所降落。

二、索引存储数据结构

下面曾经说到，索引实际上是数据库中 满足特定查找算法的数据结构 ，这些数据结构以某种形式援用（指向）数据，这样就能够 在这些数据结构上实现高级查找算法。

可能咱们都晓得，MySQL 索引是 B+ 树 数据结构，当然，实际上索引还有 哈希表 、 有序数组 等常见的数据结构。

1、哈希表

哈希表是一种以键 - 值（key-value）存储数据的构造，咱们只有输出待查找的值即 key，就能够找到其对应的值即 Value。哈希的思路很简略，把值放在数组里，用一个哈希函数把 key 换算成一个确定的地位，而后把 value 放在数组的这个地位。

不可避免地，多个 key 值通过哈希函数的换算，会呈现同一个值的状况。解决这种状况的一种办法是，拉出一个链表。

所以，须要留神，哈希表后的链表并不是有序的，区间查问的话须要扫描链表，所以 哈希表这种构造实用于只有等值查问的场景，比方 Memcached 及其他一些 NoSQL 引擎。

2、有序数组

另外一个大家比拟相熟的数组构造，有序数组在等值查问和范畴查问场景中的性能都十分优良。

如果仅仅看查问效率，有序数组是十分棒的数据结构。然而，在须要更新数据的时候就麻烦了，你往两头插入一个记录就必须得移动前面所有的记录，老本太高。

所以，有序数组索引只实用于动态存储引擎，比方你要保留的是 2017 年某个城市的所有人口信息，这类不会再批改的数据。

这两种都不是最次要的索引，常见的索引应用的数据结构是树结构，树是数据结构里绝对简单一些的数据结构，咱们来一步步意识索引的树结构。

3、二分查找

二分查找也称折半查找（Binary Search），它是一种效率较高的查找办法。然而，折半查找要求线性表必须采纳顺序存储构造，而且表中元素按关键字有序排列。

查找办法：首先，假如表中元素是按升序排列，将表两头地位记录的关键字与查找关键字比拟，如果两者相等，则查找胜利；否则利用两头地位记录将表分成前、后两个子表，如果两头地位记录的关键字大于查找关键字，则进一步查找前一子表，否则进一步查找后一子表。反复以上过程，直到找到满足条件的记录，使查找胜利，或直到子表不存在为止，此时查找不胜利。

下面提到的有序数组的等值查问和比拟查问效率十分高，然而更新数据存在问题。

为了反对频繁的批改，比方插入数据，咱们须要采纳链表。链表的话，如果是单链表，它的查找效率还是不够高。

所以，有没有能够应用二分查找的链表呢?

为了解决这个问题，BST(Binary Search Tree)也就是咱们所说的二叉查找树诞生了。

4、二叉查找树

二叉树具备以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。

如下图所示就是一棵二叉查找树，

在这种比拟均衡的状态下查找时间复杂度是 O(log(n))。

然而二叉查找树存在一个问题：在某些极其状况下会进化成链表。

同样是 2,3,4,6,7,8 这六个数字，如果咱们插入的数据刚好是有序的，那它就变成这样????

这个时候，二叉查找树查找的工夫复杂度就和链表一样，是 O(n)。

造成它“叉劈”的起因是什么呢? 因为左右子树深度差太大，这棵树的左子树基本没有节点——也就是它不够均衡。

所以，咱们有没有左右子树深度相差不是那么大，更加均衡的树呢? ——那就就是均衡二叉树，叫做 Balanced binary search trees，或者 AVL 树。

5、AVL 树

AVL Trees (Balanced binary search trees) 均衡二叉树的定义: 左右子树深度差绝对值不能超过 1。

例如左子树的深度是 2，右子树的深度只能是 1 或者 3。这个时候咱们再按程序插入 2,3,4,6,7,8，就不会“叉劈”????

AVL 树的均衡是怎么做到的呢？次要用到了两个操作 左旋 、 右旋。

• 插入 1、2、3。

  当咱们插入了 1、2 之后，如果依照二叉查找树的定义，3 必定是要在 2 的左边的，这个时候根节点 1 的右节点深度会变成 2，然而左节点的深度是 0，因为它没有子节点，所以就会违反均衡二叉树的定义。

  那应该怎么办呢? 因为它是右节点上面接一个右节点，右–右型，所以这个时候咱们要把 2 提上去，这个操作叫做 左旋。

• 同样的，如果咱们插入 3、2、1，这个时候会变成左左型，就会产生 右旋 操作，把 2 提上去。

既然均衡二叉树能保持平衡，不会进化，那么咱们用均衡二叉树存储索引能够吗？——能够的。

当咱们用树的构造来存储索引的时候，拜访一个节点就要跟磁盘之间产生一次 IO。InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁盘块)。与主存不同，磁盘 I / O 存在机械运动消耗，因而磁盘 I / O 的工夫耗费是微小的。

所以如果每个节点存储的数据太少，从索引中找到咱们须要的数据，就要拜访更多的节点，意味着跟磁盘交互次数就会过多。

那么解决方案是什么？

• 让每个节点存储更多的数据。
• 让节点上有更多的关键字。

节点上的关键字的数量越多，咱们的指针数也越多，也就是意味着能够有更多的分叉(咱们把它叫做“路数”)。

因为分叉数越多，树的深度就会缩小(根节点是 0)。这样，树就从瘦高变成了矮胖。

这个时候，咱们的树就不再是二叉了，而是多叉，或者叫做 多路。

6、多路均衡查找树（B-Tree）

接下来看一下多路均衡查找树，也就是 B 树。

B 树是一种多叉均衡查找树，如下图次要特点：

• B 树的节点中存储着多个元素，每个内节点有多个分叉。
• 节点中的元素蕴含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都贮存数据。
• 父节点当中的元素不会呈现在子节点中。
• 所有的叶子结点都位于同一层，叶节点具备雷同的深度，叶节点之间没有指针连贯。

以上图为例，咱们来简略看几个查问：

• 如果查找 key<17，就走右边子节点；
• 如果查找 17<key<35，就走两头子节点；
• 如果查找 key>35，就走左边子节点；
• 如果查找 key=17，间接命中；
• 如果查找 key=35，间接命中；

B 树看起来很完满，到这就完结了吗？并没有。

• B 树不反对范畴查问的疾速查找，你想想这么一个状况如果咱们想要查找 10 和 35 之间的数据，查找到 15 之后，须要回到根节点从新遍历查找，须要从根节点进行屡次遍历，查问效率有待进步。
• 如果 data 存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘 IO 次数就会变大

所以接下来就引入咱们的终极数据结构——B+ 树。

7、加强版多路均衡查找树(B+Tree)

B+ 树，作为 B 树的升级版，在 B 树根底上，MySQL 在 B 树的根底上持续革新，应用 B + 树构建索引。B+ 树和 B 树最次要的区别在于 非叶子节点是否存储数据 的问题

• B 树：非叶子节点和叶子节点都会存储数据。
• B+ 树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间应用双向指针连贯，最底层的叶子节点造成了一个双向有序链表。

来看一下 InnoDB 里的 B + 树的具体存储构造：

来说一下这张图的重点：

• 最里面的方块，的块咱们称之为一个磁盘块，能够看到每个磁盘块蕴含几个数据项（粉色所示）和指针（黄色 / 灰色所示），如根节点磁盘蕴含数据项 17 和 35，蕴含指针 P1、P2、P3，P1 示意小于 17 的磁盘块，P2 示意在 17 和 35 之间的磁盘块，P3 示意大于 35 的磁盘块。实在的数据存在于叶子节点即 3、4、5……、65。非叶子节点只不存储实在的数据，只存储指引搜寻方向的数据项，如 17、35 并不实在存在于数据表中。
• 叶子节点之间应用双向指针连贯，最底层的叶子节点造成了一个双向有序链表。

7.1、存储容量

举个例子: 假如一条记录是 1K，一个叶子节点 (一页) 能够存储 16 条记录。非叶子节点能够存储多少个指针?

假如索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点 (一页) 能够存储 16384/14=1170 个这样的 单元(键值 + 指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，能够存储的数据为 1170*1170*16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查问数据最多须要拜访 3 次磁盘。

所以在 InnoDB 中 B+ 树深度个别为 1-3 层，它就能满足千万级的数据存储。

7.2、查问效率

咱们来看一下 B+Tree 的数据搜查过程:

• 1) 例如咱们要查找 35，在根节点就找到了键值，然而因为它不是页子节点，所以会持续往下搜查，25 是 [17,35) 的左闭右开的区间的临界值，所以会走两头的子节点，然 后持续搜寻，它又是 [28,34) 的左闭右开的区间的临界值，所以会走右边的子节点，最初在叶子节点上找到了须要的数据。
• 2) 如果是范畴查问，比方要查问从 22 到 60 的数据，当找到 22 之后，只须要顺着节点和指针程序遍历就能够一次性拜访到所有的数据节点，这样就极大地提高 了区间查问效率(不须要返回下层父节点反复遍历查找)。
• 3）增加了指向相邻叶节点的指针 ，造成了带有程序拜访指针的 B +Tree，这样做是为了 进步区间查找的效率，只有找到第一个值那么就能够程序的查找前面的值。

7.3、B+ 树特点总结

总结一下，InnoDB 中的 B+Tree 的特点:

• 1) 它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么?(每个节点存储更多关键字；路数更多)
• 2)扫库、扫表能力更强(如果咱们要对表进行全表扫描，只须要遍历叶子节点就能够 了，不须要遍历整棵 B+Tree 拿到所有的数据)
• 3) B+Tree 的磁盘读写能力绝对于 B Tree 来说更强(根节点和枝节点不保留数据区，所以一个节点能够保留更多的关键字，一次磁盘加载的关键字更多)
• 4) 排序能力更强(因为叶子节点上有下一个数据区的指针，数据造成了链表)
• 5) 效率更加稳固(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳固的)

三、聚簇索引和非聚簇索引

MySQL 中最常见的两种存储引擎别离是 MyISAM 和 InnoDB，别离实现了 非聚簇索引 和聚簇索引。

首先要介绍几个概念，在索引的分类中，咱们能够依照索引的键是否为主键来分为“主键索引 ”和“ 辅助索引 ”，应用主键键值建设的索引称为“ 主键索引 ”，其它的称为“ 辅助索引 ”。因而 主键索引 只能有一个，辅助索引能够有很多个。

1、MyISAM——非聚簇索引

MyISAM 存储引擎采纳的是非聚簇索引，非聚簇索引的主键索引和辅助索引 基本上是雷同的，只是主键索引不容许反复，不容许空值，他们的叶子结点的 key 都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是离开存储的。

非聚簇索引中的数据是依据数据的插入程序保留。因而非聚簇索引更适宜单个数据的查问。插入程序不受键值影响。

思考：既然非聚簇索引的主键索引索引和辅助索引指向雷同的内容，为什么还要辅助索引呢？索引不就是用来查问的吗，用在哪些地方呢？不就是 WHERE 和 ORDER BY 语句前面吗，那么如果查问的条件不是主键怎么办呢，这个时候就须要辅助索引了。

2、InnoDB——聚簇索引

聚簇索引的 主键索引的叶子结点存储的是键值对应的数据自身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因而主键的值长度越小越好，类型越简略越好。

聚簇索引的数据和主键索引存储在一起。

从上图中能够看到辅助索引的叶子节点的 data 存储的是主键的值，主键索引的叶子节点的 data 存储的是数据自身，也就是说数据和索引存储在一起，并且索引查问到的中央就是数据（data）自身，那么索引的程序和数据自身的程序就是雷同的。

因为聚簇辅助索引存储的是主键的键值，因而能够在数据行挪动或者页决裂的时候降低成本，因为这时不必保护辅助索引。然而因为主键索引存储的是数据自身，因而聚簇索引会占用更多的空间。

聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时须要检测主键是否反复，这须要遍历主索引的所有叶节点，而非聚簇索引的叶节点保留的是数据地址，占用空间少，因而散布集中，查问的时候 I / O 更少，但聚簇索引的主索引中存储的是数据自身，数据占用空间大，散布范畴更大，可能占用好多的扇区，因而须要更屡次 I / O 能力遍历结束。

四、索引应用准则

1、列的离散度

第一个叫做列的离散度，咱们先来看一下列的离散度的公式:

count(distinct(column_name)) : count(*)

列的全副不同值和所有数据行的比例。数据行数雷同的状况下，分子越大，列的离散度就越高。

mysql> SELECT * FROM `test`.`user` ORDER BY `id` LIMIT 10 OFFSET 0;
+----+-----------+--------+-------------+
| id | name      | gender | phone       |
+----+-----------+--------+-------------+
|  1 | 秦啭      |      0 | 13601722591 |
|  2 | 李镒榘    |      0 | 15204160836 |
|  3 | 陈艮      |      0 | 13601994087 |
|  4 | 沈夷旌    |      0 | 15507785988 |
|  5 | 朱桐泰    |      1 | 13201268193 |
|  6 | 周韬蕊    |      1 | 15705478612 |
|  7 | 冯叻加    |      0 | 13705834063 |
|  8 | 王焓      |      1 | 15006956358 |
|  9 | 黄芪      |      0 | 15108012536 |
| 10 | 吴笄游    |      0 | 15301860708 |
+----+-----------+--------+-------------+
10 rows in set (0.00 sec)

简略来说，如果列的反复值越多，离散度就越低，反复值越少，离散度就越高。

理解了离散度的概念之后，咱们再来思考一个问题，咱们在 name 下面建设索引和 在 gender 下面建设索引有什么区别。

当咱们用在 gender 上建设的索引去检索数据的时候，因为反复值太多，须要扫描的行数就更多。例如，咱们当初在 gender 列下面创立一个索引，而后看一下执行打算。

ALTER TABLE user ADD INDEX idx_user_gender (gender); -- 耗时比拟久 
EXPLAIN SELECT * FROM `user` WHERE gender = 0;

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys   | key        | key_len | ref   | rows    | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
|  1 | SIMPLE      | user      | NULL       | ref  | idx_user_gender | idx_user_gender | 2       | const | 2492574 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而 name 的离散度更高，比方“陈艮”的这名字，只须要扫描一行。

ALTER TABLE user ADD INDEX idx_user_name (name); 

EXPLAIN SELECT * FROM `user` WHERE name = '陈艮';

+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user      | NULL       | ref  | idx_name | idx_name | 1023    | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

查看表上的索引，Cardinality [kɑ:dɪ’nælɪtɪ]代表基数，代表预估的不反复的值的数量。索引的基数与表总行数越靠近，列的离散度就越高。

mysql> show indexes from user;
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY           |            1 | id          | A         |     4985145 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_name          |            1 | name        | A         |     2605146 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_user_gender   |            1 | gender      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | comidx_name_phone |            1 | name        | A         |     2595718 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | comidx_name_phone |            2 | phone       | A         |     4972647 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

如果在索引 B+Tree 构造外面的反复值太多，MySQL 的优化器发现走索引跟应用全表扫描差不了多少的时候，就算建了索引，也不肯定会走索引。

2、组合索引最左匹配

后面咱们说的都是针对单列创立的索引，但有的时候咱们的多条件查问的时候，也会建设组合索引。单列索引能够看成是非凡的组合索引。

比方咱们在 user 表下面，给 name 和 phone 建设了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,phone);

组合索引在 B+Tree 中是复合的数据结构，它是依照从左到右的程序来建设搜寻树的 (name 在右边，phone 在左边)。

从这张图能够看进去，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。

这个时候咱们应用 where name=‘wangwu‘and phone =‘139xx‘去查问数据的时候，B+Tree 会优先比拟 name 来确定下一步应该搜寻的方向，往左还是往右。如果 name 雷同的时候再比拟 phone。然而如果查问条件没有 name，就不晓得第一步应该查哪个 节点，因为建设搜寻树的时候 name 是第一个比拟因子，所以用不到索引。

2.1、什么时候用到组合索引

所以，咱们在建设组合索引的时候，肯定要把最罕用的列放在最右边。比方上面的三条语句，能用到组合索引吗?

• 1)应用两个字段，能够用到组合索引:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陈艮' AND phone = '13601994087';
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys    | key               | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | comidx_name_phone | comidx_name_phone | 1070    | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 2)应用右边的 name 字段，能够用到组合索引:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陈艮';
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | comidx_name_phone | idx_name | 1023    | const |   19 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 3)应用左边的 phone 字段，无奈应用索引，全表扫描:

mysql> EXPLAIN SELECT * FROM user WHERE  phone = '13601994087';
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4985148 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.2、如何创立组合索引

当创立 (a,b,c) 联结索引时，相当于创立了 (a) 单列索引，(a,b)组合索引以及 (a,b,c) 组合索引，想要索引失效的话, 只能应用 a 和 a,b 和 a,b,c 三种组合；当然，b,a 也是好使的，因为 sql 会对它优化。

用 where b=? 和 where b=? and c=? 和 where a=? and c=? 是不能应用到索引。不能不用第一个字段，不能中断。

这里就是 MySQL 组合索引的最左匹配准则。

3、笼罩索引

3.1、回表

在聚簇索引里，通过辅助索引查找数据，先通过索引找到主键索引的键值，再通过主键值查出索引外面没有的数据，它比基于主键索引的查问多扫描了一棵索引树，这个过程就叫回表。

例如:select * from user where name =‘lisi’;

3.2、笼罩索引

在辅助索引外面，不论是单列索引还是联结索引，如果 select 的数据列只用从索引中就可能获得，不用从数据区中读取，这时候应用的索引就叫做笼罩索引，这样就防止了回表。

咱们先来创立一个联结索引:

-- 创立联结索引
ALTER TABLE user add INDEX 'comixd_name_phone' ('name','phone');

这三个查问语句都用到了笼罩索引:

EXPLAIN SELECT name,phone FROM user WHERE name= '陈艮' AND phone = '13601994087';
EXPLAIN SELECT name FROM user WHERE name= '陈艮' AND phone = '13601994087';
EXPLAIN SELECT phone FROM user WHERE name= '陈艮' AND phone = '13601994087';

Extra 外面值为“Using index”代表应用了笼罩索引。

mysql> EXPLAIN SELECT name FROM user_innodb WHERE name= '陈艮' AND phone = '13601994087';
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys              | key               | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | idx_name,comidx_name_phone | comidx_name_phone | 1070    | const,const |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

select *，用不到笼罩索引。

很显著，因为笼罩索引缩小了 IO 次数，缩小了数据的访问量，能够大大地晋升查问效率。

4、索引条件下推(ICP)

“索引条件下推”，称为 Index Condition Pushdown (ICP)，这是 MySQL 提供的用某 一个 索引对 一个 特定的表从表中获取元组”，留神咱们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连贯而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种形式。它的作用如下

• 一是阐明缩小残缺记录（一条残缺元组）读取的个数；
• 二是阐明对于 InnoDB 汇集索引有效，只能是对 SECOND INDEX 这样的非聚簇索引无效。

敞开 ICP:

set optimizer_switch='index_condition_pushdown=off';

查看参数:

show variables like 'optimizer_switch';

当初咱们要查问所有名字为陈艮，并且手机号码后四位为 4087 这个人。查问的 SQL:

SELECT * FROM user WHERE name= '陈艮'  and phone LIKE '%4087' ;

这条 SQL 有两种执行形式:

• 1、依据组合索引查出所有名字是’陈艮’的二级索引数据，而后回表，到主键索引上查问全副符合条件的数据(19 条数据)。而后返回给 Server 层，在 Server 层过滤出手机号码后四位为 4087 这个人。
• 2、依据组合索引查出所有名字是’陈艮’的二级索引数据(19 个索引)，而后从二级索引 中筛选出手机号码后四位为 4087 的索引(1 个索引)，而后再回表，到主键索引上查问全副符合条件的数据(1 条数据)，返回给 Server 层。

很显著，第二种形式到主键索引上查问的数据更少。

留神，索引的比拟是在存储引擎进行的，数据记录的比拟，是在 Server 层进行的。而当 phone 的条件不能用于索引过滤时，Server 层不会把 phone 的条件传递 给存储引擎，所以读取了两条没有必要的记录。

这时候，如果满足 name=’陈艮’的记录有 100000 条，就会有 99999 条没有 必要读取的记录。

执行以下 SQL，Using where:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陈艮' AND phone LIKE '%4087';
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | comidx_name_phone | comidx_name_phone | 1023    | const |   19 |    11.11 | Using where |
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using Where 代表从存储引擎取回的数据不全副满足条件，须要在 Server 层过滤。

先用 name 条件进行索引范畴扫描，读取数据表记录，而后进行比拟，查看是否合乎 phone LIKE‘%4087’的条件。此时 19 条中只有 1 条符合条件。

五、索引创立应用总结

因为索引对于改善查问性能的作用是微小的，所以咱们的指标是尽量应用索引。

5.1. 索引的创立

依据上一节的剖析，咱们总结出索引创立的一些留神点：

• 1、在用于 where 判断 order 排序和 join 的 (on) 字段上创立索引
• 2、索引的个数不要过多。——节约空间，更新变慢。
• 3、区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。
• 4、频繁更新的值，不要作为主键或者索引。——页决裂
• 5、组合索引把散列性高 (区分度高) 的值放在后面。——最左前缀匹配准则
• 6、创立复合索引，而不是批改单列索引。——组合索引代替多个单列索引（因为 MySQL 中每次只能应用一个索引，所以常常应用多个条件查问时更适宜应用组合索引）
• 7、过长的字段，怎么建设索引?——应用短索引。
  当字段值比拟长的时候，建设索引会耗费很多的空间，搜寻起来也会很慢。咱们能够通过截取字段的后面一部分内容建设索引，这个就叫前缀索引。

create table shop(address varchar(120) not null); 
alter table shop add key (address(12));

• 8、不倡议用无序的值 (例如身份证、UUID) 作为索引——当主键具备不确定性，会造成叶子节点频繁决裂，呈现磁盘存储的碎片化

5.2. 什么时候会用不到索引

• 1、索引列上应用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、计算(+ – * /):

explain SELECT * FROM 't2' where id+1 = 4;

• 2、字符串不加引号，呈现隐式转换

explain SELECT * FROM 'user' where name = 136; 

explain SELECT * FROM 'user' where name = '136';

• 3、like 条件中后面带 %

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗? 为什么?

explain select * from user where name like 'wang%'; 

explain select * from user where name like '%wang';

过滤的开销太大，所以无奈应用索引。这个时候能够用全文索引。

• 4、负向查问

NOT LIKE 不能:

explain select *from employees where last_name not like 'wang'

!= (<>)和 NOT IN 在某些状况下能够:

explain select * from user where id not in (1) 
explain select * from user where id <> 1

• 5. 索引不会蕴含有 NULL 值的列

只有列中蕴含有 NULL 值都将不会被蕴含在索引中，复合索引中只有有一列含有 NULL 值，那么这一列对于此复合索引就是有效的。所以咱们在数据库设计时不要让字段的默认值为 NULL。

• 6，排序的索引问题

MySQL 查问只应用一个索引，因而如果 where 子句中曾经应用了索引的话，那么 order by 中的列是不会应用索引的。因而数据库默认排序能够符合要求的状况下不要应用排序操作；尽量不要蕴含多个列的排序，如果须要最好给这些列创立复合索引。

留神一个 SQL 语句是否应用索引，跟数据库版本、数据量、数据抉择度都有关系。

其实，用不必索引，最终都是优化器说了算。
优化器是基于什么的优化器?
基于 cost 开销(Cost Base Optimizer)，它不是基于规定(Rule-Based Optimizer)，也不是基于语义。怎么样开销小就怎么来。

以上是我对索引相干常识的整顿，心愿你能有所播种，参考如下！

<big>参考：</big>

【1】：《高性能 MySQL》

【2】：MySQL 索引原理及慢查问优化

【3】：极客工夫《MySQL45 讲》

【4】：MySQL 索引背地的数据结构及算法原理

【5】：MySQL 索引原理，一篇从头到尾讲清楚

【6】：Mysql 四种常见的索引

【7】：MySQL 探秘(三)MySQL 索引原理

【8】：一口气搞懂 MySQL 索引所有知识点