mysql基礎面試題

什么是mysql

Mysql是一種關系型數據庫，默認端口為3306。

什么是事務

事務是邏輯上的一組操作，要么都執行，要么都不執行。

• 原子性：
  事務是最小的執行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用；
• 一致性：
  執行事務前后，數據保持一致，多個事務對同一個數據讀取的結果是相同的；
• 隔離性：
  并發訪問數據庫時，一個用戶的事務不被其他事務所干擾，各并發事務之間數據庫是獨立的；
• 永久性：
  一個事務被提交之后。它對數據庫中數據的改變是持久的，即使數據庫發生故障也不應該對其有任何影響。

并發事務帶來的影響

• 臟讀：
  當一個事務正在訪問數據并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時另外一個事務也訪問了這個數據。
• 丟失修改：
  指在一個事務讀取一個數據時，另外一個事務也訪問了該數據，那么在第一個事務中修改了這個數據后，第二個事務也修改了這個數據。這樣第一個事務內的修改結果就被丟失，因此稱為丟失修改。
• 不可重復讀：
  一個事務內多次讀同一數據。在這個事務還沒有結束時，另一個事務也訪問該數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改導致第一個事務兩次讀取的數據可能不太一樣。
• 幻讀：
  一個事務（T1）讀取了幾行數據，接著另一個并發事務（T2）插入了一些數據時。在隨后的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄。

事物的隔離級別

• 1：讀取未提交：
  最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重復讀。
• 2：讀取已提交：
  允許讀取并發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生。
• 3：可重復讀：
  對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發生。
• 4：可串行化：
  最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀。

索引

Mysql 使用的索引主要由BTree索引和哈希索引， 對于哈希索引來說，底層使用的數據結構就是哈希表。 因此在絕大多數需求為單挑記錄查詢時，可以選擇哈希索引，查詢性能最快；其余大部分場景，選擇BTree 索引。
Mysql 中的BTree 索引使用的時 B樹 中的 B+Tree, 但對于主要的兩種存儲引擎的實現方式是不同的。

大表優化

當Mysql 單表記錄數過大時，數據庫的CRUD 性能會明顯下降，一些常見的優化措施如下：

• 限定數據的范圍
  務必禁止不帶任何限制數據范圍條件的查詢語句。比如，我們在查詢訂單歷史數據時，可以控制在一個月
• 讀寫分離
  經典的數據庫拆分方案，主庫負責寫，從庫負責讀
• 垂直分區
  根據數據庫里面的數據表的相關性進行拆分，例如用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個單獨的表，甚至放入單獨的庫做分庫。
  優點：
  可以使得列數據變小，在查詢時減少讀取的Block 數，減少 I/O 次數，此外，垂直分區可以簡化表的結構，易于維護。
  缺點：
  主鍵會出現冗余，需要管理冗余列，并會引起 Join 操作，可以通過在應用層進行join 來解決。此外，垂直分區也會讓事務變得更復雜。

什么是池化思想，什么是數據庫連接池，為什么要用數據庫連接池

數據庫連接本質就是一個 socket 的連接。可以把數據庫連接池是看做是維護的數據庫連接的緩存，以便將來需要對數據庫的請求時可以重用這些連接。在連接池中，創建連接后，將其放置在池中，并再次使用它，因此不必建立新的連接。如果使用了所有連接，則會建立一個新連接并將其添加到池中。連接池還減少了用戶必須等待建立與數據庫的連接的時間。

?鎖，表鎖；樂觀鎖，悲觀鎖

• ?鎖：
  數據庫表中某??被鎖住。
• 表鎖：
  整個數據庫表被鎖住。
• 樂觀鎖：
  顧名思義，就是很樂觀，每次去拿數據的時候都認為別?不會修改，具體實現是給表增加?個版本號的字段，在執?update操作時?較該版本號是否與當前數據庫中版本號?致，如?致，更新數據，反之拒絕。
• 悲觀鎖：
  顧名思義，就是很悲觀，每次去拿數據的時候都認為別?會修改。讀數據的時候會上鎖，直到update完成才釋放鎖，使?悲觀鎖要注意不要鎖住整個表。

MySQL主備同步的基本原理

mysql主備復制實現分成三個步驟：

• master將改變記錄到?進制?志(binary log)中（這些記錄叫做?進制?志事件，binary log events，可以通過show binlog events進?查看）；
• slave將master的binary log events拷?到它的中繼?志(relay log)；
• slave重做中繼?志中的事件，將改變反映它??的數據。
  

SQL什么情況下不會使?索引（不包含，不等于，函數）

• select * 可能導致不?索引；
• 空值會導致不?索引，因為hashset不能存空值；
• 索引列有函數運算，不?索引，可以在索引列建??個函數的索引。
• 隱式轉換可能導致不?索引；
• 表的數據庫?或者需要選擇?部分數據，不?索引；
• !=或者<>可能導致不?索引；
• 字符型的索引列會導致優化器認為需要掃描索引?部分數據且聚簇因?很?，最終導致棄?索引掃描?改?全表掃描?式
• like ‘%liu’ 百分號在前不?索引；
• not in, not exist不?索引；

—般在什么字段上建索引（過濾數據最多的字段）

• 表的主鍵、外鍵必須有索引；
• 數據量超過300的表應該有索引；
• 經常與其他表進?連接的表，在連接字段上應該建?索引；
• 經常出現在Where?句中的字段，特別是?表的字段，應該建?索引；
• 索引應該建在選擇性?的字段上；
• 索引應該建在?字段上，對于?的?本字段甚?超?字段，不要建索引；

mysql調優

