数据库部署和运维

0、概要

1、如何更新给⼀个⼤表建索引

2、如何批量删除N⾏记录, 有什么注意事项

3、如何删除表？

4、MySQL如何扩容

5、如何排查因为MySQL导致CPU占⽤⾼的问题？

6、MySQL数据库磁盘IO使⽤⾼，请问如何进⾏排查？

7、如何批量插⼊⼤量数据?

8、数据备份和恢复

1、如何更新给⼀个⼤表建索引

出现概率: ★★★★

这个问题考察的点: 线上⾼并发下的添加⼤表建索引

当表数据量很⼤时，建⽴索引或者修改表结构会很慢，⽽且在操作的过程中，数据库甚⾄处于死锁状态，那么有没有其他的好的办法呢？

⽅式1、“影⼦策略”

创建⼀张与原表（tb）结构相同的新表（tb_new）

在新表上创建索引

重命名原表为其他表名（tb => tbtmp），新表重命名为原表名（tbnew => tb），此时新表（tb）承担业务

为原表（tb_tmp）新增索引

交换表，新表改回最初的名称（tb => tbnew），原表改回最初的名称（tbtmp => tb），原表（tb）重新承担业务

把新表数据导⼊原表（即把新表承担业务期间产⽣的数据和到原表中）

# 以下sql对应上⾯六步

create table tb_new like tb;

alter table tb_new add index idx_col_name (col_name);

rename table tb to tb_tmp, tb_new to tb;

alter table tb_tmp add index idx_col_name (col_name);

rename table tb to tb_new, tb_tmp => tb;

insert into tb (col_name1, col_name2) select col_name1, col_name2 from tb_new;

在⽣产环境, 这个⽅式有⽐较严重的问题, 步骤3之后，新表改为原表名后（tb）开始承担业务，步骤3到结束之前这段时间的新产⽣的数据都是存在新表中的，但是如果有业务对⽼数据进⾏修改或删除操作，那将⽆法实现，所以步骤3到结束这段时间可能会产⽣数据（更新和删除）丢失。

⽅式2、利⽤辅助⼯具 Percona Toolkit 是⼀组⾼级的命令⾏⼯具，⽤来管理 MySQL 和系统任务，主要包括：

验证主节点和复制数据的⼀致性

有效的对记录⾏进⾏归档

找出重复的索引

总结 MySQL 服务器

从⽇志和 tcpdump 中分析查询

问题发⽣时收集重要的系统信息

在线修改表结构

⼯作原理如下:

如果存在外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理。没有使⽤ alter-foreign-keys-method 指定特定的值，该⼯具不予执⾏

创建⼀个新的空表，其命名规则是：下划线 + 原表名 +new—-原表名_new

根据 alter 语句，更新新表的表结构；
创建触发器，⽤于记录从拷⻉数据开始之后，对源数据表继续进⾏数据修改的操作记录下来，⽤于数据拷⻉结束后，执⾏这些操作，保证数据不会丢失。如果表中已经定义了触发器这个⼯具就不能⼯作了。

拷⻉数据，从源数据表中拷⻉数据到新表中。

修改外键相关的⼦表，根据修改后的数据，修改外键关联的⼦表。

rename 源数据表为 old 表，把新表 rename 为源表名，其通过⼀个 RENAME TABLE 同时处理两个表，实现原⼦操作。（RENAME TABLE dbteamdb.user TO dbteamdb.userold, dbteamdb.usernew TO dbteamdb.user）

将 old 表删除、删除触发器。

⽅式3、凌晨进⾏维护， 添加索引, ⼀部分公司也是这么做的， 但是不够灵活，遇到⼀些紧急情况还是⽅式2更好些。

2、 如何批量删除N⾏记录, 有什么注意事项

出现概率: ★★★★

因为批量删除⼤表⼤量数据时, 数据会锁表, 在业务⾼峰时会导致 数据库CPU暴涨, 降低服务器性能, 可能会造成数据库雪崩现象。

这个也是每个线上开发⼈员可能遇到的业务场景。

⽅式1、删除⼤表的多⾏数据时，会超出innod block table size的限制，最⼩化的减少锁表的时间的⽅案是：

选择不需要删除的数据，并把它们存在⼀张相同结构的空表⾥

重命名原始表，并给新表命名为原始表的原始表名

删掉原始表

这个会有⼀个问题， 正式环境删除间隙的数据同步问题。

⽅式2、将批量删除任务拆减成 N个⼩删除任务, ⽐如只删1w条后, 将删除任务压⼊到异步队列等待执⾏.

