MySQL應用優化
MySQL應用優化
1.數據庫連接池
對於數據庫來說，頻繁的關閉創建連接是比較消耗資源的，所以有必要建立 數據庫連接池
2.減少對MySQL的訪問
在寫代碼的時候，一個sql查詢了某兩個字段，而另一個sql查詢了id字段，則可以將這sql合併，就可以減少對數據進行重複檢索。
還可以增加緩存層，使用Mybatis、Hibernate提供的一級二級緩存，或者Redis數據庫來緩存數據
3.負載均衡
（1）利用某種均衡算法，將載荷量分佈在不同的服務器上，比如Nginx代理服務器
（2）利用MySQL主從複製，實現讀寫分離
我們有一個MySQL的主節點服務器，它會將數據同步到其他節點的服務器，並且完全一致，在進行增刪改操作的時候，主節點服務器發生修改，並且同步到子節點服務器，但是在查詢的時候，就不需要通過主節點服務器，直接在子節點進行操作，這樣主從複製讀寫分離就能分解數據庫的壓力。
（3）使用分佈式數據庫架構
4.MySQL查詢緩存優化
在MySQL中開啟緩存查詢，當執行完全相同的SQl語句的同時，服務器就直接在緩存中拿數據，數據被修改緩存就會失效。
1.服務器收到一個sql
2.先檢查緩存器中是否有緩存，有的話直接返回結果
3.沒有的話要進行解析SQL解析、預處理，再由優化器生成對應的執行計劃
4.MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢
5.將結果返回給客戶端
5.MySQL如何使用緩存
1.首先查看數據庫是否支持查詢緩存
show variables like 'have_query_cache';
2.查看數據庫是否開啟了緩存
show variables like 'query_cache_type';
OFF或者0：查詢緩存關閉
ON或者1：查詢緩存打開，符合條件的SQL就會緩存，顯式指定SQL_NO_CACHE不予緩存
DEMAND或者2：查詢緩存按需進行，顯式指定SQL_CACHE的select語句才混緩存
找到mysql的配置文件然後加上，就開啟了，重啟MySQL服務生效
query_cache_type=1
通過查詢語句的執行時間，可以判斷是否進入了緩存
3.查詢緩存佔用的大小 下面單位是位元組 算出來大約是1M
show variables like 'query_cache_size';
4.查看查詢緩存的狀態變量
show status like 'Qcache%';
Qcache_free_blocks：可用的內存快的個數
Qcache_free_memory：可用的內存空間
Qcache_hits：查詢緩存的命中次數
Qcache_inserts：添加到查詢緩存的次數 如果MySQL中沒有操作過緩存這兩個數據為0
Qcache_lowmem_prunes：內存不足查詢緩存中刪除的次數
Qcache_not_cached：非緩存查詢的次數
Qcache_queries_in_cache：查詢緩存中註冊的查詢數
Qcache_total_blocks ： 查詢緩存中的塊總數
5.查詢緩存select選項
可以在select查詢的時候選擇使用緩存或者不使用緩存
SQL_CATCH：如果查詢緩存已經打開，則緩存查詢結果
SQL_NO_CATCH：服務器不使用查詢緩存 ，不緩存
select SQL_CATCH * from Student;
6.查詢結果緩存失效
（1）SQL語句不相同，這裡就不再演示了
（2）查詢語句結果不固定，比如查詢當前時間等等 select Now()
（3）沒有使用表的語句
（4）查詢Mysql系統表的時候
（5）在存儲的函數，或者存儲過程中的查詢
（6）表被更改，增刪改都可以讓表發生更改，刪除表。就會將表的緩存刪除變為無效
6.MySQL內存管理以及優化
原則
（1）要盡量將多的內存分配給MySQL
（2）MyISAM存儲引擎依賴操作系統本身的IO，因此如果有MyISAM表就要預留更多的內存給操作系統
（3）排序區和緩存區的內存要合理分配，因為過大的話，並發連接較高的時候，就會導致物理內存消耗
MyISAM內存優化
存儲引擎使用key_buffer 緩存索引塊，加速myisam索引的讀寫速度。對於myisam表的數據塊，mysql沒有特別的緩存機制，完全依賴於操作系統的IO緩存。
key_buffer_size
key_buffer_size決定MyISAM索引塊緩存區的大小，直接影響到MyISAM表的存取效率。可以在MySQL參數文件中設置key_buffer_size的值，對於一般MyISAM數據庫，建議至少將1/4可用內存分配給key_buffer_size。
在MySQL配置文件中做如下配置：
key_buffer_size=512M
read_buffer_size
如果需要經常順序掃描myisam表，可以通過增大read_buffer_size的值來改善性能。但需要注意的是read_buffer_size是每個session獨佔的，如果默認值設置太大，就會造成內存浪費。
read_rnd_buffer_size
對於需要做排序的myisam表的查詢，如帶有order by子句的sql，適當增加 read_rnd_buffer_size 的值，可以改善此類的sql性能。但需要注意的是 read_rnd_buffer_size 是每個session獨佔的，如果默認值設置太大，就會造成內存浪費。
InnoDB內存優化
InnoDB用內存區做緩存，用來緩存數據塊和索引塊，因此就要給InnoDB分配過多的緩存，在MySQL配置文件中
innodb_buffer_pool_size
該變量決定了 innodb 存儲引擎表數據和索引數據的最大緩存區大小。在保證操作系統及其他程序有足夠內存可用的情況下，innodb_buffer_pool_size 的值越大，緩存命中率越高，訪問InnoDB表需要的磁盤I/O 就越少，性能也就越高。
innodb_buffer_pool_size=512M
innodb_log_buffer_size
決定了innodb重做日誌緩存的大小，對於可能產生大量更新記錄的大事務，增加innodb_log_buffer_size的大小，可以避免innodb在事務提交前就執行不必要的日誌寫入磁盤操作。
innodb_log_buffer_size=10M
MySQL並發參數的調整
max_connections
採用max_connections 控制允許連接到MySQL數據庫的最大數量，默認值是 151。如果狀態變量 connection_errors_max_connections 不為零，並且一直增長，則說明不斷有連接請求因數據庫連接數已達到允許最大值而失敗，這是可以考慮增大max_connections 的值。
Mysql 最大可支持的連接數，取決於很多因素，包括給定操作系統平台的線程庫的質量、內存大小、每個連接的負荷、CPU的處理速度，期望的響應時間等。在Linux 平台下，性能好的服務器，支持 500-1000 個連接不是難事，需要根據服務器性能進行評估設定。
show variables like 'max_connection';
back_log
back_log 參數控制MySQL監聽TCP端口時設置的積壓請求棧大小。如果MySql的連接數達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源，將會報錯。5.6.6 版本之前默認值為 50 ， 之後的版本默認為 50 + （max_connections / 5）， 但最大不超過900。
如果需要數據庫在較短的時間內處理大量連接請求， 可以考慮適當增大back_log 的值。
show variables like 'back_log';
table_open_cache
該參數用來控制所有SQL語句執行線程可打開表緩存的數量， 而在執行SQL語句時，每一個SQL執行線程至少要打開 1 個表緩存。該參數的值應該根據設置的最大連接數 max_connections 以及每個連接執行關聯查詢中涉及的表的最大數量來設定 ：
max_connections x N ；
show variables like 'table_open_cache';
thread_cache_size
為了加快連接數據庫的速度，MySQL 會緩存一定數量的客戶服務線程以備重用，通過參數 thread_cache_size 可控制 MySQL 緩存客戶服務線程的數量。
show variables like 'thread_cache_size';
innodb_lock_wait_timeout
該參數是用來設置InnoDB 事務等待行鎖的時間，默認值是50ms ， 可以根據需要進行動態設置。對於需要快速反饋的業務系統來說，可以將行鎖的等待時間調小，以避免事務長時間掛起； 對於後台運行的批量處理程序來說， 可以將行鎖的等待時間調大， 以避免發生大的回滾操作。
show variables like 'innodb_lock_wait_timeout';
MySQL鎖
鎖（避免資源爭搶）
鎖是計算機協調多個進程或線程並發訪問某一資源的機制。在數據庫中，除傳統的計算資源（如 CPU、RAM、I/O 等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖衝突也是影響數據庫並發訪問性能的一個重要因素。
MySQL鎖
-
表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高,並發度最低。
-
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,並發度也最高。
-
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般。
-
讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
-
寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。
| 存儲引擎 | 表級鎖 | 行級鎖 | 頁面鎖 |
|---|---|---|---|
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
僅從鎖的角度來說：表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如
Web 應用；而行級鎖則更適合於有大量按索引條件並發更新少量不同數據，同時又有並發
查詢的應用，如一些在線事務處理（OLTP）系統。
MyISAM表鎖
（1）在MyISAM引擎中查詢會自動的加上讀鎖，增刪改自動加上寫鎖。不需要人為加上
（2）讀鎖：給一個表接了一個讀鎖之後，其他客戶端也可以查詢到，因為讀鎖可以共同進行不會影響操作，必須要解鎖之後 unlock tables，才能進行增刪改，不然直接進行修改表，會進去等待狀態。
（3）寫鎖：在進行寫鎖之後，在當前客戶端可以增刪改查，但是在其他客戶端執行操作就會進入等待狀態，需要解鎖才可以進行下一步操作。
可見
（1）對 MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
（2）對 MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；
MyISAM 的讀寫鎖調度是寫優先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因。因為寫鎖後，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞。
查看鎖的爭用指令
show open tables;
In_user : 表當前被查詢使用的次數。如果該數為零，則表是打開的，但是當前沒有被使用。
Name_locked：表名稱是否被鎖定。名稱鎖定用於取消表或對錶進行重命名等操作。
show status like 'table_locks%';
Table_locks_immediate ： 指的是能夠立即獲得表級鎖的次數，每立即獲取鎖，值加1。
Table_locks_waited ： 指的是不能立即獲取表級鎖而需要等待的次數，每等待一次，該值加1，此值高說明存在着較為嚴重的表級鎖爭用情況。
InnoDB行鎖
InnoDB 與 MyISAM 的最大不同有兩點：
（1）支持事務（TRANSACTION）；
（2）採用了行級鎖。
事務是由一組 SQL 語句組成的邏輯處理單元，事務具有以下 4 個屬性，通常簡稱為事務的ACID 屬性。
- 原子性（Atomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
- 一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味着所有相關的數據規則都必須應用於事務的修改，以保持數據的完整性；事務結束時，所有的內部數據結構（如 B 樹索引或雙向鏈表）也都必須是正確的。
- 隔離性（Isolation）：數據庫系統提供一定的隔離機制，保證事務在不受外部並發操作影響的「獨立」環境執行。這意味着事務處理過程中的中間狀態對外部是不可見的，反之亦然。
- 持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。
並發事務處理帶來的問題
-
更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題最後的更新覆蓋了由其他事務所做的更新。例如，兩個編輯人員製作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文檔。最後保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同一文件，則可避免此問題。
-
臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的數據就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」數據，並據此做進一步的處理，就會產生未提交的數據依賴關係。這種現象被形象地叫做”臟讀”。
-
不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做「不可重複讀」。
-
幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀」。
事務隔離級別
數據庫實現事務隔離的方式，基本上可分為以下兩種。
（1）在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
（2）不用加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照（Snapshot)，並用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度來看，好象是數據庫可以提供同一數據的多個版本，因此，這種技術叫做數據多版本並發控制（MultiVersion Concurrency Control，簡稱 MVCC 或MCC），也經常稱為多版本數據庫。
MySQL默認隔離級別
show variables like 'tx_isolation';
InnoDB行鎖演示
先關閉InnoDB表的事務自動提交，這種情況下只有提交事務才會生效，然後打開終端A更新一條id=3的數據，現在數據更新成功但是不提交（commit）,再打開終端B，寫上同樣的Sql語句，語句執行就處於等待狀態，這時候提交A終端數據，B終端正常更新了。InnoDB行級鎖只鎖定一行，更新兩條不同的行，正常可以獲取到鎖，只要進行增刪改的時候InnoDb就會為行加上排它鎖，別的事務無法執行SQL語句。關閉自動提交，每次相當於開啟了一個事務，其他地方提交的東西看不到，因為事務具有隔離級別，再提交一次才可以看到。
行鎖升級表鎖的原因
在關閉事務自動提交之後，更新查詢字段為varchar沒有加單引號導致索引會失效，索引失效就會導致行鎖升級為表鎖。
間隙鎖的危害
當我們用範圍條件，而不是使用相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據進行加鎖； 對於鍵值在條件範圍內但並不存在的記錄，叫做 “間隙（GAP）” ， InnoDB也會對這個 “間隙” 加鎖，這種鎖機制就是所謂的 間隙鎖（Next-Key鎖） 。
比如在數據中更新分數大於80的學生，因為數據中有的學生沒有成績，為空也會被加上鎖。這時候沒有提交數據，將無法更新分數大於80的學生。
查看鎖的爭用情況
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 當前正在等待鎖定的數量
Innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度
Innodb_row_lock_time_avg:每次等待所花平均時長
Innodb_row_lock_time_max:從系統啟動到現在等待最長的一次所花的時間
Innodb_row_lock_waits: 系統啟動後到現在總共等待的次數
當等待的次數很高，而且每次等待的時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果着手制定優化計劃。
總結
InnoDB存儲引擎由於實現了 行級鎖定，雖然在鎖定機制的實現方面帶來了性能損耗可能表現比表鎖更高一些但是在橫踢並發處理方面能力要遠遠優於MyISAM的表鎖，當系統並發量較高的時候，InnoDB的增提性能和MyISAM相比就會有比較明顯的優勢。但是當InnoDB行級鎖處理不當的時候，可能讓InnoDB整體性能表現比更差。
建議：
- 儘可能讓所有數據檢索都能通過索引來完成，避免無索引行鎖升級為表鎖。
- 合理設計索引，儘可能縮小鎖的範圍
- 儘可能減少索引失效的條件，及索引範圍，避免間隙鎖。
- 儘可能控制事務大小，減少鎖定資源和時間長度。
- 儘可能使用低級別事務隔離
