共計 2902 個字符，預計需要花費 8 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 5.7 分區表性能下降的原因是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“MySQL 5.7 分區表性能下降的原因是什么”吧！

問題描述

MySQL  5.7 版本中，性能相關的改進非常多。包括臨時表相關的性能改進，連接建立速度的優化和復制分發相關的性能改進等等。基本上不需要做配置修改，只需要升級到 5.7 版本，就能帶來不少性能的提升。

我們在測試環境，把數據庫升級到 5.7.18 版本，驗證 MySQL  5.7.18 版本是否符合我們的預期。觀察運行了一段時間，有開發反饋，數據庫的性能比之前的 5.6.21 版本有下降。主要的表現特征是遇到比較多的鎖超時情況。開發另外反饋，性能下降相關的表都是分區表。更新走的都是主鍵。這個反饋引起了我們重視。我們做了如下嘗試：

數據庫的版本為 5.7.18, 保留分區表，性能會下降。

數據庫版本為 5.7.18，把表調整為非分區表，性能正常。

把數據庫的版本回退到 5.6.21 版本，保留分區表，性能也是正常

通過上述測試，我們大致判定，這個性能下降和 MySQL5.7 版本升級有關。

問題重現

測試環境的數據庫表結構比較多，并且調用關系也比較復雜。為了進一步分析并定位問題，我們抽絲剝繭，構建了如下一個簡單的重現過程

//  創建一個測試分區表 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) */ //  插入測試數據  INSERT INTO t2 VALUES (1, NOW(),  1  INSERT INTO t2 VALUES (2, NOW(),  2  INSERT INTO t2 VALUES (3, NOW(),  3  // SESSION 1  對 id = 1 的   記錄   做一個更新操作，事務先不提交。 BEGIN;UPDATE t2 SET DATA =  12  WHERE id = 1; // SESSION 2  對 id = 2  的記錄做一個更新。 BEGIN;UPDATE t2 SET DATA =  21  WHERE id = 2;

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

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

觀察此時的 innodb_locks 表，事務 id=40021 鎖住第 3 頁的第 2 行記錄，導致事務 id=40022 無法進行下去。

我們把數據庫回退到 5.6.21 版本，則不能重現上述場景。

進一步分析

根據 innodb_locks 表提供的信息，我們知道問題在于 InnoDB 鎖定了不恰當的行。該表是 memory 存儲引擎。我們在 memory   存儲引擎的插入接口設置斷點，得到如下堆棧信息。確定是紅框部分，將鎖信息寫入到 innodb_locks 表中。

并在函數 fill_innodb_locks_from_cache 中得以確認，每次寫入行的數據，都是從如下代碼中 Cache 對象中獲取的。

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

針對 lock_t 類型的使用位置進行排查。經過篩選和調試，發現函數 RecLock::lock_add 中，生成的行鎖被加入到該鎖所在的事務鏈表中。

RecLock::lock_add 函數可以推出行鎖的生成原因。因此，通過對該函數進行斷點設置，查看函數堆棧，在如下堆棧內，定位到紅框位置的函數：

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

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

驗證結論

根據之前的分析，每次單條記錄的 update 操作，會對分區表數目相同的行數進行上鎖。我們嘗試驗證我們的發現。

新增如下兩條記錄：

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

我們發現，對 id = 4 的更新可以正常進行。不會受到 id = 1   的更新影響。這是因為 id= 4 的記錄，超過了測試案例的分區個數，不會被鎖住。在實際應用中，分區表所定義分區數不會如測試用例中的只有 3 個，而是數十個乃至數百個。這樣進行上鎖的結果，將加劇更新情況下的鎖沖突，導致事務處于鎖等待狀態。如下圖所示，每個事務都上 N 個行鎖，那么這些上鎖記錄互相覆蓋的可能性就極大的提高，也就導致并發下降，效率降低。

感謝各位的閱讀，以上就是“MySQL 5.7 分區表性能下降的原因是什么”的內容了，經過本文的學習后，相信大家對 MySQL 5.7 分區表性能下降的原因是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！