Detailed explanation of MySQL using profile to analyze slow SQL (group left join is more efficient than subquery)

Detailed explanation of MySQL using profile to analyze slow SQL (group left join is more efficient than subquery)

Use profile to analyze slow SQL

The main purpose of MySQL's SQL performance analyzer is to display the usage of various resources during the entire SQL execution process. The profiler can better show where performance problems with bad SQL lie. Recently, I encountered a slow SQL statement that used a subquery and took about 0.8 seconds. This took a long time and seriously affected the performance, so it needed to be optimized. It is very fast to query a single table or subquery records separately. Let's take a look at the detailed introduction below.

Open profile

mysql> show profiles; -- Check whether the Empty set is enabled, 1 warning (0.00 sec)

mysql> set profiling=1; -- Enable profiling
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

mysql>

Execute queries to facilitate profile tracking records

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|upload/2022/web/CvkBIlhu-yqABqmAABYR7dHOmno819.jpg |
| 1245.00 | 1 | 20170104 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhtpSeAI_YHAADNjq7TPq8244.jpg |
| 14766.00 | 4 | 20170103 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhrrf6AQ5uIAAEobJv68FU398.jpg |
| 32449.00 | 2 | 20170102 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhrDguAfaMIABjKB9uvu04477.jpg |
| 37246.00 | 5 | 20170101 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhpCnGASEyLAAEu6l9SI0o812.jpg |
| 105094.00 | 2 | 20161231 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhnwp-ALIvWAAEAaGPayjg732.jpg |
| 88032.00 | 3 | 20161230 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhmaqSAHcEZAAFAyS8Zx8Q067.jpg |
| 3845.00 | 1 | 20161229 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhl206AaS-FAAFMhvX8PYY578.jpg |
| 2118.00 | 4 | 20161228 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhjxhyAMOfhAAD8wUzTUUY855.jpg |
| 2980.00 | 1 | 20161227 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhicfCAFmwgAAE9ULPqEJ4030.jpg |
| 1080.00 | 1 | 20161226 | 667E240C44B4469892C261CE9243A8C3|upload/2022/web/CvkBIVhhy6iAMm8tAAFHOT5zBiM875.jpg |
| 2980.00 | 1 | 20161225 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhfzCWAdw2LAAFpDXmwio4327.jpg |
| 10201.00 | 1 | 20161224 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhfJfKAIoBiAADqgbF1pBo054.jpg |
| 3003.00 | 4 | 20161223 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhdMHeAQi8cAAGAOQTgxLo422.jpg |
| 2698.00 | 1 | 20161222 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhb2U2AXWRuAAEc4LIr2nc172.jpg |
| 990.00 | 1 | 20161221 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhbM6aAGMQAAAEQ9ptn0FU333.jpg |
| 1427.00 | 1 | 20161220 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhZNJqAAsvWAAGuJ6g1pyU541.jpg |
| 2465.00 | 1 | 20161219 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhX4_mAfn-SAAEptH1Fyp8152.jpg |
| 2360.00 | 1 | 20161218 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhWl_-AclhbAAGLv79hoh8428.jpg |
| 3998.00 | 1 | 20161217 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhVSLGAFct_AAFQRetyWnc285.jpg |
| 0.00 | 0 | 20161216 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhU8g-AXywcAAGn1gdsQQc959.jpg |
| 0.00 | 0 | 20161215 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhSmryAZXITAG-zN3WQv4c789.jpg |
| 9900.00 | 1 | 20161214 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhRTrOALwG6AAE_csC3lvk695.jpg |
| 4320.00 | 1 | 20161213 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhQrzCAfApEAAFKbHqkH3w634.jpg |
| 8760.00 | 2 | 20161212 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhOqjeAO1BdAAGHdajOU2E697.jpg |
| 213335.00 | 4 | 20161211 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIVhNYQSAfxXgAAHZL9a8Nrs596.jpg |
| 47104.00 | 5 | 20161210 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhMsPSAAnrAAAETxX9fCuw946.jpg |
| 6100.00 | 1 | 20161209 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhLfXOAClJVAAFmuoqBI5o264.jpg |
| 13515.00 | 2 | 20161208 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhJZ06AbuaNAAGg7Bz3OsA569.jpg |
| 26769.00 | 4 | 20161207 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhIGgeAdNxuAAETxX9fCuw408.jpg |
| 0.00 | 0 | 20161206 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhGxAuAfQr8AAFatVZ2sFk337.jpg |
| 0.00 | 0 | 20161205 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhGKy-AU9guAAGm4jFhmoU601.jpg |
| 20000.00 | 3 | 20161204 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhEIDGAMPuIAAH6chL6Wo8684.jpg |
| 20275.00 | 4 | 20161203 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhCyrSAE-uGAAGf0CWFbZM991.jpg |
| 3988.00 | 1 | 20161202 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhCI7mAUN_9AAIsSLMhcns351.jpg |
| 4460.00 | 1 | 20161201 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhAKwmACroNAAGpJUqVqIA247.jpg |
| 10498.00 | 2 | 20161130 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlg-3euAbsd5AAGr-r7GCH0254.jpg |
| 11080.00 | 2 | 20161129 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIVg9i6WAD4Z0AAHLB1yISaQ864.jpg |
| 6100.00 | 1 | 20161128 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg8OHGABTZOAAG1ZWoLoXY932.jpg |
| 5580.00 | 1 | 20161127 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVg65aKAcRWWAAFnaqaodKs660.jpg |
| 32630.00 | 2 | 20161126 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIVg5kveALns0AAHLB1yISaQ850.jpg |
| 9800.00 | 1 | 20161125 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg4Qg2AMqoNAAH--He3hsg726.jpg |
| 32500.00 | 2 | 20161124 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg27_OAAV5OAAE8vRiZWHs684.jpg |
| 2700.00 | 1 | 20161123 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlg2T4OAL3t5AAFsAWaUI98731.jpg |
| 4580.00 | 1 | 20161122 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg0-UeAFDr_AAIBY_LNIxs656.jpg |
| 14120.00 | 1 | 20161121 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlgy_EeAaPdBAAHeyO5nxeo952.jpg |
| 41510.00 | 2 | 20161120 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVgyYRKAZKi3AAGEp_IGjVM389.jpg |
| 7800.00 | 2 | 20161118 | C91D5E7905BA44C8A14045C9C228157F|upload/2022/web/CvkBIlgw_viAFHiPAAH0MZwoiCE530.jpg |
+-----------+------+----------+------------------------------------------------------------------------------------------------------------+
48 rows in set (0.75 sec)

