MySQL使用profile区分慢sql代码案例详细说明1

[摘要]left;">最近因为一个用了子查询的sql语句查询很慢，严重影响了性能，所以需要进行优化，下面这篇文章主要跟大家介绍了关于MySQL利用profile分析慢sql的相关资料，文中介...

left;">最近因为一个用了子查询的sql语句查询很慢，严重影响了性能，所以需要进行优化，下面这篇文章主要跟大家介绍了关于MySQL利用profile分析慢sql的相关资料，文中介绍的非常详细，需要的朋友们可以参考借鉴，下面来一起看看吧。

使用profile来分析慢sql

mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良 SQL 的性能问题所在。最近遇到一个查询比较慢的sql语句，用了子查询，大概需要0.8秒左右，这个消耗时间比较长，严重影响了性能，所以需要进行优化。单独查询单表或者子查询记录都很快，下面来看看详细的介绍。

开启profile

mysql> show profiles; -- 查看是否开启
Empty set, 1 warning (0.00 sec)
mysql> set profiling=1; -- 开启profile
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
mysql>

执行查询，方便profile跟踪记录

mysql> SELECT SQL_NO_CACHE
 ->     t1.amount,
 ->     t1.count,
 ->     t1.date ,
 ->     (SELECT (CONCAT(t2.APPROVE_ID,'  ' ,t2.PATH)) AS RECEIPT FROM TB_BIS_MERCHANT_SETTLEMENT t2 
 WHERE t2.`MERCHANT_ID` = t1.`MERCHANT_ID` AND t2.`DATE`=t1.DATE AND t2.APPROVE_STATUS=5) AS receipts
 ->    FROM
 ->     TB_BIS_MERCHANT_TURNOVER t1
 ->    WHERE t1.MERCHANT_ID='64884DE062BC11E682B00017FA000202'
 ->         ORDER BY t1.date DESC
 -> 
 -> LIMIT 0,100;
+-----------+-------+----------+------------------------------------------------------------------------------------------------------------+
   amount    count    date     receipts                           
