MySQL in太多过慢的三种解决方案！

admin • • SQL技巧

MySQL in太多过慢的三种解决方案！

在MySQL中有一个配置参数eq_range_index_dive_limit,它的作用是一个等值查询(比如：in 查询),使用扫描索引树的方式分析在MySQL内部叫做index dives,使用索引统计的方式分析在MySQL内部叫做index statistics,本文给大家介绍了MySQL in太多过慢的三种解决方案。

MySQL in 太多出现慢的原因

在MySQL中有一个配置参数eq_range_index_dive_limit，它的作用是一个等值查询(比如：in 查询)，其等值条件数小于该配置参数，则查询成本分析使用扫描索引树的方式分析，如果大于等于该配置参数，则使用索引统计的方式分析。使用扫描索引树的方式分析在MySQL内部叫做index dives，使用索引统计的方式分析在MySQL内部叫做index statistics。

eq_range_index_dive_limit 默认值是 200 .

1
select * from dogs where id in (1, 2, 3, 4);

结合上面这条 SQL，就是如果 SQL 中 IN 查询字段 id 的值出现的数量小于 eq_range_index_dive_limit，则走索引树扫描分析查询成本，大于等于 eq_range_index_dive_limit，则走索引统计的方式分析查询成本。

扫描索引树的方式分析 SQL 的查询成本，它的好处就是在 IN 查询的值数量不多时，得到的成本结果是精确的，这就意味着 MySQL 可以选择正确的执行计划，保证语句查询的性能。你现在一定有个疑问：为什么说是在 IN 查询的值数量不多时才是精确的，因为扫描性能的原因，MySQL 在 IN 查询的值数量很多的情况下，扫描索引树成本提高，性能下降，导致查询成本分析代价也随之提高了。

索引统计的方式分析 SQL 的查询成本，由于无需扫描索引树，所以，它的优势就是查询成本分析过程快，代价低。但是，它的缺点也很明显，由于无需扫描索引树，通过粗略统计索引使用情况，得出查询成本，导致 MySQL 可能选错执行计划，使得 SQL 查询性能下降。

解决方案

方案一

可以通过拆分 in 的数量, 分批查询.

1
select * from dogs where id in (1, 2);
1
select * from dogs where id in (3, 4);

这种方法缺点也明显, 对于分页或者是查询总条件的一部分并不能实现.

方案二

使用 union all 实现内存级别临时表.

1
2
select *
from users where task_created > '2020-01-01' and  task_tag_id in ('-1', '1' , ....'1000个');

结果: 在 1 s 631 ms (execution: 172 ms, fetching: 1 s 459 ms) 内检索到从 1 开始的 500 行

1
2
3
4
select * from users u
inner join (select -99 as id union all select '1' union all select '-1'
union all select '1' ) as temp on u.task_tag_id = temp.id
where task_created > '2020-01-01'

结果: 在 383 ms (execution: 201 ms, fetching: 182 ms) 内检索到从 1 开始的 500 行

方案三

使用 实体表

创建实体表

1
2
3
4
5
6
7
8
9
10
11
12
create table jump_data
(
id          bigint auto_increment
primary key,
user_id      bigint   default -1                not null comment '人员id',
hash        varchar(70)          not null comment '当前存储关联 hash 值',
ref         varchar(100)                comment '关联数据 id',
ref_long    bigint                             null,
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
index idx_hash_ref(hash, ref),
index idx_hash_ref_long(hash, ref)
);
  • 将上面 task_tag_id 插入至 临时表可使用 insert values 插入
  • 如果是结果值可以直接使用insert select 插入

使用

1
2
3
select *
from users u  inner join jump_data jd on u.hash = '' and u.ref_long = u.id
where task_created > '2020-01-01'

注意点

  • 需要及时清理 jump_data 表
  • 定时需要 truncate 表因为反复的新增和删除导致 MySQL 预估数据不准确导致速度下降

以上就是MySQL in太多过慢的三种解决方案的详细内容。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

企业级Ovirt引擎超融合技术！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>