一条SQL引起的系统不可用

最新推荐文章于 2024-04-15 16:04:18 发布
最新推荐文章于 2024-04-15 16:04:18 发布
阅读量1.1k 收藏 19
点赞数 23
分类专栏： java 数据库 文章标签： sql java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dongjing991/article/details/136447817
版权
java 同时被 2 个专栏收录
441 篇文章 28 订阅
订阅专栏
数据库
19 篇文章 1 订阅
订阅专栏

一.前言

最近在运维系统，系统对客端突然报了403错误，从后台看发现了大量的慢SQL，导致查询超时，仔细分析我从来没见过那么厚颜无耻的SQL，一条SQL语句关联了一个大表（6000数据）查询了10次。我也很少见过一个SQL语句写了500多行。将一个很大的任务放在一个SQL里计算。以前能跑得起来是因为数据量少，现在表的数据量增加到6000万。性能急剧下降。

二、慢SQL分析（没见过如此厚颜无耻的SQL）

select
  count(1) as cnt,
  user_tag_user_life_period as label_value,
  'user_tag_user_life_period' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_user_life_period
having
  user_tag_user_life_period is not null
  and user_tag_user_life_period <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_level_name_taxfree as label_value,
  'user_tag_level_name_taxfree' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_level_name_taxfree
having
  user_tag_level_name_taxfree is not null
  and user_tag_level_name_taxfree <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_level_name_travel as label_value,
  'user_tag_level_name_travel' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_level_name_travel
having
  user_tag_level_name_travel is not null
  and user_tag_level_name_travel <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_level_name_hotel as label_value,
  'user_tag_level_name_hotel' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_level_name_hotel
having
  user_tag_level_name_hotel is not null
  and user_tag_level_name_hotel <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_taxfree_and_travel_sex as label_value,
  'user_tag_taxfree_and_travel_sex' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_taxfree_and_travel_sex
having
  user_tag_taxfree_and_travel_sex is not null
  and user_tag_taxfree_and_travel_sex <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_taxfree_and_travel_user as label_value,
  'user_tag_taxfree_and_travel_user' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_taxfree_and_travel_user
having
  user_tag_taxfree_and_travel_user is not null
  and user_tag_taxfree_and_travel_user <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_taxfree_and_hotel_sex as label_value,
  'user_tag_taxfree_and_hotel_sex' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_taxfree_and_hotel_sex
having
  user_tag_taxfree_and_hotel_sex is not null
  and user_tag_taxfree_and_hotel_sex <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_taxfree_and_hotel_user as label_value,
  'user_tag_taxfree_and_hotel_user' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_taxfree_and_hotel_user
having
  user_tag_taxfree_and_hotel_user is not null
  and user_tag_taxfree_and_hotel_user <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_last_year_amount as label_value,
  'user_tag_last_year_amount' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_last_year_amount
having
  user_tag_last_year_amount is not null
  and user_tag_last_year_amount <> ''
order by
  cnt desc
limit
  1
union all
select
  count(1) as cnt,
  user_tag_last_year_frequency as label_value,
  'user_tag_last_year_frequency' as label_english
from
  data_tag.tag_user_attribute_all
where
  one_id global in (
    select
      one_id
    from
      data_dwd.big_table final
    where
      (
        (
          case
            when reg_time_taxfree is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_hotel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_travel is not null then 1
            else 0
          end
        ) + (
          case
            when reg_time_invest is not null then 1
            else 0
          end
        )
      ) >= 2
  )
group by
  user_tag_last_year_frequency
having
  user_tag_last_year_frequency is not null
  and user_tag_last_year_frequency <> ''
order by
  cnt desc
limit 1;

这个SQL引发系统403，

一个重要设计缺陷是查询频繁。

第二个是这个查询放在对客端的微服务中开启了线程，这种设计严重影响对客端的性能和流畅度，这种业务应放在后台管理系统计算，而不是对客端。

第三、就是这个业务没有进行分解，是个大业务SQL 。

第四、这是一个无耻不考虑后果的SQL。没有考虑到单表数据量的暴增。

三、如何解决慢SQL和避免慢SQL

解决慢SQL（慢查询）和避免慢SQL是数据库优化中的关键任务。以下是一些建议和方法，可以帮助你解决和避免慢SQL：

