MySQL 5.7分區(qū)表性能下降的原因是什么

這篇文章主要講解了“MySQL 5.7分區(qū)表性能下降的原因是什么”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL 5.7分區(qū)表性能下降的原因是什么”吧！

問(wèn)題描述

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

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

1. 數(shù)據(jù)庫(kù)的版本為5.7.18, 保留分區(qū)表，性能會(huì)下降。

2. 數(shù)據(jù)庫(kù)版本為5.7.18，把表調(diào)整為非分區(qū)表，性能正常。

3. 把數(shù)據(jù)庫(kù)的版本回退到5.6.21版本，保留分區(qū)表，性能也是正常

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

問(wèn)題重現(xiàn)

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

// 創(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的記錄更新。

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

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

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

進(jìn)一步分析

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

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

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

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

RecLock::lock_add函數(shù)可以推出行鎖的生成原因。因此，通過(guò)對(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ù)，此處即為非正常行鎖生成的原因。

最終問(wèn)題歸結(jié)到m_part_spec.end_part 的生成原因。通過(guò)對(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的記錄，超過(guò)了測(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ā)下降，效率降低。

感謝各位的閱讀，以上就是“MySQL 5.7分區(qū)表性能下降的原因是什么”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL 5.7分區(qū)表性能下降的原因是什么這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！