备案控制台
探索云世界
开发者社区 数据库 文章 正文

记录一次mysql慢查询的优化过程

2023-08-02 123
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介： 优化mysql的慢查询语句，优化效果明显，记录一下。

前言

业务上线后经常报查询超时，数据库使用的是阿里云的RDS，mysql版本是5.6.16-log，有几条统计数据的查询语句执行很慢，有的甚至执行一次需要10多秒，简直无法忍受。
查看了超时时间，默认为0

show variables like '%MAX_STATEMENT_TIME%'
show variables like '%MAX_EXECUTION_TIME%'
# 网上说这个参数只针对5.7以上的版本生效
SET GLOBAL MAX_EXECUTION_TIME=30000;
# 对5.6版本生效
SET GLOBAL MAX_STATEMENT_TIME=30000;

然后在 select 后面 加上 /*+ max_statement_time(1000) */。
设置后也没有效果，然后在阿里云上面手动修改对应的参数，也没用。

最后在阿里云上面看到几条优化建议，参考阿里云的建议，查询速度明显提升，这里也记录一下。

例1

表里面有810w+的数据
image.png

  1. 首先，就是建立正确的索引。
    多创建了两个联合索引，第一个是之前创建的，虽然能命中，但不是最佳。

    ALTER TABLE `user_event_record` ADD INDEX `idx_event_gmtcreate_userid` (`event`, `gmt_create`, `user_id`)    
ALTER TABLE `user_event_record` ADD INDEX `idx_gmtcreate_event` (`gmt_create`, `event`)

    image.png

  2. 修改时间查询条件

  • 原sql
    SELECT 
  LEFT (f.gmt_create, 10 ) `date`,
  f.event,
  COUNT(1) total 
FROM user_event_record f 
WHERE
  date_format(f.gmt_create, '%y%m%d') >= date_format('2023-01-14', '%y%m%d') 
GROUP BY `date`, f.event;

  • 修改后的sql

    SELECT 
  LEFT (f.gmt_create, 10) `date`,
  f.event,
  COUNT(1) total 
FROM user_event_record f 
WHERE
  f.gmt_create > DATE('2023-01-14') 
GROUP BY `date`, f.event;

    查询速度对比

  • 原sql执行时间
    image.png

  • 修改后的sql执行时间
    image.png

直接提升了7-8倍，查询结果一致，效果提升还是很明显的。

例2

同一个表
原SQL

SELECT
        DATE_FORMAT(gmt_create, '%m-%d') AS `time`,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' OR event = 'enterSystemSuc' THEN user_id END) AS total,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' THEN user_id END) AS success,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' THEN user_id END) AS fail,
        COUNT(DISTINCT CASE WHEN app = '1.0.7' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS oldCount,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.7' THEN user_id END) AS oldSuccess,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.7' THEN user_id END) AS oldFail,
        COUNT(DISTINCT CASE WHEN app = '1.0.8' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS newCount,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.8' THEN user_id END) AS newSuccess,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.8' THEN user_id END) AS newFail
FROM user_event_record
WHERE DATE_FORMAT(gmt_create, '%Y-%m-%d') > DATE_SUB(CURDATE(), INTERVAL 10 DAY)
GROUP BY DATE_FORMAT(gmt_create, '%m-%d')

修改后的sql

