前言

數(shù)據(jù)庫優(yōu)化是一個任重而道遠(yuǎn)的任務(wù)，想要做優(yōu)化必須深入理解數(shù)據(jù)庫的各種特性。在開發(fā)過程中我們經(jīng)常會遇到一些原因很簡單但造成的后果卻很嚴(yán)重的疑難雜癥，這類問題往往還不容易定位，排查費(fèi)時費(fèi)力最后發(fā)現(xiàn)是一個很小的疏忽造成的，又或者是因為不了解某個技術(shù)特性產(chǎn)生的。

于數(shù)據(jù)庫層面，最常見的恐怕就是索引失效了，且一開始因為數(shù)據(jù)量小還不易被發(fā)現(xiàn)。但隨著業(yè)務(wù)的拓展數(shù)據(jù)量的提升，性能問題慢慢的就體現(xiàn)出來了，處理不及時還很容易造成雪球效應(yīng)，最終導(dǎo)致數(shù)據(jù)庫卡死甚至癱瘓。造成索引失效的原因可能有很多種，相關(guān)技術(shù)博客已經(jīng)有太多了，今天我要記錄的是隱式轉(zhuǎn)換造成的索引失效。

數(shù)據(jù)準(zhǔn)備

首先使用存儲過程生成 1000 萬條測試數(shù)據(jù)，測試表一共建立了 7 個字段（包括主鍵），num1和num2保存的是和ID一樣的順序數(shù)字，其中num2是字符串類型。

type1和type2保存的都是主鍵對 5 的取模，目的是模擬實際應(yīng)用中常用類似 type 類型的數(shù)據(jù)，但是type2是沒有建立索引的。

str1和str2都是保存了一個 20 位長度的隨機(jī)字符串，str1不能為NULL，str2允許為NULL，相應(yīng)的生成測試數(shù)據(jù)的時候我也會在str2字段生產(chǎn)少量NULL值（每 100 條數(shù)據(jù)產(chǎn)生一個NULL值）。

-- 創(chuàng)建測試數(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)建存儲過程
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)生一個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)化，每一萬條數(shù)據(jù)提交一次事務(wù)
        IF i % 10000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
END;
// DELIMITER ;
-- 執(zhí)行存儲過程
CALL pre_test1();

數(shù)據(jù)量比較大，還涉及使用MD5生成隨機(jī)字符串，所以速度有點(diǎn)慢，稍安勿躁，耐心等待即可。

1000 萬條數(shù)據(jù)，我用了 33 分鐘才跑完（實際時間跟你電腦硬件配置有關(guān)）。這里貼幾條生成的數(shù)據(jù)，大致長這樣。

SQL測試

先來看這組 SQL，一共四條，我們的測試數(shù)據(jù)表num1是int類型，num2是varchar類型，但是存儲的數(shù)據(jù)都是跟主鍵id一樣的順序數(shù)字，兩個字段都建立有索引。

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 都是有針對性寫的，12 查詢的字段是 int 類型，34 查詢的字段是varchar類型。12 或 34 查詢的字段雖然都相同，但是一個條件是數(shù)字，一個條件是用引號引起來的字符串。這樣做有什么區(qū)別呢？先不看下邊的測試結(jié)果你能猜出這四條 SQL 的效率順序嗎？

經(jīng)測試這四條 SQL 最后的執(zhí)行結(jié)果卻相差很大，其中 124 三條 SQL 基本都是瞬間出結(jié)果，大概在 0.001~0.005 秒，在千萬級的數(shù)據(jù)量下這樣的結(jié)果可以判定這三條 SQL 性能基本沒差別了。但是第三條 SQL，多次測試耗時基本在 4.5~4.8 秒之間。

為什么 34 兩條 SQL 效率相差那么大，但是同樣做對比的 12 兩條 SQL 卻沒什么差別呢？查看一下執(zhí)行計劃，下邊分別 1234 條 SQL 的執(zhí)行計劃數(shù)據(jù)：

可以看到，124 三條 SQL 都能使用到索引，連接類型都為ref，掃描行數(shù)都為 1，所以效率非常高。再看看第三條 SQL，沒有用上索引，所以為全表掃描，rows直接到達(dá) 1000 萬了，所以性能差別才那么大。

