MySQL的where子句中or导致索引失效，以及包含or时能使索引生效的情况

最新推荐文章于 2025-02-11 21:07:01 发布
最新推荐文章于 2025-02-11 21:07:01 发布
阅读量2.3k 收藏 6
点赞数 3
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： mysql 索引 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43073775/article/details/118466301
本文解析了在SQL查询中，当使用包含OR的条件时，为何必须确保至少有一个索引包含所有涉及的字段，否则索引可能失效。通过实例展示了有效和无效的索引使用情况，强调了全字段索引对于复杂查询的重要性。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

结论：包含or时，需要有一个索引包含了所有where用到的字段，这个索引才会生效。否则索引不生效。

建表语句

CREATE TABLE `course` (
  `cno` varchar(20) NOT NULL,
  `cname` varchar(10) NOT NULL,
  `tno` varchar(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`cno`),
  KEY `cname` (`cname`) USING BTREE,
  KEY `tno` (`tno`) USING BTREE,
  KEY `age` (`age`) USING BTREE,
  KEY `tow` (`cname`,`tno`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如下tow索引能生效

SELECT tno,cname from course WHERE tno = "804" or tno = "805";

如下三种情况tow索引能生效

SELECT tno,cname from course WHERE tno = "804" or cname="操作系统";

SELECT tno,cname from course WHERE tno = "804" or tno = "805" and cname="操作系统";

SELECT tno,cname from course WHERE cname="操作系统" or cname="操作" and tno = "804";

如下sql索引不生效，因为没有一个索引包含了所有where用到的字段。

SELECT tno,cname from course WHERE cname="操作系统" or age=23 and tno = "804";

SELECT tno,cname from course WHERE cname="操作系统" or tno = "804" and age=23;
【经典面试题】【MySQL数据库】MySQL索引失效是什么以及常见的MySQL索引失效的原因
本本本添哥
03-13 282
索引失效（Index Not Used or Index Ignored）在数据库查询优化中是指虽然存在针对某列或某组合列的索引，但是在执行特定查询时，数据库管理系统没有选择使用这些索引来加速检索过程。这通常会导致原本可以通过快速定位索引来高效访问数据的操作转而采用全表扫描的方式进行，从而影响查询性能。索引失效是指数据库查询过程中，本应使用索引的情况而没有使用索引，导致查询效率降低。索引失效是指数据库在查询数据时，未能有效利用已经创建的索引，导致查询效率降低。
mysql中or是否走索引详解
08-11
NULL 博文链接：https://bugyun.iteye.com/blog/2242094
为什么where条件中使用or索引不起作用？
azhegps的博客
08-05 7574
为什么where条件中使用or索引不起作用？where条件中使用or，索引就会失效，会造成全表扫描 是误区 一，要求使用的所有字段，都必须建立索引。 二，数据量太少，制定执行计划时发现全表扫描比索引查找更快。 三，确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union...
mysql 索引列 or_MySQL避免索引列使用 OR 条件
weixin_35861265的博客
01-25 202
这个亏已经吃过很多次了，在开发以前的sql代码里面，许多以 or 作为where条件的查询，甚至更新。这里举例来说明使用 or 的弊端，以及改进办法。select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067and (f_mobile ='1234567891' or f_phone ='1234567891' ) limit...
部分会导致索引失效的情况
运气只是当机会来临时，你已为它准备好。
08-11 2747
使所有索引都有效的方案：将c放到建立索引的语句的最后，例如：建立(a,b,d,c)的索引（a,b,d的顺序可以任意调整），这样执行sql的时候，优化器会帮我们调整WHERE后a,b,c,d的顺序（将c放到最后），让我们用上索引。其实单个索引字段，使用is null或is not null时，是可以命中索引的，但是两个不同索引字段用or连接时，索引就失效了。叶子节点的b值为1,2,1,4,1,2，它不是有序的，因此不能使用(a,b)联合索引。假设USER表中的user_id列有索引，age列没有索引。...
MySQL中：有关使用where语句时索引是否失效的问题
weixin_68093610的博客
02-11 1218
详细总结了在MySQL中使用where语句时索引是否失效的问题，可以帮助对数据库优化器的理解！
mysql索引失效的五种情况分析
12-15
为了使索引生效，每个OR子句中的列都需要有独立的索引。例如，`WHERE col1 = 'value1' OR col2 = 'value2'`，如果col1和col2都有索引，那么两个索引都将被利用。 2. **多列索引的顺序**：多列索引只有在查询按照...
MySQL数据库索引失效的10种场景.zip
02-17
可以考虑用多个相等条件替代，以使索引生效。 **8. 数据类型不兼容** 如果查询中的列和索引的类型不完全匹配，如整数和字符串的比较，MySQL可能无法使用索引。确保数据类型的一致性很重要。 **9. GROUP BY和ORDER ...
MySQL索引失效的场景
SHI940637335的博客
08-22 1136
表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。
MySQL语句之or/and
热门推荐
stypace的专栏
08-02 4万+
1 在where中可以包含任意数目的and和or操作副
mysql索引or_mysql索引之or条件
weixin_29905347的博客
01-18 962
在某些情况下，or条件可以避免全表扫描的。1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。1)myisam表：CREATE TABLE IF NOT EXISTS `a` (`id` int(1) NOT NULL AUTO_INCREMENT,`uid` int(11) NOT NULL,`aNum` char(20) DEFAULT NULL,PRI...
mysql where 条件语句中的OR和AND的用法注意！
szq466028431的专栏
02-09 4万+
在mysql中，在进行数据库的增删改查时，经常会遇到这样的情况，在写条件语句where时，可能会同时有多个条件的“或”或者“与”,但用法不当经常会达不到效果，经过尝试，本人发现一个where语句中同时出现条件的“与”或者“或的时候”，要将多个OR用小括号括起来再和AND进行“与”，或者将多个AND用小括号括起来再与OR进行“或”。
mysql关于or的索引问题
gb4215287的博客
09-24 1153
摘自： http://www.educity.cn/wenda/590849.html http://blog.youkuaiyun.com/hguisu/article/details/7106159 问： 不是说，一条sql语句只能用一个索引么 但SELECT * FROM `comment` WHERE `toconuid` = '10' or `tocomuid` = '10' 其中 toconuid列 和 tocomuid列 分别为单列索引 explain后 显示两个索引都用了，extra为 U
mysql查询条件为or_使用mysql查询where条件里的or和and
weixin_39997443的博客
01-18 975
为什么要着重讲这块内容呢?因为好多小伙伴都会混淆，要不就是不知道怎么组合使用，今天就给大家讲这部分内容干货，让大家半分钟看懂。AND、OR运算符的组合使用在WHERE子句中，通过AND、OR运算符可以同时连接多个条件，当然AND、OR运算符也可以同时使用。但是当AND、OR运算符同时存在时，其优先级如何确定呢?与大多数语言一样，SQL语言认为AND运算符的运算级别要高于OR运算符。即当出现123c...
sql语句中where后边的哪些条件会使索引失效 -- SQL语句优化
aoshanshu6185的博客
09-17 4862
在我们编写sql的过程中where的条件使用不当会使索引（index）失效 1. 在 where 子句中使用!=或<>操作符，会使引擎放弃使用索引而进行全表扫描 2. 在 where 子句中对字段进行 null 值判断，会使引擎放弃使用索引而进行全表扫描 3. 在 where 子句中使用 or 来连接条件,会使引擎放弃使用索引而进行全表扫描 可改为： ...
mysql关于or的索引问题_mysql 关于or前后条件的索引有关问题
weixin_32658257的博客
01-25 350
引用7.2.6. 索引合并优化7.2.6.1. 索引合并交集访问算法7.2.6.2. 索引合并并集访问算法7.2.6.3. 索引合并排序并集访问算法索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引...
mysql索引
weixin_46217160的博客
02-07 544
索引的概念： 在关系数据库中，索引是一种对数据库表中一列或多列的值进行排序的一种数据结构，可以提高查询速度。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，以此快速定位查询数据。 本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，根本原因就在于索引减少了查询过程中的IO次数。 索引的数据结构： 索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引..
mysql or 创建索引_mysql里where条件中包含or应该如何建立有效的索引
weixin_39586915的博客
01-18 1664
mysql里where条件中包含or应该怎么建立有效的索引。SELECT t.* FROM cdb_threads t WHERE (t.fid= '2 ' Or t.otherfid= '2 ') AND t.displayorder> =0 ORDER BY t.displayorder DESC, t.lastpost D...
使用or会导致索引失效吗
最新发布
06-22
<think>我们正在讨论SQL中使用OR导致索引失效的问题及优化方案。根据引用[2]和引用[3]：-当OR连接的两个条件中，有一个条件的列没有索引时，整个查询的索引会失效。-即使OR连接的同一字段，如果其中包含非索引查询（比如范围查询、不等号等），也可能导致索引失效。具体来说：-情况1：OR前后存在非索引的列，索引失效。-情况2：OR连接同一字段，但存在非索引查询（如>、<、!=）时，索引失效（注意：只有当OR左右两侧查询都使用索引时，索引才有效）。因此，我们需要针对这些情况提出优化方案。优化方案：1.**为每个条件列创建索引**：确保OR连接的每个列都有索引。但注意，即使每个列都有单列索引，MySQL在OR条件下也可能不会使用索引（除非使用索引合并优化index_merge）。2.**使用UNION替代OR**：将OR条件拆分成多个查询，然后用UNION（或UNIONALL，如果不需要去重）连接。这样每个查询都可以使用自己的索引，然后合并结果。3.**使用覆盖索引**：如果查询的列都包含在索引中，那么即使使用OR，也可能通过索引覆盖来提升性能（但要注意OR条件本身是否会导致索引失效）。4.**使用索引合并优化（index_merge）**：MySQL在某些情况下可以对OR条件使用索引合并（index_merge）策略，即同时使用多个索引，然后合并结果。但该优化并不总是生效，取决于优化器的选择。下面详细说明：###1.使用UNION替代OR（推荐）将OR条件拆分成多个SELECT，然后用UNIONALL连接（如果确定没有重复记录，或者不关心重复记录，使用UNIONALL效率更高，因为不需要去重）。原始查询（可能索引失效）：```sqlSELECT*FROMtableWHEREcol1='value1'ORcol2='value2';```优化后：```sqlSELECT*FROMtableWHEREcol1='value1'UNIONALLSELECT*FROMtableWHEREcol2='value2';```注意：如果两个条件可能同时满足，会产生重复记录，可以使用UNION（去重）或者根据业务需求决定是否使用UNIONALL。###2.确保索引存在并尝试使用索引合并为col1和col2分别建立索引：```sqlCREATEINDEXidx_col1ONtable(col1);CREATEINDEXidx_col2ONtable(col2);```然后，MySQL可能会使用index_merge优化，通过执行计划查看：```sqlEXPLAINSELECT*FROMtableWHEREcol1='value1'ORcol2='value2';```如果执行计划的type列显示为`index_merge`，并且Extra列显示`Usingunion(idx_col1,idx_col2);Usingwhere`，则说明使用了索引合并。但是，索引合并并不总是比UNIONALL高效，尤其是在数据量较大时，因为需要合并两个索引的结果并去重（如果是OR条件，需要取并集）。所以，如果发现索引合并效率不高，可以强制使用UNIONALL的方式。###3.使用覆盖索引如果查询的列都包含在索引中，那么即使使用OR条件，数据库也可以只通过索引来获取数据（不需要回表），这样可能提升性能。但要注意，OR条件本身是否导致索引失效（覆盖索引只是避免了回表，但索引扫描本身可能还是全索引扫描）。例如，我们创建一个复合索引包含(col1,col2)和查询的列（假设还有id）：```sqlCREATEINDEXidx_coveringONtable(col1,col2,id);```然后查询：```sqlSELECTid,col1,col2FROMtableWHEREcol1='value1'ORcol2='value2';```这样，查询的列（id,col1,col2）都在索引中，数据库可能只扫描索引而不用回表。但是，这个复合索引对于OR条件可能不会被有效使用（因为复合索引对于OR条件并不友好）。因此，这种方法可能不如前两种方法有效。###4.其他优化建议-**避免在WHERE子句中使用OR**：在编写SQL时，尽量避免使用OR，特别是当OR连接的条件在不同的列上时。-**使用IN代替OR**：如果OR条件是在同一字段上的多个值，使用IN代替OR。例如，`WHEREcol='a'ORcol='b'`可以写成`WHEREcolIN('a','b')`，这样通常可以使用索引。-**使用CASE语句或条件重写**：有时候可以通过逻辑重写来避免OR条件。###示例优化分析假设有一个表`employee`，包含字段`id`（主键）、`name`（有索引）、`department`（无索引）、`salary`（有索引）。查询：找出名字为'John'或工资大于50000的员工。原始SQL：```sqlSELECT*FROMemployeeWHEREname='John'ORsalary>50000;```问题：由于`department`没有索引不是问题，但这里OR连接的两个条件中，`name`有索引，`salary`有索引，但是`salary>50000`是一个范围条件，且两个条件在不同的列上。这时，MySQL可能会尝试使用索引合并（如果优化器认为合适），也可能不使用索引（特别是如果范围条件返回的行很多）。优化方案1：使用UNIONALL```sqlSELECT*FROMemployeeWHEREname='John'UNIONALLSELECT*FROMemployeeWHEREsalary>50000;```但是要注意，如果一个人同时满足两个条件（名字是John且工资大于50000），他会被查询两次。为了避免重复，可以使用UNION（去重）：```sqlSELECT*FROMemployeeWHEREname='John'UNIONSELECT*FROMemployeeWHEREsalary>50000;```然而，去重操作会带来额外的开销。因此，如果业务上允许重复（或者确定不会重复），则使用UNIONALL。优化方案2：创建复合索引？在这个例子中，由于是两个不同的列，复合索引（name,salary）对OR条件没有帮助，因为复合索引要求最左前缀匹配。优化方案3：覆盖索引？如果我们在(name,salary)上建立复合索引，并且查询的列只有name和salary，那么我们可以使用覆盖索引：```sqlSELECTname,salaryFROMemployeeWHEREname='John'ORsalary>50000;```这样，数据库可以只扫描索引，而不用回表。但是，如果查询的列是`*`，则覆盖索引无效。因此，最通用的优化方案是使用UNIONALL。###总结当SQL查询中使用OR导致索引失效时，我们可以：1.使用UNION或UNIONALL重写查询（推荐）。2.确保OR连接的每个列都有索引，并尝试利用索引合并（但需注意性能）。3.考虑使用覆盖索引（如果查询的列较少且可以包含在索引中）。4.避免在WHERE子句中使用OR，尤其是连接不同列的条件。根据具体情况选择优化方案，并通过EXPLAIN命令验证优化效果。注意：优化方案需要根据实际数据量、索引情况、数据库版本等因素进行调整。引用：[^2]:OR前后存在非索引的列，索引失效；索引优化：关联查询优化。[^3]:or连接不同字段，索引失效；or连接同一字段，但存在非索引查询（如>、<、!=）时，索引失效（除非左右都是索引查询）。相关问题：1.如何通过EXPLAIN分析OR查询的执行计划？2.索引合并（index_merge）的工作原理是什么？3.使用UNIONALL时如何避免重复记录？4.覆盖索引在OR查询中如何应用？5.在哪些情况下，使用OR不会导致索引失效？</think>### SQL 使用 OR 导致索引失效的原因及优化方案 #### 一、OR 导致索引失效的核心原因 1. **OR 连接不同字段**（至少一个字段无索引） ```sql -- 假设 name 有索引，age 无索引 SELECT * FROM users WHERE name = 'John' OR age > 30; -- 索引失效 ``` 2. **OR 连接同一字段但含非索引操作** ```sql -- 假设 salary 有索引 SELECT * FROM employees WHERE salary > 5000 OR salary < 2000; -- 范围查询使索引失效[^3] ``` 3. **OR 与 NULL 值组合** ```sql -- 假设 phone 有索引 SELECT * FROM contacts WHERE phone = '123' OR phone IS NULL; -- IS NULL 可能使索引失效[^3] ``` #### 二、优化方案（附示例代码） ##### ✅ 方案 1：UNION ALL 拆分查询（推荐） ```sql -- 原始失效查询 SELECT * FROM products WHERE category_id = 5 OR price > 100; -- 优化后（确保每个子查询走索引） SELECT * FROM products WHERE category_id = 5 UNION ALL SELECT * FROM products WHERE price > 100; ``` **优势**： - 每个子查询可独立使用索引[^2] - 避免全表扫描 - 通过 `UNION ALL` 避免去重开销 ##### ✅ 方案 2：改写为 IN 查询 ```sql -- 适用于离散值场景 SELECT * FROM orders WHERE status = 'SHIPPED' OR status = 'DELIVERED'; -- 改写为（status字段需有索引） SELECT * FROM orders WHERE status IN ('SHIPPED', 'DELIVERED'); ``` **注意**： - IN 列表值不宜过多（建议 < 1000） - 值过多时需分批次处理[^3] ##### ✅ 方案 3：覆盖索引优化 ```sql -- 原始查询 SELECT id, name FROM employees WHERE department = 'IT' OR hire_year > 2020; -- 创建覆盖索引 CREATE INDEX idx_cover ON employees(department, hire_year, id, name); -- 查询使用覆盖索引（避免回表） SELECT id, name FROM employees WHERE department = 'IT' OR hire_year > 2020; ``` **适用场景**： - 仅查询索引包含的字段[^4] - 需权衡索引维护成本 ##### ✅ 方案 4：临时表 + JOIN ```sql -- 复杂OR条件场景 CREATE TEMPORARY TABLE temp_conditions ( id INT PRIMARY KEY ); INSERT INTO temp_conditions VALUES (1), (2), (3); -- 动态生成条件值 SELECT u.* FROM users u JOIN temp_conditions t ON u.id = t.id WHERE u.age > 25 OR u.city = 'Beijing'; ``` **优势**： - 适合超大型数据集 - 避免单次复杂条件解析[^3] #### 三、特殊情况处理 1. **OR 与 NULL 组合优化** ```sql -- 避免 IS NULL SELECT * FROM customers WHERE phone = '123' OR COALESCE(phone, '') = ''; ``` 2. **OR 含函数时的优化** ```sql -- 原始失效查询 SELECT * FROM logs WHERE DATE(create_time) = '2023-01-01' OR content LIKE '%error%'; -- 优化：剥离函数计算 SELECT * FROM logs WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59' OR content LIKE '%error%'; ``` #### 四、最佳实践总结 1. **优先使用 `UNION ALL`**：95%场景的最优解 2. **索引设计原则**： - OR 涉及的字段均需独立索引 - 避免在索引列使用函数或计算 3. **监控执行计划**： ```sql EXPLAIN SELECT ... -- 检查是否使用索引[^1] ``` 4. **控制结果集大小**： - 添加 `LIMIT` 分页 - 使用 `WHERE` 提前过滤 > **关键结论**：OR 是否导致索引失效取决于**所有连接字段的索引状态**和**操作符类型**。通过查询拆分、索引覆盖和临时表技术可有效解决[^2][^3]。 --- ### 相关问题 1. **如何验证 SQL 语句是否使用了索引？** - 使用 `EXPLAIN` 分析执行计划 - 观察 `type` 列是否为 `ref/range` - 检查 `key` 列是否显示索引名称[^1] 2. **UNION 和 UNION ALL 在优化中的性能差异？** - `UNION ALL` 不排序去重，性能提升 30-50% - 业务允许重复数据时优先选 `UNION ALL` 3. **覆盖索引在 OR 优化中的局限性是什么？** - 仅适用于查询字段全在索引中 - 宽字段索引会显著增加存储开销 - 更新频繁的表需谨慎使用[^4] 4. **MySQL 和 PostgreSQL 处理 OR 索引失效的机制有何不同？** - PostgreSQL 支持 Bitmap Index Scan 合并索引 - MySQL 依赖 Index Merge 优化（需显式开启） - 两者在 NULL 处理上存在差异 5. **如何优化包含多个 OR 条件的复杂查询？** - 使用物化视图预计算 - 拆分到程序层分步执行 - 考虑 NoSQL 替代方案（如 Elasticsearch）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值