大家好,我是漫步coding, 最近在整理2022年MySQL最新面试题, 大家也可以通过我​​下面的博客地址在线阅读​​, 今天讲讲第9篇 - MySQL部署和运维。本文首发于公众号: 漫步coding

https://manbucoding.com/travel-coding/mysql/%E5%89%8D%E8%A8%80.html

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

2022年MySQL最新面试题目录

MySQL数据库基础知识​

MySQL索引​

MySQL存储引擎​

MySQL事务​

MySQL数据库读写锁​

MySQL视图​

MySQL触发器​

MySQL数据库优化​

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_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)

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()
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不同的版本之间二进制日志可能不一样。

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

当然这种复制和重复都是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表):

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

恢复过程如下:

#步骤一:解压(如果没有压缩可以忽略这一步)
innobackupex--decompress<备份文件所在目录>

#步骤二:应用日志
innobackupex--apply-log<备份文件所在目录>

#步骤三:复制备份文件到数据目录
innobackupex--datadir=<MySQL数据目录>--copy-back<备份文件所在目录>

c)、基于时间点恢复

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

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

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来管理, 数据只能靠我们自己进行备份、只有平时做好了操作, 才能在关键时刻恢复。比如这个经典案例, 如果平时做好了演练, 也就没有那么多事情了:

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

也欢迎关注我的公众号: ​​漫步coding​​, 回复: ​​mysql​​免费获取最新Mysql面试题汇总(含答案)。一起交流, 在coding的世界里漫步。