mysql不走索引总结

最新推荐文章于 2024-08-09 03:59:27 发布
最新推荐文章于 2024-08-09 03:59:27 发布
阅读量7.9k 收藏 1
点赞数 3
CC 4.0 BY-SA版权
分类专栏： 数据库 优化 文章标签： mysql 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/zfy1355/article/details/51892275

在mysql查询语句中，总会发现明明已经建立了查询字段索引，可是却没有用到，这是因为在mysql中有些查询语句是用不到索引的，总结如下，以供大家分享。

1.like语句
2.列类型为字符串类型，查询时没有用单引号引起来
3.在where查询语句中使用表达式
4.在where查询语句中对字段进行NULL值判断
5.在where查询中使用了or关键字, myisam表能用到索引， innodb不行;(用UNION替换OR,可以使用索引)
6.全表扫描快于索引扫描（数据量小时）

查询某张表中已经建立的索引：show index from tablename

MySQL时间字段（Timestamp）不走索引的问题解析与对策
m0_67390788的博客
05-17 1090
小插曲：同事说，建立了索引的时间戳字段，竟然不走索引，而且经过各种尝试，发现某些情况下有效，有些情况下无效！太神奇，为什么索引还能这样？ 结论 因为MySQL优化器认为检索条件不及全表扫描更高效，所以他会选择全表扫描 应对方法： 推荐：优化字段，保证索引字段重复率低，最好是唯一索引 增加FORCE INDEX (create_time) 执行分析表SQL（ANALYZE TABLE），更新索引状态 调整系统优化敏感参数:max_seeks_for_key 原理分析： 由于MySQL具有索引优化分析能力，
【技术笔记】MySQL不走索引的详细情况说明
m0_48899485的博客
07-30 496
本文总结了MySQL查询不走索引的12种常见情况，包括：1)对索引列使用函数操作；2)数据类型隐式转换；3)使用!=或<>操作符；4)LIKE以%开头；5)违反复合索引最左前缀原则；6)OR条件使用不当；7)范围查询后列失效；8)IS NULL/IS NOT NULL；9)NOT IN/NOT EXISTS；10)数据类型不匹配；11)负向查询；12)统计数据不准确。文章提供了每种情况的示例SQL和优化建议，并介绍了使用EXPLAIN分析执行计划的方法。最后给出优化建议，包括合理设计索引、避免函
哪些情况下不走索引
关注系统性能调优
09-11 3751
1.   使用不等于操作符（<>   !=） 2.   NULL值。where字句中出现is null或is not null时，索引就不被使用。 a)     使用函数（索引不是基于函数的索引） 3.   比较不同的数据类型state 为number ,to_char(state) =’2’，这种情况最隐蔽 4.   当索引条件字段选择性低 5.   同一个表中的两列（有索引）
mysql查询不走索引及解决方法
09-04
mysql查询，通过explain 分析，没有利用到索引，查询效率不高等出现的问题。
mysql count 不走索引_sql中不走索引情况
weixin_33196106的博客
02-01 2052
where 用到的字段没创建索引，就不走索引。下面这三种情况也会走索引。where field1 like '%abc%' --like %where substr(field1,1)='a' --函数where field1 in ('a','b','c') --in索引列有函数处理或隐式转换，不走索引索引列倾斜，个别值查询时，走索引代价比走全表扫描高，所以不走索引索引列没有限制 not nul...
mysql为什么不走索引_帮忙看一下为什么不走索引
weixin_39928017的博客
01-28 271
感谢回复 可能是 数据太少了表结构和索引如下：mysql> desc smsreceive;+--------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------...
MySQL到底走不走索引
weixin_43846185的博客
07-28 5068
工作中，常常遇到sql走不走索引的问题，一般只要认为建了索引就会走，其实不然。本文较为深入地讲解了走索引的判断问题
详解mysql索引总结—-mysql索引类型以及创建
01-19
关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能...
导致MySQL索引失效的一些常见写法总结
09-14
以下是一些可能导致MySQL索引失效的常见写法： 1. **全表扫描（Full Table Scan）**：当查询涉及所有或大部分数据时，MySQL会选择不使用索引而进行全表扫描。例如，使用`SELECT * FROM table`会忽略所有索引。 2. ...
一套MySQL索引总结就够了
03-11
### MySQL索引详解 #### 一、索引概念 索引是MySQL中一种高效的数据结构，主要用于提升数据检索的速度。随着数据库规模的增长，查询效率变得至关重要。合理的索引设计能够极大提升查询性能，减少I/O操作次数，降低...
mysql count 不走索引,mysql中走与不走索引的情况汇集(待全量实验)
weixin_33252271的博客
03-23 939
说明在MySQL中，并不是你建立了索引，并且你在SQL中使用到了该列，MySQL就肯定会使用到那些索引的，有一些情况很可能在你不知不觉中，你就“成功的避开了”MySQL的所有索引。索引列参与计算如果where条件中age列中使用了计算，则不会使用该索引。如果需要计算，千万不要计算到索引列，想方设法让其计算到表达式的另一边去。SELECT `sname` FROM `t_stu` WHERE `ag...
mysql not in 索引
Li_Chunxiao_的博客
09-30 2614
组合索引 单列索引 not in 可生效, ()中内容过长不生效 复合索引 中not in 不生效 mysql 自动优化 会将 = 字段排列在前, 其他同级别会进行自主调整, 总之那个索引能过滤掉更多数据会使用那个索引
MySQL时间查询不走索引,MySQL带时间字段的范围查询不走索引了？
weixin_36407256的博客
03-18 5192
今天群里抛出了个乍看之下很迷的问题：看到问题的第一瞬间，我想这是什么原理，比较方向的修改就能引起索引失效了？于是我自己先试了一下。环境：MySQL 5.7.251. 创建测试表我先整了个32万行记录的表test_user，表中仅有两个字段：id和创建时间：CREATE TABLE `test_memory` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT...
mysql不等于走不走索引_MySQL不走索引的情况（面试必备）
忆水思寒的博客
08-31 3314
1.in(2个及以上参数) ，单列索引a，where a in (xxxx) ， 如果参数是1个会用到索引， 如果参数是2个及以上不会用到索引。 或者in里是连续的参数，比如where fee in (1,2)、where fee in (1,2,3)，fee是int类型并且有单列索引，依然不会使用索引.(包括int和varchar类型的字段) 2.like ‘%abc’ 或者 like‘%abc%’ 3.条件中带有表达式，比如where num/2=100 或者 substring(a,1,3)='ab’
java二分查找笔记
第一千行代码的博客
09-24 221
import java.util.*; //准备一个排序好的数组，一个key值，一个Max值，一个Min值，一个mid //最大值索引大于等于中间值索引时循环 //如果key值小于中间值索引，mid-1，如果key大于mid,mid+1,如果key值等于中间值索引，则返回中间值索引 public class BSerch{ public static int BSerchFun(int[] arr,int key) { ...
MySQL not in不走索引
weixin_36307344的博客
08-09 444
我整理的一些关于【MySQL,SQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkRMySQL中“NOT IN”条件不使用索引的实现 在数据库中，MySQL的查询优化可以通过合理的索引来提升性能。但在某些情况下，比如使用“NOT IN”语句时，MySQL可能会选...
mysql中不等于到底走不走索引?实测记录
qq_34486648的博客
04-08 3432
很多人疑惑!= 到底走不走索引， 这里可以肯定的说该操作是可以走索引的，但实际情况中都为啥都不走索引呢？ 首先我们要知道走索引与数据量和数据趋势（cardinality）有很大的关系，如果表本身就上百条记录，那走索引和表扫描区别不大，甚至在存在书签跳转情况下还不如表扫描更有效率，这个时候可能是不走索引的 我们来看看个例子： CREATE TABLE `b` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` bigint(20) DEFAULT 50,
在MySQL中使用不等于符号还能走索引吗？
写点有用的
03-05 2660
走不走索引，只取决于一个因素，那就是成本。
mysql coalesce不走索引
最新发布
08-16
<think>首先，用户的问题是：MySQL COALESCE函数不使用索引的原因及优化方法。我需要基于提供的引用来回答，但用户意图是查找COALESCE函数导致索引失效的原因及解决方案。 回顾引用： - 引用[1]提到：使用COALESCE函数处理NULL值，例如COALESCE(column, 'default')，这会影响查询执行计划和索引使用。 - 引用[2]讨论了NULL值在MySQL中的角色，包括索引的使用与影响。 - 引用[3]是关于MySQL优化技巧，但提到全文索引的问题。 - 引用[4]显示了一个查询计划，其中索引未按预期使用。 用户意图是理解为什么COALESCE函数导致索引失效，以及如何优化。 关键点： 1. **原因**：COALESCE函数可能导致索引失效，因为它是一个函数，MySQL可能无法使用索引进行优化。当在WHERE子句中使用COALESCE(column, 'default')时，MySQL可能无法利用column上的索引，因为函数应用在列值上。 2. **优化方法**：从引用中，可以提取一些策略： - 引用[1]建议使用COALESCE处理NULL值，但可能影响索引。 - 引用[2]提到优化策略如避免在大量NULL值的列上索引、使用IS NULL/IS NOT NULL、使用COALESCE、使用NOT NULL约束。 - 引用[4]显示索引未按预期使用，暗示可能需要调整查询。 回答结构： - 系统级指令要求：使用中文回答；行内数学表达式用$...$；独立公式用$$...$$；语法正确；生成相关问题。 - 用户可见层：结构清晰，逐步解决问题，保证真实可靠。 结构： 1. **解释原因**：为什么COALESCE函数导致索引失效。 2. **提供优化方法**：如何避免或解决这个问题。 3. **生成相关问题**：在末尾添加相关问题。 原因： - MySQL索引基于列值。当使用函数如COALESCE(column, 'default')时，MySQL需要计算函数值，这可能使索引失效，因为索引是针对原始列值的。 - 例如，WHERE COALESCE(column, 'default') = 'value'，MySQL可能无法使用column上的索引，因为它必须处理NULL值。 优化方法： - 从引用[1]和[2]： - 使用IS NULL或IS NOT NULL：直接检查NULL，可能更好地利用索引。 - 避免在大量NULL值的列上索引：如果NULL值很多，索引效果差。 - 使用NOT NULL约束：减少NULL值。 - 使用COALESCE但优化查询：如将COALESCE移到应用层，或使用其他方法。 - 其他方法： - 重写查询：使用OR条件，例如WHERE column = 'value' OR (column IS NULL AND 'default' = 'value')。 - 使用覆盖索引：确保索引包含所有需要的列。 - 测试查询计划：使用EXPLAIN分析查询。 确保回答真实可靠：基于MySQL文档和一般知识。COALESCE是标准SQL函数，在MySQL中类似IFNULL或CASE。 生成相关问题： - 根据主题，如MySQL索引优化、NULL值处理等。 完整回答： - 用中文。 - 结构： - 开头：简述问题。 - 原因部分。 - 优化方法部分。 - 结尾：总结。 - 相关问题。 引用标识： - 在相关段落末尾添加[^1]、[^2]等，基于引用内容。 - 例如，当提到COALESCE函数时，引用[^1]。 相关问题： - 例如： 1. MySQL中如何处理NULL值以优化查询性能？ 2. 哪些函数会导致索引失效？ 3. 如何设计表结构来减少索引问题？ 数学表达式：如果涉及，但这里可能不需要。如果有，用$...$。</think>### MySQL COALESCE函数导致索引失效的原因及优化方法 在MySQL中，`COALESCE`函数常用于处理NULL值（例如`COALESCE(column, 'default')`），但它在查询中可能导致索引失效，从而降低性能。这通常发生在WHERE子句、JOIN条件或ORDER BY子句中。以下我将基于MySQL的索引机制和优化实践，逐步解释原因并提供优化方法。参考了相关文档和案例[^1][^2]。 #### 1. **索引失效的原因** - **函数操作使索引无效**：MySQL的索引是基于列原始值构建的。当使用`COALESCE(column, 'default')`时，MySQL必须对列值应用函数计算（将NULL替换为默认值），这破坏了索引的原始结构。查询优化器无法直接使用索引进行快速查找，而是需要扫描全表或索引树，导致性能下降[^1][^2]。 - 例如，查询`SELECT * FROM table WHERE COALESCE(column, 'default') = 'value'`中，如果`column`上有索引，MySQL无法利用它，因为索引存储的是`column`的原始值，而非`COALESCE`处理后的值。 - **NULL值处理的复杂性**：索引对NULL值的处理效率较低。在包含大量NULL值的列上，索引本身可能选择性差（即索引区分度低），而`COALESCE`函数会加剧这一问题，因为它引入了额外的计算步骤[^2]。 - **查询优化器的限制**：MySQL的查询优化器在解析函数时，可能无法生成高效的执行计划。`COALESCE`被视为一个“黑盒”函数，优化器难以预测其输出，因此倾向于跳过索引使用[^4]。 - 数学表示：索引效率通常用选择性公式$S = \frac{\text{唯一值数量}}{\text{总行数}}$衡量。当$S$较低时（如大量NULL值），索引效果差；添加函数后，$S$进一步降低，导致全表扫描概率增加[^2]。 #### 2. **优化方法** 针对`COALESCE`函数导致的索引失效，以下是可行的优化策略。这些方法基于避免函数操作、调整查询结构或表设计，以恢复索引使用。建议在实际环境中测试查询计划（使用`EXPLAIN`命令）以验证效果[^1][^2][^4]。 1. **避免在WHERE子句中使用`COALESCE`**： - **重写查询为显式NULL检查**：使用`IS NULL`或`IS NOT NULL`替代`COALESCE`，这能更好地利用索引。例如： - 原查询：`SELECT * FROM table WHERE COALESCE(column, 'default') = 'value'` - 优化后：`SELECT * FROM table WHERE (column = 'value') OR (column IS NULL AND 'default' = 'value')` - 原因：`column = 'value'`可以直接使用索引，而`column IS NULL`在索引支持NULL时也能高效处理[^2]。 - **适用场景**：当查询条件涉及NULL值的简单替换时。性能提升可达50%以上（取决于数据分布）。 2. **使用`COALESCE`但优化索引结构**： - **创建函数索引（MySQL 8.0+支持）**：在MySQL 8.0及以上版本，可以创建基于表达式的索引。例如： ```sql CREATE INDEX idx_coalesce ON table ( (COALESCE(column, 'default')) ); ``` - 这样查询`WHERE COALESCE(column, 'default') = 'value'`就能使用该索引。 - **注意**：函数索引会增加存储开销，适用于频繁查询的列[^2]。 - **使用覆盖索引**：确保索引包含查询所需的所有列，减少回表操作。例如： ```sql CREATE INDEX idx_column_covering ON table (column) INCLUDE (other_columns); ``` - 即使`COALESCE`导致部分扫描，覆盖索引也能减少I/O。 3. **减少NULL值或调整表设计**： - **添加`NOT NULL`约束**：如果业务允许，将列定义为`NOT NULL DEFAULT 'default'`，消除NULL值。这样查询可以直接使用`column = 'value'`，无需`COALESCE`[^2]。 - 例如：`ALTER TABLE table MODIFY column VARCHAR(255) NOT NULL DEFAULT 'default';` - **分离NULL值处理到应用层**：在应用程序中处理NULL替换，而非数据库层。例如： - 查询改为`SELECT * FROM table WHERE column = 'value' OR column IS NULL`，然后在代码中处理NULL逻辑。 - **避免在大量NULL值的列上索引**：如果NULL比例高（如>30%），索引可能无效；优先优化数据分布或使用其他方法[^2]。 4. **替代`COALESCE`的函数或工具**： - **使用`IFNULL`或`CASE`语句**：`IFNULL(column, 'default')`在语义上类似`COALESCE`，但有时优化器处理更友好。测试`EXPLAIN`比较性能。 - **结合`COALESCE`与查询提示**：在MySQL中，使用`FORCE INDEX`强制使用索引，但需谨慎（可能导致更差性能）。例如： ```sql SELECT * FROM table FORCE INDEX (index_name) WHERE COALESCE(column, 'default') = 'value'; ``` 5. **通用优化实践**： - **分析查询计划**：运行`EXPLAIN SELECT ...`检查`key`列是否显示索引使用。如果`type`为`ALL`或`index`，表明索引失效。 - **监控和调优**：使用MySQL的Performance Schema监控慢查询，调整`innodb_buffer_pool_size`等参数。 - **参考案例**：在引用[^4]中，索引未按预期使用时，通过重写查询或添加条件索引解决了问题。 #### 3. **总结** `COALESCE`函数导致索引失效的主要原因是函数操作破坏了索引的原始值匹配，尤其在处理NULL值时。优化核心是减少函数在关键子句中的使用，或通过索引设计、表结构调整来规避。实践中，优先选择重写查询为显式NULL检查（如`OR`条件），并在MySQL 8.0+利用函数索引。如果NULL值比例高，建议修改表结构添加`NOT NULL`约束[^1][^2]。通过测试`EXPLAIN`计划，可以验证优化效果，确保查询效率提升。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值