MySQL调优 ---- IN

最新推荐文章于 2025-06-18 21:56:33 发布
原创 最新推荐文章于 2025-06-18 21:56:33 发布 · 3.9k 阅读 · 4 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#MySQL #优化 #IN #JOIN #性能

本文详细阐述了在慢查询优化中，通过熟悉业务逻辑将使用IN关键字的子查询转换为JOIN查询的方法，从而显著提升查询效率。以具体案例为例，展示从EXPLAIN结果分析慢查询原因，并提出优化方案。最终通过比较优化前后的EXPLAIN结果，直观呈现性能提升效果。

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

  在慢查询优化中， 对于 IN 这个关键字的优化的出现概率还是挺高的。 其实对于 IN 关键字出现的 SQL 优化其实难度不高， 重要的是熟悉该 SQL 的应用场景也可以说是业务逻辑。


  一、举个最近遇到的例子( 业务： 查询该标签下所有的文章数量 )：
select count(*) from cms_article ca where 某个TagID in (select cat.tag_id from cms_article_tag cat where ca.id=cat.article_id)


看下 EXPLAIN 结果：


引用MySQL官方文档的一句话：A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

可以从 EXPLAIN 结果明显看出， IN 子查询就扫了2行， 而另一个查询扫描了大量行数， 完全符合官方文档说的慢查询情况。




  二、如何优化呢？
 
  本文开头就说了， 先熟悉业务逻辑， 然后就很好改了。
引用MySQL官方文档的解决方案：The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.［也就是熟悉业务， 然后大多数情况都能改成 JOIN 的方式来优化］

优化后SQL：
select count(*) from cms_article ca RIGHT JOIN cms_article_tag cat ON cna.id = cnat.article_id WHERE cnat.tag_id=某TagID



EXPLAIN结果：

查询行数锐减， 只要25*1的复杂度就完成了本次查询。 



  三、 参考资料

                         MySQL官网