+-----------+-------+----------+------------------------------------------------------------------------------------------------------------+
   15800.00     1    20170105    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/49/CvkBIlhu-yqABqmAABYR7dHOmno819.jpg   
   1245.00     1    20170104    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/4F/CvkBIVhtpSeAI_YHAADNjq7TPq8244.jpg   
   14766.00     4    20170103    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/45/CvkBIlhrrf6AQ5uIAAEobJv68FU398.jpg   
   32449.00     2    20170102    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/4C/CvkBIVhrDguAfaMIABjKB9uvu04477.jpg   
   37246.00     5    20170101    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/4A/CvkBIVhpCnGASEyLAAEu6l9SI0o812.jpg   
   105094.00     2    20161231    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/49/CvkBIVhnwp-ALIvWAAEAaGPayjg732.jpg   
   88032.00     3    20161230    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/47/CvkBIVhmaqSAHcEZAAFAyS8Zx8Q067.jpg   
   3845.00     1    20161229    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3F/CvkBIlhl206AaS-FAAFMhvX8PYY578.jpg   
   2118.00     4    20161228    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/3D/CvkBIlhjxhyAMOfhAAD8wUzTUUY855.jpg   
   2980.00     1    20161227    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/3B/CvkBIlhicfCAFmwgAAE9ULPqEJ4030.jpg   
   1080.00     1    20161226    667E240C44B4469892C261CE9243A8C3  http://testxxx.cn/group1/M00/00/42/CvkBIVhhy6iAMm8tAAFHOT5zBiM875.jpg   
   2980.00     1    20161225    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/40/CvkBIVhfzCWAdw2LAAFpDXmwio4327.jpg   
   10201.00     1    20161224    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/38/CvkBIlhfJfKAIoBiAADqgbF1pBo054.jpg   
   3003.00     4    20161223    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3D/CvkBIVhdMHeAQi8cAAGAOQTgxLo422.jpg   
   2698.00     1    20161222    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3C/CvkBIVhb2U2AXWRuAAEc4LIr2nc172.jpg   
   990.00     1    20161221    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3B/CvkBIVhbM6aAGMQAAAEQ9ptn0FU333.jpg   
   1427.00     1    20161220    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/31/CvkBIlhZNJqAAsvWAAGuJ6g1pyU541.jpg   
   2465.00     1    20161219    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/30/CvkBIlhX4_mAfn-SAAEptH1Fyp8152.jpg   
   2360.00     1    20161218    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/2F/CvkBIlhWl_-AclhbAAGLv79hoh8428.jpg   
   3998.00     1    20161217    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/35/CvkBIVhVSLGAFct_AAFQRetyWnc285.jpg   
    0.00     0    20161216    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/2D/CvkBIlhU8g-AXywcAAGn1gdsQQc959.jpg   
    0.00     0    20161215    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/2A/CvkBIlhSmryAZXITAG-zN3WQv4c789.jpg   
   9900.00     1    20161214    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/31/CvkBIVhRTrOALwG6AAE_csC3lvk695.jpg   
   4320.00     1    20161213    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/28/CvkBIlhQrzCAfApEAAFKbHqkH3w634.jpg   
   8760.00     2    20161212    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/26/CvkBIlhOqjeAO1BdAAGHdajOU2E697.jpg   
   213335.00     4    20161211    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/2D/CvkBIVhNYQSAfxXgAAHZL9a8Nrs596.jpg   
   47104.00     5    20161210    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/2C/CvkBIVhMsPSAAnrAAAETxX9fCuw946.jpg   
   6100.00     1    20161209    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/23/CvkBIlhLfXOAClJVAAFmuoqBI5o264.jpg   
   13515.00     2    20161208    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/21/CvkBIlhJZ06AbuaNAAGg7Bz3OsA569.jpg   
   26769.00     4    20161207    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/20/CvkBIlhIGgeAdNxuAAETxX9fCuw408.jpg   
    0.00     0    20161206    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/1E/CvkBIlhGxAuAfQr8AAFatVZ2sFk337.jpg   
    0.00     0    20161205    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/1D/CvkBIlhGKy-AU9guAAGm4jFhmoU601.jpg   
   20000.00     3    20161204    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/23/CvkBIVhEIDGAMPuIAAH6chL6Wo8684.jpg   
   20275.00     4    20161203    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/21/CvkBIVhCyrSAE-uGAAGf0CWFbZM991.jpg   
   3988.00     1    20161202    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/19/CvkBIlhCI7mAUN_9AAIsSLMhcns351.jpg   
   4460.00     1    20161201    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/17/CvkBIlhAKwmACroNAAGpJUqVqIA247.jpg   
   10498.00     2    20161130    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/16/CvkBIlg-3euAbsd5AAGr-r7GCH0254.jpg   
   11080.00     2    20161129    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/1C/CvkBIVg9i6WAD4Z0AAHLB1yISaQ864.jpg   
   6100.00     1    20161128    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/1B/CvkBIVg8OHGABTZOAAG1ZWoLoXY932.jpg   
   5580.00     1    20161127    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/19/CvkBIVg65aKAcRWWAAFnaqaodKs660.jpg   
   32630.00     2    20161126    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/18/CvkBIVg5kveALns0AAHLB1yISaQ850.jpg   
   9800.00     1    20161125    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/17/CvkBIVg4Qg2AMqoNAAH--He3hsg726.jpg   
   32500.00     2    20161124    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/16/CvkBIVg27_OAAV5OAAE8vRiZWHs684.jpg   
   2700.00     1    20161123    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/0E/CvkBIlg2T4OAL3t5AAFsAWaUI98731.jpg   
   4580.00     1    20161122    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/14/CvkBIVg0-UeAFDr_AAIBY_LNIxs656.jpg   
   14120.00     1    20161121    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/0B/CvkBIlgy_EeAaPdBAAHeyO5nxeo952.jpg   
   41510.00     2    20161120    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/12/CvkBIVgyYRKAZKi3AAGEp_IGjVM389.jpg   
   7800.00     2    20161118    C91D5E7905BA44C8A14045C9C228157F  http://testxxx.cn/group1/M00/00/09/CvkBIlgw_viAFHiPAAH0MZwoiCE530.jpg   
+-----------+-------+----------+------------------------------------------------------------------------------------------------------------+
48 rows in set (0.75 sec)
mysql>

查看当前的profile记录，主要获得Query_ID值

mysql> show profiles;
+----------+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------+
   Query_ID    Duration    Query                              
+----------+------------+-------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+
    1    0.00009250    show warning                          
    2    0.00013125    show warnings                 
    3    0.00014375    set profiling=1                 
    4    0.75458525    SELECT SQL_NO_CACHE
    t1.amount,
    t1.count,
    t1.date ,
     (SELECT (CONCAT(t2.APPROVE_ID,'  ' ,t2.PATH)) AS RECEIPT FROM TB_BIS_MERCHANT_SETTLEMENT t2 
     WHERE t2.`MERCHANT_ID` = t1.`MERCHANT_ID` AND t2.`DATE`=t1.DATE AND t2.APPROVE_STATUS=5) AS r   
