数据库面试（一）--- 基础知识

事务

事务的四大特性ACID

• 原子性（Atomicity）
  事务是不可分割的最小操作单位，要么同时提交成功，要么全部失败回滚
• 一致性（Consistency）
  事务操作前后保持一致性状态，数据总量不变
• 隔离性（Isolation）
  多个事务之间互相独立，一个事务在最终提交前，对其他事务是不可见的
• 持久性（Durability）
  一旦事务提交，它所做的修改将会永远保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失

事务的实现原理
事务的原子性、一致性、持久性是通过redo log（重做日志）和undo log（回滚日志）来实现。隔离性是通过锁来实现的。

• undo log保存了数据修改之前的值，方便数据库回滚的时候做undo操作。undo log记录了sql执行的相关信息，需要回滚的时候回执行sql语句相反的操作。
• redo log记录了数据库对buffer pool中数据的修改操作，用于crash recovery。保证了MySQL宕机也不会影响持久性，实现事务的持久性。

并发一致性问题

• 脏读（读取了未提交的数据）
  一个事务读取到另一个事务中没有提交的数据
• 不可重复读（前后读取的结果不一样）
  在同一个事务中，两次读取到的数据不一样
• 幻读（前后读取的数据总量不一样）
  一个事务读了几行数据，另外一个事务插入了几行数据，则第一个事务再查询数据总量不同

幻读和不可重复读的区别

• 幻读是读取了其他事务新增或删除的数据，针对insert和delete操作；
• 不可重复读是读取了其他事务修改的数据，真多update操作

隔离级别

隔离级别	含义	问题	默认
未提交读 read uncommtted	事务中的修改即使未提交，对其他事务也可见	脏读、不可重复读、幻读
提交读 read committed	事务所做的修改在提交前对其他事务是不可见的	不可重复读、幻读	Oracle、SQL Server
可重复读 repeatable read	同一个事务多次读取同样数据的结果是一样的	幻读	MySQL
可串行化serializable	强制事务串行执行，事务之间完全不干扰	无

MySQL的默认隔离级别是可重复读，因为binlog会在事务提交的时候才记录sql语句，所以binlog记录的sql语句可能不是sql语句的执行顺序。

锁机制

从锁的属性来分：共享锁、排他锁

• 共享锁：读锁/S锁。多个事务可以对同一个数据共享同一把锁。持有锁的事务都可以访问数据，但是只能读不能修改。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。
• 排他锁：写锁/X锁。只有一个事务能够获得排他锁，其他事务都不能获取该行的锁。排他锁的目的是在数据修改时候，不允许其他人读取和修改，避免出现脏数据和脏读的问题。InnoDB会对update/delete/insert语句自动添加排他锁。

从锁的粒度来分：行锁、表锁、页锁、全局锁
1、行锁

• 自增锁：通常是针对MySQL当中的自增字段。如果有事务回滚这种情况，数据会回滚，但是自增序列不会回滚。
• 记录锁（Record Lock）：范围只是表中的某一条记录。可以避免重复读和脏读的问题。
• 间隙锁（Gap Lock）：在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。查询条件必须命中索引，间隙锁只会出现在REPEATABLE READ（重复读）的事务级别中。避免了幻读问题。
• 临建锁（Next-key Lock）：InnoDB的行锁默认算法，记录锁和间隙锁的组合，临建做会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也锁住，再之它会把相邻的下一个区间也锁住。。触发条件是范围查询并命中，查询命中了索引。避免了脏读、重复读、幻读问题。

2、表锁

• 表共享读锁
• 表排他写锁
• 意向锁：加锁时增加一个表是不是被加锁的状态，避免了对整个所以树的每个节点扫描是否加锁。当一个事务试图对整个表进行加锁之前（共享或排他），首先需要获得这个表的意向锁。是InnoDB自动添加的一种锁，不需要用户干预。

3、页锁：粒度介于行锁和表锁之间。一次锁定相邻的一组记录。特点是开销和加锁时间介于表锁和行锁之间，会出现死锁，并发度一般。

4、全局锁：Flush tables with read lock。加锁之后整个数据库实例都处于只读状态。所有的数据变更操作都会被挂起。一般用于全库备份的时候。

三大范式

• 第一范式（1NF）
  属性不可分，每一列都是不可分割的原子数据项
• 第二范式（2NF）
  在1NF基础上，非主键列完全依赖于主键，而不是主键的一部分
• 第三范式（3NF）
  在2NF基础上，非主键列不依赖于其他非主键，消除传递依赖

数据库逻辑结构

MySQL分层

• 顶层：接入层，不同语言的客户端通过mysql的协议与mysql服务器进行连接通信，接入层进行权限验证、连接池管理、线程管理等。
• mysql服务层：包括sql解析器、sql优化器、数据缓冲、缓存等
• mysql存储引擎：mysql中存储引擎是基于表的
• 系统文件层：保存数据、索引、日志等

MySQL查询语句

主键和外键
主键可以唯一地标识一行，是可以被外键有效引用的对象
外键是为了保存数据的一致性，主表的外键可以有多个，子表必须把外键当主键

datetime和timestamp
datetime时间范围为1001-9999，与时区无关，占8字节，默认值为null
timestamp时间范围为1970-2038，与时区有关，占4字节，默认值为当前时间

delete、drop、truncate
drop直接删除整张表；truncate删除的是表中的数据；delete删除可以在后面添加where条件；truncate、drop是DDL语句，执行后就提交，不能回滚；delete可以回滚

where和having
where在分组之前进行限定，如果不满足条件则不参与分组；having在分组之后进行限定，不满足条件，则不会被查询出来
where后不可跟聚合函数，having可以进行聚合函数的判断