• explain select語句；
• 當只要?條數據時使?limit 1；
• 為搜索字段建索引；
• 避免select *；
• 字段盡量使?not null；
• 垂直分割；
• 拆分?的delete和insert語句：delete和insert會鎖表；
• 分表分庫分區。

innodb如何實現mysql的事務？

事務進?過程中，每次sql語句執?，都會記錄undo log和redo log，然后更新數據形成臟?，然后redo log按照時間或者空間等條件進?落盤，undo log和臟?按照checkpoint進?落盤，落盤后相應的redo log就可以刪除了。此時，事務還未COMMIT，如果發?崩潰，則?先檢查checkpoint記錄，使?相應的redo log進?數據和undo log的恢復，然后查看undo log的狀態發現事務尚未提交，然后就使?undo log進?事務回滾。事務執?COMMIT操作時，會將本事務相關的所有redo log都進?落盤，只有所有redo log落盤成功，才算COMMIT成功。然后內存中的數據臟?繼續按照checkpoint進?落盤。如果此時發?了崩潰，則只使?redo log恢復數據。

為什么索引采用B+Tree

• 什么是索引:
  索引的存在是為了加速對表中數據行的檢索，所以他是分散的一種數據結構。
  索引的數據結構到底是什么樣的呢？
  索引里存儲的是數據的磁盤地址，這樣查找數據庫的時候，并不需要全表搜索，而只需要在索引里先找到數據對應的地址，直接到數據庫里取就好了。
• 為什么是b+tree呢：
  為什么不是二叉查找樹？
  二叉查找樹是不平衡的，如果我們要查找22這個數就幾乎要遍歷所有的數據，這個結構就看起來很不對頭。肯定不能作為索引的數據結構。（7654321）
  為什么平衡二叉樹不行？
  因為一個節點只有兩個子節點，而且每個節點里面只有一條數據，那一百萬條數據的索引，那效率就會很低。
  為什么不是B-tree?

mysql如何處理慢查詢

1：定位慢查詢

• mysql慢查詢日志的開啟

#開啟慢查詢日志記錄
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)#查詢時間超過0.1秒的sql語句會被記錄
mysql> set global long_query_time=0.1;
Query OK, 0 rows affected (0.03 sec)#記錄慢查詢日志的文件地址
mysql> set global slow_query_log_file="/var/lib/mysql/localhost-slow.log";
Query OK, 0 rows affected (0.04 sec)#記錄沒有使用索引的查詢
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)show variables like 'slow_query_log';show variables like 'long_query_time';show variables like 'slow_query_log_file';show variables like 'log_queries_not_using_indexes';

2：分析慢查詢sql語句

用 EXPLAIN 查看SQL執行計劃。

3：字段解析

• type列

• 連接類型。一個好的sql語句至少要達到range級別。杜絕出現all級別

• key列
  使用到的索引名。如果沒有選擇索引，值是NULL。可以采取強制索引方式

• key_len列
  索引長度

• rows列
  掃描行數。該值是個預估值

• extra列
  詳細說明。注意常見的不太友好的值有：Using filesort, Using temporary
  

4：通過sql分析，找到問題所在，

比如這個sql是否全表掃描了，索引是否正常生效（前提是表建了索引），堆sql進行一個優化。

一條SQL在MySQL中是如何執行的

1：mysql的基本架構：

• 連接器：
  身份認證和權限相關(登錄 MySQL 的時候)。
• 查詢緩存:
  執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本后移除，因為這個功能不太實用）。
• 分析器:
  沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。
• 優化器：
  按照 MySQL 認為最優的方案去執行。
• 執行器:
  執行語句，然后從存儲引擎返回數據。
  

2：Mysql主要分為server層和存儲引擎層：

• Server 層：

主要包括連接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日志模塊 binglog 日志模塊。

連接器

• 連接器負責跟客戶端建立連接、獲取權限、維持和管理連接。
  在完成經典的 TCP 握手后，連接器就要開始認證你的身份，
  如果用戶名密碼認證通過，連接器會到權限表里面查出你擁有的權限。之后，這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限。
  這就意味著，一個用戶成功建立連接后，即使你用管理員賬號對這個用戶的權限做了修改，也不會影響已經存在連接的權限。修改完成后，只有再新建的連接才會使用新的權限設置。用戶的權限表在系統表空間的mysql的user表中。

• 連接完成后，如果你沒有后續的動作，這個連接就處于空閑狀態，你可以在 show processlist 命令中看到它。文本中這個圖是 show processlist 的結果，其中的 Command 列顯示為“Sleep”的這一行，就表示現在系統里面有一個空閑連接。
  

• 客戶端如果長時間不發送command到Server端，連接器就會自動將它斷開。這個時間是由參數 wait_timeout 控制的，默認值
  是 8 小時。
  

• mysql常用命令

mysql>show databases; 顯示所有數據庫
mysql>use dbname； 打開數據庫：
mysql>show tables; 顯示數據庫mysql中所有的表；
mysql>describe user; 顯示表mysql數據庫中user表的列信息）；

查詢緩存

• 連接建立完成后，你就可以執行 select 語句了。執行邏輯就會來到第二步：查詢緩存。
  MySQL 拿到一個查詢請求后，會先到查詢緩存看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以key-value 對的形式，被直接緩存在內存中。key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個緩存中找到 key，那么這個 value 就會被直接返回給客戶端。

  如果語句不在查詢緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被存入查詢緩存中。你可以看到，如果查詢命中緩存，MySQL 不需要執行后面的復雜操作，就可以直接返回結果，這個效率會很高。

