明輝手游網(wǎng)中心：是一個免費提供流行視頻軟件教程、在線學(xué)習(xí)分享的學(xué)習(xí)平臺！

MySQL使用profile區(qū)分慢sql代碼案例詳細說明

發(fā)表時間：2023-09-11 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：

[摘要]left;">最近因為一個用了子查詢的sql語句查詢很慢，嚴重影響了性能，所以需要進行優(yōu)化，下面這篇文章主要跟大家介紹了關(guān)于MySQL利用profile分析慢sql的相關(guān)資料，文中介紹的非常詳細，需要的朋友們可以參考借鑒，下面來一起看看吧。使用profile來分析慢sqlmysql...
left;">最近因為一個用了子查詢的sql語句查詢很慢，嚴重影響了性能，所以需要進行優(yōu)化，下面這篇文章主要跟大家介紹了關(guān)于MySQL利用profile分析慢sql的相關(guān)資料，文中介紹的非常詳細，需要的朋友們可以參考借鑒，下面來一起看看吧。

使用profile來分析慢sql

mysql 的 sql 性能分析器主要用途是顯示 sql 執(zhí)行的整個過程中各項資源的使用情況。分析器可以更好的展示出不良 SQL 的性能問題所在。 最近遇到一個查詢比較慢的sql語句，用了子查詢，大概需要0.8秒左右，這個消耗時間比較長，嚴重影響了性能，所以需要進行優(yōu)化。單獨查詢單表或者子查詢記錄都很快，下面來看看詳細的介紹。

開啟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>

執(zhí)行查詢，方便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>

查看當(dāng)前的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>

查看剛才執(zhí)行的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>

根據(jù)分析結(jié)果可以看到，有大量的Sending data消耗，而且是持續(xù)不斷的，這樣的可以判斷為子查詢導(dǎo)致的，所以在這個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>

可以看到，執(zhí)行時間變成了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

總結(jié)

以上就是MySQL利用profile分析慢sql代碼實例詳解的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！


學(xué)習(xí)教程快速掌握從入門到精通的SQL知識。