我就想加個索引，怎麼就這麼難？

iisheng發表於2020-08-07
索引

領導讓我SQL優化，我直接把服務幹掛了...

前言

MySQL大表加欄位或者加索引，是有一定風險的。

大公司一般有DBA，會幫助開發解決這個痛點，可是DBA是怎麼做的呢？

小公司沒有DBA，作為開發我們的責任就更大了。那麼我們怎麼才能安全的加個索引呢？

今天，我們通過模擬案例以及原理分析，去弄清楚MySQL中DDL的風險，以及如何避免事故發生。

準備

軟體以及專案

  1. 安裝本地版本MySQL。
  2. 一個簡單的增刪改查專案。
  3. 使用JMeter進行併發請求測試。

建立表

# 如果存在user表則刪除
DROP TABLE  IF EXISTS user;

# 建立user表
CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` int(2) DEFAULT NULL COMMENT '年齡',
  `address` varchar(30) DEFAULT NULL COMMENT '地址',
  `description` varchar(100) DEFAULT NULL COMMENT '描述',
  `test_id` bigint DEFAULT NULL COMMENT '測試 id',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '建立時間',
  `modify_time` timestamp NULL DEFAULT NULL COMMENT '修改時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='mysql ddl測試表';

建立儲存過程

# 如果存在test儲存過程則刪除
DROP PROCEDURE IF EXISTS `test`;

# 建立無參儲存過程，名稱為test
CREATE PROCEDURE test()

BEGIN
    # 宣告變數
    DECLARE i INT;
    # 變數賦值
    SET i = 0;
    # 結束迴圈的條件: 當i等於100萬時跳出while迴圈
    WHILE i < 1000000 DO
    # 往t_test表新增資料
    INSERT INTO `test`.user (`name`, `age`, `address`, 
                             `description`, `test_id`, `create_time`, `modify_time`)
    VALUES ('iisheng', 26, '北京', '如逆水行舟', LAST_INSERT_ID() + 1, 
            '2020-05-17 16:01:44', '2020-05-17 16:01:51');

    # 迴圈一次, i加1
    SET i = i + 1;
    # 結束while迴圈
    END WHILE;

END

下面的建立儲存過程語句，是在IDE內選擇程式碼塊執行的，如果在Terminal中執行，需要使用DELIMITER關鍵字，更改語句結束標誌。

呼叫儲存過程，生成百萬資料

CALL test();

開啟慢SQL日誌

# 檢視MySQL是否開啟慢日誌記錄
SHOW VARIABLES LIKE 'slow_query_log';

# 開啟慢SQL日誌記錄
SET GLOBAL slow_query_log = 'ON';

# 檢視慢SQL日誌位置
SHOW VARIABLES LIKE 'slow_query_log_file';

# 檢視執行多久的SQL才算慢SQL
SHOW VARIABLES LIKE 'long_query_time';

# 設定慢SQL執行時間 只有新session才生效
SET GLOBAL long_query_time = 1;

通常情況下這些會在MySQL的配置檔案中配置，啟動時生效。

幾個有用的SQL語句

# 展示哪些執行緒正在執行
SHOW PROCESSLIST;

# 檢視正在執行的事務
SELECT * FROM information_schema.INNODB_TRX;

# 檢視正在鎖的事務
SELECT * FROM information_schema.INNODB_LOCKS;

# 檢視正在等待鎖的事務
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

# 顯示innodb儲存引擎狀態的大量資訊，包含死鎖日誌
SHOW ENGINE INNODB STATUS ;

# 展示資料庫最大連線數的配置
SHOW VARIABLES LIKE 'max_connections';

# 檢視存在哪些觸發器
SELECT * FROM information_schema.TRIGGERS;

# 檢視MySQL版本
SELECT VERSION();

後面我們會主要用前兩條。

事故現場

說明

  1. 我建立的user表除了主鍵是沒有其他索引的。
  2. 測試的user表資料量為一百萬。
  3. 測試MySQL版本為5.7.28。
  4. 測試專案的邏輯：隨機get()、list()、update()、create()，每個操作都開啟事務，並且休眠500毫秒。

步驟

執行測試專案

專案啟動圖

這裡我們可以看到，專案已經正常啟動了。

postman呼叫一下介面

介面請求圖

這裡我們隨便測試一個介面，請求時間2秒左右。

執行JMeter的Test Plan，觀察專案日誌

JMeter配置圖

這裡我們建立了四個執行緒組，每個執行緒組呼叫一個我們的介面。模擬10個人迴圈1000次的訪問。

正常專案日誌圖

這裡我們看到該請求頻率下，日誌無異常。

慢SQL日誌

慢SQL日誌圖

這裡我們看到，百萬級的SQL，如果沒加索引SQL執行時間還是比較長的，有的已經達到了2s。

加個索引，再觀察專案日誌

加索引過程日誌圖

這裡我們看到，專案已經開始報錯了，大量的Connection is not available, request timed out after 30001ms。

SHOW PROCESSLIST 一下

PROCESSLIST圖

這裡我們看到，有大量的Waiting for table metadata lock。

postman再次呼叫一下介面

請求介面報錯圖

這個時候，呼叫介面已經報錯了，響應時間也比較久。此時，服務對使用者來說，已經基本不可用了。

為什麼會這樣？

我就想加個索引，怎麼就這麼難？

看吧，就因為我加了個索引，服務就掛了，我沒加之前還是好好的。遇到問題，我們要冷靜，不是我們的鍋堅決不能背，真的是我們的問題，下次一定要記得改正。那麼，此刻的服務為什麼就不可用了呢？

首先我們要知道，在InnoDB事務中，鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

然後，在MySQL5.5版本中引入了MDL(Metadata Lock)，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對錶做結構變更操作的時候，加MDL寫鎖。

我們可以簡單的嘗試一下下面的情況。

DDL鎖等待圖

Session A開啟一個事務，執行了一個簡單的查詢語句。此時，Session B，執行另一個查詢語句，可以成功。接著，Session C執行了一個DDL操作，加了個欄位，因為Session A的事務沒有提交，而且Session A持有MDL讀鎖，Session C獲取不到MDL寫鎖，所以Session C堵塞等待MDL寫鎖。又由於MDL寫鎖獲取優先順序高於MDL讀鎖，因此Session D這個時候也獲取不到MDL讀鎖，等待Session C獲取到MDL寫鎖之後它才能獲取到MDL讀鎖。

我們發現，DDL操作之前如果存在長事務，一直不提交，DDL操作就會一直被堵塞，還會間接的影響後面其他的查詢，導致所有的查詢都被堵塞。

這也就是為什麼我們把服務幹掛的原因了。

目前主流解決方案

針對上面出現的情況，我們怎麼解決呢？

MySQL5.6的Online DDL

MySQL從5.6開始，支援Online DDL。類似於這種的語句ALTER TABLE user ADD INDEX idx_test_id (test_id), ALGORITHM=INPLACE, LOCK=NONE在普通的ALTER TABLE或者CREATE INDEX語句後面新增ALGORITHM引數和LOCK引數。

實際上，ALTERT TABLE語句如果不加ALGORITHM引數，預設就會選擇ALGORITHM=INPLACE的形式，如果執行的語句支援INPLACE，否則，會使用ALGORITHM=COPY。

以前寫SQL只會ALTER TABLE不知道後面還可以加ALGORITHM引數，後來知道了Online DDL，知道了可以加ALGORITHM=INPLACE，結果兩種寫法有的時候是一樣的...

MySQL官網截圖

這裡順便提一句，學習的途徑有很多，但是官網，的確可以多看看。

使用pt-online-schema-change

簡單說一下怎麼安裝這個東西

首先官網下載，然後校驗以及安裝，執行下面命令

perl Makefile.PL
make
make install

然後使用CPAN安裝相關依賴（適用Unix），CentOS下直接yum更簡單

perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql

我自己Mac安裝沒啥問題，公司Mac安裝失敗了，然後升級了一下Perl版本就可以了。

語法

pt-online-schema-change --charset=utf8 --no-check-replication-filters --no-version-check --user=user --password=pass --host=host_addr  P=3306,D=database,t=table --alter "ADD INDEX idx_name(field_name)" --execute

我的指令碼新增索引

pt-online-schema-change --charset=utf8 --no-check-replication-filters --no-version-check --user=root --password=mGy6GAzdawFPTJ7R --host=127.0.0.1  P=3306,D=test,t=user --alter "add INDEX idx_test_id(test_id)" --execute

使用pt-osc測試

pt-osc執行圖

這裡我們看到，pt-osc建立觸發器的時候卡在那了。實際上這裡也是在等待鎖。

最終成功了，但是整個過程時間比較久。過程中我們也發現了一些死鎖的日誌。

pt-osc死鎖日誌

其實，這個跟我的程式碼有一定的關係，我的測試程式碼隨機數生成的範圍是[0, 20000]，然後我根據生成的隨機數，去查詢資料庫，鎖的衝突會比較多。把範圍修改為[0, 1000000]會好很多。

再看Online DDL

因為剛才我們發現了，自己程式碼寫的有一些問題，所以我們剛才的結論也有一些影響。我們把隨機數的範圍改到100萬，重新測試一遍。

Online DDL 成功

這次Online DDL也成功了。但是也是有一些連線超時的日誌。之前的測試如果一直執行下去，也會成功，只不過堵塞時間太長，對使用者影響太大，我就停止算執行失敗了。

實際效果跟機器效能也是有一些關係的，這裡的關鍵點在於拿MDL寫鎖的等待時間，這個時間稍微久一些就會對使用者造成很大的影響。

pt-osc執行過程

  1. 建立一個和原表表結構一樣的臨時表(_tablename_new)，執行alter修改臨時表表結構。
  2. 在原表上建立3個與insert delete update對應的觸發器，用於copy資料的過程中，在原表的更新操作，更新到新表。
  3. 從原表拷貝資料到臨時表，拷貝過程中在原表進行的寫操作都會更新到新建的臨時表。
  4. rename原資料表為old表，把新表rename為原表名，並將old表刪除。
  5. 刪除觸發器。

這裡面建立、刪除觸發器和rename表的時候都會嘗試獲取DML寫鎖，如果獲取不到會等待。就是我們看到的Waiting for table metadata lock。

所以，這些時間段如果長時間獲取不到鎖，就會一直堵塞，還是會出現問題的。

Online DDL執行過程

  1. 拿MDL寫鎖
  2. 降級成MDL讀鎖
  3. 真正做DDL
  4. 升級成MDL寫鎖
  5. 釋放MDL鎖

1、4如果沒有鎖衝突，執行時間非常短。第3步佔用了DDL絕大部分時間，這期間這個表可以正常讀寫資料，因此稱為online。

但是，如果拿鎖的時候沒拿到，或者升級MDL寫鎖不能成功，就會等待，我們又會看到Waiting for table metadata lock，然後就接著的一系列問題了。

總結

加個索引，說難也難，說不難也不難。如果資料量大，又存在長事務，加索引的過程又有使用者訪問，Online DDL和pt-osc都不能保證對業務沒有影響。但是如果我們SQL的執行時間比較短，或者我們加索引的時候，對應的業務沒有多少請求。那麼我們就可以很快的加完索引。

加欄位也是類似的過程，但是如果我們能保證沒有慢SQL，那麼就不會存在長事務，那麼執行時間就會很快，對使用者就可以做到幾乎沒有影響。至於選擇Online DDL還是pt-osc就要看他們的一些限制以及自己的場景需求了。感興趣的同學，自己嘗試一下。

最後想說

當萬丈高樓崩塌的時候，超人也不能將它復原。我們應該做的，是有一個好的規範，好的認知，好的監控，在問題沒有出現的時候，就將問題扼殺在搖籃中。而不是讓問題，日漸壯大，大到覆水難收...

參考文獻：
[1]:《MySQL實戰45講》
[2]: https://dev.mysql.com/doc/refman/5.7/en/
[3]: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

歡迎關注個人微信公眾號【如逆水行舟】，用心輸出基礎、演算法、原始碼系列文章。

相關文章