• 大多數情況查詢緩存就是個雞肋，為什么呢？
  因為查詢緩存往往弊大于利。查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對于更新壓力大的數據庫來說，查詢緩存的命中率會非常低。
  一般建議大家在靜態表里使用查詢緩存，什么叫靜態表呢？就是一般我們極少更新的表。比如，一個系統配置表、字典表，那這張表上的查詢才適合使用查詢緩存。好在 MySQL 也提供了這種“按需使用”的方式。你可以將my.cnf參數query_cache_type 設置成 DEMAND。

my.cnf
#query_cache_type有3個值 0代表關閉查詢緩存OFF，1代表開啟ON，2（DEMAND）代表當sql語句中有SQL_CACHE關鍵詞時才緩存
query_cache_type=2

• 如何指定sql語句使用查詢緩存
  這樣對于默認的 SQL 語句都不使用查詢緩存。而對于你確定要使用查詢緩存的語句，可以用 SQL_CACHE 顯式指定，像下面這個語句一樣：

select SQL_CACHE * from test where ID=5；

這樣這個sql查詢的數據就會進入緩存。

• 查看當前mysql實例是否開啟緩存機制

 show global variables like "%query_cache_type%";

• 監控查詢緩存的命中率:

show status like'%Qcache%'; //查看運行的緩存信息

Qcache_free_blocks:
表示查詢緩存中目前還有多少剩余的blocks，如果該值顯示較大，則說明查詢緩存中的內存碎片過多了，可能在一定的時間進行整理。
Qcache_free_memory:
查詢緩存的內存大小，通過這個參數可以很清晰的知道當前系統的查詢內存是否夠用，是多了，還是不夠用，DBA可以根據實際情況做出調整。
Qcache_hits:
表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢緩存的效果。數字越大，緩存效果越理想。
Qcache_inserts:
表示多少次未命中然后插入，意思是新來的SQL請求在緩存中未找到，不得不執行查詢處理，執行查詢處理后把結果insert到查詢緩存中。這樣的情況的次數，次數越多，表示查詢緩存應用到的比較少，效果也就不理想。當然系統剛啟動后，查詢緩存是空的，這很正常。
Qcache_lowmem_prunes:
該參數記錄有多少條查詢因為內存不足而被移除出查詢緩存。通過這個值，用戶可以適當的調整緩存大小。
Qcache_not_cached:
表示因為query_cache_type的設置而沒有被緩存的查詢數量。
Qcache_queries_in_cache:
當前緩存中緩存的查詢數量。
Qcache_total_blocks:
當前緩存的block數量。

mysql8.0已經移除了查詢緩存功能

分析器

• 如果沒有命中查詢緩存，就要開始真正執行語句了。首先，MySQL 需要知道你要做什么，因此需要對 SQL 語句做解析。
  MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字符串“T”識別成“表名 T”，把字符串“ID”識別成“列 ID”。
  做完了這些識別以后，就要做“語法分析”。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。如果你的語句不對，就會收到“You have an error in your SQL syntax”的錯誤提醒，比如下面這個語句 from 寫成了"rom"。

• 詞法分析器原理
  詞法分析器分成6個主要步驟完成對sql語句的分析
  1、詞法分析
  2、語法分析
  3、語義分析
  4、構造執行樹
  5、生成執行計劃
  6、計劃的執行
  

• SQL語句的分析分為詞法分析與語法分析，
  mysql的詞法分析由MySQLLex[MySQL自己實現的]完成，語法分析由Bison生成。
  那么除了Bison外，Java當中也有開源的詞法結構分析工具例如Antlr4，ANTLR從語法生成一個解析器，可以構建和遍歷解析樹，可以在IDEA工具當中安裝插件：antlr v4 grammar plugin。插件使用詳見課程。

• 過bison語法分析之后，會生成一個這樣的語法樹
  

優化器

• 按照 MySQL 認為最優的方案去執行。
  優化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。
• 比如你執行下面這樣的語句，這個語句是執行兩個表的 join：

select * from test1 join test2 using(ID) where test1.name=yangguo and test2.name=xiaolongnv;

既可以先從表 test1 里面取出 name=yangguo的記錄的 ID 值，再根據 ID 值關聯到表 test2，再判斷 test2 里面 name的值是否等于 yangguo。
也可以先從表 test2 里面取出 name=xiaolongnv 的記錄的 ID 值，再根據 ID 值關聯到 test1，再判斷 test1 里面 name的值是否等于 yangguo。
這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案。優化器階段完成后，這個語句的執行方案就確定下來了，然后進入執行器階段。

執行器

> select * from test where id=1;

• 開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的權限，如果沒有，就會返回沒有權限的錯誤，如下所示 (在工程實現上，如果命中查詢緩存，會在查詢緩存返回結果的時候，做權限驗證。查詢也會在優化器之前調用 precheck 驗證權限)。
• 如果有權限，就打開表繼續執行。打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的接口。
• 比如我們這個例子中的表 test 中，ID 字段沒有索引，那么執行器的執行流程是這樣的：
  1：調用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中；
  2：調用引擎接口取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。
  3：執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

bin-log相關知識

刪庫是不需要跑路的，因為我們的SQL執行時，會將sql語句的執行邏輯記錄在我們的bin-log當中，

什么是bin-log呢？

binlog是Server層實現的二進制日志,他會記錄我們的cud操作。Binlog有以下幾個特點：
1、Binlog在MySQL的Server層實現（引擎共用）
2、Binlog為邏輯日志,記錄的是一條語句的原始邏輯
3、Binlog不限大小,追加寫入,不會覆蓋以前的日志
如果，我們誤刪了數據庫,可以使用binlog進行歸檔!要使用binlog歸檔，首先我們得記錄binlog，因此需要先開啟MySQL的binlog功能。

開啟MySQL的binlog功能。

