最近因为一个用了子查询的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代码实例详解的详细内容。更多信息请关注PHP中文网其他相关文章！
声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
MySQL索引基数如何影响查询性能？Apr 14, 2025 am 12:18 AMMySQL索引基数对查询性能有显着影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。
MySQL：新用户的资源和教程Apr 14, 2025 am 12:16 AMMySQL学习路径包括基础知识、核心概念、使用示例和优化技巧。1)了解表、行、列、SQL查询等基础概念。2)学习MySQL的定义、工作原理和优势。3)掌握基本CRUD操作和高级用法，如索引和存储过程。4)熟悉常见错误调试和性能优化建议，如合理使用索引和优化查询。通过这些步骤，你将全面掌握MySQL的使用和优化。
现实世界Mysql：示例和用例Apr 14, 2025 am 12:15 AMMySQL在现实世界的应用包括基础数据库设计和复杂查询优化。1)基本用法：用于存储和管理用户数据，如插入、查询、更新和删除用户信息。2)高级用法：处理复杂业务逻辑，如电子商务平台的订单和库存管理。3)性能优化：通过合理使用索引、分区表和查询缓存来提升性能。
MySQL中的SQL命令：实践示例Apr 14, 2025 am 12:09 AMMySQL中的SQL命令可以分为DDL、DML、DQL、DCL等类别，用于创建、修改、删除数据库和表，插入、更新、删除数据，以及执行复杂的查询操作。1.基本用法包括CREATETABLE创建表、INSERTINTO插入数据和SELECT查询数据。2.高级用法涉及JOIN进行表联接、子查询和GROUPBY进行数据聚合。3.常见错误如语法错误、数据类型不匹配和权限问题可以通过语法检查、数据类型转换和权限管理来调试。4.性能优化建议包括使用索引、避免全表扫描、优化JOIN操作和使用事务来保证数据一致性
InnoDB如何处理酸合规性？Apr 14, 2025 am 12:03 AMInnoDB通过undolog实现原子性，通过锁机制和MVCC实现一致性和隔离性，通过redolog实现持久性。1）原子性：使用undolog记录原始数据，确保事务可回滚。2）一致性：通过行级锁和MVCC确保数据一致。3）隔离性：支持多种隔离级别，默认使用REPEATABLEREAD。4）持久性：使用redolog记录修改，确保数据持久保存。
MySQL的位置：数据库和编程Apr 13, 2025 am 12:18 AMMySQL在数据库和编程中的地位非常重要，它是一个开源的关系型数据库管理系统，广泛应用于各种应用场景。1）MySQL提供高效的数据存储、组织和检索功能，支持Web、移动和企业级系统。2）它使用客户端-服务器架构，支持多种存储引擎和索引优化。3）基本用法包括创建表和插入数据，高级用法涉及多表JOIN和复杂查询。4）常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5）性能优化方法包括合理使用索引、优化查询和使用缓存，最佳实践包括使用事务和PreparedStatemen
MySQL：从小型企业到大型企业Apr 13, 2025 am 12:17 AMMySQL适合小型和大型企业。1)小型企业可使用MySQL进行基本数据管理，如存储客户信息。2)大型企业可利用MySQL处理海量数据和复杂业务逻辑，优化查询性能和事务处理。
幻影是什么读取的，InnoDB如何阻止它们（下一个键锁定）？Apr 13, 2025 am 12:16 AMInnoDB通过Next-KeyLocking机制有效防止幻读。1）Next-KeyLocking结合行锁和间隙锁，锁定记录及其间隙，防止新记录插入。2）在实际应用中，通过优化查询和调整隔离级别，可以减少锁竞争，提高并发性能。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前By尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
2 周前ByDDD
R.E.P.O.如果您听不到任何人，如何修复音频
4 周前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解锁Myrise中的所有内容
1 个月前By尊渡假赌尊渡假赌尊渡假赌
热工具
SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合，这些列表在安全评估过程中经常使用，都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表，帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上，他就可以访问到所需的每种类型的列表。
ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境
Atom编辑器mac版下载
最流行的的开源编辑器
PhpStorm Mac 版本
最新（2018.2.1 ）专业的PHP集成开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