SELECT
        DATE_FORMAT(gmt_create, '%m-%d') AS `time`,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' OR event = 'enterSystemSuc' THEN user_id END) AS total,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' THEN user_id END) AS success,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' THEN user_id END) AS fail,
        COUNT(DISTINCT CASE WHEN app = '1.0.7' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS oldCount,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.7' THEN user_id END) AS oldSuccess,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.7' THEN user_id END) AS oldFail,
        COUNT(DISTINCT CASE WHEN app = '1.0.8' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS newCount,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.8' THEN user_id END) AS newSuccess,
        COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.8' THEN user_id END) AS newFail
FROM user_event_record
WHERE gmt_create > DATE_SUB(CURDATE(), INTERVAL 9 DAY)
GROUP BY DATE_FORMAT(gmt_create, '%m-%d')

查询速度对比

  • 原sql执行时间
    image.png

  • 修改后的sql执行时间
    image.png

从28秒提升到3秒，提升了8-9倍

例3

表数据530w+
image.png

创建了联合索引

ALTER TABLE `file_record_info` ADD INDEX `idx_mark_gmtcreate_userid` (`mark`, `gmt_create`, `user_id`)

image.png

  • 原sql

    SELECT
  '2023-01-12' AS `date`,
  COUNT(DISTINCT CASE WHEN `mark` = '10000' THEN `user_id` ELSE NULL END) AS `upgradeFailCount`,
  COUNT(DISTINCT CASE WHEN `mark` = '10001' THEN `user_id` ELSE NULL END) AS `enterSysFailCount`,
  COUNT(DISTINCT CASE WHEN `mark` = '10008' THEN `user_id` ELSE NULL END) AS `dllLoadFailCount`,
  COUNT(DISTINCT CASE WHEN `mark` = '10009' THEN `user_id` ELSE NULL END) AS `enableDeviceFailCount`    
FROM file_record_info 
WHERE 
  mark IN ('10000', '10001', '10008', '10009' ) 
  AND DATE_FORMAT(gmt_create, '%Y-%m-%d') = '2023-01-12';

  • 修改后的sql

    SELECT
  '2023-01-12' AS `date`,
  COUNT(DISTINCT CASE WHEN mark = '10000' THEN user_id END) AS upgradeFailCount,
  COUNT(DISTINCT CASE WHEN mark = '10001' THEN user_id END) AS enterSysFailCount,
  COUNT(DISTINCT CASE WHEN mark = '10008' THEN user_id END) AS dllLoadFailCount,
  COUNT(DISTINCT CASE WHEN mark = '10009' THEN user_id END) AS enableDeviceFailCount
FROM file_record_info WHERE mark IN ( '10000', '10001', '10008', '10009' )
AND (`gmt_create` >= DATE('2023-01-12') AND `gmt_create` < DATE_ADD(DATE('2023-01-12'), INTERVAL 1 DAY));

    查询速度对比

  • 原sql执行时间
    image.png

  • 修改后的sql执行时间
    image.png

提升约30倍

例4

同样的例子

SELECT
/*+ MAX_EXECUTION_TIME(20000) */
    '2023-01-15' AS `date`,
    COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' OR `event` = 'enterOBDSystemSuc' THEN user_id END ) AS total,
    COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemSuc' THEN user_id END ) AS success,
    COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' THEN user_id END ) AS fail 
FROM user_event_record 
WHERE
    `event` IN ( 'enterOBDSystemFail', 'enterOBDSystemSuc' ) 
    AND DATE_FORMAT( gmt_create, '%Y-%m-%d' ) = '2023-01-15';
  • 修改后的sql
    SELECT
  '2023-01-15' AS `date`,
  COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' OR `event` = 'enterOBDSystemSuc' THEN `user_id` ELSE NULL END ) AS `total`,
  COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemSuc' THEN `user_id` ELSE NULL END ) AS `success`,
  COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' THEN `user_id` ELSE NULL END ) AS `fail` 
FROM `user_event_record` 
WHERE
  `event` IN ( 'enterOBDSystemFail', 'enterOBDSystemSuc' ) 
  AND `gmt_create` >= DATE( '2023-01-15' ) 
  AND `gmt_create` < DATE_ADD( DATE( '2023-01-15' ), INTERVAL 1 DAY );
    查询速度对比

  • 原sql执行时间
    image.png

  • 修改后的sql执行时间
    image.png

提升约40倍

例5

  • 原sql
    SELECT
  IFNULL( DATE_FORMAT( gmt_create, '%Y-%m-%d' ), '2022-12-01' ) `date`,
  COUNT( DISTINCT CASE WHEN mark = '10000' THEN user_id END ) AS upgradeFailCount,
  COUNT( DISTINCT CASE WHEN mark = '10001' THEN user_id END ) AS enterSysFailCount,
  COUNT( DISTINCT CASE WHEN mark = '10008' THEN user_id END ) AS dllLoadFailCount,
  COUNT( DISTINCT CASE WHEN mark = '10009' THEN user_id END ) AS enableDeviceFailCount 
FROM file_record_info 
WHERE
  mark IN ('10000', '10001', '10008', '10009') 
  AND DATE_FORMAT(gmt_create, '%Y-%m-%d')>='2022-12-01'
GROUP BY `date`
  • 修改后的sql
    SELECT
  IFNULL( DATE_FORMAT( gmt_create, '%Y-%m-%d' ), '2022-12-01' ) `date`,
  COUNT( DISTINCT CASE WHEN mark = '10000' THEN user_id END ) AS upgradeFailCount,
  COUNT( DISTINCT CASE WHEN mark = '10001' THEN user_id END ) AS enterSysFailCount,
  COUNT( DISTINCT CASE WHEN mark = '10008' THEN user_id END ) AS dllLoadFailCount,
  COUNT( DISTINCT CASE WHEN mark = '10009' THEN user_id END ) AS enableDeviceFailCount 
FROM file_record_info 
WHERE
  mark IN ('10000', '10001', '10008', '10009') 
  AND gmt_create > DATE('2022-12-01') 
GROUP BY `date`
    查询速度对比

  • 原sql执行时间
    image.png

  • 修改后的sql执行时间
    image.png

有所提升。

--------2023-05-13补充-------

例6：百万数据分页

  • 原sql
    SELECT  * FROM sys_oper_log WHERE  title like '%用户服务-上报用户事件%' LIMIT 1000000,10
  • 修改后的sql
    SELECT u.* FROM (SELECT oper_id FROM sys_oper_log WHERE title like '%用户服务-上报用户事件%' LIMIT 1000000,10) AS temp INNER JOIN sys_oper_log u ON u.oper_id = temp.oper_id
    查询速度对比

  • 原sql执行时间
    image.png

  • 修改后的sql执行时间
    image.png

这里是使用了覆盖索引，避免了回表操作，查询性能提升约60倍

总结

  1. 首先索引一定要根据业务来创建，不然没办法命中，可以通过EXPLAIN关键字来分析。
  2. 尽量避免DATE_FORMAT函数
  3. 优化sql，避免不必要的回表操作
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
RDS
数据库
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版慢查询
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
左边的天堂
目录
相关文章
jianz123
|
21天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
jianz123
16 0
技术蜜糖罐
|
27天前
|
存储 关系型数据库 MySQL
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
技术蜜糖罐
46 0
技术蜜糖罐
|
27天前
|
存储 SQL 关系型数据库
轻松入门MySQL：加速进销存！利用MySQL存储过程轻松优化每日销售统计（15）
轻松入门MySQL：加速进销存！利用MySQL存储过程轻松优化每日销售统计（15）
技术蜜糖罐
54 0
技术蜜糖罐
|
27天前
|
存储 关系型数据库 MySQL
轻松入门MySQL：优化复杂查询，使用临时表简化数据库查询流程（13）
轻松入门MySQL：优化复杂查询，使用临时表简化数据库查询流程（13）
技术蜜糖罐
29 0
技术蜜糖罐
|
27天前
|
存储 关系型数据库 MySQL
轻松入门MySQL：优化进销存管理，掌握MySQL索引，提升系统效率（11）
轻松入门MySQL：优化进销存管理，掌握MySQL索引，提升系统效率（11）
技术蜜糖罐
33 0
风水道人
|
29天前
|
存储 SQL 关系型数据库
mysql优化一
mysql优化一
风水道人
17 0
技术蜜糖罐
|
27天前
|
SQL 监控 关系型数据库
深入理解MySQL日志：通用查询、慢查询和错误日志详解
深入理解MySQL日志：通用查询、慢查询和错误日志详解
技术蜜糖罐
77 0
jianz123
|
21天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
jianz123
87 1
技术蜜糖罐
|
27天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘：表设计优化的高效策略（优化数据类型、增加冗余字段、拆分表以及使用非空约束）
MySQL数据库性能大揭秘：表设计优化的高效策略（优化数据类型、增加冗余字段、拆分表以及使用非空约束）
技术蜜糖罐
36 0
技术蜜糖罐
|
27天前
|
缓存 关系型数据库 MySQL
MySQL查询优化：提速查询效率的13大秘籍（合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作）（下）
MySQL查询优化：提速查询效率的13大秘籍（合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作）（下）
技术蜜糖罐
45 0

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    [MySQL]事务原理之redo log,undo log
    46
  • 2
    [MySQL]SQL优化之索引的使用规则
    36
  • 3
    [MySQL] SQL优化之性能分析
    33
  • 4
    【mysql】—— 用户管理
    31
  • 5
    【mysql】—— 视图
    32
  • 6
    【mysql】—— 函数的基本介绍
    26
  • 7
    【mysql】—— 表的增删改查
    41
  • 8
    【mysql】—— 表的约束
    33
  • 9
    【MYSQL】—— MySQL 在 Centos 7环境安装
    56
  • 10
    MySQL模糊查询二三事
    25

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考