SQL索引 – LarkdropPARA

索引的引入

索引（Index）是数据库中一种特殊的数据结构，它用于提高数据库查询的效率和速度。在数据库中，索引类似于书籍中的目录，我们可以根据关键字快速定位到需要查看的内容所在的位置。

在数据库中，常用的索引类型包括主键索引、唯一索引、普通索引等。不同的索引类型适用于不同的查询场景，开发人员需要根据实际需求选择合适的索引类型。

同时，在MySQL中我们如果使用不同的存储引擎，比如常见的MyISAM，InnoDB等，索引的存储方式也会有所不同

存储引擎

先来看看MySQL的架构

连接器：负责接收处理客户端的连接请求，建立连接，管理连接等工作
查询解析器：在这一步MySQL会将SQL语句解析为树，根据语法规则对语句进行验证，比如关键字是否正确，关键字顺序是否正确等等
查询优化器: 一条查询可以有很多种执行方式，最终返回相同的结果，优化器负责分析查询，评估各种可能的执行路径，并选择最有效的路径来执行该查询。这涉及到决策如何连接表、选择使用哪些索引、决定查询的执行顺序等。
执行器：执行器的功能是按照优化器提供的执行计划来执行查询。它负责实际上的数据读取、联接表、过滤行等操作。
存储引擎：数据库系统中负责数据存储和检索，在MySQL中有多种不同的存储引擎，每个存储引擎都提供了不同的存储机制、索引技术、以及加锁方式等等功能。

这里我们重点需要关注的是存储引擎，对于索引而言，不同的存储引擎其索引的实现有所差异，所以我们在提及索引的时候，都会先说明所使用的存储引擎。MySQL中提供了很多的存储引擎，常见的有InnoDB，MyISAM等，但是最常见和常用，以及MySQL默认的还是InnoDB存储引擎，所以接下来，我们主要基于InnoDB存储引擎来讲解索引。

MySQL数据的存储(InnoDB)

实际上我们存储在MySQL中的数据，最终都是存储在磁盘中的。因此，当我们对MySQL进行查询操作的时候，会频繁读取磁盘文件内容，那么一次至少读取磁盘中的多少数据到内存呢？一次读取至少一页数据，16KB大小的内容。

对于MySQL而言，看到的是一页一页的数据。每一页中存储的是什么数据呢？我们目前只关注表中数据，所以我们可以认为页中存储的是表中的数据。但是请注意一张表中的数据，仅一个页中就一定能存的下吗？当然不是，所以一张表中的数据，可能存储在多个不同的页中。

所以总结一下：

页是MySQL(InnoDB)存储管理的基本单位
一页中可以存储多条数据
一张表中的数据可以存储在多个页中。
一张表对应的多个数据页会组成一个双向链表

关于页我们还需要说明的是，在MySQL中有多种不同类型的页，用来存储表中数据的页，我们称之为数据页。存储的问题搞清楚了，那么随之而来的另外一个问题是，一个数据页中存储了多条行数据，我们如何快速定位到其中的某一条目标数据呢？在MySQL的实现中，它主要是通过二分查找来实现的。但是，一张表中的数据可能存在于不同的数据页中，怎么在一张表对应的多页中快速查找到某一条记录呢？其实思路也不难：

首先定位到存储目标数据的数据页
在该页中进行二分查找(这也意味着，一页中的多行数据是有序的)

所以，快速查找到表中的一条记录的问题，就演变成了如何快速定位到目标数据页的问题了。那么如何快速定位到目标数据页呢？这就是索引要解决的问题了！

MySQL索引(InnoDB)

接下来，我们就重点研究下InnoDB中的索引。

索引数据结构(InnoDB)

假如有如下的一张表，我们以主键列id为条件，查询目标行数据。

