mysql資料庫最佳化

標籤：

1.表設計
    遵循三範式,但必要的時候做資料冗餘，舉例說明：在許可權模型中可能會用到5張表 使用者表、角色表、許可權表，還有使用者角色關聯表和角色許可權關聯表。如果此時要通過使用者查詢許可權則必須關聯查詢或者使用多條sql查詢，此時可以在使用者表增加一個欄位來儲存使用者的許可權（例如將許可權值使用逗號隔開），這樣可以如果查詢某使用者的許可權可以直接在使用者表中查詢，查詢後再用程式來處理。

    設計表時要選用合適的儲存引擎，對於經常查詢的表，並且不需要事務等應用，優先選用MyISAM（例如新聞表），如果需要交易處理對穩定性要求高的表優先選用InnoDB儲存引擎（例如訂單表，訂單商品表），目前InnoDB已經成為新版MySQL的預設儲存引擎，在高並發寫入時的穩定性相比MyISAM更高,在讀取速度上速度也比較快，所以推薦使用此引擎。 不需要永久儲存的資料可以選用Memory儲存引擎（例如session，購物車）

    對於不同的欄位選用合適的欄位類型，例如IP地址應該儲存為整型，見將IP以整型的方式儲存到資料庫中。固定長度的字串要使用char，例如md5加密的密碼。

2.分表技術
    橫向分表（各個表的欄位類型與數量是完全一致的），例如使用者表，可以按使用者首字母橫向切分；交易記錄可以按照年份或者月份切分，每張表的欄位一致，至於按什麼規則來來區分可以看具體的業務。
    縱向分表（各個表的欄位不一致，但條數是一致的），例如某商城網站使用者表使用MyISAM引擎，可以應對使用者登入時的查詢操作，但是使用者之間轉賬需要事務來保證安全，這樣就可以將使用者餘額欄位分離出來組成一張InnoDB引擎的新表，和使用者表關聯，既滿足高效查詢，又滿足穩定性。
    另外還可以進行分區，或者分庫，都是類似的思路。
3.索引最佳化
    常見的索引有 主鍵索引、唯一索引、普通索引、全文索引（僅MyISAM儲存引擎支援，並且不支援中文，如果需要支援中文需要安裝外掛程式）。通常需要在條件欄位、排序欄位、分組欄位以及關聯欄位上建立對應索引。但重複率很高的欄位不宜建立索引,比如狀態欄位(是否被刪除,是否上架等),emum類型(性別等)，另外建立索引會對增刪改操作的速度有影響,因此頻繁更新的欄位不適合建立索引,例如文章的點擊量。
4.SQL最佳化
在開發時可以使用explain 測試sql語句
在測試結果會有如下參數，簡要解釋一下
select_type    
查詢的方式 SIMPLE表示select類型,沒有串連或者子查詢,PRIMARY表示主查詢(注意:不是主鍵,例如子查詢時的外層查詢,UNION查詢時的第一個select),DEPENDENT SUBQUERY表示子查詢語句,UNION表示UNION查詢時除了PRIMARY(第一條語句)之外的語句
table        表名
type        掃描類型（重要）如果是All表示全表掃描，效率低;如果是const表示最多有一行與結果匹配，效率高;system表示表中僅有一條資料,肯定高效;eq_ref表示所以用到主鍵或者唯一索引;ref表示用到普通索引;range表示查詢一個區間(範圍的資料);index表示都是通過索引查詢效能一般
possible_keys    可能使用到的索引（重要）
key        實際用到的索引（重要）
key_len        索引長度
ref
rows        MySQL認為它執行查詢時必須檢查的行數（重要）越小越好        
Extra        額外資訊 Using filesort表示查詢中使用了order by 並且無法利用索引排序，如果確實不需要排序可以在SQL語句末尾增加order by null.Using temporary某些操作使用了暫存資料表,不要.Using where 使用索引.

    編寫代碼以及SQL語句需要注意的如下幾項：

    1）如果查詢條件中有or,則所有使用到的欄位必須都有索引才有效；

    2）like查詢條件中如果以%開頭則索引失效；

    3）聯合(多列)索引 只有使用左邊的列索引才會生效；

    4）where後有多個查詢條件應優先使用能夠過濾資料量大的條件（先後順序很重要）；

    5）不要使用子查詢（效率很低，除非資料量非常少），儘可能少用關聯查詢，寧可份多次查詢，有條件可以封裝成預存程序。

    6）不要在迴圈中使用SQL語句，如果是查詢儘可能一次查詢然後通過程式來計算。如果是insert可使用擴充插入,即類似insert into tablename values (11,22),(33,33),(45,42)。
    檢測MySQL的索引使用方式可以使用以下命令
show global status where Variable_name like ‘Handler_read%‘; 
解釋一下主要參數
Handler_read_first 全索引掃描的次數，使用到了索引，但是全部索引；
Handler_read_key 越大越好，使用索引的情況；
Handler_read_rnd_next 表示沒有命中，值越大索引利用率越低；

對於已經上線的項目可以開啟MySQL的慢查詢來定位低效率的SQL，見使用MySQL的慢查詢日誌找到低效的SQL語句並通過explain分析進行最佳化
使用如下命令
show global status like ‘Com%‘;
show global status like ‘InnoDB_rows%‘;
show variables like  ‘long_query_time‘;
show variables like  ‘%slow%‘;

查看MySQL各種SQL的執行頻度 以及慢查詢狀態

使用optimize table 表名;命令來最佳化表,執行時會佔用大量資源,所以建議在使用者訪問量少的時刻執行。
5.配置最佳化

修改mysql的設定檔，通常在windows下為my.ini，在Linux下為my.cnf

query_cache_size = 64M

該值可以通過使用SHOW STATUS LIKE ‘Qcache%‘;命令來查看MySQL狀態來進行相應的更改，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況需要增加cache值；如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，是理想狀態，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝。
對於MyISAM設定
key_buffer_size = 32M
MyISAM表會使用作業系統的緩衝來快取資料，設定的值取決於系統記憶體、索引大小、資料量以及負載。
對於InnoDB設定
innodb_buffer_pool_size = 2.4G
可以設定記憶體的70%左右，當然要考慮記憶體的整體佔用情況。

6.硬體架構設計最佳化

更換硬體 硬碟更換為SSD 可提高几倍的效率，
延時寫入(更新),對於即時性較小的資料可以先將資料寫入到檔案,每隔一段時間批量寫入(更新)到資料庫中
搭建MySQL主從同步架構，使用讀寫分離技術
使用sphinx 全文檢索索引伺服器， 減輕MySQL伺服器的壓力

mysql資料庫最佳化