手机网站开发平台,泰州住房城乡建设网站,中国空间站照片,成都官网优化推广1. 临时表#xff1a;MySQL性能的隐形杀手#xff0c;你注意到了吗#xff1f; 大家好#xff0c;我是老张#xff0c;一个和MySQL打了十几年交道的“老运维”。今天想和大家聊聊一个平时不太起眼#xff0c;但关键时刻能让你数据库“卡脖子”的参数——tmp_table_size。…1. 临时表MySQL性能的隐形杀手你注意到了吗大家好我是老张一个和MySQL打了十几年交道的“老运维”。今天想和大家聊聊一个平时不太起眼但关键时刻能让你数据库“卡脖子”的参数——tmp_table_size。很多朋友在调优时眼睛总盯着innodb_buffer_pool_size、query_cache_size这些“大明星”却忽略了临时表这个“幕后工作者”。结果就是明明硬件配置不差索引也建得挺合理一到跑复杂报表或者大分组查询的时候系统就慢得跟蜗牛一样CPU和IO还居高不下。这时候十有八九就是临时表在“搞鬼”。简单来说临时表就是MySQL在执行一些复杂查询时为了完成中间计算而临时创建的表。比如你做GROUP BY、DISTINCT、UNION或者一些复杂的ORDER BY时MySQL就可能需要先把一部分数据捞出来放到一个临时的地方进行排序、去重或者聚合这个地方就是临时表。理想情况下这个临时表应该放在内存里操作速度飞快。但内存空间是有限的如果这个临时表太大内存里放不下了MySQL就不得不把它写到磁盘上。从内存操作切换到磁盘操作这个性能落差就好比你从开法拉利跑车瞬间换成了骑自行车速度能不掉吗tmp_table_size这个参数就是用来定义这个“内存临时表”的最大尺寸的。它就像一个工作台的大小。工作台够大tmp_table_size设置合理所有工具和零件都能摆开你干活就利索。工作台太小东西摆不下你就得不停地弯腰从地上的工具箱磁盘里取放工具效率自然大打折扣。所以理解并调优这个参数对于处理线上那些复杂的分析型查询、报表生成任务至关重要。接下来我就带大家从实战出发看看怎么揪出临时表性能问题并把它搞定。2. 实战诊断如何发现临时表正在拖慢你的数据库光说理论没感觉我们直接上实战。诊断临时表问题不能靠猜MySQL给我们留了几个非常实用的“观察窗口”。我最常用的就是两个状态变量Created_tmp_tables和Created_tmp_disk_tables。你可以把它们想象成工厂的生产报表Created_tmp_tables告诉你今天一共生产了多少个产品创建了多少临时表而Created_tmp_disk_tables则告诉你因为生产线内存不够用有多少产品被转到又慢又费劲的外包车间磁盘去生产了。2.1 看懂状态变量你的数据库“健康体检报告”查看方法非常简单在MySQL命令行里执行SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;你会看到类似这样的结果-------------------------------- | Variable_name | Value | -------------------------------- | Created_tmp_disk_tables | 12345 | | Created_tmp_files | 10 | | Created_tmp_tables | 98765 | --------------------------------这里的关键指标是磁盘临时表占比。你可以心算一下Created_tmp_disk_tables/Created_tmp_tables。如果这个比例很高比如超过10%甚至更高那就亮起红灯了说明有大量临时表无法在内存中完成被迫落盘性能瓶颈已经出现。我遇到过的一个典型案例是一个电商平台的每日销售汇总报表每天凌晨生成时总会超时。一查这个状态发现磁盘临时表占比高达30%。这意味着有将近三分之一的临时表操作都在和慢吞吞的磁盘打交道不慢才怪。所以定期检查这个全局状态是发现潜在问题的第一步。2.2 使用 EXPLAIN 命令提前预知查询的“行为”除了事后统计我们还能在查询执行前就预判它会不会使用临时表。这就需要用到EXPLAIN这个神器。在你要分析的SQL语句前加上EXPLAIN比如EXPLAIN SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;仔细看输出结果中的Extra列。如果这里出现了Using temporary那就明确告诉你MySQL执行这个查询需要创建临时表。如果再结合Using filesort说明还需要在临时表上进行排序这通常是性能消耗的大户。通过EXPLAIN我们可以在开发阶段或上线前就识别出那些可能产生临时表的“高危”查询语句从而有针对性地进行优化比如调整索引、改写SQL或者为这类查询提前准备好合适的tmp_table_size环境。这是一种非常主动的性能治理方式。2.3 模拟一个真实场景调小参数感受性能断崖纸上得来终觉浅我们直接动手做个对比实验让大家直观感受一下。我准备了一个模拟订单表orders里面有上百万条数据。现在要执行一个典型的报表查询按月份和业务员统计订单数量、总金额和平均金额。SELECT DATE_FORMAT(o_orderDATE, ‘%Y-%m’), o_clerk, COUNT(1), SUM(o_totalprice), AVG(o_totalprice) FROM orders GROUP BY DATE_FORMAT(o_orderDATE, ‘%Y-%m’), o_clerk;首先在tmp_table_size为默认的16MB时我执行了这个查询耗时大约7.3秒。执行后查看状态Created_tmp_tables增加了1而Created_tmp_disk_tables为0说明这个临时表完全在内存中完成。接着我故意把tmp_table_size调到一个非常小的值比如16KBSET tmp_table_size 16 * 1024; -- 设置为16KB然后再次执行同样的查询。这次耗时猛增到了18.5秒翻了一倍还多再次检查状态发现Created_tmp_disk_tables变成了1。这说明因为内存临时表空间不足MySQL不得不将临时表写入磁盘巨大的性能损耗立刻体现了出来。这个实验清晰地证明了一个不合理的tmp_table_size设置对复杂查询的影响是毁灭性的。3. 深入原理tmp_table_size 如何影响查询生命周期知道了现象我们还得挖一挖背后的原理这样才能举一反三。当MySQL决定使用临时表时它会经历一个标准的决策和执行流程。首先查询优化器会估算这个临时表大概需要多大的存储空间。这个估算基于要处理的行数、每行数据的类型和长度。比如你的GROUP BY涉及几个VARCHAR(255)字段和一个BIGINT那每行占用的空间就比只涉及几个INT字段要大得多。优化器拿着这个估算值去和tmp_table_size以及另一个相关参数max_heap_table_size内存表最大尺寸做比较。tmp_table_size和max_heap_table_size两者中较小的那个决定了内存临时表的最大可用空间。如果估算大小小于这个限制皆大欢喜MySQL会在内存中创建一个Memory引擎的临时表所有操作都在内存中进行速度极快。如果估算大小超过了限制MySQL就会启动“B计划”在磁盘上创建一个MyISAM或InnoDB引擎的临时表取决于版本和配置。这个切换动作本身有开销更重要的是磁盘I/O速度比内存慢几个数量级尤其是当临时表需要被多次读写时性能差距会非常明显。这里有一个容易踩的坑tmp_table_size是会话级的参数但max_heap_table_size是全局的。也就是说每个连接会话都能创建不超过tmp_table_size大小的内存临时表但所有内存表包括用户创建的MEMORY引擎表和内存临时表的总占用不能超过max_heap_table_size。所以在调整时通常建议将这两个参数设置为相同的值避免因为max_heap_table_size更小而造成意料之外的限制。4. 优化策略给你的临时表找到“黄金尺寸”诊断出问题理解了原理接下来就是最关键的优化环节了。给tmp_table_size设置一个合理的值不是拍脑袋决定的需要结合监控数据和业务特点。4.1 如何确定一个合理的初始值对于大多数线上业务我建议可以从32MB或64MB开始。这个值比默认的16MB大能为多数中小型分组查询提供足够的内存空间同时又不会过度消耗服务器内存。你可以通过监控一段时间内的Created_tmp_disk_tables增长情况来验证。如果设置后磁盘临时表数量增长明显变缓甚至停止说明这个值初步有效。那么服务器有多少内存可以分配给临时表呢这里需要一个整体的考量。假设你有一台专门用于数据库的服务器内存是64GB。通常我们会把50%-70%的内存分配给innodb_buffer_pool_size这是InnoDB的缓存池是性能的核心。剩下的内存要留给操作系统、其他缓存以及像临时表这样的操作内存。你可以用下面这个公式做个粗略估算可用内存 ≈ 总内存 - InnoDB缓冲池 - 操作系统预留通常2-4GB - 其他开销然后从可用内存中划出一部分给tmp_table_size。对于高并发、复杂查询多的系统需要保守一点因为每个连接都可能创建临时表。对于并发低、但单个查询很重的分析型系统可以给得大方一些。4.2 针对不同工作负载的精细化调整一刀切的配置往往不是最优解。我们需要根据系统的具体工作负载来调整。OLTP在线事务处理系统这类系统特点是并发高但单个事务或查询通常比较简单、快速很少使用大临时表。对于这种场景tmp_table_size不宜设置过大16MB到32MB通常足够了。设置过大反而可能导致内存浪费或者在极少数产生大临时表的查询出错时瞬间占用过多内存影响其他正常事务。OLAP在线分析处理或报表系统这类系统恰恰相反并发可能不高但跑的都是非常复杂的查询涉及大量数据的GROUP BY、DISTINCT、JOIN和排序。这是临时表使用的“重灾区”。对于这类系统应该显著调高tmp_table_size比如设置为256MB、512MB甚至1GB。同时也要确保max_heap_table_size同步调大。目标是让绝大多数分析查询的临时表都能在内存中完成这是提升此类系统性能最直接有效的手段之一。4.3 配置修改与验证操作指南确定了优化值修改起来很简单。有两种方式动态修改立即生效但重启失效SET GLOBAL tmp_table_size 33554432; -- 设置为32MB SET GLOBAL max_heap_table_size 33554432; -- 建议同时设置注意动态修改只对新建立的连接会话有效已有的连接不会生效。所以通常用于临时调整和测试。永久修改写入配置文件重启生效 打开MySQL的配置文件my.cnf或my.ini在[mysqld]段落下添加[mysqld] tmp_table_size 64M max_heap_table_size 64M修改保存后重启MySQL服务使配置生效。修改之后验证工作至关重要。不要以为改完就万事大吉。你需要在业务高峰期再次执行SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;观察磁盘临时表的增长速率是否显著下降。找几个之前已知的慢查询特别是EXPLAIN显示Using temporary的重新跑一下对比执行时间是否有改善。监控服务器整体内存使用情况确保没有因为参数调大而导致内存紧张。5. 超越参数还有哪些优化临时表的招数调整tmp_table_size是治标有时候我们还需要治本。优化临时表性能不仅仅是一个参数的事情更需要从查询和设计层面入手。5.1 查询语句优化从源头减少临时表的使用很多时候产生临时表是因为查询语句写得不够高效。我们可以尝试改写SQL来避免或减少临时表。为GROUP BY和ORDER BY使用索引这是最有效的方法之一。如果GROUP BY或ORDER BY的字段上有合适的索引MySQL可能直接利用索引的有序性来完成分组和排序根本不需要创建临时表。比如上面的例子如果在(o_orderDATE, o_clerk)上建立一个联合索引性能可能会有飞跃。避免使用SELECT *只选择需要的列临时表里存储的就是你SELECT的列。如果你用SELECT *会把所有列包括你根本用不上的TEXT、BLOB大字段都塞进临时表瞬间就可能把内存撑爆。明确列出需要的字段能极大减小临时表的体积。谨慎使用DISTINCT不必要的DISTINCT会强制MySQL进行去重操作常常需要临时表。在写DISTINCT前先想想这个去重是不是真的必要。优化子查询和UNION有些子查询和UNION的实现方式会导致产生临时表。可以尝试将子查询改写为JOIN或者评估UNION ALL是否能替代UNIONUNION ALL不去重有时可以避免临时表。5.2 利用更强大的监控工具MySQL自带的慢查询日志默认不记录临时表的使用详情这给我们排查问题带来了不便。这里可以借助一些更强大的工具。Percona Server的增强慢查询日志如果你使用的是Percona Server这个MySQL分支版本它的慢查询日志会详细记录查询使用了磁盘临时表还是内存临时表、临时表的大小等信息。这对于精准定位哪些查询是“临时表大户”非常有帮助。性能模式Performance SchemaMySQL 5.7及以上版本自带的Performance Schema功能非常强大。你可以通过查询performance_schema库中的表例如events_statements_summary_by_digest来分析哪些SQL模式Digest产生了最多的临时表或磁盘临时表实现更细粒度的监控。5.3 关注MySQL版本升级带来的红利数据库引擎也在不断进化。特别是MySQL 8.0在临时表方面做了很多优化。新的临时表引擎MySQL 8.0引入了TempTable存储引擎作为内存临时表的默认引擎通过internal_tmp_mem_storage_engine参数控制。相比老的MEMORY引擎TempTable对变长数据类型如VARCHAR,TEXT的支持更好存储效率更高这意味着同样大小的tmp_table_size能容纳更多数据。默认的磁盘临时表引擎变为InnoDB以前磁盘临时表默认使用MyISAM引擎现在默认使用InnoDB。InnoDB支持事务对于临时表虽然用处不大和行级锁在某些并发场景下可能比MyISAM的表级锁表现更好。所以如果你的系统还在使用MySQL 5.6或5.7并且深受临时表性能问题困扰升级到8.0本身可能就会带来一定的性能提升。当然升级前务必做好充分的测试。6. 避坑指南tmp_table_size 调优中的常见误区最后结合我这些年踩过的坑给大家提几个醒避免在优化时走弯路。误区一越大越好。这是最危险的误区。盲目将tmp_table_size设置为几个GB会带来严重风险。如果一个复杂查询错误地生成了一个超大的临时表它会瞬间吃掉大量内存可能导致MySQL因为内存不足OOM而崩溃或者触发系统级的OOM Killer杀掉MySQL进程。内存是共享资源必须谨慎分配。误区二只调tmp_table_size忘了max_heap_table_size。就像前面说的这两个参数是联动的取最小值生效。你兴冲冲地把tmp_table_size改成1G结果max_heap_table_size还是默认的16M那内存临时表最大也只能到16M调整完全没效果。记住要改就一起改并且保持两者值一致是最省心的做法。误区三设置后不监控。调优不是一劳永逸的。业务在增长查询在变化。今天合适的值半年后可能就不合适了。你需要把Created_tmp_disk_tables的监控纳入日常的数据库健康检查中设定一个告警阈值比如磁盘临时表占比连续超过5%当告警触发时就是需要重新审视和调整的时候了。误区四忽视SQL本身的优化。参数调优是辅助SQL优化才是根本。如果一个查询因为缺少索引而不得不对百万行数据创建临时表排序那么你把tmp_table_size调到再大也只是让它从“磁盘慢”变成“内存慢”本质上还是慢。正确的思路永远是先优化查询和索引尽可能让查询不产生或产生更小的临时表然后再用tmp_table_size来为那些确实需要临时表的查询提供一个良好的运行环境。调优tmp_table_size的过程其实就是一场在内存资源、查询复杂度和执行效率之间的精细平衡。没有放之四海而皆准的“最佳值”只有最适合你当前业务状态的“黄金值”。多观察、多测试、循序渐进你就能找到这个平衡点让数据库里的那些“临时工”们也能高效地完成任务。