使用覆盖索引解决 LIKE以%开头会导致索引失效问题

最新推荐文章于 2024-08-29 17:37:53 发布
最新推荐文章于 2024-08-29 17:37:53 发布
阅读量1.5k 收藏 4
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/A980719/article/details/122863342
版权
本文通过多个示例分析了在MySQL中，当使用LIKE '%...%'查询时，如何利用覆盖索引来避免索引失效导致的全表扫描。结论指出，只有当模糊查询条件字段及查询返回的所有字段都在索引列上，才能有效利用索引。
摘要由CSDN通过智能技术生成

1 建立索引

CREATE INDEX idx_nameAgePos ON staff(name, age, pos);
1.1 测试&Explain分析

IndexCase#1：查询Id

EXPLAIN SELECT id FROM staff WHERE name LIKE ‘%Alice%’;
在这里插入图片描述

结果：使用上了索引（因为 name 有索引，同时查询的 Id 是主键肯定也有索引）

IndexCase#2：查询name

EXPLAIN SELECT name FROM staff WHERE name LIKE ‘%Alice%’;

在这里插入图片描述

结果：使用上了索引（因为查询条件和查询字段都是有索引的 name）

IndexCase#3：查询age

EXPLAIN SELECT age FROM staff WHERE name LIKE ‘%Alice%’;

在这里插入图片描述

结果：使用上了索引（因为查询条件的 name 以及查询字段的 age 都有索引）

IndexCase#4：查询 id & name

EXPL

