mysql index method_Mysql成神之路----索引在单表中的应用（干货，深入理解mysql中的索引到底是怎么回事）...

首先我们先来创建一个表：CREATE TABLE single_table (

id INT NOT NULL AUTO_INCREMENT,

key1 VARCHAR(100),

key2 INT,

key3 VARCHAR(100),

key_part1 VARCHAR(100),

key_part2 VARCHAR(100),

key_part3 VARCHAR(100),

common_field VARCHAR(100),

PRIMARY KEY (id),

KEY idx_key1 (key1),

UNIQUE KEY idx_key2 (key2),

KEY idx_key3 (key3),

KEY idx_key_part(key_part1, key_part2, key_part3)

) Engine=InnoDB CHARSET=utf8;复制代码

我们为这个single_table表建立了1个聚簇索引和4个二级索引，分别是：id列(主键)建立的是聚簇索引。

key1列建立的idx_key1是二级索引。

key2列建立的idx_key2(UNIQUE)是二级索引，而且该索引是唯一二级索引。

key3列建立的idx_key3是二级索引

key_part1、key_part2、key_part3列建立的idx_key_part是二级索引，这也是一个联合索引。

访问方法(access method)的概念

其实所谓访问方法也就是我们做选择的概念，正所谓条条大路通罗马，我们可以选择任何一条，只是每一条的风光和耗时会有所不同，但是最终我们都能到达罗马。Mysql查询亦是如此，我们的sql语法好比是一个进行选择的规则不同的语法对应不同的规则，但是sql执行器最终都会返回给我们想要的答案。

mysql的查询大致分为两种方式:使用全表扫描的形式进行查询：这个很好理解，就是把每一条记录全部都要扫一遍，在里面找我们需要的数据好比java中的for循环，我们需要把所有的数据都遍历完才能确定我们数据数据有多少，哪怕最后的几千、几万甚至更多数据中没有了我们需要的数据，我们也需要去遍历，因为我们并不知道那些数据中到底有没有我们需要的数据。

使用索引进行查询：索引，index，这个概念我们做开发的人来说是非常熟悉的光看名字我们就知道这个要比另一个快很多能够帮助我们精准的定位内容。索引相当于下标，我们可以根据索引精准的定位到某一条或者某一类数据这样大大的增加了我们的查询效率，但是会增加内存空间的使用，如果创建大量的索引会消耗大量的内存空间，还有增加维护成本，降低写操作的效率。虽然会增加查询的效率，但是会降低写操作的效率，我们好做好blance。

细化索引查询的几种情况:

下面我们来说说几种查询类型(访问方法)

const

我们通过主键或者唯一的二级索引来定位一条记录的时候是非常快的，就像下面这样：SELECT * FROM single_table WHERE id = 1438;MySQL会直接利用主键值在聚簇索引中定位对应的用户记录，就像这样：

针对主键和二级索引的等值查询复制代码

针对普通二级索引的等值查询复制代码

针对索引列的范围查询复制代码

直接扫描整个索引复制代码

对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树本来就是一个矮矮的大胖子，所以这样根据主键值定位一条记录的速度贼快。类似的，我们根据唯一二级索引列来定位一条记录的速度也是贼快的，比如下边这个查询：SELECT * FROM single_table WHERE key2 = 3841;这个查询的执行过程的示意图就是这样：

虽然唯一二级索引是分两步来完成查询的，但是实际查询效率还是很高的，这种效率基本是O(1)的常数级别的，我们称这种方法叫做：const。如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效(这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录)。

注意：对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，比如这样：SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 上边这个语句不可以使用const访问方法来执行(至于是什么访问方法我们下边马上说)。

ref

SELECT * FROM single_table WHERE key1 = 'abc';像这种使用普通二级索引与常数进行比较的情况，这种情况是有可能来进行全表扫描的，但是一般情况下是优先选择二级索引的方法进行查询，但是因为普通的二级索引不是唯一的，可能查询到一批的数据都是满足的，然后在根据二级索引对应的聚簇索引去查找完整的用户记录，当然这种情况下，结果集越小效率越高，回表的代价越小。这种方法我们称它为ref.下面是执行查询的示意图：

当然如果普通二级索引的结果集很少的话也就比const这种方法的效率低那么一丢丢，但是如果结果集很大回表的成本会很大，那么效率还是不是很友好的，不过有两种特殊的情况需要我们注意：二级索引列值为NULL的情况：不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法，比方说下边这几个查询：SELECT * FROM single_table WHERE key_part1 = 'god like';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';复制代码

我们可以自己用执行计划去看一下sql，到底是什么样的结果，但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样：SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null

这是一种特殊的查询方法 SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL; 这样就会触发这种查询方法，如果是SELECT * FROM single_table WHERE key1 = 'abc' OR key1 ='123'就不会触发

range

我们之前介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到(ref_or_null比较奇特，还计算了值为NULL的情况)，但是有时候我们面对的搜索条件更复杂，比如下边这个查询：SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

像key2 = 1438和key2 = 6328这种属于单点范围，key2 ∈ [38, 79]这种属于连续范围，

二级索引 + 回表 这种都能触发我们的索引+回表的方式进行查询。

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';像上面这种查询语句因为where条件 key_part2不是(key_part1, key_part2, key_part3)联合索引的最左索引列,所以是不能使用ref或者range索引方法的,但是又因为SELECT key_part1, key_part2, key_part3 查询的内容信息是存在联合索引中的，遍历索引列就行拿到我们想要的结果，我们称这种方法叫index.ELECT key_part1, key_part2, key_part3 ,key1 from single_table WHERE key_part2 = 'abc';如果查询改成以上语句那么就成改成ALL方法查询。

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为：all。

总结

重温 二级索引 + 回表

一般情况下只能利用单个二级索引执行查询，比方说下边的这个查询：SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;查询优化器会意识到这个查询中的两个搜索条件：key1 = 'abc'

key2 > 1000

mysql的优化器，一般情况下，会自动优化，选择一个效率比较高的方法，去进行查询并返回结果。

这里需要特别提醒大家的一点是，因为二级索引的节点中的记录只包含索引列和主键，所以在步骤1中使用idx_key1索引进行查询时只会用到与key1列有关的搜索条件，其余条件，比如key2 > 1000这个条件在步骤1中是用不到的，只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。

明确range访问方法使用的范围区间

其实对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、=、<=、BETWEEN、!=(不等于也可以写成<>)或者LIKE操作符连接起来，就可以产生一个所谓的区间。小贴士： LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引，具体原因我们在前边的章节中唠叨过了，这里就不赘述了。 IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的： SELECT * FROM single_table WHERE key2 IN (1438, 6328); SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

索引合并

我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但不是还有特殊情况么，在这些特殊情况下也可能在一个查询中使用到多个二级索引，设计MySQL的大叔把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

Intersection合并

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

假设这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：从idx_key1二级索引对应的B+树中取出key1 = 'a'的相关记录。

从idx_key3二级索引对应的B+树中取出key3 = 'b'的相关记录。

二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。

按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

情况二：主键列可以是范围匹配：比方说下边这个查询可能用到主键和idx_key1进行Intersection索引合并的操作：SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

为什么二级索引必须是等值不可以是范围是因为等值的内容是连续的，这个两个索引条件取交集的时候非常方便，反之很困难。 试想一下，1，2，3和2，3 取交集和1，4，6，和2，4，6取交集，感受一下这个过程。

Union合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比方说这样：SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

情况二：主键列可以是范围匹配

情况三：使用Intersection索引合并的搜索条件

当然，查询条件符合了这些情况也不一定就会采用Union索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并

nion索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a'从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > 'z'从idx_key3索引中获取的二级索引记录的主键值也不是排好序的，但是key1 < 'a'和key3 > 'z'这两个条件又特别让我们动心，所以我们可以这样：先根据key1 < 'a'条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序

再根据key3 > 'z'条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序

因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

我们把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

适当的使用联合索引替代Intersection索引合并还有就是，我们要学会使用EXPLAIN,学习本文之后相信大家分析执行计划这件事度对于我们来说是so easy的事情啦，我们去把我们项目中的sql分析一下，然后优化一下对于我们熟悉这些更加有帮助