⽅式3、删除表上的索引, 在删除数据, 可以提⾼效率, 但是不太适合线上环境.

3、如何删除表

出现概率: ★★★★

这个是如何批量删除N⾏记录的延伸问题.

如果我们清空表数据，建议直接使⽤truncate，效率上truncate远⾼于delete，在另⼀篇⽂章讲mysql的，可以得知，我们truncate不⾛事务，不会锁表，也不会产⽣⼤量⽇志写⼊⽇志⽂件，我们访问log执⾏⽇志可以发现每次delete都有记录。truncate table tablename 会⽴刻释放磁盘空间，并重置autoincrement的值，delete 删除不释放磁盘空间，insert会覆盖之前的数据上，因为我们创建表的时候有⼀个创建版本号。

4、MySQL如何扩容

出现概率: ★★★

⽬前可⽤⽅案

1)、MySQL的复制：
⼀个Master数据库，多个Salve，然后利⽤MySQL的异步复制能⼒实现读写分离，这个⽅案⽬前应⽤⽐较⼴泛，这种技术对于以读为主的应⽤很有效。 数据切分（MySQL的Sharding策略）：
垂直切分：⼀种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；垂直切分的思路就是分析表 间的聚合关系，把关系紧密的表放在⼀起。

⽔平切分：另外⼀种则是根据表中的数据的逻辑关系，将同⼀个表中的数据按照某种条件拆分到多台数据库（主机）上⾯，这种切分称之为数据的⽔平（横向）切分。

2)、通过集群扩展：MySQL Cluster(NDB Cluster)

类似于MongoDB的动态扩容策略。

MySQL Cluster是⼀套具备可扩展能⼒、实时、内存内且符合ACID要求的事务型数据库，其将99.999%⾼可⽤性与低廉的开源总体拥有成本相结合。在设计思路⽅⾯，MySQL Cluster采⽤⼀套分布式多主架构并借此彻底消灭了单点故障问题。MySQL Cluster能够横向扩展⾄商⽤硬件之上，能够通过⾃动分区以承载读取与写⼊敏感型⼯作负载，并可通过SQL与NoSQL接⼝实现访问。

采⽤NDB存储引擎，有数据节点，SQL节点，和管理节点（1个，配置要求低）

3)、分库分表分区

5、如何排查因为MySQL导致CPU占⽤⾼的问题？

出现概率: ★★★
在问题出现之前, 我们需要做的是加强监控, ⽐如当CPU暴涨到85%时就进⾏报警, 这样当有问题时, 开发⼈员和运维⼈员可以第⼀时间收到报警, 并处理， ⽽不是让⽤户报出来: 你家APP是不是⼜崩了, 卡死了。

我们平时遇到⼤部分MySQL导致CPU占⽤⾼的情况是因为慢查询实例出现CPU飙升。这种情况表现是QPS（每秒执⾏的查询次数）不⾼；查询执⾏效率低、执⾏时需要扫描⼤量表中数据。此时可能是由于存在慢查询导致，查询执⾏效率低，为了获取预期的结果就需要访问⼤量的数据，导致平均逻辑IO增⾼，此时就会是CPU利⽤率过⾼。

此时需要DBA，定位效率低的查询、优化查询的执⾏效率、降低查询执⾏的成本。

具体定位的过程可以参考：

1)、通过在控制台下载慢查询⽇志，定位效率低的SQL并进⾏优化
2)、也可以通过  show processlist ; 或  show full processlist ; 命令查看当前执⾏的查询，反复执⾏，找到慢查询的process ID ，运⾏kill 慢查询的id 命名杀掉慢查询进程。

同时MySQL处在⾼负载环境下，磁盘IO读写过多，也会占⽤很多资源，必然会使CPU占⽤过⾼。CPU过⾼，可以从下⾯⼏个⽅向进⾏排查问题和优化：1)、打开慢查询⽇志，查询是否是某个SQL语句占⽤过多资源，如果是的话，可以对SQL语句进⾏优化，⽐如优化 insert 语句、优化 group by 语句、优化 order by 语句、优化 join 语句等等；

2)、考虑索引问题；

3)、定期分析表，使 optimize table ；

4)、优化数据库对象；

5)、考虑是否是锁问题；
6)、调整⼀些MySQL Server参数，⽐如keybuffersize、tablecache、innodbbufferpoolsize、innodblogfile_size等等；7)、如果数据量过⼤，可以考虑使⽤MySQL集群或者搭建⾼可⽤环境。

8)、考虑磁盘是否满了

