innodbtrx表數據被鎖

① 一文詳解-MySQL 事務和鎖

當多個用戶訪問同一份數據時，一個用戶在更改數據的過程中，可能有其他用戶同時發起更改請求，為保證資料庫記錄的更新從一個一致性狀態變為另外一個一致性狀態，使用事務處理是非常閉友必要的，事務具有以下四個特性：

MySQL 提供了多種事務型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務。為了支持事務，InnoDB 存儲引擎引入了與事務處理相關的 REDO 日誌和 UNDO 日誌，同時事務依賴於 MySQL 提供的鎖機制

事務執行時需要將執行的事務日誌寫入日誌文件，對應的文件為 REDO 日誌。當每條 SQL 進行數據更新操作時，首先將 REDO 日誌寫進日誌緩沖區。當客戶端執行 COMMIT 命令提交時，日誌緩沖區的內容將被刷新到磁碟，日誌緩沖區的刷新方式或者時間間隔可以通過參數 innodb_flush_log_at_trx_commit 控制

REDO 日誌對應磁碟上的 ib_logifleN 文件，該文件默認為 5MB，建議設置為 512MB，以便容納較大的事務。MySQL 崩潰恢復時會重新執行 REDO 日誌的記錄，恢復最新數據，保證已提交事務的持久性

與 REDO 日誌相反，UNDO 日誌主要用於事務異常時的數據回滾，具體內容就是記錄數據被修改前的信息到 UNDO 緩沖區，然後在合適的時間將內容刷新到磁碟

假如由於系統錯誤或者 rollback 操作而導致事務回滾，可以根據 undo 日誌回滾到沒修改前的狀態，保證未提交事務的原子性

與 REDO 日誌不同的是，磁碟上不存在單獨的 UNDO 日誌文件，所有的 UNDO 日誌均存在表空間對應的 .ibd 數據文件中，即使 MySQL 服務啟動了獨立表空間

在 MySQL 中，可以使用 BEGIN 開始事務，使用 COMMIT 結束事務，中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務

MySQL 定義了四種隔離級別，指定事務中哪些數據改變其他事務可見、哪些數據該表其他事務不可見。低級別的隔離級別可以支持更高的並發處理，同時佔用的系統資源更少

InnoDB 系統級事務隔離級別可以使用以下語句設置：

查看系統級事務隔離級別：

InnoDB 會話級事務隔離級別可以使用以下語句設置：

查看會話級事務隔離級別：

在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。讀取未提交的數據稱為臟讀（Dirty Read），即困蔽是：首先開啟 A 和 B 兩個事務，在 B 事務更新但未提交之前，A 事務讀取到了更新後的數據，但由於 B 事務回滾，導致 A 事務出現了臟讀現象

所有事務只能看見已經提交事務所做的改變，此級別可以解決臟讀，但也會導致不可重復讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務，A事務讀取了 B 事務的數據，在 B 事務更新並提交後，A 事務又讀取到了更新後的數據，此時就出現了同一 A 事務中的查詢出現了不同的查詢結果

MySQL 默認的事務隔離級別，能確保同一事務的多個實例在並發讀取數據時看到同樣的數據行，理論上會導致一個問題，幻讀（Phontom Read）。例如，第一個事務對一個表中的數據做了修改，這種修改會涉及表中的全部數據行，同時第二個事務也修改這個表中的數據，這次的修改是向表中插入一行新數據，此時就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行

InnoDB 通過多版本並汪態州發控制機制（MVCC）解決了該問題：InnoDB 通過為每個數據行增加兩個隱含值的方式來實現，這兩個隱含值記錄了行的創建時間、過期時間以及每一行存儲時間發生時的系統版本號，每個查詢根據事務的版本號來查詢結果

通過強制事務排序，使其不可能相互沖突，從而解決幻讀問題。簡而言之，就是在每個讀的數據行上加上共享鎖實現，這個級別會導致大量的超時現象和鎖競爭，一般不推薦使用

為了解決資料庫並發控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對並發操作進行控制，因此產生了鎖

共享鎖的粒度是行或者元組（多個行），一個事務獲取了共享鎖以後，可以對鎖定范圍內的數據執行讀操作

