MySQL5.7分區(qū)表性能下降的原因是什么

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

站在用戶的角度思考問題，與客戶深入溝通，找到若羌網站設計與若羌網站推廣的解決方案，憑借多年的經驗，讓設計與互聯網技術結合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網站制作、成都網站設計、企業(yè)官網、英文網站、手機端網站、網站推廣、域名注冊、虛擬空間、企業(yè)郵箱。業(yè)務覆蓋若羌地區(qū)。

問題描述

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

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

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

2. 數據庫版本為5.7.18，把表調整為非分區(qū)表，性能正常。

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

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

問題重現

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

// 創(chuàng)建一個測試分區(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) */     // 插入測試數據  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，我們發(fā)現，這個更新操作一直在等待。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。在此函數設置斷點進行調試后，發(fā)現其內容與填寫innodb_locks表的數據一致。確定該函數使用的lock對象，就是我們要找的鎖對象。

針對lock_t 類型的使用位置進行排查。經過篩選和調試，發(fā)現函數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上鎖時，對分區(qū)表數目相同的行數進行上鎖。這個是根本原因。

 

驗證結論

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

新增如下兩條記錄：

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;

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

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

文章題目：MySQL5.7分區(qū)表性能下降的原因是什么
文章起源：http://muchs.cn/article8/gdijip.html

成都網站建設公司_創(chuàng)新互聯，為您提供Google、搜索引擎優(yōu)化、電子商務、網站改版、響應式網站、品牌網站制作

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