MySQL探秘(三)MySQL索引原理

MySQL探秘(三)MySQL索引原理

一、索引介绍

1.1什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

1.2为什么要用索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据， 直到找到这条数据。

但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊 的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据 了。

就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容。

1.3索引的类型

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型 的字段才可以创建全文索引，比如 char、varchar、text。

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

创建

create table user ( 
    name varchar(50), 
    fulltext index(name) 
);

使用

select * from fulltext_test where match(content) against('zhangsan' IN NATURAL LANGUAGE MODE);

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个字符作为索引

二、索引存储的数据结构

查询是数据库的最主要功能之一。我们都希望查询速度能尽可能快，因此数据库系统的设计者会从查询算法角度优化

最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的

好在CS的发展提供了很多更优秀的查找算法，如二分查找（binary search）、二叉树查找（binary tree search）等

稍微分析一下会发现，每种查找算法都只能应用于特定数据结构之上，如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树，但数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）

所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法

这种ADT，就是索引

2.1二分查找

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

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

有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题， 可能要挪动大量的数据(改变 index)，所以只适合存储静态的数据。

为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链 表，它的查找效率还是不够高。

所以，有没有可以使用二分查找的链表呢?

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

2.2二叉查找树(BST Binary Search Tree)

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

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

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，如果我们插入的数据刚好是有序的：

这个时候我们的二叉查找树变成了什么样了呢?

它会变成链表(我们把这种树叫做“斜树”)，这种情况下不能达到加快检索速度 的目的，和顺序查找效率是没有区别的。

造成它倾斜的原因是什么呢? 因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。 所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢? 这个就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树(AVL 是发明这个数据结构的人的名字)。

2.3平衡二叉树（AVL Tree）

AVL Trees (Balanced binary search trees) 平衡二叉树的定义:左右子树深度差绝对值不能超过 1。 是什么意思呢?比如左子树的深度是 2，右子树的深度只能是 1 或者 3。 这个时候我们再按顺序插入 1、2、3、4、5、6，一定是这样，不会变成一棵“斜树”。

那它的平衡是怎么做到的呢?怎么保证左右子树的深度差不能超过 1 呢?

插入 1、2、3。

我们注意看:当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在 2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它 没有子节点，所以就会违反平衡二叉树的定义。

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

所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的 操作。

2.3.1AVL 树用于存储索引数据

索引的数据，是放在硬盘上的。查看数据和索引的大小:

SELECT
CONCAT(ROUND(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS data_len,
CONCAT(ROUND(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS index_len
FROM
information_schema.TABLES
WHERE
table_schema = 'test'
AND table_name = 'user_innodb';

结果

+----------+-----------+
| data_len | index_len |
+----------+-----------+
| 232.73MB | 341.08MB  |
+----------+-----------+
1 row in set (0.00 sec)

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。 InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁盘块)，大小是 16K(16384 字节)。

那么，一个树的节点就是 16K 的大小。

如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个 或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候， 浪费了大量的空间。

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

如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数越多，消耗的时间就越多。

比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=37 的时候，要查询

两个子节点，就需要跟磁盘交互 3 次，如果我们有几百万的数据呢?这个时间更加难以 估计。

所以我们的解决方案是什么呢?

第一个就是让每个节点存储更多的数据。

第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有更多的分叉(我们把它叫做“路数”)。

因为分叉数越多，树的深度就会减少(根节点是 0)。 这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子? 这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

2.4多路平衡查找树（B-Tree）

Balanced Tree
这个就是我们的多路平衡查找树，叫做 B Tree(B 代表平衡)。
跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。 它有一个特点:分叉数(路数)永远比关键字数多 1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。

假如树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

1.每个叶子结点的高度一样，等于h；
2.每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
3.叶子结点指针都为null；
4.非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

如果查找key<17，就走左边子节点；

如果查找17<key<35就走中间子节点；

如果查找key>35就走右边子节点；

如果查找key=17，直接命中；

如果查找key=35，直接命中；

从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。

节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。

2.5加强版多路平衡查找树(B+Tree)

我们来看一下 InnoDB 里面的B+Tree的存储结构:

1.B+Tree中的非叶子结点不存储数据，只存储键值；

2.B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；

3.B+Tree的每个非叶子节点由n个键值key和n个指针point组成，键值数量和指针数量相同；

4.B+Tree搜索到键值不会直接返回，会到最后一层的叶子节点；

5、B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数 据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

6、它是根据左闭右开的区间 [ )来检索数据。

2.5.1存储容量

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

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

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

2.5.2查询效率

我们来看一下 B+Tree 的数据搜寻过程:

1)比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以 会继续往下搜寻，28 是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然 后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后 在叶子节点上找到了需要的数据。

2)第二个，如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高 了区间查询效率(不需要返回上层父节点重复遍历查找)。

• 带顺序索引的B+TREE

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

特点

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

1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题 是什么?(每个节点存储更多关键字;路数更多)

2)扫库、扫表能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据)

3) B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多)

4)排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)

5)效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的)

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

分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序

非聚簇索引的解释是:索引顺序与数据物理排列顺序无关

首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

3.1 MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

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

非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)

*最开始我一直不懂既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢，后来才明白索引不就是用来查询的吗，用在那些地方呢，不就是WHERE和ORDER BY 语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。

3.2 InnoDB——聚簇索引

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

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

聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。

在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

*使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

*因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。

*聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别

从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

此外MyISAM和innoDB的区别总结如下：

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
此外，Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

四、索引使用原则

4.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)

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 的优化器发现走索引跟使用全表扫描差

不了多少的时候，就算建了索引，也不一定会走索引。

4.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 是第一个比较因子，所以用不到索引。

4.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)

4.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 联合索引的最左匹配原则。

4.3. 覆盖索引

回表:

非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

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

在辅助索引里面，不管是单列索引还是联合索引，如果 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';

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.4. 索引条件下推(ICP)

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

一是说明减少完整记录（一条完整元组）读取的个数；

二是说明对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚集索引有效。

关闭 ICP:

set optimizer_switch='index_condition_pushdown=off';

show variables like 'optimizer_switch';

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)

set optimizer_switch='index_condition_pushdown=on';

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 index condition |
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

把 phone LIKE ‘%4087’下推给存储引擎后，只会从数据表读取所需的 1 条记录。 索引条件下推(Index Condition Pushdown)，5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

5. 索引的创建与使用

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。

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));

问题是，截取多少呢?截取得多了，达不到节省索引存储空间的目的，截取得少了， 重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?

先看一下字段在全部数据中的选择度:

select count(distinct address) / count(*) from shop;

通过不同长度去计算，与全表的选择性对比:

select count(distinct left(address,10))/count(*) as sub10, count(distinct left(address,11))/count(*) as sub11, count(distinct left(address,12))/count(*) as sub12, count(distinct left(address,13))/count(*) as sub13
from shop;

8、为什么不建议用无序的值(例如身份证、UUID )作为索引?

——当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

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

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

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

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

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

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

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

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)，也不是基于语义。怎么样开销小就怎么来。

https://dev.mysql.com/doc/refman/5.7/en/cost-model.html