Mysql優化方法詳細介紹

2023-02-05 14:03:55

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優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！