在线能看的丝袜网站,91黄色大片,天天日天天干天天色,影音先锋AV一区二区三区四区 ,成人精品三级AV在线看,成人网在线免费观看,午夜aaa,色综合小说

Java技術(shù)棧

www.javastack.cn

關(guān)注閱讀更多優(yōu)質(zhì)文章

?
領(lǐng)導(dǎo)讓我SQL優(yōu)化，我直接把服務(wù)干掛了...
?

前言

MySQL大表加字段或者加索引，是有一定風(fēng)險(xiǎn)的。

大公司一般有DBA，會(huì)幫助開(kāi)發(fā)解決這個(gè)痛點(diǎn)，可是DBA是怎么做的呢？

小公司沒(méi)有DBA，作為開(kāi)發(fā)我們的責(zé)任就更大了。那么我們?cè)趺床拍馨踩募觽€(gè)索引呢？

今天，我們通過(guò)模擬案例以及原理分析，去弄清楚MySQL中DDL的風(fēng)險(xiǎn)，以及如何避免事故發(fā)生。

準(zhǔn)備

軟件以及項(xiàng)目

安裝本地版本MySQL。
一個(gè)簡(jiǎn)單的增刪改查項(xiàng)目。
使用JMeter進(jìn)行并發(fā)請(qǐng)求測(cè)試。

創(chuàng)建表

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

#?創(chuàng)建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?'測(cè)試?id',
??`create_time`?timestamp?NULL?DEFAULT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??`modify_time`?timestamp?NULL?DEFAULT?NULL?COMMENT?'修改時(shí)間',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COMMENT='mysql?ddl測(cè)試表';

創(chuàng)建存儲(chǔ)過(guò)程

#?如果存在test存儲(chǔ)過(guò)程則刪除
DROP?PROCEDURE?IF?EXISTS?`test`;

#?創(chuàng)建無(wú)參存儲(chǔ)過(guò)程，名稱為test
CREATE?PROCEDURE?test()

BEGIN
????#?聲明變量
????DECLARE?i?INT;
????#?變量賦值
????SET?i?=?0;
????#?結(jié)束循環(huán)的條件:?當(dāng)i等于100萬(wàn)時(shí)跳出while循環(huán)
????WHILE?i?DO
????#?往t_test表添加數(shù)據(jù)
????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');

????#?循環(huán)一次,?i加1
????SET?i?=?i?+?1;
????#?結(jié)束while循環(huán)
????END?WHILE;

END

?
下面的創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句，是在IDE內(nèi)選擇代碼塊執(zhí)行的，如果在Terminal中執(zhí)行，需要使用DELIMITER關(guān)鍵字，更改語(yǔ)句結(jié)束標(biāo)志。
?

調(diào)用存儲(chǔ)過(guò)程，生成百萬(wàn)數(shù)據(jù)

CALL?test();

開(kāi)啟慢SQL日志

#?查看MySQL是否開(kāi)啟慢日志記錄
SHOW?VARIABLES?LIKE?'slow_query_log';

#?開(kāi)啟慢SQL日志記錄
SET?GLOBAL?slow_query_log?=?'ON';

#?查看慢SQL日志位置
SHOW?VARIABLES?LIKE?'slow_query_log_file';

#?查看執(zhí)行多久的SQL才算慢SQL
SHOW?VARIABLES?LIKE?'long_query_time';

#?設(shè)置慢SQL執(zhí)行時(shí)間?只有新session才生效
SET?GLOBAL?long_query_time?=?1;

?
通常情況下這些會(huì)在MySQL的配置文件中配置，啟動(dòng)時(shí)生效。
?

幾個(gè)有用的SQL語(yǔ)句

#?展示哪些線程正在運(yùn)行
SHOW?PROCESSLIST;

#?查看正在執(zhí)行的事務(wù)
SELECT?*?FROM?information_schema.INNODB_TRX;

