【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现, 查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右 。 SQL语句如下: SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROM gm_game g LEFT
【问题现象】
线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。
SQL语句如下:
SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROM gm_game
g LEFT JOIN gm_cp
cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category
c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type
t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;
【问题分析】
使用explain查看执行计划,结果如下:
这条sql语句的问题其实还是比较明显的:
查询了大量数据(包括数据条数、以及g.* ),然后使用临时表order by,但最终又只返回了20条数据。
DBA观察到的IO高,是因为sql语句生成了一个巨大的临时表,内存放不下,于是全部拷贝到磁盘,导致IO飙升。
【优化方案】
优化的总体思路是拆分sql,将排序操作和查询所有信息的操作分开。
第一条语句:查询符合条件的数据,只需要查询g.id即可
SELECT DISTINCT g.id FROM gm_game
g LEFT JOIN gm_cp
cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category
c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type
t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;
第二条语句:查询符合条件的详细数据,将第一条sql的结果使用in操作拼接到第二条的sql
SELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROM gm_game
g LEFT JOIN gm_cp
cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category
c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type
t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;
【实测效果】
在SATA机器上测试,优化前大约需要50s,优化后第一条0.3s,第二条0.1s,优化后执行速度是原来的100倍以上,IO从100%降到不到1%
在SSD机器上测试,优化前大约需要7s,优化后第一条0.3s,第二条0.1s,优化后执行速度是原来的10倍以上,IO从100%降到不到1%
可以看出,优化前磁盘io是性能瓶颈,SSD的速度要比SATA明显要快,优化后磁盘不再是瓶颈,SSD和SATA性能没有差别。
【理论分析】
MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。
- 临时表存储
MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎;
一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表;
Linux平台上缺省是/tmp目录,/tmp目录小的系统要注意啦。
- 使用临时表的场景
1)ORDER BY子句和GROUP BY子句不同, 例如:ORDERY BY price GROUP BY name;
2)在JOIN查询中,ORDER BY或者GROUP BY使用了不是第一个表的列 例如:SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
3)ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
4)SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下,我们没有必要使用这个选项,让MySQL服务器选择即可。
- 直接使用磁盘临时表的场景
1)表包含TEXT或者BLOB列;
2)GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列;
3)使用UNION或者UNION ALL时,SELECT子句中包含大于512字节的列;
- 临时表相关配置
tmp_table_size:指定系统创建的内存临时表最大大小; http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size
max_heap_table_size: 指定用户创建的内存表的最大大小; http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size
注意:最终的系统创建的内存临时表大小是取上述两个配置值的最小值。
- 表的设计原则
使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有:
1)创建索引:在ORDER BY或者GROUP BY的列上创建索引;
2)分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。
- SQL优化
如果表的设计已经确定,修改比较困难,那么也可以通过优化SQL语句来减少临时表的大小,以提升SQL执行效率。
常见的优化SQL语句方法如下:
1)拆分SQL语句
临时表主要是用于排序和分组,很多业务都是要求排序后再取出详细的分页数据,这种情况下可以将排序和取出详细数据拆分成不同的SQL,以降低排序或分组时临时表的大小,提升排序和分组的效率,我们的案例就是采用这种方法。
2)优化业务,去掉排序分组等操作
有时候业务其实并不需要排序或分组,仅仅是为了好看或者阅读方便而进行了排序,例如数据导出、数据查询等操作,这种情况下去掉排序和分组对业务也没有多大影响。
- 如何判断使用了临时表?
使用explain查看执行计划,Extra列看到Using temporary就意味着使用了临时表。
详细信息请参考MySQL官方手册: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
原文地址:优化临时表使用,SQL语句性能提升100倍, 感谢原作者分享。

GeforceExperience不仅为您下载最新版本的游戏驱动程序,它还提供更多!最酷的事情之一是它可以根据您的系统规格优化您安装的所有游戏,为您提供最佳的游戏体验。但是一些游戏玩家报告了一个问题,即GeForceExperience没有优化他们系统上的游戏。只需执行这些简单的步骤即可在您的系统上解决此问题。修复1–为所有游戏使用最佳设置您可以设置为所有游戏使用最佳设置。1.在您的系统上打开GeForceExperience应用程序。2.GeForceExperience面

