mysql效能最佳化

標籤：第一個   nio   資料庫最佳化   資料   頻率   業務   掃描   提高   垂直   

效能最佳化

myql資料庫最佳化的原則：

1.減少系統的瓶頸。

2.減少資源的佔用。

3.增加系統的反應速度。

 

 

最佳化查詢

1.索引對查詢的影響

比如有一個表student表，有id、name、sex、no，4個欄位，裡面有20條記錄，如果沒有索引時，查詢no=20的學生。

sql語句是SELECT * from student WHERE no =‘20‘。分析當前sql ：執行 explain SELECT * from student WHERE no =‘20‘

 

這樣可以看到rows是20，也就是說查詢掃描了20條記錄。

• select_type：SIMPLE，這個不是很關鍵，只是表示這是一次簡單的查詢，沒有join，沒有union，沒有中間表
• type：ALL，表示該次SQL進行了全表查詢

在no欄位使用索引後

看到結果是1，證明查詢的時候只掃描了一條記錄。查詢速度自然比不使用索引快。而且possible_keys和key都可以說明使用了索引。

 

2.使用索引查詢

（1）使用like查詢時

如果第一個字元是%，索引不起作用，只要%不在第一位置索引才起作用。

（2）使用多列索引查詢

mysql可以為多個欄位建立索引，一個索引可以包含16個欄位，只有查詢條件中使用了這些欄位的第一個欄位時，索引才有用。

（3）使用or關鍵字查詢

查詢條件只有or關鍵字，且or前後兩個條件都是索引時，查詢才會使用索引。

3.避免使用select *

查詢內容越多，自然越慢。

4.永遠為每張表設定一個id

表裡設定一個id作為主鍵，最好是int類型，並加上自增標識。使用varchar當做主鍵會使效能下降。

 

資料庫結構最佳化

1.將欄位很多的表分解為多個表

對於欄位很多的表，如果有些欄位使用的頻率很低，可以將這些欄位分離出來形成新表。因為一個表資料量很大時，會由於使用頻率低的欄位存在而變慢，通過分解欄位，可以提高表的查詢效率。

2.增加中間表

對於需要經常聯集查詢的表，可以建立中間表以提高查詢的效率。把經常要聯集查詢的資料插入到中間表，然後將原來聯集查詢改為對中間表的查詢，以此來提高表的查詢效率。

3.最佳化插入記錄的速度

影響插入速度主要是索引、唯一性校正、一次插入的條數。以InnoDB表為例。

（1）禁止唯一性檢查

插入資料時，mysql會對插入的記錄。唯一性校正會降低插入記錄的速度。等記錄插入完畢後再開啟。

SET UNIQUE_CHECKS = 0;開啟

SET UNIQUE_CHECKS = 1;關閉

（2）禁止外鍵檢查

插入資料之前禁止對外鍵的檢查，資料插入後再恢複對外鍵的檢查。

SET foreign_key_checks = 0;

SET foreign_key_checks = 1;

（3）禁止自動認可

插入資料之前禁止事務的自動認可，資料匯入後再恢複。

SET autocommit = 0;

SET autocommit = 1;

 

資料庫最佳化

1.最佳化表的結構，對常用欄位和非常用的欄位分開儲存。

2.最佳化sql合理使用索引。

3.做資料庫讀寫分離，減少io壓力。因為資料庫對記錄做持久化並儲存在磁碟上，對磁碟的io是非常消耗效能的操作，因此，讀寫在一個庫中會大大增加io的壓力。

4.嘗試使用緩衝，不要讓資料都走資料庫。

5.對業務做垂直分割。

6.對錶做水平分割。

合理使用所以對效能提升至關重要。

如果索引建立在重複資料很少的欄位上效果明顯，對於重複資料量大的欄位就不見得會提升效率。

對索引列使用函數會導致索引失效。技巧：對索引列不使用函數而對常數項使用函數，索引就有效了。

 

mysql效能最佳化