前言：希望通過本文，使MySQL5.7.18的使用者知曉分區(qū)表使用中存在的陷阱，避免在該版本上繼續(xù)踩坑。同時(shí)通過對(duì)源碼的分享，升級(jí)MySQL5.7.18時(shí)分區(qū)表性能下降的根本原因，向MySQL源碼愛好者展示分區(qū)表實(shí)現(xiàn)中鎖的運(yùn)用。

問題描述

MySQL 5.7版本中，性能相關(guān)的改進(jìn)非常多。包括臨時(shí)表相關(guān)的性能改進(jìn)，連接建立速度的優(yōu)化和復(fù)制分發(fā)相關(guān)的性能改進(jìn)等等?；旧喜恍枰雠渲眯薷模恍枰?jí)到5.7版本，就能帶來不少性能的提升。

我們?cè)跍y(cè)試環(huán)境，把數(shù)據(jù)庫升級(jí)到5.7.18版本，驗(yàn)證MySQL 5.7.18版本是否符合我們的預(yù)期。觀察運(yùn)行了一段時(shí)間，有開發(fā)反饋，數(shù)據(jù)庫的性能比之前的5.6.21版本有下降。主要的表現(xiàn)特征是遇到比較多的鎖超時(shí)情況。開發(fā)另外反饋，性能下降相關(guān)的表都是分區(qū)表。更新走的都是主鍵。這個(gè)反饋引起了我們重視。我們做了如下嘗試：

1. 數(shù)據(jù)庫的版本為5.7.18, 保留分區(qū)表，性能會(huì)下降。
2. 數(shù)據(jù)庫版本為5.7.18，把表調(diào)整為非分區(qū)表，性能正常。
3. 把數(shù)據(jù)庫的版本回退到5.6.21版本，保留分區(qū)表，性能也是正常

通過上述測(cè)試，我們大致判定，這個(gè)性能下降和MySQL5.7版本升級(jí)有關(guān)。

問題重現(xiàn)

測(cè)試環(huán)境的數(shù)據(jù)庫表結(jié)構(gòu)比較多，并且調(diào)用關(guān)系也比較復(fù)雜。為了進(jìn)一步分析并定位問題，我們抽絲剝繭，構(gòu)建了如下一個(gè)簡(jiǎn)單的重現(xiàn)過程

// 創(chuàng)建一個(gè)測(cè)試分區(qū)表t2: 
 
CREATE TABLE `t2`( 
 
  `id` INT(11) NOT NULL, 
 
  `dt` DATETIME NOT NULL, 
 
  `data` VARCHAR(10) DEFAULT NULL, 
 
  PRIMARYKEY (`id`,`dt`), 
 
  KEY`idx_dt`(`dt`) 
 
) ENGINE=INNODB DEFAULTCHARSET=latin1 
 
/*!50100 PARTITION BY RANGE (to_days(dt)) 
 
(PARTITION p20170218 VALUES LESS THAN (736744)ENGINE = InnoDB, 
 
 PARTITIONp20170219 VALUES LESS THAN (736745) ENGINE = InnoDB, 
 
 PARTITIONpMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */  
  
 
// 插入測(cè)試數(shù)據(jù) 
 
INSERT INTO t2 VALUES (1, NOW(), '1'); 
 
INSERT INTO t2 VALUES (2, NOW(), '2'); 
 
INSERT INTO t2 VALUES (3, NOW(), '3');  
  
 
// SESSION 1 對(duì)id = 1的 記錄 做一個(gè)更新操作，事務(wù)先不提交。 
 
BEGIN;UPDATE t2 SET DATA = '12' WHERE id = 1;  
  
 
// SESSION 2 對(duì)id = 2 的記錄做一個(gè)更新。  
 
BEGIN;UPDATE t2 SET DATA = '21' WHERE id = 2;  

在SESSION 2，我們發(fā)現(xiàn)，這個(gè)更新操作一直在等待。ID是主鍵，按道理，主鍵id = 1 的記錄更新，不至于影響到主鍵id = 2的記錄更新。

查詢information_schema下的innodb_locks這張表。這張表是用于記錄InnoDB事務(wù)嘗試申請(qǐng)但還未獲取的鎖，以及阻塞其他事務(wù)的事務(wù)所擁有的鎖。有兩條記錄：

觀察此時(shí)的innodb_locks表，事務(wù)id=40021鎖住第3頁的第2行記錄，導(dǎo)致事務(wù)id=40022無法進(jìn)行下去。

我們把數(shù)據(jù)庫回退到5.6.21版本，則不能重現(xiàn)上述場(chǎng)景。

進(jìn)一步分析

