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

Petra ·

更新时间:2024-09-20

· 677 次阅读

使用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://huoche.7234.cn/images/jb51/qjdto1hsnqw.jpg |
| 1245.00 |  1 | 20170104 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/nmtsf3igrws.jpg |
| 14766.00 |  4 | 20170103 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/xy4z21q3r35.jpg |
| 32449.00 |  2 | 20170102 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/elg5slgqzdg.jpg |
| 37246.00 |  5 | 20170101 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/oyw3dgse45y.jpg |
| 105094.00 |  2 | 20161231 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/h4lycna01qh.jpg |
| 88032.00 |  3 | 20161230 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/03yzpzf4so2.jpg |
| 3845.00 |  1 | 20161229 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/wktuevhilfd.jpg |
| 2118.00 |  4 | 20161228 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/q4bycqma2bl.jpg |
| 2980.00 |  1 | 20161227 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/5qf1mf5nyrd.jpg |
| 1080.00 |  1 | 20161226 | 667E240C44B4469892C261CE9243A8C3|http://huoche.7234.cn/images/jb51/2l0xpoao5qf.jpg |
| 2980.00 |  1 | 20161225 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/qwvmeqdgm4b.jpg |
| 10201.00 |  1 | 20161224 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/3driyrwq3ko.jpg |
| 3003.00 |  4 | 20161223 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/gpnuqllk3mn.jpg |
| 2698.00 |  1 | 20161222 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/urqulndknx5.jpg |
| 990.00 |  1 | 20161221 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/bvstkgdcnve.jpg |
| 1427.00 |  1 | 20161220 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/k34ho5u5w0z.jpg |
| 2465.00 |  1 | 20161219 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/l3yifxat5wj.jpg |
| 2360.00 |  1 | 20161218 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/nhrusi2lnko.jpg |
| 3998.00 |  1 | 20161217 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/bif4jnivdte.jpg |
|  0.00 |  0 | 20161216 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/5oyxqkz1caw.jpg |
|  0.00 |  0 | 20161215 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/w4iyoo2fm0o.jpg |
| 9900.00 |  1 | 20161214 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/ggatkze4vpa.jpg |
| 4320.00 |  1 | 20161213 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/421mfduhkcp.jpg |
| 8760.00 |  2 | 20161212 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/mr3czixlwzy.jpg |
| 213335.00 |  4 | 20161211 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/rxsn0ppdntn.jpg |
| 47104.00 |  5 | 20161210 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/a3h4owtggvo.jpg |
| 6100.00 |  1 | 20161209 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/5r5qpvhc2v2.jpg |
| 13515.00 |  2 | 20161208 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/mdpq2xx55p4.jpg |
| 26769.00 |  4 | 20161207 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/adxqdex54pz.jpg |
|  0.00 |  0 | 20161206 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/bk40zh54ip1.jpg |
|  0.00 |  0 | 20161205 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/qicxxy55bxl.jpg |
| 20000.00 |  3 | 20161204 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/hu4n5h1w50m.jpg |
| 20275.00 |  4 | 20161203 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/ugqczqjecpb.jpg |
| 3988.00 |  1 | 20161202 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/hkyq2mfckti.jpg |
| 4460.00 |  1 | 20161201 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/caqetjkxpmf.jpg |
| 10498.00 |  2 | 20161130 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/lhwsd5dwrjb.jpg |
| 11080.00 |  2 | 20161129 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/qiww3xl035y.jpg |
| 6100.00 |  1 | 20161128 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/qyirmeuz0e2.jpg |
| 5580.00 |  1 | 20161127 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/f1rjf5cijx3.jpg |
| 32630.00 |  2 | 20161126 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/3k1vuc22ebn.jpg |
| 9800.00 |  1 | 20161125 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/jxmm2bzb1ve.jpg |
| 32500.00 |  2 | 20161124 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/xhmkkgvtrek.jpg |
| 2700.00 |  1 | 20161123 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/ss40maud50u.jpg |
| 4580.00 |  1 | 20161122 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/orpizc4vvca.jpg |
| 14120.00 |  1 | 20161121 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/gd0re5our2l.jpg |
| 41510.00 |  2 | 20161120 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/5pflpjniscr.jpg |
| 7800.00 |  2 | 20161118 | C91D5E7905BA44C8A14045C9C228157F|http://huoche.7234.cn/images/jb51/csfskozjlx3.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://huoche.7234.cn/images/jb51/qjdto1hsnqw.jpg |
| 1245.00 |  1 | 20170104 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/nmtsf3igrws.jpg |
| 14766.00 |  4 | 20170103 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/xy4z21q3r35.jpg |
| 32449.00 |  2 | 20170102 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/elg5slgqzdg.jpg |
| 37246.00 |  5 | 20170101 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/oyw3dgse45y.jpg |
| 105094.00 |  2 | 20161231 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/h4lycna01qh.jpg |
| 88032.00 |  3 | 20161230 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/03yzpzf4so2.jpg |
| 3845.00 |  1 | 20161229 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/wktuevhilfd.jpg |
| 2118.00 |  4 | 20161228 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/q4bycqma2bl.jpg |
| 2980.00 |  1 | 20161227 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/5qf1mf5nyrd.jpg |
| 1080.00 |  1 | 20161226 | 667E240C44B4469892C261CE9243A8C3|http://huoche.7234.cn/images/jb51/2l0xpoao5qf.jpg |
| 2980.00 |  1 | 20161225 | 0DDFD555F93B45BEB0905B1E6DE89D29|http://huoche.7234.cn/images/jb51/qwvmeqdgm4b.jpg |
| 10201.00 |  1 | 20161224 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/3driyrwq3ko.jpg |
| 3003.00 |  4 | 20161223 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/gpnuqllk3mn.jpg |
| 2698.00 |  1 | 20161222 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/urqulndknx5.jpg |
| 990.00 |  1 | 20161221 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/bvstkgdcnve.jpg |
| 1427.00 |  1 | 20161220 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/k34ho5u5w0z.jpg |
| 2465.00 |  1 | 20161219 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/l3yifxat5wj.jpg |
| 2360.00 |  1 | 20161218 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/nhrusi2lnko.jpg |
| 3998.00 |  1 | 20161217 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/bif4jnivdte.jpg |
|  0.00 |  0 | 20161216 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/5oyxqkz1caw.jpg |
|  0.00 |  0 | 20161215 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/w4iyoo2fm0o.jpg |
| 9900.00 |  1 | 20161214 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/ggatkze4vpa.jpg |
| 4320.00 |  1 | 20161213 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/421mfduhkcp.jpg |
| 8760.00 |  2 | 20161212 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/mr3czixlwzy.jpg |
| 213335.00 |  4 | 20161211 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/rxsn0ppdntn.jpg |
| 47104.00 |  5 | 20161210 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/a3h4owtggvo.jpg |
| 6100.00 |  1 | 20161209 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/5r5qpvhc2v2.jpg |
| 13515.00 |  2 | 20161208 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/mdpq2xx55p4.jpg |
| 26769.00 |  4 | 20161207 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/adxqdex54pz.jpg |
|  0.00 |  0 | 20161206 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/bk40zh54ip1.jpg |
|  0.00 |  0 | 20161205 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/qicxxy55bxl.jpg |
| 20000.00 |  3 | 20161204 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/hu4n5h1w50m.jpg |
| 20275.00 |  4 | 20161203 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/ugqczqjecpb.jpg |
| 3988.00 |  1 | 20161202 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/hkyq2mfckti.jpg |
| 4460.00 |  1 | 20161201 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/caqetjkxpmf.jpg |
| 10498.00 |  2 | 20161130 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/lhwsd5dwrjb.jpg |
| 11080.00 |  2 | 20161129 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/qiww3xl035y.jpg |
| 6100.00 |  1 | 20161128 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/qyirmeuz0e2.jpg |
| 5580.00 |  1 | 20161127 | 98FDB31FE4B04C21BC7EBE8A22981DA0|http://huoche.7234.cn/images/jb51/f1rjf5cijx3.jpg |
| 32630.00 |  2 | 20161126 | 2154FDCDA51A4257811F1EA886AACD14|http://huoche.7234.cn/images/jb51/3k1vuc22ebn.jpg |
| 9800.00 |  1 | 20161125 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/jxmm2bzb1ve.jpg |
| 32500.00 |  2 | 20161124 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/xhmkkgvtrek.jpg |
| 2700.00 |  1 | 20161123 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/ss40maud50u.jpg |
| 4580.00 |  1 | 20161122 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/orpizc4vvca.jpg |
| 14120.00 |  1 | 20161121 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/gd0re5our2l.jpg |
| 41510.00 |  2 | 20161120 | EC481757CFDB445092D16D6B616350C8|http://huoche.7234.cn/images/jb51/5pflpjniscr.jpg |
| 7800.00 |  2 | 20161118 | C91D5E7905BA44C8A14045C9C228157F|http://huoche.7234.cn/images/jb51/csfskozjlx3.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

总结

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

您可能感兴趣的文章:超详细mysql left join,right join,inner join用法分析mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录深入理解mysql之left join 使用详解MySQL Left JOIN时指定NULL列返回特定值详解MySQL在右表数据不唯一的情况下使用left join的方法MySQL表LEFT JOIN左连接与RIGHT JOIN右连接的实例教程解析mysql left( right ) join使用on与where筛选的差异MySQL的LEFT JOIN表连接的进阶学习教程mysql多个left join连接查询用法分析MySQL left join操作中on和where放置条件的区别介绍

left join LEFT group join Mysql

1024 个赞