mysql>

View the current profile record, mainly to obtain the Query_ID value

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>

View the trace record of Query_ID 4 just executed

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>

According to the analysis results, we can see that a large amount of Sending data is consumed continuously, which can be judged to be caused by subqueries. Therefore, in this case, subqueries are not suitable and are too inefficient. So what can be used to avoid it?

Rewrite using 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|upload/2022/web/CvkBIlhu-yqABqmAABYR7dHOmno819.jpg |
| 1245.00 | 1 | 20170104 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhtpSeAI_YHAADNjq7TPq8244.jpg |
| 14766.00 | 4 | 20170103 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhrrf6AQ5uIAAEobJv68FU398.jpg |
| 32449.00 | 2 | 20170102 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhrDguAfaMIABjKB9uvu04477.jpg |
| 37246.00 | 5 | 20170101 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhpCnGASEyLAAEu6l9SI0o812.jpg |
| 105094.00 | 2 | 20161231 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhnwp-ALIvWAAEAaGPayjg732.jpg |
| 88032.00 | 3 | 20161230 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhmaqSAHcEZAAFAyS8Zx8Q067.jpg |
| 3845.00 | 1 | 20161229 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhl206AaS-FAAFMhvX8PYY578.jpg |
| 2118.00 | 4 | 20161228 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhjxhyAMOfhAAD8wUzTUUY855.jpg |
| 2980.00 | 1 | 20161227 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhicfCAFmwgAAE9ULPqEJ4030.jpg |
| 1080.00 | 1 | 20161226 | 667E240C44B4469892C261CE9243A8C3|upload/2022/web/CvkBIVhhy6iAMm8tAAFHOT5zBiM875.jpg |
| 2980.00 | 1 | 20161225 | 0DDFD555F93B45BEB0905B1E6DE89D29|upload/2022/web/CvkBIVhfzCWAdw2LAAFpDXmwio4327.jpg |
| 10201.00 | 1 | 20161224 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhfJfKAIoBiAADqgbF1pBo054.jpg |
| 3003.00 | 4 | 20161223 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhdMHeAQi8cAAGAOQTgxLo422.jpg |
| 2698.00 | 1 | 20161222 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhb2U2AXWRuAAEc4LIr2nc172.jpg |
| 990.00 | 1 | 20161221 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhbM6aAGMQAAAEQ9ptn0FU333.jpg |
| 1427.00 | 1 | 20161220 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhZNJqAAsvWAAGuJ6g1pyU541.jpg |
| 2465.00 | 1 | 20161219 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhX4_mAfn-SAAEptH1Fyp8152.jpg |
| 2360.00 | 1 | 20161218 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhWl_-AclhbAAGLv79hoh8428.jpg |
| 3998.00 | 1 | 20161217 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhVSLGAFct_AAFQRetyWnc285.jpg |
| 0.00 | 0 | 20161216 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhU8g-AXywcAAGn1gdsQQc959.jpg |
| 0.00 | 0 | 20161215 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhSmryAZXITAG-zN3WQv4c789.jpg |
| 9900.00 | 1 | 20161214 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhRTrOALwG6AAE_csC3lvk695.jpg |
| 4320.00 | 1 | 20161213 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhQrzCAfApEAAFKbHqkH3w634.jpg |
| 8760.00 | 2 | 20161212 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhOqjeAO1BdAAGHdajOU2E697.jpg |
| 213335.00 | 4 | 20161211 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIVhNYQSAfxXgAAHZL9a8Nrs596.jpg |
| 47104.00 | 5 | 20161210 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVhMsPSAAnrAAAETxX9fCuw946.jpg |
| 6100.00 | 1 | 20161209 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhLfXOAClJVAAFmuoqBI5o264.jpg |
| 13515.00 | 2 | 20161208 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhJZ06AbuaNAAGg7Bz3OsA569.jpg |
| 26769.00 | 4 | 20161207 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhIGgeAdNxuAAETxX9fCuw408.jpg |
| 0.00 | 0 | 20161206 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlhGxAuAfQr8AAFatVZ2sFk337.jpg |
| 0.00 | 0 | 20161205 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhGKy-AU9guAAGm4jFhmoU601.jpg |
| 20000.00 | 3 | 20161204 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhEIDGAMPuIAAH6chL6Wo8684.jpg |
| 20275.00 | 4 | 20161203 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVhCyrSAE-uGAAGf0CWFbZM991.jpg |
| 3988.00 | 1 | 20161202 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIlhCI7mAUN_9AAIsSLMhcns351.jpg |
| 4460.00 | 1 | 20161201 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlhAKwmACroNAAGpJUqVqIA247.jpg |
| 10498.00 | 2 | 20161130 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIlg-3euAbsd5AAGr-r7GCH0254.jpg |
| 11080.00 | 2 | 20161129 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIVg9i6WAD4Z0AAHLB1yISaQ864.jpg |
| 6100.00 | 1 | 20161128 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg8OHGABTZOAAG1ZWoLoXY932.jpg |
| 5580.00 | 1 | 20161127 | 98FDB31FE4B04C21BC7EBE8A22981DA0|upload/2022/web/CvkBIVg65aKAcRWWAAFnaqaodKs660.jpg |
| 32630.00 | 2 | 20161126 | 2154FDCDA51A4257811F1EA886AACD14|upload/2022/web/CvkBIVg5kveALns0AAHLB1yISaQ850.jpg |
| 9800.00 | 1 | 20161125 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg4Qg2AMqoNAAH--He3hsg726.jpg |
| 32500.00 | 2 | 20161124 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg27_OAAV5OAAE8vRiZWHs684.jpg |
| 2700.00 | 1 | 20161123 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlg2T4OAL3t5AAFsAWaUI98731.jpg |
| 4580.00 | 1 | 20161122 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVg0-UeAFDr_AAIBY_LNIxs656.jpg |
| 14120.00 | 1 | 20161121 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIlgy_EeAaPdBAAHeyO5nxeo952.jpg |
| 41510.00 | 2 | 20161120 | EC481757CFDB445092D16D6B616350C8|upload/2022/web/CvkBIVgyYRKAZKi3AAGEp_IGjVM389.jpg |
| 7800.00 | 2 | 20161118 | C91D5E7905BA44C8A14045C9C228157F|upload/2022/web/CvkBIlgw_viAFHiPAAH0MZwoiCE530.jpg |
+-----------+------+----------+------------------------------------------------------------------------------------------------------------+
48 rows in set (0.15 sec)

