面试系列（三）--MySQL知识

南希_0196
117 阅读10分钟

数据库基础知识

E-R图：实体、属性和联系

数据库范式

  • 1-NF：实体的属性不能再被分割
  • 2-NF：函数依赖、部分函数依赖、完全函数依赖、传递函数依赖
  • 3-NF：消除传递函数依赖的字段设计

主键和外键的区别：

主键是唯一表示数据的id；外键是table1余table2的关联，在table2中做主键

为什么不使用外键？

因为一次更新需要级联更新所有相关的外键的数据，分库分表也外键不生效

什么是存储过程？

存储过程是一段sql语句的集合

drop、truncate、delete的区别？

drop 删除表结构

truncate：清理整表数据

delete * from table_name where xxx；清理指定条件的数据

3、MySQL

MySQL的架构：client发起请求 ——> 首先经过查询缓存，能查到直接返回，不能查到——> 分析器 ——> 优化器（优化执行方式，选择索引）——> 执行器（查询存储引擎，返回结果）

MySQL的存储引擎有哪些？

  • MyISAM
  • InnoDB

1、InnoDB比MyISAM的好处？

  • InnoDB支持事务，并且有多种事务隔离级别
  • InnoDB支持行级锁，MVCC
  • InnoDB支持崩溃可恢复

2、MySQL的索引实现

索引是查询和快速检索数据的数据组织结构

索引的优缺点？有了索引查询更快，但是索引本身的存储和结构维护会耗费一定的空间和时间

索引的数据结构？

B树、B+树的区别？

  • B树每个节点存储的都是key、data；B+树只有叶子节点【叶子节点才有所有的数据】存储data
  • B树叶子节点都是独立的，B+树叶子节点相邻之间是连接的

最左匹配原则

联合索引使用索引情况

索引：a_b

在索引结构里面是先按照a字段进行了排序，在a相同的数据里，按照b字段排序，整体结构看b字段不是有序的

select * from t_table where a > 1 and b = 2;  // 使用的索引是a字段
select * from t_table where a >= 1 and b = 2   // 使用的索引用到了a、b两个字段。a=1时b=2是有效的
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2  // 使用了a、b两个字段，MySQL中
SELECT * FROM t_user WHERE name like 'j%' and age = 22 //[联合索引name_age]，实际查询过程中使用了name、age两个字段进行查询，这里面用到age=22的条件相当于用到了索引下推

varchar类型的字段本身长度+2【变长字节】

单张表索引字段最好不要超过5个

索引下推

索引失效场景

  • select *
  • 未遵循最左匹配原则
  • 在索引列上进行计算、函数、类型转换
  • 使用%开始的like查询
  • or查询

MySQL语句的执行计划

explain 语句，获取优化器优化之后的执行方式

image.png type：

  • all：全表扫描
  • ref：使用普通索引
  • system：表中只有一行数据
  • const：表中最多有一行数据
  • eq_ref：连表查询时后表只有一行数据对应
  • range：索引范围查询
  • index：遍历整个索引树
  • index_merge：查询条件使用多个索引

possible_keys：可能用到的索引
key：实际用到的索引
key_len：实际索引的长度
rows：大致要扫描的行数

Extra：
Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

3、MySQL日志

1、MySQL 中常见的日志有哪些？

2、慢查询日志有什么用？

3、binlog 主要记录了什么？

4、redo log 如何保证事务的持久性？

对数据库中数据进行修改时，记录修改操作，然后将修改操作记录到redo log，只要记录到了redo log的日志文件中，那么即便发生数据库服务器宕机情况，也能根据redo log的记录，恢复事务的执行，保证事务的持久化完成

redo log的刷盘机制？

redo log中记录修改数据记录，每次事务提交的时候，将redo log数据内容同步到磁盘中

内存的redo log buffer ——> 系统buffer ——> 磁盘的redo log文件中

从buffer刷新到磁盘文件的配置：0、1、2

  • 0：只是记录到redo log buffer中，由系统单独起线程控制 刷新到系统buffer和redo log文件
  • 1：每次执行事务的commit时，都要刷新到磁盘的redo log文件
  • 2：每次提交事务时只是把数据刷到系统buffer中，再每隔一秒刷新到redo log文件

redo log的写入机制

redo log文件存储是环状，一个指向当前正在写入的地址，一个指向刷盘的地址，这两个之间的间隔代表可以写入的空间。ib_logfile0ib_logfile1ib_logfile2

redo log的LSN机制

文件的逻辑序列号，用来比对写入到磁盘中的和内存中的位置是不是一致的

5、undo log是什么？有什么作用？如何保证事务的一致性？

undo log记录的是事务变更时的逆向过程数据

作用：用来恢复事务回滚前数据状态

undo log存储方式：

通过回滚段的方式管理，每个回滚段里有多个segment。

undo log页的重用怎么实现？

每次事务执行完之后并不会立刻回收回滚段，而是判断segment是否可重用，如果可以的话，那么一个segment是属于多个事务使用的

undo log如何实现MVCC

undo log记录了事务内修改操作的数据列表：事务内insert操作执行完成后删除；update、delete操作会将事务内对行记录的变更都记录下来，通过版本号方式代表每行不同版本下的记录

MVCC：读取数据时，如果行数据对当前事务不可见，可以通过读取undo log中事务开始之前的数据