6、MySQL数据库磁盘IO使⽤⾼，请问如何进⾏排查？

出现概率: ★★★

我们以mysql5.7版本为例，结合 performance_schema 来查看MySQL数据库的各种指标。IO的话，可以查看这张表：

performance_schema.file_instances ：列出了⽂件I O操作及其相关⽂件的⼯具实例

⼀般遇到问题时的排查思路：

1、慢SQL排除

2、硬件问题-RAID降级，磁盘故障等排除

3、innodblog、innodbbufferpoolwait相关配置

4、IO相关参数配置

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_doublewrite = 1

delay_key_write

innodb_read_io_threads

innodb_read_io_threads

innodb_io_capacity

innodb_flush_neighbors

sync_binlog

其中可以主要关注：sync_binlog, binlog刷新的参数，默认是1。

mysql> show variables like '%sync_bin%';

+--------------+-------+

| Variable_name | Value |

+---------------+-------+

| sync_binlog   | 1   |

+---------------+-------+

1 row in set (0.00 sec)

syncbinlog为1, 表示每次事务提交后MySQL将进⾏⼀次fsync之类的磁盘同步指令来将binlogcache中的数据强制写⼊磁盘，频繁的写盘导致磁盘IO居⾼不下，将sync_binlog调整为500。

⼤家都知道,影响数据库最⼤的性能问题就是磁盘IO，为了提升数据库的IOPS，可以使⽤SSD或者PCIE-SSD⾼速磁盘。内存⽅⾯也很重要，内存可以缓存热点数据和存储引擎⽂件，避免产⽣过多的物理IO，可以增加物理内存来提⾼数据库的并发和读写性能。

最后也建议：最好部署相关的监控平台或者对⽐历史性能记录，结合业务以及负载来分析。

7、如何批量插⼊⼤量数据?

出现概率: ★★★

1)、⾃⼰也循环⼀条⼀条插⼊, 缺点时因为频繁建⽴连接, ⽐较耗时

2)、减少连接资源，拼接⼀条sql，这样写⼀次正常插⼊⼀万条基本问题不⼤，除⾮数据很⻓，应付普通的批量插⼊够⽤了，⽐如：批量⽣成卡号，批量⽣成随机码等等。

3)、使⽤存储过程

delimiter $$$

create procedure zqtest()

begin

declare i int default 0;

set i=0;

start transaction;

while i<80000 do

 //your insert sql

set i=i+1;

end while;

commit;

end

$$$

delimiter;

call zqtest();

4)、使⽤MYSQL LOCAL_INFILE

LOAD DATA LOCAL INFILE 'F:\\milo.csv' INTO TABLE test

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES

(id, name)

时间对⽐:

经过测试插⼊1w条数据时候与拼装批量插⼊语句时间差别不⼤，当插⼊数量达到10w出现了明显的时间差：

拼装批量插⼊语句花费时间：6.83s

LOAD DATA LOCAL INFILE实现⼤批量插⼊花费时间：1.23s

当表格的字段更多数据量更⼤出现的时间差就越⼤。

总结：当需要进⾏⼤批量数据插⼊的时候，可以优先考虑LOAD DATA LOCAL INFILE实现⽅式。

8、数据备份和恢复

出现概率: ★★★★

Mysql 提供了 mysqldump、ibbackup、replication ⼯具来备份, 关于备份需要了解的⼏个概念:

1)、数据备份按备份类型：

热备：在数据库运⾏过程中直接备份， 读写操作均可执⾏

冷备：在数据库停⽌的情况下备份，读写操作均不可进⾏，⼀般直接复制相关的物理⽂件即可
温备：在数据库运⾏过程中备份， 读操作可执⾏；但写操作不可执⾏，但对数据库操作有影响，如加个全局读锁以保证备份数据⼀致性

关于热备原理:

双机热备就是使⽤MySQL提供的⼀种主从备份机制实现。所谓双机热备其实是⼀个复制的过程，复制过程中⼀个服务器充当主服务器，⼀个或多个服务器充当从服务。这个复制的过程实质上是从服务器复制主服务器上MySQL的⼆进制⽇志（bin-log），并在从服务器上还原主服务器上的sql语句操作，这样只要两个数据库的初态是⼀样的，就能⼀直同步。

双机热备的实现需要MySQL的版本⾼于3.2.。另外由于这种备份是基于MySQL⼆进制⽇志实现，所以主从服务器上的MySQL版本最好能够⼀样，⾄少从服务器的MySQL版本不可以低于主服务器的数据库版本。因为MySQL不同的版本之间⼆进制⽇志可能不⼀样。

