一 背景

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

二 場景分析

表結構:

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 ’創建時間’, `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 然后手工執行計劃竟然沒有走idx_userid_groupid索引，

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

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

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

我們使用optimizer_trace 來跟蹤執行計劃。

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的執行計劃并對比，結果顯示

看到結果我表示

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

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

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

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

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

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

where name = 123

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

3 多表join關聯條件字符集類型不一樣。比如

a 表 order_no 是utf8mb4 ,b 表order_no 是 utf8

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

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