Nginx是一种常用的Web服务器,代理服务器和负载均衡器,性能优越,安全可靠,可以用于高负载的Web应用程序。在本文中,我们将探讨Nginx的性能优化和安全设置。一、性能优化调整worker_processes参数worker_processes是Nginx的一个重要参数。它指定了可以使用的worker进程数。这个值需要根据服务器硬件、网络带宽、负载类型等

如果您在Windows机器上玩旧版游戏,您会很高兴知道Microsoft为它们计划了某些优化,特别是如果您在窗口模式下运行它们。该公司宣布,最近开发频道版本的内部人员现在可以利用这些功能。本质上,许多旧游戏使用“legacy-blt”演示模型在您的显示器上渲染帧。尽管DirectX12(DX12)已经利用了一种称为“翻转模型”的新演示模式,但Microsoft现在也正在向DX10和DX11游戏推出这一增强功能。迁移将改善延迟,还将为自动HDR和可变刷新率(VRR)等进一步增强打

随着互联网的不断发展和应用的扩展,越来越多的网站和应用需要处理海量的数据和实现高流量的访问。在这种背景下,对于PHP和MySQL这样的常用技术,缓存优化成为了非常必要的优化手段。本文将在介绍缓存的概念及作用的基础上,从两个方面的PHP和MySQL进行缓存优化的实现,希望能够为广大开发者提供一些帮助。一、缓存的概念及作用缓存是指将计算结果或读取数据的结果缓存到

昨天一个跑了220个小时的微调训练完成了,主要任务是想在CHATGLM-6B上微调出一个能够较为精确的诊断数据库错误信息的对话模型来。不过这个等了将近十天的训练最后的结果令人失望,比起我之前做的一个样本覆盖更小的训练来,差的还是挺大的。这样的结果还是有点令人失望的,这个模型基本上是没有实用价值的。看样子需要重新调整参数与训练集,再做一次训练。大语言模型的训练是一场军备竞赛,没有好的装备是玩不起来的。看样子我们也必须要升级一下实验室的装备了,否则没有几个十天可以浪费。从最近的几次失败的微调训练来看

MySQL是目前最流行的关系型数据库之一,但是在处理大量数据时,MySQL的性能可能会受到影响。其中,一种常见的性能瓶颈是查询中的LIKE操作。在MySQL中,LIKE操作是用来模糊匹配字符串的,它可以在查询数据表时用来查找包含指定字符或者模式的数据记录。但是,在大型数据表中,如果使用LIKE操作,它会对数据库的性能造成影响。为了解决这个问题,我们可

5月26日消息,SnapchatAR试穿滤镜技术升级,并与OPI品牌合作,推出指甲油AR试用滤镜。据悉,为了优化AR滤镜对手指甲的追踪定位,Snap在LensStudio中推出手部和指甲分割功能,允许开发者将AR图像叠加在指甲这种细节部分。据青亭网了解,指甲分割功能在识别到人手后,会给手部和指甲分别设置掩膜,用于渲染2D纹理。此外,还会识别用户个人指甲的底色,来模拟指甲油真实上手的效果。从演示效果来看,新的AR指甲油滤镜可以很好的模拟浅蓝磨砂质地。实际上,此前Snapchat曾推出AR指甲油试用

Go语言是一门相对年轻的编程语言,虽然从语言本身的设计来看,其已经考虑到了很多优化点,使得其具备高效的性能和良好的可维护性,但是这并不代表着我们在开发Go应用时不需要优化和重构,特别是在长期的代码积累过程中,原来的代码架构可能已经开始失去优势,需要通过优化和重构来提高系统的性能和可维护性。本文将分享一些在Go语言中优化和重构的方法,希望能够对Go开发者有所帮


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Zend Studio 13.0.1
Powerful PHP integrated development environment

SublimeText3 Chinese version
Chinese version, very easy to use

SublimeText3 Linux new version
SublimeText3 Linux latest version

Notepad++7.3.1
Easy-to-use and free code editor

Dreamweaver CS6
Visual web development tools