当然这种复制和重复都是MySQL⾃动实现的，我们只需要配置即可。

上图中有两个服务器， 演示了从⼀个主服务器（master） 把数据同步到从服务器（slave）的过程。

这是⼀个主-从复制的例⼦。 主-主互相复制只是把上⾯的例⼦反过来再做⼀遍。

2)、按备份内容：

⽇志备份：主要备份 bin-log ⽇志，然后 replay 来完成 point-in-time

完全备份：对数据库⼀个完整的备份
增量备份：在上次完全备份的基础上对更改部分进⾏备份（MySQL 没真正的增量备份，⼀般通过 bin-log 完成，要借助第三⽅⼯具才能实现）3)、按备份⽂件：

逻辑⽂件：指备份出的⽂件可读，⼀般指 SQL 语句（适⽤库升级，迁移，但恢复时间较⻓需要执⾏ SQL 语句）

物理⽂件：指复制数据库的物理⽂件

其中 mysqldump 是属于逻辑备份，也是最常⻅的备份⼯具了，缺点在于备份和恢复速度不是特别快

4)、最后关于备份数据, 别忘了我们也要备份 binlog
如果有 DBA 告诉你，这个数据库能够恢复到两个个⽉内任何状态，这说明了，这个数据库的 binlog ⽇志⾄少保留了两个⽉。备份 binlog 的好处: 可以实现基于任意时间点的恢复

可以⽤于误操作数据闪回

可以⽤于审计

5)、关于备份恢复
a)、对于使⽤mysqldump进⾏逻辑备份的⽂件，  mysql -u root -p [dbname] < backup.sql  进⾏恢复即可
b)、xtrabackup 备份全量恢复
Percona-xtrabackup是 Percona公司开发的⼀个⽤于MySQL数据库物理热备的备份⼯具，⽀持MySQL、Percona server和MariaDB，开源免费，是⽬前较为受欢迎的主流备份⼯具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，⽽不能备份MyISAM数据表。

MySQL冷备、mysqldump、MySQL热拷⻉都⽆法实现对数据库进⾏增量备份。在实际⽣产环境中增量备份是⾮常实⽤的，如果数据⼤于50G或100G，存储空间⾜够的情况下，可以每天进⾏完整备份，如果每天产⽣的数据量较⼤，需要定制数据备份策略。例如每周实⽤完整备份，周⼀到周六实⽤增量备份。⽽Percona-Xtrabackup就是为了实现增量备份⽽出现的⼀款主流备份⼯具，

xtrabackup包含两个主要的⼯具，即xtrabackup和innobackupex，⼆者区别如下：

（1）xtrabackup只能备份innodb和xtradb两种引擎的表，⽽不能备份myisam引擎的表；
（2）innobackupex是⼀个封装了xtrabackup的Perl脚本，⽀持同时备份innodb和myisam，但在对myisam备份时需要加⼀个全局的读锁。还有就是myisam不⽀持增量备份

Xtrabackup备份原理

Xtrabackup备份流程图（xtrabackup备份过程中，先备份innodb表，再备份⾮innodb表）：

恢复过程如下：

#步骤⼀：解压（如果没有压缩可以忽略这⼀步）

innobackupex--decompress<备份⽂件所在⽬录>

#步骤⼆：应⽤⽇志

innobackupex--apply-log<备份⽂件所在⽬录>

#步骤三：复制备份⽂件到数据⽬录

innobackupex--datadir=<MySQL数据⽬录>--copy-back<备份⽂件所在⽬录>

c)、基于时间点恢复

基于时间点的恢复依赖的是 binlog ⽇志，需要从 binlog 中找过从备份点到恢复点的所有⽇志，然后应⽤。

show variables like 'log_bin%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_basename                |       |

| log_bin_index                   |       |

| log_bin_trust_function_creators | OFF   |

| log_bin_use_v1_row_events       | OFF   |

+---------------------------------+-------+

5 rows in set (0.06 sec)

查看log_bin 配置是否启⽤ binlog。Mysql 8.0 默认开启 binlog。

如果log_bin 是关闭, 可以修改my.conf 打开. 这⾥不就展开讲binlog的恢复了，想深⼊了解的朋友可以⾃⼰研究⼀下。

结语: 对于⼀个在正式环境运⾏的系统来说, 数据是最重要的了, 代码我们可以通过git来管理, 数据只能靠我们⾃⼰进⾏备份、只有平时做好了操作， 才能在关键时刻恢复。 ⽐如这个经典案例, 如果平时做好了演练, 也就没有那么多事情了: