一  背景

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

二 場(chǎng)景分析

表結(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)建時(shí)間',

  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改時(shí)間',

  PRIMARY KEY (`id`),

  KEY `idx_userid_groupid` (`user_id`,`group_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1382032 DEFAULT CHARSET=utf8mb4 ;

問(wèn)題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í)行計(jì)劃竟然沒(méi)有走idx_userid_groupid索引，

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

執(zhí)行計(jì)劃都是正確。對(duì)此我們要解決兩個(gè)問(wèn)題 

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

我們使用optimizer_trace 來(lái)跟蹤執(zhí)行計(jì)劃。

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';

獲取兩個(gè)sql的執(zhí)行計(jì)劃并對(duì)比，結(jié)果顯示 

看到結(jié)果我表示

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

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

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

目前的解決方式是和開(kāi)發(fā)同學(xué)溝通讓他們?cè)诔绦蜃鰠?shù)類型一致性校驗(yàn)，都轉(zhuǎn)換為 int/long 類型。

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

1  where 判斷符號(hào)左邊是字符串 ，右邊是數(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 

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

以上就是記一次MySQL的優(yōu)化案例的詳細(xì)內(nèi)容，更多關(guān)于MySQL優(yōu)化案例的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！