当前位置: 首页 > news >正文

mysql not in 查询引发的bug问题记录

mysql not in 查询引发的bug问题记录

  • 数据准备
    • 版本信息
    • 建表语句
    • 测试数据
  • 问题说明
    • 使用 not in 查询
  • 问题分析以及正确写法
    • 执行逻辑
    • 错误问题分析
    • 性能分析
    • 正确写法

数据准备

版本信息

mysql 8.0.13

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (0.00 sec)

建表语句

create table t_null_test(id bigint primary key auto_increment,city varchar(100) comment '居住城市',user_id bigint comment '用户ID'
) charset=utf8mb4 comment = 'not in null测试表';create table t_user(
id bigint primary key auto_increment,
name varchar(50) comment '姓名'
) charset=utf8mb4;

查看表信息
show create table t_null_test\G

mysql> show create table t_null_test\G;
*************************** 1. row ***************************Table: t_null_test
Create Table: CREATE TABLE `t_null_test` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`city` varchar(100) DEFAULT NULL COMMENT '居住城市',`user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='not in null测试表'
1 row in set (0.00 sec)ERROR:
No query specifiedmysql> show create table t_user\G;
*************************** 1. row ***************************Table: t_user
Create Table: CREATE TABLE `t_user` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL COMMENT '姓名',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR:
No query specified

测试数据

insert into t_null_test(city, user_id) values('佛山', 'cd'),('广州', 'scd'), ('深圳', 'ss'), ('东莞', null);
mysql> insert into t_null_test(city, user_id) values('佛山', 1),('广州', 2), ('深圳', 3), ('东莞', null);
Query OK, 4 rows affected (0.16 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> select * from t_null_test;
+----+------+---------+
| id | city | user_id |
+----+------+---------+
|  1 | 佛山 |       1 |
|  2 | 广州 |       2 |
|  3 | 深圳 |       3 |
|  4 | 东莞 |    NULL |
+----+------+---------+
4 rows in set (0.00 sec)mysql> insert into t_user(name) values('scd'),('cd'),('ss'),('sss');
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | scd  |
|  2 | cd   |
|  3 | ss   |
|  4 | sss  |
+----+------+
4 rows in set (0.00 sec)

问题说明

使用 not in 查询

mysql> select * from t_user where id not in (select user_id from t_null_test);
Empty set (0.07 sec)

这条有问题的sql 主要的功能是从t_user表中筛选出那些id值不在t_null_test表的user_id列中的记录,查询出的结果为空,实际上是有一个id=4不存在 t_null_test表的

问题分析以及正确写法

执行逻辑

  1. 先执行子查询select user_id from t_null_test获取所有user_id值
  2. 主查询检查t_user表中每条记录的id是否不在子查询结果集中
  3. 最终返回满足条件的完整用户记录

错误问题分析

NOT IN在处理包含NULL值的子查询时可能返回意外结果,因为NULL值的比较会返回UNKNOWN而非TRUE/FALSE

性能分析

当子查询结果集较大时性能较差,因为它需要对子查询结果进行哈希匹配

正确写法

  1. 过滤 null 数据
mysql> select * from t_user where id not in (select user_id from t_null_test where user_id is not null);
+----+------+
| id | name |
+----+------+
|  4 | sss  |
+----+------+
1 row in set (0.00 sec)
  1. 使用 not exists (推荐)
mysql> select * from t_user u where not exists (select 1 from t_null_test t where u.id=t.user_id);
+----+------+
| id | name |
+----+------+
|  4 | sss  |
+----+------+
1 row in set (0.00 sec)
  1. 使用left join 过滤 null
mysql> explain select * from t_user u left join t_null_test t on u.id=t.user_id where t.user_id is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_user_id   | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

为啥推荐 not exists, 查看sql 执行计划分析

mysql> explain select * from t_user u where not exists (select 1 from t_null_test t where u.id=t.user_id);
+----+--------------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key         | key_len | ref       | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+-------------+
|  1 | PRIMARY            | u     | NULL       | ALL  | NULL          | NULL        | NULL    | NULL      |    4 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t     | NULL       | ref  | idx_user_id   | idx_user_id | 9       | test.u.id |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)mysql> explain select * from t_user where id not in (select user_id from t_null_test where user_id is not null);
+----+--------------------+-------------+------------+----------------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type        | table       | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------------+------------+----------------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | t_user      | NULL       | ALL            | NULL          | NULL        | NULL    | NULL |    4 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t_null_test | NULL       | index_subquery | idx_user_id   | idx_user_id | 9       | func |    2 |   100.00 | Using where; Using index |
+----+--------------------+-------------+------------+----------------+---------------+-------------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.02 sec)mysql> explain select * from t_user u left join t_null_test t on u.id=t.user_id where t.user_id is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_user_id   | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

扫描行数计算 rows * filtered%

  1. not exists = 1 * 100% = 1
  2. not in = 2 * 100% = 2
  3. left join … not null = 4 * 25% = 1
http://www.xdnf.cn/news/1143865.html

相关文章:

  • pycharm结构查看器
  • 2.3 前端-ts的接口以及自定义类型
  • 哪个厂家生产的戒烟药好:从机制到体验的差异化博弈
  • MySQL 插入时间 更新时间
  • 推荐 1 款 4.5k stars 的AI 大模型驱动的开源知识库搭建系统
  • 跨域问题及解决方案
  • AI(day10)模块化编程概念(模块、包、导入)及常见系统模块总结和第三方模块管理
  • Java Web项目Dump文件分析指南
  • LLM(Large Language Model)大规模语言模型浅析
  • 在 Jenkins 中使用 SSH 部署密钥
  • 游戏盾能否保护业务免受DDoS攻击吗?
  • C语言基础:数组练习题
  • 服务器内存满了怎么清理缓存?
  • 【C++】——类和对象(中)——默认成员函数
  • 前端基础——B/S工作原理、服务器与前端三大件
  • 【Docker】在Linux环境下使用Dockerfile打包镜像(图文示例)
  • 完整的 Meteor NPM 集成
  • 6 种无线传输照片从安卓到 Mac 的方法
  • UDP 协议下一发一收通信程序的实现与解析
  • 防爆手机是什么?能用普通手机改装吗?
  • 免费PDF文件格式转换工具
  • FastAdmin框架超级管理员密码重置与常规admin安全机制解析-卓伊凡|大东家
  • python学智能算法(二十三)|SVM-几何距离
  • react控制react Popover组件显示隐藏
  • 【怜渠客】简单实现手机云控Windows电脑锁屏
  • 力扣面试150(33/150)
  • pytest + requests 接口自动化测试框架
  • UE 一些基础的python
  • AE MDX L6 L12 L18 电源手侧操作使用说明
  • 在git中同时配置gitcode和github访问权限