单表访问优化

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;

然后我们需要为这个表插入10000行记录

访问方法

• 使用全表扫描进行查询
• 是所有索引进行查询

const

设计MySQL的大叔认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是像坐火箭一样快的，所以他们把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。

不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

ref

SELECT * FROM single_table WHERE key1 = 'abc';
由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。

需要注意的两点:

• 二级索引列值为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';

但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样：
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

range

设计MySQL的大叔把这种利用索引进行范围匹配的访问方法称之为：range。
此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引。

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。

设计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之为：index。

all

注意事项

二级索引 + 回表

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

优化器一般会根据single_table表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询（关于如何比较的细节我们后边的章节中会唠叨）。

一般来说，等值查找比范围查找需要扫描的行数更少（也就是ref的访问方法一般比range好，但这也不总是一定的，也可能采用ref访问方法的那个索引列的值为特定值的行数特别多），所以这里假设优化器决定使用idx_key1索引进行查询，那么整个查询过程可以分为两个步骤：

步骤1：使用二级索引定位记录的阶段，也就是根据条件key1 = 'abc'从idx_key1索引代表的B+树中找到对应的二级索引记录。

步骤2：回表阶段，也就是根据上一步骤中找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2 > 1000到完整的用户记录继续过滤。

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

其实对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的区间。

所有搜索条件都可以使用某个索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

有的搜索条件无法使用索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
这个查询中,索引只有idx_key2一个,所以在使用二级索引idx_key2定位记录的阶段用不到common_field = 'abc'这个条件，这个条件是在回表获取了完整的用户记录后才使用的，所以在确定范围区间的时候不需要考虑common_field = 'abc'这个条件 ,
我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。

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索引的搜索条件替换为TRUE：
SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
简化
SELECT * FROM single_table WHERE TRUE;
也就是说走索引的代价大于直接全表扫描的代价,所以就用不到索引

索引合并 index merge

Intersection合并

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

只读取一个二级索引的成本：
按照某个搜索条件读取一个二级索引
根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：
按照不同的搜索条件分别读取不同的二级索引
将从多个二级索引得到的主键值取交集，然后进行回表操作

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

情况二：主键列可以是范围匹配
二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

Union合并
Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。

情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

比方说下边这个查询可能用到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';

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

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

搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询：
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');
优化器可能采用这样的方式来执行这个查询：

先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。

再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引idx_key_part中得到另一个主键集合。

采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。