数据库调优教程（十） 【精华章节】解决like ’%str’ 时索引不被使用的4种方法

最新推荐文章于 2025-05-20 16:47:31 发布
最新推荐文章于 2025-05-20 16:47:31 发布
阅读量1.1w 收藏 12
点赞数 4
CC 4.0 BY-SA版权
分类专栏： 数据库调优 MySql数据库调优 文章标签： 数据库调优 mysql 数据库 索引 模糊查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/hzy38324/article/details/44975859
本文详细介绍了如何解决MySQL中使用LIKE '%str%'导致索引失效的问题，提供了包括选择主键查询、应用覆盖索引、使用全文索引以及利用全文检索引擎工具在内的四种解决方案，对于大数据量表的高效模糊查询具有指导意义。

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

三、           索引


7.      解决like’%str’不使用模糊查询的4种方法


上一讲最后说了，只要模糊查询的模糊值在字符串前面，则不会使用索引，‘%aaa’和‘_aaa’都不会！

如下


应该说这是Mysql给程序员们开的一个玩笑。要是我的表数据量很大，而且又需要使用like’%%’这样的模糊查询来检索时，该怎么办？？

接下来，笔者将会给大家分享解决这个问题的四种方法！


1)    Select主键

只要Select的字段刚好是主键，那么就会使用到索引(只对innodb数据库有效)

比如下面的

最低0.47元/天 解锁文章

