MySQL利用profile分析慢sql详解（group left join效率高于子查询）
程序员文章站
2023-11-30 09:12:46
使用profile来分析慢sql
mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良 sql 的性能...
使用profile来分析慢sql
mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良 sql 的性能问题所在。 最近遇到一个查询比较慢的sql语句，用了子查询，大概需要0.8秒左右，这个消耗时间比较长，严重影响了性能，所以需要进行优化。单独查询单表或者子查询记录都很快，下面来看看详细的介绍。
开启profile
mysql> show profiles; -- 查看是否开启 empty set, 1 warning (0.00 sec) mysql> set profiling=1; -- 开启profile query ok, 0 rows affected, 1 warning (0.00 sec) mysql> show profiles; empty set, 1 warning (0.00 sec) mysql>
执行查询，方便profile跟踪记录
mysql> select sql_no_cache -> t1.amount, -> t1.count, -> t1.date , -> (select (concat(t2.approve_id,'|' ,t2.path)) as receipt from tb_bis_merchant_settlement t2 where t2.`merchant_id` = t1.`merchant_id` and t2.`date`=t1.date and t2.approve_status=5) as receipts -> from -> tb_bis_merchant_turnover t1 -> where t1.merchant_id='64884de062bc11e682b00017fa000202' -> order by t1.date desc -> -> limit 0,100; +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ | amount | count | date | receipts | +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ | 15800.00 | 1 | 20170105 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/49/cvkbilhu-yqabqmaabyr7dhomno819.jpg | | 1245.00 | 1 | 20170104 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/4f/cvkbivhtpseai_yhaadnjq7tpq8244.jpg | | 14766.00 | 4 | 20170103 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/45/cvkbilhrrf6aq5uiaaeobjv68fu398.jpg | | 32449.00 | 2 | 20170102 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/4c/cvkbivhrdguafamiabjkb9uvu04477.jpg | | 37246.00 | 5 | 20170101 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/4a/cvkbivhpcngaseylaaeu6l9si0o812.jpg | | 105094.00 | 2 | 20161231 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/49/cvkbivhnwp-alivwaaeaagpayjg732.jpg | | 88032.00 | 3 | 20161230 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/47/cvkbivhmaqsahcezaafays8zx8q067.jpg | | 3845.00 | 1 | 20161229 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3f/cvkbilhl206aas-faafmhvx8pyy578.jpg | | 2118.00 | 4 | 20161228 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/3d/cvkbilhjxhyamofhaad8wuztuuy855.jpg | | 2980.00 | 1 | 20161227 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/3b/cvkbilhicfcafmwgaae9ulpqej4030.jpg | | 1080.00 | 1 | 20161226 | 667e240c44b4469892c261ce9243a8c3|http://testxxx.cn/group1/m00/00/42/cvkbivhhy6iamm8taafhot5zbim875.jpg | | 2980.00 | 1 | 20161225 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/40/cvkbivhfzcwadw2laafpdxmwio4327.jpg | | 10201.00 | 1 | 20161224 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/38/cvkbilhfjfkaiobiaadqgbf1pbo054.jpg | | 3003.00 | 4 | 20161223 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3d/cvkbivhdmheaqi8caagaoqtgxlo422.jpg | | 2698.00 | 1 | 20161222 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3c/cvkbivhb2u2axwruaaec4lir2nc172.jpg | | 990.00 | 1 | 20161221 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3b/cvkbivhbm6aagmqaaaeq9ptn0fu333.jpg | | 1427.00 | 1 | 20161220 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/31/cvkbilhznjqaasvwaaguj6g1pyu541.jpg | | 2465.00 | 1 | 20161219 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/30/cvkbilhx4_mafn-saaepth1fyp8152.jpg | | 2360.00 | 1 | 20161218 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/2f/cvkbilhwl_-aclhbaaglv79hoh8428.jpg | | 3998.00 | 1 | 20161217 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/35/cvkbivhvslgafct_aafqretywnc285.jpg | | 0.00 | 0 | 20161216 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/2d/cvkbilhu8g-axywcaagn1gdsqqc959.jpg | | 0.00 | 0 | 20161215 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/2a/cvkbilhsmryazxitag-zn3wqv4c789.jpg | | 9900.00 | 1 | 20161214 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/31/cvkbivhrtroalwg6aae_csc3lvk695.jpg | | 4320.00 | 1 | 20161213 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/28/cvkbilhqrzcafapeaafkbhqkh3w634.jpg | | 8760.00 | 2 | 20161212 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/26/cvkbilhoqjeao1bdaaghdajou2e697.jpg | | 213335.00 | 4 | 20161211 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/2d/cvkbivhnyqsafxxgaahzl9a8nrs596.jpg | | 47104.00 | 5 | 20161210 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/2c/cvkbivhmspsaanraaaetxx9fcuw946.jpg | | 6100.00 | 1 | 20161209 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/23/cvkbilhlfxoacljvaafmuoqbi5o264.jpg | | 13515.00 | 2 | 20161208 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/21/cvkbilhjz06abuanaagg7bz3osa569.jpg | | 26769.00 | 4 | 20161207 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/20/cvkbilhiggeadnxuaaetxx9fcuw408.jpg | | 0.00 | 0 | 20161206 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/1e/cvkbilhgxauafqr8aafatvz2sfk337.jpg | | 0.00 | 0 | 20161205 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/1d/cvkbilhgky-au9guaagm4jfhmou601.jpg | | 20000.00 | 3 | 20161204 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/23/cvkbivheidgampuiaah6chl6wo8684.jpg | | 20275.00 | 4 | 20161203 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/21/cvkbivhcyrsae-ugaagf0cwfbzm991.jpg | | 3988.00 | 1 | 20161202 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/19/cvkbilhci7maun_9aaisslmhcns351.jpg | | 4460.00 | 1 | 20161201 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/17/cvkbilhakwmacronaagpjuqvqia247.jpg | | 10498.00 | 2 | 20161130 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/16/cvkbilg-3euabsd5aagr-r7gch0254.jpg | | 11080.00 | 2 | 20161129 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/1c/cvkbivg9i6wad4z0aahlb1yisaq864.jpg | | 6100.00 | 1 | 20161128 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/1b/cvkbivg8ohgabtzoaag1zwoloxy932.jpg | | 5580.00 | 1 | 20161127 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/19/cvkbivg65akacrwwaafnaqaodks660.jpg | | 32630.00 | 2 | 20161126 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/18/cvkbivg5kvealns0aahlb1yisaq850.jpg | | 9800.00 | 1 | 20161125 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/17/cvkbivg4qg2amqonaah--he3hsg726.jpg | | 32500.00 | 2 | 20161124 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/16/cvkbivg27_oaav5oaae8vrizwhs684.jpg | | 2700.00 | 1 | 20161123 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/0e/cvkbilg2t4oal3t5aafsawaui98731.jpg | | 4580.00 | 1 | 20161122 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/14/cvkbivg0-ueafdr_aaiby_lnixs656.jpg | | 14120.00 | 1 | 20161121 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/0b/cvkbilgy_eeaapdbaaheyo5nxeo952.jpg | | 41510.00 | 2 | 20161120 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/12/cvkbivgyyrkazki3aagep_igjvm389.jpg | | 7800.00 | 2 | 20161118 | c91d5e7905ba44c8a14045c9c228157f|http://testxxx.cn/group1/m00/00/09/cvkbilgw_viafhipaah0mzwoice530.jpg | +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ 48 rows in set (0.75 sec) mysql>
查看当前的profile记录，主要获得query_id值
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_id | duration | query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00009250 | show warning |
| 2 | 0.00013125 | show warnings |
| 3 | 0.00014375 | set profiling=1 |
| 4 | 0.75458525 | select sql_no_cache
t1.amount,
t1.count,
t1.date ,
(select (concat(t2.approve_id,'|' ,t2.path)) as receipt from tb_bis_merchant_settlement t2 where t2.`merchant_id` = t1.`merchant_id` and t2.`date`=t1.date and t2.approve_status=5) as r |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql>
查看刚才执行的query_id为4的跟踪记录
mysql> show profile for query 4; +--------------------+----------+ | status | duration | +--------------------+----------+ | executing | 0.000017 | | sending data | 0.018048 | | executing | 0.000028 | | sending data | 0.018125 | | executing | 0.000022 | | sending data | 0.015749 | | executing | 0.000017 | | sending data | 0.015633 | | executing | 0.000017 | | sending data | 0.015382 | | executing | 0.000015 | | sending data | 0.015707 | | executing | 0.000023 | | sending data | 0.015890 | | executing | 0.000022 | | sending data | 0.015908 | | executing | 0.000017 | | sending data | 0.015761 | | executing | 0.000022 | | sending data | 0.015542 | | executing | 0.000014 | | sending data | 0.015561 | | executing | 0.000016 | | sending data | 0.015546 | | executing | 0.000037 | | sending data | 0.015555 | | executing | 0.000015 | | sending data | 0.015779 | | executing | 0.000026 | | sending data | 0.015815 | | executing | 0.000015 | | sending data | 0.015468 | | executing | 0.000015 | | sending data | 0.015457 | | executing | 0.000015 | | sending data | 0.015457 | | executing | 0.000014 | | sending data | 0.015500 | | executing | 0.000014 | | sending data | 0.015557 | | executing | 0.000015 | | sending data | 0.015537 | | executing | 0.000014 | | sending data | 0.015395 | | executing | 0.000021 | | sending data | 0.015416 | | executing | 0.000014 | | sending data | 0.015416 | | executing | 0.000014 | | sending data | 0.015399 | | executing | 0.000023 | | sending data | 0.015407 | | executing | 0.000014 | | sending data | 0.015585 | | executing | 0.000014 | | sending data | 0.015385 | | executing | 0.000014 | | sending data | 0.015412 | | executing | 0.000014 | | sending data | 0.015408 | | executing | 0.000014 | | sending data | 0.015753 | | executing | 0.000014 | | sending data | 0.015376 | | executing | 0.000014 | | sending data | 0.015416 | | executing | 0.000019 | | sending data | 0.015368 | | executing | 0.000014 | | sending data | 0.015481 | | executing | 0.000015 | | sending data | 0.015619 | | executing | 0.000015 | | sending data | 0.015662 | | executing | 0.000016 | | sending data | 0.015574 | | executing | 0.000015 | | sending data | 0.015566 | | executing | 0.000015 | | sending data | 0.015488 | | executing | 0.000013 | | sending data | 0.015493 | | executing | 0.000015 | | sending data | 0.015386 | | executing | 0.000015 | | sending data | 0.015485 | | executing | 0.000018 | | sending data | 0.015760 | | executing | 0.000014 | | sending data | 0.015386 | | executing | 0.000015 | | sending data | 0.015418 | | executing | 0.000014 | | sending data | 0.015458 | | end | 0.000016 | | query end | 0.000019 | | closing tables | 0.000018 | | freeing items | 0.000825 | | logging slow query | 0.000067 | | cleaning up | 0.000025 | +--------------------+----------+ 100 rows in set, 1 warning (0.00 sec) mysql>
根据分析结果可以看到，有大量的sending data消耗，而且是持续不断的，这样的可以判断为子查询导致的，所以在这个case里面，子查询不适合用，效率太低。 那该用什么来避免呢？
用group by + left join 改写
mysql> select sql_no_cache distinct -> t1.amount, -> t1.count, -> t1.date, group_concat(concat(t2.approve_id,'|' ,t2.path)) as receipt -> from -> tb_bis_merchant_turnover t1 left join tb_bis_merchant_settlement t2 on t2.`merchant_id` = t1.`merchant_id` and t2.`date`=t1.date and t2.approve_status=5 -> where t1.merchant_id='64884de062bc11e682b00017fa000202' -> group by t1.amount, -> t1.count, -> t1.date -> order by t1.date desc -> -> limit 0,100; +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ | amount | count | date | receipt | +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ | 15800.00 | 1 | 20170105 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/49/cvkbilhu-yqabqmaabyr7dhomno819.jpg | | 1245.00 | 1 | 20170104 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/4f/cvkbivhtpseai_yhaadnjq7tpq8244.jpg | | 14766.00 | 4 | 20170103 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/45/cvkbilhrrf6aq5uiaaeobjv68fu398.jpg | | 32449.00 | 2 | 20170102 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/4c/cvkbivhrdguafamiabjkb9uvu04477.jpg | | 37246.00 | 5 | 20170101 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/4a/cvkbivhpcngaseylaaeu6l9si0o812.jpg | | 105094.00 | 2 | 20161231 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/49/cvkbivhnwp-alivwaaeaagpayjg732.jpg | | 88032.00 | 3 | 20161230 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/47/cvkbivhmaqsahcezaafays8zx8q067.jpg | | 3845.00 | 1 | 20161229 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3f/cvkbilhl206aas-faafmhvx8pyy578.jpg | | 2118.00 | 4 | 20161228 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/3d/cvkbilhjxhyamofhaad8wuztuuy855.jpg | | 2980.00 | 1 | 20161227 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/3b/cvkbilhicfcafmwgaae9ulpqej4030.jpg | | 1080.00 | 1 | 20161226 | 667e240c44b4469892c261ce9243a8c3|http://testxxx.cn/group1/m00/00/42/cvkbivhhy6iamm8taafhot5zbim875.jpg | | 2980.00 | 1 | 20161225 | 0ddfd555f93b45beb0905b1e6de89d29|http://testxxx.cn/group1/m00/00/40/cvkbivhfzcwadw2laafpdxmwio4327.jpg | | 10201.00 | 1 | 20161224 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/38/cvkbilhfjfkaiobiaadqgbf1pbo054.jpg | | 3003.00 | 4 | 20161223 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3d/cvkbivhdmheaqi8caagaoqtgxlo422.jpg | | 2698.00 | 1 | 20161222 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3c/cvkbivhb2u2axwruaaec4lir2nc172.jpg | | 990.00 | 1 | 20161221 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/3b/cvkbivhbm6aagmqaaaeq9ptn0fu333.jpg | | 1427.00 | 1 | 20161220 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/31/cvkbilhznjqaasvwaaguj6g1pyu541.jpg | | 2465.00 | 1 | 20161219 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/30/cvkbilhx4_mafn-saaepth1fyp8152.jpg | | 2360.00 | 1 | 20161218 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/2f/cvkbilhwl_-aclhbaaglv79hoh8428.jpg | | 3998.00 | 1 | 20161217 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/35/cvkbivhvslgafct_aafqretywnc285.jpg | | 0.00 | 0 | 20161216 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/2d/cvkbilhu8g-axywcaagn1gdsqqc959.jpg | | 0.00 | 0 | 20161215 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/2a/cvkbilhsmryazxitag-zn3wqv4c789.jpg | | 9900.00 | 1 | 20161214 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/31/cvkbivhrtroalwg6aae_csc3lvk695.jpg | | 4320.00 | 1 | 20161213 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/28/cvkbilhqrzcafapeaafkbhqkh3w634.jpg | | 8760.00 | 2 | 20161212 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/26/cvkbilhoqjeao1bdaaghdajou2e697.jpg | | 213335.00 | 4 | 20161211 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/2d/cvkbivhnyqsafxxgaahzl9a8nrs596.jpg | | 47104.00 | 5 | 20161210 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/2c/cvkbivhmspsaanraaaetxx9fcuw946.jpg | | 6100.00 | 1 | 20161209 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/23/cvkbilhlfxoacljvaafmuoqbi5o264.jpg | | 13515.00 | 2 | 20161208 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/21/cvkbilhjz06abuanaagg7bz3osa569.jpg | | 26769.00 | 4 | 20161207 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/20/cvkbilhiggeadnxuaaetxx9fcuw408.jpg | | 0.00 | 0 | 20161206 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/1e/cvkbilhgxauafqr8aafatvz2sfk337.jpg | | 0.00 | 0 | 20161205 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/1d/cvkbilhgky-au9guaagm4jfhmou601.jpg | | 20000.00 | 3 | 20161204 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/23/cvkbivheidgampuiaah6chl6wo8684.jpg | | 20275.00 | 4 | 20161203 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/21/cvkbivhcyrsae-ugaagf0cwfbzm991.jpg | | 3988.00 | 1 | 20161202 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/19/cvkbilhci7maun_9aaisslmhcns351.jpg | | 4460.00 | 1 | 20161201 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/17/cvkbilhakwmacronaagpjuqvqia247.jpg | | 10498.00 | 2 | 20161130 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/16/cvkbilg-3euabsd5aagr-r7gch0254.jpg | | 11080.00 | 2 | 20161129 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/1c/cvkbivg9i6wad4z0aahlb1yisaq864.jpg | | 6100.00 | 1 | 20161128 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/1b/cvkbivg8ohgabtzoaag1zwoloxy932.jpg | | 5580.00 | 1 | 20161127 | 98fdb31fe4b04c21bc7ebe8a22981da0|http://testxxx.cn/group1/m00/00/19/cvkbivg65akacrwwaafnaqaodks660.jpg | | 32630.00 | 2 | 20161126 | 2154fdcda51a4257811f1ea886aacd14|http://testxxx.cn/group1/m00/00/18/cvkbivg5kvealns0aahlb1yisaq850.jpg | | 9800.00 | 1 | 20161125 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/17/cvkbivg4qg2amqonaah--he3hsg726.jpg | | 32500.00 | 2 | 20161124 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/16/cvkbivg27_oaav5oaae8vrizwhs684.jpg | | 2700.00 | 1 | 20161123 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/0e/cvkbilg2t4oal3t5aafsawaui98731.jpg | | 4580.00 | 1 | 20161122 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/14/cvkbivg0-ueafdr_aaiby_lnixs656.jpg | | 14120.00 | 1 | 20161121 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/0b/cvkbilgy_eeaapdbaaheyo5nxeo952.jpg | | 41510.00 | 2 | 20161120 | ec481757cfdb445092d16d6b616350c8|http://testxxx.cn/group1/m00/00/12/cvkbivgyyrkazki3aagep_igjvm389.jpg | | 7800.00 | 2 | 20161118 | c91d5e7905ba44c8a14045c9c228157f|http://testxxx.cn/group1/m00/00/09/cvkbilgw_viafhipaah0mzwoice530.jpg | +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ 48 rows in set (0.15 sec) mysql>
可以看到，执行时间变成了0.15秒，提升了5倍的效率。再看profile的跟踪分析。
mysql> show profile for query 8; +-------------------------------+----------+ | status | duration | +-------------------------------+----------+ | starting | 0.000125 | | checking permissions | 0.000015 | | checking permissions | 0.000014 | | opening tables | 0.000029 | | init | 0.000055 | | system lock | 0.000020 | | waiting for query cache lock | 0.000013 | | system lock | 0.000050 | | optimizing | 0.000023 | | statistics | 0.000087 | | preparing | 0.000066 | | creating tmp table | 0.000062 | | creating tmp table | 0.000028 | | sorting result | 0.000016 | | executing | 0.000012 | | sending data | 0.148283 | | creating sort index | 0.000342 | | creating sort index | 0.000223 | | end | 0.000015 | | query end | 0.000046 | | removing tmp table | 0.000017 | | query end | 0.000012 | | removing tmp table | 0.000062 | | query end | 0.000015 | | closing tables | 0.000017 | | freeing items | 0.000019 | | removing tmp table | 0.000025 | | freeing items | 0.000016 | | waiting for query cache lock | 0.000012 | | freeing items | 0.000915 | | waiting for query cache lock | 0.000015 | | freeing items | 0.000011 | | storing result in query cache | 0.000013 | | cleaning up | 0.000024 | +-------------------------------+----------+ 34 rows in set, 1 warning (0.00 sec) mysql>
可以看到，只有一次| sending data | 0.148283 |的消耗，所以效率提升很快。
扩展部分
select name, value from v $ parameter where name in ( 'pga_aggregate_target', 'sga_target' ) union select 'maximum pga allocated' as name, to_char (value) as value from v $ pgastat where name = 'maximum pga allocated' ;
-- insert data insert into t1 select 1,'a' from db1.t2; call db1.proc_get_fints
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。
上一篇： MySQL5.7主从配置实例解析