MySQL

连接

一些默认情况
- join == inner join
- left join == left outer join
- MySQL没有full outer join，只能用left join union right join去模拟
内连接和外连接的区别？
- 内连接只输出满足两表on条件的行对应的列，外连接则可以输出不满足on条件的行。
内连接和左连接的区别？
- 以左边的表为驱动表，用左表的每一项去匹配右表的每一项。内连接会选出符合on条件的行，并输出对应的列；左连接会选出符合on条件的行（如果某一行没有匹配上右表的任意一行，则右表的列用null代替），然后输出对应的列。
union和union all
- union会做去重操作，而union all不会。所以union all的运行效率会高一些。

表设计

三大范式
- 第一范式：表的每一列都是不可再分割的，比如库存表中的物品和数量肯定是要分开的。
- 第二范式：以第一范式为基础，不能存在部分函数依赖，即非主键列要依赖于全部主键，比如成绩表中，主键为(学生id, 课程id)，所有的列为（学生id，课程id），分数，姓名只依赖于学生id，那么姓名就不应该出现在该表中。
- 第三范式：以第二范式为基础，不能存在传递函数依赖，比如学生表中，主键为(学生id)，所有的列为(学生id，姓名，系id)，此时系主任就不应该出现在表中，因为可以通过 学生id → 系id → 系主任，推出系主任。

事务和锁机制

前提：
- MySQL的记录中有一个隐藏列用来存储事务id；
事务的特性
- 原子性：事务内的语句要么全部成功执行，要么全部失败回滚。用undo日志来保证。
- 一致性：事务使数据从从一个一致性状态到另一个一致性状态。一致性状态：管理员定义的数据库约束条件，比如在在银行系统中，用户的余额不能小于0，转账不会使两个用户的总余额发生变化。建表时开启约束。create table account(id int, balance int, check(balance >= 0));
- 隔离性：在某些隔离级别下，一个事务不能感知到另一个未提交事务对数据库的改变。通过MVCC和锁保证。
- 持久性：一旦事务提交，事务对数据库的改变会持久化到磁盘，不会丢失。通过redo日志和binlog保证。
事务的提交
- 执行create和alter或者drop语句时会隐式提交事务，即使没有开启自动提交。
实务操作
- 开启事务
  - begin;
  - start transaction;后面可以跟read only或者read write（默认）。
- 提交事务
  - commit;
- 回滚事务
  - rollback;
分配事务id的时机。
- 对于只读事务来说，对临时表进行增删改操作时才会为事务分配事务id，否则不分配事务id。
- 对于读写事务来说，对表进行增删改操作时才会为事务分配事务id，否则不分配事务id。
隔离级别(set session transaction isolation level serializable)
- 读未提交：直接读取表中最新的记录，不管事务有没有提交。可能发生脏读、不可重复读、幻读。
- 读已提交：每次执行select的时候生成一个ReadView，可以防止脏读，但还是可能出现不可重复读和幻读现象。
- 可重复读：在首次执行select的时候生成一个ReadView，可以防止脏读和不可重复读，但可能会发生幻读现象。
- 串行化：每次select时都会在表上加share锁。
ReadView：
- 是一个四元组：（m_ids，min_trx_id，max_trx_id，creator_trx_id）
  - m_ids：生成ReadView时，当前系统的活跃事务id列表。当事务介于min_trx_id和max_trx_id之间，则判断是否在列表内，如果不在，则可以访问。
  - min_trx_id：活跃事务id列表中最小的那个id。可以访问比这个小的记录。
  - max_trx_id：生成ReadView时，系统应该分配给下一个事务的事务id值。不可访问。
  - creatot_trx_id：生成该ReadView的事务的事务id。可以访问等于这个的记录。
快照读和当前读
- 快照读：通过MVCC和ReadView，undo日志。
- 当前读：在查询的时候加锁，串行化隔离级别会自动加锁。
加锁方式
- 加共享锁：select *** from *** lock in share mode;
- 加排他锁：select *** from *** for update;