首页

MySQL（存储引擎为InnoDB）

三大范式

第一范式（1 NF）：字段不可再拆分。 第二范式（2 NF）：表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。 第三范式（3 NF）：在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取 某非主键字段 B。

基础架构

客户端 -> 服务器（连接器-查询缓存-分析器-优化器-执行器）-> 存储引擎 -> 文件系统

数据存储

依次可以分为：表空间、段、区、页、行。

分区表

分区表是一个独立的逻辑表，但是底层由多个物理子表组成。MySQL使用【PARTITION BY】定义每个分区存放的数据，分区表的索引只是在各个底层表上各自加上一个完全相同的索引，主要目的是将数据按照一个较粗的粒度分在不同的表中（如历史数据归纳）。

redolog & undolog & binlog

事务

ACID

并发事务带来的问题

隔离级别

事务执行流程

  1. 分配事务ID（自增值），开启事务，获取锁，没有获取到锁则等待；
  2. 执行器先通过存储引擎找到对应的数据页，如果Buffer Pool（缓冲池）存在数据则直接取出，没有则回查主键索引，从磁盘取出并放入缓冲池；
  3. 在数据页内找到需要具体的记录，修改后写入Buffer Pool；
  4. 存储引擎生成redolog和undolog到内存中，将redolog状态设为预提交；
  5. 将redolog和undolog写入文件中并调用fsync刷盘；
  6. 事务提交，服务器生成binlog并写入binlog文件中，调用fsync保证刷盘；
  7. 将redolog状态改为已提交，并释放所有锁。

AUTOCOMMIT机制

MySQL默认采用自动提交模式，即如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

MVCC

多版本并发控制协议，InnoDB会为每条记录添加额外字段：

优点是快照读不需要获取锁，提高了系统的并发度；缺点是需要维护每条记录的版本信息，且在检索行时需要判断版本是否可见，降低了查询的效率，同时还需要定期清理及时回收空间。

更新数据流程

  1. 获取排他锁；
  2. 修改记录；
  3. 写redolog和undolog；
  4. 设置当前事务ID，将回滚指针指向undolog历史数据。

ReadView

配合MVCC使用，组成：当前事务ID、当前进行中的事务ID集合、当前进行中的事务ID集合的最小值、当前将要分配的下一个事务Id（即事务ID的上限）。

RR级别只会在第一个查询时创建ReadView，而RC级别每次查询都会创建一个ReadView，所以RR级别快照读不会出现幻读。

查询过程：

  1. 查询到某条记录后，判断该版本记录的trx_id是否等于ReadView中的creator_trx_id是否相等，相等则表示可读直接返回，否则进行以下判断：
    1. 小于ReadView记录的最小事务号，则可读；
    2. 大于等于ReadView记录的最大事务号，则不可读；
    3. 在两者之间，则在ReadView记录的进行中的事务ID集合中查找当前版本事务ID，如果找不到则表示创建当前ReadView时该事务已经提交故可读，否则表示事务还未提交不可读。
  2. 如果当前版本不可读，通过回滚指针沿着undolog链向上查找历史版本，重复上面步骤。

锁

锁策略

锁类型

表级锁主要用于执行DDL语句。

意向锁：表级锁，包括意向共享锁（IS 锁）和意向排他锁（IX 锁）。

行级锁：

2PL

二阶段锁，事务期间加锁和解锁分为两个完全不相交的阶段，加锁阶段只加锁，解锁阶段只释放锁。

RR级别加锁行为

组合索引加锁

  1. delete from t where a>1 and a<5 and b=2 and c=1：组合索引上，gap锁5个（a值1到5之间），X锁两个（b=2），聚簇索引X锁两个（id=1,2）

  2. delete from t where a=3 and b>1 and b < 3 and c=1：组合索引上，gap锁3个（a值等于3且b值大于1小于3），X锁两个（b=2），聚簇索引X锁两个（id=1,2）

