下面聊聊mysql常見的優(yōu)化方法。

1、字段屬性

mysql是一種關(guān)系型數(shù)據(jù)庫，可以很好地支撐大數(shù)據(jù)量的存儲(chǔ)，但一般來說，數(shù)據(jù)庫中的表越小，執(zhí)行的查詢也就越快，因此在建表時(shí)，選擇最合適的字段屬性，使字段的長度盡可能的小。

如，在定義名稱字段時(shí)，若將其設(shè)置為char(255)，顯然給數(shù)據(jù)庫增加了不必要的空間。

2、字段NOT NULL

盡量把字段設(shè)置為NOT NULL，這樣在執(zhí)行查詢的時(shí)候，數(shù)據(jù)庫不用去比較NULL值了。

3、JOIN代替子查詢

在sql查詢語句中，盡量使用連接join代替子查詢，提高查詢效率。

如，查詢客戶基本信息表中沒有任何訂單的客戶，可以利用子查詢先從銷售信息表中將所有訂單的客戶id取出來，然后將結(jié)果傳遞給主查詢。

select * from custominfo where customId not in (select customId from salesinfo)

利用連接join來實(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í)表來完成這個(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ì)返回左表所有的行，即使在右表中沒有匹配的記錄；
• right join：在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回右表所有的行，即使在左表中沒有匹配的記錄；
• full join：在兩張表進(jìn)行連接查詢時(shí)，返回左表和右表中所有沒有匹配的行；

4、UNION代替手動(dòng)創(chuàng)建臨時(shí)表

在客戶端查詢會(huì)話結(jié)束的時(shí)候，臨時(shí)表會(huì)被自動(dòng)刪除，從而保證數(shù)據(jù)庫整齊、高效。使用union來創(chuàng)建查詢的時(shí)候，我們只需要用union作為關(guān)鍵字把多個(gè)select語句連接起來就可以了，要注意的是所有select語句中的字段數(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)行唯一性過濾，會(huì)涉及到排序，增加了cpu運(yùn)算，和資源消耗。

5、事務(wù)

盡管我們可以使用子查詢、JOIN和UNION來創(chuàng)建各種各樣的查詢，但不是所有的數(shù)據(jù)庫操作，都可以只用一條或少數(shù)幾條就可以完成的。更多的時(shí)候是需要用一系列的語句來完成某種工作。但是在這種情況下，當(dāng)這個(gè)語句塊中的某一條語句運(yùn)行出錯(cuò)的時(shí)候，整個(gè)語句塊的操作就會(huì)變得不確定起來。要避免這種情況，就出現(xiàn)了事務(wù)，要么每條語句都執(zhí)行成功，要么都失敗。

事務(wù)以begin關(guān)鍵字開始，commit關(guān)鍵字結(jié)束。若其中的一條sql執(zhí)行失敗，那么rollback命令就可以把數(shù)據(jù)庫恢復(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ù)庫的完整性沒有被破壞；
• 隔離性：Isolation，數(shù)據(jù)庫允許多個(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ù)庫性能的常用方法。

一般來說，索引應(yīng)該建立在那些將用于join，where判斷和orderby排序的字段上。

盡量不要對(duì)數(shù)據(jù)庫中某個(gè)含有大量重復(fù)的值的字段建立索引，對(duì)于一個(gè)ENUM類型的字段來說，出現(xiàn)大量重復(fù)值是很有可能的情況。

如customerinfo中的“province”…字段，在這樣的字段上建立索引將不會(huì)有什么幫助；相反，還有可能降低數(shù)據(jù)庫的性能。我們?cè)趧?chuàng)建表的時(shí)候可以同時(shí)創(chuàng)建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以后創(chuà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)文章希望大家以后多多支持腳本之家！

最新評(píng)論