由浅入深全面分析MySQL索引底层原理

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

1.2 索引的分类

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

1.2.1 主键索引

在一张表中把某个列设为主键的时候，则该列就是主键索引，一张表中只能够有一个主键索引。

当一条记录进行插入的时候，必须要包含一个索引的值，这个值可以是主键，也可以是唯一键，甚至可以是一个6字节的rowId，那么，在实际选择的时候会按照主键、唯一键、6字节的rowId 这样的顺序来选择索引的key值。

create table TestA (  
	ID int(11) not null key auto_increment,
	Name varchar(20) not null default '',
    primary key(`ID`)
)engine=innodb auto_increment=1 default charset=utf8;

这里的 ID 就是表的主键ID。如果在创建表时没有指定主键ID，也可以在创建表之后手动添加：

alter table table_name add primary key (column_name);

1.2.2 普通索引

用表中的普通列构建的索引，称之为普通索引。普通索引没有任何限制。

create index index_name on table_name(column1);
alter table table_name add index index_name(column1);

1.2.3 全文索引

全文索引主要针对文本文件，比如文章、标题。在MySQL5.6之前，只有MyISAM存储引擎支持全文索引，MySQL5.6之后InnoDB存储引擎也开始支持全文索引。

create table TestA(  
	ID int(11) not null auto_increment,  
	Title varchar(20),  
	Content text,  
	fulltext(Title,Content),
    primary key(`ID`)
)engine=innodb auto_increment=1 default charset=utf8;

insert into TestA(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.2.4 唯一索引

见名知义，索引列中的值必须是唯一的，不能重复的，但是允许为空值null。TestA表中Name就是唯一索引，相比主键索引ID，主键字段不能为null，也不能重复。

create table TestA (  
	ID int(11) not null key auto_increment,
	Name varchar(20) not null default '',
    primary key(`ID`),
    unique key 'UK_IX_Name'(`Name`)
)engine=innodb auto_increment=1 default charset=utf8;

1.2.5 组合索引

用多个列组合构建的索引，称之为组合索引，这多个列中的值不允许有空值null。

alter table 'table_name' add index index_name('col1','col2','col3')；

组合索引遵循 “ 最左前缀匹配原则 ” ，使用时最好把最常作为检索或排序的列放在最左边，依次递减。组合索引相当于建立了 col1col2col3 、col1col2、col1 三个索引，而 col2 或者 col3 是不能使用索引的。在使用组合索引的时候，可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引。

alter table 'table_name' add index index_name(col1(4),col2（3))；

表示使用 col1 的前4个字符和 col2 的前3个字符作为组合索引。

1.3 索引机制

我们这里先简单剖析一下索引的机制，为接下来的深入做一些铺垫。

1.3.1 索引加快数据查询的原理

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。在我们添加完索引之后，MySQL一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，使用能够大幅度提高查询效率的折半查找方式进行查找，找到相应的键从而获取数据。

1.3.2 索引的代价

创建索引是会产生索引文件的，占用磁盘空间。索引文件是一个二叉树类型的文件，可想而知，我们的DML操作【数据操作语言，对表记录的（增、删、改）操作】，同样也会对索引文件进行修改，所以性能会相应的有所下降。

2、索引存储的数据结构

上面已经说到，索引实际上是数据库中满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。可能我们都知道，MySQL索引是B+树数据结构，当然，实际上索引还有哈希表、有序数组等常见的数据结构。

2.1 哈希表

哈希表是一种以键-值对（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单：把值放在数组里，用一个哈希函数把key换算成一个确定的存储位置，然后把value放在数组的这个位置。

不可避免地，多个key值经过哈希函数的换算，有可能会出现同一个存储位置值的情况。处理这种情况的一种方法是：拉出一个链表。

所以，需要注意，哈希表后的链表并不是有序的，区间查询的话需要扫描链表。所以，哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL存储引擎。

2.2 有序数组

另外一个大家比较熟悉的数组结构，有序数组在等值查询和范围查询场景中的性能都非常优秀。

如果仅仅看查询效率，有序数组是非常棒的数据结构。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录，就必须得挪动后面的所有记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是2017年某个城市的所有人口信息等这类不会再被修改的静态数据。

