MySQL not in 失效、优化

原创 已于 2023-04-13 10:50:37 修改 · 742 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #java

于 2023-03-16 13:35:25 首次发布
当使用MySQL的notin操作符时，如果子查询包含null值，结果可能会为空。这是因为notin会将null视为无效条件。为避免这种情况并优化查询，可以使用左连接代替，例如：select*fromt1leftjoint2ont1.aa=t2.bb。这种方法能有效排除null值的影响，同时可能提高查询效率。

mysql 的 not in 中，不能包含 null 值。否则，将会返回空结果集。

对于 not in 来说，如果子查询中包含 null 值的话，那么，它将会翻译为 not in null。除了 null 以外的所有数据，都满足这一点。所以，就会出现 not in “失效”的情况。

not in 优化可以使用左连接替代；
如：
select * from t1 left join t2 on t1.aa=t2.bb;
左连接可以排除not in 中 null值失效的风险；还可以提升效率；

【异常】MySQL NOT IN 子查询 不生效的原因
本本本添哥
02-11 1155
业务表中存在着多条上报的数据，需求是需要将按经纬度+车机SN码以及记录时间来进行去重后选ID，并删除多余的不需要的ID记录，即一天只保留一条相同经纬度坐标以及车机SN码的记录。于是， 编写了如下的SQL语句。
MySQL性能优化MySQL索引失效的13种隐蔽场景排查及解决方法
C_V_Better的博客
02-08 1995
通过以上隐蔽场景的分析，我们可以看到 MySQL 索引失效的原因多种多样。在实际应用中，我们需要仔细分析查询语句，确保索引被正确使用。通过合理创建索引、优化查询语句和使用 EXPLAIN 命令分析查询计划，可以有效避免索引失效，提高数据库的性能。希望本文的内容对你有所帮助，如果你有任何问题或建议，欢迎在评论区留言。
Mysql not in 不生效
qq_45472675的博客
11-04 1769
mysql not in sql 不生效 失效问题解决方案
not in 失效
liheqian_0083
03-09 249
    今天同事碰到一个问题，在做两个表的数据对照时用了not in ，结果出现了查找失败的结果。网上查了一下才知道，not in 有个毛病：    像SQL语句  select * from t1 where f1 not in (select f1 from t2) 这是去除t1表中包括t2中数据的语句。如果在t2中的f1字段中有null值的话，则查不到结果。也就是说在not...
