MySQL利用profile分析慢sql详解（group left join效率高于子查询）
使用profile来分析慢sql
mysql的sql性能分析器主要用途是显示sql执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良SQL的性能问题所在。最近遇到一个查询比较慢的sql语句，用了子查询，大概需要0.8秒左右，这个消耗时间比较长，严重影响了性能，所以需要进行优化。单独查询单表或者子查询记录都很快，下面来看看详细的介绍。
开启profile
mysql>showprofiles;--查看是否开启 Emptyset,1warning(0.00sec) mysql>setprofiling=1;--开启profile QueryOK,0rowsaffected,1warning(0.00sec) mysql>showprofiles; Emptyset,1warning(0.00sec) mysql>
执行查询，方便profile跟踪记录
mysql>SELECTSQL_NO_CACHE ->t1.amount, ->t1.count, ->t1.date, ->(SELECT(CONCAT(t2.APPROVE_ID,'|',t2.PATH))ASRECEIPTFROMTB_BIS_MERCHANT_SETTLEMENTt2WHEREt2.`MERCHANT_ID`=t1.`MERCHANT_ID`ANDt2.`DATE`=t1.DATEANDt2.APPROVE_STATUS=5)ASreceipts ->FROM ->TB_BIS_MERCHANT_TURNOVERt1 ->WHEREt1.MERCHANT_ID='64884DE062BC11E682B00017FA000202' ->ORDERBYt1.dateDESC -> ->LIMIT0,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| +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ 48rowsinset(0.75sec) mysql>
查看当前的profile记录，主要获得Query_ID值
mysql>showprofiles; +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Query_ID|Duration|Query| +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |1|0.00009250|showwarning| |2|0.00013125|showwarnings| |3|0.00014375|setprofiling=1| |4|0.75458525|SELECTSQL_NO_CACHE t1.amount, t1.count, t1.date, (SELECT(CONCAT(t2.APPROVE_ID,'|',t2.PATH))ASRECEIPTFROMTB_BIS_MERCHANT_SETTLEMENTt2WHEREt2.`MERCHANT_ID`=t1.`MERCHANT_ID`ANDt2.`DATE`=t1.DATEANDt2.APPROVE_STATUS=5)ASr| +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4rowsinset,1warning(0.00sec) mysql>
查看刚才执行的Query_ID为4的跟踪记录
mysql>showprofileforquery4; +--------------------+----------+ |Status|Duration| +--------------------+----------+ |executing|0.000017| |Sendingdata|0.018048| |executing|0.000028| |Sendingdata|0.018125| |executing|0.000022| |Sendingdata|0.015749| |executing|0.000017| |Sendingdata|0.015633| |executing|0.000017| |Sendingdata|0.015382| |executing|0.000015| |Sendingdata|0.015707| |executing|0.000023| |Sendingdata|0.015890| |executing|0.000022| |Sendingdata|0.015908| |executing|0.000017| |Sendingdata|0.015761| |executing|0.000022| |Sendingdata|0.015542| |executing|0.000014| |Sendingdata|0.015561| |executing|0.000016| |Sendingdata|0.015546| |executing|0.000037| |Sendingdata|0.015555| |executing|0.000015| |Sendingdata|0.015779| |executing|0.000026| |Sendingdata|0.015815| |executing|0.000015| |Sendingdata|0.015468| |executing|0.000015| |Sendingdata|0.015457| |executing|0.000015| |Sendingdata|0.015457| |executing|0.000014| |Sendingdata|0.015500| |executing|0.000014| |Sendingdata|0.015557| |executing|0.000015| |Sendingdata|0.015537| |executing|0.000014| |Sendingdata|0.015395| |executing|0.000021| |Sendingdata|0.015416| |executing|0.000014| |Sendingdata|0.015416| |executing|0.000014| |Sendingdata|0.015399| |executing|0.000023| |Sendingdata|0.015407| |executing|0.000014| |Sendingdata|0.015585| |executing|0.000014| |Sendingdata|0.015385| |executing|0.000014| |Sendingdata|0.015412| |executing|0.000014| |Sendingdata|0.015408| |executing|0.000014| |Sendingdata|0.015753| |executing|0.000014| |Sendingdata|0.015376| |executing|0.000014| |Sendingdata|0.015416| |executing|0.000019| |Sendingdata|0.015368| |executing|0.000014| |Sendingdata|0.015481| |executing|0.000015| |Sendingdata|0.015619| |executing|0.000015| |Sendingdata|0.015662| |executing|0.000016| |Sendingdata|0.015574| |executing|0.000015| |Sendingdata|0.015566| |executing|0.000015| |Sendingdata|0.015488| |executing|0.000013| |Sendingdata|0.015493| |executing|0.000015| |Sendingdata|0.015386| |executing|0.000015| |Sendingdata|0.015485| |executing|0.000018| |Sendingdata|0.015760| |executing|0.000014| |Sendingdata|0.015386| |executing|0.000015| |Sendingdata|0.015418| |executing|0.000014| |Sendingdata|0.015458| |end|0.000016| |queryend|0.000019| |closingtables|0.000018| |freeingitems|0.000825| |loggingslowquery|0.000067| |cleaningup|0.000025| +--------------------+----------+ 100rowsinset,1warning(0.00sec) mysql>
根据分析结果可以看到，有大量的Sendingdata消耗，而且是持续不断的，这样的可以判断为子查询导致的，所以在这个case里面，子查询不适合用，效率太低。那该用什么来避免呢？
用groupby+leftjoin改写
mysql>SELECTSQL_NO_CACHEDISTINCT ->t1.amount, ->t1.count, ->t1.date,GROUP_CONCAT(CONCAT(t2.APPROVE_ID,'|',t2.PATH))ASRECEIPT ->FROM ->TB_BIS_MERCHANT_TURNOVERt1LEFTJOINTB_BIS_MERCHANT_SETTLEMENTt2ONt2.`MERCHANT_ID`=t1.`MERCHANT_ID`ANDt2.`DATE`=t1.DATEANDt2.APPROVE_STATUS=5 ->WHEREt1.MERCHANT_ID='64884DE062BC11E682B00017FA000202' ->GROUPBYt1.amount, ->t1.count, ->t1.date ->ORDERBYt1.dateDESC -> ->LIMIT0,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| +-----------+-------+----------+------------------------------------------------------------------------------------------------------------+ 48rowsinset(0.15sec) mysql>
可以看到，执行时间变成了0.15秒，提升了5倍的效率。再看profile的跟踪分析。
mysql>showprofileforquery8; +-------------------------------+----------+ |Status|Duration| +-------------------------------+----------+ |starting|0.000125| |checkingpermissions|0.000015| |checkingpermissions|0.000014| |Openingtables|0.000029| |init|0.000055| |Systemlock|0.000020| |Waitingforquerycachelock|0.000013| |Systemlock|0.000050| |optimizing|0.000023| |statistics|0.000087| |preparing|0.000066| |Creatingtmptable|0.000062| |Creatingtmptable|0.000028| |Sortingresult|0.000016| |executing|0.000012| |Sendingdata|0.148283| |Creatingsortindex|0.000342| |Creatingsortindex|0.000223| |end|0.000015| |queryend|0.000046| |removingtmptable|0.000017| |queryend|0.000012| |removingtmptable|0.000062| |queryend|0.000015| |closingtables|0.000017| |freeingitems|0.000019| |removingtmptable|0.000025| |freeingitems|0.000016| |Waitingforquerycachelock|0.000012| |freeingitems|0.000915| |Waitingforquerycachelock|0.000015| |freeingitems|0.000011| |storingresultinquerycache|0.000013| |cleaningup|0.000024| +-------------------------------+----------+ 34rowsinset,1warning(0.00sec) mysql>
可以看到，只有一次|Sendingdata|0.148283|的消耗，所以效率提升很快。
扩展部分
SELECT NAME, VALUE FROM v$parameter WHERENAMEIN( 'pga_aggregate_target', 'sga_target' ) UNION SELECT 'maximumPGAallocated'ASNAME, TO_CHAR(VALUE)ASVALUE FROM v$pgastat WHERENAME='maximumPGAallocated';
--insertdata insertintot1select1,'a'fromdb1.t2; calldb1.proc_get_fints
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。