MySQL 面试指南

MySQL 版本类问题

你之前工作中使用的是什么版本的 MySQL？为什么选择这个版本？

知识点

MySQL 常见的发行版：官方社区版、官方企业版、Percona MySQL、MariaDB
各个发行版之间的区别及优缺点

如何决定是否要对 MYSQL 进行升级？如何进行升级？

在对 MySQL 进行升级前要考虑什么？

升级可以给业务带来的益处
- 是否可以解决业务上某一方面的痛点
- 是否可以解决运维上某一方面的痛点
升级可能对业务带来的影响
- 对原业务程序的支持是否有影响
- 对原业务程序的性能是否有影响
数据库升级方案的制定
- 评估受影响的业务系统
- 升级的详细步骤（演练）
- 升级后的数据库环境检查
- 升级后的业务检查
升级失败的回滚方案
- 升级失败回滚的步骤
- 回滚后的数据库环境检查
- 回滚后的业务检查

MYSQL 升级的步骤

对待升级数据库进行备份
升级 Slave 服务器版本
手动进行主从切换
升级 MASTER 服务器版本
升级完成后进行业务检查

最新的 MySQL 版本是什么？它有什么特性比较吸引你？

MySQL 8.0

特性

服务器特性
所有元数据使用 InnoDB 引擎存储，无 frm 文件系统表。采用 InnoDB 存储并采用独立表空间
支持定义资源管理组（目前仅支持 CPU 资源）
支持窗口函数
支持在线修改全局参数持久化
用户安全
默认使用 caching_sha2_password 认证插件
新增支持定义角色（role）
新增密码历史记录功能，限制重复使用密码

用户管理类问题

如何在给定场景下为某用户授权？

知识点

如何定义 MySQL 数据库账号？
用户名 @ 可访问控制列表：hefery@192.168.1.%
使用 CREATE USER 命令建立用户
MySQL 常用的用户权限
- Admin
  Create User：建立新的用户的权限
  Grant option：为其他用户授权的权限
  Super：管理服务器的权限
- DDL
  Create：新建数据库，表的权限
  Alter；修改表结构的权限
  Drop：删除数据库和表的权限
  Index：建立和删除索引的权限
- DML
  Select：查询表中数据的权限
  Insert：向表中插入数据的权限
  Update：更新表中数据的权限
  Delete：删除表中数据的权限
  Execute：执行存储过程的权限
如何为用户授权？
- 遵循最小权限原则
- 使用 Grant 命令对用户授权
  - grant select,insert,update,delete on db.tb to uesr@ip
  - revoke delete on db.tb from uesr@ip

如何保证数据库账号的安全？

知识点

数据库用户管理流程规范
密码管理策略
数据库用户管理流程规范
- 最小权限原则
- 密码强度策略
- 密码过期原则
- 限制历史密码重用原则

如何从一个实例迁移数据库账号到另一个实例？

对比数据库版本是否一致
- 是就备份数据库，在目的实例恢复
- 否就导出授权语句（pt-show-grants u=root,p=123456,h=localhost），在目的实例执行

服务器配置类问题

请分析一个 Group By 语句的异常原因

结果集并未出现预期结果

SQL_MODE：配置 MySQL 处理 SQL 的方式
set [session/global/persist] sql_mode='xxxxxx'
[mysqld] sql_mode=xxxxxx
常见 SQL_MODE
ONLY_FULL_GROUP_BY：对于 GROUPBY 聚合操作，如果出现在 SELECT 中的列、HAVING 或者 ORDERBY 子句的非聚合列，没有在 GROUP BY 中出现，那么这个 SQL 语法检查报错
ANSL_QUOTES：禁止用双引号来引用字符串
REAL_AS_FLOAT：Real 做为 float 的同义词
PIPES_AS_CONCAT：将 || 视为字符串的连接操作符而非或运算符
STRICT_TRANS_TABLES/STRICT_ALL_TABLES：在事务存储引擎/所有存储引擎上启用严格模式出现，那么这个 SQL 语法检查报错
ERROR_FOR_DIVISION_BY_ZERO：不允许 0 做为除数
NO_AUTO_CREATE_USER：在用户不存在时不允许 Grant 语句自动建立用
NO ZERO IN DATE/NO_ZERO_DATE：日期数据内/日期数据不能含 0
NO_ENGINE_SUBSTITUTION：当指定的存储引擎不可用时报错

