mysql字段为NULL索引是否会失效_mysql null 索引-程序员宅基地

技术标签: mysql 数据库 索引  mysql  

项目场景:

很多博客说mysql在字段中创建普通索引,如果该索引中的数据存在null值是不走索引这个结论是错误的,不过尽量还是设置默认值。(版本8.0低于这个版本可能结果不一致)


1、创建表sc_base_color,其中普通索引为 “name,group_num”,这里暂时不测组合索引,下面再测试。


CREATE TABLE `sc_base_color` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_num` bigint DEFAULT NULL COMMENT '颜色代码',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '颜色名称',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`),
  KEY `idx_group_num` (`group_num`)
) ENGINE=InnoDB AUTO_INCREMENT=574 DEFAULT CHARSET=utf8mb3 COMMENT='颜色';

2、初始化测试数据

INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (30, 1, '米黄');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (31, 1, '黑色');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (32, 1, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (33, 1, '白色');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (34, 1, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (35, 1, '绿色');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (36, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (37, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (38, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (39, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (40, NULL, '紫色');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (41, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (42, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (43, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (44, NULL, '蓝色');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (45, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (46, NULL, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (47, 2, '米蓝色');
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (48, 2, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (49, 2, NULL);
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (50, 2, '黑红色');


3、测试普通索引为NULL的情况是否使用了索引

  • 使用 = 查询,测试结果中使用到了索引,其中索引字段的值为“NULL”

    EXPLAIN select * from sc_base_color where name = '米黄';
    EXPLAIN select * from sc_base_color where group_num = 1;
    

    截图结果,两列数据都存在空,最终走了索引。
    在这里插入图片描述

  • 使用 大于、小于 查询

    EXPLAIN select * from sc_base_color where name > '米黄';
    EXPLAIN select * from sc_base_color where name < '米黄';
    

    截图结果
    在这里插入图片描述

  • 使用 不等于、not in 、isnull、!isnull查询

    EXPLAIN select * from sc_base_color where group_num != 1;
    EXPLAIN select * from sc_base_color where group_num not in (1);
    EXPLAIN select * from sc_base_color where  isnull(group_num);
    EXPLAIN select * from sc_base_color where  !isnull(group_num);
    

    截图结果
    在这里插入图片描述

  • 使用isnull、is not null查询

    # 使用is not null可能会导致索引失效,我测试了20条数据,只要null值占全部数据的百分之50就不会失效,否则会失效。又测了40条数据,23条数据不会为空,22条为null的会为空
    EXPLAIN select  * from sc_base_color where  group_num is not null;
    # 使用is null也可能会导致索引失效,我测试了20条数据,6数数据不为空不会失效,也就是可能当空的数据占比70%的时候索引会失效。
    EXPLAIN select  * from sc_base_color where  group_num is  null;
    

    由此可以得出结论,字段为空是可以走索引的,但是部分场景可能会失效,尽量还是给默认值。

4、测试组合索引为NULL是否走了索引

  • 先删除普通索引字段,增加组合索引

    ALTER TABLE sc_base_color DROP INDEX idx_group_num;
    ALTER TABLE sc_base_color DROP INDEX idx_name;
    alter table `sc_base_color` add index idx_group_num_idx_name (group_num, name);
    
  • 测试 = > < 查询结果

    EXPLAIN select  * from sc_base_color where  group_num > 1;
    EXPLAIN select  * from sc_base_color where  group_num < 1;
    EXPLAIN select  * from sc_base_color where  group_num = 1;
    EXPLAIN select  * from sc_base_color where group_num = 1 and name = '米黄';
    

    截图结果,是可以走索引的,下面的逻辑就不用测试了和普通索引一样,除非不符合最左匹配原则直接查询name字段。
    在这里插入图片描述

5、总结

  • 在设计数据库的时候尽量还是给字段的默认值。
    1、比如int、bigint类型默认值为-1/0
    2、比如varchar类型默认值为空串
    3、bigdecimal类型为0等等。
  • NULL值会有不少坑
    1、count(字段NULL)会过滤统计的数据,sum这些函数也会
    2、使用> < 的时候也会过滤掉为NULL的数据
    3、group by 的时候会把所有为NULL的数据合并,可以随机生成UUID解决
    4、还有场景可能也有问题,这里我也忘记了,用的时候才会想起来。
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_41768242/article/details/123957321

智能推荐

AR502H-CN开发笔记54:OVF和OVA的区别_ova/ovf-程序员宅基地

文章浏览阅读801次。本系列文章将向大家讲述华为网关AR502H的开发方法。本文介绍OVF和OVA的区别。一、什么是OVF文件开源虚拟化格式OVF文件是一种开源的文件规范,它描述了一个开源、安全、有效、可拓展的便携式虚拟打包以及软件分布格式,它一般有几个部分组成,分别是ovf文件、mf文件、cert文件、vmdk文件和iso文件。二、OVF文件工作原理以及各组件的作用简单来说,OVF文件可以抽象看做一个由规定的几个不同类型的文件所组成的文件包,这个文件包可作为以后不同虚拟机之间一个标准可靠的虚拟文件格式,实现_ova/ovf

【Vue-2】flask+vue时,浏览器可正常访问,但从vue访问返回Error: Network Error_vue network error-程序员宅基地

文章浏览阅读2.6k次。问题:flask+vue时,flask的网址可正常访问,但从vue访问返回【Error: Network Error】解决方案:在flask里 加一句:CORS(app, supports_credentials=True)解决跨域问题,服务端把CORS配置好,或者用node做代理层访问API浏览器访问时,对跨域请求进行了组织, 而vue的response没有, 所以用浏览器是可以进行请求的并且请求成功了, 但是vue肯定是不行的.开发环境的话可以通过Cors做一个中间层进行代理,js发_vue network error

JAVA 02——第一个JAVA程序的总结和提升_注释_数据类型_1.8^308-程序员宅基地

文章浏览阅读339次。目录第一个JAVA程序的总结和提升注释数据类型整数变量整型常数的三种表示形式进制转换浮点型字符型(2个字节)boolean类型(一位,不是一个字节)类型转换自动类型转换强制类型转换运算时类型提升问题基本类型转化时常见错误和问题第一个JAVA程序的总结和提升Java对大小写敏感,如果出现了大小写拼写错误,程序无法运行关键字cass表明Java程序中的全部内容都包含在类中,Java是一种面向对象的语言。 main方法是Java应用程序的入口方.._1.8^308

NOJ-1149-旅游预算_旅游预算算法设计图示-程序员宅基地

文章浏览阅读952次。旅游预算描述一个旅行社需要估算乘汽车从某城市到另一城市的最小费用,沿路有若干加油站,每个加油站收费不一定相同。旅游预算有如下规则: 若油箱的油过半,不停车加油,除非油箱中的油不可支持到下一站;每次加油时都加满;在一个加油站加油时,司机要花费2元买东西吃;司机不必为其他意外情况而准备额外的油;汽车开出时在起点加满油箱;计算精确到分(1元=100分)。编写程序估计实际行驶在某路线所需的最小费用。输入第一行为起点到终点的距离(实数) 第二行为三个实数,后跟一个整数,每两个数据间用一个空格隔开。其中第一个数_旅游预算算法设计图示

iOS SDK与Xcode_ios sdk如何混淆-程序员宅基地

文章浏览阅读415次。1. Xcode是一个非开源的IDE,即集成开发环境(Integrated Development Environment)。2. Xcode是用于(桌面级的)OS X和(移动级的)iOS开发。3. Xcode包括:> 设备分析工具(Instruments analysis tool)> iOS模拟器(iOS Simulator)> OS (OS Frameworks) :_ios sdk如何混淆

计算机辅助求解如何做,计算机辅助求解一元高次方程.pdf-程序员宅基地

文章浏览阅读482次。计算机辅助求解一元高次方程.pdf应用研究 数字技术与应用计算机辅助求解一元高次方程李艳(潍坊职业学院信息工程学院 山东潍坊 261041)摘要:本文主要介绍了使用计算机辅助求解..._计算机解高次方程

随便推点

Base64编码,去掉等号特殊字符_base64去掉等号不能被四整除-程序员宅基地

文章浏览阅读2.3w次。/// 自定义包含指定字符的base64工具 internal static class Base64Helper { static readonly string base64Table = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-"; static read_base64去掉等号不能被四整除

C++函数对象-谓词-程序员宅基地

文章浏览阅读2.5k次。函数对象(也叫functor),听起来或许有点陌生,但他们是c++实体,即使你没有用过,也一定见过,只是你没有意识到而已。从概念上讲,函数对象时用作函数的对象;但是从实现上来说,函数对象时实现了 operate()的类的对象。虽然函数和函数指针也可以归为函数对象,但实现了operate()的类的对象才能保存状态,才能用于STL。\quad我们直接看定义: - 一元函数:接受一个参数的函数,如f(x

做一个懒cocos2d-x程序猿(一)停止手打所有cpp文件到android.mk-程序员宅基地

文章浏览阅读815次。前言:"懒"在这里当然不是贬义词,而是追求高效,拒绝重复劳动的代名词!做一个懒cocos2d-x程序猿的系列文章将教会大家在工作中如何偷懒,文章篇幅大多较短,有的甚至只是几行代码,争取把懒发挥到极致!一.懒人说书Android.mk中LOCAL_SRC_FILES需要罗列出所有参与编译的文件,这样在.cpp文件少的时候还可以一个一个添加,当有几百个文件的时候会十分的痛苦!我们下

Linux nc常用命令_nc -nv命令-程序员宅基地

文章浏览阅读1.3k次。Linux nc常用命令Linux中nc命令是一个功能强大的网络工具,全称是netcat。 nc/netcat(选项)(参数) 选项:-g&lt;网关&gt;:设置路由器跃程通信网关,最多设置8个;-G&lt;指向器数目&gt;:设置来源路由指向器,其数值为4的倍数;-h:在线帮助;-i&lt;延迟秒数&gt;:设置时间间隔,以便传送信息及扫描通信端口;-l:使用监听模式,..._nc -nv命令

开启gtid 主从配置模式_gtid主从配置是否需要开启skip_slave_start-程序员宅基地

文章浏览阅读1.1k次。错误:描述2018-03-09 10:41:08 5128 [Note] InnoDB: Highest supported file format is Barracuda.2018-03-09 10:41:08 5128 [Note] InnoDB: 128 rollback segment(s) are active.2018-03-09 10:41:08 5128 [Note] Inn..._gtid主从配置是否需要开启skip_slave_start

java http url 读取远程文件-程序员宅基地

文章浏览阅读1.5k次。private static String readUrlFile(String url) { BufferedReader reader = null; StringBuilder content = new StringBuilder(); try { URL httpUrl = new URL(url); String line = null; CookieHandler.set.

推荐文章

热门文章

相关标签