Home>

Use profile to analyze slow sql

The main purpose of mysql's sql performance analyzer is to show the usage of various resources during the entire execution of sql.The profiler can better show the performance problems of bad SQL. Recently encountered a slow SQL query, using a subquery,It takes about 0.8 seconds, which takes a long time.Severely affected performance,So it needs to be optimized.Querying a single table or a subquery record individually is fast,Let's take a look at the detailed introduction below.

Open profile

mysql>show profiles;-see if open
empty set, 1 warning (0.00 sec)
mysql>set profiling=1;-open profile
query ok, 0 rows affected, 1 warning (0.00 sec)
mysql>show profiles;
empty set, 1 warning (0.00 sec)
mysql>

Execute the query,Convenient profile tracking record

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>

View the current profile record, mainly get 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,There is a lot of sending data consumption, and it is continuous,This can be judged to be caused by a subquery,So in this case, subqueries are not suitable for use.The efficiency is too low. What should we use to avoid it?

Rewrite with 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>

can be seen,The execution time has become 0.15 seconds, which improves the efficiency by 5 times.Look at the tracking analysis of the 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>

can be seen,Only once | consumption of | sending data | 0.148283 |

Extension

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

to sum up

  • Previous Detailed explanation of several Linux query IP methods
  • Next Save the last position of the ListView's scroll bar instance (must see)