Mysql優(yōu)化方法詳細(xì)介紹
下面聊聊mysql常見的優(yōu)化方法。
1、字段屬性
mysql是一種關(guān)系型數(shù)據(jù)庫(kù),可以很好地支撐大數(shù)據(jù)量的存儲(chǔ),但一般來(lái)說(shuō),數(shù)據(jù)庫(kù)中的表越小,執(zhí)行的查詢也就越快,因此在建表時(shí),選擇最合適的字段屬性,使字段的長(zhǎng)度盡可能的小。
如,在定義名稱字段時(shí),若將其設(shè)置為char(255),顯然給數(shù)據(jù)庫(kù)增加了不必要的空間。
2、字段NOT NULL
盡量把字段設(shè)置為NOT NULL,這樣在執(zhí)行查詢的時(shí)候,數(shù)據(jù)庫(kù)不用去比較NULL值了。
3、JOIN代替子查詢
在sql查詢語(yǔ)句中,盡量使用連接join代替子查詢,提高查詢效率。
如,查詢客戶基本信息表中沒(méi)有任何訂單的客戶,可以利用子查詢先從銷售信息表中將所有訂單的客戶id取出來(lái),然后將結(jié)果傳遞給主查詢。
select * from custominfo where customId not in (select customId from salesinfo)
利用連接join來(lái)實(shí)現(xiàn),速度會(huì)更快。
select * from custominfo left join salesinfo on custominfo.customId = salesinfo.customId where salesinfo.customId is NULL
連接join之所以更有效率,是因?yàn)閙ysql不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上需要兩個(gè)步驟的查詢工作。
sql中連接查詢方式有inner join(內(nèi)連接)、left join(左連接)、right join(有連接)、full join(全連接)四種。
- inner join:在兩張表進(jìn)行連接查詢時(shí),只保留兩張表中完全匹配的結(jié)果集;
- left join:在兩張表進(jìn)行連接查詢時(shí),會(huì)返回左表所有的行,即使在右表中沒(méi)有匹配的記錄;
- right join:在兩張表進(jìn)行連接查詢時(shí),會(huì)返回右表所有的行,即使在左表中沒(méi)有匹配的記錄;
- full join:在兩張表進(jìn)行連接查詢時(shí),返回左表和右表中所有沒(méi)有匹配的行;
4、UNION代替手動(dòng)創(chuàng)建臨時(shí)表
在客戶端查詢會(huì)話結(jié)束的時(shí)候,臨時(shí)表會(huì)被自動(dòng)刪除,從而保證數(shù)據(jù)庫(kù)整齊、高效。使用union來(lái)創(chuàng)建查詢的時(shí)候,我們只需要用union作為關(guān)鍵字把多個(gè)select語(yǔ)句連接起來(lái)就可以了,要注意的是所有select語(yǔ)句中的字段數(shù)目要相同。
select name,phone from client union select name,birthday from author union select name,supplier from product
當(dāng)確認(rèn)各個(gè)select查詢不可能出現(xiàn)重復(fù)結(jié)果集或不在乎重復(fù)結(jié)果集的時(shí)候盡量用union all而不是union,因?yàn)閡nion和union all的差異主要是前者需要將兩個(gè)或多個(gè)結(jié)果集合并后再進(jìn)行唯一性過(guò)濾,會(huì)涉及到排序,增加了cpu運(yùn)算,和資源消耗。
5、事務(wù)
盡管我們可以使用子查詢、JOIN和UNION來(lái)創(chuàng)建各種各樣的查詢,但不是所有的數(shù)據(jù)庫(kù)操作,都可以只用一條或少數(shù)幾條就可以完成的。更多的時(shí)候是需要用一系列的語(yǔ)句來(lái)完成某種工作。但是在這種情況下,當(dāng)這個(gè)語(yǔ)句塊中的某一條語(yǔ)句運(yùn)行出錯(cuò)的時(shí)候,整個(gè)語(yǔ)句塊的操作就會(huì)變得不確定起來(lái)。要避免這種情況,就出現(xiàn)了事務(wù),要么每條語(yǔ)句都執(zhí)行成功,要么都失敗。
事務(wù)以begin關(guān)鍵字開始,commit關(guān)鍵字結(jié)束。若其中的一條sql執(zhí)行失敗,那么rollback命令就可以把數(shù)據(jù)庫(kù)恢復(fù)到begin開始之前的狀態(tài)。
begin; update salesinfo set customerId = 1; update custominfo set name = 'daivid'; commit;
事務(wù)必須滿足四個(gè)條件ACID:
- 原子性:Atomicity,一個(gè)事物(transaction)中的所有操作,要么全部完成,要么全部不完成,不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié);
- 一致性:Consistency,在事務(wù)開始之前和事務(wù)結(jié)束之后,數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞;
- 隔離性:Isolation,數(shù)據(jù)庫(kù)允許多個(gè)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力,隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同的級(jí)別,包括讀未提交(Read uncommitted)、讀已提交(Read committed)、可重復(fù)讀(repeateable read)和串行化(Serializable);
- 持久性:Durability,事務(wù)處理結(jié)束后,對(duì)數(shù)據(jù)的修改就是永久的,即便系統(tǒng)故障也不會(huì)丟失;
6、索引
索引是提高數(shù)據(jù)庫(kù)性能的常用方法。
一般來(lái)說(shuō),索引應(yīng)該建立在那些將用于join,where判斷和orderby排序的字段上。
盡量不要對(duì)數(shù)據(jù)庫(kù)中某個(gè)含有大量重復(fù)的值的字段建立索引,對(duì)于一個(gè)ENUM類型的字段來(lái)說(shuō),出現(xiàn)大量重復(fù)值是很有可能的情況。
如customerinfo中的“province”…字段,在這樣的字段上建立索引將不會(huì)有什么幫助;相反,還有可能降低數(shù)據(jù)庫(kù)的性能。我們?cè)趧?chuàng)建表的時(shí)候可以同時(shí)創(chuàng)建合適的索引,也可以使用ALTER TABLE或CREATE INDEX在以后創(chuàng)建索引。
避免索引失效的場(chǎng)景:
SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那樣支持函數(shù)索引,即使d字段有索引,也會(huì)直接全表掃描,應(yīng)改為
SELECT * FROM t WHERE d >= '2016-01-01';
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
應(yīng)改為
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
SELECT * FROM t WHERE name LIKE ‘%de%';
應(yīng)改為
SELECT * FROM t WHERE name LIKE ‘de%';
到此這篇關(guān)于Mysql優(yōu)化方法詳細(xì)介紹的文章就介紹到這了,更多相關(guān)Mysql優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實(shí)例詳解
這篇文章主要介紹了mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實(shí)例詳解的相關(guān)資料,需要的朋友可以參考下2017-03-03Mysql DNS反向解析導(dǎo)致連接超時(shí)過(guò)程分析(skip-name-resolve)
從其它地方連接MySQL數(shù)據(jù)庫(kù)的時(shí)候,有時(shí)候很慢。慢的原因有可能是MySQL進(jìn)行反向DNS解析造成的,這里簡(jiǎn)單介紹下原理,需要的朋友可以參考下2013-03-03將圖片儲(chǔ)存在MySQL數(shù)據(jù)庫(kù)中的幾種方法
今天小編就為大家分享一篇關(guān)于將圖片儲(chǔ)存在MySQL數(shù)據(jù)庫(kù)中的幾種方法,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-03-03Mysql忘記密碼怎么重置密碼的幾種實(shí)現(xiàn)方法
本文主要介紹了Mysql忘記密碼怎么重置密碼的幾種實(shí)現(xiàn)方法,每種方法都有其適用的情況,根據(jù)具體情況選擇合適的方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10MySQL中UPDATE語(yǔ)句使用的實(shí)例教程
這篇文章主要介紹了MySQL中UPDATE語(yǔ)句使用的實(shí)例教程,包括UPDATE的使用中所容易引起的性能問(wèn)題的分析,需要的朋友可以參考下2015-11-11MySQL中的數(shù)據(jù)加密解密安全技術(shù)教程
在數(shù)據(jù)庫(kù)應(yīng)用程序中,數(shù)據(jù)的安全性是至關(guān)重要的,MySQL作為一種常用的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),也提供了一些數(shù)據(jù)加密和解密的技巧來(lái)保護(hù)敏感數(shù)據(jù)的安全性,為了保護(hù)敏感數(shù)據(jù)免受未經(jīng)授權(quán)的訪問(wèn),我們可以使用加密和解密技術(shù)2024-01-01