目錄

• 1、字段屬性
• 2、字段NOT NULL
• 3、JOIN代替子查詢
• 4、UNION代替手動(dòng)創(chuàng)建臨時(shí)表
• 5、事務(wù)
• 6、索引

下面聊聊mysql常見(jiàn)的優(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)鍵字開(kāi)始，commit關(guān)鍵字結(jié)束。若其中的一條sql執(zhí)行失敗，那么rollback命令就可以把數(shù)據(jù)庫(kù)恢復(fù)到begin開(kāi)始之前的狀態(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ù)開(kāi)始之前和事務(wù)結(jié)束之后，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞；
• 隔離性：Isolation，數(shù)據(jù)庫(kù)允許多個(gè)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫(xiě)和修改的能力，隔離性可以防止多個(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)文章希望大家以后多多支持！