如何比较系统运行配置和配置文件中的配置是否一致？

知识点

使用 set 命令配置动态参数
set [session | @@session.]system_var_name=expr
set [global | @@global.]system_var_name=expr
set [persist | @@persist.]system_var_name=expr
使用 pt-config-diff 工具比较配置文件
pt-config-diff u=root,p=,h=localhost /etc/my.cnf

举几个 MySQL 中的关建性能参数

服务器配置参数
max_connections：设置 MySQL 允许访问的最大连接数量
interactive_timeout：设置交互连接的 timeout 时间
wait_timeout：设置非交互连接的 timeout 时间
max_allowed_packet：控制 MySQL 可以接收的数据包的大小
sort_buffer_size：设置每个会话使用的排序缓存区的大小
join_buffer_size：设置每个会话所使用的连接缓冲的大
read_buffer_size ：指定了当对一个 MYISAM 进行表扫描时所分配的读缓冲池大小
read_rnd_buffer_size：设置控制索引缓冲区的大小
binlog_cache_size 设置每个会话用于缓存未提交的事务缓存大小
存储引擎参数
innodb_flush_log_attrx_commit：
0：每秒进行一次重做日志的磁盘刷新操作。
1：每次事务提交都会刷新事务日志到磁盘中。
2：每次事务提交写入系统缓存每秒向磁盘刷新一次
innodb_buffer _pool_size：设置 Innodb 缓冲池的大小，应为系统可用内存的 75%
innodb_buffer_pool_instances：Innodb 缓冲池的实例个数，每个实例的大小为总缓冲池大小/实例个数
innodb_file_per_table：设置每个表独立使用一个表空间文件

日志类问题

常用的 MySQL 日志有那些？我们在什么情况下使用这些日志？

知识点

MySQL 常用的日志类型
各种日志的配置和使用场景

如何通过日志来审计用户活动？

存储引擎相关问题

说一说你了解的 MySQL 存储引擎及其适用场景

知识点

MySQL 常用的存储引擎
- MYISAM：不支持事务，最常用的非事务型存储引擎
- CSV：不支持事务，以 CSV 格式存储的非事务型存储引擎
- Archive：不支持事务，只允许查询和新增，不允许修改的非事务型存储引擎
- Memory：不支持事务，读写速度非常快，易失性非事务型存储引擎
- InnoDB：最常用的事务型存储引擎
- NDB：MySQL 集群所使用的内存型事务存储引擎
各种常见存储引擎的使用场景
- MYISAM
  - 特点
    非事务型存储引擎
    以堆表方式存储
    使用表级锁
    支持 Btree 索引，空间索引，全文索引
  - 适用场景
    读操作远远大于写操作的场景
    不需要使用事务的场景
- CSV
  - 特点
    非事务型存储引擎
    数据以 CSV 格式存储
    所有列都不能为 NULL
    不支持索引
  - 适用场景
    做为数据交换的中间表使用
- Archive
  - 特点
    非事务型存储引擎
    表数据使用 zlib 压缩
    只支持 Insert 和 Select
    只允许在自增 ID 上建立索引
  - 适用场景
    日志和数据采集类应用
    数据归档存储
- Memory
  - 特点
    非事务型存储引擎
    数据保存在内存中
    所有字段长度固定
    支持 Btree 和 Hash 索引
  - 适用场景
    用于缓存字典映射表
    缓存周期性分析数据
- InnoDB
  - 特点
    事务型存储引擎支持 ACID
    数据按主键聚集存储
    支持行级锁及 MVCC
    支持 Btree 和自适应 Hash 索引
    支持全文及空间索引
  - 适用场景
    大多数 OLTP 场景

在什么情况下 InnoDB 无法在线修改表结构？

知识点

InnoDB 不支持在线修改表结构的场景
加全文索引：CREATE FULLTEXT INDEX name ON table（column）
加空间索引：ALTER TABLE geom ADD SPATIAL INDEX（g）
删除主键：ALTER TABLE tbl_name DROP PRIMARY KEY
增加自增列：alter table t add column id int auto_increment not null primary key
修改列类型：ALTER TABLE tbl_name CHANGE c1 c1 NEW_TYPE
改表字符集：ALTER TABLE tbl_name CHARACTER SET=charset_name
如何更安全的在线修改表结构
pt-online-schema-change [OPTIONS] DSN