CREATE TABLE `index_demo` (
  `id` int NOT NULL,
  `age` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

假设该表中当前存储了很多数据，多到数据需要多个数据页来存储，并且在数据页中的记录以id主键值的大小排序，我们可以先自己来思考以下这个问题。如果执行如下查询：

select * from index_demo where id = 5;

我们如何快速查找到这条记录呢？很遗憾，目前我们只能遍历表的每一个页，在每一页中根据id主键值进行二分查找。但是很显然，因为要遍历数据页，那么查找效率不是很理想，能否实现对于数据页的二分查找，即利用二分查找的方式，快速确定目标id值所在的数据页呢？可以，但是会遇到两个问题。

第一个问题是，虽然每一页内的数据按照主键id值有序的，但是不同页之间记录的id主键值可能并没有固定的大小顺序。所以，我们可以在添加一个约束，让下一页的记录的id值都不小于上一页的记录id值，这样一来，页与页之间也变得有序了，我们就可以用类似的思路对页进行二分查找，如何对页进行二分查找呢？如图，如果我们记录每个数据页中，id的最小值，那么在进行二分查找的时候我们可以这样判断：

如果待查找的id值大于当前页A中的的最小id值
同时，待查找的id值小于下一页B的最小id值
这时就可以确定待查找的id值对应的数据在当前页A中

第二个问题是，因为在MySQL中分配给一张表的多个页可能物理上并不连续，它们以双向链表的形式串联起来，如图所示：

显然我们无法直接在一个双向链表上实现二分查找，但是如何实现二分查找的效果呢？这也难不倒我们，我们只需要将所有的数据页的最小值，集中到一个数据页中即可，所以我们需要完成如下步骤：

为每一页创建一条目录项记录，目录项记录中只需要记录每一页的最小id值，以及该页的页号
将每一页对应的目录项都存储在一个数据页中
保证存储在数据页中的目录项也按照id值排序

这样一来我们在查找的时候

首先访问目录页中的目录项，做二分查找
判断如果某个目录项的id值小于待查找的id值，且待查找的id值小于后一个目录项的id值，比如 1 < 5 < 51
那么待查找的id值就在该目录项所在的页中。

因为现在数据页中也会存放目录项记录了，所以为了好区分，我们称存储目录项的页为目录页，称存储行数据的页为普通数据页。(这种说法并非官方说法)

但是这样就可以了吗? 当然还是不行，因为一张表包含的普通数据页数量可能很多，多到什么程度呢？多到一个目录页都放不下普通数据页对应的目录项了，此时，我们的目录项就需要存储在多个目录页中，并且这些目录页之间继续用双向链表串联起来。这样一来存储目录项的问题解决了，但是还能对目录页中的目录项记录做二分查找吗？还是不行，原因有两个：

目录页内的目录项记录根据id有序，但是目录页之间无序
无法直接对链表做二分查找

所以为了实现对目录页中的目录项的二分查找，先来解决第一个问题保证：

首先已知目录页中的目录项是有序的(根据id值有序)
在此基础上在施加约束，即上一个目录页的目录项id值，小于下一目录页的所有目录项id值

其次，再来解决第二个问题，为每一个目录页创建对应的目录项记录，并保存在数据页中即可(即普通数据页的目录页的目录页)：

每一个目录页对应的目录项记录中只需要存储每一目录页的最小id值，以及该目录页的页号
每一个目录页对应的目录项都存储在一个数据页中
保证页中的目录项按照id值排序，如图所示

因为这个新的数据存储的是目录页对应的目录项，所以我们称这个数据页为目录页的目录页(非官方说法)

这样一来，我们在查询的时候，就可以按照如下流程来完成：

在目录页对应的目录页中快速定位到某个目录页的目录项，并找到该目录项对应的目录页
然后在目录页中继续二分查找，就可以找到目标数据对应的目录项，并找到目录项对应的普通数据页
再在普通数据页中做二分查找，就可以找到对应的目标行数据了

接下来，有同学可能还会有疑惑，那目录页的目录项也很多，多到一个数据页放不下呢？其实和之前一模一样的思路，依次类推即可，于是就形成了如下的结构：

这不就是一颗树的形状嘛，更准确的来说，其实它就是一颗B+树！这样的一颗B+树，就对应的一个索引，通过它可以快速定位到目标数据。

需要注意的是：

在这颗B+树索引中，树中节点都是页(16kb)
用来存储真正的表中数据的数据页我们称它们为B+树中的叶子节点
用来存储目录项记录的节点，我们称之为非叶子节点，或者内节点
所有的叶子节点都在同一层，且组成了一个双向链表，我们称该层位第0层

总结一下，在数据库表中查找一条记录的过程，如果有索引的话，其实就转化成了在B+树从根节点，到叶子节点的一次访问过程。但是，因为每访问B+树中的一个节点都需要进行一次磁盘IO，所以B+树的高度就决定了从根到叶的一次访问过程中所进行的IO次数，从这一点上来说，使用B+树作为索引也是很合适的，相比于我们非常熟悉的平衡二叉树，B+树作为一颗平衡多叉树，大大降低了树的高度，从而减少IO次数，所以一般来说MySQL中B+树索引的高度不会超过4层！

聚簇索引(InnoDB)

我们上面根据主键列的值所创建的那颗B+树，即主键列索引就是聚簇索引。对于数据库中我们定义的所有表，MySQL都会自动根据主键列创建一颗的B+树索引，即聚簇索引，聚簇索引具有两个非常重要的特征：

首先，索引即数据。即聚簇索引这颗B+树，即是一个索引，而且还包含了表中每一行的完整的数据，这里可以和我们前面讲的书和目录的例子做一个类比，一本书的目录并未包含书中任何一页的完整内容，它只是帮我们快速定位到某一页的真正内容。如果把书的目录当做索引，那么它就和我们的聚簇索引不同了，因为聚簇索引的叶子节点中(普通数据页)，就存储了表中的每一行完整数据！

其次，索引中的数据都以主键值排序，表现在：

普通数据页和目录页内存储的数据，按照主键值排序
如果分层来看，那么每一层的数据页，都按照id值有序

还有同学可能会有疑惑，如果对于一张表而言，没有定义主键那这张表是不是就没有聚簇索引了呢？并不是，如果一张表没有定义主键，那么MySQL或做如下工作：

会为当前表中添加了NOT NULL 和UNIQUE域约束的列创建聚簇索引
如果既没有定义主键，也没有定义了NOT NULL和UNIQUE的列，那么MySQL会自动生成一个名为row_id的隐藏列，MySQL保证该列的值在一张表中不会重复。
然后根据row_id这一列的值创建聚簇索引

聚簇索引非常重要，每张表都具有聚簇索引，且其叶子节点中存储着表中数据！

二级索引(InnoDB)

讲完了聚簇索引，当我们根据id值进行查询的时候，可以根据聚簇索引快速找到目标行。但是，如果我们的查询条件不是主键值，而是其他列的值，还可以用到聚簇索引来实现快速的查询吗？答案当然是否定的，因为聚簇索引只根据id值有序，从而构造出了id值有序的B+树。

有同学可能马上就要问了，只有根据主键值查询才能用到索引吗？当然不是，因为一张表可以具有多个索引，我们可以根据需要，创建除主键列以外的其他列的B+树索引，这样的索引我们称之为二级索引。比如，我们可以对age列创建索引，如图所示

这颗根据age列所创建的B+树索引(二级索引)，与聚簇索引相比，有如下区别:

首先，索引非数据。在这颗二级索引B+树的叶子节点(普通数据页)，并没有存储完整的表中行数据，而是仅仅存储了该行数据的索列的值(age值) + 该行数据的主键值

其次，二级索引中的数据都是根据索引列age的值来排序，具体表现在:

叶子结点，即普通数据页和目录项数据页内的条数据，按照age列的值有序
分层来看，每层数据页也按照age列有序

最后，对于我们创建的任何一张表而言，聚簇索引永远只会有一个，但是二级索引，我们可以根据需要创建多个！

接下来，我们看看如下的SQL，模拟根据age列创建的二级索引，执行查询的过程。

select * from index_demo where age = 14;

查询过程如下:

先找到根节点所在的页面即页16，经过二分查找确定，age值为14的记录对应的目录项记录所在的页为72(12 < 15 < 80)
然后再到页72中，确定age为14的数据所在的普通数据页为100(对应的目录项没画出来，但是可以推出来)
然后再到页100中，在页内通过二分查找，找到age值为14的行数据 (age为14的行数据可能有多条)

但是很遗憾，在二级索引中即使我们找到了age值为14某条记录，但里面仅仅只存储了age为14的这行所对应的主键值，但是我们用的是select * 的方式来查询的，怎么办呢？很简单，我们只需要根据已知的主键值，在去聚簇索引中查询一次就可以得到完整的行数据了。

在二级索引中根据条件查询到目标行数据的主键值之后，如果需要获取完整的行数据，就需要根据从二级索引中得到的主键值在去查询一次聚簇索引，这一过程我们称之为回表。一般来说，根据二级索引查询，但是又需要查询出完整的行数据，都需要进行回表操作。

联合索引

前面讲过的索引，都是以单列的值作为排序规则创建的B+树索引，我们同时也可以以多个列的大小作为排序规则，同时为多个列建立索引，这种索引我们称之为联合索引。比如我们可以同时为age列和name列创建索引，其比较规则如下:

先根据第一列age的值比较大小
age列值相同，再根据name列的值比较大小

根据以上的比较规则，我们可以创建如图所示的联合索引：

需要注意的是：

上面以age和name两列所创建的联合索引，因为也是非主键列索引，所以也属于二级索引
在联合索引B+树的叶节点中，存储的表中每行数据对应所有索引列值(age, name)以及对应的id主键列的值
在所有的目录页中的目录项主要包含所有索引列(age,name)的值和的目录项对应的页号
以上的联合索引是同时根据age和name两列创建的一颗B+树索引，但是如果我们要分别根据age列和name列创建B+树索引，那创建出的是两个不同的B+树索引

在以上联合索引中，我们可以模拟以下查询语句的查找过程:

select * from index_demo where age=15 and name='bd'

首先找到联合索引B+树的根节点，先比较age列的值，age列的值相同，在比较name列的值，确定满足条件的记录所对应的目录项记录所在的页号为50 ( 12 < 15 < 80)
找到目录项所在的目录页，在进行二分查找（ 12 < 15 < 50）,确定数据页页号为45
在页45中，先比较age的值，从而找到所有满足条件的记录，这里需要注意的是，在age值相同时，记录会按照name列的值排序，因此name相同的记录紧邻。
所以在满足age条件的记录集合中，在根据name条件进行判断，快速最终找到满足条件的记录集合
巧合的是，因为我们创建的表很简单，在这个联合索引叶节点中刚好包含了完整的行数据，所以不用在执行回表操作

MyISAM存储引擎

前面我们介绍的都是InnoDB存储引擎中的索引方案，这也是我们实际开发中最常用的。但是鉴于面试中可能会提及，所以我们在给大家简单介绍下MyISAM存储引擎的索引方案。

我们知道在InnoDB存储引擎中的索引方案有一个特征，索引即数据，因为每张表都会自动创建聚簇索引。而MyISAM存储引擎的索引方案虽然也使用树形结构，但是却是将索引和数据分开存放的。

首先对于每一张表中所有的行数据，都会按照数据的插入顺序，一行一行的放入到一个文件中，这个文件称之为数据文件。在这个文件中，每一行数据都有一个唯一的行号，如图所示：

MyISAM会将表的索引存储到另外一个索引文件中，和InnoDB类似MyISAM也会为自动根据每张表的主键，自动创建索引，只不过索引的叶子节点中存储的不是完整的用户记录，而是该记录在数据文件中所对应的行号 + 主键值。所以在该索引中查询的时候，需要先查询主键索引，然后做一次类似于InnoDB中的回表操作，才能查询到完整的行数据，这就意味着如果做一个类比的话，在MyISAM存储引擎中所创建的所有的索引，都相当于InnoDB中的二级索引

如果有需要的话，我们完全可以对其他列分别建立索引或者联合索引，原理和InnoDB中的B+树索引差不多，唯一的区别是叶子节点处，存储的是索引列的值 + 数据文件行号。

当然，在理解了以上MyISAM存储引擎特征之后，我们需要再声明一点，在MyISAM存储引擎中创建的索引的叶子节点中存储的并不一定是对应的数据文件中的行号，这还要看数据文件中存储行数据使用的是什么格式：

如果是定长格式，即每一行占用的存储空间大小相同，则索引叶节点存储行号
否则如果不是定长格式，即不同的行数据占用的存储空间大小不同，则索引叶子节点存储每行数据对应的地址偏移量

除了索引实现的区别之外，它们还有如下主要的区别:

InnoDB 支持事务处理，而MyISAM不支持事务，因此MyISAM更适合于只读数据或者在数据完整性要求不高的场合使用。
InnoDB支持行级锁，但是MyISAM只支持表级锁
InnoDB提供了崩溃恢复机制，但是MyISAM缺少崩溃恢复机制

索引的使用(InnoDB)

在学习了InnoDB的索引原理之后，可以得到一个结论，如果在查询的时候，可以用到索引，可能会显著的提高查询效率。接下来，就有一个问题了，是不是索引创建的越多越好呢？当然不是

从空间上来说，我们每创建一个索引，就会生成一颗新的B+树，一颗B+树中可能包含很多节点，而每一个节点都对应一个默认16KB大小的数据页，所以一颗B+树会占用大量的存储空间。

从时间上来说

对表中的数据做任何增删改的操作，都需要在聚簇索引，以及对应的所有二级索引做出相应的修改，会一定程度影响增删改的效率
不论是B+树中的叶子节点，还是非叶子节点中的记录都是按照索引列值来排序的，而增删改操作往往会破会这个顺序，所以存储引擎为了维护这个顺序，需要额外做一些记录的移位，页面的分裂等操作，即需要付出一定的代价

所以，我们为一张表创建的索引越多，占用的存储空间就越大，增删改的效率就越低。为了能创建又少又好用的索引，接下来我们需要研究一下索引的应用场景。

为了更好举例说明，我们创建如下的一张表：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `point` bigint DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  index `idx_name_birthday_city` (`name`,`birthday`,`city`) # 给name,birthday,city创建联合索引
);

