MySQL in 太慢的 3 种优化方案

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: MySQL中的`eq_range_index_dive_limit`参数默认值为200,影响了IN查询的执行方式。当IN列表项少于这个值时,MySQL会使用扫描索引树(精确成本计算),而多于此值则使用索引统计(快速但可能不准)来分析查询成本。大量IN值可能导致性能下降。解决方案包括:1) 分批查询;2) 使用UNION ALL创建内存临时表;3) 创建实体表存储IN值并进行JOIN操作。注意,实体表需及时清理并避免反复插入删除导致性能下降。

MySQL in 太多出现慢的原因

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

eq_range_index_dive_limit 默认值是 200 .

csharp

复制代码

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 的数量, 分批查询.

csharp

复制代码

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

csharp

复制代码

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

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

方案二

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

sql

复制代码

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 行

sql

复制代码

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 行

方案三

使用 实体表

创建实体表

sql

复制代码

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)
);
  1. 将上面 task_tag_id 插入至 临时表
  1. 可使用 insert values 插入
  1. 如果是结果值可以直接使用
  1. insert select 插入

使用

csharp

复制代码

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

⚠ 注意点

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


转载来源:https://juejin.cn/post/7245564421316788284

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
3天前
|
缓存 监控 关系型数据库
mysql优化
【6月更文挑战第12天】mysql优化
15 3
|
8天前
|
SQL 关系型数据库 MySQL
MySQL大数据量分页查询方法及其优化
MySQL大数据量分页查询方法及其优化
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
14 0
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
12 0
|
9天前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
20 1
|
10天前
|
存储 SQL 关系型数据库
MySQL调优之索引:索引的失效与优化
MySQL调优之索引:索引的失效与优化
12 0
|
15天前
|
SQL 关系型数据库 MySQL
mysql 故障排除与优化
mysql 故障排除与优化
|
2天前
|
SQL 存储 关系型数据库
MySQL 示例数据库大全
我们练习 SQL 时,总会自己创造一些测试数据或者网上找些案例来学习,其实 MySQL 官方提供了好几个示例数据库,在 MySQL 的学习、开发和实践中具有非常重要的作用,能够帮助初学者更好地理解和应用 MySQL 的各种功能和特性,特别是练习 SQL 的好帮手。
21 0
|
2天前
|
SQL 关系型数据库 MySQL
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)

相关产品

  • 云数据库 RDS MySQL 版