mysql学习笔记-索引失效

索引失效问题

测试用例

数据准备

通过存储过程生成1000w条数据，其中num1位int类型，num2位varchar类型。

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;
-- 创建存储过程
DROP PROCEDURE IF EXISTS pre_test1;
CREATE PROCEDURE `pre_test1`()
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    WHILE i < 50000000 DO
        SET i = i + 1;
        SET @str1 = SUBSTRING(MD5(RAND()),1,20);
        -- 每100条数据str2产生一个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);
        -- 事务优化，每一万条数据提交一次事务
        IF i % 10000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
END;
-- 执行存储过程
CALL pre_test1();

执行如下sql统计查询时间

SELECT * FROM `test1` WHERE num1 = 10000;
SELECT * FROM `test1` WHERE num1 = '10000';
SELECT * FROM `test1` WHERE num2 = 10000;
SELECT * FROM `test1` WHERE num2 = '10000';

总数据量1000w，其中1，2，4 查询时间在0.03s相差无几，第三条查询时间在12s。可以明显看出索引此处未生效，进一步查看执行计划，进行分析。

查看执行计划

explain SELECT * FROM `test1` WHERE num1 = 10000;
explain  SELECT * FROM `test1` WHERE num1 = '10000';
explain  SELECT * FROM `test1` WHERE num2 = 10000;
explain  SELECT * FROM `test1` WHERE num2 = '10000';

可以看到，124 三条 SQL 都能使用到索引，连接类型都为ref，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，rows直接到达 1000 万了，所以性能差别才那么大

问题分析

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容，某些转换是隐式发生，例如，mysql会根据需要自动将字符串转换成数字。
根据规则，第2，3条都发生了隐式转换，第2条查询num1=‘1000’,左边是int，右边是字符串，第三条相反，左右两边都会转换为浮点数比较。

补充隐式转换规则

1.两个参数都是字符串，会按照字符串来比较，不做类型转换。
2.两个参数都是整数，按照整数来比较 ，不做类型转换。
3.十六进制的数和非数字做比较时，会被当作二进制串。
4.有一个参数是Timstamp或DateTime，另一个参数是常量，会被转换为timeStamp
5.有一个参数是decimal，另一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较。
6.其他情况下，两个参数会被转换为浮点数进行比较。

其中第2条，SELECT * FROM test1 WHERE num1 = ‘10000’; 左边int类型，转换为浮点数10000，右边字符串类型，转换为浮点数也是10000，两边转换结果都是唯一的，所以不影响使用索引。

第三条： SELECT * FROM test1 WHERE num2 = 10000; 左边字符串‘10000’，转浮点数10000，是唯一的，右边int类型转换为10000也是唯一的，但是因为左边是检索条件，‘10000’转到10000 虽然是唯一的，但是其他字符串也可以转换为10000，例如 ‘10000a’，‘010000’，‘10000’ 这样的情况下是不能用索引的。

具体的字符串转换为浮点数规则如下：

1. 不以数字开头的字符串都将转换为0，如‘abc’,'abc123’都会转化为0 ，
   2.以数字开头的字符串转换时会进行截取，从第一个字符串截取到第一个非数字内容为止。

综上总结

1. 当操作符左右两边数据不一致时，会发生隐式转换
2. 当where查询操作符左边为数值类型时，隐式转换转换对效率影响不大。
3. 当where查询操作符左边为字符类型时，发生隐式转换造成索引失效，以及查询结果不匹配。
4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

造成索引失效总结

1. 对索引采用左或者左右模糊匹配时索引失效，如like '%xxx’或者like‘%xxx%’
2. 对索引使用函数 如 select * from test where lenght(num2)=10;
3. 对索引进行表达式计算：如select * from test where num1+10 = 100;
4. 对索引隐式类型转换。
5. sql语句中写了or，如果or后的字段不全是带索引的字段，索引无效
6. 数据量极少时，不会使用索引。