#?查看正在鎖的事務(wù)
SELECT?*?FROM?information_schema.INNODB_LOCKS;

#?查看正在等待鎖的事務(wù)
SELECT?*?FROM?information_schema.INNODB_LOCK_WAITS;

#?顯示innodb存儲(chǔ)引擎狀態(tài)的大量信息，包含死鎖日志
SHOW?ENGINE?INNODB?STATUS?;

#?展示數(shù)據(jù)庫(kù)最大連接數(shù)的配置
SHOW?VARIABLES?LIKE?'max_connections';

#?查看存在哪些觸發(fā)器
SELECT?*?FROM?information_schema.TRIGGERS;

#?查看MySQL版本
SELECT?VERSION();

?
后面我們會(huì)主要用前兩條。
?

事故現(xiàn)場(chǎng)

說(shuō)明

我創(chuàng)建的user表除了主鍵是沒(méi)有其他索引的。
測(cè)試的user表數(shù)據(jù)量為一百萬(wàn)。
測(cè)試MySQL版本為5.7.28。
測(cè)試項(xiàng)目的邏輯：隨機(jī)get()、list()、update()、create()，每個(gè)操作都開(kāi)啟事務(wù)，并且休眠500毫秒。

步驟

?
運(yùn)行測(cè)試項(xiàng)目
?

這里我們可以看到，項(xiàng)目已經(jīng)正常啟動(dòng)了。

?
postman調(diào)用一下接口
?

這里我們隨便測(cè)試一個(gè)接口，請(qǐng)求時(shí)間2秒左右。

?
執(zhí)行JMeter的Test Plan，觀察項(xiàng)目日志
?

這里我們創(chuàng)建了四個(gè)線程組，每個(gè)線程組調(diào)用一個(gè)我們的接口。模擬10個(gè)人循環(huán)1000次的訪問(wèn)。

這里我們看到該請(qǐng)求頻率下，日志無(wú)異常。

?
慢SQL日志
?

這里我們看到，百萬(wàn)級(jí)的SQL，如果沒(méi)加索引SQL執(zhí)行時(shí)間還是比較長(zhǎng)的，有的已經(jīng)達(dá)到了2s。另外，關(guān)注公眾號(hào)Java技術(shù)棧可以獲取更多我整理的MySQL系列優(yōu)化教程。

?
加個(gè)索引，再觀察項(xiàng)目日志
?

這里我們看到，項(xiàng)目已經(jīng)開(kāi)始報(bào)錯(cuò)了，大量的Connection is not available, request timed out after 30001ms。

?
SHOW PROCESSLIST一下
?

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

?
postman再次調(diào)用一下接口
?

這個(gè)時(shí)候，調(diào)用接口已經(jīng)報(bào)錯(cuò)了，響應(yīng)時(shí)間也比較久。此時(shí)，服務(wù)對(duì)用戶來(lái)說(shuō)，已經(jīng)基本不可用了。

為什么會(huì)這樣？

?
我就想加個(gè)索引，怎么就這么難？
?

看吧，就因?yàn)槲壹恿藗€(gè)索引，服務(wù)就掛了，我沒(méi)加之前還是好好的。遇到問(wèn)題，我們要冷靜，不是我們的鍋堅(jiān)決不能背，真的是我們的問(wèn)題，下次一定要記得改正。那么，此刻的服務(wù)為什么就不可用了呢？

首先我們要知道，在InnoDB事務(wù)中，鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段鎖協(xié)議。

然后，在MySQL5.5版本中引入了MDL(Metadata Lock)，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加MDL讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加MDL寫鎖。

我們可以簡(jiǎn)單的嘗試一下下面的情況。

