干货！SQL性能优化，书写高质量SQL语句（二）

2020-01-15 17:44 3557

本片博文承接上一篇《干货！SQL性能优化，书写高质量SQL语句》。这里非常感谢大家，很给面子，评论区给了很多意见和指导！上篇文章出现太多的理论知识，并没有用具体的测试用例说服大家，那么本片干货分享主要针对上篇掘友们提出的疑问点进行回答，提供详细的测试用例。如有不足还请赐教~

数据库版本 mysql 5.7.26

1. where语句使用 IN 到底走不走索引？

首先先创建一张测试表

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `page` bigint(20) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT '状态：0 正常，1 冻结，2 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

批量新增100w条数据

DELIMITER ;;
    CREATE PROCEDURE insertData()
    BEGIN
        declare i int;
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO student(`name`,class,`page`,`status`)
                VALUES(CONCAT('class_', i),
                    CONCAT('class_', i),
                i, (SELECT FLOOR(RAND() * 2)));
            set i = i + 1;
        END WHILE;
        commit;
		END;;
CALL insertData();

name 字段不加索引，进行测试, 耗时 0.8s

SELECT * FROM student WHERE `name` IN
('class_1','class_100','class_1000','class_100000');

name 字段加索引后,测试耗时 0.021s

那么我们发现where 语句使用 IN 是走索引的

但是！如果数据量过大(百万级别)，例如估计为全表80%，那么是不会走索引的，会走全表扫描！

再查一下 student 表中的 status 字段 in 的情况

SELECT * FROM student WHERE `status` IN(0,2);

加索引后,因为数据量大,并未走索引，如图所示：

总结

翻阅大量资料进行测试，如果是mysql5.5之前的版本，确实是不会走索引的，在5.5及之后的版本中，MySql做了优化，在2010年发布的5.5版本中,优化器对 IN 操作符可以自动完成优化，针对建立了索引的列可以使用索引，没有索引的列还是会走全表扫描，但是如果数据量大，例如估计为全表80%，会走全表扫描！

2.where子句中使用 IS NULL 或 IS NOT NULL 到底走不走索引?

我们分三种情况来测试

索引列 name 默认值为 NULL 允许为空
索引列 name 默认值为 '1' 允许为空
索引列 name 默认值为 '1' 不允许为空

1.索引列 name 默认值为 NULL 允许为空

SELECT * FROM student WHERE `name` is not null

SELECT * FROM student WHERE `name` is null

分析: IS NULL 使用了索引 , IS NOT NULL 没有走索引

2.索引列 name 默认值为 '1' 允许为空

分析: IS NULL 使用了索引 , IS NOT NULL 没有走索引

3.索引列 name 默认值为 '1' 不允许为空

分析: IS NULL 没有走索引 , IS NOT NULL 也没有走索引

结论

is not null不管字段是否为空都不会走索引,is null在字段允许为空时会使用索引！