根據(jù)innodb_locks表提供的信息，我們知道問題在于InnoDB鎖定了不恰當(dāng)?shù)男?。該表是memory存儲(chǔ)引擎。我們?cè)趍emory 存儲(chǔ)引擎的插入接口設(shè)置斷點(diǎn)，得到如下堆棧信息。確定是紅框部分，將鎖信息寫入到innodb_locks表中。 

并在函數(shù)fill_innodb_locks_from_cache中得以確認(rèn)，每次寫入行的數(shù)據(jù)，都是從如下代碼中Cache對(duì)象中獲取的。

我們知道Cache中保存了事務(wù)鎖的信息，因此需要進(jìn)一步查找Cache中的數(shù)據(jù)，是如何添加進(jìn)去的。通過搜索cache對(duì)象在innodb代碼中出現(xiàn)的位置，找到函數(shù)add_lock_to_cache。在此函數(shù)設(shè)置斷點(diǎn)進(jìn)行調(diào)試后，發(fā)現(xiàn)其內(nèi)容與填寫innodb_locks表的數(shù)據(jù)一致。確定該函數(shù)使用的lock對(duì)象，就是我們要找的鎖對(duì)象。

針對(duì)lock_t 類型的使用位置進(jìn)行排查。經(jīng)過篩選和調(diào)試，發(fā)現(xiàn)函數(shù)RecLock::lock_add中，生成的行鎖被加入到該鎖所在的事務(wù)鏈表中。

RecLock::lock_add函數(shù)可以推出行鎖的生成原因。因此，通過對(duì)該函數(shù)進(jìn)行斷點(diǎn)設(shè)置，查看函數(shù)堆棧，在如下堆棧內(nèi)，定位到紅框位置的函數(shù)：

針對(duì)Partition_helper::handle_ordered_index_scan的如下代碼進(jìn)行跟蹤，根據(jù)該段代碼的分析，m_part_spec.end_part 決定了進(jìn)行上鎖的***行數(shù)，此處即為非正常行鎖生成的原因。

最終問題歸結(jié)到m_part_spec.end_part 的生成原因。通過對(duì)end_part 使用地方進(jìn)行排查，最終在get_partition_set函數(shù)中定位到該變量在使用前的初始設(shè)置值。從代碼中可以看出，每次單條記錄的update操作，在進(jìn)行index scan上鎖時(shí)，對(duì)分區(qū)表數(shù)目相同的行數(shù)進(jìn)行上鎖。這個(gè)是根本原因。

驗(yàn)證結(jié)論

根據(jù)之前的分析，每次單條記錄的update操作，會(huì)對(duì)分區(qū)表數(shù)目相同的行數(shù)進(jìn)行上鎖。我們嘗試驗(yàn)證我們的發(fā)現(xiàn)。

新增如下兩條記錄：

INSERT INTO t2 VALUES (4, NOW(), '4'); 
 
INSERT INTO t2 VALUES (5, NOW(), '5');  
 
// SESSION 1 對(duì)id = 1的 記錄 做一個(gè)更新操作，事務(wù)先不提交。 
 
BEGIN;UPDATE t2 SET DATA = '12' WHERE id = 1; 
 
// SESSION 2 現(xiàn)在對(duì)id = 4 的記錄做一個(gè)更新。  
 
BEGIN;UPDATE t2 SET DATA = '44' WHERE id = 4;  

我們發(fā)現(xiàn)，對(duì)id = 4的更新可以正常進(jìn)行。不會(huì)受到id = 1 的更新影響。這是因?yàn)閕d=4的記錄，超過了測(cè)試案例的分區(qū)個(gè)數(shù)，不會(huì)被鎖住。在實(shí)際應(yīng)用中，分區(qū)表所定義分區(qū)數(shù)不會(huì)如測(cè)試用例中的只有3個(gè)，而是數(shù)十個(gè)乃至數(shù)百個(gè)。這樣進(jìn)行上鎖的結(jié)果，將加劇更新情況下的鎖沖突，導(dǎo)致事務(wù)處于鎖等待狀態(tài)。如下圖所示，每個(gè)事務(wù)都上N個(gè)行鎖，那么這些上鎖記錄互相覆蓋的可能性就極大的提高，也就導(dǎo)致并發(fā)下降，效率降低。

結(jié)論

通過上述分析，我們非常確認(rèn)，這個(gè)應(yīng)該是MySQL 5.7版本的一個(gè)regression。我們提交了一個(gè)Bug到開源社區(qū)。Oracle確認(rèn)是一個(gè)問題，需進(jìn)一步分析調(diào)查這個(gè)Bug。