MySQL利用profile分析慢sql详解（group left join效率高于子查询）
作者：黄杉 发布时间：2024-01-18 02:16:18
标签：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
来源：http://blog.csdn.net/mchdba/article/details/54380221
0
投稿猜你喜欢
- Python 定义了全局变量的特性，使用global 关键字修饰global key_word但是他的一大缺陷就是只能本module 中也就
- 目的：把训练好的pth模型参数提取出来，然后用其他方式部署到边缘设备。Pytorch给了很方便的读取参数接口：nn.Module.param
字符串是不可变类型，可以重新赋值，但不可以索引改变其中一个值，只能拼接字符串建立新变量索引和切片索引：越界会报错切片：越界会自动修改不包含右- 近期对数据库进行巡检，发现数据库业务用户（非 SYS/Public）下存在失效对象。对失效对象进行分析，主要包括失效的视图、物化视图、函数、
- 代码很简单，这里就不多废话了，直接奉上：$(function(){ var w=
- JavaScript 有三种弹窗 Alert (只有确定按钮), Confirmation (确定,取消等按钮), Prompt (有输入对
- 在Web开发者中，Google Chrome是使用最广泛的浏览器。六周一次的发布周期和一套强大的不断扩大开发功能，使其成为了web开发者必备
- if"""条件语句：ifif...elseif...elif...elseif 语句的格式：if 条件： &n
- 前言本笔记通过记录 数据包 在网络中的生命履历来引出一些网络基础知识，如：MAC、ARP、IP、子网掩码、网关、集线器、交换机、路由器这些概
- Python自带的 functools 模块提供了一些常用的高阶函数，也就是用于处理其它函数的特殊函数。换言之，就是能使用该模块对可调用对象
- 读写文件首先看一个例子：f = open('thefile.txt','w') #以写方式打开，
- 本文实例为大家分享了Python九宫格图片生成的具体代码，供大家参考，具体内容如下利用Image类将一张图片分割成9张，发朋友圈利器，打包成
- 本文实例为大家分享了python实现大量图片重命名的具体代码，供大家参考，具体内容如下说明在进行深度学习的过程中，需要对图片进行批量的命名处
- Javascript脚本实现静态网页加密,阅读Javascript脚本实现静态网页加密,前两天看见有人问静态网页加密问题，就写了这个代码思路
- 项目结构├── node_modules # 项目依赖包目录├── build &nb
- 目前很多网站都使用ajax技术动态加载数据，和常规的网站不一样，数据时动态加载的，如果我们使用常规的方法爬取网页，得到的只是一堆html代码
- matplotlib窗口图标默认是matplotlib的标志，如果想修改怎么改呢？由于我选择的matplotlib后端是PyQT5，直接查看
- 现代网络应用Web APP或大型网站的后台一般只有一个，然后客户端却是各种各样的(iOS, android, 浏览器),
- 对于有的vps，系统默认安装了mysql。我们需要从我们的服务器、vps上卸载(移除)默认的mysql。那么如何(怎样)在ubuntu\De
- 本文实例为大家分享了pygame实现非图片按钮效果的具体代码，供大家参考，具体内容如下按钮类程序# -*- coding=utf-8 -*-
