联合索引
联合索引是指对表上的多个列进行索引。
联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。 例如一下代码创建了表t，和联合索引 idx_a_b，联合的列为（a, b）。
CREATE TABLE t (
a INT,
b INT,
PRIMARY KEY (a),
KEY idx_a_b (a, b)
)ENGINE=INNODB
联合索引的实现
从本质上来说，联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。
下面来看一个两个整型列组成的联合索引，设为a、b，如下图所示： 上图和之前的单键值的B+树并没有什么不同，键值都是排序的，通过叶子节点可以逻辑上顺序地读出所有数据。 如上图所示：(1, 1)、(1, 2)、(2, 1)、(2, 4)、(3, 1)、 (3, 2)。数据按(a, b)的顺序进行了存放。
如何使用联合索引
因此，对于a，b列的查询语句 SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然是可以使用(a, b) 这个联合索引的。 对于单个的a列查询 SELECT * FROM TABLE WHERE a=xxx，也可以使用这个(a, b) 索引。 但对于b列的查询则不可以使用这课B+树索引。
可以发现叶子节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到（a，b）的索引。
避免排序： 联合索引的第二个好处是已经对第二个键值进行了排序处理，有时候可以避免一次排序。
例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间排序，最后取出近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。
如前所述，联合索引(a, b) 是根据列a、b进行排序，因此下列语句可以直接使用联合索引得到结果：
SELECT ... FROM TABLE WHERE a=xxx order by b
然而对于联合索引（a, b, c）来说，下列语句同样可以直接通过联合索引得到结果：
SELECT ... FROM TABLE WHERE a=xxx order by b
SELECT ... FROM TABLE WHERE a=xxx AND b=xxx order by c
但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引（a, c）并未排序：
SELECT ... FROM TABLE WHERE a=xxx order by c
覆盖索引
InnoDB支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到要查询的记录。 使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，所以其大小远小于聚集索引，因此可以减少大量的IO操作。
MySQL5.0或以下的，以及InnoDB版本小于1.0的，InnoDB不支持覆盖索引特性。
对于InnoDB的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1, primary key2, …, key1, key2, …)。例如，下列语句都可仅使用一次辅助索引来完成查询：
...
SELECT primary key1, kye2 FROM table where key1=xxx;
SELECT primary key1, primary key2, kye2 FROM table where key1=xxx;
对某些统计问题而言，辅助索引还有另一个好处。 比如存在包含主键的联合索引：
SELECT COUNT(*) FROM buy_log;
InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于还有辅助索引，且其远小于聚集索引，选择辅助索引可以减少IO操作。 此外，通常情况下，诸如(a, b)的联合索引，一般是不可以选择列b中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如下述语句：
SELECT COUNT(*) FROM buy_log
WHERE buy_date >= '2011-01-01' AND buy_date<'2011-02-01'
表buy_log有（userid, buy_date) 的联合索引，这里指根据列b进行条件查询，一般情况下是不能进行该联合索引的，但是这句SQL查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引。
优化器选择不使用索引的情况
在某些情况下，当只想EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。
这种情况多发生于范围查找、JSON连接操作等情况下。例如：
SELECT * FROM orderdetails
WHERE orderId>10000 and orderid<102000;
上述SQL查找订单号大于10000的订单详情，通过命令SHOW INDEDX FROM orderdetails，可以发现表orderdetails有（OrderID，ProductID）的联合主键，此外还有对于列OrderID（非主键）的单个索引。 上述SQL显然可以通过扫描OrderID上的索引进行数据的查找，然而并没有。 该句使用了PRIMARY聚集索引，也就是表扫描（table scan），而非OrderID辅助索引扫描（index scan）。
原因
这是为什么呢？因为用户要选取的数据是整行信息，而OrderId索引不能覆盖到我们要查询的信息。 虽然OrderId索引中数据时顺序存放的，还需要一次书签（到主键索引去）查找的数据则是无序的，因此变为列磁盘上的离散读操作。 如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。
何时选择辅助索引（当无法索引覆盖时）
因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量。 这是由当前传统机械硬盘的特性决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引。
索引提示
MySQL支持索引提示（INDEX HINT），显式地告诉优化器使用哪个索引。 个人总结以下两种情况可能需要用到INDEX HINT：
MySQL优化器选择了错误的索引，导致SQL语句运行的很慢（非常少见）某个SQL语句可选择的索引非常多，这时优化器选择执行计划时间的开销可能大于SQL语句本身。例如，优化器分析选择执行计划时间的开销可能会大于SQL语句本身。
这里不做赘述。
Multi-Range Read优化
MySQL5.6版本开始支持Multi-Range Read（MRR）优化。 Multi-Range Read优化的目的就是减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。
Multi-Range Read优化可适用于range，ref，eq_ref类型的查询。 MRR优化有以下几个好处：
MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行查找。减少缓冲池中页被替换的次数。批量处理对键值的查询操作。
原理
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：
将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据时根据辅助索引键值排序的。将缓存中的键值根据RowID进行排序。根据RowID的排序顺序来访问时机的数据文件。
此外，若InnoDB存储引擎或者MyISAM的缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。 若是按照主键顺序进行访问，则可以将此重复行为降为最低。如下面这句SQL语句：
SELECT * FROM salaries WHERE salary>10000 AND salary<40000;
salary上 有一个辅助索引idx_s，因此除了通过辅助索引查找键值外，还需要通过书签（主键）查找来进行整行数据的查询。 当不启用Multi-Range Read特性时，不会在列Extra看到Using index condition和Using MRR选项；启用时则可以看到。 而启用Multi-Range Read特性后，查询性能会得到很大提高。
执行过程
此外，Multi-Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。 这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，例如：
SELECT * FROM t
WHERE key_part1 >= 1000 AND key_part1 < 2000
AND key_part2 = 10000;
表t有（key_part1, key_part2）的联合索引，因此索引根据key_part1, key_part2的位置关系进行排序。
若没有Multi-Read Range，此时查询类型为Range，SQL优化器会先将key_part1大于1000且小于2000的数据都取出（即使key_part2不等于1000）。 待取出后再根据key_part2的条件进行过滤，这会导致无用数据被取出，如果有大量的数据且其key_part2不等于1000，则启用Multi-Range Read优化会使性能有巨大的提升。
若启用了MRR优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会先将查询条件拆分为（1000，1000），（1001，1000），（1002，1000），… ，( 1999， 1000），最后再根据这些拆分出的条件进行数据的查询。
如何启用MRR
是否启用Multi-Range Read优化可以通过参数optimizer_switch中的标记（flag）来控制。 当mrr为on时，表示启用了MRR优化。mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。 若将mrr设为on，mrr_cost_based设为off，则总是启用Multi-Range Read优化。
下述语句可以将Multi-Range Read优化总是设为开启状态：
SET @@optimizer_switch='mrr=on,mrr_cost_based=off';
参数read_rnd_buffer_size用来控制键值的缓冲区大小，当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256k：
SELECT @@read_rnd_buffer_size\G;
Index Condition Pushdown(ICP)优化
和Multi-Range Read一样，Index Condition Pushdown同样是MySQL5.6开始支持的一种根据索引进行查询的优化方式。
当MySQL版本不支持ICP时，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。 在支持Index Condition Pushdown（ICP）后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询条件下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。
Index Condition Pushdown优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。当优化器选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Using index condition提示。
参考
精彩链接
发表评论