使用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
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！
如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
暂无“MySQL利用profile分析慢sql详解（group left join效率高于子查询）”评论...
《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。
更新动态
2024年12月22日
2024年12月22日
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果（九）》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]