在这张user表中有两个索引，一个聚簇索引一个联合索引，所以我们会有两颗B+树，其中联合索引的如图:

再次强调，大家一定要注意，在此联合索引的B+树中，不管是目录页还是普通数据页中，记录的排序规则：

先按照姓名排序(字符串字典序)
姓名相同在根据生日日期大小排序
姓名和出生日期都相同，再根据城市排序(字符串字典序)

查询中的使用

接下来，我们给大家分析一下索引通常在查询中使用的一些典型场景。

全值匹配

如果我们的搜索条件中的列和索引列一致，且指定每一列的精确值，这种查询就称为全值匹配，比如下面的查询：

select * from user where name='aq' and birthday='2002-10-10' and city='北京'

select * from user where name='aq' and city='北京'and birthday='2002-10-10'

该SQL语句的查询条件中包含了idx_name_birthday_city索引中的所有索引列，我们可以模拟以上查询过程：

在联合索引的B+树上，从根节点出发先根据name条件定位到第一个满足name=’aq’条件的第一条记录
因为idx_name_birthday_city首先根据name列的值排序，所以name值相同的记录紧邻，所以从满足name=‘aq’的第一条记录开始，依次向后遍历，直到找到所有name=‘aq’的记录
然后，因为对于name=‘aq’的所有记录，在联合索引中会根据birthday排序，birthday值相同的记录同样会紧邻，所以接下来可以快速在满足name=‘aq’的集合中，得到满足同时满足birthday=’2002-10-10’的记录
满足name=‘aq’且birthday=’2002-10-10条件的记录，在联合索引中会根据city列的值排序，所以city值相同的记录也会紧邻，所以在其中，可以快速定位到所有city=’北京’的记录

