提高mysql查詢速度的方法:1、選取最適用的字段屬性;2、使用連接(JOIN)來(lái)代替子查詢;3、使用聯(lián)合(UNION)來(lái)代替手動(dòng)創(chuàng)建的臨時(shí)表;4、添加索引;5、對(duì)查詢進(jìn)行優(yōu)化,盡量避免全表掃描;6、盡量使用表變量來(lái)代替臨時(shí)表等等。
本教程操作環(huán)境:windows7系統(tǒng)、mysql8版本、Dell G3電腦。
查詢速度慢的原因
從程序員的角度
-
查詢語(yǔ)句寫(xiě)的不好
-
沒(méi)建索引,索引建的不合理或索引失效
-
關(guān)聯(lián)查詢有太多的join
從服務(wù)器的角度
- 服務(wù)器磁盤(pán)空間不足
- 服務(wù)器調(diào)優(yōu)配置參數(shù)設(shè)置不合理
MySQL數(shù)據(jù)庫(kù)優(yōu)化的八種方式
1、選取最適用的字段屬性
MySQL可以很好的支持大數(shù)據(jù)量的存取,但是一般說(shuō)來(lái),數(shù)據(jù)庫(kù)中的表越小,在它上面執(zhí)行的查詢也就會(huì)越快。因此,在創(chuàng)建表的時(shí)候,為了獲得更好的性能,我們可以將表中字段的寬度設(shè)得盡可能小。
例如,在定義郵政編碼這個(gè)字段時(shí),如果將其設(shè)置為CHAR(255),顯然給數(shù)據(jù)庫(kù)增加了不必要的空間,甚至使用VARCHAR這種類型也是多余的,因?yàn)镃HAR(6)就可以很好的完成任務(wù)了。同樣的,如果可以的話,我們應(yīng)該使用MEDIUMINT而不是BIGIN來(lái)定義整型字段。
另外一個(gè)提高效率的方法是在可能的情況下,應(yīng)該盡量把字段設(shè)置為NOTNULL,這樣在將來(lái)執(zhí)行查詢的時(shí)候,數(shù)據(jù)庫(kù)不用去比較NULL值。
對(duì)于某些文本字段,例如“省份”或者“性別”,我們可以將它們定義為ENUM類型。因?yàn)樵贛ySQL中,ENUM類型被當(dāng)作數(shù)值型數(shù)據(jù)來(lái)處理,而數(shù)值型數(shù)據(jù)被處理起來(lái)的速度要比文本類型快得多。這樣,我們又可以提高數(shù)據(jù)庫(kù)的性能。
2、使用連接(JOIN)來(lái)代替子查詢(Sub-Queries)
MySQL從4.1開(kāi)始支持SQL的子查詢。這個(gè)技術(shù)可以使用SELECT語(yǔ)句來(lái)創(chuàng)建一個(gè)單列的查詢結(jié)果,然后把這個(gè)結(jié)果作為過(guò)濾條件用在另一個(gè)查詢中。例如,我們要將客戶基本信息表中沒(méi)有任何訂單的客戶刪除掉,就可以利用子查詢先從銷售信息表中將所有發(fā)出訂單的客戶ID取出來(lái),然后將結(jié)果傳遞給主查詢,如下所示:
DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)
使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作,同時(shí)也可以避免事務(wù)或者表鎖死,并且寫(xiě)起來(lái)也很容易。但是,有些情況下,子查詢可以被更有效率的連接(JOIN)..替代。例如,假設(shè)我們要將所有沒(méi)有訂單記錄的用戶取出來(lái),可以用下面這個(gè)查詢完成:
SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)
如果使用連接(JOIN)..來(lái)完成這個(gè)查詢工作,速度將會(huì)快很多。尤其是當(dāng)salesinfo表中對(duì)CustomerID建有索引的話,性能將會(huì)更好,查詢?nèi)缦拢?/p>
SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID ISNULL
連接(JOIN)..之所以更有效率一些,是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。
3、使用聯(lián)合(UNION)來(lái)代替手動(dòng)創(chuàng)建的臨時(shí)表
MySQL從4.0的版本開(kāi)始支持union查詢,它可以把需要使用臨時(shí)表的兩條或