排他鎖的粒度與共享鎖相同，一個事務獲取排他鎖以後，可以對鎖定范圍內的數據執行寫操作

有兩個事務 A 和 B，如果事務 A 獲取了一個元組的共享鎖，事務 B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務 A 釋放共享鎖之後。如果事務 A 獲取了一個元組的排他鎖，事務 B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之後

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務有意對數據上共享鎖或者排他鎖。有意表示事務想執行操作但還沒真正執行

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小，同時允許的並發量也是最小。MyISAM 存儲引擎使用該鎖機制。當要寫入數據時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執行時使用的也是表鎖

行鎖可以支持最大的並發，InnoDB 存儲引擎使用該鎖機制。如果要支持並發讀/寫，建議採用 InnoDB 存儲引擎

② MySQL資料庫表被鎖、解鎖，刪除事務

在程序員的職業生涯中，總會遇到資料庫表被鎖的情況，前些天就又撞見一次。由於業務突發需求，各個部門都在批量操作、導出數據，而資料庫又未做讀寫分離，結果就是：資料庫的某張表被鎖了！

用戶反饋系統部分功能無法使用，緊急排查，定位是資料庫表被鎖，然後進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。

用戶反饋某功能頁面報502錯誤，於是第一時間看服務是否正常，資料庫是否正常。在控制台看到資料庫CPU飆升，堆積大量未提交事務，部分事務已經阻塞了很長時間，基本定位是資料庫層出現問題了。

查看阻塞事務列表，發現其中有鎖表現象，本想利用控制台直接結束掉阻塞的事務，但控制台賬號許可權有限，於是通過客戶端登錄對應賬號將鎖表事務kill掉，才避免了情況惡化。

下面就聊聊，如果當突然面對類似的情況，我們該如何緊急響應？

想像一個場景，當然也是軟體工程師職業生涯中會遇到的一種場景：原本運行正常的程序，某一天突然資料庫的表被鎖了，業務無法正常運轉，那麼我們該如何快速定位是哪個事務鎖了表，如何結束對應的事物？

首先最簡單粗暴的方式就是：重啟MySQL。對的，網管解決問題的神器——「重啟」。至於後果如何，你能不能跑了，要你自己三思而後行了！

重啟是可以解決表被鎖的問題的，但針對線上業務很顯然不太具有可行性。

下面來看看不用跑路的解決方案：

遇到資料庫阻塞問題，首先要查詢一下表是否在使用。

如果查詢結果為空，那麼說明表沒在使用，說明不是鎖表的問題。

如果查詢結果不為空，比如出現如下結果：

則說明表（test）正在被使用，此時需要進一步排查。

查看資料庫當前的進程，看看是否有慢SQL或被阻塞的線程。

執行命令：

該命令只顯示當前用戶正在運行的線程，當然，如果是root用戶是能看到所有的。

在上述實慧明踐中，阿里雲控制台之所以能夠查看到所有的線程，猜測應該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的資料庫賬號，無法操作另外一個用戶的線程。

如果情況伍碧腔緊急，此步驟可以跳過，主要用來查看核對：

如果情況緊急，此步驟可以跳過，主要用來查看核對：

看事務表INNODB_TRX中是否有正在鎖定的事務線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務一直沒有commit或者rollback，而是卡住了，需要手動kill掉。

搜索的結果中，如果在事務表發現了很多任務，最好都kill掉。

執行kill命令：

對應的線程都執行完kill命令之後，後續事務便可正常處理。

針對緊急情況，通常也會直接操作第一、第二、第六步。

這里再補充一些MySQL鎖相關的知識點：資料庫鎖設計的初衷是處理並發問題，作為多用戶共享的資源，當出現並發訪問的時候，資料庫需要合理地腔衫控制資源的訪問規則，而鎖就是用來實現這些訪問規則的重要數據結構。

根據加鎖的范圍，MySQL裡面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（metadata lock，MDL)。

表鎖是在Server層實現的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現，而對於InnoDB來說，一般會採用行級鎖。畢竟鎖住整張表影響范圍太大了。

另外一個表級鎖是MDL（metadata lock），用於並發情況下維護數據的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。

常見的一種鎖表場景就是有事務操作處於：Waiting for table metadata lock狀態。

