MySQL技术内幕 InnoDB存储引擎：B+树索引的使用

MySQL技术内幕 InnoDB存储引擎：B+树索引的使用

1、联合索引

MySQL允许对表上的多个列进行索引,联合索引的创建方法与单个索引创建的方法一样,不同之处仅在于有多个索引列。

CREATE TABLE t（ a INT, b INT,PRIMARY KEY（a）,KEY idx_a_b（a, b））ENGINE=InnoDB

多个键值的B+树

对于查询SELECT*FROM TABLE WHERE a=xxx and b=xxx,显然是可以使用（a, b）这个联合索引的。对于单个的a列查询SELECT*FROM TABLE WHERE a=xxx,也可以使用这个（a, b）索引。但对于b列的查询SELECT*FROM TABLE WHERE b=xxx,则不可以使用这棵B+树索引。

联合索引的第二个好处是已经对第二个键值进行了排序处理。 例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。

2、覆盖索引（covering index）

即从辅助索引中就可以得到查询的记录， 而不需要查询聚集索引中的记录。 使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息， 故其大小要远小于聚集索引， 因此可以减少大量的 IO 操作。

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，…,key1，key2，…）。例如，下面语句都可仅使用一次辅助联合索引来完成查询

SELECT key2 FROM table WHERE key1=xxx;SELECT primary key2,key2 FROM table key1=xxx;SELECT primary key1,key2 FROM table key1=xxx;SELECT primary key1,primary key2,key2 FROM table key1=xxx;

覆盖索引的另一个好处是对某些统计问题而言，如上的buy_log，要进行如下查询

CREATE TABLE buy_log(userid INT UNSIGNED NOT NULL,buy_date DATE)ENGINE=INNODB;INSERT INTO buy_log VALUES(1,‘2009-01-01‘);INSERT INTO buy_log VALUES(2,‘2009-01-01‘);INSERT INTO buy_log VALUES(3,‘2009-01-01‘);INSERT INTO buy_log VALUES(1,‘2009-02-01‘);INSERT INTO buy_log VALUES(3,‘2009-02-01‘);INSERT INTO buy_log VALUES(1,‘2009-03-01‘);INSERT INTO buy_log VALUES(1,‘2009-04-01‘);ALTER TABLE buy_log ADD KEY(userid);ALTER TABLE buy_log ADD KEY(userid,buy_date);

explain SELECT COUNT(*) FROM buy_log;

InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log还有辅助索引，而辅助索引远小于聚集索引。选择辅助索引可以减少IO操作。

如图显示。possible_keys是NULL，但是实际执行优化器却选择了userid，而列Extra的Using index就是代表优化器选择了覆盖索引的操作 。

3、索引提示

MySQL数据库支持索引提示(INDEX HINT)显式的高速优化器使用了哪个索引。以下是可能需要用到INDEX HINT的情况

MySQL数据库的优化器错误的选择了某个索引，导致SQL运行很慢。这个在最新版的数据库版本中非常少见。优化器在绝大部分情况下工作的非常有效和正确。某些SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身例如优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过index hint来强制使优化器不进行各个路径的成本分析直接选择指定的索引来完成查询

4、Multi-Range Read优化

MySQL 5.6开始支持Multi-Range Read(MRR)优化。目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对IO-bound类型的SQL查询语句可带来性能极大的提升。 MRR优化可适用于rangeref,eq_ref类型的查询

MRR优化的好处

MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果按照主键进行排序，并按照主键排序的顺序进行书签查找减少缓冲池中页被替换的次数批量处理对键值的查询操作

对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR工作方式如下

将查询得到的辅助索引键值存放在一个缓存中，这是缓存中的数据是根据辅助索引键值排序的将缓存中的键值根据RowID进行排序根据RowID的排序顺序来访问实际的数据文件

此外，若InnoDB存储引擎或者MyISAM存储引擎的缓冲池不足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后有不断地被读入缓冲池。

5、Index Condition Pushdown 优化

ICP原理通俗讲就是,查询过程中,直接在查询引擎层的API获取数据的时候实现"非直接索引"过滤条件的筛选,而不是查询引擎层查询出来之后在Server层筛选。

换句话说就是ICP在获取数据的同时实现了where的次选条件中无法直接使用索引的情况下的筛选,避免了没有ICP优化的时候分两个步骤的实现(获取数据的过程没有做次选条件的过滤)

如果是非ICP优化查询的话,是两步：

获取数据获取的数据进行条件筛选。

显然,相比后者,前者可以一步实现索引的查找Seek+filter,效率上更高。

适应的场景:

ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法

本文整理自：《​​MySQL技术内幕 InnoDB存储引擎​​》

作者：jiankunking ​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。