測試環境治理之MYSQL索引優化篇

測試環境這個話題對於開發和測試同學一定不陌生，大家幾乎每天都會接觸。但是說到對測試環境的印象，卻鮮有好評，下麵就主要給大家分享一次實際工作中的Mysql性能優化實踐，與大家共勉~ ...

作者：京東物流李光新

1 治理背景

測試環境這個話題對於開發和測試同學一定不陌生，大家幾乎每天都會接觸。但是說到對測試環境的印象，卻鮮有好評:

•環境不穩定，測試五分鐘，排查兩小時

•基礎建設不全，導致驗證不充分，遺漏缺陷

•多人共用，節點堵塞

這些問題在行業內其實屢見不鮮，針對測試環境的治理，不得不引起我們的重視。

首先我們要清晰的認知到，測試環境管理做的不好，不光有嚴重的質量風險，還會非常影響迭代效率，所以這件事情很重要。那在解決它之前，我們首先要去想想，對於測試環境我們到底有哪些訴求？

很明顯，測試環境的定位就是滿足產研測的測試需求，保障產品迭代質量。所以從使用類型上，一般要支撐集成測試，系統測試，甚至故障測試等。

而這些環境背後，其實都伴隨著非功能性要求 ，重點體現在:

1.從使用者角度

•想用就有，不要等待

•要低維護，高穩定

1.從企業角度

•低成本，高效率

簡單總結一下，理想的測試環境應該是：自由連接、隨時可用、互訪可控。

那麼現實中的測試環境又是怎樣的呢？所謂“理想很豐滿，現實很骨感”，對於一線測試工程師可能會發現，真實的測試環境並非這麼理想。

測試同學算是測試環境的主要使用者，對測試環境的管理理應負有直接責任。不過現實中，經常看到的是，測試同學因本身測試任務較多，且測試環境管理也要求具備一定的系統運維能力，導致相對而言，測試同學要想做好測試環境管理，也不容易~

下麵就主要給大家分享一次實際工作中的Mysql性能優化實踐，與大家共勉~

問題點：物流中台運單waybill.etms應用，由於包裹表未使用索引，導致的cpu飈高問題

2 分析過程

1.不管是在日常自動化測試還是功能測試過程中，經常會遇到資料庫數據落庫比較慢的場景，不僅影響功能測試進度，還會影響自動化的執行時長和成功率，在此背景下，展開如下排查工作~

2.查詢兩個異常運單，發現數據落庫在十分鐘以上，展開分析，

3.發現都是查詢delivery_package_d拋出異常，懷疑是不是共性問題；

ybill_log.log:2022-03-17 14:42:03 ERROR com.jd.etms.waybill.worker.business.WaybillCreateFromBusiLogic handling:65 - Bus運單JDVE00001018005接貨平臺下發處理異常
waybill_log.log-org.springframework.jdbc.UncategorizedSQLException: 
waybill_log.log-### Error querying database. Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
waybill_log.log-### The error may exist in mybatis/mysql/DeliveryPackageDDao.xml
waybill_log.log-### The error may involve defaultParameterMap
waybill_log.log-### The error occurred while setting parameters
waybill_log.log-### SQL: select   package_id,package_barcode,waybill_code,vendor_barcode,good_weigth,good_volume,remark,  create_time,update_time,yn,again_weight,weigh_User_Name,weigh_Time,pack_time,again_weight_volume,package_state,data_version,flag,expected_delivered_time,packwk_no,store_id,cky2   from delivery_package_d  where waybill_code=? and yn=1
waybill_log.log-### Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
waybill_log.log-; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Statement cancelled due to timeout or client request; nested exception is com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
waybill_log.log-    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) ~[spring-jdbc-3.2.18.RELEASE.jar:3.2.18.RELEASE]

4.直接搜異常日誌關鍵字，“接貨平臺下發處理異常”，確認推測正確；

5.排查異常sql:

waybill_log.log-### SQL: select   package_id,package_barcode,waybill_code,vendor_barcode,good_weigth,good_volume,remark,  create_time,update_time,yn,again_weight,weigh_User_Name,weigh_Time,pack_time,again_weight_volume,package_state,data_version,flag,expected_delivered_time,packwk_no,store_id,cky2   from delivery_package_d  where waybill_code=? and yn=1
waybill_log.log-### Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

從上面sql中可以定位到，是查詢表delivery_package_d時出現了問題，而且是執行超時，不是連接超時，所以可以排除是連接的問題，與研發溝通，懷疑是索引的問題；

6.然後排查資料庫索引：

運單資料庫是分庫分表的，上述包裹表delivery_package_d有兩個表比另外兩個表，少了兩個索引，定位異常問題，然後添加索引；

