- 1PS提示：因为图层已锁定，无法编辑图层的处理方法
- 2picacg苹果版怎么找不到|picacg ios版是不是下架了介绍
- 3Adobe Illustrator CS5 序列号大全
- 4ACDsee注册码免费分享（含ACDsee18、ACDsee10等版本）
- 5PDF浏览器能看3D文件吗？PDF浏览器看3D文件图文详细教程
- 6Potato(马铃薯聊天)怎么注册不了|Potato不能注册处理方法介绍
- 7Potato(土豆聊天)怎么换头像|Potato app更改头像方法介绍
- 8最新的Adobe Illustrator CS4序列号大全
- 9Intel i7-9700K性能跑分曝光：同代提升约12%
- 10qq邮件是否已读怎么看 QQ邮箱已经发出去的邮件怎么知道对方是否已经查看
- 11AMD推土机架构回顾：虽陨落却是Zen成功的垫脚石
- 12美国增加25％进口关税！PC硬件要大涨价
[摘要]left;">最近因为一个用了子查询的sql语句查询很慢，严重影响了性能，所以需要进行优化，下面这篇文章主要跟大家介绍了关于MySQL利用profile分析慢sql的相关资料，文中介绍的非常详细，需要的朋友们可以参考借鉴，下面来一起看看吧。使用profile来分析慢sqlmysql...
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代码实例详解的详细内容，更多请关注php中文网其它相关文章！
学习教程快速掌握从入门到精通的SQL知识。
推荐资讯 总人气榜
最新教程 本月人气
- 1腾讯视频会员账号分享（2017年1月4日）
- 2爱奇艺vip会员如何廉价开通？爱奇艺vip会员开通方法
- 3斗鱼TV如何屏蔽礼物？斗鱼TV屏蔽礼物图文说明教程
- 4最新腾讯视频会员账号共享（2017年1月3日）
- 5最新的腾讯视频会员账号免费共享
- 62018年12月29日腾讯视频会员账号共享
- 7腾讯视频周末影院福利开通方法说明
- 82组腾讯视频会员共享（2018.12.28）
- 9腾讯视频如何打开硬件加速？腾讯视频打开硬件加速图文说明教程
- 10腾讯视频下载管理下文在什么地方一个文件夹
- 112017年1月6日更新的腾讯视频会员账号共享账号
- 12a站如何关闭弹幕？AcFun关弹幕图文说明教程
- 1QQ影音如何截取QQ表情？QQ影音截取QQ表情图文说明教程
- 2最新爱奇艺会员账号密码大全（2017.2.20）
- 3QQ影音如何转换视频格式？
- 4qq影音如何慢放与加速？
- 5qq影音如何删除？qq影音删除图文步骤
- 6qq影音如何安装？qq影音安装图文说明教程
- 72组腾讯视频会员共享（2017.02.14）
- 8详细说明MySQL Group Replication[Multi-Primary Mode]的搭建部署过程（图文）
- 9tcl手机750多少钱 tcl750初现手机价格
- 10红米3s如何刷miui8 红米3s刷机miui8方法流程图解
- 11用钱宝如何注销 用钱宝注销图文说明教程详细说明
- 12用钱宝收不到验证码如何回事 用钱宝收不到验证码处理方法
相关文章
- MySQL优化之缓存优化详细说明（一）
- 详细说明Mysql主从同步设置示例代码
- MySQL在Mac下初始化密码设置详细说明
- 详细说明Mysql5.7从节点设置多线程主从复制的方法
- Windows下容易的Mysql备份BAT脚本代码分享
- 详细说明MySQL数据库事务隔离级别
- MySQL使用profile区分慢sql代码案例详细说明
- 详细说明Linux下mysql的root密码更改方法的示例代码
- mysql中异常出错ERROR：2002的处理方法分享
- mysql delete多表连接删除技巧的示例代码详细说明
- mysql基于正则完成含糊替换字符串的方法说明
- MySQL中使用replace、regexp进行正则表达式替换的用法区分
