mysql 8.0 in 索引_MySQL 8.0索引合並

簡介

參考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個字段(所有索引字段都被覆蓋)，N>=1，N如果大於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個字段(所有索引字段都被覆蓋)，N>=1，N如果大於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`)

) ENGINE=InnoDB AUTO_INCREMENT=140249 DEFAULT CHARSET=utf8;

2.死鎖信息提取與分析

通過show engine innodb status;命令可以獲取innodb引擎中最近一次發生死鎖的信息，信息如下:

*** (1) TRANSACTION: UPDATE t_xxx_customer SET xxx_id='101', xxx_name='bbb' where customer_id=235646 and partner_id=1688 and deleted=0;

*** (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_id='102', xxx_name='aaa' where customer_id=151069 and partner_id=1688 and deleted=0;

*** (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_merge，Extra的信息是Using intersect(customerid,partnerid)，從而我們得知語句執行計划走了index_merge優化，單個語句通過兩個索引(customerid,partnerid)來提取記錄集合並取交集獲得最終結果集。index_merge具體算法不在此展開，基本使用場景是語句包含多個查詢條件，每個條件都單獨存在索引，而單個條件的索引過濾度不高，組合起來過濾度比較高，這個時候就可能會走index_merge優化，使得單個SQL語句可以同時利用兩個索引過濾。會不會與index_merge有關呢？

在index_merge的情況下，會導致二級索引與主鍵索引順序不一致的情況嗎？結合上面的死鎖信息，我們得知死鎖兩個的二級索引key是0x698，而主鍵索引key是0x21747。我們看看到底是哪條記錄的主鍵和二級索引發生了死鎖，

可以看到0x21747對應的customer_id為151069，partner_id為1688，是不是感覺似曾相識，對的，第二個事務的語句查詢條件就是這兩個條件的組合。這說明，對於這條記錄，第一個事務語句只有partnerid索引(1688)滿足條件；對於第二個事務，customer_id和partner_id索引都滿足條件。由於每個語句執行時都需要利用兩個二級索引，假設先使用customer_id索引掃描，然后使用partner_id索引掃描，那么對於id為0x21747的記錄，事務1的partner_id=1688滿足條件，加partner_id鎖，然后對對應的PK索引加鎖；對於事務2，對customer_id= 151069加鎖，對對應的PK索引加鎖，然后對partner_id=1688索引加鎖。那么對partner_id二級索引和PK主鍵索引在兩個事務的上鎖順序是相反的，所以導致了死鎖。對於id為0x21747記錄：

序號

事務1

事務2

1

customer_id 不滿足條件不加鎖

customer_id= 151069 加鎖

2

partner_id=1688加鎖

PK=0x21747加鎖

3

PK=0x21747加鎖

partner_id=1688加鎖

4

PK=0x21747加鎖

表格第2步和第3步，兩個事務的加鎖順序是相反的，導致了死鎖發生。

=============================================================================