Session A開(kāi)啟一個(gè)事務(wù)，執(zhí)行了一個(gè)簡(jiǎn)單的查詢語(yǔ)句。此時(shí)，Session B，執(zhí)行另一個(gè)查詢語(yǔ)句，可以成功。接著，Session C執(zhí)行了一個(gè)DDL操作，加了個(gè)字段，因?yàn)?code style>Session A的事務(wù)沒(méi)有提交，而且Session A持有MDL讀鎖，Session C獲取不到MDL寫鎖，所以Session C堵塞等待MDL寫鎖。又由于MDL寫鎖獲取優(yōu)先級(jí)高于MDL讀鎖，因此Session D這個(gè)時(shí)候也獲取不到MDL讀鎖，等待Session C獲取到MDL寫鎖之后它才能獲取到MDL讀鎖。

我們發(fā)現(xiàn)，DDL操作之前如果存在長(zhǎng)事務(wù)，一直不提交，DDL操作就會(huì)一直被堵塞，還會(huì)間接的影響后面其他的查詢，導(dǎo)致所有的查詢都被堵塞。

這也就是為什么我們把服務(wù)干掛的原因了。

目前主流解決方案

針對(duì)上面出現(xiàn)的情況，我們?cè)趺唇鉀Q呢？

MySQL5.6的`Online DDL`

MySQL從5.6開(kāi)始，支持Online DDL。類似于這種的語(yǔ)句ALTER TABLE user ADD INDEX idx_test_id (test_id), ALGORITHM=INPLACE, LOCK=NONE在普通的ALTER TABLE或者CREATE INDEX語(yǔ)句后面添加ALGORITHM參數(shù)和LOCK參數(shù)。

?
實(shí)際上，ALTERT TABLE語(yǔ)句如果不加ALGORITHM參數(shù)，默認(rèn)就會(huì)選擇ALGORITHM=INPLACE的形式，如果執(zhí)行的語(yǔ)句支持INPLACE，否則，會(huì)使用ALGORITHM=COPY。
?

以前寫SQL只會(huì)ALTER TABLE不知道后面還可以加ALGORITHM參數(shù)，后來(lái)知道了Online DDL，知道了可以加ALGORITHM=INPLACE，結(jié)果兩種寫法有的時(shí)候是一樣的...

這里順便提一句，學(xué)習(xí)的途徑有很多，但是官網(wǎng)，的確可以多看看。

使用`pt-online-schema-change`

?
簡(jiǎn)單說(shuō)一下怎么安裝這個(gè)東西
?

首先官網(wǎng)下載，然后校驗(yàn)以及安裝，執(zhí)行下面命令

perl?Makefile.PL
make
make?install

然后使用CPAN安裝相關(guān)依賴（適用Unix），CentOS下直接yum更簡(jiǎn)單

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

?
我自己Mac安裝沒(méi)啥問(wèn)題，公司Mac安裝失敗了，然后升級(jí)了一下Perl版本就可以了。
?

