前段时间刚入职一家公司，就遇到了 MySQL 亿级大表优化这事!
图片来自 Pexels
背景
XX 实例(一主一从)xxx 告警中每天凌晨在报 SLA 报警，该报警的意思是存在一定的主从延迟。(若在此时发生主从切换，需要长时间才可以完成切换，要追延迟来保证主从数据H C b k , K U 2的一致性)
XX 实例的慢查询数量最多(执行时间超过 1s 的 SQL 会被记录)，XX 应用那方每天晚上在做删除一个月前数据的任务。
分析w % q P r K
使用 pt-query-digest 工具分析最近一周的 myso I L h 6 | / m 6ql-slow.log：
pt-_ P $ .query-digest | * T i--since=148h mysql-slow.log | { E f A u G d;less 结果第一部分：
最近一个星期内，总共记录的慢查询执行花费时间为 254y r M ~ 8 j03s，最大的慢 SQL 执行时间为 266s，平均每个慢% ( w f H X g M o SQL 执行~ z w ! z j时间 5s，平均扫描的行数为 1766 万。
结果第二部分：
select arrival_record 操作记录的慢查询数量最多有 4 万多次，平均响应时间为 4s，delete arrival_record 记录了 6 次，平均响应时间 258s。
select xxx_record 语句
select arrival_record 慢查询语句j G f Z u C | : 5都类似于如下所示，where 语句中的参数字段是一样的，传入的参数值不一样：
select count(*) from arrival_record I J k Z ~ z J w 8;whe+ e A b ;re product_id=26- ( n # A N ~ k and receive_ti% B {me between \'O j 52019-03-25 14:00:00\'&X f $ Qnbsp;and \s Q G O # J'2019-03-25 15:00:00\' anZ . O _ d receive_spend_ms>=0\\G
select arrival_record 语句在 MySQd & u T x # kL 中最多扫描的行数为 5600 万、平均扫描的行数为 172 万，推断由于扫描的行数多导致的执行时间长。
查看执行计划：
explain select count(*) from arrival_recorn _ P Id where product_id=26 and receive_time between \'2019-03-25 14:00:00\' and \'2019-03-25 15z L G:00:00\' and receive_spend_ms>=0\M Z p _\G;
*************************** 1. row ***************************
i= U q Z .d: 1O * / 1 ? x
select_type: SIMPLE
table: arrival_r 7 f D ~ecord
partitions: NULL
type} l t (: ref
possible_keys: IXFK_arrival_record
key: IXFK_arrival_rec_ c k R S = # * !ord
key_len: 8
ref: const
rows: 32261320
filtered: 3.70
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec) 用到了索引 IXFK_* Z 8 arrival_record，但预计扫描的行数很多有 3000 多万行：
show ing W J @dex from arrival_recorN * Ed;
+----------------+------------+---------------------+------4 - ] M ] g N--------+--------------+----^ q e r ]-------+-------------+----------+--------+------+------------+---------+--------------2 I -+
| Table | Non_unique | Key_name | Seq_in_ind^ q v h : w 5ex | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Ind/ f !ex_type | Comment | Index_comment |
+----------------+------------+---------------------+--------------+--------------+------[ 8 ]-----+-------------+----------+--------+------J z g ? 3+------------+---------+---------------+
| arrival_record | 0 | PRIMARY | 1 | id | A | 107990720 | NULL | NULL | | BTREE | | |
| arrival_recoY h | F } * -rd | 1 | IXFK_arrival_record | 1 | p^ # 8 h 5 Y k ?roduct_i/ K + a z ] ^ Pd6 o A | A | 1344 | NULL | NULL | | BTREE | | |
| arrival_record | 1 | IXFK_arrival_record | 2 | station_no | A | 22161 | NULL | NULL | YES | BTRE$ { % } 7 n pE | | |
| arrival_record | 1 | IXFK_arrival_record | 3 | sequence | A | 77233384 | NULL | NULL | | BTRE4 D e +E | | |
| arrival_record | 1 | IXFK_arrival_record | 4 | receive_time | A` w g J t u | 65854652 | NULL | NULL | YES | BTREE | | |
| arrival_record | 1 | IXFK_arrival_record | 5 | arrival_time | A | 73861904 | NULL | NULL | Yi i N ` BES | BTREE | | |
+-i _ g---------------+------------+-^ M : m D--------------------+-------------: g h-+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
show create table arrival_record;
..........
arrival_spend_ms bigint(20) DEFAULT NULL,
total_spend_ms bigint(20) DEFAULT NULL,
PRIMARY KEY (id),
KEY IXFK_arrival_record (product_id,station_no,seque4 . a a 4 z a Gnce,receive_ti[ / d # j c w E cme,arrival_time) USING BR 4 ` u 6TREE,
CONSTRAINT FK_arriva4 ? ? O C il_record_product FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=614538979 DEFAULT CHARSET=utf8 COLLATE=utf8_biny - ~ ) 6 f o | ①该表总记录数约 1 亿多条，表上只有一个X 2 ) V复合索引，product_id 字段基数很小，选择性不好。
②传入的过滤条件：
where product_id=26 and receive_time betweenm P [ I b 5 \= o % 5 * A ^ F ,'2019-03-25 14:00:00\'&nbs7 j 1 k R D 5p;and \'2019-03-25 15:00:00\' and p S C areceive_spend_ms>=0 没有 station_nu 字段，使用不到复合索引 IXFK_arrival_record 的 product_id，station_no，sequenk , 4 a # Zce，receive_time 这几个字段。
③W X a | G a @ *根据最左前缀原则，select arrival_record 只用到了复合索引 IXFK_arrival_record 的第一个字段 product_id，而该字段选择性很差，导致扫描的行数很多，执行时间长。
④receive_time 字段的基数大，选择性好，可对该字段单独建立索引，select arrival_record sql| % $ 就会使用到该索引。
现在已经知道了在慢查询中记录的 select arrival_recoN ; 0 a I 5 *rd where 语句传入的参数字段有 product_id，receive_time，receive_spend_ms，还想知道对该表的访问有没有通过其他字段来过滤了?
神器 tcpdump 出场的时候到了，使y ( A t用 tcpdump 抓包一段时间对该表的 select 语句：
tcpdump&nby X M K Psp;-i bond0 -s&nbs^ X P / o $p;0&nbs) 5 hp;-l&p ) q ? &nb[ k e H ` ^ J ]sp;-w - dst port 3316 | strings | grep select | egrepd 5 8 J -i&nb7 } t o C G W 5sp;\'arrival_recordh , f k ]\' >/ta D $ w S # ?mp/select_arri.log 获取 select 语句中 from 后面的 wheI & h Y T } ] Fre 条件语句：
IFS_OLD=$IFS
IFS=$\'\\n\'
for i in `cat /tmp/select_arr* 2 z j 1 | z 0 Ii.log `;do echo ${i#*\'O j I =from\'}; done | lesE e 8 ;s
IFS=$IFS_OLD
arP R v ~ y q _ Prival_record arrivalrec0_ where arrivalrec0_.sequence=\'2019-03-27 08:40\' a| e X 1nd arrivalrec0_.product_id=17 and arrivalrec0_9 U g c 3 F.station_no=\'56742\'
arrival_record arrivalrec0_ wherV W 7 / O # / %e arrivalrec0_.sequeO F @ 2 # t x @nc1 + Je=\'2019-03-27 08:40\' and, l 8 V , j 7 arrivalrec0_.product_id=22 and arrivalrec0_.station_no=\'S7100\'
arrival_record arrivalr/ 1 *ec0_ where arrivalrec0_.sequence=\'2019-03-27 08:40\' and arrivalrec0_.U 8 z 6 m 7 ) J #proc { 8 xduct_id=24 and arrival~ U k Hrec0_.station_no=\'V4631\'
arrival_record arrivalrec0_ where arrivalrec4 - A s ( d t0_.sd : , ; y % Tequence=\'2019? f a-03-27 08:40\n J' and arrivalP [ [ 2 K a Trec0_.product_id=22 and arrivalreI # k V V 1 ` Wc0_.station_no=\'S9466\* _ X , Z t m 9 '
arrival_record arrivalrec0_ whJ 2 g Cere arrivalrec0_.sequence=\'2019-03-27 08:40\' and arrivalrec0_.product_id=2u R z H A4 an7 ; 3d arrivaq } @ T l lrec0_.station_no=\'V4205\'
arr- l 9 B sival_record arrivalrec0_ where arrivalrec0_.sequence=\'2019-03-U h Y % Y27 08:40\' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no=\'V4105\'
arrival_record arrivalrec0_ where arrivalrec0_.sequence=\'2019-03-27 08: # # J J z40\' andc 4 y P A r arrival{ m k @ [ krec0_.product_id=24 and arrivalrec0_.statiH / H u - F ;on_no=\'V4x $ N f ]506\'
arrival_record arriva; + / K 5 ? # Qlrec0_ where arrivalrec0_.sequence=\'2019-03-27 08:40\' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no=\'V4617\'
arrival_record arrivalrec0_ where arrivalrec0_.sequence=\'2019-03-27 08:40\' a4 ? Q V 4 z & u ind arrivalrec0_.product_id=22 and arQ v ; rivalrec0_.station_no=\'S8356\'
arrival_record arrivalrec0_ whert ? $ r # ` H O }e arrivalrec0_.sequence=\'2019-03-27{ 7 c { S g K 08:40\' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no=Y w ;\'S8356\'select 该表 where 条件中有 product_idn , R f C B ^ }，station_no，sequence 字段，可以使用到复合索引 IXFK_arrival_rs G Y r P Eecord 的前三个字段。
综上所示，优化方法为：
- 删除复合索引 IB b PXFK_arrival_recorm S l : / $d
- 建立复合索引 idx_sequenH D { k U Qce_statioZ z d E = $n_no_product_id
- 建立单独索引 indx_receive_time
delete xxx_record 语句
该 delete 操作平均扫描M ^ E [ d e Z f行数为 1.1 亿行，平均执行时间是 262s。
delete 语句如下所示，每次记录的慢查询传入的参数值不V J 9 x d m一样：
delete from arrival_record where rece g _ deive_time < STR_TO_DATE(\'2019-02-23\', \'%Y-%m-%d\')\\G 执行计划：
explait l wn se- G E % a W l xlect * from arrival_record where receive_time < STR/ f m d /_TO_DATE(\'2019-02-23\', \'%Y-%m-%d\')\\G
*o = Y************************** 1. row ***************************
id: 1
se~ 1 plect_type: SIMPLE
table: arrival_record
partitions: NULL
type: ALL
poss{ 5 * w % # f |ible_kK z P % f reys: NULL
key: NULL
key_len: NULL
re{ z H if: NUq S , 5 7 8 s G 7LL
rows: 109501508
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec) 该 delete 语句没有使用索引(没有合适的索引可用)，走的全表扫描，导致执行时间长。
优化方法也是：建立单独索引 indx_receive_time(receive_time)。
拷贝 arrival_rec- K Y tord 表到测试实例上进行删除重新索引操作。
XX 实例 arrival_re^ l t Ycord 表信息：
du -sh /datas/mysql/data/3316/cq_new_cimiss/arrival_record*J 8 d 7
12K /datas/mysql/data/33h ~ s k x16/cq_new_cimiss/arrival_record.frm
48G /datas/mysql/data/3316/cq_new_cimiss/arrival_record.ibd
select count() from cq_new_cimiss.arrival_recg H L ` s ; b Jord: p , J R P x t;
+-----------+J W G Z Z 9 t A
| count() |
+-----------+
| 11229494G 0 X d t6 |
+-----------+
1亿多记录数
SELECT
table_name,
CONY Z u Y _ t Z NCAT(FORMAT(SUM(dZ 6 2 B 4 K hata_lengthJ J # ~ ; E 5 ] o) / 1024 / 1024,2),\'M\') AS dbdataR % d y P_size,
C R X nONCAT(F7 | k * ? f (ORMAT(SUM(index_length) / 1024 / 1024,2),\'M\') AS dbindex_size,
CONCAT(FORMAT(SUM(data_l H $length +B U G ^ v L I K Q index_lengt= v & W v Nh) / 1L 1 I j 8 q & v B024 / 1024 / 1024,2),\'G\') AS table_s~ U # k c I Bize(GA D B),
AVG_ROW_LENGTH,table_rows,update_timD I N @ Ae
FROM
information_schema.tables
WHERE table_sc. 5 ; l Q { Hhema = \'cq_new_cimiss\' and table_J R _ t u Y #name=\'_ K E karrival_record\';
+----------------+-------------+------------C d v n [--+------------+--o # U k B R 2 A--------------+---------Q ( A [ | h z E---+--------------G V V j N [ f ! w-------+
| table_name | dbdata_size | dbindex_size | table_size(G) | AVG_ROW_LENGTH | tablP S i , I 9 G 6 %e_rowE + ; S , _s | update_time |
+----------------+-------------+-----------e ; Q ~ h---+-Q S k | N c-----------+--------- ) E ( P L 2 j--) ! O C Q ? 3 , y-----+------------+---------------------+
| arrival_record | 18,268.6 . % . N u02M | 13,868.05M | 31.38G | 175 | 109155053 | 2019-03-26 12:40:17 |
+----------------+-------------+--------------+------------+H _ + s ) l ----------------+------------+---------------------+ 磁盘占用空间 48G，MySQL 中该表大小为 31G，存在{ 1 [ ? M ! S 17G 左右的碎片，大多由于删除操作造成的。(记录被删除了，空间没有回收)
备份还原该表到新的实例中，删除原来的复合索引，重新添加索引进B R W ? t行测试。
mydumper 并行压缩备份：
user=root
passwd=xxxx
socket=/k u - 7 # t # Ldatas/mysql/data/3316/mysqld.sock
db=cq_new_cimiss
table_name=arrival_record
backupdir=/datas/du& F @ H ~mp_$table_name
mkdir -p $backupdir
nohup echo `date +%T` && mydumper -u $user -p $r E % 6 0 I g A Epasswd -S $socket -B $db -c -T $table_name -o $backupdir -t 32 -r 2000000 && echo `date +%T`6 9 ] E 1 C + b &并行压缩备份所花时间(52s)和占用空间(1.2G，实际该表B @ l 2 Y 1 w l ,占用磁7 W L o Z X盘空间为 48G，mydumper 并行压缩备份压缩比相当高)：
Started dump at: 2019-03-26 12:46:04
.....F Q _ } i U S a -...
Finished dump at: 2019-03-26 12:46:56
du -sh /dam X ? # 6 ? 0 Qtas/dump_arrW b + i , ? O :ival_record/
1.2G /datas/dump_arrival_record/ 拷贝 dumpX + 5 K p 数据到测试节点：
scp -rp /datas/dump_arrivg L J /al_record( _ h j ^ O Q root@10.230.126 q M 5 * j L4.19:/datas 多线程导入数据：
time myloader -u root -S /datas/mysql/data/3308/mysqld.sock -P 3308 -p root -B test -d /datas/dump_arrival_record -t 32
real 126m- C h & ; 8 a42.885s
user 1m4.543s
sys 0m4.M % v i e - ? u267s 逻辑导 t S { r | i / A入该表后磁盘占用空间：
du -h -d 1 /datas/mysql/data/3308q ~ 6 2 7 a A Z/d L i - n U ~ ^test/arrival@ ! ` ; $ t t_record.*
12K /datas/mysql/data/3308/test/ar6 ( g Y # - 4 Krival_record.frm
30G /datasK S w/mysql/data/33( / 408/test/arrival_record.ibd
没有碎片，和mysql的该表的大小一致
cp -rp /datat 7 q X 8 D }s/mysql/data/3308 /datas 分别使用 online DDL 和 pt-osc 工具来做删除重建` U B W U $索引操作。
先删除外键，不删除外键，无法删除复合索引，外键列属于复合索引中第一列：
nohup bash /tmp/ddl_index.sh &
2019-04-04-10:41:39 begin stop mysqld_3308
2019-04-04-10:41:41 begin rm -rf datadir and cp -r= U G I 7 / ip datadir_bak
2019-04-04-10:46:53 start mysqld_3308
2019-04-04U ` N J T i k r-10:46r S x 0 * U 2 W O:59 online ddl begin
2019-04-04-11:20:34 onlie ddlC f , t a ; stop
2019-04-04s @ b E 1 i E-11:20:34 begin stop mysqld_G ` d B3308
2019-04-04-11:20:36 begin rm -rf datadir and cp -rp datadir_bak
2019-04-04-11:22:48 start mysqld_3308
2019-04-04-11:22:53 pt-osc begin
2019-04-04-12:19:15 pt-osc stop online DDL 花费时间为 34 分钟，pt-osc 花U [ j @ a费时间为 57` + s 分钟，使用 onlE ! A 6 U 3 s R one DDL 时间约为 pt-osc 工具时间的一半。
做 DDL 参考：
实施
由于是一主一从实例，应用是连接的 vip，删除重建索引采用 online DDL 来做。
停止主从复制后，先在从实例上做(不记录 binlog)，主从切换，再在新切换的从实例上做(不记录 binlog)：
fH S W cunction red_echo () {
local what=\"$*\"
echo -e \"$(date +%F-%T) ${what}\"
}
function check_las_comm(){
if [ \"$1\" != \) r S Q"0\" ];J / [then
red_echo \"$2\"
echo 7 U 0 gP X q % W"exit 1\"
exiz c } Pt 1
fi
}
red_echo \"stop slave\"
mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -e\O z e } d h Z"stop slave\"
check_las_comm \"p U X$?\" \"stop slave failed\"
red_echo \"onlinG N @ te ddl begin\"
mysql -uroot -p$passwa C R # Pd --socket=/datas/mysql/data/${porU P a # i f U U ^t}/mysqld.sock -e\"set sql_log_bin=0;selecD W F H = | B I /t now() as ddl_start;ALTER TA7 3 p d ^ s d `BLE $db_.\\`${table_name}\O N ~` DROP FOREIGN KEY FK_arrivP k sal_record_5 # ^ 1 P ` produf / ) h Z $ +ct,drop index IXF: 9 C 8K_arrival_record,add index idx_product_id_sequence_station_no(product_id,sequence,station& 2 3 j 2 , ? i :_no),add index idx_receive_time(receive_time);select now() as ddl_stopK s 1 n ^\" >>${log_file} 2>& 1
re1 B d A H A ,d_echoE a G : ] D ? a 7 \"onlie dd1 w / ql stop\"
r) 7 s 9ed_echo \"add foreign key\"
mysql -p q Ruroo6 m a 8 2 w { e Lt -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -_ q = &e\"set sql_log_bin=0;ALTER TABLE $db_.${table_nameR $ 2 R} ADa Q / C a GD CONSTRAINT _FK_${table_name}_X G D c b m /product FOREIGN KEY (product_id) REFERENCES c d W ` Z G P u Tq_new_cimiss.produD 2 I ] * act (id)g D ON DELETE NO ACTION ON UPB g R 6 +DATE NOB w w = Ab u - ` CCTION;\" >>${log_file} 2>& 1
check_las_comm \"$?\" \"add foreign kO 3 | sey error\"
red_echo \"add foreign key stop\"
red_echo \"start slave\"
mysql -uroot -p$passwd --socket=/datas/mysql1 e T S ` J g j s/data/${port}/myv N e ) _sqT ^ O Q i fld.sock -e\"start slave\"
check_las_comm \"$?\" \"start slave failed\" 执行时间：* 5 ` V M E Q {
2019-04-08-11:17:^ i 7 h + J ) ~ F36 stop slave
mysql: [Wa; v _rning] Using a password on the command line interface c` i 9 R e e I I Jan be insecure.
ddl_start
2019-04-08 11:17:36p Z =
ddl_stop
2019-04-08 11:45:13
2019-04-08-11:45:1e n 1 D3 onlie? r ( ( [ 3 U f ddl stop
2019-04-08-11:45:13 add foreign key
mysql: [Warning] Using a password on the command l. p Pine interface can be insecure.
2019-04-08-12:33:48 add( I Q P a 3 foreign key stop
2019-04-08-12:33:48 start slaW B B . Ove 删除重建索引花费时间为 2F a b I8 分钟，添加外键约束时间为 48 分U I [钟。
再次查看g i M % L A Y delete 和 select 语句的执行计划S Y ( M N Y Y 8 4：
explain select count(*) from arrival_record where rd i R ? 6eceive_time < S* ; 9 h q W U )TR_TO_DATE(\'2019-03-10\', \x G r 8 t _ s'%Y-%m-%d\')\\G
*******4 ] d 0 R******************** 1. row ***************************
id: 1
select_type: SIMPLE
table: arrival_2 w t ; ^ . 8record
partitions: NULL
type: range
possible_keys: id8 % cx_receive_time
key: idx_receive_time
key_len: 6
ref: NULL
rows: 7540948
filtered: 100.00
Extra: Using where; Using index
explain select count(*) from arrival_record where product_id=26 and receiveB [ Y ( ? u_ti4 9 E e . p Lme between \'2019-03-25 14] @ $ { #:00:00\' and \'2019-03-25 15:00:00\' and receive_spend_ms>=08 f J\G;
************************O 6 E q ) I ^ = 2*** 1. row ***************************
id: 1
selk e M P a V D $ [ect_type: SIMPLE
table: arrival_recoX A V ; 2 . Urd
parti^ ; 0 n m D Q Q ztions: NULL
type: range
possible_keys: idx_product_id_sequence_station_no,idx_receive_time
key: idx_receive_time
key_len: 6
ref: NULL
rows: 291448
filtered: 16.66
Extra: Using index condition; Using where 都使用到了 idx_receive_time 索引，扫描的行数大大降低{ F ; {。
索引优化后
delete 还是花费了 77s 时间：
delete from arrival_record where receive_time < STR_TO_DATE(\F / M R r'2019-03-10\', \'%YR J z N-%m-%d\')\\G
delete 语句通过 receive_time 的索引删除 300 多万的记录花费 77s ^ I @ v 时间。
de7 O q s V 3lete 大表优化为小批量删除
应用端已优化成每次删除 10 分钟的数据(每次执行时间 1s 左右)，xxx 中没在出现 SLA(主从延 - y o d 0 & (迟告警)：
另一个方法是通过主键的顺序每次删除 20000 条记录：
#得到满足时间条件的最大主键ID
#通过按照主键的顺序去 顺序扫描小批量删除数据
#先执行一次以~ 0 4 _ t l q B `下语句
SELECT MAX(id) INTO @need_delete_max_id FROM `arrival_record` WHERE receive_time<\'2019-03-01\' ;
DELETE FROM arrival_record WHE) 8 2 p | : ARE id<@need_delete_max_id LIMIT 20000;
select ROW_COUNT(); #返回20000
#执行小批量delete后会返回row_count(), 删除的行数
#程序判断返回的row_count()是否为0，不为0执行以下循环，H 6 Z l # c W # $为0退出循环，删除操f U G / 0 ]作完成
DELETE FROM arrival_record WHERE id<@need_delete_max_id LIMIT 20000;
select ROW_COUNT();
#程序睡眠0.5s 总O 7 / ^ % X , |结
表数据量太大时，除了关注访问该表的响V X J应时间外，还要关注对该表的维护成本(如做 DDL 表更时间太长，delete 历史数据)。
对大表进行 DDL 操作时，要考虑表的实际情况(如对该表的并3 W R 2 +发表，是 | Y ` D Y 2 否有外键)来选择合适的 DDL 变更方式H 3 q Z。
对大数据量表进行 delete，用小批量删除的方式，减少对主实例的压力和[ , [ 3 S主从延迟。
作者：jia-xiC C s @ ? D a N !n
来源：博客园
出处：U v S v W 0 w nhttpsp ! 4://www.cnblogs.8 2 [ O b }com/YangJiaXin/p/108282448 J N ^ h z h.html
本文系本站编辑转载，文章版权归原作者所有，内容为作者个人观点，转载目的在于传递更多信息，并不代表本站赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题，请与本站联系，本站将在第一时间删除内容！