MySQL面试指南（一）

简介

• 本篇主要对MySQL面试时可能会问到的技术点做一个总结，经验有限，主要是面向校招
• 推荐大佬的文章，可以详细学习一下：篇一，篇二
• 导学：
  
  • 这里不涉及基本语法、基础操作（SQLBoy）可以看我的MySQL基础篇
  • 后面我们会着重回顾一下分库分表

版本问题

• Q：你在学习工作中使用哪个版本的数据库？为什么选择这个版本？
• Q：是否升级过？为何升级？如何升级？
• Q：最新的MySQL版本是什么？你关注哪个特性？
• 注：这个版本指的是不同企业维护的数据库系统，不是MySQL从5.7到8.0

常见发行版

• Oracle收购MySQL之后，作为官方一直在维护
  • 社区版：国内最常用的数据库
  • 企业版：收费
• Percona MySQL
  • 二次开发，性能较官方版本更好一些，但更新慢一步
• MariaDB
  • 不是完全兼容MySQL的
• 从服务器特性看：
  
  • 当然，也有一些免费的监控工具，例如mytop等
• 从高可用性看：
  
  • MGR是基于Paxos算法实现的MySQL Server插件，可以实现组复制，保证数据一致性；是一种高可用解决方案
  • MySQL Router是一个轻量级的中间件
• 安全性方面：
  
  • 官方MySQL8.0升级了对密码的管理，使用了生命周期机制和sha2插件提高安全性
• 从开发管理来看：
  
• 现在，我们可以结合上面的特性，看看自己使用的MySQL都有哪些优势，回答第一个Q

升级

• 升级前的考虑
  • 给业务带来的益处和影响
  • 制定升级方案及失败的回滚方案
• 业务和运维上的痛点
  • 一般和新特性相关，这个…校招生暂时无以奉告
• 对业务程序的支持和性能的影响
• 升级方案
  • 评估受影响的系统有哪些（按组升级）
  • 和业务人员沟通，是否需要修改程序，确定升级时机
  • 使用真实数据多次测试演练
  • 升级后的数据库环境检查
• 回滚方案
  • 一般是主从复制的滚动升级方案，主库回滚
  • 回滚后也要检查
• 基本步骤：
  

8.0

• 即使没有使用也要关注新版本的特性，万一能很好解决你当前的问题呢？
• MySQL从5.7直接到8.0
• 服务器新特性主要如下：
  
  • 资源组：可以修改线程的优先级以及所能使用的硬件资源，可以指定不同的线程使用特定的资源
    
  • 资源组的定义：
    
  • 资源组的使用：即资源组占领1号CPU，给84号线程使用了
    
• 安全新特性如下：
  
  • 密码记录：可以定时让业务人员修改密码，防止密码使用太久增加破解风险
• InnoDB引擎新特性：
  

用户管理

• Q：如何在给定场景下为某用户授权？
• Q：如何保证数据库账号的安全
• Q：如何从一个实例迁移数据库账号到另一个实例（数据库）

授权

• 规范定义MySQL账号

  -- 可以使用 \h create user 查看帮助
  create user roy@localhost:test_authority
  

  
• 常用用户权限
  • DBA权限及库表结构相关：
    
  • 数据操作相关：一般情况下用户只有DML权限
    
  • 可以使用命令show privileges;查看权限列表及其说明
• 给定场景下授权原则
  • 遵循最小权限原则
  • 使用grant命令授权

  -- 给用户授予操作某个数据库表的权限
  grant select,insert,update,delete on db.tb to user@ip;
  

  • 使用revoke收回权限

  -- 收回delete权限
  revoke delete on db.tb from user@ip
  

账号安全

• 保证数据库账号安全
  • 规范的用户管理流程（别瞎授权）
  • 密码策略
    
  • 创建用户的时候即可定义密码过期
    

    -- 指定密码可使用一天
    create user roy @'localhost' identified by '123456' password history 1; 
    -- 设置过期
    alter user roy@'localhost' password expire;
    

  • 可在内置mysql库的user表中看到：Password_reuse_history: 1
    
  • 此时登录看不到任何数据库，因为还未授权
    
  • 设置roy过期后重新登录（密码过期后可以使用过期密码登录，但是提示必须修改），此时便不能再执行任何操作命令

    -- 由于密码策略，必须使用与上一次不同的密码
     alter user user() identified by '654321';
    

账号迁移

• 迁移数据库账号，即数据库用户（开发和维护人员）的信息和权限
• 要判断两个实例的数据库版本是否一致
  
  • 版本一致，即兼容，直接都copy过去
  • 不一致，则在那边的数据库按这边的权限授权即可
• 导出创建用户及授权语句的方法

  pt-show-grants u=dba_test, p=123456, h=localhost;
  

• 导出语句如下：
  
  • 在新实例运行即可

服务器配置

• Q：分析一个group by语句的异常原因？
• Q：如何比较系统运行的配置和配置文件中的设置是否一致？
• Q：MySQL中的关键性能参数？

执行模式

• 给出如下数据表：
  
  • 统计：每种产品在每个仓库中的总数量
    
  • 当然，这个DML语句有误，但在MySQL中并不会报错；我们暂且叫异常
  • 这关系到SQL_MODE，即SQL语句执行的模式，可配置

  set global sql_mode = 'xxx';	-- 作用域还可以是session、persist
  

  
  • 为了解决上面的问题，可以做如下设置：
    
  • 还有一些常见设置

    -- 例如
    create table t(id real);	-- float
    

    
    • MySQL从5.6开始将SQL_MODE设置成TRADITIONAL，要注意

配置一致性

• 由于SQL_MODE可以动态设置，因此会出现系统运行配置和配置文件不一致的情况
• 使用pt-config-diff工具对比
• 先说明一下变量设置时的persist参数
  • session和global就不多说了，注意他们都会因重新连接而丢失设置
  • 但persist不会，直接将设置持久化在文件
    
  • 这是在8.0提出来的，如果不是此版本，需要global之后手动修改my.cnf文件
• 对比配置的命令：pt-config-diff u=root,p=123456,h=localhost /etc/my.cnf，指定对比的配置文件即可

关键性能参数

• 服务器配置参数
  
  
  • 针对线程的设置意味着有多少线程，配置的资源就会扩大多少倍，所以别太大！
  • max_connections的控制，会占用内存
  • 对于master服务器，sync_binlog最好设置为1
  • sort_buffer_size只会在查询涉及到排序时分配，而且会立即分配所有空间
• 存储引擎参数
  
• 服务器配置这部分主要了解执行模式SQL_MODE和关键参数即可
• 任何问题都可从效率、空间开始入手分析