MySQL在進行alter table等DDL操作時，有時會出現Waiting for table metadata lock的等待場景。

一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態，後續對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現了鎖等待隊列，就會造成災難性的後果。

通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨占鎖，會進行等待。

通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨占鎖。

處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然後kill掉，讓其回滾。

通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。很可能是因為在一個顯式的事務中，對表進行了一個失敗的操作（比如查詢了一個不存在的欄位），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。

處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。

總之，alter table的語句是很危險的（核心是未提交事務或者長事務導致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。

如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。

關於MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發生，當然這需要一定經驗的支撐。但更重要的是，如果發現鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。

③ MySQL innodb引擎深入講解

表空間(ibd文件),一個MySQL實例可以對應多個表空間，用於存儲記錄，索引等數據。

段，分為數據段、索引段、回滾段，innodb是索引組織表，數據段就是B+Tree的葉子節點，索引段為非葉子節點，段用來管理多個區。

區，表空間的單元結構，每個區的大小為1M,默認情況下，innodb存儲引擎頁大小為16K，即一個區中一共有64個連續的頁。

頁，是innodb存儲引擎磁碟管理的最小單元，每個頁的大小為16K，為了保證頁的連續性，innodb存儲引擎每次從磁碟申請4~5個區。

行，innodb存儲引擎數據是按行進行存儲的。Trx_id 最後一次事務操作的id、roll_pointer滾動指針。

i nnodb的內存結構 ，由Buffer Pool、Change Buffer和Log Buffer組成。

Buffer Pool : 緩沖池是主內存中的一個區域，裡面可以緩存磁碟上經常操作的真實數據，在執行增刪改查操作時，先操作緩沖池中的數據(若緩沖池么有數據，則從磁碟載入並緩存)，然後再以一定頻率刷新磁碟，從而減少磁碟IO，加快處理速度。

緩沖池以page頁為單位，底層採用鏈表數據結構管理page，根據狀態，將page分為三種類型:

1、free page 即空閑page，未被使用。

2、clean page 被使用page，數據沒有被修改過。

3、dirty page 臟頁，被使用page，數據被修改過，這個page當中的數據和磁碟當中的數據 不一致。說得簡單點就是緩沖池中的數據改了，磁碟中的沒改，因為還沒刷寫到磁碟。

Change Buffer ：更改緩沖區(針對於非唯一二級索引頁)，在執行DML語句時，如果這些數據page沒有在Buffer Pool中，不會直接操作磁碟，而會將數據變更存在更改緩沖區Change Buffer中，在未來數據被讀取時。再將數據合並恢復到Buffer Pool中，再將合並後的數據刷新到磁碟中。

二級索引通常是非唯一的，並且以相對隨機的順序插入二級索引頁，同樣，刪除和更新可能會影響索引樹中不相鄰的二級索引頁。如果每一次都操作磁碟，會造成大量磁碟IO，有了Change Buffer之後，我們可以在緩沖池中進行合並處理，減少磁碟IO。

Adaptive Hash Index： 自適應hash索引，用於優化對Buffer Pool數據的查詢，InnoDB存儲引擎會監控對表上各索引頁的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引。無需人工干預，系統根據情況自動完成。

參數：innodb_adaptive_hash_index

Log Buffer: 日誌緩沖區，用來保存要寫入到磁碟中的log日誌數據(redo log、undo log)，默認大小為16M，日誌緩沖區的日誌會定期刷新到磁碟中，如果需要更新，插入或刪除許多行的事務，增加日誌緩沖區的大小可以節省磁碟IO。

參數: innodb_log_buffer_size 緩沖區大小

innodb_flush_log_at_trx_commit 日誌刷新到磁碟時機

innodb_flush_log_at_trx_commit=1 表示日誌在每次事務提交時寫入並刷新到磁碟

2 表示日誌在每次事務提交後寫入，並每秒刷新到磁碟一次

0 表示每秒將日誌寫入並刷新到磁碟一次。

InnoDB 的磁碟結構，由系統表空間(ibdata1)，獨立表空間(*.ibd),通用表空間，撤銷表空間(undo tablespaces), 臨時表空間(Temporary Tablespaces), 雙寫緩沖區(Doublewrite Buffer files), 重做日誌(Redo Log).