有同学可能会有疑问，对于全值匹配而言，条件的书写顺序，必须和索引列中定义的顺序相同吗？其实不用，因为MySQL优化器会分析这些搜索条件，并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件

细心的同学一定发现了，其实到这里我们的查询还没有结束，因为我们只是在idx_name_birthday_city中查询到了满足条件的记录，但是这些记录中并未包含point列的值，而我们使用的查询语法为select *，所以接下来，我们还需要回表，根据idx_name_birthday_city中查询到的主键id值，去聚簇索引中查询出所有满足条件的完整行数据。

匹配左边的列(最左前缀)

其实在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含联合索引中左边连续列就行，比方说下边的查询语句：

SELECT * FROM user WHERE name = 'aq';

或者包含多个左边的列也行：

SELECT * FROM user WHERE name = 'aq' AND birthday = '2002-10-10';

SELECT * FROM user WHERE name = 'aq' AND birthday = '2002-10-10' and city='北京';

有同学可能马上就要问了，那为什么搜索条件中必须出现左边的列才可以使用到这个 B+ 树索引呢？比如下边的语句就用不到这个B+树索引么？

SELECT * FROM user WHERE birthday = '2002-10-10';
SELECT * FROM user WHERE birthday = '2002-10-10' and city = 北京;
SELECT * FROM user WHERE  city = 北京;

是的，的确用不到。因为B+树的数据页和目录页中的记录是先按照 name 列的值排序的，在name 列的值相同的情况下才使用 birthday列进行排序，也就是说 name 列的值不同的记录中 birthday 的值可能是无序的，这就意味着birthday列值相同的记录并不会紧邻，且无法使用二分查找

我们需要特别注意的：如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列

比如以下查询条件，就能用到name列的索引

SELECT * FROM user WHERE name = 'zs' AND city = '武汉'

关于这个例子我们需要注意的是：

根据name列的查询条件，是可以用到idx_name_birthday_city索引的，因为无论数据页还是目录页中的记录都根据name列有序
但是，city列的查询条件无法使用联合索引中的city索引列，因为在只有在birthday相同的情况下，idx_name_birthday_city索引的city列才是有序的

匹配列前缀(针对字符串)

其实，为某个列建立索引的意思就是在对应的B+ 树的记录中使用该列的值进行排序。比如说联合索引idx_name_birthday_city会先用name列的值排序，所以这个联合索引对应的B+树记录中的 name 列排列如下：

aq
aq
aqg
axl
bbb
ccc
zzz
...

name的值是字符串类型，所以其实name列是根据字符串比较规则排序，一般来说，我们认为字符串根据字典序排序，其排序特征为：

先按照字符串的第一个字符进行排序。
如果第一个字符相同再按照第二个字符进行排序。
如果第二个字符相同再按照第三个字符进行排序，依此类推。

也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的。比如说我们想查询名字以 ‘a’ 开头的名字，那就可以这么写查询语句：

 SELECT * FROM user WHERE name LIKE 'a%';

需要注意的是，如果只给出后缀或者中间的某个字符串，比如这样：

SELECT * FROM user WHERE name LIKE '%a%';

SELECT * FROM user WHERE name LIKE '%a';

MySQL 就无法快速定位记录位置了，因为字符串中间或后缀有 ‘a’ 的字符串并没有排好序，所以只能全表扫描了。

但是有时候我们有一些匹配某些字符串后缀的需求，比方说某个表有一个 url 列，该列中存储了许多url：

 www.baidu.com 
 www.google.com
 www.github.com
 ...
 www.stackoverflow.com
 www.redis.cn
 www.redis.io

假设已经对该 url 列创建了索引，如果我们想查询以 com 为后缀的网址的话可以这样写查询条件，

WHERE url LIKE '%com'

但是这样的话无法使用该 url 列的索引。怎么办呢？其实我们可以把表中的数据全部逆序存储一下

com.baidu.www 
com.google.www
com.github.www
 ...
com.stackoverflow.www
cn.redis.www
io.redis.www

WHERE url LIKE 'com%'

这样一来，就可以把后缀查询改写成前缀查询，使用到索引了。

范围匹配

继续看idx_name_birthday_city索引，如果只看name一列的话，所有记录都是按照name索引列的值从小到大的顺序排好序的，这就极大的方便了我们，在name值的某个范围内查找目标记录，比如：

SELECT * FROM user WHERE name > 'aq' AND name < 'ww';

在idx_name_birthday_city B+树索引中，其查找过程大致如下:

找到 name 值为 aq 的最后一条记录A。
找到 name 值为 ww 的第一条记录B。
取出（A, B）范围的所有记录，并根据记录中的主键值回表

这里需要注意的是，由于所有记录都是由链表连起来的（页内的记录有序，数据页之间用双链表连接且有序），所以他们之间的记录都可以获取到

但是，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引中对应的索引列，比如下面的SQL语句

SELECT * FROM user WHERE name > 'a' AND name < 'ww' AND birthday > '2000-01-01';

以上的查询可以分成两个部分：

通过条件 name > ‘a’ AND name < ‘ww’ 来对 name 进行范围，查找的结果可能有多条 name值不同的记录
对这些 name 值不同的记录继续通过 birthday > ‘1980-01-01’ 条件继续过滤

这样一来，对于联合索引 idx_name_birthday_city来说，只能用到 name 列的索引部分，而用不到 birthday 索引列的部分，原因是：

只有 name 值相同的情况下才能用 birthday 列的值进行排序，
而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的
所以在搜索条件中继续以 birthday 列进行查找时是用不到这个B+树birthday索引列。

精确匹配 + 范围匹配

