当前位置：移动技术网 > IT编程>数据库>Mysql > MySQL利用profile分析慢sql详解（group left join效率高于子查询）

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

2017年12月12日 | 移动技术网IT编程 | 我要评论

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

总结

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

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

HttpRunner+Mysql查询实现接口测试

一、框架版本httprunner 2.X二、实现场景-实现充值接口测试首先登陆鉴权，获取token后，运行“充值... [阅读全文]
为什么我推荐Nginx作为后端服务器代理(原因解析)

1. 前言我们真实的服务器不应该直接暴露到公网上去，否则更加容易泄露服务器的信息，也更加容易受到攻击。一个比较“平民化”的方案是使用nginx反向代理它。今天就... [阅读全文]
使用Nginx搭建流媒体服务器实现直播功能

写在前面最近几年，直播行业比较火，无论是传统行业的直播，还是购物、游戏、教育，都在涉及直播。作为在互联网行业奋斗了多年的小伙伴，你有没有想过如果使用nginx搭... [阅读全文]
服务器Centos部署MySql并连接Navicat过程详解

(1)服务器配置：[root@localhost ~]# cd /usr/local/src/[root@localhost src]# wget http:/... [阅读全文]
mysql 8.0.21 安装配置方法图文教程

记录了mysql 8.0.21 的安装配置方法，分享给大家。一、下载1、下载安装包mysql直接点击链接也可以下载：2、解压压缩包解压到安装的目录：3、在此目录... [阅读全文]
浅谈mysql通配符进行模糊查询的实现方法

在mysql数据库中，当我们需要模糊查询的时候，我们会使用到通配符。首先我们来了解一下2个概念，一个是操作符，一个是通配符。操作符like就是sql语句中的操... [阅读全文]
MySQL的视图和索引用法与区别详解

mysql的视图简单来说mysql的视图就是对select 命令的定义的一个快捷键，我们查询时会用到非常复杂的select语句，而这个语句我们以后还会经常用到，... [阅读全文]
mysql 8.0.21免安装版配置方法图文教程

六步安装mysql（免安装包的才可以选安装位置），供大家参考，具体内容如下第一步：下载mysql压缩包mysql直接点击链接也可以下载：第二步：解压到自己想要安... [阅读全文]
apollo5.5.0技术文档

快速入门指南校准指南Apollo目前提供强大的校准服务，可满足您从LiDAR，IMU到摄像机的校准要求。百度还可... [阅读全文]
业界首发｜阿里云重磅发布云原生架构白皮书

2020 年 7 月 21 日，由阿里云 20+ 位云原生技术专家共同编撰的《云原生架构白皮书》正式对外发布。作... [阅读全文]