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整體效能表現比更差。
建議：
- 儘可能讓所有資料檢索都能通過索引來完成，避免無索引行鎖升級為表鎖。
- 合理設計索引，儘可能縮小鎖的範圍
- 儘可能減少索引失效的條件，及索引範圍，避免間隙鎖。
- 儘可能控制事務大小，減少鎖定資源和時間長度。
- 儘可能使用低階別事務隔離