对于同一个联合索引来说，虽然我们上面说，对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比如如下的SQL语句

SELECT * FROM user WHERE name = 'aq' AND birthday > '2000-01-01' AND birthday
< '2000-12-31' AND city = '北京';

这个查询可以分成三个部分来看：

name = ‘aq’ ，对 name 列进行精确查找，当然可以使用联合索引了
birthday > ‘2000-01-01’ AND birthday< ‘2000-12-31’ 由于 name 列是精确查找，所以满足 name = ‘aq’ 条件的记录会再按照 birthday 的值进行排序。所以此时对 birthday 列进行范围查找是可以用到联合索引birthday索引列。
city = ‘北京’，通过 birthday 的范围查找的记录的 birthday 的值可能不同，所以这个条件无法再利用 B+ 树索引了，只能遍历上一步查询得到的记录。

同理，下边的查询也是可能用到 idx_name_birthday_city联合索引的：

SELECT * FROM user WHERE name = 'aq' AND birthday = '2002-10-10' AND city like '北%';

回表的代价

下面我们以idx_name_birthday_city 这颗B+树索引为例，研究回表的代价，假设有如下SQL语句：

SELECT * FROM user WHERE name > 'a' AND name < 'ww'

该查询的执行大致可以分为两步：

从索引 idx_name_birthday_city 对应的 B+ 树中取出 name 值在 a～ ww之间的记录
因为使用select * 所以还需要根据上一步获得的每一条记录的主键值回表，查询出完整的行数据

对于InnoDB存储引擎来说，索引中的页数据都存放在磁盘的文件中，同时，B+树的页节点会使用双向链表连接起来，页与页之间可以不必紧邻。但是在实际实现中，InnoDB还是会尽可能的让一颗B+树的叶子节点物理紧邻。而叶子节点本身按照name列排序，这也就是说，在读取name值在(‘a’, ‘ww’)范围内的记录时，我们所读取的页面在磁盘上也是尽可能紧邻的，这意味着在扫描二级索引idx_name_birthday_city (‘a’, ‘ww’)时，执行的IO大部分都是顺序IO，相对来说IO代价会小一些。

但是，在(‘a’, ‘ww’)内的主键值，却是毫无规律可言的，因此执行回表操作时，但是由于主键值不连续，因此其页面所在的位置可能页毫无规律，因此会造成大量的随机IO。

一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的相对较快，而步骤2相对较慢，所以这个使用索引 idx_name_birthday_city的查询有这么两个特点：

会使用到两个B+ 树索引，一个二级索引，一个聚簇索引。
访问二级索引使用顺序I/O ，访问聚簇索引使用随机I/O

需要回表的记录越多，使用二级索引的性能就越低，甚至当回表操作很多的时候，查询优化器宁愿让某些查询使用全表扫描也不使用二级索引。比方说 name 值在（a～ ww）之间的用户记录数量占全部记录数量90%以上，那么如果使用 idx_name_birthday_city 索引的话，有90%多的 id 值需要回表，还不如直接去扫描聚簇索引（也就是全表扫描)。

那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这是由查询优化器决定的，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致如此。

一般情况下，限制查询获取较少的记录，会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高，比如说上边的查询可以改写成这样：

SELECT * FROM user WHERE name > 'a' AND name < 'ww'  LIMIT 10;

添加了 LIMIT 10 的查询更容易让优化器采用二级索引 + 回表的方式进行查询。

覆盖索引

为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列，比如如下SQL：

SELECT name, birthday, city FROM user WHERE name > 'a' AND name < 'ww'

因为我们只查询 name , birthday , city这三个索引列的值，而它们已经被包含在idx_name_birthday_city索引中，所以就不必到聚簇索引回表，就减少了回表操作带来的性能损耗。

我们把这种只需要用到二级索引的查询方式称为索引覆盖。当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是我们很不鼓励用 * 号作为查询列表，最好把我们需要查询的列依次标明。

在排序中的使用

我们在写查询语句的时候经常需要对查询出来的记录通过 ORDER BY 子句按照某种规则进行排序。一般情况下，我们只能把记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序等等排序算法在内存中对这些记录进行排序，有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。

在 MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort ），跟文件这个词儿一沾边儿，就会使得这些排序操作非常慢了。

但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，比如下边这个简单的查询语句：

SELECT * FROM user ORDER BY name, birthday, city LIMIT 10;

这个查询的结果集需要先按照 name 值排序，如果记录的 name 值相同，则需要按照 birthday 来排序，如果 birthday 的值相同，则需要按照 city排序。这不是和我们的 idx_name_birthday_city索引的的排序规则相同吗，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了，所以大大提升了排序效率

需要说明的是，对于排序而言，之前讨论对于采用全表扫描还是二级索引 + 回表的方式进行查询的讨论在排序中仍然适用。由于上面的查询列表是 * ，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（ filesort ）低，所以优化器可能会倾向于使用全表扫描的方式执行查询。如果我们加了 LIMIT 子句

联合索引注意事项

如果我们想在排序的时候使用到联合索引，我们就一定要注意ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 ORDER BY city, birthday, name 的顺序，那也是用不了联合索引，这种颠倒顺序就不能使用索引的原因与B+树的排序顺序有关系，这就不赘述了。

同理， ORDER BY name 、 ORDER BY name, birthday 这种匹配索引左边的列的形式可以使用部分的 B+ 树索引。当查询条件中使用了对联合索引左边列的精确值匹配，也可以使用后边的列进行排序，比如这样：name, birthday, city

SELECT * FROM user WHERE name = 'aq' ORDER BY birthday, city LIMIT 10;

这个查询能使用联合索引进行排序是因为name 列的值相同的记录是按照 birthday , city排序的。

无法使用索引的情况

在以下的几种情况下，在排序时是无法使用到索引的

ASC DESC混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是 ASC 规则排序，要么都是 DESC 规则排序。

为什么会有这样的规定呢？这个还得回头想想这个 idx_name_birthday_city 联合索引中记录的排序规则：

先按照记录的 name 列的值进行升序排列。
如果记录的 name 列的值相同，再按照 birthday 列的值进行升序排列。
如果记录的name和birthday 列的值相同，再按照 city列的值进行升序排列。

如果查询中的各个排序列的排序顺序是一致的，比方说下边两这种情况：

// 情况1
SELECT * FROM user ORDER BY name, birthday LIMIT 10
// 情况2
SELECT * FROM user ORDER BY name DESC, birthday DESC LIMIT 10

情况1直接从索引的最左边开始往右读10行记录就可以了，情况2直接从索引的最右边开始往左读10行记录就可以了。

但是如果我们查询的需求是先按照 name 列进行升序排列，再按照 birthday 列进行降序排列的话，比如如下查询语句：

SELECT * FROM user ORDER BY name, birthday DESC LIMIT 10

这样如果使用索引排序的话过程就是这样的：

先从索引的最左边确定 name 列最小的值，然后找到 name 列等于该值的所有记录，然后从 name 列等于该值的最右边的那条记录开始往左找10条记录。
如果 name 列等于最小的值的记录不足10条，再从左继续往右找 name 值第二小的记录，重复上边那个过程，直到找到10条记录为止。

这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，还不如直接文件排序来的快！

排序列包含非同一个索引的列

有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比如如下SQL语句：

SELECT * FROM user ORDER BY name, point LIMIT 10

name 和 point 并不属于一个联合索引中的列，所以无法使用索引进行排序。

排序列使用了复杂的表达式

要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，比如如下SQL：

SELECT * FROM user ORDER BY UPPER(name) LIMIT 10;

这里的UPPER是MySQL中的函数，使用了 UPPER 函数修饰过的列就不是单独的列啦，这样就无法使用索引进行排序啦。

在分组中的使用

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下边这个分组查询：

 SELECT name, birthday, city, COUNT(*) FROM user GROUP BY name, birthday, city

这个查询语句相当于做了3次分组操作：

先把记录按照 name 值进行分组，所有 name 值相同的记录划分为一组。
将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组，将 birthday 值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
再将上一步中产生的小分组按照 city的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

然后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现。

而有了索引的话，如果恰巧这个分组顺序又和我们的 B+树中的索引列的顺序是一致的，而我们的 B+ 树索引又是按照索引列排好序的，所以可以直接使用 B+ 树索引进行分组。和使用 B+ 树索引进行排序其实是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组。

索引的语法

在MySQL中我们可以定义单列索引，也可以定义包含多列的索引(即联合索引)。

如果在创建表的时候定义索引，可以基于如下语法：

CREATE TABLE 表名 (
    ...
	[UNIQUE] INDEX 索引名称 (column1, column2, ...)
    ...
)
# 索引名称我们习惯上通常定位idx_索引列名1_索引列名2...
# 括号中的索引列可以是一列也可以是多列，如果是多列，列名之间以,分隔

如果在定义索引时添加了UNIQUE关键字，则该索引就变成了唯一索引，有如下效果：

如果索引列是单列的话，那么该单列的值在索引中不能重复（其实也就限定了在表中不能重复）
如果索引列是多列，即联合索引，那么各索引列的取值组合不能重复（其实也就限定了在表中这些列的取值组合不能重复）

我们也可以在创建表之后，通过修改表结构，给表添加索引

# 添加索引
ALTER TABLE 表名 ADD INDEX 索引名(列名1,列名2, ...);
# 添加唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名(列名1,列名2, ...);

我们也可以在创建索引后删除索引:

ALTER TABLE 表名 DROP INDEX 索引名;

如何挑选索引

对于单列索引的查询和使用都比较简单，大家只要理解了B+树索引即可掌握，所以我们主要是针对联合索引的在MySQL使用进行了介绍，接下来我们重点研究，在实际开发时索引的使用。

只为特殊目的创建索引

只为用于搜索、排序或分组的列创建索引，也就是说只为

出现在 WHERE 子句中的列
连接子句中的连接列 (连接的时候也可以使用到索引) join on
或者出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。

而出现在查询列表中的列就没必要建立索引了：

SELECT birthday, city FROM user WHERE name = 'aq';

像查询列表中的 birthday 、 city这两个列就不需要建立索引，我们只需要为出现在 WHERE 子句中的 name 列创建索引就可以了。

