Mysql效能調優

標籤：序列化   緩衝區   事務隔離   2nf   建立   復原   3nf   定位   序列   

1. 宏觀上調優可以考慮三個部分，分別為硬體、網路、軟體，此處主要考慮軟體調優

（1）軟體調優包括：表設計（範式、欄位類型、資料存放區引擎）、SQL語句語索引、設定檔參數、檔案系統、作業系統、MYSQL版本、體系架構

2. 表設計

（1） 第一範式（1NF）：資料庫表中的欄位都是單一屬性的，不可再分。這個單一屬性由基礎資料型別 (Elementary Data Type)構成，包括整型、字元型、邏輯型、日期型等。（只要是關係型資料庫都符合第一範式）

（2） 第二範式（2NF）:要求實體的屬性完全依賴於主關鍵字，即只能有一個主關鍵字。也即非關鍵字段都依賴於主鍵

（3） 第三範式（3NF）:不存在非關鍵字段對任一候選關鍵字段的傳遞函數依賴。

3. 欄位類型的選擇：一般原則是保小不保大，能用占位元組少的欄位就不用大欄位。

3.1 數實值型別

（1）錄入手機號碼用bigint

（2）IP地址可以採用unsigned int整型，並採用INET_NTOA()負責將數字轉換為IP地址，INET_ATON()負責將IP地址轉換為數字

（3）根據需要選擇最小整數類型

3.2 字元類型

（1）計算varchar的最大長度：varchar(32766)

3.3 時間類型

4. 採用更合適的鎖機制

4.1 MYSQL的鎖分為三種：

（1）表級鎖：開銷小、加鎖快，不會出現死結；鎖粒度大，發生衝突的機率最高，並發度最低。MyISAM引擎

（2）行級鎖：開銷大、加鎖慢、會出現死結；鎖粒度最小，發生衝突的機率最低，並發度最高。InnoDB引擎

（3）頁面鎖：介於中間。NDB引擎

4.2 MyISAM引擎：

（1）對MyiSAM表的讀操作（加讀鎖），其它進程可以讀，但是會阻塞同一表的寫操作；

（2）對MyISAM表的寫操作，會阻塞其它線程對同一表的讀寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作；

4.3 InnoDB引擎：InnoDB儲存引擎是通過給索引上的索引項目加鎖來實現的，這說明只有通過索引條件檢索資料，InnoDB才會使用行級鎖，否則，InnoDB將採用表鎖 （為防止在高並發訪問下，大量事務因無法立即獲得所需的鎖而掛起，會佔用大量電腦資源，造成嚴重的效能問題，這時可以通過設定合適的鎖等待逾時閾值參數innode_lock_wait_timeout來解決，一般設定為100s）

5 選擇合適的交易隔離等級

5.1 事務的屬性：

（1）原子性

（2）一致性

（3）隔離性

（4）持久性

5.2 不確定的讀取情況

（1）髒讀：一個事務開始讀取某行資料，另外一個事務已經更新了此資料但沒有及時提交。而另一事務可能發生復原。

（2）不可重複讀取：一個事務對同一行資料重複讀取兩次，但是得到了不同的結果。例如，在兩次讀取的途中，有另外一個事務對該行的資料進行了修改並提交。

（3）兩次更新問題：無法重複讀取的特例。兩個並發事務同時讀取同一行資料，然後其中一個對它進行修改提交，而另一個也進行了修改提交。這就造成第一次寫操作失效

（4）幻讀：事務在操作過程中進行了兩次查詢，第二次查詢的結果包含了第一次查詢中未出現的資料。這是因為在兩次查詢過程中有另外一個事務插入資料。

5.3 InnodDB 交易隔離等級

（1）Read Uncommitted 讀未提交：會產生髒讀取。（“排他寫鎖”）

（2）Read Committed 讀提交：會產生不可重複讀取。（“瞬間共用讀鎖”和“排他寫鎖”）

（3）Repeated Read 可重複讀取：會產生幻讀。（“共用讀鎖”和“排他寫鎖”）

（4）Serilizable 序列化：提供嚴格的事務隔離，事務嚴格序列化，事務只能一個接一個地執行，不能並發執行。

 6. SQL最佳化與合理利用索引

6.1 通過開啟慢日誌來定位執行很慢的SQL語句：（explain最佳化器查詢）

開啟慢日誌的方法：在my.cnf設定檔中，加入以下參數：

slow_query_log=1

slow_query_log_file=mysql.slow

long_query_time=2（超過2秒的SQL會記錄下來）

6.2 SQL最佳化案例分析

（1）not in 子查詢最佳化：盡量避免子查詢，採用left join表串連

（2）模式比對like ‘%xxx%‘: 在MYSQL中，like ‘xxx%’ 可以用到索引，而like ‘%xxx%‘則不行；利用覆蓋索引來進行最佳化

（3）limit分頁最佳化

（4）count(*)統計資料如何加快速度：利用輔助索引count(輔助索引)快於count（*）；

（5）SQL語句中有or條件，則會用不到索引；將其改為union all結果集合并

（6）不必要的排序；

（7）不必要的嵌套select查詢

（8）不必要的表自身串連、

（9）用where子句代替having子句

6.3 合理使用索引：索引有一定開銷，例如寫入和更新或刪除操作時都需要更新索引。此外，只有當某列被用於where子句時，才能享受到索引效能提升的好處

（1）單列索引和聯合索引的對比：聯合索引遵循最左側原則

（2）欄位使用函數，將不能用到索引

（3）致命的無引號導致全表掃描，無法用到索引：

（4）當取出的資料量超過表中資料的20%時，最佳化器就不會使用索引，而是全表掃描

（5）考慮不為某些列建立索引：

（6）group by、order by最佳化

（7）MYSQL5.6 InnoDB引擎支援全文索引

（8）MYSQL5.6支援explain update/select

（9）MySQL5.6最佳化了index merge合并索引，即可以實現一條SQL可以使用兩個索引了

7. my.cnf設定檔調優

7.1 per_thread_buffers最佳化：為每個串連到MySQL的使用者進程分配記憶體

（1）read_buffer_size: 用於表的順序掃描，表示每個線程分配的緩衝區大小。

（2）read_rnd_buffer_size: 用於表的隨機讀取，表示每個線程分配的緩衝區大小

（3）sort_buffer_size: 在表進行order by 和group by 排序操作時，由於排序的欄位沒有索引，會出現Using filesort，為了提高效能，可用此參數增加每個線程分配的緩衝區大小。

（4）thread_stack: 表示每個線程的堆棧大小

（5）join_buffer_size：

（6）max_connections: 該參數用來設定最大串連數，預設100。一般設定為512到1000即可。

7.2 global_buffers最佳化：用於在記憶體中緩衝從資料檔案中檢索出來的資料區塊，可以大大提高查詢和更新資料的效能。

（1）innodb_buffer_pool_size: 核心參數，預設為128MB

（2）innodb_additional_mem_pool_size：用來儲存資料字典資訊和其他內部資料結構，表越多，需要分配的記憶體越多，一版設定為16MB

（3）innodb_log_buffer_size: 交易記錄所使用的緩衝區。

7.3 Query Cache在不同環境下的使用： 其功能是緩衝select語句和結果集。查詢快取絕不會返回到期資料，當資料被修改後，在查詢快取中的任何相關詞條都會被清除。

如果環境中寫操作很多，不適合開啟query_cache_type，如果讀操作頻繁而寫操作很少，則開啟query_cache_type=1。

 

Mysql效能調優