1. 优化查询语句

  • 使用索引：确保查询中使用的字段都已经建立了索引，这可以大大提高查询速度。
  • **避免SELECT ***：只选择需要的字段，而不是选择所有字段。
  • 使用连接（JOIN）代替子查询：当可能时，使用JOIN操作代替子查询。
  • 优化WHERE子句：避免在WHERE子句中使用函数或计算，这会导致索引失效。

2. 优化数据库设计

  • 正规化：通过正规化来减少数据冗余。
  • 反正规化：在某些情况下，为了查询性能，可以故意引入一些冗余。
  • 分区：将大表分区，可以提高查询性能。

3. 优化数据库配置

  • 调整缓存大小：根据工作负载调整数据库的缓存大小。
  • 调整I/O性能：确保数据库服务器有足够的I/O性能。
  • 监控和调整并发连接数：根据实际需要调整最大并发连接数。

4. 使用分析工具

  • 慢查询日志：启用数据库的慢查询日志功能，找出执行时间长的查询。
  • EXPLAIN计划：使用EXPLAIN语句查看查询的执行计划，找出性能瓶颈。

5. 硬件和存储优化

  • 使用更快的存储：例如，使用SSD替代HDD。
  • 增加内存：为数据库服务器增加更多的内存。
  • 优化I/O配置：确保I/O子系统（如RAID配置）是最优的。

6. 避免常见错误

  • 避免在循环中执行查询：这会导致大量的数据库连接和查询。
  • 避免使用LIKE操作符进行前缀模糊匹配：这会导致全表扫描。

7. 定期维护

  • 更新统计信息：定期更新数据库的统计信息，以便优化器做出更好的决策。
  • 重建索引：定期重建或优化索引，保持其性能。

8. 考虑使用缓存

  • 查询缓存：某些数据库支持查询缓存，可以考虑启用。
  • 外部缓存：如Redis或Memcached，用于缓存热点数据。

9. 考虑分布式解决方案

  • 读写分离：将读操作和写操作分离到不同的服务器上。
  • 分片：将数据分布到多个数据库服务器上。

10. 持续监控和学习

  • 监控数据库性能：使用监控工具持续监控数据库性能。
  • 持续学习：数据库技术和最佳实践在不断变化，保持学习是关键。

 四、常见SQL优化方法

常见的SQL优化方法包括以下几个方面：

  1. 选择特定字段：尽量避免使用SELECT *，而是选择你真正需要的具体字段。这样可以减少不必要的数据传输和处理，从而提高查询效率。

  2. 使用索引：确保查询中使用的字段都已经建立了索引。索引可以大大提高查询速度，因为数据库可以快速定位到数据而不需要全表扫描。

  3. 优化WHERE子句：

    • 避免在WHERE子句中使用函数或计算，因为这会导致索引失效。
    • 尽量避免使用OR来连接条件，因为当OR两边的字段不是索引字段时，查询可能不走索引。
    • 尽量避免在索引列上使用MySQL的内置函数。

  4. 优化JOIN操作：优先使用INNER JOIN，如果是LEFT JOIN，确保左边表的结果集尽量小。

  5. 使用LIMIT：当只需要一条或少数几条记录时，使用LIMIT来限制返回的结果集大小。

  6. 优化LIKE查询：尽量避免使用前缀模糊查询（如LIKE '%li%'），因为它会导致全表扫描。如果可能，尽量使用后缀模糊查询（如LIKE 'li%'）。

  7. 避免使用子查询：当可能时，使用JOIN操作代替子查询。

  8. 优化排序操作：如果排序字段没有用到索引，尽量减少排序操作。

  9. 考虑表的设计：正规化和反正规化可以影响查询性能。确保你的表设计是合理的，并且考虑了查询性能。

  10. 使用分析工具：利用数据库的慢查询日志功能和EXPLAIN计划来找出性能瓶颈。

  11. 硬件和存储优化：确保数据库服务器有足够的硬件资源，如内存和I/O性能。使用更快的存储，如SSD，也可以提高性能。

  12. 避免常见错误：例如，避免在循环中执行查询，这会导致大量的数据库连接和查询。

  13. 定期维护：更新统计信息，重建或优化索引，以保持数据库性能。

  14. 考虑使用缓存：例如，使用查询缓存或外部缓存（如Redis或Memcached）来缓存热点数据。

  15. 持续监控和学习：使用监控工具持续监控数据库性能，并随着技术和最佳实践的发展保持学习。

结合这些策略和方法，你可以有效地优化SQL查询，提高数据库性能。