死锁产生条件

产生死锁原因

  1. 两个事务行锁加锁顺序不一致；
    • 批量更新时，按固定的顺序（如ID顺序）操作。
  2. 两个事务的间隙锁互斥，先执行删除就获得了间隙锁再执行插入就会被对方阻塞；
    • 事务级别调整到RC。
  3. 唯一索引插入导致死锁，第一个事务插入后回滚之后，其他两个事务通过一个当前读获取到了共享间隙锁，导致互相等待；
    • 使用insert on duplicate key update。
  4. 同一加锁行为，由于index_merge使用了多个索引，这多个索引对主键行锁的加锁顺序不一致。
    • 使用force index；
    • 创建组合索引。

索引

索引类型

索引策略

index_merge

index_merge是MySQL 5.1后引入的一项索引合并优化技术，它允许对同一个表同时使用多个索引进行查询，并对多个索引的查询结果进行合并后返回。在使用index_merge技术后，会同时执行两个索引，故可能导致死锁，可以使用【force index】操作避免。

索引失效场景

  1. 查询范围太大，即服务器认为走全表扫描会更快，包含使用负向扫描（NOT）的场景；
  2. 数据隐式转换，关联查询时字符集不同也会导致隐式转换字符；
  3. 对列使用函数；
  4. 对列进行运算；
  5. like使用左模糊；
  6. 组合索引不符合最左匹配原则。

索引选择策略

扫描行数是主要因素但并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。会统计基数用于预估行，即选取一定的数量的数据页，统计其不同值得到平均值。

  1. 主键索引优先；
  2. 可以使用覆盖索引；
  3. 扫描行数少；
  4. 可以用于排序；
  5. 索引大小。

show index from

主从复制

  1. 主库将数据库中数据的变化写入到binlog；
  2. 从库连接主库，主库会创建一个binlog dump线程来发送binlog，从库中的I/O线程负责接收更新的binlog；
  3. 从库的I/O线程将接收的binlog写入到relay log中；
  4. 从库的 SQL 线程读取relay log同步数据本地（再执行一遍SQL）。

为异步复制，主库只保证将数据变更写入binlog，但不关心是否被从库接收，缺点就是如果主库出现宕机且还未来的及将新写入的binlog发送给从库，此时若将从库升级为主库则会出现数据丢失。

EXISTS & IN

  1. 如果无法使用索引的情况下，MySQL会把IN的查询语句改成EXISTS去执行；
  2. IN查询在内部表和外部表上都可以使用到索引，Exists查询仅在内部表上可以使用到索引；
  3. 当子查询结果集很大，而外部表较小的时候，Exists的BNL算法的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN；而当子查询结果集较小，而外部表很大的时候，IN的外表索引优势占主要作用，此时IN的查询效率会优于Exists。

Block Nested Loop：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。

EXPLAIN

数据库调优策略

SQL题

取出每个科目所有分数排名前2的成绩

  1. 使用子查询： 
    select * 
from t t0 
where 2 >
(
  select count(distinct t1.score)
  from t t1
  where t1.subject = t0.subject and t1.score > t0.score
);
  2. 使用exists： 
    select * 
from t t0 
where exists
(
  select id 
  from t t1
  where t1.subject = t0.subject and t1.score > t0.score
  having count(distinct t1.score) < 2
);

    以上两种方式是一样的，主表都是全表扫描，子查询会走subject索引。

如果是找出第N高的成绩，则将<改为=号即可。

找出连续三天以上访客超过100的记录

select distinct t1.*
from t t1, t t2, t t3
where t1.people > 100 and t2.people > 100 and t3.people > 100
and
(
    (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)
    or (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)
    or (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)
)
order by t1.id;

树节点

select id, 
case when t.id=(select t1.id from tree t1 where t1.p_id is null) then 'Root'
when t.id in (select p_id from tree t2) then 'Inner'
else 'Leaf' end
as type
from tree t;