MySQL利用profile分析慢sql详解(group left join效率高于子查询)

使用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

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

时间: 2017-03-15

mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

NOT IN.JOIN.IS NULL.NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select count(*) from A where not exists (select a from B where A.a = B.a) 知道以上三

MySQL中使用or、in与union all在查询命令下的效率对比

OR.in和union all 查询效率到底哪个快? 网上很多的声音都是说union all 快于 or.in,因为or.in会导致全表扫描,他们给出了很多的实例. 但真的union all真的快于or.in? EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel' 这条语

浅谈mysql的子查询联合与in的效率

最近的产品测试发现一个问题,当并发数量小于10时,响应时间可以维持在100毫秒以内.但是当并发数到达30个时,响应时间就超过1秒.这太不能接受了,要求是通过1秒中并发100个. 经过检测发现,时间主要是耗在其中的一个存储过程中.把存储过程的语句一条一条的过一遍也没有发现明显的不合理.因为mysql本身不能提供毫秒级别的时间,google了一个mysql的能提供毫秒的时间函数,再做测试,做了一个定位.发现是其中一条语句,语句是这个样子: select .... from A, B where ..

提高MySQL中InnoDB表BLOB列的存储效率的教程

首先,介绍下关于InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效: 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size

MySQL中TEXT与BLOB字段类型的区别

在MySQL中有两个字段类型容易让人感觉混淆,那就是TEXT与BLOB,特别是自己写博客程序的博主不知道改为自己的博客正文字段选择TEXT还是BLOB类型. 下面给出几点区别: 一.主要差别 TEXT与BLOB的主要差别就是BLOB保存二进制数据,TEXT保存字符数据.目前几乎所有博客内容里的图片都不是以二进制存储在数据库的,而是把图片上传到服务器然后正文里使用<img>标签引用,这样的博客就可以使用TEXT类型.而BLOB就可以把图片换算成二进制保存到数据库中. 二.类型区别 BLOB有4种

MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式.同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力.所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在.这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么. 一次封锁or两段锁? 因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会

浅谈mysql中多表不关联查询的实现方法

大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

Sql Server中判断表、列不存在则创建的方法

一.Sql Server中如何判断表中某列是否存在 首先跟大家分享Sql Server中判断表中某列是否存在的两个方法,方法示例如下: 比如说要判断表A中的字段C是否存在两个方法: 第一种方法  IF EXISTS ( SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID WHERE T1.NAME='A' AND T2.NAME='C' ) PRINT '存在' ELSE PRINT '不存在' 第二种方法,短小

图文详解MySQL中两表关联的连接表如何创建索引

本文介绍了MySQL中两表关联的连接表是如何创建索引的相关内容,分享出来供大家参考学习,下面来看看详细的介绍: 问题介绍 创建数据库的索引,可以选择单列索引,也可以选择创建组合索引. 遇到如下这种情况,用户表(user)与部门表(dept)通过部门用户关联表(deptuser)连接起来,如下图所示: 表间关系 问题就是,在这个关联表中该如何建立索引呢? 针对该表,有如下四种选择: 针对于user_uuid建立单列索引idx_user 针对于user_dept建立单列索引idx_dept 建立组合

优化InnoDB表BLOB,TEXT列的存储效率

首先,介绍下关于MySQL InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效. 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page

大幅提升MySQL中InnoDB的全表扫描速度的方法

 在 InnoDB中更加快速的全表扫描  一般来讲,大多数应用查询的时候都会用索引,查找很少的几行数据(主键查找或百行内的查询),但有时候我们需要全表查询.典型的全表扫描就是逻辑备份  (mysqldump) 和 online schema changes( 注:在线上对大表 schema 的操作,也是 facebook 的一个开源项目) (SELECT ... INTO OUTFILE). 在 Facebook我们用 mysqldump 来备份数据库. 正如你所知MySql提供两种备份方式,提

mysql 中InnoDB和MyISAM的区别分析小结

MyIASM是IASM表的新版本,有如下扩展: 二进制层次的可移植性. NULL列索引. 对变长行比ISAM表有更少的碎片. 支持大文件. 更好的索引压缩. 更好的键吗统计分布. 更好和更快的auto_increment处理. 以下是一些细节和具体实现的差别: 1.InnoDB不支持FULLTEXT类型的索引. 2.InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保

MySQL中InnoDB存储引擎的锁的基本使用教程

MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 各种锁特点 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生冲突的概率最高,并发度最低 行级锁:开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也最高 页面锁:开销和加锁时间介于表锁和行锁之间:会出现死锁:锁定粒度介于表锁和行锁之