一 背景

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

二 場景分析

表結(jié)構(gòu):

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：數(shù)據(jù)有效、1：數(shù)據(jù)邏輯刪除’, `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 ，先檢查了表結(jié)構(gòu) 和索引 user_id 是數(shù)值類型的，且索引ok 然后手工執(zhí)行計劃竟然沒有走idx_userid_groupid索引，

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

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

那么為啥當(dāng)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í)行計劃并對比，結(jié)果顯示

看到結(jié)果我表示

翻閱 https://bugs.mysql.com 還沒找到相關(guān)結(jié)果。

代碼里面如何產(chǎn)生不同類型的值？

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

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

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

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

where name = 123

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

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

a 表 order_no 是utf8mb4 ,b 表order_no 是 utf8

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

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