目錄

• 1、字段屬性
• 2、字段NOT NULL
• 3、JOIN代替子查詢
• 4、UNION代替手動創建臨時表
• 5、事務
• 6、索引

下面聊聊mysql常見的優化方法。

1、字段屬性

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

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

2、字段NOT NULL

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

3、JOIN代替子查詢

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

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

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

利用連接join來實現，速度會更快。

select * from custominfo left join salesinfo on custominfo.customId = salesinfo.customId where salesinfo.customId is NULL

連接join之所以更有效率，是因為mysql不需要在內存中創建臨時表來完成這個邏輯上需要兩個步驟的查詢工作。

sql中連接查詢方式有inner join（內連接）、left join（左連接）、right join（有連接）、full join（全連接）四種。

• inner join：在兩張表進行連接查詢時，只保留兩張表中完全匹配的結果集；
• left join：在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄；
• right join：在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄；
• full join：在兩張表進行連接查詢時，返回左表和右表中所有沒有匹配的行；

4、UNION代替手動創建臨時表

在客戶端查詢會話結束的時候，臨時表會被自動刪除，從而保證數據庫整齊、高效。使用union來創建查詢的時候，我們只需要用union作為關鍵字把多個select語句連接起來就可以了，要注意的是所有select語句中的字段數目要相同。

select name,phone from client union
select name,birthday from author union
select name,supplier from product

當確認各個select查詢不可能出現重復結果集或不在乎重復結果集的時候盡量用union all而不是union，因為union和union all的差異主要是前者需要將兩個或多個結果集合并后再進行唯一性過濾，會涉及到排序，增加了cpu運算，和資源消耗。

5、事務

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

事務以begin關鍵字開始，commit關鍵字結束。若其中的一條sql執行失敗，那么rollback命令就可以把數據庫恢復到begin開始之前的狀態。

begin;
update salesinfo set customerId = 1;
update custominfo set name = "daivid";
commit;

事務必須滿足四個條件ACID：

• 原子性：Atomicity，一個事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節；
• 一致性：Consistency，在事務開始之前和事務結束之后，數據庫的完整性沒有被破壞；
• 隔離性：Isolation，數據庫允許多個事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務隔離分為不同的級別，包括讀未提交(Read uncommitted)、讀已提交(Read committed)、可重復讀(repeateable read)和串行化(Serializable)；
• 持久性：Durability，事務處理結束后，對數據的修改就是永久的，即便系統故障也不會丟失；

6、索引

索引是提高數據庫性能的常用方法。

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

盡量不要對數據庫中某個含有大量重復的值的字段建立索引，對于一個ENUM類型的字段來說，出現大量重復值是很有可能的情況。

如customerinfo中的“province”…字段，在這樣的字段上建立索引將不會有什么幫助；相反，還有可能降低數據庫的性能。我們在創建表的時候可以同時創建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以后創建索引。

避免索引失效的場景：

SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那樣支持函數索引，即使d字段有索引，也會直接全表掃描，應改為

SELECT * FROM t WHERE d >= "2016-01-01";

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

應改為

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

SELECT * FROM t WHERE name LIKE ‘%de%";

應改為

SELECT * FROM t WHERE name LIKE ‘de%";

到此這篇關于Mysql優化方法詳細介紹的文章就介紹到這了,更多相關Mysql優化內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！