mysql>

As you can see, the execution time has been reduced to 0.15 seconds, which is a 5-fold increase in efficiency. Let’s look at the profile tracking analysis.

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 results in query cache | 0.000013 |
| cleaning up | 0.000024 |
+-------------------------------+----------+
34 rows in set, 1 warning (0.00 sec)

mysql>

As you can see, there is only one consumption of | Sending data | 0.148283 |, so the efficiency is improved rapidly.

Extensions

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

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • How to solve the problem of a slow SQL query in MySQL causing the entire website to crash
  • MySQL starts slow SQL and analyzes the causes
  • Common causes and solutions for slow MySQL SQL statements
  • Let's talk in detail about the direction of slow SQL optimization in MySQL

<<:  Solve the problem that vue project cannot carry cookies when started locally

>>:  Building .NET Core 2.0 + Nginx + Supervisor environment under Centos7 system

Recommend

MySQL max_allowed_packet setting

max_allowed_packet is a parameter in MySQL that i...

How to use MySQL covering index and table return

Two major categories of indexes Storage engine us...

How to add configuration options to Discuz! Forum

Discuz! Forum has many configuration options in th...

VMware and CentOS system installation method to reset the root password

Today's Tasks 1. Choice of Linux distribution...

Node implements search box for fuzzy query

This article example shares the specific code for...

Solve the problem of running node process in Linux system but unable to kill the process

Let me first introduce to you that the node proce...

Implement dynamic management and monitoring of docker containers based on spring-boot and docker-java [with complete source code download]

Introduction to Docker Docker is an open source a...

Vue uses dynamic components to achieve TAB switching effect

Table of contents Problem Description What is Vue...

Detailed steps for implementing timeout status monitoring in Apache FlinkCEP

CEP - Complex Event Processing. The payment has n...

How to disable ads in the terminal welcome message in Ubuntu Server

If you are using the latest Ubuntu Server version...

Problem record of using vue+echarts chart

Preface echarts is my most commonly used charting...

Problems and solutions for MYSQL5.7.17 connection failure under MAC

The problem that MYSQL5.7.17 cannot connect under...

Sample code for CSS dynamic loading bar effect

Using the knowledge of CSS variables, I will dire...

MySQL tutorial on how to deploy multiple instances on a single machine using mysqld_multi

Table of contents 1. MySQL compilation and instal...

Basic reference types of JavaScript advanced programming

Table of contents 1. Date 2. RegExp 3. Original p...