连接

  • 一些默认情况
    • 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;

存储引擎

  • InnoDB和MyISAM的对比
    • InnoDB支持事务；MyISAM不支持事务。
    • InnoDB支持行级锁和表级锁；MyISAM只支持表级锁。
    • InnoDB使用聚簇索引，查询时不需要回表；MyISAM查询时需要回表（默认情况下只为主键建立B+树索引）。