MySQL之IN的优化
KingdomCoder
01-17 1万+
场景描述： MySQL的IN的问题：由于in里面的内容过于庞大 (5000+)，导致查询效率低下(4秒以上)。 SELECT * FROM xxx WHERE `id` IN(xxx) 计划解决方案： 1.测试得到IN里面的最佳数量 2.循环分多次查询 3.多线程共同执行 实际测试记录： 1.查询最佳数量 1次 4265ms、4177ms、4112ms 平均4.18秒 ...
mysql 调优-优化
myBackAtOne的博客
03-02 332
5.读写分离: 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构，读写分离，解决的是，数据库的写入，影响了查询的效率。在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现，但是当应用系统正式上线后，随着生产环境中数据量的急剧增长，很多SQL语句和数据库整体方案开始逐渐显露出了性能问题，对生产的影响也越来越大，此时MySQL数据库的性能问题成为系统应用的瓶颈，因此需要进行MySQL数据库的性能优化。
mysql in 查询优化
weixin_34321753的博客
11-29 264
2014年11月29日21:01:01 场景：有的时候查询数据库的select in 语句中会有非常多不连续的数值，会很影响查询效率 方法：将select in 查询转换成多个select between and 语句 代码：核心代码(id要排过顺序) 1 public function group() 2 { 3 // $a = array(...
MySQL 多列 IN 查询详解：语法、性能与实战技巧
最新发布
2501_92289558的博客
06-18 668
优先使用联合索引确保的查询顺序与索引一致。控制条件组数量单次查询避免超过 1000 组值。监控执行计划定期用EXPLAIN避免全表扫描若未命中索引，考虑优化查询条件或数据结构。事务中谨慎使用长时间持有锁可能导致并发问题。
Mysql in查询优化
大黄鸭在发光的专栏
01-09 1908
Mysql in查询优化
MySQL in查询优化
stepMore的博客
11-29 3659
select sum(integral_left) from `integral_produce_available` where `DELETED` =0 and integral_produce_id in (select id from `integral_produce` where account_Id='1446656640503070720' and `STATUS` =1 and `DELETED` =0 and activate_flag=1 and expired_flag=0 and
性能调优-（2）Explain详解与索引优化最佳实践
10-17
在进行性能调优时，尤其针对数据库查询的性能优化，掌握Explain命令的使用与索引优化知识至关重要。Explain命令可以帮助我们分析SQL语句的执行计划，从而直观地理解查询的效率和行为。在Explain的输出结果中，关注的...
SQL调优 - 优化 MySQL 中的 IN 语句查询效率
逍遥Sean的博客
08-07 2986
在使用 MySQL 进行开发时，我们经常会使用到 `IN` 语句来查询某个字段是否包含在一个给定的值集合中。然而，当这个值集合的数量超过一定的限制时，`IN` 语句可能会导致查询效率显著下降，甚至引起性能问题。本文将探讨如何优化 MySQL 中 `IN` 语句的查询效率，以提升数据库操作的性能。 @[TOC](优化 MySQL 中的 IN 语句查询效率) ## 优化 MySQL 中的 IN 语句查询效率 ### 问题背景 `IN` 语句的一般形式如下： ```sql SELECT * FROM t
架构师系列-MYSQL调优（五）- JOIN、in及exists优化
dengwei_dw的专栏
04-23 1499
什么是驱动表?
Mysql调优大全梳理（涵盖90%需要调优的场景）
qq_45455361的博客
02-20 8031
前言 学习这个文章需要具备一定的理论基础，不妨先来看一下我这篇文章《一文道尽数据库底层原理，探讨Mysql调优之道》 1.Join语句优化 Join语句相关算法 算法1：Nested-Loop Join（NLJ） 这种算法称为“嵌套循环Join”，大致是这样玩的： Table Join Type t1 range t2 ref t3 All for each row in t1 matching range{ for each row in t2 matching refere
mysql in查询优化
warrah 南极狼
04-13 1523
不过我还是想知道，这个效果怎么样，于是在本地的mariadb上运行，效果如下，嗯，貌似有进步。还不如in，固然chatgpt说的专家，也就是砖家。说明采用的是物化视图，因为物化视图是预计算和存储的查询结果，用于提高查询性能，也就是说。将in的内容，创建一个临时表，按照别人的示例，我为什么执行不通过呢？，按照这个说的集中方法，验证一下。版本不支持，而我们的生产环境就是mysql5.7,因此这条路是走不通的。，但那是临时表，也就是in里面的内容，所以没有影响。下面的in里面的值不是固定的，
MySql之in查询优化
qq_46144673的博客
12-07 9326
Mysql之in条件查询优化
mysql的IN查询优化
Cyms,BgXmSfXtsj !!!! Battle Each Moment !!!!
06-23 6873
mysql的IN里面的数量太大，比如大于1千时，查询的性能就会差很多。 有以下的解决方法。 解决方法一：拆分IN的数量 IN 数量超过1千，就拆成多条 sql， 每条sql的IN数量不超过1千。 用OR或者UNION进行SQL改写。 也可以使用 Java写代码，把 IN 数量进行拆分，每条sql的IN数量不超过1千。多次执行。 比如使用 IN 的sql 是 SELECT 字段1 FROM 表名 ...
mysql的查询优化方法
纵横的博客
11-16 1261
前言；mysql的性能优化包括： 索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬件优化，应用层面优化（web服务器，缓存）等等。这里的记录的优化技巧更适用于开发人员，都是从网络上收集和自己整理的，主要是查询语句上面的优化，其它层面的优化技巧在此不做记录。 查询的开销指标： 执行时间 检查的行数 返回的行数 查询优化 1、索引优化 建立索引来优化查询 如何选择合适的列建立索引？ 1.在where、group by 、order by 、on从句出现的列 2.索引字段越小越好 3.离散度大的列放到联
【mysql in 查询优化】从表结构，代码层面，sql语法分析in优化
qq_36896980的博客
04-27 414
大多数表设计子级父级关系时，会引入parent_id字段作为对于父级子级的数据查询区分，在显目前常用的数据mysql8中也引入了但是当子父级的关联关系过多时如一个parent_id会导致直接全表查询所以是不是应该思考在什么场景下，能查询他的下级单位，但是不引入in函数呢，通过地区code的设计和使用会发这种使用方式是不是就是一种解决方式？in两种方式在不同的查询场景下会有不同的好处，所以在对需求分析的过程中需要考虑到我们的查询场景，在两种方式的都需要的时候，可以使用parent_id的情况下冗余一个。
mysql之 in的优化
weixin_51260368的博客
12-24 772
在 innodb 情况下我们查询一个语句mysql 在查询优化器会对我们写的语句会做优化，比如select * from tableA AS A where A.colum in（select * from tableB ） 我们把 select * from tableB 查询结果作为结果集B，这个情况叫做行子查询 ，我们都知道它会先执行 select * from tableB 这个语句，然后会优化成 A.colum=B.colum 假如我们colum这个字段有索引的情况下 结果集有10行就是每次都是
MySQL的优化
2303_81990289的博客
12-12 958
正确使用索引可以显著减少数据库的I/O操作，提高查询速度。：对于复杂查询，可以先查询并存储到临时表中，再进行进一步查询操作，减少重复计算。：在进行大量数据插入或更新时，批处理可以减少数据库的I/O操作次数，提高性能。：不同的存储引擎具有不同的性能特点，选择合适的存储引擎可以提高查询性能。子句限制返回结果集的大小，尤其在分页查询时，可以显著提高性能。：包括更新统计信息、重建索引等，以确保查询性能保持最佳状态。：对于大型表，使用分区可以提高查询性能和数据管理效率。：了解查询的具体执行步骤，找出性能瓶颈。
MySQL调优
06-08
<think>嗯，用户这次明确想了解MySQL性能优化的具体方法和最佳实践，看来ta正在处理数据库性能问题。结合历史对话，我之前已经提供了索引优化、查询优化等基础建议，这次需要更系统地展开。用户引用的几篇材料虽然零散，但透露出几个关键点：JOIN操作的索引重要性（引用[3]）、存储过程优化思路（引用[2]）、分页查询的随机偏移技巧（引用[4]），这些正好能补充到我的知识框架里。不过引用[1]和[3]标题重复，可能源自同一份资料，需要去重处理。从技术维度看，MySQL优化可以分三层展开：首先是最基础的索引策略，这是见效最快的；然后是查询语句的编写技巧，避免全表扫描；最后是表结构和存储引擎层面的高级调优。用户提到的分页优化（引用[4]）属于查询技巧的细分场景，值得单独强调。注意到用户没有提供具体业务场景，但引用了存储过程相关内容（引用[2]），暗示可能存在复杂业务逻辑。在响应时需加入存储过程优化模块，特别要强调“避免循环操作”这点——这常是性能黑洞。分页场景的last_key技巧可能超出用户预期，但引用[4]提到的随机偏移问题正好佐证这个方案的优越性。需要用具体公式说明：WHEREid>{last_key}LIMIT20比直接OFFSET高效得多。需要警惕的是所有引文都未涉及硬件或配置调优，但考虑到InnoDB缓冲池对性能的巨大影响，还是决定补充这一点，毕竟很多开发者会忽略内存配置。最后用事务最小化原则收尾，因为引用材料里反复出现索引相关建议，说明用户很可能在处理写操作频繁的系统。</think> 以下是MySQL性能优化的关键方法和最佳实践，综合索引优化、查询设计、存储结构等方面： --- ### **一、索引优化（核心策略）** 1. **为高频查询字段建立索引** - 特别是`WHERE`、`JOIN`、`ORDER BY`、`GROUP BY`涉及的列，例如： ```sql -- 为user_id和order_date建立复合索引 CREATE INDEX idx_user_orders ON orders(user_id, order_date); ``` 2. **避免冗余索引** - 删除未使用或重复的索引（通过`SHOW INDEX FROM table_name`分析）。 3. **利用覆盖索引** - 索引包含查询所需全部字段时，避免回表查询，例如： ```sql -- 索引(user_id, status)可覆盖此查询 SELECT user_id, status FROM orders WHERE user_id = 1005; ``` --- ### **二、查询优化** 1. **避免全表扫描** - 禁用`SELECT *`，仅获取必要字段。 - 用`LIMIT`分页时避免大偏移量，改用`WHERE id > last_id LIMIT n`替代`OFFSET`[^4]。 2. **优化`JOIN`操作** - 确保关联字段**两端均有索引**[^3]： ```sql -- users.id 和 orders.user_id 均需索引 SELECT * FROM users JOIN orders ON users.id = orders.user_id; ``` 3. **慎用子查询** - 优先改用`JOIN`，例如： ```sql -- 低效写法 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); -- 高效写法 SELECT users.* FROM users JOIN orders ON users.id = orders.user_id; ``` --- ### **三、表结构与存储** 1. **选择合适的数据类型** - 用`INT`而非`VARCHAR`存储数字，用`DATETIME`而非字符串存日期。 2. **垂直拆分大表** - 将低频大字段（如`TEXT`）拆分到独立表，减少主表体积。 3. **使用分区表** - 对时间序列数据（如日志）按年月分区： ```sql PARTITION BY RANGE (YEAR(order_date)) (...); ``` --- ### **四、配置与存储过程** 1. **调整内存参数** - 增加`innodb_buffer_pool_size`（通常设为物理内存的70%-80%）。 2. **存储过程优化** - 避免循环内执行SQL，改用批量操作[^2]： ```sql -- 低效示例 WHILE ... DO INSERT INTO logs VALUES (...); END WHILE; -- 高效做法：批量INSERT INSERT INTO logs VALUES (...), (...), ...; ``` --- ### **五、高级技巧** 1. **查询缓存（谨慎使用）** - MySQL 8.0+已移除查询缓存，旧版本建议对静态表启用。 2. **读写分离** - 写操作主库，读操作分摊到多个从库。 3. **监控与慢查询分析** - 开启慢查询日志： ```ini slow_query_log = ON long_query_time = 2 # 记录超过2秒的查询 ``` --- ### **关键原则总结** | 优化方向 | 最佳实践 | |-------------------|---------------------------------------| | **索引** | 覆盖索引、复合索引、定期清理无效索引 | | **查询设计** | 避免`SELECT *`、用`EXPLAIN`分析执行计划 | | **分页** | 用`WHERE id > last_id`替代大偏移量 | | **存储引擎** | 事务需求用InnoDB，只读场景用MyISAM | | **事务控制** | 最小化事务范围，避免长事务 | > 通过`EXPLAIN SELECT ...`分析执行计划，关注`type`列（应避免`ALL`），`rows`列（扫描行数越少越好）。 --- **相关问题** 1. 如何识别MySQL中的慢查询并进行针对性优化？ 2. 在读写分离架构中，如何保证数据一致性？ 3. InnoDB和MyISAM存储引擎的主要区别及应用场景？ 4. 分库分表时有哪些经典策略及注意事项？
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值