MySQL利用profile分析慢sql代码实例详解
程序员文章站
2022-04-06 07:55:13
...
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代码实例详解的详细内容，更多请关注其它相关文章！