提高mysql查詢速度的方法:1、選取最適用的字段屬性;2、使用連接(JOIN)來代替子查詢;3、使用聯(lián)合(UNION)來代替手動創(chuàng)建的臨時表;4、添加索引;5、對查詢進(jìn)行優(yōu)化,盡量避免全表掃描;6、盡量使用表變量來代替臨時表等等。
本教程操作環(huán)境:windows7系統(tǒng)、mysql8版本、Dell G3電腦。
查詢速度慢的原因
從程序員的角度
-
查詢語句寫的不好
-
沒建索引,索引建的不合理或索引失效
-
關(guān)聯(lián)查詢有太多的join
從服務(wù)器的角度
- 服務(wù)器磁盤空間不足
- 服務(wù)器調(diào)優(yōu)配置參數(shù)設(shè)置不合理
MySQL數(shù)據(jù)庫優(yōu)化的八種方式
1、選取最適用的字段屬性
MySQL可以很好的支持大數(shù)據(jù)量的存取,但是一般說來,數(shù)據(jù)庫中的表越小,在它上面執(zhí)行的查詢也就會越快。因此,在創(chuàng)建表的時候,為了獲得更好的性能,我們可以將表中字段的寬度設(shè)得盡可能小。
例如,在定義郵政編碼這個字段時,如果將其設(shè)置為CHAR(255),顯然給數(shù)據(jù)庫增加了不必要的空間,甚至使用VARCHAR這種類型也是多余的,因為CHAR(6)就可以很好的完成任務(wù)了。同樣的,如果可以的話,我們應(yīng)該使用MEDIUMINT而不是BIGIN來定義整型字段。
另外一個提高效率的方法是在可能的情況下,應(yīng)該盡量把字段設(shè)置為NOTNULL,這樣在將來執(zhí)行查詢的時候,數(shù)據(jù)庫不用去比較NULL值。
對于某些文本字段,例如“省份”或者“性別”,我們可以將它們定義為ENUM類型。因為在MySQL中,ENUM類型被當(dāng)作數(shù)值型數(shù)據(jù)來處理,而數(shù)值型數(shù)據(jù)被處理起來的速度要比文本類型快得多。這樣,我們又可以提高數(shù)據(jù)庫的性能。
2、使用連接(JOIN)來代替子查詢(Sub-Queries)
MySQL從4.1開始支持SQL的子查詢。這個技術(shù)可以使用SELECT語句來創(chuàng)建一個單列的查詢結(jié)果,然后把這個結(jié)果作為過濾條件用在另一個查詢中。例如,我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉,就可以利用子查詢先從銷售信息表中將所有發(fā)出訂單的客戶ID取出來,然后將結(jié)果傳遞給主查詢,如下所示:
DELETE?FROM?customerinfo?WHERE?CustomerID?NOT?IN?(SELECT?CustomerID?FROM?salesinfo)
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作,同時也可以避免事務(wù)或者表鎖死,并且寫起來也很容易。但是,有些情況下,子查詢可以被更有效率的連接(JOIN)..替代。例如,假設(shè)我們要將所有沒有訂單記錄的用戶取出來,可以用下面這個查詢完成:
SELECT?*?FROM?customerinfo?WHERE?CustomerID?NOT?IN?(SELECTC?ustomerID?FROM?salesinfo)
如果使用連接(JOIN)..來完成這個查詢工作,速度將會快很多。尤其是當(dāng)salesinfo表中對CustomerID建有索引的話,性能將會更好,查詢?nèi)缦拢?/p>
SELECT?*?FROM?customerinfo?LEFT?JOIN?salesinfo? ON?customerinfo.CustomerID=salesinfo.CustomerID? WHERE?salesinfo.CustomerID?ISNULL
連接(JOIN)..之所以更有效率一些,是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
3、使用聯(lián)合(UNION)來代替手動創(chuàng)建的臨時表
MySQL從4.0的版本開始支持union查詢,它可以把需要使用臨時表的兩條或更多的select查詢合并的一個查詢中。在客戶端的查詢會話結(jié)束的時候,臨時表會被自動刪除,從而保證數(shù)據(jù)庫整齊、高效。使用union來創(chuàng)建查詢的時候,我們只需要用UNION作為關(guān)鍵字把多個select語句連接起來就可以了,要注意的是所有select語句中的字段數(shù)目要想同。下面的例子就演示了一個使用UNION的查詢。
SELECT?Name,Phone?FROM?client?UNION SELECT?Name,BirthDate?FROM?author?UNION SELECT?Name,Supplier?FROM?product
4、事務(wù)
盡管我們可以使用子查詢(Sub-Queries)、連接(JOIN)和聯(lián)合(UNION)來創(chuàng)建各種各樣的查詢,但不是所有的數(shù)據(jù)庫操作都可以只用一條或少數(shù)幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下,當(dāng)這個語句塊中的某一條語句運行出錯的時候,整個語句塊的操作就會變得不確定起來。設(shè)想一下,要把某個數(shù)據(jù)同時插入兩個相關(guān)聯(lián)的表中,可能會出現(xiàn)這樣的情況:第一個表中成功更新后,數(shù)據(jù)庫突然出現(xiàn)意外狀況,造成第二個表中的操作沒有完成,這樣,就會造成數(shù)據(jù)的不完整,甚至?xí)茐臄?shù)據(jù)庫中的數(shù)據(jù)。要避免這種情況,就應(yīng)該使用事務(wù),它的作用是:要么語句塊中每條語句都操作成功,要么都失敗。換句話說,就是可以保持?jǐn)?shù)據(jù)庫中數(shù)據(jù)的一致性和完整性。事物以BEGIN關(guān)鍵字開始,COMMIT關(guān)鍵字結(jié)束。在這之間的一條SQL操作失敗,那么,ROLLBACK命令就可以把數(shù)據(jù)庫恢復(fù)到BEGIN開始之前的狀態(tài)。
BEGIN;?INSERT?INTO?salesinfo?SET?CustomerID=14;? UPDATE?inventory?SET?Quantity=11?WHERE?item='book';?COMMIT;
事務(wù)的另一個重要作用是當(dāng)多個用戶同時使用相同的數(shù)據(jù)源時,它可以利用鎖定數(shù)據(jù)庫的方法來為用戶提供一種安全的訪問方式,這樣可以保證用戶的操作不被其它的用戶所干擾。
5、鎖定表
盡管事務(wù)是維護(hù)數(shù)據(jù)庫完整性的一個非常好的方法,但卻因為它的獨占性,有時會影響數(shù)據(jù)庫的性能,尤其是在很大的應(yīng)用系統(tǒng)中。由于在事務(wù)執(zhí)行的過程中,數(shù)據(jù)庫將會被鎖定,因此其它的用戶請求只能暫時等待直到該事務(wù)結(jié)束。如果一個數(shù)據(jù)庫系統(tǒng)只有少數(shù)幾個用戶來使用,事務(wù)造成的影響不會成為一個太大的問題;但假設(shè)有成千上萬的用戶同時訪問一個數(shù)據(jù)庫系統(tǒng),例如訪問一個電子商務(wù)網(wǎng)站,就會產(chǎn)生比較嚴(yán)重的響應(yīng)延遲。
其實,有些情況下我們可以通過鎖定表的方法來獲得更好的性能。下面的例子就用鎖定表的方法來完成前面一個例子中事務(wù)的功能。
LOCK?TABLE?inventory?WRITE?SELECT?Quantity?FROM?inventory?WHERE?Item='book'; ... UPDATE?inventory?SET?Quantity=11?WHERE?Item='book';?UNLOCKTABLES
這里,我們用一個select語句取出初始數(shù)據(jù),通過一些計算,用update語句將新值更新到表中。包含有WRITE關(guān)鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執(zhí)行之前,不會有其它的訪問來對inventory進(jìn)行插入、更新或者刪除的操作。
6、使用外鍵
鎖定表的方法可以維護(hù)數(shù)據(jù)的完整性,但是它卻不能保證數(shù)據(jù)的關(guān)聯(lián)性。這個時候我們就可以使用外鍵。
例如,外鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這里,外鍵可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID,任何一條沒有合法CustomerID的記錄都不會被更新或插入到salesinfo中。
CREATE??TABLE??customerinfo(CustomerIDINT??NOT????NULL,PRIMARYKEY(CustomerID))TYPE=INNODB; CREATE??TABLE??salesinfo(SalesIDNT??NOT??NULL,CustomerIDINT??NOT??NULL, PRIMARYKEY(CustomerID,SalesID), FOREIGNKEY(CustomerID)??REFERENCES??customerinfo(CustomerID)??ON??DELETE????CASCADE)TYPE=INNODB;
注意例子中的參數(shù)“ON DELETE CASCADE”。該參數(shù)保證當(dāng)customerinfo表中的一條客戶記錄被刪除的時候,salesinfo表中所有與該客戶相關(guān)的記錄也會被自動刪除。
7、使用索引
索引是提高數(shù)據(jù)庫性能的常用方法,它可以令數(shù)據(jù)庫服務(wù)器以比沒有索引快得多的速度檢索特定的行,尤其是在查詢語句當(dāng)中包含有MAX(),MIN()和ORDERBY這些命令的時候,性能提高更為明顯。
那該對哪些字段建立索引呢?
一般說來,索引應(yīng)建立在那些將用于JOIN,WHERE判斷和ORDERBY排序的字段上。盡量不要對數(shù)據(jù)庫中某個含有大量重復(fù)的值的字段建立索引。對于一個ENUM類型的字段來說,出現(xiàn)大量重復(fù)值是很有可能的情況
例如customerinfo中的“province”..字段,在這樣的字段上建立索引將不會有什么幫助;相反,還有可能降低數(shù)據(jù)庫的性能。我們在創(chuàng)建表的時候可以同時創(chuàng)建合適的索引,也可以使用ALTERTABLE或CREATEINDEX在以后創(chuàng)建索引。此外,MySQL從版本3.23.23開始支持全文索引和搜索。全文索引在MySQL中是一個FULLTEXT類型索引,但僅能用于MyISAM類型的表。對于一個大的數(shù)據(jù)庫,將數(shù)據(jù)裝載到一個沒有FULLTEXT索引的表中,然后再使用ALTERTABLE或CREATEINDEX創(chuàng)建索引,將是非常快的。但如果將數(shù)據(jù)裝載到一個已經(jīng)有FULLTEXT索引的表中,執(zhí)行過程將會非常慢。
8、優(yōu)化的查詢語句
絕大多數(shù)情況下,使用索引可以提高查詢的速度,但如果SQL語句使用不恰當(dāng)?shù)脑挘饕龑o法發(fā)揮它應(yīng)有的作用。
28種 SQL 查詢語句的優(yōu)化方法:
1、應(yīng)盡量避免在 where 子句中使用 != 或者 操作符,否則將引擎放棄使用索引而進(jìn)行全表掃描。
2、應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:
select?id?from?t?where?num?is?null;
可以在 num 上設(shè)置默認(rèn)值 0 ,確保表中 num 列沒有 null 值,然后這樣查詢:
select?id?from?t?where?num?=?0;
3、查詢語句的查詢條件中只有OR關(guān)鍵字,并且OR前后的兩個條件中的列都是索引時,查詢中才使用索引。否則,查詢將不使用索引。
4、對查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描,首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
5、前導(dǎo)模糊查詢不能利用索引(like ‘%XX’或者like ‘%XX%’),可以使用索引覆蓋避免。
6、in 和 not in 也要慎用,否則會導(dǎo)致全表掃描。如:
select?id?from?t?where?num?in(1,2,3)
對于連續(xù)的數(shù)值,能用 between 就不要用 in 了:?
select?id?from?t?where?num?between?1?and?3
7、如果在 where 子句中使用參數(shù),也會導(dǎo)致全表掃描。因為 SQL 只有在運行時才會解析局部變量,但優(yōu)化程序不能將訪問計劃的選擇到運行時;它必須在編譯時進(jìn)行選擇。然而,如果在編譯時簡歷訪問計劃,變量的值還是未知的,因而無法作為索引選擇的輸入項。如下面語句將進(jìn)行全表掃描:?
select?id?from?t?where?num=@num
可以改為強制查詢使用索引:?
select?id?from?t?with(index(索引名))?where?num=@num
8、應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:
select?id?from?t?where?num/2?=?100;
應(yīng)改為:
select?id?from?t?where?num?=?100?*?2;
9、應(yīng)盡量避免在 where 子句中對字段進(jìn)行函數(shù)操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:
select?id?from?t?where?substring(name,?1,?3)?=?’abc’–name;??//以abc開頭的id ? select?id?from?t?where?datediff(day,createdate,’2005-11-30′)?=?0–’2005-11-30′;??//生成的id
應(yīng)改為:
select?id?from?t?where?name?like?‘a(chǎn)bc%’ ? select?id?from?t?where?createdate?>=?’2005-11-30′?and?createdate?<p>10、不要在 where 子句中的 “=” 左邊進(jìn)行函數(shù),算術(shù)運算或者其他表達(dá)式運算,否則系統(tǒng)將可能無法正確使用索引。?</p><p>11、在使用索引字段作為條件時,如果該索引是復(fù)合索引,那么必須使用到該索引的第一個字段作為條件時才能保證系統(tǒng)使用該索引,否則該索引將不會被使用,并且應(yīng)盡可能的讓字段順序與索引順序相一致。</p><p>12、很多時候用 exists 代替 in 是一個好的選擇:?</p><pre class="brush:js;toolbar:false;">select?num?from?a?where?num?in(select?num?from?b);
用下面的語句替換:?
select?num?from?a?where?exists(select?1?from?b?where?num=a.num);
13、索引并不是越多越好,索引固然可以提高相應(yīng)的 select 的效率,但同時也降低了 insert 及 update 的效率,因為 insert 或 update 時有可能會重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。一個表的索引數(shù)最好不要超過6個,若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
14、并不是所有索引對查詢都有效,SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的,當(dāng)索引列有大量數(shù)據(jù)重復(fù)時,SQL查詢可能不會去利用索引,如一表中有字段 sex,male、female幾乎各一半,那么即使在sex上建了索引也對查詢效率起不了作用。
15、盡量使用數(shù)字型字段,若只含數(shù)值信息的字段盡量不要設(shè)計為字符型,這會降低查詢和連接的性能,并會增加存儲開銷。這是因為引擎在處理查詢和連接時會 逐個比較字符串中每一個字符,而對于數(shù)字型而言只需要比較一次就夠了。
16、盡可能的使用 varchar/nvarchar 代替 char/nchar ,因為首先變長字段存儲空間小,可以節(jié)省存儲空間,其次對于查詢來說,在一個相對較小的字段內(nèi)搜索效率顯然要高些。較一次就夠了。
17、任何地方都不要使用?select * from t?,用具體的字段列表代替?*,不要返回用不到的任何字段。
18、盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù),請注意索引非常有限(只有主鍵索引)。
19、避免頻繁創(chuàng)建和刪除臨時表,以減少系統(tǒng)表資源的消耗。
20、臨時表并不是不可使用,適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В纾?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是,對于一次性事件,最好使用導(dǎo)出表。
21、在新建臨時表時,如果一次性插入數(shù)據(jù)量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果數(shù)據(jù)量不大,為了緩和系統(tǒng)表的資源,應(yīng)先 create table,然后 insert。
22、如果使用到了臨時表,在存儲過程的最后務(wù)必將所有的臨時表顯式刪除,先 truncate table ,然后 drop table ,這樣可以避免系統(tǒng)表的較長時間鎖定。
23、盡量避免使用游標(biāo),因為游標(biāo)的效率較差,如果游標(biāo)操作的數(shù)據(jù)超過1萬行,那么就應(yīng)該考慮改寫。
24、使用基于游標(biāo)的方法或臨時表方法之前,應(yīng)先尋找基于集的解決方案來解決問題,基于集的方法通常更有效。
25、與臨時表一樣,游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法,尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時 間允許,基于游標(biāo)的方法和基于集的方法都可以嘗試一下,看哪一種方法的效果更好。
26、在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ,在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息。
27、盡量避免向客戶端返回大數(shù)據(jù)量,若數(shù)據(jù)量過大,應(yīng)該考慮相應(yīng)需求是否合理。
28、盡量避免大事務(wù)操作,提高系統(tǒng)并發(fā)能力。
【相關(guān)推薦:mysql視頻教程】