系統表空間(ibdata1)： 系統表空間是更改緩沖區的存儲區域，如果表是在系統表空間而不是每個表文件或者通用表空間中創建的，它也可能包含表和索引數據。

參數為: innodb_data_file_path

獨立表空間(*.ibd): 每個表的文件表空間包含單個innodb表的數據和索引，並存儲在文件系 統上的單個數據文件中。 參數: innodb_file_per_table

通用表空間: 需要通過create tablespace 語法創建，創建表時 可以指定該表空間。

create tablespace xxx add datafile 'file_name' engine=engine_name

create table table_name .... tablespace xxx

撤銷表空間(undo tablespaces): MySQL實例在初始化時會自動創建兩個默認的undo表空間(初始大小16K，undo_001,undo_002)，用於存儲undo log 日誌

臨時表空間(Temporary Tablespaces)： innodb使用會話臨時表空和全局表空間，存儲用 戶創建的臨時表等數據。

雙寫緩沖區(Doublewrite Buffer files)： innodb引擎將數據頁從Buffer Pool刷新到磁碟前，先將數據頁寫入緩沖區文件中，便於系統異常時恢復數據。

重做日誌(Redo Log)： 是用來實現事務的持久性，該日誌文件由兩部分組成，重做日誌緩沖區(redo log buffer)以及重做日誌文件(redo log)，前者是在內存中，後者在磁碟中，當事務提交之後會把修改信息都會存儲到該日誌中，用於在刷新臟頁到磁碟時，發送錯誤時，進行數據恢復使用。以循環方式寫入重做日誌文件，涉及兩個文件ib_logfile0,ib_logfile1。

那內存結構中的數據是如何刷新到磁碟中的? 在MySQL中有4個線程負責刷新日誌到磁碟。

1、Master Thread， mysql核心後台線程，負責調度其它線程，還負責將緩沖池中的數據異 步刷新到磁碟中，保持數據的一致性，還包括臟頁的刷新，合並插入緩沖、undo頁的回 收。

2、IO Thread，在innodb存儲引擎中大量使用了AIO來處理IO請求，這樣可以極大地提高數 據庫的性能，而IO Thead主要負責這些IO請求的回調。

4個讀線程 Read thread負責讀操作

4個寫線程write thread負責寫操作

1個Log thread線程 負責將日誌緩沖區刷新到磁碟

1個insert buffer線程 負責將寫入緩沖區內容刷新到磁碟

3、Purge Thread，主要用於回收事務已經提交了的undo log，在事務提交之後，undo log 可能不用了，就用它來回收。

4、Page Cleaner Thread， 協助Master Thread 刷新臟頁到磁碟的線程，它可以減輕主線程 的壓力，減少阻塞。

事務就是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要麼同時成功，要麼同時失效。

事務的4大特性分為:

如何保證事務的4大特性，原子性，一致性和持久性是由innodb存儲引擎底層的兩份日誌來保證的，分別是redo log和undo log。對於隔離性是由鎖機制和MVCC(多版本並發控制)來實現的。

redo log，稱為重做日誌，記錄的是事務提交時數據頁的物理修改，是用來實現事務的持久性。該日誌文件由兩部分組成: 重做日誌緩沖redo log buffer及重做日誌文件redo log file，前者是在內存中，後者是在磁碟中，當事務提交之後會把所有修改信息都存到該日誌文件中，用於在刷新臟頁到磁碟，發送錯誤時，進行數據的恢復使用，從而保證事務的持久性。

具體的操作流程是：

1、客戶端發起事務操作，包含多條DML語句。首先去innodb中的buffer pool中的數據頁去查找有沒有我們要更新的這些數據，如果沒有則通過後台線程從磁碟中載入到buffer pool對應的數據頁中，然後就可以在緩沖池中進行數據操作了。

2、此時緩沖池中的數據頁發生了變更，還沒刷寫到磁碟，這個數據頁稱為臟頁。臟頁不是實時刷新到磁碟的，而是根據你配置的刷寫策略進行刷寫到磁碟的（innodb_flush_log_at_trx_commit，0,1,2三個值）。如果臟頁在往磁碟刷新的時候出現了故障，會丟失數據，導致事務的持久性得不到保證。為了避免這種現象，當對緩沖池中的數據進行增刪改操作時，會把增刪改記錄到redo log buffer當中，redo log buffer會把數據頁的物理變更持久化到磁碟文件中(ib_logfile0/ib_logfile1)。如果臟頁刷新失敗，就可以通過這兩個日誌文件進行恢復。

undo log，它是用來解決事務的原子性的，也稱為回滾日誌。用於記錄數據被修改前的信息，作用包括:提供回滾和MVCC多版本並發控制。

undo log和redo log的記錄物理日誌不一樣，它是邏輯日誌。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，當update一條記錄時，它記錄一條對應相反的update記錄，當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容並進行回滾。

undo log銷毀: undo log 在事務執行時產生，事務提交時，並不會立即刪除undo log，因為這些日子可能用於MVCC。

undo log存儲: undo log 採用段的方式進行管理和記錄，存放在前面介紹的rollback segment回滾段中，內部包含1024個undo log segment。

mvcc(multi-Version Concurrency Control),多版本並發控制，指維護一個數據的多個版本，使得讀寫操作沒有沖突，快照讀為MySQL實現MVCC提供了一個非阻塞讀功能，MVCC的具體實現，還需要依賴於資料庫記錄中的三個隱式欄位，undo log日誌、readView。

read committed 每次select 都生成一個快照讀

repeatable read 開啟事務後第一個select語句才是快照讀的地方

serializable 快照讀會退化為當前讀。

mvcc的實現原理

DB_TRX_ID: 最近修改事務ID，記錄插入這條記錄或最後一次修改該記錄的事務ID

DB_ROLL_PTR: 回滾指針，指向這條記錄的上一個版本，用於配合undo log，指向上一個 版本

DB_ROW_ID: 隱藏主鍵，如果表結構沒有指定主鍵，將會生成該隱藏欄位。

m_ids當前活躍的事務ID集合

min_trx_id: 最小活躍事務id

max_trx_id: 預分配事務ID，當前最大事務id+1，因為事務id是自增的

creator_trx_id: ReadView創建者的事務ID

版本鏈數據訪問規則:

trx_id: 表示當前的事務ID

1、trx_id == creator_trx_id? 可以訪問讀版本-->成立的話,說明數據是當前這個事務更改的

2、trx_id 成立,說明數據已經提交了。

3、trx_id>max_trx_id？不可用訪問讀版本-> 成立的話，說明該事務是在ReadView生成後才開啟的。

4、min_trx_id

④ mysql資料庫鎖表如何解鎖

當前運行的所有事務
select * from information_schema.innodb_trx
當前出現的鎖
select * from information_schema.innodb_locks
鎖等待的對應關系
select * from information_schema.innodb_lock_waits
通過找到線程id號，進行kill

通過 select * from information_schema.innodb_trx 查詢 trx_mysql_thread_id然後執行 kill 線程ID

⑤ mysql 中InnoDB和MyISAM的區別分析小結

InnoDB和MyISAM是在使用MySQL最常用的兩個表類型，各有優缺點，視具體應用而定。
下面是已知的兩者之間的差別，僅供參考。

innodb
InnoDB 給 MySQL 提供了具有事務(commit)、回滾(rollback)和崩潰修復能力
(crash recovery capabilities)的事務安全
(transaction-safe (ACID compliant))型表。InnoDB 提供了行鎖
(locking on row level)，提供與 Oracle 類型一致的不加鎖讀取(non-locking
read in SELECTs)。這些特性均提高了多用戶並發操作的性能表現。
在InnoDB表中不需要擴大鎖定(lock escalation)，因為 InnoDB 的列鎖定(row level
locks)適宜非常小的空間。InnoDB 是 MySQL 上第一個提供外鍵約束(FOREIGN KEY
constraints)的表引擎。

InnoDB 的設計目標是處理大容量資料庫系統，它的 CPU 利用率是其它基於磁碟的關系資料庫
引擎所不能比的。在技術上，InnoDB 是一套放在 MySQL 後台的完整資料庫系統，
InnoDB 在主內存中建立其專用的緩沖池用於高速緩沖數據和索引。 InnoDB 把數據和索引存
放在表空間里，可能包含多個文件，這與其它的不一樣，舉例來說，在 MyISAM 中，表被存放
在單獨的文件中。InnoDB 表的大小隻受限於操作系統的文件大小，一般為 2 GB。
InnoDB所有的表都保存在同一個數據文件 ibdata1 中（也可能是多個文件，或者是獨立的
表空間文件）,相對來說比較不好備份，免費的方案可以是拷貝數據文件、備份 binlog，
或者用 mysqlmp。

MyISAM
MyISAM 是MySQL預設存貯引擎 .

每張MyISAM 表被存放在三個文件 。frm 文件存放表格定義。 數據文件是MYD (MYData) 。
索引文件是MYI (MYIndex) 引伸。

因為MyISAM相對簡單所以在效率上要優於InnoDB..小型應用使用MyISAM是不錯的選擇.

MyISAM表是保存成文件的形式,在跨平台的數據轉移中使用MyISAM存儲會省去不少的麻煩

MyISAM是ISAM表的新版本，有如下擴展：

·二進制層次的可移植性。
·NULL列索引。
·對變長行比ISAM表有更少的碎片。
·支持大文件。
·更好的索引壓縮。
·更好的鍵嗎統計分布。
·更好和更快的auto_increment處理。

以下是一些細節和具體實現的差別：

◆1.InnoDB不支持FULLTEXT類型的索引。
◆2.InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，
InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。
注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。
◆3.對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM
表中，可以和其他欄位一起建立聯合索引。
◆4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。
◆5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成
MyISAM表，導入數據後再改成InnoDB表，但是對於使用的額外的InnoDB特性（例如外鍵）的
表不適用。

◆MyISAM類型的二進制數據文件可以在不同操作系統中遷移。

另外，InnoDB表的行鎖也不是絕對的，假如在執行一個SQL語句時MySQL不能確定要掃描的
范圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like 「%aaa%」

再另外，使用兩種的選擇：如果你的數據執行大量的INSERT或UPDATE，出於性能方面的考慮，
應該使用InnoDB表。如果執行大量的SELECT，MyISAM是更好的選擇。若需要使用事務處理，
但是原來的數據表使用的是myisam，就需要改為bdb或者innodb，這樣基於myisam的程序，
將類型改為innodb後，其程序不用改動……

綜上所述，任何一種表都不是萬能的，只有恰當的針對業務類型來選擇合適的表類型，才能
最大的發揮MySQL的性能優勢。

MyISAM和InnoDB優化：
key_buffer_size - 這對MyISAM表來說非常重要。如果只是使用MyISAM表，可以把它設置
為可用內存的 30-40%。合理的值取決於索引大小、數據量以及負載 -- 記住，MyISAM表會
使用操作系統的緩存來緩存數據，因此需要留出部分內存給它們，很多情況下數據比索引大
多了。盡管如此，需要總是檢查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB
，而 key_buffer 卻設置為 4GB 的情況是非常少的。這么做太浪費了。如果你很少使用
MyISAM表，那麼也保留低於 16-32MB 的 key_buffer_size 以適應給予磁碟的臨時表索引
所需。
innodb_buffer_pool_size - 這對Innodb表來說非常重要。Innodb相比MyISAM表對緩沖更
為敏感。MyISAM可以在默認的 key_buffer_size 設置下運行的可以，然而Innodb在默認的
innodb_buffer_pool_size 設置下卻跟蝸牛似的。由於Innodb把數據和索引都緩存起來，
無需留給操作系統太多的內存，因此如果只需要用Innodb的話則可以設置它高達 70-80% 的
可用內存。一些應用於 key_buffer 的規則有 -- 如果你的數據量不大，並且不會暴增，那
么無需把
innodb_additional_pool_size - 這個選項對性能影響並不太多，至少在有差不多足夠內存
可分配的操作系統上是這樣。不過如果你仍然想設置為 20MB(或者更大)，因此就需要看一下
Innodb其他需要分配的內存有多少。
innodb_log_file_size 在高寫入負載尤其是大數據集的情況下很重要。這個值越大則性能相
對越高，但是要注意到可能會增加恢復時間。我經常設置為 64-512MB，跟據伺服器大小而異。
innodb_log_buffer_size 默認的設置在中等強度寫入負載以及較短事務的情況下，伺服器性
能還可以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。如果它的值設置
太高了，可能會浪費內存 -- 它每秒都會刷新一次，因此無需設置超過1秒所需的內存空間。
通常 8-16MB 就足夠了。越小的系統它的值越小。
innodb_flush_logs_at_trx_commit 是否為Innodb比MyISAM慢1000倍而頭大？看來也許你忘
了修改這個參數了。默認值是 1，這意味著每次提交的更新事務（或者每個事務之外的語句）
都會刷新到磁碟中，而這相當耗費資源，尤其是沒有電池備用緩存時。很多應用程序，尤其是
從 MyISAM轉變過來的那些，把它的值設置為 2 就可以了，也就是不把日誌刷新到磁碟上，
而只刷新到操作系統的緩存上。日誌仍然會每秒刷新到磁碟中去，因此通常不會丟失每秒1-
2次更新的消耗。如果設置為 0 就快很多了，不過也相對不安全了 -- MySQL伺服器崩潰時
就會丟失一些事務。設置為 2 指揮丟失刷新到操作系統緩存的那部分事務。
table_cache -- 打開一個表的開銷可能很大。例如MyISAM把MYI文件頭標志該表正在使用
中。你肯定不希望這種操作太頻繁，所以通常要加大緩存數量，使得足以最大限度地緩存打
開的表。它需要用到操作系統的資源以及內存，對當前的硬體配置來說當然不是什麼問題了。
如果你有200多個表的話，那麼設置為 1024 也許比較合適（每個線程都需要打開表），
如果連接數比較大那麼就加大它的值。我曾經見過設置為 100,000 的情況。
thread_cache -- 線程的創建和銷毀的開銷可能很大，因為每個線程的連接/斷開都需要。
我通常至少設置為 16。如果應用程序中有大量的跳躍並發連接並且 Threads_Created 的值
也比較大，那麼我就會加大它的值。它的目的是在通常的操作中無需創建新線程。
query_cache -- 如果你的應用程序有大量讀，而且沒有應用程序級別的緩存，那麼這很有
用。不要把它設置太大了，因為想要維護它也需要不少開銷，這會導致MySQL變慢。通常設置
為 32-512Mb。設置完之後最好是跟蹤一段時間，查看是否運行良好。在一定的負載壓力下，
如果緩存命中率太低了，就啟用它。
sort_buffer_size --如果你只有一些簡單的查詢，那麼就無需增加它的值了，盡管你有
64GB 的內存。搞不好也許會降低性能

⑥ 怎麼看mysql有沒阻塞

通過下面的查詢，來查詢當前資料庫，有哪些事務，都鎖定哪些資源。

SELECT
	trx_idAS`事務ID`,
	trx_stateAS`事務狀態`,
	trx_requested_lock_idAS`事務需要等待的資源`,
	trx_wait_started	AS`事務開始等待時間`,
	trx_tables_in_useAS`事務使用表`,
	trx_tables_lockedAS`事務擁有鎖`,
	trx_rows_lockedAS`事務鎖定行`,
	trx_rows_modifiedAS`事務更改行`
FROM
	information_schema.innodb_trx;

SELECT
	lock_id			AS	`鎖ID`,
	lock_trx_id	AS	`擁有鎖的事務ID`,
	lock_mode		AS	`鎖模式	`,
	lock_type		AS	`鎖類型`,
	lock_table		AS	`被鎖的表`,
	lock_index		AS	`被鎖的索引`,
	lock_space		AS	`被鎖的表空間號`,
	lock_page		AS	`被鎖的頁號`,
	lock_rec		AS	`被鎖的記錄號`,
	lock_data		AS	`被鎖的數據`
FROM
	information_schema.innodb_locks;

SELECT
requesting_trx_idAS`請求鎖的事務ID`,
requested_lock_idAS`請求鎖的鎖ID`,
blocking_trx_idAS`當前擁有鎖的事務ID`,
blocking_lock_idAS`當前擁有鎖的鎖ID`
FROM
innodb_lock_waits;