mysql not in 子查询 不生效的原因
weixin_51519772的博客
03-07 2609
MySQL 子查询not in不生效是因为子查询结果里有null，排除null值才可正常返回数据。 #MySQL 子查询not in不生效是因为子查询结果里有null，排除null值才可正常返回数据。 SELECT * FROM `a` WHERE `emp_mobile` NOT IN ( SELECT mobile FROM b )#这样如果mobile 里面有空值的话就会返回空，不是你想要的结果 #可以这样 SELECT* FROM `a` WHERE `emp_mobile` NOT IN
mysql not in 无效_MySQL not in查询不出数据（MySQL not in 无效）
weixin_39805119的博客
01-20 589
今天使用 MySQL 的 not in 进行查询的时候，发现结果里面并没有返回任何数据。SQL 语句没有任何问题，但是结果集却是空，实在无法理解。纠结了半天，最后使用 left join，两表关联，找到了目标数据。但是这样的话，难道 not in 就不能使用了吗？最后经过查找，找到了原因。mysql 的 not in 中，不能包含 null 值。否则，将会返回空结果集。对于 not in 来说，如...
mysql数据库In的优化.txt
06-23
### MySQL数据库In子句优化详解 #### 一、引言 在MySQL数据库的实际应用中，`IN`子句被广泛用于处理多个值的情况。当涉及到大量数据时，如何有效地使用`IN`子句成为了提高查询性能的关键。本文将详细介绍如何针对`...
导致MySQL索引失效的一些常见写法总结
09-14
12. **使用NOT IN或IN与子查询**：当使用子查询时，MySQL可能无法评估索引的有效性，导致索引失效。 13. **索引选择性低**：如果索引列的唯一性不高，即很多行都有相同的索引值，MySQL可能认为全表扫描更高效。 ...
OceanBase的SQL 优化实践： NOT IN 子查询
OceanBase分布式数据库的博客
04-26 1449
本文借助一个案例，来了解 not in 对 NULL 值敏感的处理逻辑，并探讨相应的优化方法。
Mysql索引失效的常见原因&如何用好索引
H_Jason_的博客
10-11 1597
本篇文章主要介绍了索引失效的常见原因和如何用好索引，并以案例的形式进行了说明
MySql如何使用not in实现优化
12-14
最近项目上用select查询时使用到了not in来排除用不到的主键id一开始使用的sql如下： select 　　s.SORT_ID， 　　s.SORT_NAME, 　　s.SORT_STATUS, 　　s.SORT_LOGO_URL, 　　s.SORT_LOGO_URL_LIGHT from SYS_SORT_PROMOTE s 　　WHERE 　　　　s.SORT_NAME = '必听经典' 　　　　AND s.SORT_ID NOT IN ("SORTID001") 　　limit 1; 表中的数据较多时这个sql的执行时间较长、执行效率低，在网上找资料说可以用 left join进
随笔记：MySQL not in 查询失效
如果有一天我能够拥有一个大果园，我愿放下所有追求做个农夫去种田
07-13 6970
使用 not in 加子查询的 查询语句 失效，测试数据库 上好好的，线上项目查不出数据， 数据库数据是存在的 发现 问题出在 not in 后 有为null 的数据 所以没有数据返回 null什么都不能当 null 与ID进行匹配 返回 false 所以 一条数据也不会返回 原始sql select * from xx where id not in (‘1’,’2’,n
sql语句中not in 不好使的原因之一
spWarrior的博客
08-01 6865
场景说明：查询某表中的某字段的值没有在另外一个表中对应的字段中出现过 比如现在有两个表，一个产品表product，一个优惠券批次表coupon，coupon中的product_code字段与product中的product_code形成一对一 的关系，现在有需求查询未绑定过的产品信息 一开始not exists搞，但是查了半天没搞懂，退而求其次，想用not in查询，发现查不出来，不好使。。。...
sql语句中的 in 、not in 、exists、not exists 详细用法说明和差别----not in失效
热门推荐
naruto517308294的专栏
03-08 3万+
来看下面这样一条SQL语句： select * from student where 20 其中，(select score from student)返回的是所有分数的记录集 只有当记录集中的所有分数都大于20后，才会执行前面的SQL语句。显然，任何一条记录都大于20. 如果把它改成50，则查不到任何数据，因为只要有一条记录小于50,条件都不成立。 但是，如果把all改面any就
MySQL用了not in查不出数据的原因
chuxuan0215的博客
05-26 2017
not In 相当于 all， 如果 Not In 后面跟的是子查询的话， 子查询中只要包含一个 null 的返回值，则会造成 整个 Not in 字句返回空值，结果就是查询不会返回任何结果。 而 in 相当于 =any 的意思,可以有效处理子查询中返回空值的情况,返回正确的结。 防止NOT IN 为空值的情况出现，可以在条件后面的判断加上判空语句 SELECT dataid FRO
mysql not_Mysql not in 子查询 不生效的bug发现与解决
weixin_29485605的博客
01-18 919
如下sql为当前我们的业务库中的一个去重的逻辑。select * from test_user where (user_id,area_id) in (select user_id,area_id from test_user where user_id is not null group by user_id,area_id HAVING count(*) > 1) and id not ...
MySQL not in查询不出数据（MySQL not in 无效）
gaoshan12345678910的博客
06-23 1万+
MySQL not in查询不出数据（MySQL not in 无效）
NOT IN 无效
weixin_41830501的博客
06-26 936
select * from A where id not in (一堆具体的值)；我遇到的情况是查询出A表的所有内容，not in 没有效果解决方案：我的做法是将not in 后面的值存到一张临时表B中，select * from A where id not in (select id from B)...
Mysql 用 not in 不返回任何结果的原因及解决方案
auvs67634的博客
04-22 906
原因 not in相当于all，如果 not in 后面跟的是子查询，子查询中只要包含一个 null 的返回值，则会造成 整个 not in 字句返回空值，查询不会返回任何结果。 但in 相当于 any ,可以处理子查询中返回null的情况,返回正确的结果。 解决方案 为防止not in返回空值，可以在子查询的Where 语句后筛掉为空的记录，例如： select * from user...
MySQL优化：索引失效原因
最新发布
06-14
<think>我们讨论MySQL索引失效的原因。索引失效会导致查询性能下降，甚至可能退化为全表扫描。常见的索引失效原因包括：1.使用`!=`或者`<>`操作符：如果使用不等于操作符，MySQL可能无法使用索引。2.使用`OR`连接条件：如果OR条件中有一列没有索引，那么整个查询都不会使用索引。但是，如果OR条件中的每个列都有索引，那么可能会使用索引合并（indexmerge）。3.对索引列进行函数操作或表达式计算：例如，在索引列上使用函数（如`DATE()`，`SUBSTR()`）或进行数学运算（如`col+1=10`）。4.使用`LIKE`以通配符开头：如`LIKE'%abc'`，这种前缀模糊匹配会导致索引失效；但后缀匹配`LIKE'abc%'`可以使用索引。5.类型转换：如果索引列是字符串类型，而查询条件中使用了数字，那么MySQL可能会进行隐式类型转换，导致索引失效。6.复合索引未使用最左前缀：复合索引按照最左前缀原则匹配，如果查询条件中没有使用复合索引的第一列，则索引可能不会被使用。7.使用`IN`或`NOTIN`：在MySQL中，使用`IN`通常还是会使用索引的，但是当`IN`的值过多时，优化器可能会选择全表扫描。`NOTIN`则通常不会使用索引。8.索引列上使用`ISNULL`或`ISNOTNULL`：这可能会导致索引失效，取决于数据分布和优化器的选择。不过，如果列定义为`NOTNULL`，则`ISNULL`条件永远不会成立。9.全表扫描比使用索引更快：当表中数据量很小，或者需要返回大部分数据行时，优化器可能选择全表扫描。下面我们通过一些示例来说明：示例1：使用函数导致索引失效假设有表`t1`，在列`create_time`上建立了索引，以下查询会导致索引失效：SELECT*FROMt1WHEREDATE(create_time)='2020-01-01';应该改为：SELECT*FROMt1WHEREcreate_time>='2020-01-01'ANDcreate_time<'2020-01-02';示例2：复合索引未使用最左前缀假设有复合索引`idx_name_age`(name,age)，以下查询可以使用索引：SELECT*FROMt1WHEREname='Tom';SELECT*FROMt1WHEREname='Tom'ANDage=20;但以下查询无法使用复合索引（除非单独有age的索引）：SELECT*FROMt1WHEREage=20;示例3：隐式类型转换假设列`phone`是`varchar`类型，并且有索引，以下查询会导致索引失效：SELECT*FROMt1WHEREphone=13800138000;因为这里将字符串列与数字比较，MySQL会将字符串转换为数字，相当于在列上使用了函数。应该改为：SELECT*FROMt1WHEREphone='13800138000';示例4：使用OR导致索引失效假设有索引在列`a`上，没有索引在列`b`上：SELECT*FROMt1WHEREa=1ORb=2;这个查询将不会使用列`a`上的索引，因为OR条件中有一个列没有索引。为了避免索引失效，我们应当：-避免在索引列上使用函数或表达式。-尽量避免使用`OR`，可以改用`UNION`或`UNIONALL`。-注意复合索引的最左前缀原则。-注意`LIKE`查询的通配符位置。-避免隐式类型转换。-使用`EXPLAIN`分析查询语句，查看索引使用情况。下面我们给出一个示例，使用`EXPLAIN`来查看查询是否使用了索引。假设有表`employees`：```sqlCREATETABLEemployees(idINTPRIMARYKEY,nameVARCHAR(100),ageINT,departmentVARCHAR(100),hire_dateDATE,INDEXidx_name_age(name,age),INDEXidx_hire_date(hire_date));```查询1：使用最左前缀```sqlEXPLAINSELECT*FROMemployeesWHEREname='John';```这里应该会使用到索引`idx_name_age`。查询2：不使用最左前缀```sqlEXPLAINSELECT*FROMemployeesWHEREage=30;```这里不会使用复合索引`idx_name_age`，因为age不是最左列。查询3：在hire_date上使用函数```sqlEXPLAINSELECT*FROMemployeesWHEREYEAR(hire_date)=2020;```这里不会使用索引`idx_hire_date`，因为使用了函数。我们可以通过改写查询来避免使用函数：```sqlEXPLAINSELECT*FROMemployeesWHEREhire_dateBETWEEN'2020-01-01'AND'2020-12-31';```这样就能使用索引了。注意：优化器的行为可能会因为MySQL版本、数据量、数据分布等因素而有所不同，因此在实际环境中，使用`EXPLAIN`来验证索引使用情况是非常重要的。最后，我们提供一些优化索引使用的建议：1.经常出现在WHERE、ORDERBY、GROUPBY、JOINON子句中的列，可以考虑建立索引。2.避免创建过多的索引，因为索引会占用空间，并且影响写操作的性能。3.使用覆盖索引（即索引包含所有查询的列）来避免回表。4.定期分析表（使用`ANALYZETABLE`）更新索引统计信息，帮助优化器做出更好的决策。现在，根据要求，在最后提出相关问题。</think>### MySQL索引失效原因及优化方案 索引失效会导致查询性能大幅下降，常见原因及解决方案如下： #### 1. **违反最左前缀原则（复合索引）** **原因**：复合索引未从最左侧字段开始使用 **示例**：索引 `(a,b,c)`，查询条件为 `WHERE b=1 AND c=2` **优化方案**： ```sql -- 创建复合索引 CREATE INDEX idx_composite ON table_name(col1, col2, col3); -- 有效查询（使用索引） SELECT * FROM table_name WHERE col1 = 'A' AND col2 = 'B'; -- 失效查询（跳过col1） SELECT * FROM table_name WHERE col2 = 'B'; -- ❌ 索引失效 ``` #### 2. **在索引列上使用函数或计算** **原因**：对索引列进行函数处理或计算 **示例**：`WHERE YEAR(create_time) = 2023` **优化方案**： ```sql -- 原始失效查询 SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- ❌ -- 优化为范围查询 SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; -- ✅ ``` #### 3. **隐式类型转换** **原因**：数据类型不匹配导致转换 **示例**：字符串列用数字查询 `WHERE phone = 13800138000` **优化方案**： ```sql -- phone是VARCHAR类型 SELECT * FROM users WHERE phone = '13800138000'; -- ✅ -- 错误示例（触发类型转换） SELECT * FROM users WHERE phone = 13800138000; -- ❌ ``` #### 4. **LIKE模糊查询以通配符开头** **原因**：`%value%` 无法使用索引 **优化方案**： ```sql -- 索引失效 SELECT * FROM products WHERE name LIKE '%apple%'; -- ❌ -- 可优化为（需全文索引支持） ALTER TABLE products ADD FULLTEXT(name); SELECT * FROM products WHERE MATCH(name) AGAINST('apple'); ``` #### 5. **使用OR连接非索引列** **原因**：OR条件包含无索引列 **优化方案**： ```sql -- 原始查询（status无索引） SELECT * FROM orders WHERE order_id = 100 OR status = 'shipped'; -- ❌ -- 优化方案1：拆分查询 SELECT * FROM orders WHERE order_id = 100 UNION ALL SELECT * FROM orders WHERE status = 'shipped'; -- 优化方案2：添加索引 ALTER TABLE orders ADD INDEX idx_status(status); ``` #### 6. **索引列使用不等于（!= 或 <>）** **原因**：不等于操作无法有效利用索引 **优化方案**： ```sql -- 低效查询 SELECT * FROM users WHERE age <> 30; -- ❌ -- 优化为范围查询+覆盖索引 SELECT id FROM users WHERE age < 30 OR age > 30; -- ✅ ``` #### 7. **索引列使用IS NULL/IS NOT NULL** **原因**：空值判断可能绕过索引 **优化方案**： ```sql -- 添加条件索引 CREATE INDEX idx_email ON users(email) WHERE email IS NOT NULL; -- 查询优化 SELECT * FROM users WHERE email IS NOT NULL; ``` #### 8. **数据分布不均导致优化器弃用索引** **原因**：当值重复率过高时（>30%），优化器选择全表扫描 **解决方案**： ```sql -- 强制使用索引 SELECT * FROM users FORCE INDEX(idx_status) WHERE status = 'active'; -- 优化统计信息 ANALYZE TABLE users; ``` #### 9. **JOIN字段类型/字符集不匹配** **原因**：连接字段类型不一致 **优化方案**： ```sql -- 统一字符集和排序规则 ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ``` ### 诊断工具 使用 `EXPLAIN` 分析查询： ```sql EXPLAIN SELECT * FROM users WHERE email LIKE 'user%@example.com'; ``` 关键指标： - `type`：`index`或`range`表示使用索引 - `key`：显示实际使用的索引 - `rows`：扫描行数（越少越好） - `Extra`：避免出现 `Using filesort` 或 `Using temporary` ### 最佳实践 1. 使用覆盖索引减少回表 2. 避免 `SELECT *`，只查询必要字段 3. 定期执行 `OPTIMIZE TABLE` 整理碎片 4. 监控慢查询日志：`SET GLOBAL slow_query_log = ON;`
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值