200万优质内容无限畅学
MySQL 性能调优方法论
AI天才研究院
08-04 796
2021年是MySQL数据库历史上有史以来的第十五个春天，然而其过去十多年里在处理海量数据量时却始终如此糟糕。本文就介绍一下数据库系统优化、性能优化的一些重要理论和方法论，并分享具体的案例进行分析。
MySQL调优
CUITAO2305532402的博客
04-22 1172
慢查询日志，顾名思义，就是查询花费大量时间的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。如何开启，我们稍后再说。1.3.2.1.1慢查询基础-优化数据访问查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。
数据库调优教程（十） 【精华章节】解决like ’%str’ 时索引不被使用的4种方法...
daxyang2015的博客
04-10 666
三、索引 7.解决like’%str’不使用模糊查询的4种方法 上一讲最后说了，只要模糊查询的模糊值在字符串前面，则不会使用索引，‘%aaa’和‘_aaa’都不会！ 如下 应该说这是Mysql给程序员们开的一个玩笑。要是我的表数据量很大，而且又需要使用like’%%’这样的模糊查询来检索时，该怎么办？？ ...
解决like '%字符串%'时索引不被使用的方法
上善若水
04-16 1349
样例如下： 1.创建表 create table t1( Id int not null PRIMARY key , Name varchar(20) not NULL DEFAULT ‘’, age int not null default 0, Email varchar(20) not null DEFAULT ‘’ ); 2.EXPLAIN优化分析： EXPLAIN select *...
MySQL中like模糊查询如何优化?
最新发布
jcc4261的博客
05-20 734
在 MySQL 中，LIKE模糊查询是非常常见的，但是它可能会导致性能问题，尤其是在数据量大的情况下。LIKE查询通常会导致全表扫描，因为它无法利用索引（尤其是当匹配模式以通配符开头时）。不过，针对LIKE查询的优化方法有一些常见的技巧，可以帮助提高查询效率。
规避like '%xxx' 无法使用索引
自知者明
05-29 2441
本文解决的问题是根据某个字段中内容后几位进行模糊查询时不能使用索引的情况，比如根据手机号后四位模糊匹配查询。 mysql中模糊查询如 mobile like ‘%2520’是无法使用mobile上的索引的，那么如果要根据手机号后四位进行模糊匹配可以用以下方法进行改造。 我们可以加入冗余列，比如mobile_reverse,内部存储为mobile的倒叙文本,如mobile 15836652520，那...
索引法则--LIKE以%开头会导致索引失效进而转向全表扫描（使用覆盖索引解决）
Stone.鱼儿的博客
09-18 3415
什么是覆盖索引？ 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。 （即select和where条件中的字段都出现在索引中，即为覆盖索引） 解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索..
sql 中使用like%、函数导致索引失效的解决方案
weixin_58691039的博客
09-06 1238
sql 中使用like%、函数导致索引失效的解决方案全文索引与虚拟列索引
【SQL】索引失效的11种情况
null
11-24 7352
数据库调优的大致方向：sql查询优化技术有很多，大体分为物理查询优化和逻辑查询优化:数据准备： 索引失效案例 【1】. 全值匹配 【2】. 最佳左前缀法则 在定义表时，让主键auto_increment，否则，插入一条数据时可能会移动大量数据。 如，往 1 5 8 10 15 … 100 中插9，会放在8 10 中间，因为索引默认升序排列。那么10往后的数据都要挪动，页不够时又要放到下一页，每插一条数据都这样挪一次，开销很大 我们自定义的主键列id 拥有AUTO_INCREMENT 属性，在插入记录时存
索引失效情况之like
Kaiser__的博客
05-24 971
在学习索引时，有一个问题通常很绕，那就是like。它有时可以使用到索引，而有时又不可以使用到索引。因此，今天按照我的个人理解对like进行一个总结。希望可以帮助到初学者，也欢迎大家一起讨论。
数据库优化之：like %xxx%该如何优化
1点25的博客
11-16 2585
再来简单分析一下索引条件下推的原理，在执行查询时先利用SQL中所提供的created_at条件在联合索引B+树中进行快速查找，匹配到所有符合created_at条件的B+树叶子节点后，再根据company_name条件进行过滤，然后再根据过滤之后的结果中的主键ID进行回表找到其他字段（回表），最终才返回结果，这样处理的好处是能够减少回表的次数，从而提高查询效率。就一定会导致索引失效，原理也可以配合其他字段一起来建联合索引，从而使用到索引条件下推机制。就会利用到索引条件下推机制，比如下图中Extra里的。
mysql优化之like和=性能详析
09-08
主要给大家介绍了关于mysql优化之like和=性能的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
Mysql中like %xxx% 模糊查询该如何优化
weixin_40587144的博客
02-22 2333
Mysql中like %xxx% 模糊查询该如何优化
mysql like‘%xxx%‘优化
农夫三拳
02-12 1024
3、union联合使用虚拟队列进行查询。2、为虚拟列创建索引。
mysql怎样优化where like ‘%字符串%‘这种模糊匹配的慢sql
雅冰石的专栏
02-27 610
工作中经常遇到这种模糊匹配的慢sql：select * from 表名 where 字段 like '%字符串%';由于前面有%，导致无法走该字段上的索引。
MySQL索引系列--索引的优化--LIKE模糊查询
IT利刃出鞘的博客
02-08 9311
本文介绍MySQL的LIKE模糊查询索引的优化。主要是索引失效的解决方案。
MySQL学习笔记之索引优化与查询优化
四国棋客
06-22 1264
本文记录了MySQL学习笔记中的查询优化和索引优化。索引优化部分列举并分析了索引失效的各种场景，查询优化部分包括了关联查询优化、子查询优化、排序优化、分组查询优化、分页查询优化，还介绍了覆盖索引、索引下推等概念和使用场景。最后，对比了普通索引和唯一索引、count(*)、count(1)和count(字段)的效率、exists和in的区分等优化手段。
mysql模糊查询like优化
weixin_42412601的博客
06-01 1364
1、使用全文索引 MySQL 之全文索引：https://blog.youkuaiyun.com/mrzhouxiaofei/article/details/79940958 2、使用ICP特性 【mysql】关于ICP、MRR、BKA等特性：https://www.cnblogs.com/chenpingzhao/p/6720531.html 连模糊匹配like %%怎么优化都不知道：https://mp.weixin.qq.com/s/ygvuP35B_sJAlBHuuEJhfg ...
MySQL换成达梦数据库，需要注意的地方
03-08
<think>好的，用户问的是从MySQL切换到DM达梦数据库需要注意哪些地方。我需要先了解这两个数据库的区别，以及迁移过程中可能遇到的问题。首先，达梦作为国产数据库，可能在语法、功能、性能优化等方面与MySQL存在差异。用户可能是企业的开发人员或者系统架构师，负责数据库迁移工作，他们需要具体的迁移步骤和注意事项，避免踩坑。 用户可能已经有一定的MySQL使用经验，但对达梦不太熟悉，需要了解迁移的具体挑战，比如SQL语法差异、事务处理、存储引擎、备份恢复等。还可能关心数据迁移工具的选择、性能调优、兼容性问题以及运维方面的变化。此外，用户可能没有明确提到的潜在需求包括合规性、安全性、成本评估以及后续维护的支持情况。 接下来需要分点整理注意事项，比如语法兼容性、数据类型差异、事务和锁机制、迁移工具使用、性能优化、系统函数变化、权限管理、备份策略、测试验证等。每个点都需要具体说明MySQL和达梦的不同之处，以及迁移时如何调整。例如，达梦的存储过程语法可能与MySQL不同，需要重写；索引机制可能有差异，需要优化查询语句。 还要考虑数据迁移过程中的步骤，比如使用DTS工具还是自行开发脚本，数据校验的方法，迁移后的测试策略，包括功能测试和性能测试。另外，应用层的适配也很重要，比如连接池配置、ORM框架的支持情况，可能需要修改连接字符串或驱动类。 最后需要提醒用户进行充分的测试和备份，确保迁移后的系统稳定运行。可能还需要建议分阶段迁移，先在测试环境验证，再逐步切换到生产环境，减少风险。</think>将MySQL迁移到达梦数据库时，需重点关注以下技术差异与适配要点（附典型问题解决方案）： --- ### 一、SQL语法差异适配 1. **分页机制** - MySQL：`LIMIT 10 OFFSET 20` - 达梦：`SELECT TOP 10 * FROM (SELECT ROWNUM rn,t.* FROM table t WHERE ROWNUM<=30) WHERE rn>20` - 改造方案：建立分页函数映射层，实现语法自动转换 2. **字符串处理** - MySQL：`CONCAT(str1,str2)`支持NULL值拼接 - 达梦：`str1 || str2`遇到NULL时返回NULL，需显式使用`NVL(str1,'')`处理 - 示例转换： ```sql -- 原MySQL SELECT CONCAT(address, tel) FROM users -- 达梦适配 SELECT NVL(address,'') || NVL(tel,'') FROM users ``` 3. **日期函数** - MySQL：`DATE_FORMAT(NOW(),'%Y-%m')` - 达梦：`TO_CHAR(SYSDATE, 'yyyy-mm')` - 需批量替换56种日期格式表达式 --- ### 二、数据类型映射 | MySQL类型 | 达梦类型 | 注意要点 | |----------------|----------------|----------------------------------| | TINYINT(1) | BOOLEAN | Java中需处理为Boolean类型 | | DATETIME(6) | DATETIME(6) | 达梦默认精度为0，需显式指定 | | JSON | VARCHAR(4000) | 需应用层实现JSON解析 | | AUTO_INCREMENT | IDENTITY(1,1) | 达梦不支持复合主键自增 | > **特殊案例**：MySQL的`ENUM('A','B')`需转换为达梦的`VARCHAR(10) CHECK ...` --- ### 三、事务与锁机制 1. **隔离级别差异** - 达梦默认`READ COMMITTED`，但通过`SET TRANSACTION ISOLATION LEVEL`设置后，实际行为与MySQL存在差异： - 幻读处理：达梦在RR级别下通过`SELECT ... FOR UPDATE`实现全表间隙锁 2. **死锁检测** - MySQL：等待超时后自动回滚 - 达梦：需配置`DEADLOCK_CHECK_INTERVAL=5`（秒级检测间隔） --- ### 四、迁移实施步骤 1. **结构迁移** ```bash # 使用达梦DTS工具转换 ./dts -s mysql://user:pwd@host:3306/db \ -t dm://user:pwd@host:5236/db \ --type=struct ``` 2. **数据迁移** - 分片策略：按`MOD(id,10)`将大表拆分为10个并行任务 - 数据校验： ```sql -- MySQL校验SQL SELECT COUNT(*) AS cnt, MD5(GROUP_CONCAT(id ORDER BY id)) FROM table -- 达梦校验（需处理CLOB类型）： SELECT COUNT(*), DBMS_LOB.SUBSTR(SYS_CONNECT_BY_PATH(id,','), 4000) FROM (SELECT id, ROWNUM rn FROM table ORDER BY id) ``` 3. **存储过程迁移** ```sql -- MySQL存储过程 DELIMITER $$ CREATE PROCEDURE test(IN p INT) BEGIN DECLARE v INT DEFAULT 0; SET v = p + 1; SELECT v; END$$ -- 达梦适配版 CREATE OR REPLACE PROCEDURE test(p IN INT) AS v INT := 0; BEGIN v := p + 1; PRINT v; END; ``` --- ### 五、性能调优要点 1. **索引策略调整** - 达梦的位图索引在低基数列（如性别）上效率提升显著： ```sql CREATE BITMAP INDEX idx_gender ON users(gender); ``` 2. **执行计划差异** - 达梦对`WHERE col LIKE '%value%'`无法使用索引，需改造为全文索引： ```sql CREATE CONTEXT INDEX idx_content ON docs(content) LEXER DEFAULT_LEXER; ``` 3. **内存配置** ```ini # 达梦dm.ini关键参数 MEMORY_POOL = 2000 # 共享内存池(MB) BUFFER_POOL_SIZE = 100000 # 数据缓冲区(page数) ``` --- ### 六、运维监控变化 1. **慢查询分析** - 达梦需开启`SVR_LOG.SQL_TRACE_MASK=31`记录完整日志 - 使用`DM_MONITOR`视图： ```sql SELECT sess_id, sql_text, time_used FROM V$LONG_EXEC_SQLS WHERE time_used > 5000; -- 5秒以上查询 ``` 2. **备份策略** ```bash # 达梦联机备份 ./disql SYSDBA/SYSDBA@localhost:5236 BACKUP DATABASE FULL TO 'BAK_FULL' BACKUPSET '/dmbak/full'; ``` --- ### 七、典型错误案例 1. **隐式类型转换** - MySQL允许`SELECT * FROM table WHERE date_col = '2023-02-30'`（自动转为NULL） - 达梦会直接报错："日期格式非法" 2. **子查询限制** - 达梦要求关联子查询必须包含`ROWNUM`： ```sql -- 错误写法 SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) -- 正确适配 SELECT * FROM t1 WHERE id IN (SELECT id FROM (SELECT id FROM t2) tmp) ``` --- 迁移完成后建议执行： 1. 压力测试：使用TPC-C工具验证事务处理能力 2. 回归测试：覆盖所有业务SQL语句 3. 建立兼容层：对差异函数进行统一封装 建议在过渡期保留MySQL双写机制，通过数据比对工具（如自研`DeltaChecker`）确保数据一致性，待验证稳定后再完全切换。
评论 6
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值