• 配置my.cnf

配置開啟binlog
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin
注意5.7以及更高版本需要配置本項：server‐id=123454（自定義,保證唯一性）;
#binlog格式，有3種statement,row,mixed（建議使用row或者mixd）;statement會記錄sql的執行邏輯，row會記錄影響的執行的結果，mixed兩者結合。
binlog‐format=ROW
#表示每1次執行寫入就與硬盤同步，會影響性能，為0時表示，事務提交時mysql不做刷盤操作，由系統決定
sync‐binlog=1

• binlog命令

mysql> show variables like ‘%log_bin%’; 查看bin‐log是否開啟
flush logs; 會多一個最新的bin‐log日志
show master status; 查看最后一個bin‐log日志的相關信息
reset master; 清空所有的bin‐log日志

• 查看binlog內容
  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 查看binlog內容
  binlog里的內容不具備可讀性，所以需要我們自己去判斷恢復的邏輯點位，怎么觀察呢？看重點信息，比如begin,commit這種關鍵詞信息，只要在binlog當中看到了，你就可以理解為begin-commit之間的信息是一個完整的事務邏輯,然后再根據位置position判斷恢復即可。binlog內容如下：
  

數據歸檔操作

從bin‐log恢復數據

• 恢復全部數據
  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001|mysql ‐uroot ‐p tuling(數據庫名)

• 恢復指定位置數據
  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position=“408” ‐‐stop‐position=“731”/usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(數據庫)

• 恢復指定時間段數據
  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001‐‐stop‐date= “2018‐03‐02 12:00:00” ‐‐start‐date= “2019‐03‐02 11:55:00”|mysql ‐uroot ‐p test(數據庫)

• 存儲引擎-Store層：

主要負責數據的存儲和讀取，采用可以替換的插件式架構，支持 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有自有的日志模塊 redolog 模塊。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當做默認存儲引擎了。也就是說如果我們在create table時不指定表的存儲引擎類型,默認會給你設置存儲引擎為InnoDB。

3：查詢語句的執行流程如下：

• 權限校驗（如果命中緩存）—》查詢緩存—》分析器—》優化器—》權限校驗—》執行器—》引擎

4：更新語句執行流程如下：