最低0.47元/天 解锁文章
一叶一菩提魁
关注
专栏目录
索引法则--LIKE以%开头会导致索引失效进而转向全表扫描（使用覆盖索引解决）...
weixin_34081595的博客
04-24 5542
Mysql系列文章主页 =============== 1准备数据 1.1建表 DROP TABLE IF EXISTS staff; CREATE TABLE IF NOT EXISTS staff ( id INT PRIMARY KEY auto_increment, name VARCHAR(50), age INT, ...
like使用索引如何避免失效
weixin_30414305的博客
07-18 571
1准备数据 1.1建表 DROP TABLE IF EXISTS staff; CREATE TABLE IF NOT EXISTS staff ( id INT PRIMARY KEY auto_increment, name ), age INT, pos ) COMMENT '职位', salary ,) ); 1.2插入数据 , ); ...
数据库查询导致索引失效情况
最新发布
精神小牛马
08-29 1639
查询age and classId and name时，(age,classId,name)索引比(age,classId)快。联合索引把频繁查询的列放左。索引（a,b,c），只能查(a,b,c),(a,b),(a)。如果主键不自增，需要查找目标位置再插入，并且如果目标位置所在数据页满了就必须得分页，造成性能损耗。计算例如where num+1=2，函数例如abs(num)取绝对值例如name=123，而不是name='123'。又例如使用了不同字符集。
mysql 覆盖索引_MySQL SQL优化之覆盖索引
weixin_26803527的博客
01-18 235
原标题：MySQL SQL优化之覆盖索引内容概要利用主索引提升SQL的查询效率是我们经常使用的一个技巧，但是有些时候MySQL给出的执行计划却完全出乎我们的意料，我们预想MySQL会通过索引扫描完成查询，但是MySQL给出的执行计划却是通过全表扫描完成查询的，其中的某些场景我们可以利用覆盖索引进行优化。前些天，有个同事跟我说：“我写了个SQL，SQL很简单，但是查询速度很慢，并且针对查询条件创建了...
数据库基础问答（二） —— 为什么like不会像范围查询那样使索引失效
ProLayman的博客
11-01 1069
先看例子： 建立了name_age_address组合索引： 最普通的全匹配索引查询： 最左匹配不用多说； name_age_address相当于建立了三个索引： name索引，name_age索引，name_age_address索引。 可以分别看一下长度： name —— 377： name_age —— 381： name_age_address —— ...
MySQL 使用 like “%x“，索引一定会失效吗？
小林coding
01-25 8377
大家好，我是小林。 昨天发了一篇关于索引失效的文章：谁还没碰过索引失效呢 我在文末留了一个有点意思的思考题： 这个思考题其实是出自于，我之前这篇文章「一条 SQL 语句引发的思考」中留言区一位读者朋友出的问题。 很多读者都在留言区说了自己的想法，也有不少读者私聊我答案到底是什么？ 所以，我今晚就跟大家聊聊这个思考题。 题目一 题目一很简单，相信大家都能分析出答案，我昨天分享的索引失效文章里也提及过。 **「题目 1 」**的数据库表如下，id 是主键索引，name 是二级索引，其他字段都是非索引字段。
MySQL Like模糊查询速度太慢如何解决
12-14
- 在Oracle中，对于`LIKE '%%'`应避免使用，因为这会导致全表扫描。 - 对于`LIKE '%...%'`（不以%结尾），可以利用`REVERSE`函数和反向索引来优化为`LIKE '%'`的形式。 - 对于非`'%%'`的`LIKE`查询，可以考虑使用...
oracle索引失效的总结
08-17
然而，在实际的应用过程中，由于多种原因可能会导致索引失效，从而影响系统的性能。本文将详细介绍Oracle索引失效的一些常见原因，并提供相应的解决策略。 #### 1. 使用函数或表达式对列进行操作 当在WHERE子句中...
索引法则--LIKE以%开头会导致索引失效进而转向全表扫描（使用覆盖索引解决）
qq_43183527的博客
12-28 1125
1 准备数据 1.1 建表 DROP TABLE IF EXISTS staff; CREATE TABLE IF NOT EXISTS staff ( id INT PRIMARY KEY auto_increment, name VARCHAR(50), age INT, pos VARCHAR(50) COMMENT ‘职位’, salary DECIMAL(10,2) ); 1.2 插入数据 INSERT INTO staff(name, age, pos, salary) VALUES(‘Alic
为什么Mysql 在覆盖索引下的like ‘%xxx%’查询能使用到索引，原理是什么？
weixin_43947156的博客
01-07 936
https://segmentfault.com/q/1010000022763847
mysql高级----索引优化（二）：索引失效案例（应该避免）
阿官
03-05 2488
建表SQL索引情况一、全值匹配我最爱二、最佳左前缀法则1、where条件列的，从索引的最左前列开始，且不跳过索引中的列2、违背原则：未以最左前列开始，索引失效3、违背原则：跳过索引中间列（age），只引用了部分索引结论：where条件要满足最佳左前缀法则。口诀：带头大哥不能死，中间兄弟不能断三、不在索引列上做任何操作（计算、函数、自动or手动类型转换），会导致索引失效而转向全表扫描即使满足最左前缀...
覆盖索引解决like ‘%ab’索引失效
weixin_40589458的博客
05-06 388
覆盖索引定义：MySQL 执行计划 explain 结果里的 key 有使用索引，如果 select 后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有 using index；覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引。
巧用覆盖索引提升like查询效率
weixin_54168675的博客
11-04 912
大家都知道最左前缀匹配原则，因此在使用模糊查询的过程中，会尽量避免使用右匹配和左右匹配，对于右匹配的场景，可以采用将需要模糊查询的字段，截取后面固定长度的字符串单独字段保存，并建立索引的方式来优化询效率，例如订单号，可以截取6到9位保存在short_order_no字段中，既能满足模糊查询要求，又方便业务操作。但在某些业务场景中，我们又不可避免会用到左右匹配查询，像商品名称、优惠券名称等，这种情况下直接用like '%xx%'是走不了索引的。这时候，可以怎么提升查询效率呢？
mysql的覆盖索引原理_为什么Mysql 在覆盖索引下的like ‘%xxx%’查询能使用到索引，原理是什么？...
weixin_31328555的博客
01-20 249
为什么Mysql 在覆盖索引下的like ‘%xxx%’查询能使用到索引，原理是什么？下面是在mysql5.7的一个示例：第一步准备数据CREATE TABLE `Student` (`ID` int(11) NOT NULL primary key AUTO_INCREMENT,`Name` varchar(4) NOT NULL DEFAULT '' COMMENT '姓名',`Age` in...
mysql 索引使用情况_MySQL 索引在 like 查询中的使用情况及覆盖索引优化 like 查询...
weixin_36363813的博客
02-02 212
通配符开头的模糊查询like 查询以通配符开头时无法使用索引而使用全表扫描。EXPLAIN SELECT * FROM staffs WHERE `name` like '%July%';EXPLAIN SELECT * FROM staffs WHERE `name` like '%July';通配符结尾的模糊查询like 查询以通配符结尾可以使用索引，类型为范围(range)。EXPLAIN ...
MySQL索引失效及解决、覆盖索引、全值匹配
学亮编程手记
05-12 674
覆盖索引是指查询的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。²³⁴⁵⁶覆盖索引的好处是可以避免回表操作，即不需要再根据主键去查找数据行，从而减少系统调用和数据拷贝等时间，提高查询性能。¹³⁵覆盖索引的使用方法是根据查询需求设计合适的索引，尽量让索引包含所有需要的字段，避免select *。¹²³⁴⁵例如，假设有一个表user，包含id、name、age、gender等字段，其中id是主键，name和age是非主键索引。
MySQL索引优化策略（五）：为什么覆盖索引的性能很高？
Flutter 跨平台技术研究
12-23 1509
一个通常的建议是为WHERE条件创建索引，但这其实是片面的。索引应当为全部查询设计，而不仅仅是WHERE条件。索引确实能有效地查找数据行，但MySQL也能够使用索引获取列数据。
MySQL优化技巧：索引创建与避免索引失效
同时，避免使用可能会导致索引失效的操作，如非前缀LIKE、不满足最左匹配原则的组合索引、使用OR操作符时未被索引的列以及对索引列进行函数操作。 2、避免索引失效 索引失效会导致查询效率下降。注意避免以%开头的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一叶一菩提魁

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值