InnoDB 是如何实现事务的？

知识点

什么是事务？
原子性（A）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
一致性（C）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏
隔离性（I）：事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见
持久性（D）：事务一旦提交了，其结果就是永久性的，就算发生了宕机等事故，数据库也能将数据恢复
事务的实现方式
原子性（A）：回滚日志（Undo log）：用于记录数据修改前的状态
一致性（C）：重作日志（Redo Log）：用于记录数据修改后的状态
隔离性（I）：锁：用于资源隔离，分为共享锁和排它锁
持久性（D）：重作日志（Redo log）+ 回滚日志（Undo Log）

InnoDB 读操作是否会阻塞写操作？

知识点

InnoDB MVCC（多版本并发控制）的实现方式

查询需要对资源加共享锁（S）
数据修改需要对资源加排它锁（x）

MySQL 架构类问题

MySQL 的主从复制是如何工作

知识点

MySQL 主从复制的实现原理
MySQL 主从复制的配置步骤
在 MASTER 服务器上的操作
开启 binlog（必须）开启 gtid（可选）
建立同步所用的数据库账号
使用 master_data 参数备份数据库
把备份文件转输到 Slave 服务器
在 Slave 服务器上的操作
开启 binlog（可选）开启 gtid（可选）
恢复 Master 上的备份数据库
使用 Change master 配置链路
使用 start slave 启动复制

比较一下基于 GTID 方式的复制和基于日志点的复制

知识点

什么是基于日志点的复制
传统的主从复制方式
Slave 请求 Master 的增量日志依赖于日志偏移量
配置链路时需指定 master_log_file 和 master_log_pos 参数
什么是基于 GTID 的复制
GTID=source_id:transaction_id
Slave 增量同步 Master 的数据依赖于其未同步的事务 ID
配置复制链路时，SLAVE 可以根据已经同步的事务 ID 继续自动同步
这两种复制方式各自的特点

比较一下 MMM 和 MHA 两种高可用架构的优缺点

知识点

MMM 和 MHA 两种架构的作用
MMM 架构的优缺点及适用场景
MHA 架构的优缺点及适用场景

如何减小主从复制的延迟

知识点

主从复制延迟产生的原因
减小主从延迟的处理方法

说说你对 MGR 的认识

知识点

什么是 MGR 复制
不同于异步复制的多 Master 复制集群
如何使用 MGR 复制

如何解决数据库读/写负载大的问题？

知识点

如何解决读负载大的问题？读写分离
如何解决写负载大的问题？分库分表

备份恢复类问题

在之前的工作中是如何对数据库进行备份的？

知识点

备份方式
逻辑备份（SQL 文件）和物理备份
全量备份和增量备份以及差异备份
常用的备份工具
- mysqldump：最常用的逻辑备份工具，支持全量备份及条件备份
  备份结果为可读的 SQL 文件，可用于跨版本跨平台恢复数据
  备份文件的尺寸小于物理备份，便于长时间存储
  MySQL 发行版自带工具，无需安装第三方软件
  只能单线程执行备份恢复任务，备份恢复速度较慢
  为完成一致性备份需要对备份表加锁，容易造成阻塞。
  会对 Innodb Buffer Pool 造成污染
- mysqlpump：多线程逻辑备份工具，mysqldump 的增强版本
  支持基于库和表的并行备份，可以提高逻辑备份的性能
  支持使用 ZLIB 和 Lz4 算法对备份进行压缩
  对大表性能较差
- xtrabackup：Innodb 在线物理备份工具，支持多线程和增量备份
  支持 Innodb 存储引擎的在线热备份，对 Innodb 缓冲没有影响
  支持并行对数据库的全备和增量备份
  备份和恢复效率比逻辑备份高
  做单表恢复时比较复杂
  完整的数据文件拷贝，故备份文件比逻辑备份大
  对跨平台和数据库版本的备份恢复支持度不如逻辑备份

如何对 MYSQL 进行增量备份和恢复？

使用 xtrabackup 进行增量备份

如何对 binlog 进行备份？

备份方式
利用 cp 命令进行离线备份
使用 mysqlbinlog 命令在线时实备份

管理及监控类问题

说说你都对 MySQL 进行过那些监控？

