日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

RELATEED CONSULTING
相關(guān)咨詢(xún)
選擇下列產(chǎn)品馬上在線(xiàn)溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問(wèn)題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷(xiāo)解決方案
優(yōu)化臨時(shí)表使用，SQL語(yǔ)句性能提升100倍

【問(wèn)題現(xiàn)象】

線(xiàn)上mysql數(shù)據(jù)庫(kù)爆出一個(gè)慢查詢(xún)，DBA觀(guān)察發(fā)現(xiàn)，查詢(xún)時(shí)服務(wù)器IO飆升，IO占用率達(dá)到100%， 執(zhí)行時(shí)間長(zhǎng)達(dá)7s左右。

SQL語(yǔ)句如下：

 
 
 
 
    
  
  
  
  
  1. SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name 
    2.   
  
  
  
  2. FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
    3.   
  
  
  
  3.  LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
    4.   
  
  
  
  4.  WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ; 
    5.

【問(wèn)題分析】

使用explain查看執(zhí)行計(jì)劃，結(jié)果如下：

這條sql語(yǔ)句的問(wèn)題其實(shí)還是比較明顯的：

查詢(xún)了大量數(shù)據(jù)(包括數(shù)據(jù)條數(shù)、以及g.* )，然后使用臨時(shí)表order by，但最終又只返回了20條數(shù)據(jù)。

DBA觀(guān)察到的IO高，是因?yàn)閟ql語(yǔ)句生成了一個(gè)巨大的臨時(shí)表，內(nèi)存放不下，于是全部拷貝到磁盤(pán)，導(dǎo)致IO飆升。

【優(yōu)化方案】

優(yōu)化的總體思路是拆分sql，將排序操作和查詢(xún)所有信息的操作分開(kāi)。

第一條語(yǔ)句：查詢(xún)符合條件的數(shù)據(jù)，只需要查詢(xún)g.id即可

SELECT DISTINCT g.id FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

第二條語(yǔ)句：查詢(xún)符合條件的詳細(xì)數(shù)據(jù)，將第一條sql的結(jié)果使用in操作拼接到第二條的sql

SELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;

 

【實(shí)測(cè)效果】

在SATA機(jī)器上測(cè)試，優(yōu)化前大約需要50s，優(yōu)化后第一條0.3s，第二條0.1s，優(yōu)化后執(zhí)行速度是原來(lái)的100倍以上，IO從100%降到不到1%

在SSD機(jī)器上測(cè)試，優(yōu)化前大約需要7s，優(yōu)化后第一條0.3s，第二條0.1s，優(yōu)化后執(zhí)行速度是原來(lái)的10倍以上，IO從100%降到不到1%

可以看出，優(yōu)化前磁盤(pán)io是性能瓶頸，SSD的速度要比SATA明顯要快，優(yōu)化后磁盤(pán)不再是瓶頸，SSD和SATA性能沒(méi)有差別。

【理論分析】

MySQL在執(zhí)行SQL查詢(xún)時(shí)可能會(huì)用到臨時(shí)表，一般情況下，用到臨時(shí)表就意味著性能較低。

  • 臨時(shí)表存儲(chǔ)

MySQL臨時(shí)表分為“內(nèi)存臨時(shí)表”和“磁盤(pán)臨時(shí)表”，其中內(nèi)存臨時(shí)表使用MySQL的MEMORY存儲(chǔ)引擎，磁盤(pán)臨時(shí)表使用MySQL的MyISAM存儲(chǔ)引擎；
一般情況下，MySQL會(huì)先創(chuàng)建內(nèi)存臨時(shí)表，但內(nèi)存臨時(shí)表超過(guò)配置指定的值后，MySQL會(huì)將內(nèi)存臨時(shí)表導(dǎo)出到磁盤(pán)臨時(shí)表；
Linux平臺(tái)上缺省是/tmp目錄，/tmp目錄小的系統(tǒng)要注意啦。

  • 使用臨時(shí)表的場(chǎng)景

1）ORDER BY子句和GROUP BY子句不同， 例如：ORDERY BY price GROUP BY name；

