本次測(cè)試使用的 MySQL 版本是
5.7.26，隨著 MySQL 版本的更新某些特性可能會(huì)發(fā)生改變慰毅，本文不代表所述觀點(diǎn)和結(jié)論于 MySQL 所有版本均準(zhǔn)確無(wú)誤，版本差異請(qǐng)自行甄別扎阶。
前言
數(shù)據(jù)庫(kù)優(yōu)化是一個(gè)任重而道遠(yuǎn)的任務(wù)汹胃，想要做優(yōu)化必須深入理解數(shù)據(jù)庫(kù)的各種特性。在開(kāi)發(fā)過(guò)程中我們經(jīng)常會(huì)遇到一些原因很簡(jiǎn)單但造成的后果卻很?chē)?yán)重的疑難雜癥东臀，這類(lèi)問(wèn)題往往還不容易定位着饥，排查費(fèi)時(shí)費(fèi)力最后發(fā)現(xiàn)是一個(gè)很小的疏忽造成的，又或者是因?yàn)椴涣私饽硞€(gè)技術(shù)特性產(chǎn)生的惰赋。
于數(shù)據(jù)庫(kù)層面宰掉，最常見(jiàn)的恐怕就是索引失效了，且一開(kāi)始因?yàn)閿?shù)據(jù)量小還不易被發(fā)現(xiàn)赁濒。但隨著業(yè)務(wù)的拓展數(shù)據(jù)量的提升轨奄，性能問(wèn)題慢慢的就體現(xiàn)出來(lái)了，處理不及時(shí)還很容易造成雪球效應(yīng)拒炎，最終導(dǎo)致數(shù)據(jù)庫(kù)卡死甚至癱瘓戚绕。造成索引失效的原因可能有很多種，相關(guān)技術(shù)博客已經(jīng)有太多了枝冀，今天我要記錄的是隱式轉(zhuǎn)換造成的索引失效舞丛。
數(shù)據(jù)準(zhǔn)備
首先使用存儲(chǔ)過(guò)程生成 1000 萬(wàn)條測(cè)試數(shù)據(jù)， 測(cè)試表一共建立了 7 個(gè)字段（包括主鍵）果漾，num1和num2保存的是和ID一樣的順序數(shù)字球切，其中num2是字符串類(lèi)型。 type1和type2保存的都是主鍵對(duì) 5 的取模绒障，目的是模擬實(shí)際應(yīng)用中常用類(lèi)似 type 類(lèi)型的數(shù)據(jù)吨凑，但是type2是沒(méi)有建立索引的。 str1和str2都是保存了一個(gè) 20 位長(zhǎng)度的隨機(jī)字符串户辱，str1不能為NULL鸵钝，str2允許為NULL，相應(yīng)的生成測(cè)試數(shù)據(jù)的時(shí)候我也會(huì)在str2字段生產(chǎn)少量NULL值（每 100 條數(shù)據(jù)產(chǎn)生一個(gè)NULL值）庐镐。
-- 創(chuàng)建測(cè)試數(shù)據(jù)表
DROP TABLE IF EXISTS test1;
CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`num1` int(11) NOT NULL DEFAULT '0',
`num2` varchar(11) NOT NULL DEFAULT '',
`type1` int(4) NOT NULL DEFAULT '0',
`type2` int(4) NOT NULL DEFAULT '0',
`str1` varchar(100) NOT NULL DEFAULT '',
`str2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `num1` (`num1`),
KEY `num2` (`num2`),
KEY `type1` (`type1`),
KEY `str1` (`str1`),
KEY `str2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 創(chuàng)建存儲(chǔ)過(guò)程
DROP PROCEDURE IF EXISTS pre_test1;
DELIMITER //
CREATE PROCEDURE `pre_test1`()
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
WHILE i < 10000000 DO
SET i = i + 1;
SET @str1 = SUBSTRING(MD5(RAND()),1,20);
-- 每100條數(shù)據(jù)str2產(chǎn)生一個(gè)null值
IF i % 100 = 0 THEN
SET @str2 = NULL;
ELSE
SET @str2 = @str1;
END IF;
INSERT INTO test1 (`id`, `num1`, `num2`,
`type1`, `type2`, `str1`, `str2`)
VALUES (CONCAT('', i), CONCAT('', i),
CONCAT('', i), i%5, i%5, @str1, @str2);
-- 事務(wù)優(yōu)化恩商，每一萬(wàn)條數(shù)據(jù)提交一次事務(wù)
IF i % 10000 = 0 THEN
COMMIT;
END IF;
END WHILE;
END;
// DELIMITER ;
-- 執(zhí)行存儲(chǔ)過(guò)程
CALL pre_test1();
數(shù)據(jù)量比較大，還涉及使用MD5生成隨機(jī)字符串必逆，所以速度有點(diǎn)慢怠堪，稍安勿躁揽乱，耐心等待即可。
1000 萬(wàn)條數(shù)據(jù)粟矿，我用了 33 分鐘才跑完（實(shí)際時(shí)間跟你電腦硬件配置有關(guān)）凰棉。這里貼幾條生成的數(shù)據(jù)，大致長(zhǎng)這樣陌粹。
SQL 測(cè)試
先來(lái)看這組 SQL撒犀，一共四條，我們的測(cè)試數(shù)據(jù)表num1是int類(lèi)型掏秩，num2是varchar類(lèi)型绘证，但是存儲(chǔ)的數(shù)據(jù)都是跟主鍵id一樣的順序數(shù)字，兩個(gè)字段都建立有索引哗讥。
1: SELECT * FROM `test1` WHERE num1 = 10000;
2: SELECT * FROM `test1` WHERE num1 = '10000';
3: SELECT * FROM `test1` WHERE num2 = 10000;
4: SELECT * FROM `test1` WHERE num2 = '10000';
1
2
3
4
這四條 SQL 都是有針對(duì)性寫(xiě)的，12 查詢的字段是 int 類(lèi)型胞枕，34 查詢的字段是varchar類(lèi)型杆煞。12 或 34 查詢的字段雖然都相同，但是一個(gè)條件是數(shù)字腐泻，一個(gè)條件是用引號(hào)引起來(lái)的字符串决乎。這樣做有什么區(qū)別呢？先不看下邊的測(cè)試結(jié)果你能猜出這四條 SQL 的效率順序嗎派桩？
經(jīng)測(cè)試這四條 SQL 最后的執(zhí)行結(jié)果卻相差很大构诚，其中 124 三條 SQL 基本都是瞬間出結(jié)果，大概在 0.001~0.005 秒铆惑，在千萬(wàn)級(jí)的數(shù)據(jù)量下這樣的結(jié)果可以判定這三條 SQL 性能基本沒(méi)差別了范嘱。但是第三條 SQL，多次測(cè)試耗時(shí)基本在 4.5~4.8 秒之間员魏。
為什么 34 兩條 SQL 效率相差那么大丑蛤，但是同樣做對(duì)比的 12 兩條 SQL 卻沒(méi)什么差別呢？查看一下執(zhí)行計(jì)劃撕阎，下邊分別 1234 條 SQL 的執(zhí)行計(jì)劃數(shù)據(jù)：
可以看到受裹，124 三條 SQL 都能使用到索引，連接類(lèi)型都為ref虏束，掃描行數(shù)都為 1棉饶，所以效率非常高。再看看第三條 SQL镇匀，沒(méi)有用上索引照藻，所以為全表掃描，rows直接到達(dá) 1000 萬(wàn)了汗侵，所以性能差別才那么大岩梳。
仔細(xì)觀察你會(huì)發(fā)現(xiàn)囊骤，34 兩條 SQL 查詢的字段num2是varchar類(lèi)型的，查詢條件等號(hào)右邊加引號(hào)的第 4 條 SQL 是用到索引的冀值，那么是查詢的數(shù)據(jù)類(lèi)型和字段數(shù)據(jù)類(lèi)型不一致造成的嗎也物？如果是這樣那 12 兩條 SQL 查詢的字段num1是int類(lèi)型，但是第 2 條 SQL 查詢條件右邊加了引號(hào)為什么還能用上索引呢列疗。
查閱 MySQL 相關(guān)文檔發(fā)現(xiàn)是隱式轉(zhuǎn)換造成的滑蚯，看一下官方的描述：
官方文檔： 12.2 Type Conversion in Expression Evaluationopen in new window
當(dāng)操作符與不同類(lèi)型的操作數(shù)一起使用時(shí)，會(huì)發(fā)生類(lèi)型轉(zhuǎn)換以使操作數(shù)兼容抵栈。某些轉(zhuǎn)換是隱式發(fā)生的告材。例如，MySQL 會(huì)根據(jù)需要自動(dòng)將字符串轉(zhuǎn)換為數(shù)字古劲，反之亦然斥赋。以下規(guī)則描述了比較操作的轉(zhuǎn)換方式：
- 兩個(gè)參數(shù)至少有一個(gè)是
NULL時(shí)，比較的結(jié)果也是NULL产艾，特殊的情況是使用<=>對(duì)兩個(gè)NULL做比較時(shí)會(huì)返回1疤剑，這兩種情況都不需要做類(lèi)型轉(zhuǎn)換- 兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來(lái)比較闷堡，不做類(lèi)型轉(zhuǎn)換
- 兩個(gè)參數(shù)都是整數(shù)隘膘，按照整數(shù)來(lái)比較，不做類(lèi)型轉(zhuǎn)換
- 十六進(jìn)制的值和非數(shù)字做比較時(shí)杠览，會(huì)被當(dāng)做二進(jìn)制串
- 有一個(gè)參數(shù)是
TIMESTAMP或DATETIME弯菊，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為timestamp- 有一個(gè)參數(shù)是
decimal類(lèi)型踱阿，如果另外一個(gè)參數(shù)是decimal或者整數(shù)管钳，會(huì)將整數(shù)轉(zhuǎn)換為decimal后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)软舌，則會(huì)把decimal轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較- 所有其他情況下蹋嵌，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較
根據(jù)官方文檔的描述，我們的第 23 兩條 SQL 都發(fā)生了隱式轉(zhuǎn)換葫隙，第 2 條 SQL 的查詢條件num1 = '10000'栽烂，左邊是int類(lèi)型右邊是字符串，第 3 條 SQL 相反恋脚，那么根據(jù)官方轉(zhuǎn)換規(guī)則第 7 條腺办，左右兩邊都會(huì)轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較。
先看第 2 條 SQL：SELECT * FROMtest1WHERE num1 = '10000'; 左邊為 int 類(lèi)型10000糟描，轉(zhuǎn)換為浮點(diǎn)數(shù)還是10000怀喉，右邊字符串類(lèi)型'10000'，轉(zhuǎn)換為浮點(diǎn)數(shù)也是10000船响。兩邊的轉(zhuǎn)換結(jié)果都是唯一確定的躬拢，所以不影響使用索引躲履。
第 3 條 SQL：SELECT * FROMtest1WHERE num2 = 10000; 左邊是字符串類(lèi)型'10000'，轉(zhuǎn)浮點(diǎn)數(shù)為 10000 是唯一的聊闯，右邊int類(lèi)型10000轉(zhuǎn)換結(jié)果也是唯一的工猜。但是，因?yàn)樽筮吺菣z索條件菱蔬，'10000'轉(zhuǎn)到10000雖然是唯一篷帅，但是其他字符串也可以轉(zhuǎn)換為10000，比如'10000a'拴泌，'010000'魏身，'10000'等等都能轉(zhuǎn)為浮點(diǎn)數(shù)10000，這樣的情況下蚪腐，是不能用到索引的箭昵。
關(guān)于這個(gè)隱式轉(zhuǎn)換我們可以通過(guò)查詢測(cè)試驗(yàn)證一下，先插入幾條數(shù)據(jù)回季，其中num2='10000a'家制、'010000'和'10000'：
INSERT INTO `test1` (`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000001', '10000', '10000a', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO `test1` (`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000002', '10000', '010000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO `test1` (`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000003', '10000', ' 10000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
然后使用第三條 SQL 語(yǔ)句SELECT * FROMtest1WHERE num2 = 10000;進(jìn)行查詢：
從結(jié)果可以看到，后面插入的三條數(shù)據(jù)也都匹配上了茧跋。那么這個(gè)字符串隱式轉(zhuǎn)換的規(guī)則是什么呢？為什么num2='10000a'卓囚、'010000'和'10000'這三種情形都能匹配上呢瘾杭？查閱相關(guān)資料發(fā)現(xiàn)規(guī)則如下：
-
不以數(shù)字開(kāi)頭的字符串都將轉(zhuǎn)換為
0。如'abc'哪亿、'a123bc'粥烁、'abc123'都會(huì)轉(zhuǎn)化為0； -
以數(shù)字開(kāi)頭的字符串轉(zhuǎn)換時(shí)會(huì)進(jìn)行截取蝇棉，從第一個(gè)字符截取到第一個(gè)非數(shù)字內(nèi)容為止讨阻。比如
'123abc'會(huì)轉(zhuǎn)換為123，'012abc'會(huì)轉(zhuǎn)換為012也就是12篡殷，'5.3a66b78c'會(huì)轉(zhuǎn)換為5.3钝吮，其他同理。
現(xiàn)對(duì)以上規(guī)則做如下測(cè)試驗(yàn)證：
如此也就印證了之前的查詢結(jié)果了板辽。
再次寫(xiě)一條 SQL 查詢 str1 字段：SELECT * FROMtest1WHERE str1 = 1234;
分析和總結(jié)
通過(guò)上面的測(cè)試我們發(fā)現(xiàn) MySQL 使用操作符的一些特性：
- 當(dāng)操作符左右兩邊的數(shù)據(jù)類(lèi)型不一致時(shí)奇瘦，會(huì)發(fā)生隱式轉(zhuǎn)換。
- 當(dāng) where 查詢操作符左邊為數(shù)值類(lèi)型時(shí)發(fā)生了隱式轉(zhuǎn)換劲弦，那么對(duì)效率影響不大耳标，但還是不推薦這么做。
- 當(dāng) where 查詢操作符左邊為字符類(lèi)型時(shí)發(fā)生了隱式轉(zhuǎn)換邑跪，那么會(huì)導(dǎo)致索引失效次坡，造成全表掃描效率極低呼猪。
- 字符串轉(zhuǎn)換為數(shù)值類(lèi)型時(shí)，非數(shù)字開(kāi)頭的字符串會(huì)轉(zhuǎn)化為
0砸琅，以數(shù)字開(kāi)頭的字符串會(huì)截取從第一個(gè)字符到第一個(gè)非數(shù)字內(nèi)容為止的值為轉(zhuǎn)化結(jié)果宋距。
所以，我們?cè)趯?xiě) SQL 時(shí)一定要養(yǎng)成良好的習(xí)慣明棍，查詢的字段是什么類(lèi)型乡革，等號(hào)右邊的條件就寫(xiě)成對(duì)應(yīng)的類(lèi)型。特別當(dāng)查詢的字段是字符串時(shí)摊腋，等號(hào)右邊的條件一定要用引號(hào)引起來(lái)標(biāo)明這是一個(gè)字符串沸版，否則會(huì)造成索引失效觸發(fā)全表掃描。
轉(zhuǎn)自：https://javaguide.cn/database/mysql/index-invalidation-caused-by-implicit-conversion.html
