一 背景

有贊的每個OLTP數據庫實例上會設置一個sql-killer進程用于kill 掉執(zhí)行時間超過一定閾值的sql。下午開發(fā)接收到sql被kill的報錯，一起幫助開發(fā)排查，本文介紹該案例。

二 場景分析

表結構:

CREATE TABLE `xxx_info` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ’id’, `user_id` bigint(20) unsigned NOT NULL DEFAULT ’0’ , `group_id` bigint(20) unsigned NOT NULL DEFAULT ’0’, `nick_name` varchar(30) NOT NULL DEFAULT ’’ COMMENT ’昵稱’, `is_del` tinyint(5) NOT NULL DEFAULT ’0’ COMMENT ’0：數據有效、1：數據邏輯刪除’, `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ’創(chuàng)建時間’, `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ’修改時間’, PRIMARY KEY (`id`), KEY `idx_userid_groupid` (`user_id`,`group_id`)) ENGINE=InnoDB AUTO_INCREMENT=1382032 DEFAULT CHARSET=utf8mb4 ;

問題sql如下

SELECT id, name,status FROM xxx_info WHERE user_id IN (670039223,’373149878’) AND group_id = 1 AND is_del = 0;

第一眼看到sql ，先檢查了表結構 和索引 user_id 是數值類型的，且索引ok 然后手工執(zhí)行計劃竟然沒有走idx_userid_groupid索引，

懷疑 user_id in 兩種不同類型的字段導致'隱式轉換',將 其中參數值都換為數值類型或者字符串 或者使用 user_id=數值類型 or user_id=字符串，再次執(zhí)行

執(zhí)行計劃都是正確。對此我們要解決兩個問題

那么為啥當user_id in (X,Y,Z) 是不同類型時，就不走索引了呢？

我們使用optimizer_trace 來跟蹤執(zhí)行計劃。

set session optimizer_trace=’enabled=on’;SELECT id, nick_name,is_del FROM xxx_info WHERE user_id IN (670039223,’373149878’) AND group_id = 1 AND is_del = 0;select * from information_schema.optimizer_trace;SELECT id, nick_name,is_del FROM xxx_info WHERE user_id IN (670039223,’373149878’) AND group_id = 1 AND is_del = 0;select * from information_schema.optimizer_trace;set session optimizer_trace=’enabled=off’;

獲取兩個sql的執(zhí)行計劃并對比，結果顯示

看到結果我表示

翻閱 https://bugs.mysql.com 還沒找到相關結果。

代碼里面如何產生不同類型的值？

以下是開發(fā)(阿杜)自己的測試

目前的解決方式是和開發(fā)同學溝通讓他們在程序做參數類型一致性校驗，都轉換為 int/long 類型。

特別提醒常見發(fā)生隱式轉換導致索引失效的場景

1 where 判斷符號左邊是字符串 ，右邊是數值 比如

where name = 123

2 多表join關聯(lián)條件的字段類型不一致，類似于 1

3 多表join關聯(lián)條件字符集類型不一樣。比如

a 表 order_no 是utf8mb4 ,b 表order_no 是 utf8

感興趣的 朋友可以多測試，有其他案例的 歡迎討論。

以上就是記一次MySQL的優(yōu)化案例的詳細內容，更多關于MySQL優(yōu)化案例的資料請關注好吧啦網其它相關文章！