Mysql面试

时斑余
143 阅读8分钟

连接方式

三大范式

  1. 字段不可分；
  2. 有主键，非主键字段依赖主键；
  3. 非主键字段不能相互依赖。

Drop,delete, truncate

  1. drop:删数据，删表
  2. truncate:删除所有数据
  3. delete：日常操作，不带where条件时删除所有数据，会产生binlog
----deletetruncatedrop
类型属于 DML属于 DDL属于 DDL
回滚可回滚不可回滚不可回滚
删除内容表结构还在，删除表的全部或者一部分数据行表结构还在，删除表中的所有数据从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度删除速度慢，需要逐行删除删除速度快删除速度最快

in, between

in多次查询

in, exist

  1. in:外层大表，内层小表
  2. Exist：相反
  3. not in，全表扫描
  4. not exist能利用索引

有哪些数据类型

数值、字符串、日期

char和varchar

  1. char定长、空格填充剩余、255上限
  2. varchar不定长，65532

Datetime, timestamp

Decimal和float, double

decimal字符串存储，后者浮点数

存表情

Utf8mb4

UNION,UNION ALL

  • union去重
  • Union all不去重

count(1) count(*) count(列名)

效果

  • count(*) count(1)包括所有行，不忽略null.
  • Count(*)不统计null

速度

  • 主键，count(字段)比count(1)快，反之
  • 没有主键、多列，count(1) 比count(*)快
  • Count（主键）最快
  • 只有一个字段，count(*)快

查询语句执行顺序

架构

  • 客户端
  • server层
  • 存储引擎

执行过程

  1. 解析
  2. 优化
  3. 执行
  4. 返回

常见日志

  1. 错误日志
  2. 慢查询日志
  3. 一般查询日志
  4. binlog
  5. redolog
  6. undolog

Binlog, redolog

binlog

  1. 记录所有db相关日志
  2. 事务的具体操作内容、是逻辑日志
  3. 在事务提交前提交、只写一次磁盘
  4. 追加写入，不会覆盖
  5. 主从、主备、数据同步

redolog

  1. 记录每个数据页的更改情况
  2. 事务进行过程中不断写入
  3. 循环写入和擦除

undolog

所有事务进行的修改都会先记录到回滚日志，再执行相关的操作。执行过程中遇到异常，直接利用 回滚日志 中的信息将数据回滚到修改之前！回滚日志会先于数据持久化到磁盘上

更新语句的执行过程

update 执行

两阶段提交

  1. 开始
  2. 更新数据
  3. redo, prepare
  4. 写binlog
  5. redo, commit

Redo log刷盘

redo log buffer连续内存空间。重做日志块512字节。

刷盘时机：

  1. 缓存不足
  2. 事务提交时
  3. 后台线程输入
  4. 正常关闭服务器
  5. 触发checkpoint

SQL优化

慢查询定位

分页优化：

  1. 延迟关联：先通过where条件取出主键，再将该表与原数据关联，通过主键id提取数据行，而不是原来的二级索引提取行
  2. 书签方式：找到limit第一个参数对应的主键值，根据主键值再去过滤并limit

explain计划

type列

  • system
  • const：
  • Eq_ref：主键或者唯一索引命中

extra

  • using index：覆盖索引，避免回表
  • using where：会在存储引擎检索后再过滤
  • using temporary:

索引分类

创建索引注意事项

  1. 查询频繁字段
  2. 个数适量
  3. 区分度低字段不行
  4. 更新频繁不行
  5. 区分度高的值放在组合索引前面
  6. 创建组合索引，减少单列索引
  7. 过长字段 ，使用前缀
  8. 不建议用无序的值

索引失效

B+树能存多少数据

2000万左右的表，需要访问3次磁盘，一般1-3层，满足千万级的数据存储

B+树，B树，二叉树

聚簇索引、非聚簇索引

回表

覆盖索引

最左前缀原则

索引下推优化

  • 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给 MySQL Server，MySQL Server 进行过滤条件的判断。
  • 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server 将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL Server 传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

锁

MySQL 中的锁

临键锁：左开右闭