6、binlog 和 redo log 有什么区别？

binlog是mysql记录数据变更的日志文件；redo log是InnoDB引擎记录事务操作数据页的修改内容

binlog记录内容：根据设置的模式不同记录的内容不同

Row模式：记录的是变更的数据内容

statement模式：记录的是修改数据的sql语句内容

Mixed模式：Row模式和statement模式的混合

7、MySQL的事务机制是如何实现的？

MySQL开启事务时，将待更新的数据加载到内存中——> 记录事务开始前数据到undo log中——> 修改数据并记录到redo log中——> 提交事务 ——>数据变更写入到binlog；redo log日志记录刷新到磁盘上

4、MySQL事务

事务的特性：
A：原子性。一组SQL语句要么都执行，要么都不执行
C：一致性。事务执行前后，数据时一致的。一致的理解，例如转账，前后总额都是一样的
I：隔离性。事务之间执行时是互相隔离的
D：持久性。事务提交后对数据的修改是永久的
保证了AID、也就实现了C

并发事务中存在的问题？

  • 脏读：事务A修改数据之后事务B读取到了修改之后的数据，事务A回滚的话，事务B读的数据就是脏读
  • 丢失修改：事务A和事务B同时读取了数据，事务A修改并提交后，事务B在修改提交的话，会导致事务A的提交丢失
  • 不可重复读：事务期间多次读取数据，读到的数据不一致，数据被修改了
  • 幻读：事务期间多次读取数据，数据记录条数不一致，出现新增、删除的数据

不可重复读和幻读的区别？一个是数据被修改，一个是数据出现新增和删除 并发事务控制方式

锁 + MVCC

锁：共享锁、排他锁
表级锁，行级锁。与共享锁、排他锁是交叉搭配的关系

一致性非锁定读和锁定读：

一致性非锁定读：读取时 MVCC 采用版本控制，读第一遍version=1，再读是如果version！=1 则代表不可见
锁定读：读取的是最新数据，读到之后会对数据加锁

select ... lock in share mode
select ... for update
insert、update、delete

锁定读的情况下，如何保证一个事务之间读到的数据时一致的？

MVCC + Next-key-lock 解决幻读问题：

使用Next-key Lock

事务隔离级别

读未提交：读其他事务未提交的修改数据
读已提交：读取到其他事务已经提交的修改数据。通过MVCC机制实现
可重复读：事务内多次读取数据都是一致的。通过MVCC机制实现
串行化：不同事务之间串行执行

默认是可重复读

InnoDB对MVCC的实现

通过这三个内容实现：隐藏字段 + ReadView + undo-log

隐藏字段是指在每个数据记录行后有隐藏字段，记录操作当前记录的最新事务id、undo-log的数据快照地址；
ReadView：是指事务开始之后创建的当前事务与其他事务可见性的结构。数据可见性算法使用该结构
undo-log：分为两部分，一部分是insert语句，对其他事务必然不可见，一种是修改语句，在事务提交之后undo-log进入暂存阶段，等不可用时再清理掉

RR + MVCC 解决不可重复读问题：

在不可重复读的问题下，RC下的MVCC还是会出现问题；RR下的MVCC不会出现该问题

原因是创建ReadView的时机不同：RC每次读取时都会创建ReadView、RR事务开始后第一次读取才会创建ReadView

5、MySQL锁

InnoDB行锁，锁的是索引数据页上的数据

类型：

  • 行锁（Record）：
  • 间隙锁（Gap）：锁定的是行之间的间隙，不包括行记录本身
  • 临键锁（Next-key lock）：锁定的是行以及行的间隙，包括行记录本身。默认使用的是该锁

操作主键或者唯一键索引时，临键锁会降级为行锁

举例🌰：

主键等值查询&&值存在
主键等值查询&&值不存在
主键范围查询：

  • A<= key < B
  • A < key <= B
  • A < key < B

共享锁（S）和排它锁（X）

select... lock in share mode
select... for update

一般的select不会加锁，因为有MVCC控制读取正确版本的数据

意向锁是什么含义？

意向锁是存储引擎在对数据进行加锁前首先获取意象锁

6、MySQL优化

1、单张表数据超过500w时尽量分表存储
2、查询数据时都要带有时间限制
3、 主库负责写入，从库负责读取 4、 分表：垂直分区、水平分区
垂直分区指的是数据列进行拆分，有原来20个字段拆分为单表10个字段
水平分区：拆分的是表中记录的数据量，从500w条数据拆分为单标100w数据；一般分表的时候会进行分库，这样可以将表的数据分布式存储在不同的机器上
分表之后的唯一id，美团的leaf 生成分布式唯一id

5、 一条sql语句执行的很慢的原因
每次执行都很慢的话：是否用到了索引；没有用到索引有可能是自己查询字段顺序不对，也有可能是系统评估不走索引
偶尔执行很慢：是不是业务高峰期，或者是执行期间遇到锁表情况
6、limit语句在偏移量非常大时查询效率变低
原因：如果条件是非主键索引的话，使用limit a，b查询时会根据索引查到id，在根据id回表查询a+b条数据，最终保留a，b的数据
解决办法：①、根据非主键索引查询到limit a，b下最小的id，再使用id查询主键索引的列记录

avatar
文章
阅读
粉丝