使用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中文网其它相关文章!
……