+----------+------------+----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql>

查看刚才执行的Query_ID为4的跟踪记录

mysql> show profile for query 4;
+--------------------+----------+
   Status       Duration   
+--------------------+----------+
   executing      0.000017   
   Sending data     0.018048   
   executing      0.000028   
   Sending data     0.018125   
   executing      0.000022   
   Sending data     0.015749   
   executing      0.000017   
   Sending data     0.015633   
   executing      0.000017   
   Sending data     0.015382   
   executing      0.000015   
   Sending data     0.015707   
   executing      0.000023   
   Sending data     0.015890   
   executing      0.000022   
   Sending data     0.015908   
   executing      0.000017   
   Sending data     0.015761   
   executing      0.000022   
   Sending data     0.015542   
   executing      0.000014   
   Sending data     0.015561   
   executing      0.000016   
   Sending data     0.015546   
   executing      0.000037   
   Sending data     0.015555   
   executing      0.000015   
   Sending data     0.015779   
   executing      0.000026   
   Sending data     0.015815   
   executing      0.000015   
   Sending data     0.015468   
   executing      0.000015   
   Sending data     0.015457   
   executing      0.000015   
   Sending data     0.015457   
   executing      0.000014   
   Sending data     0.015500   
   executing      0.000014   
   Sending data     0.015557   
   executing      0.000015   
   Sending data     0.015537   
   executing      0.000014   
   Sending data     0.015395   
   executing      0.000021   
   Sending data     0.015416   
   executing      0.000014   
   Sending data     0.015416   
   executing      0.000014   
   Sending data     0.015399   
   executing      0.000023   
   Sending data     0.015407   
   executing      0.000014   
   Sending data     0.015585   
   executing      0.000014   
   Sending data     0.015385   
   executing      0.000014   
   Sending data     0.015412   
   executing      0.000014   
   Sending data     0.015408   
   executing      0.000014   
   Sending data     0.015753   
   executing      0.000014   
   Sending data     0.015376   
   executing      0.000014   
   Sending data     0.015416   
   executing      0.000019   
   Sending data     0.015368   
   executing      0.000014   
   Sending data     0.015481   
   executing      0.000015   
   Sending data     0.015619   
   executing      0.000015   
   Sending data     0.015662   
   executing      0.000016   
   Sending data     0.015574   
   executing      0.000015   
   Sending data     0.015566   
   executing      0.000015   
   Sending data     0.015488   
   executing      0.000013   
   Sending data     0.015493   
   executing      0.000015   
   Sending data     0.015386   
   executing      0.000015   
   Sending data     0.015485   
   executing      0.000018   
   Sending data     0.015760   
   executing      0.000014   
   Sending data     0.015386   
   executing      0.000015   
   Sending data     0.015418   
   executing      0.000014   
   Sending data     0.015458   
   end       0.000016   
   query end      0.000019   
   closing tables     0.000018   
   freeing items     0.000825   
   logging slow query    0.000067   
   cleaning up     0.000025   
+--------------------+----------+
100 rows in set, 1 warning (0.00 sec)
mysql>

根据分析结果可以看到，有大量的Sending data消耗，而且是持续不断的，这样的可以判断为子查询导致的，所以在这个case里面，子查询不适合用，效率太低。那该用什么来避免呢？

用group by + left join 改写

mysql> SELECT SQL_NO_CACHE DISTINCT
 ->     t1.amount,
 ->     t1.count,
 ->     t1.date, GROUP_CONCAT(CONCAT(t2.APPROVE_ID,'  ' ,t2.PATH)) AS RECEIPT 
 ->    FROM
 ->     TB_BIS_MERCHANT_TURNOVER t1 LEFT JOIN TB_BIS_MERCHANT_SETTLEMENT t2 ON t2.
 `MERCHANT_ID` = t1.`MERCHANT_ID` AND t2.`DATE`=t1.DATE AND t2.APPROVE_STATUS=5
 ->    WHERE t1.MERCHANT_ID='64884DE062BC11E682B00017FA000202'
 ->    GROUP BY t1.amount,
 ->     t1.count,
 ->     t1.date
 ->         ORDER BY t1.date DESC
 -> 
 -> LIMIT 0,100;
+-----------+-------+----------+------------------------------------------------------------------------------------------------------------+
   amount    count    date     RECEIPT                           
