01-MySQL 基础

官网保平安：https://www.mysql.com/
MySQL 思维导图：https://www.processon.com/view/link/63bc2c8ea82ed9463ba99f38

与 Oracle 相比，MySQL 有什么优势

  • MySQL 是开源软件，随时可用，无需付费。
  • MySQL 是便携式的。
  • 带有命令提示符的 GUI。
  • 使用 MySQL 查询浏览器支持管理。

MySQL 有什么优点？

这个问题本质上是在问 MySQL 如此流行的原因。

MySQL 主要具有下面这些优点：

  1. 成熟稳定，功能完善。
  2. 开源免费。
  3. 文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。
  4. 开箱即用，操作简单，维护成本低。
  5. 兼容性好，支持常见的操作系统，支持多种开发语言。
  6. 社区活跃，生态完善。
  7. 事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。
  8. 支持分库分表、读写分离、高可用。

MySQL 的 change buffer 是什么

当需要更新一个数据页时，如果数据页在内存中就直接更新；如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。

这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

注意唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。因为对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断表中是否已经存在 k=4 的记录，因此需要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

适用场景：对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

MySQL 备份

数据库导入导出命令（结构+数据）

  • 导出。
    mysqldump --no-defaults -uroot -p 数据库名字 > 导出路径
    参数 --no-defaults 解决unknown option --no-beep报错。

  • 导入。

    1. mysqldump -uroot -p 数据库名称 < 路径
    2. 进入数据库: source + 要导入数据库文件路径

如何最快的复制一张表

为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。

  • 一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句。
  • 另一种方法是直接将结果导出成.csv 文件。MySQL 提供语法，用来将查询结果导出到服务端本地目录：select * from db1.t where a>900 into outfile '/t.csv'; 得到.csv 导出文件后，你就可以用 load data 命令将数据导入到目标表 db2.t 中：load data infile '/t.csv' into table db2.t;
  • 物理拷贝：在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

误删数据怎么办

如果发生了数据删除的操作，又可以从以下几个点来恢复:

  • DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多。
    • 都是先解析 binlog event，然后在进行反转。把 delete 反转为 insert，insert 反转为 delete，update 前后 image 对调。
    • 所以必须设置 binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
  • DDL 语句误操作(truncate和drop)，由于 DDL 语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。
    • 只能通过 全量备份+binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长。
  • rm 删除：使用备份跨机房，或者最好是跨城市保存。

DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：

  • 权限控制与分配(数据库和服务器权限)。
  • 制作操作规范。
  • 定期给开发进行培训。
  • 搭建延迟备库。
  • 做好 SQL 审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核。
  • 做好备份。备份的话又分为两个点。
    • 如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。
    • 如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份 binlog 文件也是很有必要的。

为什么要有多线程复制策略

因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库可能是一直追不上主库的，带来的现象就是备库上 seconds_behind_master 值越来越大。

在实际应用中，建议使用可靠性优先策略，减少主备延迟，提升系统可用性，尽量减少大事务操作，把大事务拆分小事务。

MySQL 的并行策略有哪些

  • 按表分发策略：如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。
    缺点：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。

  • 按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并行。显然，这个模式要求 binlog 格式必须是 row。
    缺点：相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

MySQL 怎么恢复半个月前的数据

通过 全量备份+binlog 进行恢复。前提是要有定期整库备份且保存了 binlog。

其他

详细说一下一条 MySQL 语句执行的步骤

Server 层按顺序执行 SQL 的步骤为：

  • 客户端请求
  • -> 连接器（验证用户身份，给予权限）
  • -> 查询缓存（存在缓存则直接返回，不存在则执行后续操作）（MySQL 8.0 版本后移除，因为这个功能不太实用）
  • -> 分析器（对 SQL 进行词法分析和语法分析操作）
  • -> 优化器（主要对执行的 SQL 优化，选择最优的执行方案方法）
  • -> 执行器（执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。）

MySQL 中有两个 kill 命令

  • 一个是 kill query + 线程id，表示终止这个线程中正在执行的语句。
  • 一个是 kill connection + 线程id，这里 connection 可缺省，表示断开这个线程的连接。

kill 不掉的原因：

  • kill 命令被堵了，还没到位。
  • kill 命令到位了，但是没被立刻触发。
  • kill 命令被触发了，但执行完也需要时间。

如何理解 MySQL 的边读边发

如果客户端接受慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间会很长。

服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个 next_buffer 来操作的。

内存的数据页都是在 Buffer_Pool 中操作的。InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描，影响也能做到可控制。

MySQL 的大表查询为什么不会爆内存

由于 MySQL 是边读变发，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集，所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。

主键和外键的区别

  • 主键。

    • 唯一标识一条记录，不能有重复，不允许为空。
    • 用来保证数据的完整性。
    • 主键只能有一个。

  • 外键。

    • 表的外键是另一个表的主键，外键可以有重复的，可以是空值。
    • 用来和其他表建立联系用的。
    • 一个表可以有多个外键。

为什么不推荐使用外键与级联

对于外键和级联，阿里巴巴开发手册这样说到：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度

为什么不要用外键呢？大部分人可能会这样回答：

  1. 增加了复杂性：
    1. 每次做 DELETE 或者 UPDATE 都必须考虑外键约束，会导致开发的时候很痛苦, 测试数据极为不方便;
    2. 外键的主从关系是定的，假如那天需求有变化，数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
  2. 增加了额外工作：数据库需要增加维护外键的工作，比如当我们做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，保证数据的的一致性和正确性，这样会不得不消耗数据库资源。如果在应用层面去维护的话，可以减小数据库压力；
  3. 对分库分表不友好：因为分库分表下外键是无法生效的。