• 分析器----》權限校驗----》執行器—》引擎—redo log(prepare 狀態—》binlog—》redo log(commit狀態)

深入理解MVCC與BufferPool緩存機

MVCC多版本并發控制機制

• Mysql在可重復讀隔離級別下如何保證事務較高的隔離性，我們上節課給大家演示過，同樣的sql查詢語句在一個事務里多次執行查詢結果相同，就算其它事務對數據有修改也不會影響當前事務sql語句的查詢結果。
  這個隔離性就是靠MVCC(Multi-Version ConcurrencyControl)機制來保證的，對一行數據的讀和寫兩個操作默認是不會通過加鎖互斥來保證隔離性，避免了頻繁加鎖互斥，而在串行化隔離級別為了保證較高的隔離性是通過將所有操作加鎖互斥來實現的。
  Mysql在讀已提交和可重復讀隔離級別下都實現了MVCC機制。

undo日志版本鏈與read view機制詳解

undo日志版本鏈

undo日志版本鏈是指一行數據被多個事務依次修改過后，在每個事務修改完后，Mysql會保留修改前的數據undo回滾日志，并且用兩個隱藏字段trx_id和roll_pointer把這些undo日志串聯起來形成一個歷史記錄版本鏈。

一致性視圖read-view

在可重復讀隔離級別，當事務開啟，執行任何查詢sql時會生成當前事務的一致性視圖read-view，該視圖在事務結束之前都不會變化(如果是讀已提交隔離級別在每次執行查詢sql時都會重新生成)，
這個視圖由執行查詢時所有未提交事務id數組（數組里最小的id為min_id）和已創建的最大事務id（max_id）組成，事務里的任何sql查詢結果需要從對應版本鏈里的最新數據開始逐條跟read-view做比對從而得到最終的快照結果。

版本鏈比對規則：

• 如果 row 的 trx_id 落在綠色部分( trx_id<min_id )，表示這個版本是已提交的事務生成的，這個數據是可見的；
• 如果 row 的 trx_id 落在紅色部分( trx_id>max_id )，表示這個版本是由將來啟動的事務生成的，是不可見的(若row 的 trx_id 就是當前自己的事務是可見的）；
• 如果 row 的 trx_id 落在黃色部分(min_id <=trx_id<= max_id)，那就包括兩種情況

1. 若 row 的 trx_id 在視圖數組中，表示這個版本是由還沒提交的事務生成的，不可見(若 row 的 trx_id就是當前自己的事務是可見的)；

2. 若 row 的 trx_id 不在視圖數組中，表示這個版本是已經提交了的事務生成的，可見。

• 對于刪除的情況可以認為是update的特殊情況，會將版本鏈上最新的數據復制一份，然后將trx_id修改成刪除操作的trx_id，同時在該條記錄的頭信息（record header）里的（deleted_flag）標記位寫上true，來表示當前記錄已經被刪除，在查詢時按照上面的規則查到對應的記錄如果delete_flag標記位為true，意味著記錄已被刪除，則不返回數據。

注意：
begin/start transaction 命令并不是一個事務的起點，在執行到它們之后的第一個修改操作InnoDB表的語句，事務才真正啟動，才會向mysql申請事務id，mysql內部是嚴格按照事務的啟動順序來分配事務id的。

總結：

MVCC機制的實現就是通過read-view機制與undo版本鏈比對機制，使得不同的事務會根據數據版本鏈對比規則讀取同一條數據在版本鏈上的不同版本數據。

Innodb引擎SQL執行的BufferPool緩存機制

為什么Mysql不能直接更新磁盤上的數據而且設置這么一套復雜的機制來執行SQL了？

• 因為來一個請求就直接對磁盤文件進行隨機讀寫，然后更新磁盤文件里的數據性能可能相當差。
• 因為磁盤隨機讀寫的性能是非常差的，所以直接更新磁盤文件是不能讓數據庫抗住很高并發的。Mysql這套機制看起來復雜，但它可以保證每個更新請求都是更新內存BufferPool，然后順序寫日志文件，同時還能保證各種異常情況下的數據一致性。
• 更新內存的性能是極高的，然后順序寫磁盤上的日志文件的性能也是非常高的，要遠高于隨機讀寫磁盤文件。正是通過這套機制，才能讓我們的MySQL數據庫在較高配置的機器上每秒可以抗下幾千的讀寫請求。

Explain詳解與索引最佳實踐

深入理解Mysql索引底層數據結構與算法

索引的本質

索引是幫助mysql高效獲取數據的排好序的數據結構（Hash或者B+Tree）

• 如上圖，如果沒有索引，那我們需要一行一行的在磁盤上去查詢數據，如果數據量特別大，就會導致做很多次的I/O,效率特別的低。所以索引就特別重要。
• 做一次磁盤I/O所花費的時間是很昂貴的。
• 數據在磁盤上存放的地址是并不連續的

各種數據結構作為索引的底層數據結構的問題

推薦一個外國的數據結構在線演示網站

二叉樹

可以看出來，就比如我們以自增id作為索引的時候，在比較極端的情況下，這不是二叉樹了，變成一個鏈表了，和沒有索引一樣，也需要一條一條的去遍歷查詢，也需要對磁盤做很多的I/O（索引和數據都是存在磁盤上的），所以二叉樹pass。

紅黑樹(二叉平衡樹)

如上圖紅黑樹看上去不錯，但其實它的本質也是二叉樹，只是加入了變色和自旋，在數據量比較大的情況下，樹的高度不可控，也需要I/O很多次。所以紅黑樹pass。

B-Tree

如上圖，索引的結點是存儲在磁盤空間上的，不管是二叉樹還是紅黑樹，一個節點是只存了一個索引的，而B-Tree呢就針對一個節點分配更大的空間，就可以在這個節點上面存儲更多的索引元素（包含了索引和數據–指索引所在行的磁盤文件的地址），從而也能更好的控制樹的高度，盡可能的做少的I/O次數。而且在此節點的磁盤空間中，對所有從左到有依次進行排序，便于更好的查找。

B+Tree

當節點中的數據大于我們規定值的15/16的時候，樹的結構就會發生變化。

B+Tree與B-Tree相比的優勢

B+Tree葉節點只存儲索引，不存儲具體的數據（Data指索引所在行的磁盤文件的地址），數據只會存在葉子結點上，而且從左到右依次排序，支持雙向查找。B+Tree會存儲一些冗余的處于中間位置的索引，這樣就可以在同樣大的磁盤空間上存儲更多的索引，提高查詢效率，而且，每一行，索引都會從左到右依次遞增。但是B-Tree葉節點即存儲了索引也存儲了起對應的內存地址，就會導致一個數據頁存儲的數據變少，當數據量過大的時候，樹的高度也是很大的，所以最終選擇了B+Tree。B+Tree相鄰的葉子結點之間是有相互指向的（會存相鄰葉子節點之間的內存地址），所以在范圍查詢的時候，效率也會更高。
除了以上優點，B+Tree還有很多優點，比如，每個父節點的值都小于等于他右子節點的值，大于他左子節點的值，這樣一種左開右閉原則也能很好的提升查詢的效率。

根據索引查詢數據的一次完整過程：

比如就上圖我們要查找30這個索引，
1：從根節點出發，根節點是常駐內存的，首先將根節點加載到RAM內存中，在內存中對半查詢查找，30在15-56之間，就將中間這塊數據（其實就是下一級節點的磁盤文件地址-指針）取出加載到內存。30在20-49之間，就將這中間的節點取出加載到內存，然后就能從內存中定位到索引為30的元素，這元素下面就含有這個索引縮影的數據在磁盤中的內存地址。

mysql針對根節點默認是可以存儲16kb的數據的，

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'

那為什么不把整張表的所有的索引元素都放在一個節點上面去？

如果數據庫表很多，而且索引也不少，可能會把內存撐爆，而且同時把那么多數據加載到內存中去再去查到，速率也不會很快。而且一次磁盤I/O也加載不了這么多數據。而且根節點是常駐內存的。

可以按照上圖就算樹高只有三層進行計算

數據庫表主鍵索引，用bigint(8字節)，（索引+下一級節點的磁盤文件地址-指針（mysql默認為6字節）），相加就14字節，16k/14~~1000多，第二層一樣，第三層有葉子結點，含有data（不同的存儲引擎存儲結構不一樣，后面再講），就算一行數據有1kb，那么三層就可以存儲1000x1000x16=16000000。而且mysql會提前將非葉子結點中的數據加載到內存中，相當于只需要做一次i/o就能定位到索引數據。

MyISAM與InnoDB

MyISAM與InnoDB是形容數據庫表而不是數據庫。

MyISAM存儲引擎底層數據文件存儲結構

一個表主要由三個文件組成：見下圖

MyISAM執行sql索引查找數據原理

sql執行的時候，會在表索引文件（B+Tree的數據結構存儲索引）中，通過B+Tree的數據結構找到我們的索引，葉子結點下面存儲的是存在表數據文件中的數據的地址，就可能達到對應的數據的內存地址，然后通過這個地址去表數據文件中定位獲取到對應的數據。

InnoDB存儲引擎底層數據文件存儲結構

一個表由兩個文件組成，見下圖

• frm:表結構文件。
• ibd:表索引以及數據文件。
  ibd文件中，葉子結點下面存儲的是索引所在行的其他關聯的數據，就不用通過地址再去其他磁盤上查找了。
  

InnoDB執行sql索引查找數據原理

去表索引以及數據文件中查詢到具體的索引后，就可以直接獲取到具體的數據，不用再去其他文件查詢了。
對于普通的索引，葉子節點存儲的是主鍵id，然后更具主鍵再去主鍵索引中查詢，查找具體數據。如果我們沒有建主鍵，那么mysql就回去數據庫中查找一列中數據唯一的字段作為主鍵，如果沒有這樣的數據，會自己生成rowid作為主鍵id。從節約空間以及通過比較大小快速查找索引角度考慮，主鍵建議使用自增的整型而不用其他的比如uuid。

聚集索引與非聚集索引的區別

• Myisam索引文件與數據文件是分離的就稱為非聚集索引，InnoDB索引與數據在同一個文件，稱為聚集索引。
• 聚集索引葉子結點包含了完整的數據記錄。
• 稀疏索引其實就是一種非聚集索引。
• 相對而言聚集索引不用跨文件去查找數據，相對而言會更快一點。

為什么建議InnoDB表必須建主鍵?

在創建表的時候，ibd文件是按照B+Tree的數據結構來組織索引的，建立主鍵后，mysql就可以使用我們建立的主鍵來組織索引，如果我們我們沒有建立自己的主鍵。那么mysql就會在我們的表中選擇一個沒有重復的數據的字段作為主鍵，如果沒有這樣的字段，mysql會自己生成一列隱藏的數據rowid來作為我們的主鍵，如果我們自己建立了主鍵，mysql就不用做后面這些工作了，可以提示mysql的性能，mysql的性能是很重要的，資源也是很寶貴的。所以我們最好自己建立個主鍵。

為什么推薦使用整型的自增主鍵？

整型自增首先他是有序的，而且相對于uuid或者字符串，在生成B+Tree數據結構的時候，因為在一個數據頁，從左到右，主鍵是逐漸變大的。更好比較大小，而且整型占用的空間相對而言要小一點。

為什么要求自增

• 在討論這個問題之前先來看看hash索引。
  
  hash會對存入來的索引數據進行一次hash運算，并且把這個值放入hash桶中，存在hash沖突的情況。
  hash計算出索引的hash值后，一般情況下只需要進行一次磁盤I/O就能定位到數據，Hash相對于B+Tree效率很高，不出現Hash沖突的情況下，查詢某一條數據時間復雜度為O（1），但是不支持范圍查找，所以用的比較少。相反B+Tree葉子節點中是從左到右一次遞增的，而且是個雙向鏈表，不管是查詢大于還是小于都很快，相鄰的節點直接存有對方的磁盤文件地址，查找起來也很快。

• 回到為什么采用自增作為主鍵
  B+Tree是排好序的。B+Tree葉子節點中是從左到右一次遞增的，而且是個雙向鏈表，不管是查詢大于還是小于都很快，相鄰的節點直接存有對方的磁盤文件地址，查找起來也很快。
  如果非自增，在插入數據的時候可能會存在要向已經填滿的節點里面強插數據，這樣就可能會導致節點分裂，甚至還需要往父節點寫入數據等等來平衡B+Tree，效率不如自增。

為什么非主鍵索引結構葉子節點存儲的是主鍵值？(一致性和節省存儲空間)

• InnoDB表只會存在一個聚集索引，非主鍵索引（二級索引）葉子結點只會存放聚集索引的值，不會存在具體的數據，這樣可以節約存儲空間，如果我一張表有很多的非主鍵索引，我每個B+Tree的葉子結點下面都像主鍵索引那樣存放所有的具體的數據，那樣會很占用空間內存。
• 還可以保持一致性，比如在插入數據的時候，非主鍵索引像主鍵索引那樣存放所有的具體的數據，我們需要確保所有的索引插入的數據正確才算完事，很比較麻煩，但是我們如果只存主鍵值，就可以通過主鍵值再去查詢其他的數據。
• 每加一個索引就會在ibd文件中維護一個B+Tree的數據結構。

聯合索引

也是B+Tree的數據結構，

聯合索引首先在構建B+Tree的數據結構的時候，排序的時候，會從左到右一次比較字段的大小去進行排序，第一個字段開始排序，如果第一個字段一樣就用第二個字段進行比較排序，依次類推。

在查詢的時候，遵循最左前綴原則，我們查詢的時候，需要用聯合索引的第一個字段來開始查，不能直接跳過這個字段去使用到其他的索引。假如這三個字段是聯合索引，在我們寫sql的時候，如果我們沒有加name這個索引條件字段，那么這個聯合索引是不會起效的，不會走索引的，因為單單從后面幾個字段我們是沒辦法判斷索引在B+Tree中的排序的。因為我們的聯合索引是排好了序的，而且是用第一個字段來一次排序，如果第一個字段不相等，那么就不用以后面的字段來排序，如果不用聯合索引中的第一個字段，我們是沒辦法判斷索引在B+Tree中的排序的。

深入理解Mysql鎖與事務隔離級別

概述

我們的數據庫一般都會并發執行多個事務，多個事務可能會并發的對相同的一批數據進行增刪改查操作，可能就會導致我們說的臟寫、臟讀、不可重復讀、幻讀這些問題。

這些問題的本質都是數據庫的多事務并發問題，為了解決多事務并發問題，數據庫設計了事務隔離機制、鎖機制、MVCC多版本并發控制隔離機制，用一整套機制來解決多事務并發問題。接下來，我們會深入講解這些機制，讓大家徹底理解數據庫內部的執行原理。

事務及其ACID屬性

• 原子性(Atomicity) ：
  事務是一個原子操作單元,其對數據的修改,要么全都執行,要么全都不執行。（操作層面）
• 一致性(Consistent) ：
  在事務開始和完成時,數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用于事務的修改,以保持數據的完整性（數據層面）
• 隔離性(Isolation) ：
  數據庫系統提供一定的隔離機制,保證事務在不受外部并發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的,反之亦然。
• 持久性(Durable) ：
  事務完成之后,它對于數據的修改是永久性的,即使出現系統故障也能夠保持。

并發事務處理帶來的問題

更新丟失(Lost Update)或臟寫

當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題–最后的更新覆蓋了由其他事務所做的更新。

臟讀（Dirty Reads）

一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數據就處于不一致的狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”數據，并據此作進一步的處理，就會產生未提交的數據依賴關系。這種現象被形象的叫做“臟讀”。

一句話：事務A讀取到了事務B已經修改但尚未提交的數據，還在這個數據基礎上做了操作。此時，如果B事務回滾，A讀取的數據無效，不符合一致性要求。

不可重讀（Non-Repeatable Reads）

一個事務在讀取某些數據后的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做“不可重復讀”。

一句話：事務A內部的相同查詢語句在不同時刻讀出的結果不一致，不符合隔離性

幻讀（Phantom Reads）

一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀”。

一句話：事務A讀取到了事務B提交的新增數據，不符合隔離性

事務隔離級別

“臟讀”、“不可重復讀”和“幻讀”,其實都是數據庫讀一致性問題,必須由數據庫提供一定的事務隔離機制來解決。

數據庫的事務隔離越嚴格,并發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度上“串行化”進行,這顯然與“并發”是矛盾的。

同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如許多應用對“不可重復讀"和“幻讀”并不敏感,可能更關心數據并發訪問的能力。

常看當前數據庫的事務隔離級別:

 show variables like 'tx_isolation';

設置事務隔離級別：

set tx_isolation='REPEATABLE-READ';

Mysql默認的事務隔離級別是可重復讀，用Spring開發程序時，如果不設置隔離級別默認用Mysql設置的隔離級別，如果Spring設置了就用已經設置的隔離級別。

鎖詳解

• 鎖是計算機協調多個進程或線程并發訪問某一資源的機制
• 在數據庫中，除了傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供需要用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。

鎖分類

• 從性能上分為樂觀鎖(用版本對比來實現)和悲觀鎖
• 從對數據庫操作的類型分，分為讀鎖和寫鎖(都屬于悲觀鎖)

讀鎖（共享鎖，S鎖(Shared)）：針對同一份數據，多個讀操作可以同時進行而不會互相影響
寫鎖（排它鎖，X鎖(eXclusive)）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖

• 從對數據操作的粒度分，分為表鎖和行鎖

表鎖

每次操作鎖住整張表。開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低；一般用在整表數據遷移的場景。

基本操作

 CREATE TABLE `mylock` (`id` INT (11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR (20) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE = MyISAM DEFAULT CHARSET = utf8;INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd')

手動增加表鎖

lock table 表名稱 read(write),表名稱2 read(write);

查看表上加過的鎖

show open tables;

刪除表鎖

unlock tables;

表鎖案例

• 加讀鎖

lock table mylock read

當前session和其他session都可以讀該表
當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待

• 加寫鎖

lock table mylock write

當前session對該表的增刪改查都沒有問題，其他session對該表的所有操作被阻塞

案例結論

• 對MyISAM表的讀操作(加讀鎖)
  不會阻寒其他進程對同一表的讀請求,但會阻賽對同一表的寫請求。只有當讀鎖釋放后,才會執行其它進程的寫操作。
• 對MylSAM表的寫操作(加寫鎖)
  會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放后,才會執行其它進程的讀寫操作

行鎖

每次操作鎖住一行數據。開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度最高。

InnoDB與MYISAM的最大不同有兩點：

• InnoDB支持事務（TRANSACTION）
• InnoDB支持行級鎖

一個session開啟事務更新不提交，另一個session更新同一條記錄會阻塞，更新不同記錄不會阻塞

總結：

• MyISAM在執行查詢語句SELECT前，會自動給涉及的所有表加讀鎖,在執行update、insert、delete操作會自動給涉及的表加寫鎖。
• InnoDB在執行查詢語句SELECT時(非串行隔離級別)，不會加鎖。但是update、insert、delete操作會加行鎖。
  簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把讀和寫都阻塞。

行鎖與事務隔離級別案例分析

建表

 CREATE TABLE `account` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`balance` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

讀未提交：

1：打開一個客戶端A，并設置當前事務模式為read uncommitted（未提交讀），查詢表account的初始值：

set tx_isolation='read-uncommitted';

2：在客戶端A的事務提交之前，打開另一個客戶端B，更新表account：

3：這時，雖然客戶端B的事務還沒提交，但是客戶端A就可以查詢到B已經更新的數據：

4：一旦客戶端B的事務因為某種原因回滾，所有的操作都將會被撤銷，那客戶端A查詢到的數據其實就是臟數據：

5：在客戶端A執行更新語句update account set balance = balance - 50 where id =1，lilei的balance沒有變成350，居然是400，是不是很奇怪，數據不一致啊，如果你這么想就太天真 了，在應用程序中，我們會用400-50=350，并不知道其他會話回滾了，要想解決這個問題可以采用讀已提交的隔離級別。

讀已提交

set tx_isolation='read-committed';

（1）打開一個客戶端A，并設置當前事務模式為read committed（未提交讀），查詢表account的所有記錄：

（2）在客戶端A的事務提交之前，打開另一個客戶端B，更新表account：

（3）這時，客戶端B的事務還沒提交，客戶端A不能查詢到B已經更新的數據，解決了臟讀問題：

（4）客戶端B的事務提交

（5）客戶端A執行與上一步相同的查詢，結果 與上一步不一致，即產生了不可重復讀的問題

可重復讀

（1）打開一個客戶端A，并設置當前事務模式為repeatable read，查詢表account的所有記錄

set tx_isolation='repeatable-read';

（2）在客戶端A的事務提交之前，打開另一個客戶端B，更新表account并提交

（3）在客戶端A查詢表account的所有記錄，與步驟（1）查詢結果一致，沒有出現不可重復讀的問題

（4）在客戶端A，接著執行update account set balance = balance - 50 where id = 1，balance沒有變成400-50=350，lilei的balance值用的是步驟2中的350來算的，所以是300，數據的一致性倒是沒有被破壞。可重復讀的隔離級別下使用了MVCC(multi-version concurrency control)機制，select操作不會更新版本號，是快照讀（歷史版本）；insert、update和delete會更新版本號，是當前讀（當前版本）。

（5）重新打開客戶端B，插入一條新數據后提交

（6）在客戶端A查詢表account的所有記錄，沒有查出新增數據，所以沒有出現幻讀；

（7)驗證幻讀
在客戶端A執行update account set balance=888 where id = 4;能更新成功，再次查詢能查到客戶端B新增的數據。

串行化

（1）打開一個客戶端A，并設置當前事務模式為serializable，查詢表account的初始值：

set tx_isolation='serializable';

（2）打開一個客戶端B，并設置當前事務模式為serializable，更新相同的id為1的記錄會被阻塞等待，更新id為2的記錄可以成功，說明在串行模式下innodb的查詢也會被加上行鎖。

如果客戶端A執行的是一個范圍查詢，那么該范圍內的所有行包括每行記錄所在的間隙區間范圍(就算該行數據還未被插入也會加鎖，這種是間隙鎖)都會被加鎖。此時如果客戶端B在該范圍內插入數據都會被阻塞，所以就避免了幻讀。
這種隔離級別并發性極低，開發中很少會用到。

間隙鎖(Gap Lock)

• 間隙鎖，鎖的就是兩個值之間的空隙。Mysql默認級別是repeatable-read，有辦法解決幻讀問題嗎？
• 間隙鎖在某些情況下可以解決幻讀問題。

假設account表里數據如下：

• 那么間隙就有 id 為 (3,10)，(10,20)，(20,正無窮) 這三個區間
• 在Session_1下面執行 update account set name = ‘zhuge’ where id > 8 and id <18;，則其他Session沒法在這個范圍所包含的所有行記錄(包括間隙行記錄)以及行記錄所在的間隙里插入或修改任何數據，即id在(3,20]區間都無法修改數據，注意最后那個20也是包含在內的。
• 間隙鎖是在可重復讀隔離級別下才會生效

無索引行鎖會升級為表鎖

• 鎖主要是加在索引上，如果對非索引字段更新，行鎖可能會變表鎖
• session1 執行：update account set balance = 800 where name = ‘lilei’;
• session2 對該表任一行操作都會阻塞住。
• InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。并且該索引不能失效，否則都會從行鎖升級為表鎖。
• 鎖定某一行還可以用lock in share mode(共享鎖) 和for update(排它鎖)，例如：select * fromtest_innodb_lock where a = 2 for update;這樣其他session只能讀這行數據，修改則會被阻塞，直到鎖定 行的session提交

結論

Innodb存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一下，但是在整體并發處理能力方面要遠遠優于MYISAM的表級鎖定的。當系統并發量高的時候，Innodb的整體性能和MYISAM相比就會有比較明顯的優勢了。

但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現不僅不能比MYISAM高，甚至可能會更差。

行鎖分析

通過檢查InnoDB_row_lock狀態變量來分析系統上的行鎖的爭奪情況

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:
  系統啟動后到現在總共等待的次數

對于這5個狀態變量，比較重要的主要是

• Innodb_row_lock_time_avg （等待平均時長）
• Innodb_row_lock_waits （等待總次數）
• Innodb_row_lock_time（等待總時長）

尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什么會有如此多的等待，然后根據分析結果著手制定優化計劃。

查看INFORMATION_SCHEMA系統庫鎖相關數據表

‐‐ 查看事務
select * from INFORMATION_SCHEMA.INNODB_TRX;
‐‐ 查看鎖
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
‐‐ 查看鎖等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;‐‐ 釋放鎖，trx_mysql_thread_id可以從INNODB_TRX表里查看到
kill trx_mysql_thread_id‐‐ 查看鎖等待詳細信息
show engine innodb status\G;

死鎖

set tx_isolation=‘repeatable-read’

• Session_1執行：select * from account where id=1 for update;
• Session_2執行：select * from account where id=2 for update;
• Session_1執行：select * from account where id=2 for update;
• Session_2執行：select * from account where id=1 for update;
  查看近期死鎖日志信息：show engine innodb status\G;

大多數情況mysql可以自動檢測死鎖并回滾產生死鎖的那個事務，但是有些情況mysql沒法自動檢測死鎖。

鎖優化建議

• 盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖
• 合理設計索引，盡量縮小鎖的范圍
• 盡可能減少檢索條件范圍，避免間隙鎖
• 盡量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql盡量放在事務最后執行
• 盡可能低級別事務隔離