語(yǔ)法

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`測(cè)試

這里我們看到，pt-osc創(chuàng)建觸發(fā)器的時(shí)候卡在那了。實(shí)際上這里也是在等待鎖。

最終成功了，但是整個(gè)過(guò)程時(shí)間比較久。過(guò)程中我們也發(fā)現(xiàn)了一些死鎖的日志。

其實(shí)，這個(gè)跟我的代碼有一定的關(guān)系，我的測(cè)試代碼隨機(jī)數(shù)生成的范圍是[0, 20000]，然后我根據(jù)生成的隨機(jī)數(shù)，去查詢數(shù)據(jù)庫(kù)，鎖的沖突會(huì)比較多。把范圍修改為[0, 1000000]會(huì)好很多。

再看`Online DDL`

因?yàn)閯偛盼覀儼l(fā)現(xiàn)了，自己代碼寫的有一些問(wèn)題，所以我們剛才的結(jié)論也有一些影響。我們把隨機(jī)數(shù)的范圍改到100萬(wàn)，重新測(cè)試一遍。

這次Online DDL也成功了。但是也是有一些連接超時(shí)的日志。之前的測(cè)試如果一直執(zhí)行下去，也會(huì)成功，只不過(guò)堵塞時(shí)間太長(zhǎng)，對(duì)用戶影響太大，我就停止算執(zhí)行失敗了。

?
實(shí)際效果跟機(jī)器性能也是有一些關(guān)系的，這里的關(guān)鍵點(diǎn)在于拿MDL寫鎖的等待時(shí)間，這個(gè)時(shí)間稍微久一些就會(huì)對(duì)用戶造成很大的影響。
?

`pt-osc`執(zhí)行過(guò)程

創(chuàng)建一個(gè)和原表表結(jié)構(gòu)一樣的臨時(shí)表(_tablename_new)，執(zhí)行alter修改臨時(shí)表表結(jié)構(gòu)。
在原表上創(chuàng)建3個(gè)與insert delete update對(duì)應(yīng)的觸發(fā)器，用于copy數(shù)據(jù)的過(guò)程中，在原表的更新操作，更新到新表。
從原表拷貝數(shù)據(jù)到臨時(shí)表，拷貝過(guò)程中在原表進(jìn)行的寫操作都會(huì)更新到新建的臨時(shí)表。
rename原數(shù)據(jù)表為old表，把新表rename為原表名，并將old表刪除。
刪除觸發(fā)器。

這里面創(chuàng)建、刪除觸發(fā)器和rename表的時(shí)候都會(huì)嘗試獲取DML寫鎖，如果獲取不到會(huì)等待。就是我們看到的Waiting for table metadata lock。

所以，這些時(shí)間段如果長(zhǎng)時(shí)間獲取不到鎖，就會(huì)一直堵塞，還是會(huì)出現(xiàn)問(wèn)題的。

`Online DDL`執(zhí)行過(guò)程

拿MDL寫鎖
降級(jí)成MDL讀鎖
真正做DDL
升級(jí)成MDL寫鎖
釋放MDL鎖

1、4如果沒(méi)有鎖沖突，執(zhí)行時(shí)間非常短。第3步占用了DDL絕大部分時(shí)間，這期間這個(gè)表可以正常讀寫數(shù)據(jù)，因此稱為online。

但是，如果拿鎖的時(shí)候沒(méi)拿到，或者升級(jí)MDL寫鎖不能成功，就會(huì)等待，我們又會(huì)看到Waiting for table metadata lock，然后就接著的一系列問(wèn)題了。

總結(jié)

加個(gè)索引，說(shuō)難也難，說(shuō)不難也不難。如果數(shù)據(jù)量大，又存在長(zhǎng)事務(wù)，加索引的過(guò)程又有用戶訪問(wèn)，Online DDL和pt-osc都不能保證對(duì)業(yè)務(wù)沒(méi)有影響。但是如果我們SQL的執(zhí)行時(shí)間比較短，或者我們加索引的時(shí)候，對(duì)應(yīng)的業(yè)務(wù)沒(méi)有多少請(qǐng)求。那么我們就可以很快的加完索引。

加字段也是類似的過(guò)程，但是如果我們能保證沒(méi)有慢SQL，那么就不會(huì)存在長(zhǎng)事務(wù)，那么執(zhí)行時(shí)間就會(huì)很快，對(duì)用戶就可以做到幾乎沒(méi)有影響。至于選擇Online DDL還是pt-osc就要看他們的一些限制以及自己的場(chǎng)景需求了。感興趣的同學(xué)，自己嘗試一下。

最后想說(shuō)

當(dāng)萬(wàn)丈高樓崩塌的時(shí)候，超人也不能將它復(fù)原。我們應(yīng)該做的，是有一個(gè)好的規(guī)范，好的認(rèn)知，好的監(jiān)控，在問(wèn)題沒(méi)有出現(xiàn)的時(shí)候，就將問(wèn)題扼殺在搖籃中。而不是讓問(wèn)題，日漸壯大，大到覆水難收...

_{參考文獻(xiàn)：}
_{[1]:《MySQL實(shí)戰(zhàn)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}

老大，我就想加個(gè)索引，怎么就這么難？

前言