当前位置:首页 > mysql > 正文内容

mysql中index_merge索引合并优化

phpmianshi4年前 (2017-04-10)mysql741

前言

深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。

MySQL在分析执行计划时发现走单个索引的过滤效果都不是很好,对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)

我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 index merge 技术。

MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。


相关文档:http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html


index merge 算法根据合并算法的不同分成了三种:intersect, union, sort_union(先内部intersect然后在外面union


场景

假设我们有如下表结构,数据100万

CREATE TABLE user(
	id INT NOT NULL auto_increment PRIMARY KEY,
	name VARCHAR (8) DEFAULT NULL,
	age TINYINT DEFAULT NULL,
	KEY idx_name (name),
	KEY idx_age (age)


1. index merge 之 intersect

index intersect merge就是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时,才会出现 index intersect merge


select * from user where name='zs' and age=12

explain分析一下发现
possible_keys   key  : 都是 idx_name,idx_age  
extra  : Using intersect(idx_name,idx_age); Using where; Using temporary; Using filesort


2. index merge 之 union

简单而言,index uion merge就是多个索引条件扫描,对得到的结果进行并集运算,显然是多个条件之间进行的是 OR 运算。

下面几种类型的 where 条件,以及他们的组合可能会使用到 index union merge算法:

1) 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

2) 主键上的任何范围条件

3) 任何符合 index intersect merge 的where条件;

上面三种 where 条件进行 OR 运算时,可能会使用 index union merge算法。


3. index merge 之 sort_union

This access algorithm is employed when the WHERE clause was converted to several range conditions combined by OR, but for which the Index Merge method union algorithm is not applicable.


多个条件扫描进行 OR 运算,但是不符合 index union merge算法的,此时可能会使用 sort_union算法


4. index merge的局限

1)If your query has a complex WHERE clause with deep AND/OR nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity laws:


5. 对 index merge 的进一步优化

index merge使得我们可以使用到多个索引同时进行扫描,然后将结果进行合并。听起来好像是很好的功能,但是如果出现了 index intersect merge,那么一般同时也意味着我们的索引建立得不太合理,因为 index intersect merge 是可以通过建立 复合索引进行更一步优化的



6. 复合索引的最左前缀原则

上面我们说到,对复合索引的非最左前缀字段进行 OR 运算,是无法使用到复合索引的


SQL如下:

select c_id, count(1) total from table1
where act_id = 100476 and deleted=0 and create_time is not null
group by c_id ;



在table1表中,act_id 和 deleted 都是独立的索引

select count(*) from table1  where act_id = 100476;   结果为20w行

select count(*) from table1  where deleted=0;  结果为100w行

从上面的解释我们可以看出来,index merge其实就是分别通过对两个独立的index进行过滤之后,将过滤之后的结果聚合在一起,然后在返回结果集。


在我们的这个例子中,由于deleted字段的过滤性不好,故返回的rows依然很多,所以造成的很多的磁盘read,导致了cpu的负载非常的高,直接就出现了延迟。

ps:其实在这个case中,并不需要加2个条件的index,只需要将deleted这个index干掉,直接使用coupon_act_id这个index即可,毕竟这个index的过滤的结果集已经很小了。

或者通过关闭index intersect功能也可以。


总结

开启优化相比不开有n倍以上的优化成绩。由index_merge的原理可以知在数据理更大的情况下优化的效果会更加明显


版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中count(x)到底慢不慢

总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的...

Mysql B+树索引常见面试题

Mysql B+树索引常见面试题

概念一个经典的B+树索引数据结构见下图:B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当...

mysql常见面试题第一讲

一、为什么用自增列作为主键1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键...

mysql中CPU或内存利用率过高问题

CPU 利用率过高原因在 MySQL 使用过程中,出现 CPU 利用率过高甚至超过100%时,与数据库存在低效 SQL 或大量行锁冲突有非常大的关系,一般都是由于大量低效的 SQL 导致,出现行锁冲突...

mysql中Query语句优化基本思路和原则

基本思路和原则在分析如何优化 MySQL Query 之前,我们需要先了解一下 Query 语句优化的基本思路和原则。一般来说,Query 语句的优化思路和原则主要体现在以下几个方面: 1. 优化更需...

innodb中统计数据是如何收集的

InnoDB 统计数据如何查看    1. 通过SHOW TABLE STATUS可以看到关于表的统计数据    2....

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。