+-----------+-------+----------+------------------------------------------------------------------------------------------------------------+
   15800.00     1    20170105    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/49/CvkBIlhu-yqABqmAABYR7dHOmno819.jpg   
   1245.00     1    20170104    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/4F/CvkBIVhtpSeAI_YHAADNjq7TPq8244.jpg   
   14766.00     4    20170103    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/45/CvkBIlhrrf6AQ5uIAAEobJv68FU398.jpg   
   32449.00     2    20170102    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/4C/CvkBIVhrDguAfaMIABjKB9uvu04477.jpg   
   37246.00     5    20170101    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/4A/CvkBIVhpCnGASEyLAAEu6l9SI0o812.jpg   
   105094.00     2    20161231    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/49/CvkBIVhnwp-ALIvWAAEAaGPayjg732.jpg   
   88032.00     3    20161230    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/47/CvkBIVhmaqSAHcEZAAFAyS8Zx8Q067.jpg   
   3845.00     1    20161229    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3F/CvkBIlhl206AaS-FAAFMhvX8PYY578.jpg   
   2118.00     4    20161228    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/3D/CvkBIlhjxhyAMOfhAAD8wUzTUUY855.jpg   
   2980.00     1    20161227    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/3B/CvkBIlhicfCAFmwgAAE9ULPqEJ4030.jpg   
   1080.00     1    20161226    667E240C44B4469892C261CE9243A8C3  http://testxxx.cn/group1/M00/00/42/CvkBIVhhy6iAMm8tAAFHOT5zBiM875.jpg   
   2980.00     1    20161225    0DDFD555F93B45BEB0905B1E6DE89D29  http://testxxx.cn/group1/M00/00/40/CvkBIVhfzCWAdw2LAAFpDXmwio4327.jpg   
   10201.00     1    20161224    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/38/CvkBIlhfJfKAIoBiAADqgbF1pBo054.jpg   
   3003.00     4    20161223    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3D/CvkBIVhdMHeAQi8cAAGAOQTgxLo422.jpg   
   2698.00     1    20161222    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3C/CvkBIVhb2U2AXWRuAAEc4LIr2nc172.jpg   
   990.00     1    20161221    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/3B/CvkBIVhbM6aAGMQAAAEQ9ptn0FU333.jpg   
   1427.00     1    20161220    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/31/CvkBIlhZNJqAAsvWAAGuJ6g1pyU541.jpg   
   2465.00     1    20161219    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/30/CvkBIlhX4_mAfn-SAAEptH1Fyp8152.jpg   
   2360.00     1    20161218    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/2F/CvkBIlhWl_-AclhbAAGLv79hoh8428.jpg   
   3998.00     1    20161217    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/35/CvkBIVhVSLGAFct_AAFQRetyWnc285.jpg   
    0.00     0    20161216    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/2D/CvkBIlhU8g-AXywcAAGn1gdsQQc959.jpg   
    0.00     0    20161215    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/2A/CvkBIlhSmryAZXITAG-zN3WQv4c789.jpg   
   9900.00     1    20161214    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/31/CvkBIVhRTrOALwG6AAE_csC3lvk695.jpg   
   4320.00     1    20161213    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/28/CvkBIlhQrzCAfApEAAFKbHqkH3w634.jpg   
   8760.00     2    20161212    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/26/CvkBIlhOqjeAO1BdAAGHdajOU2E697.jpg   
   213335.00     4    20161211    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/2D/CvkBIVhNYQSAfxXgAAHZL9a8Nrs596.jpg   
   47104.00     5    20161210    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/2C/CvkBIVhMsPSAAnrAAAETxX9fCuw946.jpg   
   6100.00     1    20161209    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/23/CvkBIlhLfXOAClJVAAFmuoqBI5o264.jpg   
   13515.00     2    20161208    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/21/CvkBIlhJZ06AbuaNAAGg7Bz3OsA569.jpg   
   26769.00     4    20161207    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/20/CvkBIlhIGgeAdNxuAAETxX9fCuw408.jpg   
    0.00     0    20161206    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/1E/CvkBIlhGxAuAfQr8AAFatVZ2sFk337.jpg   
    0.00     0    20161205    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/1D/CvkBIlhGKy-AU9guAAGm4jFhmoU601.jpg   
   20000.00     3    20161204    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/23/CvkBIVhEIDGAMPuIAAH6chL6Wo8684.jpg   
   20275.00     4    20161203    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/21/CvkBIVhCyrSAE-uGAAGf0CWFbZM991.jpg   
   3988.00     1    20161202    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/19/CvkBIlhCI7mAUN_9AAIsSLMhcns351.jpg   
   4460.00     1    20161201    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/17/CvkBIlhAKwmACroNAAGpJUqVqIA247.jpg   
   10498.00     2    20161130    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/16/CvkBIlg-3euAbsd5AAGr-r7GCH0254.jpg   
   11080.00     2    20161129    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/1C/CvkBIVg9i6WAD4Z0AAHLB1yISaQ864.jpg   
   6100.00     1    20161128    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/1B/CvkBIVg8OHGABTZOAAG1ZWoLoXY932.jpg   
   5580.00     1    20161127    98FDB31FE4B04C21BC7EBE8A22981DA0  http://testxxx.cn/group1/M00/00/19/CvkBIVg65aKAcRWWAAFnaqaodKs660.jpg   
   32630.00     2    20161126    2154FDCDA51A4257811F1EA886AACD14  http://testxxx.cn/group1/M00/00/18/CvkBIVg5kveALns0AAHLB1yISaQ850.jpg   
   9800.00     1    20161125    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/17/CvkBIVg4Qg2AMqoNAAH--He3hsg726.jpg   
   32500.00     2    20161124    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/16/CvkBIVg27_OAAV5OAAE8vRiZWHs684.jpg   
   2700.00     1    20161123    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/0E/CvkBIlg2T4OAL3t5AAFsAWaUI98731.jpg   
   4580.00     1    20161122    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/14/CvkBIVg0-UeAFDr_AAIBY_LNIxs656.jpg   
   14120.00     1    20161121    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/0B/CvkBIlgy_EeAaPdBAAHeyO5nxeo952.jpg   
   41510.00     2    20161120    EC481757CFDB445092D16D6B616350C8  http://testxxx.cn/group1/M00/00/12/CvkBIVgyYRKAZKi3AAGEp_IGjVM389.jpg   
   7800.00     2    20161118    C91D5E7905BA44C8A14045C9C228157F  http://testxxx.cn/group1/M00/00/09/CvkBIlgw_viAFHiPAAH0MZwoiCE530.jpg   