性能指标
QPS：数据库每秒钟处理的请求数量
TPS：数据库每秒钟处理的事务数量
并发数：数据库实例当前并行处理的会话数量
连接数：连接到数据库会话的数量
缓存命中率：Innodb 的缓存命中率
功能指标
可用性：数据库是否可正常对外提供服务
阻塞：当前是否有阻塞的会话
死锁：当前事务是否产生了死锁
主从延迟：数据库主从延迟时间
主从状态：数据库主从复制链路是否正常

这些监控是如何实现的？

QPS：数据库每秒钟处理的请求数量
show global status like 'com%'
Show global status like 'Queries'
QPS=（Queries2-Queries1）/时间间隔
TPS：数据库每秒钟处理的事务数量
show global status where Variable_ name in (com_ insert','com_delete','com_update');
Tcscom_insert+com_delete+com_update
TPS*(TC2-Tc1)/(time2-time1)
并发数：数据库实例当前并行处理的会话数量
SHOW GLOBAL STATUS LIKE "Threads_running'
连接数：连接到数据库会话的数量
SHOW GLOBAL STATUS LIKE 'Threads_ connected'
InnoDB 缓存命中率
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests*100%
Innodb_buffer_pool_read_requests：从缓冲池中读取的次数
Innodb_buffer_pool_reads：表示从物理磁盘读取的次数
show global status like 'innodb_buffer_pool_read%';

报警阀值：Threads_connected/max_connections>0.8

可用性：数据库是否可正常对外提供服务
周期性连接数据库服务器并执行 select@@version
mysqladmin-uxxxx-pxxxxx-hxxxxx ping

阻塞

SELECT 
	waiting_pid AS '被阻塞的线程'，
	waiting_query AS‘被阻塞的SQL'，
	blocking_pid AS '阻塞线程'，
	blocking_query AS '阻塞SQL'，
	wait age AS '阻塞时间',
	sql_kill_blocking_query AS“建议操作’
FROM 
	sys.innodb_lock_waits
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait started))_>30

监控慢查询
通过慢查询日志监控
通过 information_schema.PROCESSLIST 表实时监控
死锁
show engine innodb status
pt-deadlock-logger u=dba,p=xxxxx,h=127.0.0.1 --create-dest-table --dest u=dba,p=xxxxx,h=127.0.0.1,D=crn,t=deadlock
set global innodb_print_all_deadlocks=on;
主从延迟：数据库主从延迟时间
show slave status;（Seconds_Behind_Master）
pt-heartbeat --user=xx --password=xxx -h master --create -table --database xxx --update --daemonize --interval=1
pt-heartbeat --user=xx --password=xxx -h slave --database crn --monitor --daemonize --log/tmp/slave_lag.log
主从状态：数据库主从复制链路是否正常
show slave status;（Slave_IO_Running:YES；Slave_SQL_Running:YES）

优化及异常处理

请例举三个你曾经处理过的让你印象深刻的问题

数据库服务器负载过大的问题

现象：QPS（低谷）、CPU 占有率（峰值）
原因
服务器磁盘 IO 超负荷
存在大量阻塞线程
存在大量并发慢查询
存在其它占用 CPU 的服务
服务器硬件资源原因
解决

慢查询造成的磁盘 IO 爆表

原因
MySQL 输出大量日志
MySQL 正在进行大批量写
慢查询产生了大量的磁盘临时表
解决
优化慢查询，减少使用磁盘临时表
增加 tmp_table_size 和 max heap_table_size 参数的大小

主从数据库数据不一致

现象
主从数据库延迟为 0
IO_THREAD 和 SQL_THREAD 状态为 YES
相同查询在主从服务器中查询结果不同
原因
对从服务器进行了写操作
使用 sql_slave_skip_counter 或注入空事务的方式修复错误
使用了 statement 格式的复制
解决
设置 read_only=ON
设置 super_read_only=ON
使用 row 格式的复制

处理过哪些 MySQL 主从复制异常？

主服务器连接不上
主从服务器间网络是否畅通（ping）
是否存在防火墙，过滤了数据库端口
复制链路配置的用户名和密码是否正确，是否有相应权限
主键冲突问题
跳过故障数据
检查主从数据一致性
直接删除从库主键冲突数据
数据行不存在
跳过故障数据
使用 pt-table-sync 修复数据
relay_log 损坏
找到已经正确同步的日志点
使用 reset slave 删除 relay_log
在正确同步日志点后重新同步日志