MySQL无法命中索引的情况总结

最新推荐文章于 2024-01-21 15:37:19 发布
原创 最新推荐文章于 2024-01-21 15:37:19 发布 · 2.6k 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #索引

本文详细解析了导致数据库索引失效的七种常见情况，包括使用or关键字、左前导查询、单列索引的索引列为null、组合索引不符合左前缀原则、强制类型转换、全表查询估计更快以及负向查询条件。了解这些情况有助于优化查询效率，避免索引失效。
  1. 使用or关键字会导致无法命中索引
  2. 左前导查询回到著无法命中索引，如 like '%a'或者 like '%a%'
  3. 单列索引的索引列为null时全值匹配会使索引失效,组合索引全为null时索引失效
  4. 组合索引不符合左前缀原则的列无法命中索引，如我们有4个列a、b、c、d，我们创建一个组合索引INDEX(`a`,`b`,`c`,`d`),那么能命中索引的查询为a,ab,abc,abcd,除此之外都无法命中索引
  5. 强制类型转换会导致索引失效
  6. 如果mysql估计全表查询会比使用索引快，那么则不会使用索引
  7. 负向查询条件会导致无法使用索引，比如NOT IN,NOT LIKE,!=等
MySQL索引详细总结（B+树、聚簇索引、是否命中）
weixin_62756510的博客
06-22 1341
详解MySQL索引数据结构，总结笔记
MySQL联合索引（abc）命中规则
qq_34486648的博客
02-23 7267
1.建表 mysql创建一张表，表名：‘test_models’ id列为 主键，int类型 ，自增 a,b,c,d,e 全部是int（11） 为（a,b,c）添加一个联合索引 index_abc 执行语句： CREATE TABLE `test_models` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) DEFAULT NULL,
MySQL索引失效的几种情况
weixin_30909575的博客
11-24 385
1.索引无法存储null值 　　a.单列索引无法储null值，复合索引无法储全为null的值。 　　b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。 　　为什么索引列无法存储Null值？ 　　a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null值是不确定值无法　　 　　比较，无法确定...
MySQL排序规则导致无法命中索引问题
FirstMrRight的博客
03-18 1735
MySQL 没有找到好的索引可以使用，但发现在知道前面表的列值后，可能会使用某些索引。在SQL的关联条件中，关联字段类型相同，并不是隐式类型转换问题导致无法命中索引，那么我们开始排查两表的字符集、排序规则是否一致。，不会出现因字符集不同导致隐式转换的问题，那么对比排序规则发现两表的排序规则是不同的，排序规则不一致时，但在key项中，却没有展示执行过程中命中的索引，而是扫描了全表，在Extra信息中，出现了。方案二：将原表重命名备份， 修改原建表语句为正确的排序规则，执行建表语句，而后使用。
mysql 索引命中规则 不命中的情况
weixin_40849725的博客
12-01 3484
mysql 索引命中规则多列索引 遵循：最左匹配原则不会命中索引的情况 当一条sql语句提交给mysql数据库进行查询的时候需要经历以下几步 1、先在where解析这一步把当前的查询语句中的查询条件分解成每一个独立的条件单元 2、mysql会自动将sql拆分重组 3、然后where条件会在B-tree index这部分进行索引匹配，如果命中索引，就会定位到指定的table records位置。如果...
mysql索引命中查看_MySQL索引无法命中的几种情况及索引验证方法
tangsiqi130的博客
01-21 1393
mysql索引命中查看_MySQL索引无法命中的几种情况及索引验证方法
Mysql 中查询数据什么情况下不会命中索引？需要怎么优化呢？
猫轻王的博客
02-23 2612
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。也是我们工作与学习中需要不断提升自己的方面，这可以让我们追求性能，写出运行最快、占用内存最小、最优质的代码的第一步。
解决MySQL中IN子查询会导致无法使用索引问题
12-15
随后动手按照他说的做了一个表来测试验证，发现MySQL的IN子查询做的不好，确实会导致无法使用索引的情况（IN子查询无法使用所以，场景是MySQL，截止的版本是5.7.18） MySQL的测试环境 测试表如下 create table ...
深入浅析Mysql联合索引最左匹配原则
09-09
在这种情况下，MySQL会扫描索引中所有col2为2的行，效率相对较低，因为需要全索引扫描。 联合索引的一个重要优点是它可以减少开销。创建一个联合索引(col1,col2,col3)等同于创建了(col1), (col1,col2), 和 (col1,...
MySQL中因字段字符集不同导致索引不能命中的解决方法
09-09
如果在查询中，两个字段使用了不同的字符集，即使内容相同，数据库可能无法识别它们是一致的，因此索引可能无法命中。 在上述问题中，描述了一个实际案例：一个SQL查询在执行时没有利用到Orders表上的GuidNo字段的...
mysql 的 in 不一定不走索引
qq_40929531的博客
06-29 4021
很早之前就听别人说in 不走索引，但是一直没证实过，昨天在工作中用到了in所以在上班的时候突发奇想，使用mysql 的explain 关键字看下一条sql走不走索引，sql 如下所示： 如上图所示，这里用到了in,但是这条sql 还是走了索引，如下图：为什么会这样？参考这篇文章：MySQL中in到底走不走索引？_一个技术渣渣的博客-优快云博客_in走不走索引explain介绍mysql中explain关键字可以模拟MySQL优化器执行SQL语句，是一个可以很好的分析SQL语句或表结构的性能瓶颈。ex
两类非常隐蔽的全表扫描，不能命中索引（一分钟系列）
shenjian58的博客
07-01 580
《MySQL死锁分析的两个工具》中，举了一个强制类型转换导致死锁的例子，有朋友询问是不是类型转换都不能命中索引，花1分钟细说一下。第一类：“列类型”与“where值类型”...
mysql中的强制索引_你如何强制MySQL中的查询使用索引？
weixin_34388311的博客
02-27 395
我试图通过向慢查询日志中出现的查询添加索引来提高锤击的wordpress DB的性能.在MS SQL中,您可以使用查询提示强制查询使用索引,但如果正确覆盖列,则通常很容易获得查询以使用索引.我有这个查询出现在慢查询日志中很多SELECT SQL_CALC_FOUND_ROWS wp_posts.IDFROM wp_postsWHERE 1=1AND wp_posts.post_type = 'p...
mysql索引优化-强制走索引
u014563917的博客
12-24 1155
‐‐ 执行时间0.444s7 SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表 扫描高，因为回表效率不高。结论：联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不 如就全表扫描。‐‐ 执行时间0.333s。
MySQL强制使用索引的两种方式及优化索引，使用MySQL存储过程创建测试数据。
热门推荐
川子的博客
09-26 2万+
MySQL强制使用索引的两种方式，MySQL创建存储过程、MySQL慢SQL语句定位、常见的索引优化方式、索引失效原因的大致汇总
Mysql 索引命中规则
xfjjs_net的博客
08-24 629
1.like前缀命中，如 xx% ,,而%xx%,%xx 无法命中索引 2.or 应该无法命中索引 3.在where语句中 使用!= 或< >这种操作符，无法命中索引 4.where语句中，做null值判断，无法命中 5.组合索引的话，如(a,b,c)上建立组合索引,则a|(a,b)|(a,b,c)能命中索引(最左匹配原则) 比如你select * from table where b...
MySQL无法命中索引的条件
H_sen's Blog
05-11 1352
MySQL无法命中索引的情况总结 如果条件中有 or ，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因） 注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 如果出现OR的一个条件没有索引时，建议使用 union ，拼接多个查询语句 like查询是以%开头，索引不会命中 只有一种情况下，只查询索引列，才会用到索引，但是这种情况下跟是否使用%没有关系的，因为查询索引列的时候本身就用到了索引 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使
无法命中索引的几种情况
小龙君OvO的博客
10-07 2495
无法命中索引的几种情况 正确使用索引 数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。 即使建立索引，索引也不会生效： 1. like '%xx' select * from tb1 where name like='%cn'; 2. 使用函数 select * from tb1 where reverse(name) = 'giao'; 3. or select * from tb1 where n
Mysql范围查询时索引失效
~
05-05 1486
列上创建了索引，但Mysql没有使用该索引，可能是由于查询返回的数据行太多，Mysql认为全表扫描比使用索引更快。这种情况下，可以尝试强制Mysql使用索引。如果Mysql仍然不使用索引，可能需要进一步调整查询语句，例如添加更多限制条件或拆分查询，以便Mysql可以更有效地使用索引。如果已经在范围查询字段。
MySQL索引命中 索引回表 索引下推
最新发布
04-03
<think>好的，我现在需要解释MySQL中的“索引命中”、“索引回表”和“索引下推”这三个概念。首先，我得确保自己正确理解每个术语的含义，然后组织语言用中文清晰地表达出来，同时遵循用户提供的格式要求，比如行内公式用$...$，独立公式用$$...$$，并且结构要清晰。 首先，索引命中。这应该是指当执行查询时，MySQL能够利用索引来快速定位数据，而无需全表扫描。比如，如果有一个索引在name字段上，查询条件WHERE name = 'Alice'就会命中这个索引。需要说明索引的结构，比如B+树，以及如何通过索引快速查找数据行。 接下来是索引回表。当查询的列不在索引中时，即使命中了索引，MySQL还需要根据索引中的主键值回到主键索引（聚簇索引）中查找完整的行数据。这个过程称为回表。例如，索引是(name)，但查询需要获取age，这时候就需要回表。这里可能需要提到覆盖索引的概念，即如果索引包含所有需要的字段，就可以避免回表。 然后是索引下推（Index Condition Pushdown，ICP）。这是在MySQL 5.6引入的优化技术，允许在存储引擎层提前过滤不符合条件的行，减少回表次数。比如，查询条件WHERE name LIKE 'A%' AND age > 20，如果索引在(name, age)上，在没有ICP的情况下，存储引擎会先找到所有name以A开头的主键，然后回表获取age再过滤。而有了ICP，存储引擎会直接在索引中检查age条件，减少回表的数据量。需要解释ICP的作用和如何提升性能。 接下来需要考虑如何将这些内容结构化，分点说明，每个部分可能还需要例子或场景说明。同时注意用户提到的格式要求，行内数学表达式比如索引结构可能需要用LaTeX，例如B+树的高度$h$，查找复杂度$O(\log n)$等。不过根据用户给的例子，可能不需要太复杂的公式，所以这里可能需要酌情处理。 需要检查是否有遗漏的关键点，比如索引的结构、回表的原因、覆盖索引、ICP的工作原理和版本支持。同时确保语言通俗易懂，避免技术术语堆砌，但又要准确。可能还需要对比有无ICP的情况下的执行流程差异，以突出其优化效果。 最后，确保回答符合用户的结构要求：分点解释每个概念，可能包括定义、原理、示例、影响或优化建议。同时使用中文，避免Markdown格式，但保持段落清晰，可能用序号或标题来分隔各个部分。同时注意LaTeX的正确使用，比如行内公式用$...$，独立公式如果需要的话用$$...$$，但根据用户的问题，可能不需要太多公式，主要是在解释索引结构或查询过程时可能需要。 总结一下，回答的结构大致如下： 1. 索引命中 - 定义：查询条件使用索引快速定位数据 - 原理：B+树结构，减少扫描行数 - 示例：WHERE子句使用索引列 - 注意点：最左前缀原则，索引选择性 2. 索引回表 - 定义：需要回主键索引取数据 - 原理：二级索引存储主键值 - 示例：查询列不在索引中 - 优化：覆盖索引 3. 索引下推 - 定义：存储引擎层提前过滤 - 原理：在索引遍历时应用WHERE条件 - 示例：组合索引，部分条件在索引中处理 - 优势：减少回表次数，提升性能 在写作过程中，需要检查每个部分是否准确，是否有错误。例如，索引下推是否仅适用于组合索引，还是单列索引也可以？实际上，ICP适用于索引能够覆盖的列，不管是否是组合索引，但通常组合索引更能体现其优势。另外，需要确认MySQL版本支持情况，ICP从5.6开始支持。 可能还需要提到执行计划中的Using index condition来表示ICP的使用，而Using index表示覆盖索引。这些细节能帮助用户更好地理解如何诊断和优化查询。 最后，确保语言流畅，逻辑清晰，符合用户的要求，分点讲解，帮助用户逐步理解这三个概念。</think>### MySQL索引命中、索引回表、索引下推详解 #### 一、索引命中 1. **定义** 当查询条件**完全或部分匹配索引结构**时，MySQL可通过索引快速定位数据，避免全表扫描。例如： ```sql SELECT * FROM users WHERE name = 'Alice'; -- 若name字段有索引，则命中 ``` 2. **原理** - 索引通常采用$B^+$树结构，树高$h$决定查询复杂度为$O(\log n)$。 - **最左前缀原则**：联合索引$(a, b, c)$只能按$a$ → $a+b$ → $a+b+c$的顺序使用。 - **索引选择性**：唯一性高的字段（如ID）更适合建索引。 3. **示例** - 命中：`WHERE a=1 AND b=2`（联合索引$(a,b)$） - 未命中：`WHERE b=2`（未从最左字段$a$开始） --- #### 二、索引回表 1. **定义** 当索引**未覆盖查询所需全部列**时，需根据索引中的主键值**回主键索引（聚簇索引）**获取完整行数据。 2. **原理** - 二级索引（如`INDEX(name)`）仅存储主键值。 - 若查询需获取`age`字段（未包含在索引中），流程如下： ```sql 1. 通过二级索引找到name='Alice'对应的主键id=100 2. 回主键索引查找id=100的行，获取age=25 ``` 3. **优化：覆盖索引** 将查询列全部加入索引，例如： ```sql CREATE INDEX idx_name_age ON users(name, age); SELECT age FROM users WHERE name = 'Alice'; -- 无需回表 ``` --- #### 三、索引下推（ICP，Index Condition Pushdown） 1. **定义** MySQL 5.6+引入的优化技术，**将WHERE条件过滤提前到存储引擎层执行**，减少回表次数。 2. **原理** - **无ICP**：存储引擎仅按索引最左前缀筛选数据，剩余条件由Server层处理。 - **有ICP**：存储引擎直接利用索引中的字段过滤数据，减少传递给Server层的数据量。 3. **示例** ```sql -- 表结构：INDEX(name, age) SELECT * FROM users WHERE name LIKE 'A%' AND age > 20; ``` - **无ICP**：先找到所有`name LIKE 'A%'`的主键，回表后由Server层过滤`age > 20`。 - **有ICP**：存储引擎在索引中直接过滤`name LIKE 'A%' AND age > 20`，仅回表符合条件的主键。 4. **优势** - 减少回表次数，降低I/O开销。 - 执行计划显示`Using index condition`即表示触发ICP。 --- ### 总结对比 | 概念 | 触发场景 | 优化目标 | 关键标志 | |------------|------------------------------|-------------------|-----------------------| | 索引命中 | 查询条件匹配索引结构 | 减少扫描行数 | `Using index` | | 索引回表 | 查询列未完全被索引覆盖 | 避免回表 | 无 | | 索引下推 | WHERE条件可被索引部分覆盖 | 减少回表次数 | `Using index condition` | 通过合理设计索引（如覆盖索引、联合索引）和利用ICP，可显著提升查询性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值