MYSQL排錯指南學習筆記（一）排查及儲存引擎修復

翻到了之前整理的學習筆記，跟大家分享一下，雖然書裡的版本有點老，但是還有一些是值得借鑑和學習的部分。

1.語法錯誤(通用查詢日誌使用)
例如accessible在5.1版本是一個保留字需要語句加引號
5.0版本正常 select id from t1 where accessible=1;
5.1版本應該變為 select ‘id’ from ‘t1’ where ‘accessible’=1;
查詢sql可能由應用程式自動生成或者在儲存庫中由第三方庫動態生成

檢查mysql通用查詢日誌：包含mysql伺服器收到的每條獨立查詢（很多產品日常執行不會開啟，因為高負載情況下增長迅速，且寫入日誌會消耗mysql伺服器的資源）5.1版本後可以臨時開啟通用查詢日誌

開啟通用查詢日誌：

SET GLOBAL general_log='on';

也可以設定將日誌記錄在表中

SET GLOBAL log_output='table';

現在可以執行程式，迭代執行程式碼，查詢通用日誌記錄表

select * from mysql.general_log\G

找到問題後記得關閉通用查詢日誌；

SET GLOBAL general_log='off';

2. explain 的extended 擴充套件能夠在原本explain的基礎上額外的提供一些查詢最佳化的資訊，這些資訊可以透過mysql的show warnings命令得到。

EXPLAIN EXTENDS語句\G

show warnings\G

mysql伺服器不總是按照語句輸入順序執行，會呼叫最佳化器構造一個更好的執行計劃
EXPLAIN EXTEND 後，show warnings 看的是最佳化後的查詢
show warnings輸出包含兩個主要資訊
1.透過哪個表解析值
2.包含了最佳化後的語句，和伺服器從哪個表接受的值

3.當有錯誤碼的時候，可以透過perror工具獲取

perror 跟錯誤碼

4.查詢操作突然報錯，但是確信查詢本身沒問題
大多數是複製環境中主從節點資料不一致造成的，常見情形是期望唯一值的時候出現重複值。有時候在select才會出錯，而insert時候沒有問題。
mysql複製不會檢查資料一致性，對同一個表，同時使用sql複製和從節點上的使用者執行緒更新會使資料與主伺服器不同，導致隨後的複製事件失敗。

通常建立在從伺服器只讀的複製環境下，錯誤產生有兩種可能的原因：
要麼主伺服器插入錯誤資料，要麼資料複製時損壞

先檢查主伺服器資料是否有錯誤
master>select * from t1;
主伺服器正常，問題原因在複製層。複製看起來執行正常，猜想主節點有邏輯錯誤

檢視錶定義

show create table t1\G

5.慢查詢
主要三個技巧；最佳化sql，最佳化表（包括增加索引），最佳化伺服器

EXPLAIN
第一行 type 展示連線（join）的執行方式
操作單表時，explain也會報告連線，mysql的內部最佳化器把每個請求當成一個連線

type：連線型別 最關鍵的一列 效率（const>eq_ref>ref>range>index>all）
const:查詢索引欄位，並且表中最多隻有一行匹配（好像只有主鍵查詢只匹配一行才會是const，有些情況唯一索引匹配一行會是ref）
eq_ref 主鍵或者唯一索引
ref 非唯一索引（主鍵也是唯一索引）
range 索引的範圍查詢
index (type=index extra = using index 代表索引覆蓋，即不需要回表)
all 全表掃描（通常沒有建索引的列）

增加索引

alter table t1 add index(id);

索引有時候也會減慢查詢，這時候刪除索引或者使用忽略索引（IGNORE INDEX）--------可能其他語句都還需要改索引
也可以使用強制索引（FORCE INDEX）使最佳化器知道你要使用的索引
應該避免這兩種在生產環境使用
對單一表使用這兩種方法相對安全。

伺服器選項：
記憶體中臨時表的大小，排序緩衝區
針對特定儲存引擎（如InnoDB）