仔細(xì)觀察你會發(fā)現(xiàn)，34 兩條 SQL 查詢的字段num2是varchar類型的，查詢條件等號右邊加引號的第 4 條 SQL 是用到索引的，那么是查詢的數(shù)據(jù)類型和字段數(shù)據(jù)類型不一致造成的嗎？如果是這樣那 12 兩條 SQL 查詢的字段num1是int類型，但是第 2 條 SQL 查詢條件右邊加了引號為什么還能用上索引呢。

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

官方文檔：12.2 Type Conversion in Expression Evaluation

當(dāng)操作符與不同類型的操作數(shù)一起使用時，會發(fā)生類型轉(zhuǎn)換以使操作數(shù)兼容。某些轉(zhuǎn)換是隱式發(fā)生的。例如，MySQL 會根據(jù)需要自動將字符串轉(zhuǎn)換為數(shù)字，反之亦然。以下規(guī)則描述了比較操作的轉(zhuǎn)換方式：

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

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

先看第 2 條 SQL：SELECT * FROM `test1` WHERE num1 = '10000'; 左邊為 int 類型10000，轉(zhuǎn)換為浮點(diǎn)數(shù)還是10000，右邊字符串類型'10000'，轉(zhuǎn)換為浮點(diǎn)數(shù)也是10000。兩邊的轉(zhuǎn)換結(jié)果都是唯一確定的，所以不影響使用索引。

第 3 條 SQL：SELECT * FROM `test1` WHERE num2 = 10000; 左邊是字符串類型'10000'，轉(zhuǎn)浮點(diǎn)數(shù)為 10000 是唯一的，右邊int類型10000轉(zhuǎn)換結(jié)果也是唯一的。但是，因為左邊是檢索條件，'10000'轉(zhuǎn)到10000雖然是唯一，但是其他字符串也可以轉(zhuǎn)換為10000，比如'10000a'，'010000'，'10000'等等都能轉(zhuǎn)為浮點(diǎn)數(shù)10000，這樣的情況下，是不能用到索引的。

關(guān)于這個隱式轉(zhuǎ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 語句SELECT * FROM `test1` WHERE num2 = 10000;進(jìn)行查詢：

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

1. 不以數(shù)字開頭的字符串都將轉(zhuǎn)換為0。如'abc'、'a123bc'、'abc123'都會轉(zhuǎn)化為0；
2. 以數(shù)字開頭的字符串轉(zhuǎn)換時會進(jìn)行截取，從第一個字符截取到第一個非數(shù)字內(nèi)容為止。比如'123abc'會轉(zhuǎn)換為123，'012abc'會轉(zhuǎn)換為012也就是12，'5.3a66b78c'會轉(zhuǎn)換為5.3，其他同理。

現(xiàn)對以上規(guī)則做如下測試驗證：

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

再次寫一條 SQL 查詢 str1 字段：SELECT * FROM `test1` WHERE str1 = 1234;

分析和總結(jié)

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

1. 當(dāng)操作符左右兩邊的數(shù)據(jù)類型不一致時，會發(fā)生隱式轉(zhuǎn)換。
2. 當(dāng) where 查詢操作符左邊為數(shù)值類型時發(fā)生了隱式轉(zhuǎn)換，那么對效率影響不大，但還是不推薦這么做。
3. 當(dāng) where 查詢操作符左邊為字符類型時發(fā)生了隱式轉(zhuǎn)換，那么會導(dǎo)致索引失效，造成全表掃描效率極低。
4. 字符串轉(zhuǎn)換為數(shù)值類型時，非數(shù)字開頭的字符串會轉(zhuǎn)化為0，以數(shù)字開頭的字符串會截取從第一個字符到第一個非數(shù)字內(nèi)容為止的值為轉(zhuǎn)化結(jié)果。

所以，我們在寫 SQL 時一定要養(yǎng)成良好的習(xí)慣，查詢的字段是什么類型，等號右邊的條件就寫成對應(yīng)的類型。特別當(dāng)查詢的字段是字符串時，等號右邊的條件一定要用引號引起來標(biāo)明這是一個字符串，否則會造成索引失效觸發(fā)全表掃描。 

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

最新評論