2）在JOIN查詢(xún)中，ORDER BY或者GROUP BY使用了不是第一個(gè)表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3）ORDER BY中使用了DISTINCT關(guān)鍵字 ORDERY BY DISTINCT(price)

4）SELECT語(yǔ)句中指定了SQL_SMALL_RESULT關(guān)鍵字 SQL_SMALL_RESULT的意思就是告訴MySQL，結(jié)果會(huì)很小，請(qǐng)直接使用內(nèi)存臨時(shí)表，不需要使用索引排序 SQL_SMALL_RESULT必須和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情況下，我們沒(méi)有必要使用這個(gè)選項(xiàng)，讓MySQL服務(wù)器選擇即可。

  • 直接使用磁盤(pán)臨時(shí)表的場(chǎng)景

1）表包含TEXT或者BLOB列；

2）GROUP BY 或者 DISTINCT 子句中包含長(zhǎng)度大于512字節(jié)的列；

3）使用UNION或者UNION ALL時(shí)，SELECT子句中包含大于512字節(jié)的列；

  • 臨時(shí)表相關(guān)配置

tmp_table_size：指定系統(tǒng)創(chuàng)建的內(nèi)存臨時(shí)表最大大??； http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size

max_heap_table_size: 指定用戶(hù)創(chuàng)建的內(nèi)存表的最大大小； http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size

注意：最終的系統(tǒng)創(chuàng)建的內(nèi)存臨時(shí)表大小是取上述兩個(gè)配置值的最小值。

  • 表的設(shè)計(jì)原則

使用臨時(shí)表一般都意味著性能比較低，特別是使用磁盤(pán)臨時(shí)表，性能更慢，因此我們?cè)趯?shí)際應(yīng)用中應(yīng)該盡量避免臨時(shí)表的使用。 常見(jiàn)的避免臨時(shí)表的方法有：

1）創(chuàng)建索引：在ORDER BY或者GROUP BY的列上創(chuàng)建索引；

2）分拆很長(zhǎng)的列：一般情況下，TEXT、BLOB，大于512字節(jié)的字符串，基本上都是為了顯示信息，而不會(huì)用于查詢(xún)條件， 因此表設(shè)計(jì)的時(shí)候，應(yīng)該將這些列獨(dú)立到另外一張表。

  • SQL優(yōu)化

如果表的設(shè)計(jì)已經(jīng)確定，修改比較困難，那么也可以通過(guò)優(yōu)化SQL語(yǔ)句來(lái)減少臨時(shí)表的大小，以提升SQL執(zhí)行效率。

常見(jiàn)的優(yōu)化SQL語(yǔ)句方法如下：

1）拆分SQL語(yǔ)句

臨時(shí)表主要是用于排序和分組，很多業(yè)務(wù)都是要求排序后再取出詳細(xì)的分頁(yè)數(shù)據(jù)，這種情況下可以將排序和取出詳細(xì)數(shù)據(jù)拆分成不同的SQL，以降低排序或分組時(shí)臨時(shí)表的大小，提升排序和分組的效率，我們的案例就是采用這種方法。

2）優(yōu)化業(yè)務(wù)，去掉排序分組等操作

有時(shí)候業(yè)務(wù)其實(shí)并不需要排序或分組，僅僅是為了好看或者閱讀方便而進(jìn)行了排序，例如數(shù)據(jù)導(dǎo)出、數(shù)據(jù)查詢(xún)等操作，這種情況下去掉排序和分組對(duì)業(yè)務(wù)也沒(méi)有多大影響。

  • 如何判斷使用了臨時(shí)表？

使用explain查看執(zhí)行計(jì)劃，Extra列看到Using temporary就意味著使用了臨時(shí)表。

詳細(xì)信息請(qǐng)參考MySQL官方手冊(cè)： http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

原文鏈接：http://tech.uc.cn/?p=2218

 


文章題目：優(yōu)化臨時(shí)表使用，SQL語(yǔ)句性能提升100倍
URL網(wǎng)址：http://www.dlmjj.cn/article/dppgdid.html