数据库基础知识

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字段不是有序的

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

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

索引下推

索引失效场景

select *

未遵循最左匹配原则

在索引列上进⾏计算、函数、类型转换

使⽤%开始的like查询

or查询

MySQL语句的执⾏计划

explain 语句，获取优化器优化之后的执⾏⽅式

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的条件相当于⽤到了索引下推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机制

⽂件的逻辑序列号，⽤来⽐对写⼊到磁盘中的和内存中的位置是不是⼀致的

写redo log和binlog的时机

①、预提交redo log的写⼊ ——》②、提交binlog写⼊——》③、提交写⼊redo log

这样流程可以保证事务操作数据的完整性：预提交redolog，系统崩溃存在两种情况：1、写⼊了binlog，则按照执

⾏预提交的正式提交；2、没有写⼊binlog，则将事务操作回滚，使⽤undo log恢复数据

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事务开始后第⼀次读取才会创建

ReadView5、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优化

单张表数据超过500w时尽量分表存储

查询数据时都要带有时间限制

主库负责写⼊，从库负责读取

分表：垂直分区、⽔平分区

-- 垂直分区指的是数据列进⾏拆分，有原来20个字段拆分为单表10个字段

--⽔平分区：拆分的是表中记录的数据量，从500w条数据拆分为单标100w数据；⼀般分表的时候会进⾏分库，这

样可以将表的数据分布式存储在不同的机器上--分表之后的唯⼀id，美团的leaf ⽣成分布式唯⼀id

⼀条sql语句执⾏的很慢的原因

每次执⾏都很慢的话：是否⽤到了索引；没有⽤到索引有可能是⾃⼰查询字段顺序不对，也有可能是系统评估

不⾛索引

偶尔执⾏很慢：是不是业务⾼峰期，或者是执⾏期间遇到锁表情况

limit语句在偏移量⾮常⼤时查询效率变低

原因：如果条件是⾮主键索引的话，使⽤limit a，b查询时会根据索引查到id，在根据id回表查询a+b条数据，

最终保留a，b的数据

解决办法：①、根据⾮主键索引查询到limit a，b下最⼩的id，再使⽤id查询主键索引的列记录