网络营销网站建设ppt,什么网站可以免费做找客户,大连网站网页设计公司,兰州市规划建设局网站简介参考https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html#index-merge-intersection。索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描#xff0c;而不是跨多个表的索引扫描。合并会产生底层扫描的三种… 简介 参考https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html#index-merge-intersection。 索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式unions合并、intersections交集、unions-of-intersections先取交集再合并。 以下四个例子会产生索引合并 1、SELECT * FROM tbl_name WHERE key1 10 OR key2 20; 2、SELECT * FROM tbl_name WHERE (key1 10 OR key2 20) AND non_key 30; 3、SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE value%) AND t2.key1 t1.some_col; 4、SELECT * FROM t1, t2 WHERE t1.key1 1 AND (t2.key1 t1.some_col OR t2.key2 t1.some_col2); 索引合并有以下已知的局限性 1、如果查询语句包含一个带有严重AND/OR嵌套的复杂的WHERE子句而MySQL没有选择最佳计划那么可以尝试使用以下的标志符转换 (x AND y) OR z (x OR z) AND (y OR z) (x OR y) AND z (x AND z) OR (y AND z) 2、索引合并不适用于全文索引。 在 EXPLAIN 语句输出的信息中索引合并在type列中表现为“index_merge”在这种情况下key列包含使用的索引列表。 索引合并访问方法有几种算法表现在 EXPLAIN 语句输出的Extra字段中 Using intersect(...) Using union(...) Using sort_union(...) 下面将更详细地描述这些算法。优化器根据各种可用选项的成本估计在不同的索引合并算法和其他访问方法之间进行选择。 Index Merge Intersection算法 Index Merge Intersection算法对所有使用的索引执行同步扫描并生成从合并的索引扫描接收到的行序列的交集。 这种算法适用于当WHERE子句被转换成多个使用AND连接的不同索引key上的范围条件且条件是以下两种之一 一、这种形式的N部分表达式索引正好包括N个字段所有索引字段都被覆盖N1N如果大于1就是复合索引 key_part1 const1 AND key_part2 const2 ... AND key_partN constN。 二、InnoDB表主键上的任何范围条件。 例子 1.SELECT * FROM innodb_table WHERE primary_key 10 AND key_col1 20; 2.SELECT * FROM tbl_name WHERE key1_part1 1 AND key1_part2 2 AND key2 2; Index Merge Union算法 该算法类似于Index Merge Intersection算法适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件且条件是以下三种之一 一、这种形式的N部分表达式索引正好包括N个字段所有索引字段都被覆盖N1N如果大于1就是复合索引 key_part1 const1 AND key_part2 const2 ... AND key_partN constN。 二、InnoDB表主键上的任何范围条件。 三、符合Index Merge Intersection算法的条件。 例子 1.SELECT * FROM t1 WHERE key1 1 OR key2 2 OR key3 3; 2.SELECT * FROM innodb_table WHERE (key1 1 AND key2 2) OR (key3 foo AND key4 bar) AND key5 5; Index Merge Sort-Union算法 该算法适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件但是不符合 Index Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的区别在于Index Merge Sort-Union必须首先获取所有行的行id并在返回任何行之前对它们进行排序。 例子 1.SELECT * FROM tbl_name WHERE key_col1 10 OR key_col2 20; 2.SELECT * FROM tbl_name WHERE (key_col1 10 OR key_col2 20) AND nonkey_col 30; 索引合并引发的死锁 索引合并是MySQL优化查询速度的一种方式但是错误的使用也会导致死锁处理方式就是将引起索引合并的索引修改为复合索引。曾经就遇到过和以下所讲的几乎一样的问题所以这里就直接把别人写的转载过来转载自https://blog.csdn.net/hehehaha1123/article/details/59058067。 概述 前几天排查了一个死锁问题最开始百思不得其解因为发生死锁的两个事务是单语句事务语句类型相同(where属性列相同仅值不同)而且语句都走了相同的索引但最终确实发生了死锁。通过定位排查发现问题的源头就是index_merge死锁的原因也很普通两个事务加锁顺序不同并存在相互等待的情况。因为这个案例比较特殊所以在此分享给大家。 死锁信息 拿到死锁问题首先需要查看几个基本信息包括死锁等待关系表结构定义等。 1.表结构定义 Create Table: CREATE TABLE t_xxx_customer ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键, partner_id bigint(20) unsigned DEFAULT NULL, customer_id bigint(20) unsigned DEFAULT NULL, deleted tinyint(4) DEFAULT NULL, partner_user_id bigint(20) unsigned DEFAULT NULL, xxx_id varchar(128) DEFAULT NULL, xxx_name varchar(256) DEFAULT NULL, PRIMARY KEY (id), KEY partner_id (partner_id), KEY customer_id (customer_id), KEY partner_user_id (partner_user_id) ) ENGINEInnoDB AUTO_INCREMENT140249 DEFAULT CHARSETutf8; 2.死锁信息提取与分析 通过show engine innodb status;命令可以获取innodb引擎中最近一次发生死锁的信息信息如下: *** (1) TRANSACTION: UPDATE t_xxx_customer SET xxx_id101, xxx_namebbb where customer_id235646 and partner_id1688 and deleted0; *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 1640 page no 3947 n bits 432 index partner_id of table xxx.t_xxx_customer trx id 2625291980 lock_mode X locks rec but not gap Record lock, heap no 334 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 0000000000000698; asc ;; 1: len 8; hex 0000000000021747; asc G;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1640 page no 3395 n bits 160 index PRIMARY of table t_xxx_customer trx id 2625291980 lock_mode X locks rec but not gap waiting Record lock, heap no 89 PHYSICAL RECORD: n_fields 25; compact format; info bits 0 *** (2) TRANSACTION: UPDATE t_xxx_customer SET xxx_id102, xxx_nameaaa where customer_id151069 and partner_id1688 and deleted0; *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1640 page no 3395 n bits 160 index PRIMARY of table xxx.t_xxx_customer trx id 2625291981 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1640 page no 3947 n bits 432 index partner_id of table xxx.t_xxx_customer trx id 2625291981 lock_mode X locks rec but not gap waiting Record lock, heap no 334 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 0000000000000698; asc ;; 1: len 8; hex 0000000000021747; asc G;; *** WE ROLL BACK TRANSACTION (2) 从死锁结果来看我们很容易看到事务1持有 partner_id二级索引上的锁等待PK索引上的锁而事务2持有PK索引锁等待partner_id二级索引上的锁两个事务相互持有对方需要的锁资源而无法往前推进造成死锁。单从死锁信息来看我们可能会比较疑惑每个事务只有一个语句为什么同样的语句对二级索引和主键的加锁顺序会不同 产生死锁的原因 首先我们来看看语句的执行计划 语句的type是index_mergeExtra的信息是Using intersect(customerid,partnerid)从而我们得知语句执行计划走了index_merge优化单个语句通过两个索引(customerid,partnerid)来提取记录集合并取交集获得最终结果集。index_merge具体算法不在此展开基本使用场景是语句包含多个查询条件每个条件都单独存在索引而单个条件的索引过滤度不高组合起来过滤度比较高这个时候就可能会走index_merge优化使得单个SQL语句可以同时利用两个索引过滤。会不会与index_merge有关呢 在index_merge的情况下会导致二级索引与主键索引顺序不一致的情况吗结合上面的死锁信息我们得知死锁两个的二级索引key是0x698而主键索引key是0x21747。我们看看到底是哪条记录的主键和二级索引发生了死锁 可以看到0x21747对应的customer_id为151069partner_id为1688是不是感觉似曾相识对的第二个事务的语句查询条件就是这两个条件的组合。这说明对于这条记录第一个事务语句只有partnerid索引(1688)满足条件对于第二个事务customer_id和partner_id索引都满足条件。由于每个语句执行时都需要利用两个二级索引假设先使用customer_id索引扫描然后使用partner_id索引扫描那么对于id为0x21747的记录事务1的partner_id1688满足条件加partner_id锁然后对对应的PK索引加锁对于事务2对customer_id 151069加锁对对应的PK索引加锁然后对partner_id1688索引加锁。那么对partner_id二级索引和PK主键索引在两个事务的上锁顺序是相反的所以导致了死锁。对于id为0x21747记录 序号 事务1 事务2 1 customer_id 不满足条件不加锁 customer_id 151069 加锁 2 partner_id1688加锁 PK0x21747加锁 3 PK0x21747加锁 partner_id1688加锁 4 PK0x21747加锁 表格第2步和第3步两个事务的加锁顺序是相反的导致了死锁发生。 转载于:https://www.cnblogs.com/gjb724332682/p/11018678.html
