{"id":33206,"date":"2026-09-16T12:00:00","date_gmt":"2026-09-16T04:00:00","guid":{"rendered":"https:\/\/cn.hostease.com\/blog\/?p=33206"},"modified":"2026-09-16T21:58:10","modified_gmt":"2026-09-16T13:58:10","slug":"mysql-mysqldumpslow-sql","status":"publish","type":"post","link":"https:\/\/cn.hostease.com\/blog\/guides\/mysql-mysqldumpslow-sql\/","title":{"rendered":"MySQL \u6162\u65e5\u5fd7\u5f00\u542f\u4e0e\u5206\u6790\uff1amysqldumpslow \u5b9a\u4f4d\u4f4e\u6548 SQL"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/cn.hostease.com\/blog\/wp-content\/uploads\/2026\/09\/mysql-mysqldumpslow-sql-cover.webp\" alt=\"MySQL \u6162\u65e5\u5fd7\u5f00\u542f\u4e0e mysqldumpslow \u5206\u6790\u5c01\u9762\u914d\u56fe\" loading=\"lazy\" \/><\/p>\n<p>\u5728 Web \u4e1a\u52a1\u8fd0\u7ef4\u4e0e\u7cfb\u7edf\u7ba1\u7406\u4e2d\uff0c\u6570\u636e\u5e93\u5f80\u5f80\u662f\u51b3\u5b9a\u6574\u7ad9\u54cd\u5e94\u901f\u5ea6\u4e0e\u5e76\u53d1\u80fd\u529b\u7684\u5173\u952e\u74f6\u9888\u3002\u5f53\u9875\u9762\u52a0\u8f7d\u7f13\u6162\u3001\u63a5\u53e3\u8d85\u65f6\u6216\u670d\u52a1\u5668 CPU \u4f7f\u7528\u7387\u5c45\u9ad8\u4e0d\u4e0b\u65f6\uff0c\u76f2\u76ee\u91cd\u542f\u670d\u52a1\u5f80\u5f80\u65e0\u6cd5\u6839\u6cbb\u95ee\u9898\uff0c\u6392\u67e5\u7684\u6838\u5fc3\u7a81\u7834\u53e3\u5728\u4e8e\u7cbe\u51c6\u5b9a\u4f4d\u62d6\u57ae\u6027\u80fd\u7684\u4f4e\u6548 SQL\u3002\u672c\u6587\u5c06\u4e3a\u4f60\u63d0\u4f9b\u4e00\u4efd\u7cfb\u7edf\u7684\u5b9e\u6218\u6307\u5357\uff0c\u5e2e\u52a9\u4f60\u5f7b\u5e95\u89e3\u51b3\u6570\u636e\u5e93\u6162\u67e5\u8be2\u6392\u67e5\u96be\u9898\uff1a\u4ece\u5f00\u542f MySQL \u6162\u65e5\u5fd7\u3001\u79d1\u5b66\u6743\u8861\u8bb0\u5f55\u9608\u503c\uff0c\u5230\u5229\u7528\u5b98\u65b9\u5de5\u5177 mysqldumpslow \u805a\u5408\u63d0\u70bc\u6838\u5fc3\u6162\u8bed\u53e5\uff0c\u5e76\u7ed3\u5408\u6267\u884c\u8ba1\u5212\u8fdb\u884c\u6df1\u5ea6\u4f18\u5316\uff0c\u5168\u9762\u638c\u63e1\u5b9a\u4f4d\u4e0e\u6d88\u9664\u4f4e\u6548\u67e5\u8be2\u7684\u5b8c\u6574\u6d41\u7a0b\u3002<\/p>\n<h2>\u5f00\u542f\u6162\u65e5\u5fd7\uff1a\u52a8\u6001\u751f\u6548\u4e0e my.cnf \u6301\u4e45\u5316\u914d\u7f6e<\/h2>\n<p>MySQL \u6162\u67e5\u8be2\u65e5\u5fd7\uff08Slow Query Log\uff09\u662f\u5b9a\u4f4d\u6027\u80fd\u77ed\u677f\u6700\u6743\u5a01\u7684\u5185\u7f6e\u673a\u5236\uff0c\u4e13\u95e8\u8d1f\u8d23\u6355\u83b7\u6267\u884c\u65f6\u95f4\u8d85\u8fc7\u9608\u503c\u6216\u672a\u4f7f\u7528\u6709\u6548\u7d22\u5f15\u7684\u8bed\u53e5\u3002\u5f00\u542f\u6162\u65e5\u5fd7\u4e3b\u8981\u6709\u4e24\u79cd\u65b9\u5f0f\uff1a\u7ebf\u4e0a\u5e94\u6025\u6392\u67e5\u63a8\u8350\u901a\u8fc7\u5168\u5c40\u53d8\u91cf\u52a8\u6001\u8c03\u6574\uff0c\u65e0\u9700\u91cd\u542f\u6570\u636e\u5e93\u670d\u52a1\uff1b\u65e5\u5e38\u8fd0\u7ef4\u7ba1\u7406\u5219\u5e94\u5199\u5165\u914d\u7f6e\u6587\u4ef6\u5b9e\u73b0\u6301\u4e45\u5316\u3002<\/p>\n<h3>1. \u8fd0\u884c\u65f6\u52a8\u6001\u914d\u7f6e\uff08\u5373\u65f6\u751f\u6548\uff0c\u65e0\u9700\u91cd\u542f\uff09<\/h3>\n<p>\u5728\u4e1a\u52a1\u9ad8\u5cf0\u671f\u9047\u5230\u7a81\u53d1\u6027\u80fd\u544a\u8b66\u65f6\uff0c\u76f4\u63a5\u91cd\u542f\u6570\u636e\u5e93\u4f1a\u4e2d\u65ad\u4e1a\u52a1\u8fde\u63a5\u5e76\u5f15\u53d1\u7ea7\u8054\u98ce\u66b4\u3002\u6b64\u65f6\u53ef\u901a\u8fc7\u5177\u5907\u7ba1\u7406\u5458\u6743\u9650\u7684\u8d26\u6237\u767b\u5f55 MySQL \u7ec8\u7aef\uff0c\u5728\u7ebf\u8c03\u6574\u5168\u5c40\u7cfb\u7edf\u53d8\u91cf\u3002<\/p>\n<p>\u9996\u5148\u68c0\u67e5\u6162\u67e5\u8be2\u65e5\u5fd7\u5f53\u524d\u7684\u8fd0\u884c\u72b6\u6001\u4e0e\u4e3b\u8981\u914d\u7f6e\u53c2\u6570\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-linenumbers=\"false\">\nSHOW VARIABLES LIKE 'slow_query_log%';\nSHOW VARIABLES LIKE 'long_query_time';\n<\/pre>\n<p>\u82e5\u7ed3\u679c\u663e\u793a <code>slow_query_log<\/code> \u4e3a <code>OFF<\/code>\uff0c\u53ef\u4f9d\u6b21\u6267\u884c\u4ee5\u4e0b\u6307\u4ee4\u5f00\u542f\u6162\u65e5\u5fd7\u5e76\u6307\u5b9a\u65e5\u5fd7\u5b58\u50a8\u8def\u5f84\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-linenumbers=\"false\">\nSET GLOBAL slow_query_log = 'ON';\nSET GLOBAL slow_query_log_file = '\/var\/log\/mysql\/mysql-slow.log';\nSET GLOBAL long_query_time = 1.0;\n<\/pre>\n<p>\u9700\u8981\u7279\u522b\u6ce8\u610f\u7684\u662f\uff0c\u6267\u884c <code>SET GLOBAL long_query_time<\/code> \u4ec5\u5bf9\u540e\u7eed\u65b0\u5efa\u7684\u6570\u636e\u5e93\u8fde\u63a5\u751f\u6548\u3002\u5f53\u524d\u7ec8\u7aef\u82e5\u9700\u7acb\u5373\u6d4b\u8bd5\u6162\u67e5\u8be2\u8bb0\u5f55\uff0c\u5e94\u540c\u6b65\u6267\u884c <code>SET SESSION long_query_time = 1.0;<\/code>\u3002\u6b64\u5916\uff0c\u52a8\u6001\u53c2\u6570\u4ec5\u9a7b\u7559\u5728\u5185\u5b58\u4e2d\uff0c\u4e00\u65e6 MySQL \u5b9e\u4f8b\u91cd\u542f\u4fbf\u4f1a\u5931\u6548\uff0c\u56e0\u6b64\u5b8c\u6210\u7ebf\u4e0a\u6392\u67e5\u540e\u987b\u540c\u6b65\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\u3002<\/p>\n<h3>2. \u914d\u7f6e\u6587\u4ef6\u6301\u4e45\u5316\u914d\u7f6e\uff08my.cnf\uff09<\/h3>\n<p>\u4e3a\u4e86\u4fdd\u8bc1\u6570\u636e\u5e93\u91cd\u542f\u540e\u6162\u65e5\u5fd7\u4f9d\u7136\u6709\u6548\uff0c\u9700\u8981\u5728 MySQL \u914d\u7f6e\u6587\u4ef6\uff08Linux \u7cfb\u7edf\u901a\u5e38\u4f4d\u4e8e <code>\/etc\/my.cnf<\/code> \u6216 <code>\/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code>\uff09\u7684 <code>[mysqld]<\/code> \u914d\u7f6e\u6bb5\u6dfb\u52a0\u76f8\u5e94\u6307\u4ee4\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-linenumbers=\"false\">\n[mysqld]\nslow_query_log = 1\nslow_query_log_file = \/var\/log\/mysql\/mysql-slow.log\nlong_query_time = 1.0\nlog_queries_not_using_indexes = 0\n<\/pre>\n<p>\u914d\u7f6e\u65f6\u987b\u786e\u8ba4\u8fd0\u884c MySQL \u670d\u52a1\u7684\u7cfb\u7edf\u8d26\u6237\u5bf9\u6307\u5b9a\u7684\u65e5\u5fd7\u76ee\u5f55\u5177\u5907\u5145\u5206\u7684\u5199\u5165\u6743\u9650\u3002\u82e5\u76ee\u5f55\u4e0d\u5b58\u5728\u6216\u6743\u9650\u53d7\u9650\uff0c\u670d\u52a1\u5728\u542f\u52a8\u65f6\u53ef\u80fd\u56e0\u65e0\u6cd5\u521b\u5efa\u65e5\u5fd7\u6587\u4ef6\u800c\u62a5\u9519\u3002\u5bf9\u4e8e\u591a\u5730\u533a\u8fd0\u8425\u7684\u5916\u8d38\u4e1a\u52a1\u6216\u52a8\u6001\u4ea4\u4e92\u7ad9\u70b9\uff0c\u6570\u636e\u5e93\u5ef6\u8fdf\u4f1a\u76f4\u63a5\u62c9\u957f\u9996\u5b57\u8282\u54cd\u5e94\uff0c\u82e5\u60f3\u7cfb\u7edf\u6392\u67e5\u6574\u7ad9\u74f6\u9888\uff0c\u53ef\u53c2\u8003<a href=\"https:\/\/cn.hostease.com\/blog\/guides\/ttfb-hosting-optimization\/\">\u7f51\u7ad9\u52a0\u8f7d\u901f\u5ea6\u4f18\u5316<\/a>\u4e86\u89e3\u9996\u5b57\u8282\u65f6\u95f4\uff08TTFB\uff09\u7684\u6784\u6210\u4e0e\u4f18\u5316\u8def\u5f84\u3002<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cn.hostease.com\/blog\/wp-content\/uploads\/2026\/09\/mysql-mysqldumpslow-sql-pipeline.webp\" alt=\"MySQL \u6162\u67e5\u8be2\u65e5\u5fd7\u89e6\u53d1\u4e0e\u8bb0\u5f55\u6d41\u7a0b\u6982\u5ff5\u56fe\" loading=\"lazy\" \/><\/p>\n<h2>\u9608\u503c\u53d6\u820d\uff1a\u517c\u987e\u6392\u67e5\u7cbe\u5ea6\u4e0e\u78c1\u76d8 I\/O \u5f00\u9500<\/h2>\n<p>\u6162\u65e5\u5fd7\u7684\u6838\u5fc3\u53c2\u6570\u662f <code>long_query_time<\/code>\uff0c\u5373\u5224\u5b9a\u4e3a\u6162\u67e5\u8be2\u7684\u65f6\u95f4\u5206\u754c\u7ebf\u3002MySQL \u9ed8\u8ba4\u7684 10 \u79d2\u5728\u73b0\u4ee3\u9ad8\u5e76\u53d1 Web \u4e1a\u52a1\u4e2d\u663e\u7136\u8fc7\u9ad8\uff0c\u4e00\u6761\u67e5\u8be2\u8017\u65f6\u8d85\u8fc7 1 \u79d2\u5c31\u8db3\u4ee5\u9020\u6210\u5e94\u7528\u7ebf\u7a0b\u5806\u79ef\u4e0e\u8fde\u63a5\u6c60\u8017\u5c3d\u3002<\/p>\n<p>\u65e5\u5e38\u8fd0\u7ef4\u5efa\u8bae\u5c06\u9608\u503c\u8bbe\u5b9a\u5728 1.0 \u81f3 2.0 \u79d2\u4e4b\u95f4\u3002MySQL 5.1 \u8d77\u5df2\u652f\u6301\u5fae\u79d2\u7ea7\u7cbe\u5ea6\uff08\u4f8b\u5982 0.2 \u4ee3\u8868 200 \u6beb\u79d2\uff09\uff0c\u5728\u538b\u6d4b\u6f14\u7ec3\u6216\u9488\u5bf9\u7279\u5b9a\u6a21\u5757\u6392\u67e5\u65f6\u53ef\u4e34\u65f6\u8c03\u5c0f\u4ee5\u6355\u6349\u9690\u853d\u6162 SQL\uff0c\u6392\u67e5\u7ed3\u675f\u540e\u518d\u884c\u56de\u8c03\uff0c\u907f\u514d\u78c1\u76d8\u5199\u5165\u8fc7\u8f7d\u3002<\/p>\n<p>\u53e6\u4e00\u4e2a\u5173\u952e\u53d8\u91cf\u662f <code>log_queries_not_using_indexes<\/code>\u3002\u5f00\u542f\u540e\u5168\u8868\u626b\u63cf\u8bed\u53e5\u65e0\u8bba\u8017\u65f6\u591a\u77ed\u90fd\u4f1a\u88ab\u8bb0\u5f55\u3002\u8be5\u9009\u9879\u9002\u5408\u5728\u5f00\u53d1\u6d4b\u8bd5\u73af\u5883\u6392\u67e5\u7d22\u5f15\u7f3a\u5931\uff0c\u4f46\u5728\u751f\u4ea7\u73af\u5883\u987b\u683c\u5916\u8c28\u614e\uff1a\u4e1a\u52a1\u4e2d\u82e5\u5b58\u5728\u5927\u91cf\u51e0\u5341\u884c\u8bb0\u5f55\u7684\u5c0f\u914d\u7f6e\u8868\uff0c\u4f18\u5316\u5668\u4e3b\u52a8\u9009\u62e9\u5168\u8868\u626b\u63cf\u4e5f\u4f1a\u88ab\u6301\u7eed\u8bb0\u5f55\uff0c\u5bfc\u81f4\u6162\u65e5\u5fd7\u77ac\u95f4\u5237\u5c4f\u5e76\u6d88\u8017\u5927\u91cf I\/O\u3002\u82e5\u786e\u9700\u5f00\u542f\uff0c\u5efa\u8bae\u914d\u5408 <code>log_throttle_queries_not_using_indexes<\/code> \u9650\u5236\u6bcf\u5206\u949f\u8bb0\u5f55\u9891\u6b21\uff0c\u5e76\u914d\u7f6e\u7cfb\u7edf logrotate \u673a\u5236\u5b9a\u671f\u8f6e\u8f6c\u4e0e\u6e05\u7406\u5f52\u6863\u6587\u4ef6\u3002<\/p>\n<h2>\u4f7f\u7528 mysqldumpslow \u6c47\u603b\u4e0e\u63d0\u70bc TOP \u6162\u67e5\u8be2<\/h2>\n<p>\u968f\u7740\u7cfb\u7edf\u8fd0\u884c\uff0c\u6162\u65e5\u5fd7\u6587\u4ef6\u4e2d\u5f80\u5f80\u8bb0\u5f55\u4e86\u6210\u5343\u4e0a\u4e07\u884c\u6587\u672c\u3002\u82e5\u76f4\u63a5\u4f7f\u7528 grep \u6216 cat \u68c0\u7d22\uff0c\u76f8\u540c\u7684\u6162\u67e5\u8be2\u4f1a\u91cd\u590d\u51fa\u73b0\u6570\u5343\u6b21\uff0c\u6781\u6613\u6df9\u6ca1\u5173\u952e\u4fe1\u606f\u3002MySQL \u5b98\u65b9\u81ea\u5e26\u7684 mysqldumpslow \u5de5\u5177\u80fd\u667a\u80fd\u5c06 SQL \u4e2d\u7684\u5177\u4f53\u6570\u5b57\u66ff\u6362\u4e3a <code>N<\/code>\u3001\u5b57\u7b26\u4e32\u66ff\u6362\u4e3a <code>'S'<\/code>\uff0c\u628a\u540c\u6784\u8bed\u53e5\u805a\u5408\u5f52\u4e00\uff0c\u5e76\u7edf\u8ba1\u6267\u884c\u6b21\u6570\u4e0e\u5e73\u5747\u8017\u65f6\u3002<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cn.hostease.com\/blog\/wp-content\/uploads\/2026\/09\/mysql-mysqldumpslow-sql-grouping.webp\" alt=\"mysqldumpslow \u53c2\u6570\u62bd\u8c61\u4e0e\u6a21\u5f0f\u805a\u5408\u6982\u5ff5\u56fe\" loading=\"lazy\" \/><\/p>\n<p>\u719f\u7ec3\u638c\u63e1\u8be5\u5de5\u5177\u7684\u6838\u5fc3\u53c2\u6570\uff0c\u80fd\u9488\u5bf9\u4e0d\u540c\u573a\u666f\u5feb\u901f\u5b9a\u4f4d\u9ad8\u5371 SQL\uff1a<\/p>\n<ul>\n<li><code>-s<\/code>\uff08\u6392\u5e8f\u89c4\u5219\uff09\uff1a\u652f\u6301\u6309\u603b\u8017\u65f6 <code>t<\/code>\u3001\u5e73\u5747\u8017\u65f6 <code>at<\/code>\u3001\u6267\u884c\u6b21\u6570 <code>c<\/code>\u3001\u603b\u9501\u7b49\u5f85\u65f6\u95f4 <code>l<\/code> \u7b49\u7ef4\u5ea6\u5012\u5e8f\u6392\u5217\u3002<\/li>\n<li><code>-t N<\/code>\uff08\u8f93\u51fa\u6761\u6570\uff09\uff1a\u4ec5\u5c55\u793a\u6392\u540d\u524d N \u6761\u7684\u6162\u67e5\u8be2\u6a21\u5f0f\uff0c\u9501\u5b9a\u7cfb\u7edf\u6838\u5fc3\u77ed\u677f\u3002<\/li>\n<li><code>-g pattern<\/code>\uff08\u6b63\u5219\u8fc7\u6ee4\uff09\uff1a\u6839\u636e\u5173\u952e\u5b57\u6216\u8868\u540d\u7b5b\u9009\u7279\u5b9a\u6a21\u5757\u7684 SQL \u8bed\u53e5\u3002<\/li>\n<li><code>-v<\/code>\uff08\u8be6\u7ec6\u6a21\u5f0f\uff09\uff1a\u5728\u7ec8\u7aef\u6253\u5370\u89e3\u6790\u8fdb\u5ea6\u4e0e\u66f4\u8be6\u5c3d\u7684\u5904\u7406\u4fe1\u606f\u3002<\/li>\n<\/ul>\n<p>\u5728\u6392\u67e5\u4e2d\uff0c\u7d2f\u8ba1\u603b\u8017\u65f6\u6700\u9ad8\u4e0e\u6267\u884c\u9891\u6b21\u6700\u9ad8\u7684\u8bed\u53e5\u6700\u503c\u5f97\u4f18\u5148\u5904\u7406\u3002\u524d\u8005\u662f\u6d88\u8017\u7cfb\u7edf CPU \u548c I\/O \u7b97\u529b\u7684\u4e3b\u8981\u5143\u51f6\uff0c\u540e\u8005\u5219\u6301\u7eed\u5360\u7528\u6570\u636e\u5e93\u8fde\u63a5\u6c60\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-linenumbers=\"false\">\nmysqldumpslow -s t -t 10 \/var\/log\/mysql\/mysql-slow.log\nmysqldumpslow -s c -t 10 -g \"wp_posts\" \/var\/log\/mysql\/mysql-slow.log\n<\/pre>\n<p>\u5de5\u5177\u8f93\u51fa\u7684\u5178\u578b\u6458\u8981\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-linenumbers=\"false\">\nCount: 420  Time=2.35s (987s)  Lock=0.02s (8s)  Rows=1.0 (420), root[root]@localhost\n  SELECT * FROM wp_posts WHERE post_status = 'S' AND post_type = 'S' ORDER BY post_date DESC LIMIT N;\n<\/pre>\n<p>\u5176\u4e2d <code>Count: 420<\/code> \u4ee3\u8868\u8be5\u6a21\u5f0f\u6267\u884c\u4e86 420 \u6b21\uff1b<code>Time=2.35s (987s)<\/code> \u8868\u793a\u5355\u6b21\u5e73\u5747\u8017\u65f6 2.35 \u79d2\u3001\u7d2f\u8ba1\u8017\u65f6 987 \u79d2\uff1b<code>Lock<\/code> \u4e3a\u5355\u6b21\u4e0e\u7d2f\u8ba1\u9501\u7b49\u5f85\u8017\u65f6\uff1b<code>Rows<\/code> \u4e3a\u5355\u6b21\u5e73\u5747\u4e0e\u7d2f\u8ba1\u8fd4\u56de\u884c\u6570\u3002\u5e26\u5360\u4f4d\u7b26\u7684 SQL \u7ed3\u6784\u4e00\u76ee\u4e86\u7136\uff0c\u4fbf\u4e8e\u540e\u7eed\u9488\u5bf9\u6027\u91cd\u6784\u3002<\/p>\n<h2>\u6162\u67e5\u8be2\u6cbb\u7406\uff1a\u7ed3\u5408 EXPLAIN \u5c55\u5f00\u9488\u5bf9\u6027\u4f18\u5316<\/h2>\n<p>\u5b9a\u4f4d\u5230\u95ee\u9898 SQL \u4ec5\u662f\u7b2c\u4e00\u6b65\uff0c\u771f\u6b63\u7684\u6cbb\u7406\u5fc5\u987b\u4f9d\u636e\u6267\u884c\u8ba1\u5212\u5c55\u5f00\uff0c\u5207\u5fcc\u51ed\u4e3b\u89c2\u63a8\u6d4b\u968f\u610f\u589e\u8bbe\u7d22\u5f15\u3002<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cn.hostease.com\/blog\/wp-content\/uploads\/2026\/09\/mysql-mysqldumpslow-sql-optimization.webp\" alt=\"\u6162 SQL \u8bca\u65ad\u4e0e\u6267\u884c\u8ba1\u5212\u4f18\u5316\u95ed\u73af\u67b6\u6784\u56fe\" loading=\"lazy\" \/><\/p>\n<p>\u5c06\u62bd\u8c61 SQL \u8fd8\u539f\u4e3a\u5177\u4f53\u4e1a\u52a1\u53c2\u6570\u540e\uff0c\u5728\u7ec8\u7aef\u6267\u884c <code>EXPLAIN<\/code>\uff08\u6216 MySQL 8.0+ \u7684 <code>EXPLAIN ANALYZE<\/code>\uff09\u83b7\u53d6\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-linenumbers=\"false\">\nEXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC LIMIT 20;\n<\/pre>\n<p>\u91cd\u70b9\u6838\u67e5\u4ee5\u4e0b\u6307\u6807\uff1a\u8bbf\u95ee\u7c7b\u578b <code>type<\/code> \u662f\u5426\u4e3a\u6700\u5dee\u7684\u5168\u8868\u626b\u63cf <code>ALL<\/code>\uff0c\u7406\u60f3\u5e94\u8fbe\u5230 <code>ref<\/code>\u3001<code>range<\/code> \u6216 <code>const<\/code>\uff1b\u5b9e\u9645\u547d\u4e2d\u7d22\u5f15 <code>key<\/code> \u662f\u5426\u4e3a <code>NULL<\/code>\uff1b\u9884\u4f30\u626b\u63cf\u884c\u6570 <code>rows<\/code> \u662f\u5426\u8fc7\u5927\uff1b\u4ee5\u53ca <code>Extra<\/code> \u4e2d\u662f\u5426\u51fa\u73b0\u4e86\u4ee3\u4ef7\u6602\u8d35\u7684 <code>Using filesort<\/code>\uff08\u989d\u5916\u6587\u4ef6\u6392\u5e8f\uff09\u6216 <code>Using temporary<\/code>\uff08\u5efa\u7acb\u4e34\u65f6\u8868\uff09\u3002<\/p>\n<p>\u9488\u5bf9\u6267\u884c\u8ba1\u5212\u66b4\u9732\u51fa\u7684\u95ee\u9898\uff0c\u901a\u5e38\u4ece\u4e09\u4e2a\u7ef4\u5ea6\u5c55\u5f00\u6cbb\u7406\uff1a<\/p>\n<ul>\n<li>\u7d22\u5f15\u4f18\u5316\uff1a\u6309\u7167\u6700\u5de6\u524d\u7f00\u539f\u5219\u5efa\u7acb\u590d\u5408\u7d22\u5f15\uff0c\u5c06\u533a\u5206\u5ea6\u9ad8\u7684\u5217\u524d\u7f6e\uff0c\u5e76\u5c06\u6392\u5e8f\u5b57\u6bb5\u52a0\u5165\u590d\u5408\u7d22\u5f15\u672b\u5c3e\u4ee5\u6d88\u9664 <code>Using filesort<\/code>\uff1b\u5c3d\u91cf\u8bbe\u8ba1\u8986\u76d6\u7d22\u5f15\uff0c\u907f\u514d\u56de\u8868\u67e5\u8be2\u5e26\u6765\u7684\u968f\u673a I\/O\u3002<\/li>\n<li>SQL \u91cd\u6784\uff1a\u907f\u514d\u6ee5\u7528 <code>SELECT *<\/code>\uff0c\u4ec5\u83b7\u53d6\u5fc5\u8981\u5b57\u6bb5\uff1b\u5bf9\u5927\u504f\u79fb\u91cf\u6df1\u5206\u9875\u6539\u7528\u5ef6\u8fdf\u5173\u8054\u4f18\u5316\uff1b\u907f\u514d\u5728 WHERE \u6761\u4ef6\u7684\u7d22\u5f15\u5b57\u6bb5\u4e0a\u4f7f\u7528\u51fd\u6570\u8fd0\u7b97\u5bfc\u81f4\u7d22\u5f15\u5931\u6548\u3002<\/li>\n<li>\u7f13\u5b58\u4e0e\u67b6\u6784\u9632\u62a4\uff1a\u9ad8\u9891\u70ed\u70b9\u53ea\u8bfb\u6570\u636e\u53ef\u5f15\u5165 Redis \u7f13\u5b58\u524a\u5cf0\uff0c\u9632\u6b62\u6162\u67e5\u8be2\u76f4\u63a5\u51fb\u7a7f\u5230\u5e95\u5c42\u3002\u82e5\u662f CMS \u7c7b\u7ad9\u70b9\uff0c\u53ef\u53c2\u8003<a href=\"https:\/\/cn.hostease.com\/blog\/wordpress\/\">WordPress \u8fd0\u7ef4\u6307\u5357<\/a>\u4e86\u89e3\u5bf9\u8c61\u7f13\u5b58\u4e0e\u6570\u636e\u5e93\u7ef4\u62a4\u7b56\u7565\u3002<\/li>\n<\/ul>\n<p>\u5f53 SQL \u903b\u8f91\u4e0e\u7d22\u5f15\u5df2\u7ecf\u4f18\u5316\u5b8c\u6bd5\uff0c\u4f46\u9ad8\u5e76\u53d1\u4e1a\u52a1\u4f9d\u7136\u9762\u4e34\u6027\u80fd\u74f6\u9888\u65f6\uff0c\u5e95\u5c42\u786c\u4ef6\u7684\u7269\u7406 I\/O \u4e0e\u5185\u5b58\u5bb9\u91cf\u5f80\u5f80\u6210\u4e3a\u5236\u7ea6\u5173\u952e\u3002\u4e2d\u5c0f\u578b\u7ad9\u70b9\u53ef\u9009\u7528\u914d\u5907\u9ad8\u901f NVMe \u5b58\u50a8\u4e0e\u5145\u6c9b\u8d44\u6e90\u7684<a href=\"https:\/\/cn.hostease.com\/vps.html\">VPS\u4e91\u4e3b\u673a\u65b9\u6848<\/a>\u4fdd\u969c\u5e73\u7a33\u541e\u5410\uff1b\u800c\u5bf9\u4e8e\u6d77\u91cf\u4ea4\u6613\u6216\u91cd\u5ea6\u6570\u636e\u8fd0\u7b97\uff0c\u5219\u5efa\u8bae\u9009\u62e9\u62e5\u6709\u72ec\u4eab\u8ba1\u7b97\u4e0e\u72ec\u7acb\u78c1\u76d8\u901a\u9053\u7684<a href=\"https:\/\/cn.hostease.com\/dedicated-servers.html\">\u7f8e\u56fd\u72ec\u7acb\u670d\u52a1\u5668\u79df\u7528<\/a>\uff0c\u4ece\u7269\u7406\u786c\u4ef6\u5c42\u9762\u6d88\u9664\u591a\u79df\u6237\u8d44\u6e90\u4e89\u62a2\uff0c\u4e3a\u6570\u636e\u5e93\u7a33\u5b9a\u8fd0\u884c\u5960\u5b9a\u57fa\u77f3\u3002<\/p>\n<h2>\u65e5\u5e38\u8fd0\u7ef4\u8bef\u533a\u4e0e\u6392\u67e5\u6ce8\u610f\u4e8b\u9879<\/h2>\n<p>\u5728\u6162\u65e5\u5fd7\u7684\u5e38\u6001\u5316\u7ef4\u62a4\u4e2d\uff0c\u8fd0\u7ef4\u4eba\u5458\u9700\u6ce8\u610f\u89c4\u907f\u4ee5\u4e0b\u5e38\u89c1\u8bef\u533a\uff1a<\/p>\n<ul>\n<li>\u9501\u7b49\u5f85\u5e72\u6270\u5224\u65ad\uff1a\u6162\u65e5\u5fd7\u8bb0\u5f55\u7684\u6267\u884c\u65f6\u95f4\u5305\u542b\u4e86\u9501\u7b49\u5f85\u65f6\u95f4\u3002\u67d0\u4e9b\u7b80\u5355\u67e5\u8be2\u56e0\u957f\u4e8b\u52a1\u672a\u63d0\u4ea4\u800c\u88ab\u9501\u5835\u585e\uff0c\u8017\u65f6\u8d85\u6807\u5165\u9009\u6162\u65e5\u5fd7\uff0c\u6b64\u65f6\u5e94\u7ed3\u5408 <code>Lock<\/code> \u65f6\u95f4\u548c <code>SHOW ENGINE INNODB STATUS<\/code> \u6392\u67e5\u9501\u4e89\u7528\uff0c\u800c\u975e\u76f2\u76ee\u6539\u5199 SQL\u3002<\/li>\n<li>\u5ffd\u89c6\u8c03\u8bd5\u53c2\u6570\u590d\u539f\uff1a\u7ebf\u4e0a\u4e34\u65f6\u8c03\u4f4e <code>long_query_time<\/code> \u540e\u82e5\u9057\u5fd8\u590d\u539f\uff0c\u53ef\u80fd\u5bfc\u81f4\u65e5\u5fd7\u98de\u901f\u589e\u957f\u6491\u7206\u78c1\u76d8\u5206\u533a\u3002\u5173\u4e8e\u670d\u52a1\u5668\u78c1\u76d8\u76d1\u63a7\u4e0e\u7a7a\u95f4\u9884\u8b66\u673a\u5236\uff0c\u53ef\u53c2\u8003<a href=\"https:\/\/cn.hostease.com\/blog\/server\/\">\u670d\u52a1\u5668\u8fd0\u7ef4\u57fa\u7840<\/a>\u4e2d\u7684\u7cfb\u7edf\u7ba1\u7406\u5185\u5bb9\u3002<\/li>\n<li>\u6f0f\u68c0\u4ece\u5e93\u6162\u67e5\u8be2\uff1a\u5728\u8bfb\u5199\u5206\u79bb\u67b6\u6784\u4e0b\uff0c\u53ea\u8bfb\u4ece\u5e93\u627f\u62c5\u5927\u91cf\u68c0\u7d22\u538b\u529b\uff0c\u4e3b\u5e93\u6ca1\u6709\u6162\u67e5\u8be2\u4e0d\u4ee3\u8868\u7cfb\u7edf\u65e0\u6059\uff0c\u9700\u5bf9\u4ece\u5e93\u96c6\u7fa4\u5b9e\u65bd\u76f8\u540c\u7684\u6162\u65e5\u5fd7\u76d1\u63a7\u4e0e\u5206\u6790\u3002<\/li>\n<\/ul>\n<h2>\u603b\u7ed3\u4e0e\u884c\u52a8\u5efa\u8bae<\/h2>\n<p>\u5f00\u542f MySQL \u6162\u67e5\u8be2\u65e5\u5fd7\u5e76\u8fd0\u7528 mysqldumpslow \u805a\u5408\u5de5\u5177\uff0c\u80fd\u591f\u5c06\u7e41\u6742\u65e0\u5e8f\u7684\u5e95\u5c42\u65e5\u5fd7\u8f6c\u5316\u4e3a\u6e05\u6670\u76f4\u89c2\u7684\u95ee\u9898\u6e05\u5355\uff0c\u518d\u7ed3\u5408 EXPLAIN \u6df1\u5165\u6267\u884c\u8ba1\u5212\u5f00\u5c55\u7d22\u5f15\u8c03\u4f18\u4e0e\u4ee3\u7801\u91cd\u6784\uff0c\u662f\u8fd0\u7ef4\u56e2\u961f\u4fdd\u969c\u6570\u636e\u5e93\u9ad8\u6548\u8fd0\u8f6c\u7684\u6807\u51c6\u8def\u5f84\u3002<\/p>\n<p>\u9762\u5bf9\u4e1a\u52a1\u589e\u957f\u5e26\u6765\u7684\u6027\u80fd\u6311\u6218\uff0c\u5efa\u8bae\u8fd0\u7ef4\u4e0e\u5f00\u53d1\u56e2\u961f\u5728\u751f\u4ea7\u73af\u5883\u4e2d\u5efa\u7acb\u5e38\u6001\u5316\u6392\u67e5\u673a\u5236\uff1a\u5148\u901a\u8fc7\u52a8\u6001\u5168\u5c40\u53c2\u6570\u5e73\u7a33\u5f00\u542f\u6162\u65e5\u5fd7\u5e76\u8bbe\u7f6e 1 \u79d2\u9608\u503c\uff0c\u518d\u6bcf\u5468\u5b9a\u671f\u4f7f\u7528 mysqldumpslow \u63d0\u70bc TOP 5 \u6162\u67e5\u8be2\u7eb3\u5165\u91cd\u6784\u6e05\u5355\u3002\u5982\u679c\u4f60\u9700\u8981\u627f\u8f7d\u66f4\u9ad8\u5e76\u53d1\u7684\u52a8\u6001\u4e1a\u52a1\u6216\u7535\u5546\u5927\u4fc3\uff0c\u5355\u9760\u5e94\u7528\u5c42\u8c03\u4f18\u53ef\u80fd\u4ecd\u4f1a\u53d7\u5236\u4e8e\u5e95\u5c42 I\/O \u8d44\u6e90\uff0c\u6b64\u65f6\u53ef\u4ee5\u8003\u8651\u5f15\u5165\u8bfb\u5199\u5206\u79bb\u67b6\u6784\uff0c\u6216\u8fc1\u79fb\u81f3\u8ba1\u7b97\u4e0e\u78c1\u76d8\u901a\u9053\u66f4\u5145\u8db3\u7684\u9ad8\u6027\u80fd\u72ec\u7acb\u786c\u4ef6\u73af\u5883\uff0c\u4ece\u8f6f\u4ef6\u6267\u884c\u903b\u8f91\u4e0e\u57fa\u7840\u8bbe\u65bd\u53cc\u91cd\u7ef4\u5ea6\u4fdd\u969c\u4e1a\u52a1\u7684\u957f\u4e45\u987a\u7545\u8fd0\u884c\u3002<\/p>\n<p>\u53e6\u5916\uff0c\u5982\u679c\u4f60\u7684\u6570\u636e\u5e93\u74f6\u9888\u5df2\u7ecf\u53cd\u6620\u51fa\u5171\u4eab\u8d44\u6e90\u7684\u4e0a\u9650\uff0cHostease \u7684<a href=\"https:\/\/cn.hostease.com\/dedicated-servers.html\">\u72ec\u7acb\u670d\u52a1\u5668<\/a>\u63d0\u4f9b\u72ec\u4eab CPU \u4e0e\u78c1\u76d8 IO\uff0c\u53ef\u4ee5\u8003\u8651\u4f5c\u4e3a\u8bfb\u5199\u5206\u79bb\u67b6\u6784\u7684\u627f\u8f7d\u5e73\u53f0\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u672c\u6587\u5e2e\u52a9\u7ad9\u957f\u4e0e\u8fd0\u7ef4\u638c\u63e1 MySQL \u6162\u65e5\u5fd7\u6392\u67e5\u6d41\u7a0b\uff1a\u6db5\u76d6 slow_query_log \u52a8\u6001\u4e0e\u6301\u4e45\u5316\u914d\u7f6e\u3001long_query_time \u9608\u503c\u7b56\u7565\u3001\u4f7f\u7528 mysqldumpslow \u805a\u5408\u5206\u6790 TOP \u6162\u67e5\u8be2\uff0c\u4ee5\u53ca\u7ed3\u5408 EXPLAIN \u5c55\u5f00\u7d22\u5f15\u4f18\u5316\u7684\u5b9e\u7528\u6307\u5357\u3002<\/p>\n","protected":false},"author":2,"featured_media":33202,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3822],"tags":[545,5089,5090,4523,5091],"class_list":["post-33206","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-guides","tag-mysql","tag-mysqldumpslow","tag-5090","tag-4523","tag-5091"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/cn.hostease.com\/blog\/wp-content\/uploads\/2026\/09\/mysql-mysqldumpslow-sql-cover.webp","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/posts\/33206","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/comments?post=33206"}],"version-history":[{"count":1,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/posts\/33206\/revisions"}],"predecessor-version":[{"id":33214,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/posts\/33206\/revisions\/33214"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/media\/33202"}],"wp:attachment":[{"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/media?parent=33206"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/categories?post=33206"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cn.hostease.com\/blog\/wp-json\/wp\/v2\/tags?post=33206"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}