一 背景
有贊的每個OLTP數(shù)據(jù)庫實例上會設置一個sql-killer進程用于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 兩種不同類型的字段導致"隱式轉(zhuǎn)換",將 其中參數(shù)值都換為數(shù)值類型或者字符串 或者使用 user_id=數(shù)值類型 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í)行計劃并對比，結(jié)果顯示
看到結(jié)果我表示
翻閱 https://bugs.mysql.com 還沒找到相關結(jié)果。
代碼里面如何產(chǎn)生不同類型的值？
以下是開發(fā)(阿杜)自己的測試
目前的解決方式是和開發(fā)同學溝通讓他們在程序做參數(shù)類型一致性校驗，都轉(zhuǎn)換為 int/long 類型。
特別提醒常見發(fā)生隱式轉(zhuǎn)換導致索引失效的場景
1 where 判斷符號左邊是字符串 ，右邊是數(shù)值 比如
where name = 123
2 多表join關聯(lián)條件的字段類型不一致，類似于 1
3 多表join關聯(lián)條件字符集類型不一樣。比如
a 表 order_no 是utf8mb4 ,b 表order_no 是 utf8
感興趣的 朋友可以多測試，有其他案例的 歡迎討論。
以上就是記一次MySQL的優(yōu)化案例的詳細內(nèi)容，更多關于MySQL優(yōu)化案例的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- 淺談MySQL大表優(yōu)化方案
- MySQL group by語句如何優(yōu)化
- 解決mysql 組合AND和OR帶來的問題