实际上，我们知道外键也是有很多好处的，比如：

  1. 保证了数据库数据的一致性和完整性；
  2. 级联操作方便，减轻了程序代码量；

所以说，不要一股脑的就抛弃了外键这个概念，既然它存在就有它存在的道理，如果系统不涉及分库分表，并发量不是很高的情况还是可以考虑使用外键的。

MySQL 临时表的用法和特性

  • 只对当前 session 可见。
  • 可以与普通表重名。
  • 增删改查用的是临时表。
  • show tables 不显示普通表。
  • 在实际应用中，临时表一般用于处理比较复杂的计算逻辑。
  • 由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除。

简述触发器、函数、视图、存储过程

  • 触发器：对数据库某个表进行【增、删、改】前后，自定义的一些 SQL 操作。

  • 函数：在 SQL 语句中使用的函数。例如：select sleep(2)。

  • 视图：对某些表进行 SQL 查询，将结果实时显示出来(是虚拟表)，只能查询不能更新。
    视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增、删、改、查等操作。
    对视图的修改，不影响基本表。
    相比多表查询，它使得我们获取数据更容易。

  • 存储过程：将提前定义好的 SQL 语句保存到数据库中并命名；以后在代码中调用时直接通过名称即可。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。
    存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。
    阿里巴巴 Java 开发手册里要求禁止使用存储过程。!

  • 游标是对查询处理出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。

MySQL 常见的函数

  • 当前时间
    • select now();
  • 时间格式化
    • select DATE_FORMAT(NOW(), '%Y(年)-%m(月)-%d(日) %H(时):%i(分):%s(秒)')
  • 日期加减
    • select DATE_ADD(DATE, INTERVAL expr unit)
      • expr：正数(加)、负数(减)
      • unit：支持毫秒microsecond、秒second、小时hour、天day、周week、年year
    • select DATE_ADD(NOW(), INTERVAL 1 DAY)：当前日期加一天
  • 类型转换
    • cast( expr AS TYPE)
    • select CAST(123 AS CHAR)
  • 字符串拼接
    • concat(str1,str2，……)
    • select concat('hello','2','world') --> hellow2world
  • 聚合函数
    • avg()：平均值
    • count()：返回指定列/行的个数
    • min()：最小值
    • max()：最大值
    • sum()：求和
    • group_concat()：返回属于一组的列值，连接组合而成的结果
  • 数学函数
    • abs()：绝对值
    • bin()：二进制
    • rand()：随机数

MySQL 的并发链接和并发查询有什么区别

  • 在执行 show processlist 的结果里，看到了几千个连接，指的是并发连接。

  • 而 当前正在执行 的语句，才是并发查询。

并发连接数多影响的是内存，并发查询太高对 CPU 不利。一个机器的 CPU 核数有限，线程全冲进来，上下文切换的成本就会太高。

所以需要设置参数：innodb_thread_concurrency 用来限制线程数，当线程数达到该参数，InnoDB 就会认为线程数用完了，会阻止其他语句进入引擎执行。

为什么 MySQL 会抖一下

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

刷脏页有下面4种场景：

  • 当 redo log 写满，MySQL 就会暂停所有更新操作，将同步这部分日志对应的脏页同步到磁盘。
  • 系统内存不足时，需要淘汰一部分数据页，如果淘汰的是脏页，就要先将脏页同步到磁盘。
  • MySQL 认为系统空闲的时候，有机会就同步内存数据到磁盘，这种没有性能问题。
  • MySQL 正常关闭，MySQL 会把内存的脏页都同步到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。这种没有性能问题。

为什么删除了表，表文件的大小还是没变？

数据项删除之后，InnoDB 某个页 page A 会被标记为可复用。delete 命令把整个表的数据删除，结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

经过大量增删改的表，都是可能是存在空洞的。这些空洞也占空间，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

MySQL 查询缓存有什么弊端，应该什么情况下使用，8.0 版本对查询缓存有什么变更

  • 查询缓存可能会失效非常频繁，对于一个表，只要有更新，该表的全部查询缓存都会被清空。因此对于频繁更新的表来说，查询缓存不一定能起到正面效果。
  • 对于读远多于写的表可以考虑使用查询缓存。
  • 8.0 版本的查询缓存功能被删了 (￣.￣)。

解释 MySQL 外连接、内连接与自连接的区别

交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

内连接：则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中， 即内连接只连接匹配的行。

外连接：其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行， 这三种情况依次称之为左外连接，右外连接，和全外连接。

左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以 NULL 来填充。

右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。

左连接和右连接可以互换，MySQL 目前还不支持全外连接。

隐式转换

  1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
  2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
  3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
  4. 字符串转换为数值类型时，非数字开头的字符串会转化为 0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

能用 MySQL 直接存储文件（比如图片）吗？

可以是可以，直接存储文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。

可以选择使用云服务厂商提供的开箱即用的文件存储服务，成熟稳定，价格也比较低。

Reference

#MySQL #面试
01-MySQL 基础
https://flepeng.github.io/interview-41-数据库-41-MySQL-01-MySQL-基础/
作者
Lepeng
发布于
2020年8月8日
许可协议