ALTER TABLE waybill_0.`delivery_package_d_0` ADD INDEX `idx_package` USING BTREE(`PACKAGE_BARCODE`);
ALTER TABLE waybill_0.`delivery_package_d_0` ADD INDEX `idx_waybill_code` USING BTREE(`WAYBILL_CODE`);
ALTER TABLE waybill_0.`delivery_package_d_0` ADD INDEX `idx_waybill_code_package` USING BTREE(`WAYBILL_CODE`, `PACKAGE_BARCODE`);
ALTER TABLE waybill_0.`delivery_package_d_0` ADD PRIMARY KEY USING BTREE(`PACKAGE_ID`, `CREATE_TIME`);
ALTER TABLE waybill_0.`delivery_package_d_1` ADD INDEX `idx_waybill_code` USING BTREE(`WAYBILL_CODE`);
ALTER TABLE waybill_0.`delivery_package_d_1` ADD INDEX `idx_waybill_code_package` USING BTREE(`WAYBILL_CODE`, `PACKAGE_BARCODE`);
ALTER TABLE waybill_0.`delivery_package_d_1` ADD PRIMARY KEY USING BTREE(`PACKAGE_ID`, `CREATE_TIME`);

2.查看資料庫伺服器性能，執行前後性能對比

添加索引後，自動化執行速度和成功率也有顯著提升。

3 擴展分析

正常情況下，慢sql日誌是存儲在伺服器上的，但是也可以通過mysql設置來通過資料庫查看慢sql。

慢日誌全稱為慢查詢日誌（Slow Query Log），主要用來記錄在 MySQL 中執行時間超過指定時間的 SQL 語句。通過慢查詢日誌，可以查找出哪些語句的執行效率低，以便進行優化。

預設情況下，MySQL 並沒有開啟慢日誌，可以通過修改 slow_query_log 參數來打開慢日誌。與慢日誌相關的參數介紹如下：

•slow_query_log：是否啟用慢查詢日誌，預設為0，可設置為0、1，1表示開啟。

•slow_query_log_file：指定慢查詢日誌位置及名稱，預設值為host_name-slow.log，可指定絕對路徑。

•long_query_time：慢查詢執行時間閾值，超過此時間會記錄，預設為10，單位為s。

•log_output：慢查詢日誌輸出目標，預設為file，即輸出到文件。

•log_timestamps：主要是控制 error log、slow log、genera log 日誌文件中的顯示時區，預設使用UTC時區，建議改為 SYSTEM 系統時區。

•log_queries_not_using_indexes：是否記錄所有未使用索引的查詢語句，預設為off。

•min_examined_row_limit：對於查詢掃描行數小於此參數的SQL，將不會記錄到慢查詢日誌中，預設為0。

•log_slow_admin_statements：慢速管理語句是否寫入慢日誌中，管理語句包含 alter table、create index 等，預設為 off 即不寫入。

可以先來自定義慢sql時長，也就是語句執行超過多長時間會被定義為慢sql；

show variables like 'long_query_time' //慢sql查詢閾值設置

然後，開啟是否記錄所有未使用索引的查詢語句開關log_queries_not_using_indexes，預設為off；

SHOW variables LIKE 'log_queries_not_using_indexes' //查詢未開啟索引的開關；
set global log_queries_not_using_indexes = on //開啟索引監控開關；

上述開關開啟之後，開始分析異常日誌；

show variables like 'log_output' - log_output 預設值是FILE，是輸出在伺服器上的；
set global log_output = 'TABLE' - 設置為TABLE，可以直接從資料庫查到；

通過資料庫查詢慢sql:

select *from mysql.slow_log - 慢日誌查詢結果；

從上圖中可以很明顯的看出具體的慢sql涉及的表，及查詢時長，後面就可以針對具體的表進行針對性的優化了~

當然，在實際環境下，不建議開啟 log_queries_not_using_indexes 參數，此參數打開後可能導致慢日誌迅速增長。

所以，針對上述分析過程，各位操作完成後，可以再關閉慢日誌輸出到資料庫，之後有分析需求再開啟，這樣就會有效減少對資料庫的壓力。

4 總結

綜上，我們每個人不僅僅是測試環境的使用者，更是測試環境的建設者，每個人都需要有意識的把負責的服務測試環境穩定性提升上來，這樣整體業務的測試環境穩定性才能有保障。

而且，對於測試環境管理和維護這條路，其實是隨著解決的問題深入，需要有很深入的思考和解決問題能力，隨之，對技術的要求也越來越高，當然，這也正是我們的價值所在。

以上，與君共勉~