奋力向前123
关注
专栏目录
27、Flink 的SQL之SELECT (SQL Hints 和 Joins)介绍及详细示例（2-1）
alanchanchn的专栏
09-15 4万+
查询块（query block）是 SQL 语句的一个基础组成部分。例如，SQL 语句中任何的内联视图或者子查询（sub-query）都可以被当作外部查询的查询块。一个 SQL 语句可以由多个子查询组成，子查询可以是一个 SELECT，INSERT 或者 DELETE。子查询中又可以在 FROM 子句，WHERE 子句或者 在 UNION/UNION ALL 的子 SELECT 语句中包含其他的子查询。
一条sql执行很慢，可能是因为什么? 怎么优化？
热门推荐
孙荣达的博客
05-14 3万+
下面是网络中流传最广的一篇sql查询速度慢的原因及解决方法的文章，其对于处理mysql的慢查询有借鉴作用。由于此文转载多次，很难找到最开始的原文链接，就附送本人最先看到此文的链接:http://www.cnblogs.com/zhangwc/p/6431865.html sql查询速度慢的原因 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多
一条慢SQL导致购物车服务无法使用
Sam_Deep_Thinking
11-08 1万+
概述 今天一大早就紧张兮兮的，因为要处理一个大故障：【购物车服务无法使用了】，这个故障直接导致前端添加购物车、获取用户购物车列表等操作都失败了。购物车是入口，一旦出现问题，影响极其严重。 临时处理 购物车服务所有接口，是有打印响应时间的，发现比平时慢了很多。由于情况已是十万火急了，我只能先重启购物车，缓冲一下，然后利用这段缓冲时间，赶紧定位问题。 问题定位 之前对购物车应用基于Sprin...
高并发高可用秒杀系统(一)
AlbertYang的博客
01-30 5431
目录   1.需求分析 2. MyBatis简介 2.1MyBatis的基本用法 2.1.1环境搭建 2.1.2创建数据库和实体 2.1.3创建配置文件 2.1.4编写测试类 2.1.5源代码 1.需求分析 每逢双十一，各大电商网站和剁手党们就要忙的不亦乐乎了，商家为了多卖商品给宝贝们标出了放血的价格， 剁手党们也是参与各种抢购和秒杀活动。此时你是否会想到，面对瞬间爆发的访问...
一条慢SQL引发的改造
闲鱼技术的博客
03-02 2236
前言闲鱼服务端在做数据库查询时，对每一条SQL都需要仔细优化，尽可能使延时更低，带给用户更好的体验。但是在生产中偶尔会有一些情况怎么优化都无法满足业务场景。本文通过对一条慢SQL的真实改造...
flinksql获取系统当前时间搓_Flink之对时间的处理
weixin_36326008的博客
12-30 2919
window+trigger+watermark处理全局乱序数据，指定窗口上的allowedLateness可以处理特定窗口操作的局部事件时间乱序数据1、流处理系统中的微批Flink内部也使用了某种形式的微批处理技术，在shuffle阶段将含有多个事件的缓冲容器通过网络发送，而不是发送单个事件流处理系统中的批处理必须满足以下两点要求：批处理只作为提高系统性能的机制。批量越大，系统的吞吐量就越大。为...
sqlserver 从等待状态判断系统资源瓶颈
Hehuyi_In的博客
12-15 3710
一、 相关视图 2005、2008提供了以下三个视图供获取连接详细信息： DMV 用处 参考 Sys.dm_exec_requests 返回有关在SQL Server中执行的每个请求的信息，包括当前的等待状态 https://docs.microsoft.com/zh-cn/sql/relational...
数据库SqlServer面试题不完全总结
kalvin_y_liu的博客
10-27 2915
数据库SqlServer笔试题 一、数据库基础知识（通用）篇 1.说说主键、外键、超键、候选键 2.为什么用自增列作为主键？ 3.触发器的作用是什么？ 4.什么是存储过程？用什么来调用？ 5.说说存储过程的优缺点？ 6.说说存储过程与函数的区别 7.什么叫视图？游标是什么？ 8.视图的优缺点有哪些？ 9.说说drop、truncate、 delete区别 10.什么是临时表，临时表什么时候删除? 11.说说非关系型数据库和关系型数据库区别，优势比较? 12.什么是数据库范式，根据某个场景设计数据表? 13.
关于sql注入这一篇就够了(适合入门）
qq_53105813的博客
01-07 5381
sql注入详解
MySQL主从不一致问题相关处理
企业实战系列集 ●●● https://ximenjianxue.blog.csdn.net
09-26 1万+
某业务采取mysql的主从架构，但因为存储的问题，导致备库一直无法存储，数据同步一致性问题一直也未恢复，某次安全检查要求完成主备倒换演练，必须限期恢复主备，但是在恢复过程中，同步显示正常一段时间后，便会出现sql线程异常，主备数据不一致导致的同步错误情况。主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。根据上面的NR行数，查看附近的行，定位数据不一致的地方，后续手动补全。
通过SQL 2005 系统表监控 SQL Server 的运行状况
03-17
这条查询语句可以帮助我们快速了解系统中可用的所有DMV和DMF名称。 #### 监控CPU瓶颈 CPU瓶颈是常见的性能问题之一，它可能由多种因素引起，例如次优的查询计划、配置不当、设计缺陷或者硬件资源不足。下面的查询...
NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL、SQL-PaLM）、新一代数据集BIRD-SQL解读
最新发布
丨汀、的博客
04-15 1517
NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
SQL Server 2005数据库管理系统概述
而使用SQL Server 2005这样的数据库系统，可以避免这些问题，因为它支持数据依赖性，当删除或修改一条记录时，系统会自动处理相关联的数据，确保数据的一致性。 在数据库设计中，应尽量避免空值，因为空值可能引起...
会这个就够了-Visual paradigm – community
JAVA领域优质创作者，基于分片网络查询方法专利发明者。
03-09 1万+
不开源可免费用的软件设计工具：Visual paradigm – community为软件工程师、系统分析员、商业分析员、系统建筑师而设计的一个UML CASE工具。1.下载安装：免费的官网社区版链接为：https://www.visual-paradigm.com/download/community.jsp注意：安装过程中需要输入邮箱进行验证，按提示做即可2.设置中文菜单Window--&...
JAVA开发（记一次504 gateway timeout错误排查过程）
JAVA领域优质创作者，基于分片网络查询方法专利发明者。
06-27 1万+
最近在发布一个web项目，在测试环境都是可以的，发布到生产环境通过IP访问也是可以的，但是通过域名访问就出现504 gateway timeout。通过postman去测试接口也是一样。ip和端口都可以通，域名却不行，百思不得其解。通过一顿百度搜索，解析说通过nginx配置文件配置这几个地方
三谈ChatGPT（ChatGPT可以解决问题的90%）
JAVA领域优质创作者，基于分片网络查询方法专利发明者。
04-20 9035
这是我第三次谈ChatGPT，前两篇主要谈了ChatGPT的概念，之所以火的原因和对人们的影响，以及ChatGPT可能存在的安全风险和将面临的监管问题。这一篇主要讲讲ChatGPT的场景和处理问题的逻辑。
JAVA开发（分布式部署微服务注册到Eureka出现registration status: 204错误）
JAVA领域优质创作者，基于分片网络查询方法专利发明者。
06-13 8778
将服务注册到多个Eureka出现有点的服务只注册到一个Eureka有点的服务可以注册到多个Eureka的问题，有的注册不上，报错：registration status: 204
浅谈当下火热的ChatGPT
JAVA领域优质创作者，基于分片网络查询方法专利发明者。
04-11 8758
ChatGPT这个词语从今年初发布以来，一度成为一个火热的概念。包括CSDN也推出了C知道，不少大佬博友纷纷蹭热点，发布了无数关于ChatGPT的技术，使用，展望未来的文档和博文。其实很多文章我都没有看过，只知道当下这个工具非常的火，火到每一个人都需要和ChatGPT发生点关系，要不然你就落伍了，你不知道ChatGPT你就会被嘲笑。我最开始听到ChatGPT这个词是从抖音中知道，那时还是一些在美国的网友发的视频。从ChatGPT的概念中，我初步得知ChatGPT是一个聊天机器人，基于大语言模型训
用递归的方法输出1到100
JAVA领域优质创作者，基于分片网络查询方法专利发明者。
09-02 8078
想写一个使用递归的方法输出1到100，但是几次试验都没成功。 下面一段求1到100的和的成功了，输出结果为5050 int func(int num) { int sum; if (num > 1) sum = num + func(num-1); else sum = 0; return sum; }
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

奋力向前123

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值