+-----------+-------+----------+------------------------------------------------------------------------------------------------------------+
48 rows in set (0.15 sec)
mysql>

可以看到，执行时间变成了0.15秒，提升了5倍的效率。再看profile的跟踪分析。

mysql> show profile for query 8;
+-------------------------------+----------+
   Status         Duration   
+-------------------------------+----------+
   starting         0.000125   
   checking permissions      0.000015   
   checking permissions      0.000014   
   Opening tables       0.000029   
   init          0.000055   
   System lock        0.000020   
   Waiting for query cache lock    0.000013   
   System lock        0.000050   
   optimizing        0.000023   
   statistics        0.000087   
   preparing         0.000066   
   Creating tmp table      0.000062   
   Creating tmp table      0.000028   
   Sorting result       0.000016   
   executing         0.000012   
   Sending data        0.148283   
   Creating sort index      0.000342   
   Creating sort index      0.000223   
   end          0.000015   
   query end         0.000046   
   removing tmp table      0.000017   
   query end         0.000012   
   removing tmp table      0.000062   
   query end         0.000015   
   closing tables       0.000017   
   freeing items        0.000019   
   removing tmp table      0.000025   
   freeing items        0.000016   
   Waiting for query cache lock    0.000012   
   freeing items        0.000915   
   Waiting for query cache lock    0.000015   
   freeing items        0.000011   
   storing result in query cache    0.000013   
   cleaning up        0.000024   
+-------------------------------+----------+
34 rows in set, 1 warning (0.00 sec)
mysql>

可以看到，只有一次 Sending data 0.148283 的消耗，所以效率提升很快。

扩展部分

SELECT 
 NAME,
 VALUE 
FROM
 v $ parameter 
WHERE NAME IN (
 'pga_aggregate_target',
 'sga_target'
 ) 
UNION
SELECT 
 'maximum PGA allocated' AS NAME,
 TO_CHAR (VALUE) AS VALUE 
FROM
 v $ pgastat 
WHERE NAME = 'maximum PGA allocated' ;

-- insert data
insert into t1 select 1,'a' from db1.t2;
call db1.proc_get_fints

总结

以上就是MySQL利用profile分析慢sql代码实例详解的详细内容，更多请关注php中文网其它相关文章！

学习教程快速掌握从入门到精通的SQL知识。

……

标签:MySQL运用profile区分慢sql代码案例详细说明