1.概述MySQL Server有一个称为 查询优化器 的模块一条查询语句进行语法解析之后就会被交给查询优化器来进行优化优化的结果就是生成一个所谓的 执行计划 这个执行计划表明了应该使用哪些索引进行查询表之间的连接顺序是啥样的最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询并将查询结果返回给用户。为了故事的顺利发展我们先得有个表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) ) EngineInnoDB CHARSETutf8;然后我们需要为这个表插入10000行记录除id列外其余的列都插入随机值就好了。2.访问方法access method的概念对于单个表的查询来说设计MySQL的大叔把查询的执行方式大致分为下边两种(1). 使用全表扫描进行查询把表的每一行记录都扫一遍嘛把符合搜索条件的记录加入到结果集就完了。(2). 使用索引进行查询如果查询语句中的搜索条件可以使用到某个索引那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式五花八门又可以细分为许多种类a. 针对主键或唯一二级索引的等值查询b. 针对普通二级索引的等值查询c. 针对索引列的范围查询d. 直接扫描整个索引设计MySQL的大叔把MySQL执行查询语句的方式称之为 访问方法 或者 访问类型 。2.1.const–直接基于索引得到唯一项有的时候我们可以通过主键列来定位一条记录比方说这个查询SELECT * FROM single_table WHERE id 1438;类似的我们根据唯一二级索引列来定位一条记录的速度也是贼快的比如下边这个查询SELECT * FROM single_table WHERE key2 3841;可以看到这个查询的执行分两步第一步先从idx_key2对应的B树索引中根据key2列与常数的等值比较条件定位到一条二级索引记录然后再根据该记录的id值到聚簇索引中获取到完整的用户记录。设计MySQL的大叔认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是像坐火箭一样快的所以他们把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为const意思是常数级别的代价是可以忽略不计的。如果主键或者唯一二级索引是由多个列构成的话索引中的每一个列都需要与常数进行等值比较这个const访问方法才有效这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录。对于唯一二级索引来说查询该列为NULL值的情况比较特殊比如这样SELECT * FROM single_table WHERE key2 IS NULL;因为唯一二级索引列并不限制NULL值的数量所以上述语句可能访问到多条记录也就是说 上边这个语句不可以使用const访问方法来执行。2.2.ref–直接基于索引得到多个项有时候我们对某个普通的二级索引列与常数进行等值比较比如这样SELECT * FROM single_table WHERE key1 abc;由于普通二级索引并不限制索引列值的唯一性所以可能找到多条对应的记录也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少则回表的代价还是比较低的所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。设计MySQL的大叔就把这种搜索条件为二级索引列与常数等值比较采用二级索引来执行查询的访问方法称为ref。需要注意下边两种情况(1). 二级索引列值为NULL的情况不论是普通的二级索引还是唯一二级索引它们的索引列对包含NULL值的数量并不限制所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法而不是const的访问方法。(2). 对于某个包含多个索引列的二级索引来说只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法比方说下边这几个查询a.SELECT * FROM single_table WHERE key_part1 god like;b.SELECT * FROM single_table WHERE key_part1 god like AND key_part2 legendary;c.SELECT * FROM single_table WHERE key_part1 god like AND key_part2 legendary AND key_part3 penta kill;但是如果最左边的连续索引列并不全部是等值比较的话它的访问方法就不能称为ref了比方说这样SELECT * FROM single_table WHERE key_part1 god like AND key_part2 legendary;。因为这种方法使用索引下只能先利用key_part1定位到索引中所有项然后key_part2部分无法从这些项中立即可得。必须将这些项全部读入内存内存分析后才能刷选出最终项集合。2.3.ref_or_null–特殊的ref有时候我们不仅想找出某个二级索引列的值等于某个常数的记录还想把该列的值为NULL的记录也找出来就像下边这个查询SELECT * FROM single_demo WHERE key1 abc OR key1 IS NULL;。当使用二级索引而不是全表扫描的方式执行该查询时这种类型的查询使用的访问方法就称为ref_or_null。2.4.range我们之前介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到ref_or_null比较奇特还计算了值为NULL的情况但是有时候我们面对的搜索条件更复杂比如下边这个查询SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 38 AND key2 79);我们当然还可以使用全表扫描的方式来执行这个查询不过也可以使用 二级索引 回表 的方式执行如果采用 二级索引 回表 的方式来执行的话那么此时的搜索条件就不只是要求索引列与常数的等值匹配了而是索引列需要匹配某个或某些范围的值在本查询中key2列的值只要匹配下列3个范围中的任何一个就算是匹配成功了(1).key2的值是1438(2).key2的值是6328(3).key2的值在38和79之间。设计MySQL的大叔把这种利用索引进行范围匹配的访问方法称之为range。2.5.index–遍历二级索引看下边这个查询SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 abc;由于key_part2并不是联合索引idx_key_part最左索引列所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件(1). 它的查询列表只有3个列key_part1,key_part2,key_part3而索引idx_key_part又包含这三个列。(2). 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 abc这个条件是否成立把匹配成功的二级索引记录的key_part1,key_part2,key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列而二级索引记录只需要存放索引列和主键而且这个过程也不用进行回表操作所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多设计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之为index。2.6.all最直接的查询执行方式就是我们已经提了无数遍的全表扫描对于InnoDB表来说也就是直接扫描聚簇索引设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为all。3.注意事项3.1.重温 二级索引 回表一般情况下只能利用单个二级索引执行查询比方说下边的这个查询SELECT * FROM single_table WHERE key1 abc AND key2 1000;查询优化器会识别到这个查询中的两个搜索条件a.key1 abcb.key2 1000优化器一般会根据single_table表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。一般来说等值查找比范围查找需要扫描的行数更少也就是ref的访问方法一般比range好但这也不总是一定的也可能采用ref访问方法的那个索引列的值为特定值的行数特别多所以这里假设优化器决定使用idx_key1索引进行查询那么整个查询过程可以分为两个步骤(1). 使用二级索引定位记录的阶段也就是根据条件key1 abc从idx_key1索引代表的B树中找到对应的二级索引记录。(2). 回表阶段也就是根据上一步骤中找到的记录的主键值进行 回表 操作也就是到聚簇索引中找到对应的完整的用户记录再根据条件key2 1000到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。这里需要特别提醒大家的一点是因为二级索引的节点中的记录只包含索引列和主键所以在步骤1中使用idx_key1索引进行查询时只会用到与key1列有关的搜索条件其余条件比如key2 1000这个条件在步骤1中是用不到的只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。3.2.明确range访问方法使用的范围区间其实对于B树索引来说只要索引列和常数使用、、IN、NOT IN、IS NULL、IS NOT NULL、、、、、BETWEEN、!不等于也可以写成或者LIKE操作符连接起来就可以产生一个所谓的 区间 。LIKE操作符比较特殊只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。一个查询的WHERE子句可能有很多个小的搜索条件这些搜索条件需要使用AND或者OR操作符连接起来(1).cond1 AND cond2只有当cond1和cond2都为TRUE时整个表达式才为TRUE。(2).cond1 OR cond2只要cond1或者cond2中有一个为TRUE整个表达式就为TRUE。当我们想使用range访问方法来执行一个查询语句时重点就是找出该查询可用的索引以及这些索引对应的范围区间。3.2.1.所有搜索条件都可以使用某个索引的情况有时候每个搜索条件都可以使用到某个索引比如下边这个查询语句SELECT * FROM single_table WHERE key2 100 AND key2 200;key2 100和key2 200交集当然就是key2 200了也就是说上边这个查询使用idx_key2的范围区间就是(200, ∞)。我们再看一下使用OR将多个搜索条件连接在一起的情况SELECT * FROM single_table WHERE key2 100 OR key2 200;也就是说上边这个查询使用idx_key2的范围区间就是(100 ∞)。3.2.2.有的搜索条件无法使用索引的情况比如下边这个查询SELECT * FROM single_table WHERE key2 100 AND common_field abc;请注意这个查询语句中能利用的索引只有idx_key2一个而idx_key2这个二级索引的记录中又不包含common_field这个字段所以在使用二级索引idx_key2定位记录的阶段用不到common_field abc这个条件这个条件是在回表获取了完整的用户记录后才使用的而 范围区间 是为了到索引中取记录中提出的概念所以在确定 范围区间 的时候不需要考虑common_field abc这个条件我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。我们把上边的查询中用不到idx_key2的搜索条件替换后就是这样SELECT * FROM single_table WHERE key2 100 AND TRUE;化简之后就是这样SELECT * FROM single_table WHERE key2 100;再来看一下使用OR的情况SELECT * FROM single_table WHERE key2 100 OR common_field abc;同理我们把使用不到idx_key2索引的搜索条件替换为TRUESELECT * FROM single_table WHERE key2 100 OR TRUE;接着化简SELECT * FROM single_table WHERE TRUE;这也就说说明如果我们强制使用idx_key2执行查询的话对应的范围区间就是(-∞, ∞)也就是需要将全部二级索引的记录进行回表这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。3.3.3.复杂搜索条件下找出范围匹配的区间有的查询的搜索条件可能特别复杂光是找出范围匹配的各个区间就挺烦的比方说下边这个SELECT * FROM single_table WHERE (key1 xyz AND key2 748 ) OR (key1 abc AND key1 lmn) OR (key1 LIKE %suf AND key1 zzz AND (key2 8000 OR common_field abc)) ;(1). 首先查看WHERE子句中的搜索条件都涉及到了哪些列哪些列可能使用到索引。这个查询的搜索条件涉及到了key1、key2、common_field这3个列然后key1列有普通的二级索引idx_key1key2列有唯一二级索引idx_key2。(2). 对于那些可能用到的索引分析它们的范围区间。(2.1). 假设我们使用idx_key1执行查询我们需要把那些用不到该索引的搜索条件暂时移除掉移除方法也简单直接把它们替换为TRUE就好了。上边的查询中除了有关key2和common_field列不能使用到idx_key1索引外key1LIKE %suf也使用不到索引所以把这些搜索条件替换为TRUE之后的样子就是这样(key1 xyz AND TRUE ) OR (key1 abc AND key1 lmn) OR (TRUE AND key1 zzz AND (TRUE OR TRUE))化简一下上边的搜索条件就是下边这样(key1 xyz) OR (key1 abc AND key1 lmn) OR (key1 zzz)替换掉永远为TRUE或FALSE的条件因为符合key1 abc AND key1 lmn永远为FALSE所以上边的搜索条件可以被写成这样(key1 xyz) OR (key1 zzz)继续化简区间key1 xyz和key1 zzz之间使用OR操作符连接起来的意味着要取并集所以最终的结果化简的到的区间就是key1 xyz。也就是说上边那个有一坨搜索条件的查询语句如果使用idx_key1索引执行查询的话需要把满足key1 xyz的二级索引记录都取出来然后拿着这些记录的id再进行回表得到完整的用户记录之后再使用其他的搜索条件进行过滤。(2.2).假设我们使用idx_key2执行查询我们需要把那些用不到该索引的搜索条件暂时使用TRUE条件替换掉其中有关key1和common_field的搜索条件都需要被替换掉替换结果就是(TRUE AND key2 748 ) OR (TRUE AND TRUE) OR (TRUE AND TRUE AND (key2 8000 OR TRUE))也就是说化简之后的搜索条件成这样了key2 748 OR TRUE这个化简之后的结果就更简单了TRUE这个结果也就意味着如果我们要使用idx_key2索引执行查询语句的话需要扫描idx_key2二级索引的所有记录然后再回表这不是得不偿失么所以这种情况下不会使用idx_key2索引的。3.3.索引合并我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引但不是还有特殊情况么在这些特殊情况下也可能在一个查询中使用到多个二级索引设计MySQL的大叔把这种使用到多个索引来完成一次查询的执行方法称之为index merge具体的索引合并算法有下边三种。3.3.1.Intersection合并Intersection翻译过来的意思是 交集 。这里是说某个查询可以使用多个二级索引将从多个二级索引中查询到的结果取交集比方说下边这个查询SELECT * FROM single_table WHERE key1 a AND key3 b;假设这个查询使用Intersection合并的方式执行的话那这个过程就是这样的(1). 从idx_key1二级索引对应的B树中取出key1 a的相关记录。(2). 从idx_key3二级索引对应的B树中取出key3 b的相关记录。(3). 二级索引的记录都是由 索引列 主键 构成的所以我们可以计算出这两个结果集中id值的交集。(4). 按照上一步生成的id值列表进行回表操作也就是从聚簇索引中把指定id值的完整用户记录取出来返回给用户。为啥不直接使用idx_key1或者idx_key3只根据某个搜索条件去读取一个二级索引然后回表后再过滤另外一个搜索条件呢这里要分析一下两种查询执行方式之间需要的成本代价。只读取一个二级索引的成本(1). 按照某个搜索条件读取一个二级索引(2). 根据从该二级索引得到的主键值进行回表操作然后再过滤其他的搜索条件读取多个二级索引之后取交集成本(1). 按照不同的搜索条件分别读取不同的二级索引(2). 将从多个二级索引得到的主键值取交集然后进行回表操作虽然读取多个二级索引比读取一个二级索引消耗性能但是读取二级索引的操作是 顺序I/O而回表操作是 随机I/O所以如果只读取一个二级索引时需要回表的记录数特别多而读取多个二级索引之后取交集的记录数非常少当节省的因为 回表 而造成的性能损耗比访问多个二级索引带来的性能损耗更高时读取多个二级索引后取交集比只读取一个二级索引的成本更低。MySQL在某些特定的情况下才可能会使用到Intersection索引合并(1). 二级索引列是等值匹配的情况对于联合索引来说在联合索引中的每个列都必须等值匹配不能出现只出现匹配部分列的情况。比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Intersection索引合并的操作SELECT * FROM single_table WHERE key1 a AND key_part1 a AND key_part2 b AND key_part3 c;而下边这两个查询就不能进行Intersection索引合并SELECT * FROM single_table WHERE key1 a AND key_part1 a AND key_part2 b AND key_part3 c;SELECT * FROM single_table WHERE key1 a AND key_part1 a;第一个查询是因为对key1进行了范围匹配第二个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中所以这两个查询不能进行Intersection索引合并。对于InnoDB的二级索引来说记录先是按照索引列进行排序如果该二级索引是一个联合索引那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由 索引列 主键 构成的二级索引列的值相同的记录可能会有好多条这些索引列的值相同的记录又是按照 主键 的值进行排序的。所以重点来了之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。此时从多个二级索引结果集执行Intersection的复杂度可控制在O(n)。(2). 主键列可以是范围匹配比方说下边这个查询可能用到主键和idx_key1进行Intersection索引合并的操作SELECT * FROM single_table WHERE id 100 AND key1 a;二级索引的记录中都带有主键值的涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录。当然上边说的 情况一 和 情况二 只是发生Intersection索引合并的必要条件不是充分条件。也就是说即使情况一、情况二成立也不一定发生Intersection索引合并这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多导致回表开销太大而通过Intersection索引求交集后需要回表的记录数大大减少时才会使用Intersection索引合并。3.3.2.Union合并有时候OR关系的不同搜索条件会使用到不同的索引比方说这样SELECT * FROM single_table WHERE key1 a OR key3 bMySQL在某些特定的情况下才可能会使用到Union索引合并(1). 二级索引列是等值匹配的情况对于联合索引来说在联合索引中的每个列都必须等值匹配不能出现只出现匹配部分列的情况。比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作SELECT * FROM single_table WHERE key1 a OR ( key_part1 a AND key_part2 b AND key_part3 c);而下边这两个查询就不能进行Union索引合并SELECT * FROM single_table WHERE key1 a OR (key_part1 a AND key_part2 b AND key_part3 c); SELECT * FROM single_table WHERE key1 a OR key_part1 a;第一个查询是因为对key1进行了范围匹配第二个查询是因为联合索引idx_key_part中的key_part2key_part3列并没有出现在搜索条件中所以这两个查询不能进行Union索引合并。(2). 主键列可以是范围匹配(3). 使用Intersection索引合并的搜索条件这种情况其实也挺好理解就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取并集比方说这个查询SELECT * FROM single_table WHERE key_part1 a AND key_part2 b AND key_part3 c OR (key1 a AND key3 b);优化器可能采用这样的方式来执行这个查询a. 先按照搜索条件key1 a AND key3 b从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。b. 再按照搜索条件key_part1 a AND key_part2 b AND key_part3 c从联合索引idx_key_part中得到另一个主键集合。c. 采用Union索引合并的方式把上述两个主键集合取并集然后进行回表操作将结果返回给用户。当然查询条件符合了这些情况也不一定就会采用Union索引合并也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少通过Union索引合并后进行访问的代价比非索引合并更小时会使用Union索引合并。3.3.3.Sort-Union合并Union索引合并的使用条件太苛刻必须保证各个二级索引列在进行等值匹配的条件下才可能被用到比方说下边这个查询就无法使用到Union索引合并SELECT * FROM single_table WHERE key1 a OR key3 z这是因为根据key1 a从idx_key1索引中获取的二级索引记录的主键值不是排好序的根据key3 z从idx_key3索引中获取的二级索引记录的主键值也不是排好序的索引中记录按主键值排序可使得我们执行并集交集操作的复杂度控制在O(n) 但是key1 a和key3 z这两个条件又特别让我们动心所以我们可以这样a. 先根据key1 a条件从idx_key1二级索引总获取记录并按照记录的主键值进行排序b. 再根据key3 z条件从idx_key3二级索引总获取记录并按照记录的主键值进行排序c. 因为上述的两个二级索引主键值都是排好序的剩下的操作和Union索引合并方式就一样了。我们把上述这种先按照二级索引记录的主键值进行排序之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并很显然这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。3.3.4.索引合并注意事项3.3.4.1.联合索引替代Intersection索引合并SELECT * FROM single_table WHERE key1 a AND key3 b;这个查询之所以可能使用Intersection索引合并的方式执行还不是因为idx_key1和idx_key3是两个单独的B树索引你要是把这两个列搞一个联合索引那直接使用这个联合索引就把事情搞定了何必用啥索引合并呢就像这样ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);