快捷導(dǎo)航

記一次MySQL的優(yōu)化案例

更新時(shí)間：2020年10月27日 14:41:48 作者：yangyidba

這篇文章主要介紹了記一次MySQL的優(yōu)化案例，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下

一背景

有贊的每個(gè)OLTP數(shù)據(jù)庫實(shí)例上會(huì)設(shè)置一個(gè)sql-killer進(jìn)程用于kill 掉執(zhí)行時(shí)間超過一定閾值的sql。下午開發(fā)接收到sql被kill的報(bào)錯(cuò)，一起幫助開發(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)建時(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 ;

問題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ì)劃竟然沒有走idx_userid_groupid索引，

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

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

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

我們使用optimizer_trace 來跟蹤執(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é)果顯示