前言

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

這四條 SQL 都是有針對(duì)性寫(xiě)的，12 查詢(xún)的字段是 int 類(lèi)型，34 查詢(xún)的字段是varchar類(lèi)型。12 或 34 查詢(xún)的字段雖然都相同，但是一個(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ì)觀(guān)察你會(huì)發(fā)現(xiàn)，34 兩條 SQL 查詢(xún)的字段num2是varchar類(lèi)型的，查詢(xún)條件等號(hào)右邊加引號(hào)的第 4 條 SQL 是用到索引的，那么是查詢(xún)的數(shù)據(jù)類(lèi)型和字段數(shù)據(jù)類(lèi)型不一致造成的嗎？如果是這樣那 12 兩條 SQL 查詢(xún)的字段num1是int類(lèi)型，但是第 2 條 SQL 查詢(xún)條件右邊加了引號(hào)為什么還能用上索引呢。

查閱 MySQL 相關(guān)文檔發(fā)現(xiàn)是隱式轉(zhuǎn)換造成的，看一下官方的描述：

官方文檔：12.2 Type Conversion in Expression Evaluation

當(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)換方式：

1. 兩個(gè)參數(shù)至少有一個(gè)是NULL時(shí)，比較的結(jié)果也是NULL，特殊的情況是使用<=>對(duì)兩個(gè)NULL做比較時(shí)會(huì)返回1，這兩種情況都不需要做類(lèi)型轉(zhuǎn)換
2. 兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來(lái)比較，不做類(lèi)型轉(zhuǎn)換
3. 兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來(lái)比較，不做類(lèi)型轉(zhuǎn)換
4. 十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制串
5. 有一個(gè)參數(shù)是TIMESTAMP或DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為timestamp
6. 有一個(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)行比較
7. 所有其他情況下，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較

根據(jù)官方文檔的描述，我們的第 23 兩條 SQL 都發(fā)生了隱式轉(zhuǎn)換，第 2 條 SQL 的查詢(xún)條件num1 = '10000'，左邊是int類(lèi)型右邊是字符串，第 3 條 SQL 相反，那么根據(jù)官方轉(zhuǎn)換規(guī)則第 7 條，左右兩邊都會(huì)轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較。

先看第 2 條 SQL：SELECT * FROM `test1` WHERE 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 * FROM `test1` WHERE 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ò)查詢(xún)測(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 * FROM `test1` WHERE num2 = 10000;進(jìn)行查詢(xún)：

從結(jié)果可以看到，后面 插入的三條數(shù)據(jù)也都匹配上了。那么這個(gè)字符串隱式轉(zhuǎn)換的規(guī)則是什么呢？為什么num2='10000a'、'010000'和'10000'這三種情形都能匹配上呢？查閱相關(guān)資料發(fā)現(xiàn)規(guī)則如下：

1. 不以數(shù)字開(kāi)頭的字符串都將轉(zhuǎn)換為0。如'abc'、'a123bc'、'abc123'都會(huì)轉(zhuǎn)化為0；
2. 以數(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)證：

如此也就印證了之前的查詢(xún)結(jié)果了。

再次寫(xiě)一條 SQL 查詢(xún) str1 字段：SELECT * FROM `test1` WHERE str1 = 1234;

分析和總結(jié)

通過(guò)上面的測(cè)試我們發(fā)現(xiàn) MySQL 使用操作符的一些特性：

1. 當(dāng)操作符左右兩邊的數(shù)據(jù)類(lèi)型不一致時(shí)，會(huì)發(fā)生隱式轉(zhuǎn)換。
2. 當(dāng) where 查詢(xún)操作符左邊為數(shù)值類(lèi)型時(shí)發(fā)生了隱式轉(zhuǎn)換，那么對(duì)效率影響不大，但還是不推薦這么做。
3. 當(dāng) where 查詢(xún)操作符左邊為字符類(lèi)型時(shí)發(fā)生了隱式轉(zhuǎn)換，那么會(huì)導(dǎo)致索引失效，造成全表掃描效率極低。
4. 字符串轉(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í)慣，查詢(xún)的字段是什么類(lèi)型，等號(hào)右邊的條件就寫(xiě)成對(duì)應(yīng)的類(lèi)型。特別當(dāng)查詢(xún)的字段是字符串時(shí)，等號(hào)右邊的條件一定要用引號(hào)引起來(lái)標(biāo)明這是一個(gè)字符串，否則會(huì)造成索引失效觸發(fā)全表掃描。 

以上就是MySQL隱式轉(zhuǎn)換造成索引失效的解決辦法的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引失效的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論