2022年MySQL最新面试题第9篇 - MySQL部署和运维 【完结】

2022年MySQL最新面试题第9篇 - MySQL部署和运维 【完结】

0、概要

1、如何更新给一个大表建索引2、如何批量删除N行记录, 有什么注意事项3、如何删除表？4、MySQL如何扩容5、如何排查因为MySQL导致CPU占用高的问题？6、MySQL数据库磁盘IO使用高，请问如何进行排查？7、如何批量插入大量数据?8、数据备份和恢复

1、如何更新给一个大表建索引

出现概率: ★★★★

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

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

方式1、“影子策略”

创建一张与原表（tb）结构相同的新表（tb_new）在新表上创建索引重命名原表为其他表名（tb => tb_tmp），新表重命名为原表名（tb_new => tb），此时新表（tb）承担业务为原表（tb_tmp）新增索引交换表，新表改回最初的名称（tb => tb_new），原表改回最初的名称（tb_tmp => 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._user_old, dbteamdb._user_new 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 table_name 会立刻释放磁盘空间，并重置auto_increment的值，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参数，比如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等；7)、如果数据量过大，可以考虑使用MySQL集群或者搭建高可用环境。8)、考虑磁盘是否满了

6、MySQL数据库磁盘IO使用高，请问如何进行排查？

出现概率: ★★★

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

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

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

1、慢SQL排除2、硬件问题-RAID降级，磁盘故障等排除3、innodb_log、innodb_buffer_pool_wait相关配置4、IO相关参数配置

innodb_flush_method = O_DIRECTinnodb_file_per_table = 1innodb_doublewrite = 1delay_key_writeinnodb_read_io_threadsinnodb_read_io_threadsinnodb_io_capacityinnodb_flush_neighborssync_binlog

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

mysql> show variables like '%sync_bin%';+--------------+-------+| Variable_name | Value |+---------------+-------+| sync_binlog | 1 |+---------------+-------+1 row in set (0.00 sec)

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

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

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

7、如何批量插入大量数据?

出现概率: ★★★

1)、自己也循环一条一条插入, 缺点时因为频繁建立连接, 比较耗时

2)、减少连接资源，拼接一条sql，这样写一次正常插入一万条基本问题不大，除非数据很长，应付普通的批量插入够用了，比如：批量生成卡号，批量生成随机码等等。

3)、使用存储过程

delimiter $$$create procedure zqtest()begindeclare 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 testFIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'IGNORE 1 LINES(id, name)

时间对比:

经过测试插入1w条数据时候与拼装批量插入语句时间差别不大，当插入数量达到10w出现了明显的时间差：拼装批量插入语句花费时间：6.83sLOAD 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=--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来管理, 数据只能靠我们自己进行备份、只有平时做好了操作， 才能在关键时刻恢复。 比如这个经典案例, 如果平时做好了演练, 也就没有那么多事情了:

希望这篇文章可以帮助大家, 也希望大家都能找到找到的好工作。