緩衝區（buffer size）牢記以下要素
a1.交換區（swapping）
mysql伺服器在所需記憶體都來自實體記憶體時執行最快，當用到交換區時，效能顯著下降
有一些緩衝區是針對每個使用者執行緒的，要決定緩衝區需要多少記憶體，公式：
最大連線數 緩衝區大小（max_connectionsbuffer_size）來計算
這個值不是決定性的，因為mysqld可以分配多於你指定的大小的記憶體
a2.啟動時間
mysqld需要分配的記憶體越多，其啟動時間越長
a3.過期資料
伸縮性問題，大部分時候來自執行緒間的快取共享。擴充緩衝區會產生記憶體碎片，伺服器執行數小時後會發現記憶體碎片問題，舊的資料從緩衝區移除以給新資料騰出空間時候，會導致高速運轉的伺服器突然變慢。

6.在查詢計劃前後查詢Handler_%的狀態檢查是否使用索引：

show status like 'Handler_%';

修改例子，使其可以更新為空的列
update items set description = ‘no description’, additional = ‘no description comments’ where description is null;
語句沒有執行，因為資料在之前損壞了，每個欄位值為0，而不是null
檢視處理程式變數

handler_read_rnd_next很高，改值代表從datafile中讀取下一個值的頻繁程度。過高的值一般使用了全表掃描
headler_read_key表示讀取索引的請求數目。這裡相對於handler_read_rnd_next很低，意味著大部分的讀取都沒有使用的索引。
handler_commit 和 handler_read_first增長緩慢，表示事物提交次數和讀索引中第一項次數。
header_read_first值為1，表明我們請求伺服器讀取索引中第一條記錄，可以當做全表掃描的標誌

索引會影響插入效率，每次插入都需要更新索引。
一種加速插入的方式是批次插入

7.伺服器無響應
mysql客戶端收到嚴重錯誤資訊， “在請求中丟失與伺服器的連線”或者“伺服器已停止”
主要兩個原因：伺服器問題崩潰，濫用連線選項（超時選項或者 max_allowed_packet）
例如：
先借助程式狀態監控器看是否伺服器真崩潰
如果伺服器崩潰後，執行了mysqld_safe或者其他守護程式重啟伺服器，錯誤日誌將包含表明伺服器重啟資訊
當mysqld啟動，錯誤日誌會有類似如下資訊：

如果有這部分資訊，則伺服器已經重啟了。如果沒有資訊，伺服器已經重啟並在執行，那麼丟失連線問題最有可能是因為濫用連線選項導致的。

可以查詢狀態變數uptime，檢視伺服器已啟動時間，單位是秒

SHOW GLOBAL STATUS LIKE 'uptime';

錯誤日誌中有如下崩潰原因關鍵行

意味著mysql伺服器在向作業系統申請資源（例如，訪問檔案或記憶體）後終止了，得到了錯誤程式碼11
在大多數系統中，這個訊號代表分段錯誤
在某個執行緒中導致伺服器崩潰的請求的相關日誌周靜提取的摘要資訊：

重新執行（在測試環境）

這是一個已知的bug#47780，5.0.88之後版本修復了

特定查詢會引起崩潰，伺服器的執行環境也可能引起崩潰，最常見的原因是缺少可用的記憶體（RAM）,尤其是使用者分配超大緩衝區時。

8.儲存引擎問題（MyISAM和InnoDB）
與儲存引擎相關的錯誤要麼反饋到客戶端，要麼記錄在錯誤日誌檔案中，一般，儲存引擎名字也會出現在錯誤訊息中，偶爾用perror也查不到的位置去錯誤碼，問題來自儲存引擎。
常見儲存引擎問題：資料損壞
不一定總是儲存引擎的錯誤，也可能是磁碟損壞，系統崩潰或者mysql伺服器崩潰等外部問題。kill -9終止伺服器程式，就可能導致資料損壞，可以嘗試CHECK TABLE命令。

a）MyISAM損壞
三個檔案一組儲存每張表
table_name.frm 儲存表的結構（schema）
table_name.MYD 檔案儲存資料
table_name.MYI 檔案儲存索引
崩潰會損壞資料檔案或者索引檔案，或者二者都損壞了，這種情況，訪問表，獲得類似於

的錯誤訊息，可以透過repair或crashed來判斷是否表損壞。

SQL語句中用 CHECK TABLE和  REPAIR TABLE針對資料損壞問題，shell裡也可以使用 myisamchk工具進行同樣的工作（不必訪問正在執行的mysql伺服器）

透過SQL修復MyISAM
CHECK TABLE t2;


這是一個損壞的表的輸出示例。解決問題第一步是執行不帶引數的REPAIR TABLE命令
REPAIR TABLE t2;

表被修復了，可以再次執行CHECK TABLE 進行確認
CHECK TABLE t2;

如果REPAIR TABLE 沒起到效果，可選擇其他兩個選項：
REPAIR TABLE EXTENDED 執行速度比REPAIR TABLE慢很多，但是可以修復99%的錯誤
作為最後的選擇，可以執行 REPAIR TABLE USE_FRM命令，該命令不會相信索引檔案中的資訊。他會刪除索引並利用table_name.frm檔案中的描述重建索引，並透過table_name.MYD檔案填充鍵對應的值。

為了達到同樣的目的，可以使用mysqlcheck工具，該工具透過向伺服器傳送check和repair命令進行工作，具有 --all-databases,改引數可以幫助使用者高效地執行表的維護。
mysqlcheck像其他使用者一樣連線到mysql伺服器工作，因此可以遠端使用。

使用myisamchk修復MyISAM表（不建議使用）
該命令包含許多額外的表維護選項
myisamchk可以直接訪問表檔案，而無需啟動mysql伺服器，myisamchk需要對錶檔案保持獨立的，排他的訪問，因此使用者也應該避免在mysql伺服器執行過程中使用該工具。
如果必須在伺服器執行期間使用該工具，則先執行FLUSH TABLES 和LOCK TABLE table_name WRITE 語句，然後等待直到最後的查詢返回命令提示符，接著在並行會話中執行myisamchk。如果有除了myisamchk之外的程式在myisamchk執行訪問期間訪問表， 則可能導致更糟糕的損壞
一條基本恢復命令
myisamchk --backup --recover t2

其中，- -backup選項通知myisamchk在嘗試恢復表之前進行資料檔案備份，- -recover選項執行實際恢復，如果這個命令不夠，使用- -safe-recover選項，會使用在早期的mysql版本中存在的恢復模式進行恢復，並且會找到簡單的–recover無法發現的問題。此外還有更加嚴格的- -extend-check
也可以使用- -sort-recover選項，會使用排序來解析鍵，甚至在臨時檔案很大的時候
選項- -description 選項比較有用，會輸出表的描述資訊，結合-v或者等價的-version，輸出額外的資訊，可以指定兩次-v或者三次-v去獲得更多的資訊。

b）InnoDB資料損壞
InnoDB在共享的表空間中儲存器其資料和索引，如果伺服器在建立表時以選項- -innodb_file_per_table選項啟動，那麼它會有自己的資料檔案，但是表的定義依然在共享表空間裡。
InnoDB是帶有事務的儲存引擎，並且內部機制會自動修復大部分資料損壞錯誤，它會在伺服器啟動時進行修復。
下面摘要了MYSQL企業級備份（MEB）也稱作InnoDb熱備份，中執行mysqlbackup --copy-back 命令完成備份後，從錯誤日誌中摘錄的，展示了典型恢復情況

有時候損壞嚴重，InnoDB無法在沒有使用者互動的情況下完成修復。
有–innodb_force_recovery啟動選項，可以設定0~6值，（0不強制修復，1最低階別，6最高階別）
如果發生損壞可以從1開始依次濟寧嘗試–innodb_force_recovery選項，直到可以啟動伺服器並可以訪問有問題的表為止。
檢查應該發現哪個表損壞了，用SELECT INTO OUTLINE將錶轉儲到檔案中，然後使用DROP和CREATE命令重新建立表，最後用–innodb_force_recovery=0重新啟動伺服器，然後載入轉儲資料。如果問題還存在，找到其他損壞的表繼續操作。
當需要–innodb_force_recovery的值是正數時修復資料庫，錯誤日誌有類似提示

InnoDB在寫實際資料前會立即對資料、索引和日誌頁寫校驗和，並在從磁碟讀資料之後會立即確認校驗和。
通常，一旦InnoDB資料損壞，意味著磁碟或者記憶體有問題。