这两种都不是最主要的索引，常见的索引使用的数据结构是树结构，树是数据结构里相对复杂一些的数据结构，我们来一步步认识索引的树结构。

2.3 二分查找

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

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

上面提到的有序数组的等值查询和比较查询效率非常高，但是更新数据存在问题。为了支持频繁的修改，比如插入数据与删除数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。所以，有没有可以使用二分查找算法的链表呢？为了解决这个问题，BST (Binary Search Tree)，也就是我们所说的二叉查找树诞生了。

2.4 二分查找树

二叉树具有以下性质：左子树的键值小于根节点的键值，右子树的键值大于根节点的键值。如下图所示就是一棵二叉查找树。

在这种比较平衡的状态下，二叉树查找的时间复杂度是O(log(n))。但是，二叉查找树存在一个问题：在某些极端情况下会退化成链表。同样是【2,3,4,6,7,8】这6个数字，如果我们插入的数据刚好是有序的，那它就变成这样：

这个时候，二叉查找树其实就退化成了一个链表，它的查找的时间复杂度就和链表一样，是 O(n)。造成它 “劈叉” 的原因是什么呢? 因为左右子树深度的差值太大，这棵树的左子树根本没有节点 —— 也就是它不够平衡。所以，我们有没有左右子树深度相差不是那么大、更加平衡的树呢? —— 那就是平衡二叉树，叫做 Balanced binary search trees，或者AVL树。

2.5 平衡二叉树：AVL树

AVL Trees (Balanced binary search trees) ，平衡二叉树的定义：左右子树的深度差绝对值不能超过 1。例如左子树的深度是 2，右子树的深度只能是 1 或者 3。这个时候我们再按顺序插入【2,3,4,6,7,8】，就不会“劈叉”。

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

（1）插入 1、2、3。

当我们插入了1、2之后，如果按照二叉查找树的定义，3肯定是要在2的右边的，这个时候根节点1的右节点深度会变成2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。那应该怎么办呢？因为它是右节点下面接一个右节点，右–右型，所以这个时候我们要把2提上去，这个操作叫做左旋。

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

既然平衡二叉树能保持树的平衡，不会退化，那么我们用平衡二叉树这个结构来存储索引可以吗？—— 当然可以。如下图所示。但是，你会发现，平衡二叉树虽然能够保持树的平衡性，但是它所带来的问题是：树的每个节点只存储两个左右2个指针，导致树的高度很高。

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次IO。 InnoDB存储引擎操作磁盘的最小的单位是一页（或者叫一个磁盘块）。与主存不同的是，磁盘I/O 存在机械运动耗费，因此，磁盘I/O的时间消耗是巨大的。

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

（1）让每个节点存储更多的数据。（2）让每个节点上有更多的关键字。

节点上的关键字的数量越多，我们的指针数也就越多，也就是意味着可以有更多的分叉（我们把它叫做“路数”）。因为分叉数越多，树的深度就会减少（根节点的深度是 0）。这样，树就从【瘦高】变成了【矮胖】。这个时候，我们的树就不再是二叉的了，而是多叉，或者叫做多路。由此，引入了多路平衡查找树。

2.6 B树：多路平衡查找树

接下来，看一下多路平衡查找树，也就是B树。B树是一种“ 多路平衡查找树 ”，主要特点如下：

（1）B树的每个节点中都存储着多个元素，每个内节点可以有多个分叉。

（2）节点中的元素包含键值和数据（也即在所有的节点中都储存着真实的数据），节点中的键值从小到大排列。

（3）父节点中的元素不会出现在子节点中。

（4）所有的叶子结点都位于同一层，叶子节点具有相同的深度，叶子节点之间没有指针连接。

以上图为例，我们来简单看几个查询：

（1）如果查找 key<17，就走左边子节点，以此类推；

（2）如果查找 17<key<35，就走中间子节点，以此类推；

（3）如果查找 key>35，就走右边子节点，以此类推；

（4）如果查找 key=17，就直接命中返回；

（5）如果查找 key=35，就直接命中返回；

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

B树不支持范围查询的快速查找。你想想如下两个情况：

（1）如果我们想要查找 10和35之间的数据：查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待进一步提高。

