MySQL 面试题(四)

R先生专栏
205 阅读7分钟 
本文已参与「新人创作礼」活动，一起开启掘金创作之路。

视图、存储过程、触发器

什么是视图

  • MySQL 必知必会里讲到视图是虚拟的表，与包含数据的表不同，视图只包含使用时动态检索数据的查询

为什么使用视图

  • 重用 sql 语句
  • 简化复杂的 sql 操作
  • 只使用部分表，提高安全性
  • 视图可返回与实体表格式不同的数据

特点

  • 视图列可来自不同的表，是抽象和逻辑意义上新的关系
  • 视图的建立和删除不影响实体表
  • 对视图内容的更新直接影响实体表
  • 当视图来自多个表时，不允许添加和删除数据

优缺点

  • 优点

    • 查询简单化
    • 保证了数据安全
    • 逻辑数据的独立性

  • 缺点

    • 可能会导致性能偏低
    • 修改限制

什么是存储过程

  • 简单说，就是为以后使用而保存的一条或多条 sql 语句的集合
  • 预编译的 sql 语句
  • 创建方式：CREATE PROCEDURE procedure_name(args) BEGIN ........ END;
  • 执行方式：CALL procedure_name(args);

优缺点

  • 优点

    • 预编译过，执行效率高
    • 直接存储在数据库中，减少网络开销
    • 安全性高
    • 可重复使用

  • 缺点

    • 调试麻烦
    • 移植与兼容性问题
    • 对新人不友好，需要更多专业知识

什么是触发器

  • 触发器是MySQL 响应特殊的语句而自动执行的一条 MySQL 语句
  • 使用场景：当业务完成需要自动执行某些语句的时候

有哪些触发器(三类六种)

  • Insert(Before/After)
  • Update(Before/After)
  • Delete(Before/After)

其他

in 和 exists 的区别

  • in 语句是把外表和内表作 hash 连接
  • exists 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询
  • 子查询表大的用 exists，子查询表小的用in
  • not in 和 not exists，如果查询使用了 not in 那么内外表都会进行全表扫描，not exists 的子查询依然可以使用索引

char 和 varchar 的区别

  • char 定长，varchar 可变
  • 如果插入数据小于 char 规定的长度，则用空格填充，最多存放255个字符，与编码无关
  • varchar 最多可存放65532个字符

varchar(48)的含义

  • 最多存放48个字符，实际与 varchar(128)所占空间一样，但后者在排序时会更消耗内存，因为 order by 采用 fixed_length 计算列长度

drop、delete、truncate 区别

比较DELETETRUNCATEDROP
类型DMLDDLDDL
回滚可回滚不可回滚不可回滚
删除内容表结构还在，删除一行或多行数据表结构在，删除所有数据删除表结构、数据、索引、权限
删除速度最慢，需逐行删除较快最快

sql 优化

  • 查询 sql 语句是否有多余的数据查询，避免 select *
  • explain 执行计划查看是否走索引
  • 尽量全部大写 sql，因为数据库首先会把 sql 编译成大写再执行
  • 避免 is null 判断，会走扫描
  • 加密后的密码、身份证号等固定长度可以 char 存储

sql 执行顺序

  • FROM—> WHERE —> JOIN —> ON —> GROUP BY —> HAVING —> SELECT —> DISTINCT —> UNION —> ORDER BY —> LIMIT

sql 生命周期

  • 客户端与服务器首先建立连接
  • 服务端收到请求解析 sql，转化成抽象语法树
  • 执行器解析抽象语法树，生成逻辑执行计划
  • 逻辑执行计划在存储引擎层转化为物理执行计划
  • 执行并读取数据到内存并进行逻辑处理
  • 结果发送给客户端并关闭连接释放资源

慢查询日志

  • 开启慢查询日志：show_query_log=on
  • 会在 datadir 下产生一个 xxx-show.log 文件
  • 设置临界时间：long_query_time=xxx
  • 一旦 sql 超过我们设置的临界时间就会被记录到 xxx-show.log 文件中

数据库优化

  • 字段很多的表拆分成多个表
  • 增加中间表
  • 增减冗余字段
  • 分库分表以后面临分布式事务问题
  • 分库分表以后面临跨库 join
  • 跨节点的聚合函数处理

主从复制

主从复制原理

将主数据库中的 DDL 和 DML 操作通过 binlog 复制到从库上的中继日志中，读取中继日志事件，重新执行这些日志，从而使得主从保持一致

主从复制基本原理流程

  • 主：bin log 线程—记录了所有改变数据库数据的语句，放进 master 上的 bin log 中
  • 从：io 线程—负责从 master 上拉取 bin log 内容，放进自己的 relay log 中
  • 从：sql 执行线程—执行 relay log 中的语句

详细复制过程

bin log：主数据库的二进制日志

relay log：从服务器的中继日志

  1. master 在每个事务更新数据完成之前，将该操作记录串行写入到 bin log 文件中
  2. slave 开启一个 I/O 线程，该线程在 master 上打开一个普通连接，主要工作是 bin log dump process。 如果读取的进度已经跟上了 master，就进入睡眠状态并等待 master 产生新的事件。I/O 线程最终的目的是将这些事件写入到中继日志中
  3. sql 线程会读取中继日志，并顺序执行该日志中的 sql 事件，从而与主数据库中的数据保持一致

主从复制解决的问题

  • 数据分布
  • 负载均衡
  • 高可用和故障切换
  • 升级测试

mysql dump 和 xtrabackup 实现原理

  • mysql dump 属于逻辑备份，加入 -single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 可重复度，之后显示开启一个事务，这样就保证了该事务里读到的数据都是事务时候的快照。之后再把表的数据读取出来。如果加上-master-data=1 的话，在刚开始的时候还会加一个数据库的读锁，等事务开启后，再记录下数据库此时的 bin log 位置，马上解锁，再读取表的数据，等所有的数据都导出完毕，事务结束
  • xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo log 并保存下来，最后完成 innodb 的备份后，会做一个 刷新操作，确保所有的 redo log 都已经落盘，这里涉及到事务的两阶段提交，因为 xtrabackup 并不拷贝 bin log，所以必须保证所有的 redo log 都落盘，否则可能会丢失数据；这个时间节点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性，还需 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完成后解锁

数据表损坏的修复方式

略

读写分离有哪些好的解决方案

读写分离依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求 slave 不能写只能读

  • 方案一：使用 mysql-proxy 代理

    • 优点：直接实现读写分离和负载均衡，不用修改代码，master 和 slave 用一样的账号，但官方不建议使用

  • 读写分离放在持久层

    mybatis plugin 拦截 sql 语句，所有的修改语句都访问 master 库，所有的查询都访问 slave 库，通过 AOP+注解即可实现。还需重写 DataSourceTransactionManager 将 read-only 的事务放进读库，其余有读有写的放进写库

  • 读写分离放在 service 层

  • END -

avatar
文章
阅读
粉丝