考虑列的基数

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 ，虽然有 9 条记录，但该列的基数却是 3 。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。

这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。假设某个列的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了. 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。

所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TINYINT 、 MEDIUMINT 、 INT 、 BIGINT 这么几种，它们占用的存储空间依次递增，当然，这几种数据类型能表示的整数范围当然也是依次递增

如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT。这是因为

数据类型越小，在查询时进行的比较操作越快（CPU层次）
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O 。

使用索引列前缀（针对字符串）

我们知道一个字符串其实是由若干个字符组成，如果我们在 MySQL 中使用 utf8 字符集去存储字符串的话，编码一个字符需要占用 1~3 个字节。假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。

在我们需要为这个字符串列建立索引时，那就意味着在对应的 B+ 树中有这么两个问题：

B+ 树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
如果 B+ 树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们前边说过索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了一个方案，只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。

这样，只在 B+ 树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题，何乐而不为，比方说我们在建表语句中只对 name 列的前5个字符进行索引可以这么写：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `point` bigint DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_birthday_city` (`name`(5),`birthday`,`city`)
);

name(5) 就表示在建立的 B+ 树索引中只保留记录的前5个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候

但是，需要注意的是如果使用了索引列前缀，比方说前边只把 name 列的前5个字符放到了二级索引中，下边这个排序可能就有点儿问题了：

SELECT * FROM user ORDER BY name LIMIT 10;

因为二级索引中不包含完整的 name 列信息，所以无法对前5个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用filesort文件排序。

索引列在表达式中单独出现

假设表中有一个整数列 int_column，我们为这个列建立了索引。下边的两个 WHERE 子句虽然语义是一致的，但是在效率上却有差别：

 1. WHERE int_column * 3 < 60
 2. WHERE int_column < 60 / 3

第1个 WHERE 子句中 int_column列并不是以单独列的形式出现的，而是以 int_column* 3 这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于 60 ，所以这种情况下是使用不到为 int_column列建立的 B+ 树索引的。
而第2个 WHERE 子句中 int_column列并是以单独列的形式出现的，这样的情况可以直接使用 B+ 树索引。

所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

主键插入顺序

我们知道，对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。

而记录又是存储在普通数据页中的，普通数据页中的记录又是按照记录主键值从小到大的顺序进行排序，所以

如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，
而如果我们插入的主键值忽大忽小的话，这就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间：

假设此时我们需要向数据页中插入主键值为7的一行数据，如图所示:

可这个数据页已经满了啊，再插进来咋办呢？InnoDB需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。

所以我们建议：为了让主键依次递增我们可以使用 AUTO_INCREMENT，或者使用其他方式(雪花算法)生成依次递增的主键。

消除冗余和重复索引

很多时候，我们会有意或者无意地创建同一个列的多个不同索引，比如如下的建表语句:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `point` bigint DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_birthday_city` (`name`(5),`birthday`,`city`),
  KEY 'idx_name'(`name`(5))
);

我们知道，通过 idx_name_birthday_city 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

另一种情况，我们可能会对某个列重复建立索引，比如如下建表语句：

CREATE TABLE repeat_demo (
 id INT PRIMARY KEY,
 age INT,
 UNIQUE uidx_id (id),
 INDEX idx_id (id)
);

我们看到， id 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

总结

这里我们最主要的是给大家介绍了InnoDB的B+树索引(聚簇索引，二级索引，联合索引)，在充分理解了B+树索引的基础上，我们有给大家讲解了索引在InnoDB查询，排序，分组中的使用，最后讲解了我们应该如何选择使用索引。

通过以上的讲解，大家应该能更加深刻的理解，索引尤其在加速查询过程中的作用。正因为如此，基于我们以上所讲的索引原理，一些常见的SQL优化规则我们就可以理解了，比如:

不建议查询时使用select *
所谓的最左前缀匹配原则
查询条件中，建议索引列单独出现(不出现在方法调用，以及运算表达式中)
排序中的多列顺序与联合索引列一致(如果有的话)
排序不建议ASC，DESC混用
建议保持主键列递增
在满足业务需要的前提下，尽量创建联合索引

…

除此之外，我们还应该知道，如果已经发现一个SQL语句执行较慢，如何在SQL语句层面优化？

首先，加快查询我们应该马上想到索引，先看看该SQL语句的查询条件所涉及的列是否创建索引，若没有则可以添加索引
但是，如果发现查询条件涉及到的列已经添加了索引，此时我们就需要判断，本次查询是否走了索引(这种情况大部分是没有走索引的)然后做相应调整，那么问题来了，我们怎么知道查询有没有走索引呢？可以通过MySQL提供的explain工具，其语法如下:

explain 查询语句

MySQL中的查询，都是经过查询优化器最终决定的查询计划执行的，而explain可以输查询语句执行时的执行计划，通过查看执行计划，就可以判断查询的执行过程，也可以看到查询过程中的索引使用情况，如图:

比如，针对某个查询，通过其possible_keys列的值，可以看到该查询可能使用到的索引，key列的值表示该查询实际使用的索引等等，这些信息，我们都可以从explain的执行计划输出中看到。