（2）如果data存储的是行记录，行的大小随着列数的增多所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变多，查找效率就好进一步下降。

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

2.7 B+树：升级版多路平衡查找树

B+树，作为B树的升级版，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于 " 非叶子节点是否存储数据 ” 的问题。

（1）B树：非叶子节点和叶子节点都会存储键值key和数据data。

（2）B+树：非叶子节点只存储键值key，只有叶子节点才会存储数据data。同时，叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

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

来说一下这张图的重点：

（1）最外面的方块，我们称之为一个磁盘块（也就是一个节点）。可以看到，每个磁盘块包含几个数据项和指针项。如根节点磁盘块中包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即【3、4、5……、65】。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 这些并不真实存在于数据表中。

（2）叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

2.7.1 存储容量

举个例子：假设一条记录的大小是1K，一个叶子节点（一页）可以存储16条记录，那么一个非叶子节点可以存储多少个指针?

假设索引字段是bigint类型，长度为8字节。指针大小在InnoDB源码中设置为6字节，这样，一共占据14字节。

非叶子节点（一页）可以存储 16*1024/14=1170 个这样的单元（键值+指针），代表有1170 个指针。

树深度为2的时候，有 1170*1170 个叶子节点，可以存储的数据为1170*1170*16=2190 2400。在查找数据时，一个页的查找代表一次磁盘IO，也就是说，一张2000多万左右的表，查询数据最多需要访问3次磁盘。所以，在InnoDB中，B+树深度一般为1-3 层，它就能满足千万级的数据存储。

2.7.2 查找效率

我们来看一下B+树的数据查找过程：

（1）例如我们要查找 35，在根节点就找到了键值。但是，因为它不是叶子节点，所以会继续往下查找，35 是 [17,35) 的左闭右开的区间的临界值，所以会走P2指向的中间的子节点，然后继续查找，它属于 [31, +无穷) 的左闭右开的区间内，所以会走P3指向的右边的子节点，最后在叶子节点上找到了需要的数据 35 。

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

（3）添加了指向相邻叶子节点的指针，形成了带有顺序访问指针的B+树，这样做就是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

2.7.3 B+树的特点总结

总结一下，InnoDB中的B+树的特点：

（1）它是B树的变种，B树能解决的问题，B+树都能解决：B树解决的两大问题是什么？一个是：每个节点可以存储更多的关键字。另一个是：每个节点的路数更多。

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

（3）B+树的磁盘读写能力相对于B树来说更强：根节点和非叶子节点不保存数据区，所以一个非叶子节点可以保存更多的关键字，一次磁盘IO加载的关键字更多。

（4）B+树的范围查找能力更强、排序能力更强：因为叶子节点上有下一个数据区的指针和上一个数据区的指针，在叶子节点上的数据形成了双向链表。

（5）B+树的查找效率更加稳定：B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。

3、聚簇索引和非聚簇索引

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

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

3.1 MyISAM存储引擎：非聚簇索引

（1）MyISAM存储引擎，采用的是非聚簇索引。非聚簇索引的主键索引和辅助索引基本上是相同的，只是主键索引不允许重复、不允许有空值。他们的叶子节点的key都存储着指向键值对应的真实数据的物理地址。

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

（3）非聚簇索引中的数据是根据数据的插入顺序保存的。

（4）思考：既然非聚簇索引的主键索引和辅助索引指向相同的内容，为什么还要辅助索引呢？索引不就是用来查询的吗，用在哪些地方呢？不就是 where 和 order by 语句后面吗？那么，如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。

3.2 InnoDB存储引擎：聚簇索引

（1）在InnoDB存储引擎中，使用的是聚簇索引。

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

（3）在聚簇索引中，主键索引的叶子节点存储的是键值和键值所对应的数据本身。辅助索引的叶子节点存储的是键值和键值所对应的数据的主键键值。

从上图中可以看到：

（1）辅助索引的叶子节点的data存储的是主键的值；主键索引的叶子节点的data存储的是记录本身。这也就是说：数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么，索引的顺序和数据本身的顺序就是相同的。

（2）因为聚簇索引中的辅助索引存储的是主键的键值，因此，可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。

