《MysqL学习MysqL利用profile分析慢sql详解（group left join效率高于子查询）》要点：
本文介绍了MysqL学习MysqL利用profile分析慢sql详解（group left join效率高于子查询），希望对您有用。如果有疑问，可以联系我们。
MysqL教程使用profile来分析慢sql
MysqL教程MysqL 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况.分析器可以更好的展示出不良 sql 的性能问题所在. 最近遇到一个查询比较慢的SQL语句,用了子查询,大概需要0.8秒左右,这个消耗时间比较长,严重影响了性能,所以需要进行优化.单独查询单表或者子查询记录都很快,下面来看看详细的介绍.
MysqL教程开启profile
MysqL教程MysqL> show profiles; -- 查看是否开启Empty set,1 warning (0.00 sec)MysqL> set profiling=1; -- 开启profilequery OK,0 rows affected,1 warning (0.00 sec)MysqL> show profiles;Empty set,1 warning (0.00 sec)MysqL> MysqL教程执行查询,方便profile跟踪记录
MysqL教程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> MysqL教程查看当前的profile记录,主要获得query_ID值
MysqL教程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>MysqL教程查看刚才执行的query_ID为4的跟踪记录
MysqL教程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> MysqL教程根据分析结果可以看到,有大量的Sending data消耗,而且是持续不断的,这样的可以判断为子查询导致的,所以在这个case里面,子查询不适合用,效率太低. 那该用什么来避免呢?
MysqL教程用group by + left join 改写
MysqL教程MysqL> SELECT sql_NO_CACHE disTINCT -> t1.amount,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.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> MysqL教程可以看到,执行时间变成了0.15秒,提升了5倍的效率.再看profile的跟踪分析.
MysqL教程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> MysqL教程可以看到,只有一次| Sending data | 0.148283 |的消耗,所以效率提升很快.
MysqL教程扩展部分
MysqL教程SELECT name,VALUE FROM v $ parameter WHERE name IN ( 'pga_aggregate_target','sga_target' ) UNIONSELECT 'maximum PGA allocated' AS name,TO_CHAR (VALUE) AS VALUE FROM v $ pgastat WHERE name = 'maximum PGA allocated' ;MysqL教程-- insert datainsert into t1 select 1,'a' from db1.t2;call db1.proc_get_fintsMysqL教程总结
MysqL教程以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对内存溢出PHP的支持.
总结以上是内存溢出为你收集整理的Mysql学习MySQL利用profile分析慢sql详解（group left join效率高于子查询）全部内容，希望文章能够帮你解决Mysql学习MySQL利用profile分析慢sql详解（group left join效率高于子查询）所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）