例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。mysql 默认行锁类型就是临键锁(Next-Key Locks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！

插入意向锁

意向锁

表级锁。判断数据行有没有锁定。

乐观锁、悲观锁

  • 悲观锁：悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。数据库中的行锁，表锁，读锁，写锁均为悲观锁。
  • 乐观锁乐观锁认为数据的变动不会太频繁。乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现，其中，版本最为常用。

排查死锁

查看死锁日志 show engine innodb status

事务四大特性

  • 原子性：要么全执行，要不都没执行
  • 一致性：事务前后，数据不会被破坏
  • 隔离性：事务之间没有影响
  • 持久性：事务完成后，进行的修改持久 保存在数据库中

ACID保证方式

  1. 原子性：undo log。记录事务发生之前的数据，如果事务失败，根据日志回滚，如果成功，日志会在某个时间点删除
  2. 一致性：是目的
  3. 隔离性：MVCC
  4. 持久性：redo log + binlog

隔离级别

  1. 读未提交
  2. 读已提交
  3. 可重复读
  4. 串行化

脏读、幻读、不可重复读

  • 脏读：A事务读取了B事务没有提交的数据
  • 不可重复读：在一个事务范围内，两次相同查询一条记录，返回不同数据
  • 事务查询范围内出现了另一个数据提交的数据

不同隔离级别下可能出现的问题

隔离级别脏读不可重复读幻读
Read Uncommited 读取未提交是是是
Read Commited 读取已提交否是是
Repeatable Read 可重复读否否是
Serialzable 可串行化否否否

读未提交：不加锁

读已提交&可重复读：都用了MVCC和ReadView

串行化：读写加锁

MVCC

多版本并发控制，简单来说就是通过维护数据历史版本，从而解决并发访问情况下的读一致性问题。关于它的实现，要抓住几个关键点，隐式字段、undo 日志、版本链、快照读&当前读、Read View。

版本链

对于 InnoDB 存储引擎，每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR

  • DB_TRX_ID，事务 ID，每次修改时，都会把该事务 ID 复制给DB_TRX_ID；
  • DB_ROLL_PTR，回滚指针，指向回滚段的 undo 日志。

读写分离

  • 数据库服务器搭建主从集群，一主一从、一主多从都可以。
  • 数据库主机负责读写操作，从机只负责读操作。
  • 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。
  • 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。

主从复制原理

  • master 数据写入，更新 binlog
  • master 创建一个 dump 线程向 slave 推送 binlog
  • slave 连接到 master 的时候，会创建一个 IO 线程接收 binlog，并记录到 relay log 中继日志中
  • slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行，完成同步
  • slave 记录自己的 binglog

主从同步延迟

  1. 写操作后的读操作指定发给数据库主服务器
  2. 读从机失败后再读一次主机
  3. 关键业务读写操作全部指向主机，非关键业务采用读写分离

分库

  • 垂直分库
  • 水平分库

分表

  • 垂直
  • 水平

路由：

  1. hash
  2. 范围

不停机扩容

  1. varchar(100)和varchar(10)的区别？
  2. 为什么不推荐TEXT, BLOB
  3. NULL和''的区别
  4. binlog cache
  5. 页修改之后为什么不直接刷盘呢？
  6. 并发事务的控制方式有哪些？
  7. 行级锁使用的注意事项？
  8. Inno DB有哪些行级锁？默认用什么？
  9. 共享锁和排他锁
  10. 如何存储IP地址
  11. SQL优化手段
  12. 如何分析SQL的性能
  13. 子查询
  14. 子查询性能差的原因
  15. 查询缓存的优缺点
  16. 自增主键一定是连续的吗？
  17. 隐式转化造成索引失效

参考

MySQl 索引之道 - 掘金

Mybatis

ORM

生命周期

  1. 创建sqlSessioniFactory
  2. 创建SqlSession
  3. 获取mapper
  4. 执行sql
  5. 提交事务
  6. 关闭session

#{}和${}

#{}是占位符，预编译处理

${}是拼接符，字符串替换

延迟加载

支持，在引擎层操作关联的数据

动态SQL

一级缓存、二级缓存

一级缓存：在一个连接中生效

二级缓存：全局生效

会话运行过程

  1. Executor
  2. statementHandler
  3. ParameterHandler
  4. ResultSetHandler
avatar
文章
阅读
粉丝