（3）聚簇索引在插入新数据的时候比非聚簇索引慢很多，这是因为在插入新数据时，需要检测主键是否重复，这就需要遍历主键索引的所有叶子节点。

4、索引使用原则

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)

简单来说：

（1）如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

（2）离散度越高，字段的区分度越好，索引的使用效率就越高。

了解了列的离散度的概念之后，我们再来思考一个问题，我们在 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 代表基数，代表预估的不重复的值的数量。索引的基数与表总行数越接近，列的离散度就越高。

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+树结构里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算构建了索引，也不一定会走索引。

4.2 联合索引的最左匹配原则

前面我们说的都是针对单列创建的索引，但是有的时候我们需要多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。

比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。

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

联合索引在 B+树中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，phone 在右边)。

从这张图可以看出：name 是有序的，phone 是无序的。当 name 相等的时候， phone 才是有序的。

这个时候我们使用 where name= ‘wangwu‘ and phone = ‘139xx ‘ 去查询数据的时候， B+树会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再去比较 phone。但是，如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以此时会用不到联合索引。

4.2.1 什么时候使用联合索引

所以，我们在建立联合索引的时候，一定要把最常用作查询条件的列放在最左边。比如下面的三条语句，能用到组合索引吗?

（1）使用 name和phone 两个字段，可以用到联合索引：

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 也是好使的，因为MySQL优化器会对它进行顺序优化。

用 where b=? 和 where b=? and c=? 和 where a=? and c=? 都是不能使用到联合索引的。不能不用第一个字段，中间的索引字段不能中断。这里就是 MySQL 联合索引的最左匹配原则。

4.3 覆盖索引

4.3.1 回表

在InnoDB存储引擎的聚簇索引里，通过辅助索引查找数据：首先通过辅助索引找到主键索引的键值，然后再通过主键值在主键索引中查找出目标数据，它比基于主键索引的查询多扫描了一棵主键索引树，这个过程就叫做回表。例如：

select * from user where name = ‘lisi’;

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

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

（1）减少完整记录（一条完整元组）读取的个数。

（2）对于InnoDB聚集索引无效，只能是对 SECOND INDEX 二级索引这样的非聚簇索引有效。

关闭ICP：

set optimizer_switch='index_condition_pushdown=off';

查看ICP参数：

show variables like 'optimizer_switch';

现在我们要查询所有名字为陈艮并且手机号码后四位为4087 的人。查询的 SQL：

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

这条SQL有两种执行方式：

（方式1）根据联合索引查出所有名字是’陈艮’的二级索引数据，然后回表，到主键索引上查询出全部符合条件的数据 (19 条记录) ，然后把这19条记录返回给 Server 层，在 Server 层过滤出手机号码后四位为4087的人。

（方式2）根据联合索引查出所有名字是’陈艮’的二级索引数据 (19 条记录) ，然后从二级索引中筛选出手机号码后四位为4087的记录 (1 条记录)，然后再回表，到主键索引上查询全部符合条件的完整数据 (1 条记录)，返回给 Server 层。

很明显：第二种方式到主键索引上查询的数据更少。

注意：索引的比较是在存储引擎层面上进行的。数据记录的比较，是在 Server 层进行的。而当 phone 的条件不能用于索引过滤时，Server 层不会把 phone 的条件传递给存储引擎，所以读取了18条没有必要的记录。这时候，如果满足 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、索引创建使用总结

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

5.1 创建索引的注意点

根据上一节的分析，我们总结出索引创建的一些注意点：

（1）在用于 where 判断、order by 排序、group by 分组、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';  -- 不会使用
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语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

99、参考

（1）https://blog.csdn.net/cmm0401/article/details/115787852

（2）https://blog.csdn.net/cmm0401/article/details/115715634

（3）https://www.jianshu.com/p/6f69cd528453

（4）

风暴计划

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
由浅入深全面分析MySQL索引底层原理

由浅入深全面分析MySQL索引底层原理1、索引基础索引是MySQL优化中最重要的手段之一，本文从基础到原理，让你重新认识并掌握索引。1.1 什么是索引MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。索引，对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化，应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。通俗来讲，索引是一种数据结构，类似于一本书的目录，用来提高查询数据的效率。...
复制链接

扫一扫