三大范式

NF 概念
第一范式 每个列都不可以再拆分（原子性）
第二范式 在1NF的基础上，非主键列完全依赖于主键（不存在部分依赖）
第三范式 在2NF的基础上，非主键列直接依赖于主键（不存在传递依赖）

存储引擎

存储引擎是数据库底层软件组织，是一套文件系统的实现
数据库管理系统（DBMS）使用存储引擎进行创建、查询、更新和删除数据
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能
存储引擎主要有： MyIsam、InnoDB、Memory、Archive、Federated 等

  • Memory：所有数据都在内存中，处理速度快但是安全性不高，不常用。

MyISAM和InnoDB区别

功能 区别
行级锁 MyISAM 只有表级锁，InnoDB 支持行级锁（默认）和表级锁
事务 &
崩溃后的安全恢复		 MyISAM 强调的是性能，每次查询具有原子性，执行速度比InnoDB更快，但是不提供事务支持
InnoDB 支持事务,外键等高级数据库功能。具有事务提交、回滚和崩溃修复能⼒的事务安全型表
外键 MyISAM 不支持，InnoDB支持
全文索引 MyISAM 支持，InnoDB(1.2.x)开始支持

索引

索引类型

索引 描述
主键索引 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引 基本的索引类型，没有唯一性的限制，允许为NULL值。
全文索引 是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
组合索引 多列值组成一个索引，用于组合搜索，效率大于索引合并
  • 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行

树的演变

在线测试

从图中可以看到，我们为 user 表(用户信息表)建立了一个二叉查找树的索引。
如果想找到12，只需要经过3次比较即可得到结果。

但是在极端情况下，二叉树会如此退化成链表

由前苏联的数学家 Adelse-Velskil 和 Landis 在 1962 年提出的高度平衡的二叉树，根据科学家的英文名也称为 AVL 树。

在线测试

在平衡二叉树中，为了解决平衡的问题，它引入了左旋（变为右孩子的左孩子）和右旋（变为左孩子的右孩子）的方法，使得任何结点的两个子树的高度差不大于1，这就会使得查询的效率能够稳定在O(logN)。

在线测试

红黑树是用非严格的平衡来换取增删结点时候旋转次数的降低，任何不平衡都会在三次旋转之内解决，红黑树并不追求“完全平衡”，它只要求部分地达到平衡要求，降低了对旋转的要求，从而提高了性能。

特性：

  • 结点是红色或黑色。根结点是黑色。
  • 每个叶子结点都是黑色的空结点（nil结点）。
  • 每个红色结点的两个子结点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色结点)
  • 从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点。

索引结构

MySQL索引使⽤的数据结构主要有BTree索引和哈希索引。

  • 哈希索引底层数据结构是哈希表，绝⼤多数需求为单条记录查询的时候，适合用哈希索引，查询性能最快
    哈希索引可以根据数据的hash值直接定位到索引数据的存储位置，相当于知道了数组的下标，然后根据下标取数据，效率极高。
  • 绝大多数场景下，都会使用BTree索引。
  • 其他的索引：Fulltext索引、 R-Tree索引。

为什么不选择平衡二叉树作为数据库的索引呢？

  • 平衡二叉树每次访问一次结点就对应数据库的一次IO，因为操作系统和磁盘之间一次数据交换是以页为单位的，一页大小为 4K，即每次IO操作系统会将4K数据加载进内存。但是，在二叉树每个结点的结构只保存一个关键字，一个数据区，两个子结点的引用，并不能够填满4K的内容。第一，这会浪费4k的空间，第二，当平衡二叉树的结点达到百万级以上的时候，树的高度将会非常高，如果数据刚好在根结点，就会发送很多次的IO，使得大量时间花费在IO上。
  • 平衡二叉树的查找效率不稳定，有时候只需要发生一次IO，有时要发生成千上万次的IO。

B树

B树（Blance-Tree）就是可以单结点存储多键值和数据的平衡树，每一个结点我们称之为页（Page），即一页数据。
B树每个结点存储了更多键值和数据，把键值和数据都放在一个页当中，并且每个结点拥有了更多子结点，子结点的个数一般称为阶。B树在查找数据I/O次数也就大大减少，查找效率比AVL高很多。

B+树

B+树是对B树的进一步优化。B+树的非叶子结点是不存储数据的，仅存放键值。之所以这样做，是因为数据库中页的大小是固定的（InnoDB默认16KB），如果不存储数据，就可以存储更多键值，结点个数就越大，查找数据进行磁盘I/O次数进一步减少。

为什么用B+树不用B树

  • B树只适合随机检索，而B+树适合随机检索和顺序检索（叶子结点是链表）
  • B+树空间利用率更高，I/O次数少，硬盘读写代价低（非叶子结点仅存放键值）
  • B+树的查询效率更加稳定
    B树的查找只需找到匹配元素，最好的情况下查找到根结点，最坏的情况下会查找到叶子结点。所以性能很不稳定。
    B+树中随机索引时，所有关键字的查找都要查找到子结点，查找路径相同是相同的。所以更加稳定。
  • B+树遍历效率更高（叶子结点是链表）
  • B+树增删结点效率更高（叶子结点是链表）

B*树（仅了解）

在B+树的基础上，中间结点也都增加了指针，提高了结点的利用率。

聚簇/非聚簇索引

MyISAM和InnoDB两种存储引擎对B+树索引的实现方式是不同的。

MyISAM

主索引和辅助索引叶子结点的data域存放的是数据记录的地址。
在索引检索的时候，首先按照B+树算法搜索索引，则取出指定的Key对应的data域的值（地址），然后根据地址读取相应的数据记录。这被称为 非聚簇索引。

InnoDB

数据文件本身就是索引文件。
主键索引（主索引） 的叶子结点存放的是整行数据 。
非主键索引（辅助索引/二级索引） 的叶子结点存放的是主键的值 。根据辅助索引查找时，需要先取出主键的值，再走一遍主索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

事务

ACID

特性 描述
原子性（Atomicity） 一个事务中的操作要么全部成功，要么全部失败
一致性（Consistency）[参考] 一个事务处理前后结果应与其所抽象的客观世界中真实状况保持一致
A账户总共5000元，不论怎么给B转，AB账户总额还是5000，这就是一致性
隔离性（Isolation） 一个事务不应被其他事务干扰。
持久性（Durability） 一个事务结束后对数据库的改变是永久的

隔离级别

级别 描述 脏读 不可重复读 幻读
读未提交 事务修改了数据之后就算还没有提交，对其他事务也是可见的 ✔ ✔ ✔
读已提交 事务提交完，其他事务才能看见新数据（大多数据库默认） - ✔ ✔
可重复读 保证同一事务中多次读取的数据是一样的（MySQL默认）
理论上存在幻读，InnoDB解决了幻读（可重复读配合间隙锁 不会出现幻读）		 - - ✔
串行化 强制事务串行执行（保证一个事务执行完才执行下一个事务） - - -

脏读是指一个事务在处理数据的过程中，读取到另一个事务 未提交 的数据。

1
2
3
4
5
[事务1] BEGIN;
[事务1] update user set name = '汐涌及岸' where id = 1;
[事务2] select name from user where id = 1;
     -- 此时事务1未提交,但事务2已读到'汐涌及岸'【脏读】
[事务1] ROLLBACK;

不可重复读是指读一个事务范围内的多次查询却返回了不同的结果。这是由于在两次查询之间，数据被另外一个事务 修改 并提交了。

1
2
3
4
5
6
7
8
[事务1] BEGIN;
[事务1] select name from user where id = 1;
     -- 此时事务1读到'汐涌及岸'
[事务2] BEGIN;
[事务2] update user set name = '汐涌及岸二号' where id = 1;
[事务2] COMMIT;
[事务1] select name from user where id = 1;
     -- 此时事务1读到已被事务2提交的'汐涌及岸二号'【不可重复读】

幻读和不可重复读相似，不同的是，幻读是指被其他事务 增删 数据。

1
2
3
4
5
6
7
8
[事务1] BEGIN;
[事务1] select count(*) from user;
     -- 此时事务1读到数量为1
[事务2] BEGIN;
[事务2] insert into user(id,name) values(2,'汐涌及岸二号')
[事务2] COMMIT;
[事务1] select count(*) from user;
     -- 此时事务1读到数量为2，像出现幻觉一样【幻读】
  • 为什么要区分 不可重复读 和 幻读 ？
    简单来说，解决不可重复读（修改）的方法是 锁行，解决幻读（增删）的方式是 锁表（InnoDB依靠间隙锁解决）。
1
2
3
4
5
SELECT @@tx_isolation;                                    --查看当前隔离级别
set session transaction isolation level read uncommitted; --读未提交
set session transaction isolation level read committed;   --读已提交
set session transaction isolation level repeatable read;  --可重复读
set session transaction isolation level serializable;     --串行化

锁

  • 锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。

锁的粒度

粒度 -
行级锁 只针对当前操作的行进行加锁。大大减少数据库操作的冲突。锁定粒度小，开销大。会出现死锁。
页级锁 加锁时间、锁定粒度、开销 界于表锁和行锁之间，会出现死锁，并发度一般。【BDB引擎默认，仅了解】
表级锁 加锁快，锁定粒度大，发出锁冲突的概率最高，不会出现死锁，并发度最低。

锁的类别

粒度 -
共享锁（S锁） 用户读取数据时，对数据加共享锁
使用：select ... lock in share mode ，其他用户 只能读不能写
排他锁（X锁） 用户写入数据时，对数据加排他锁
使用：select ... for update ，其他用户 不能读不能写
意向共享锁 加共享锁前，对数据先加意向共享锁（Intention S Lock）
其他用户先获取一下意向锁，获取到就不再继续操作，提高效率
意向排他锁 加排他锁钱，对数据先加意向排他锁（Intention X Lock）
自增锁 自增锁（Auto-inc Lock）是一种特殊的表级锁，专门针对事务插入 AUTO_INCREMENT 类型的列。
最简单的情况：一个事务往表中插入记录s，其他事务的插入必须等待，保证事务插入的行主键连续

排他锁的算法

算法 -
记录锁 单条记录上的锁
间隙锁 键值在条件范围内但并不存在的记录，叫做间隙。它是基于临键锁实现的。
能够阻止多个事务将记录插入到同一范围内，解决了幻读问题。

关闭方式：将事务隔离级别设置为读已提交read committed
     将参数innodb_locks_unsafe_for_binlog设置为1
临键锁 间隙锁 + 记录锁。锁定一个范围，并包含记录。
每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。是对B+树底层的节点范围加的锁。

乐观锁和悲观锁

乐观锁 就是认为不会发生并发冲突，进行操作时不加锁，只在提交操作时检查是否违反数据完整性。
乐观锁不是数据库自带的，需要我们自己去实现。

  • 通常实现是这样的：
    给数据表加一个版本(version)字段，更新前查一下版本; 更新后，将那条记录的版本号加1。
    如果更新时version值与刚刚获取的version的值不相等，则不进行更新操作。
1
2
3
4
5
6
7
8
-- 查询出商品信息和版本（此时version=1）
select (status,status,version) from goods where id=1;
-- 根据商品信息生成订单
insert order ...;
-- 修改商品status为2
--    此时version=1说明生成订单期间没被其他人操作，可以更新
--    如果version=2说明已经有其他程序操作了，则不再进行操作
update goods set status=2,version=version+1 where id=1 and version=#{version};

除了自己手动实现乐观锁之外，许多框架已经封装好了乐观锁的实现，如hibernate，需要时，可自行搜索”hiberate 乐观锁”。

悲观锁 就是认为会发生并发冲突，进行每次操作时都要获取锁才能操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。
悲观锁由数据库实现（共享锁、排他锁都是悲观锁）。

死锁

在MySQL中就是指多个事务互相占用对方资源，导致恶性循环。

解决方案：

  • 尽量约定以相同的顺序访问表
  • 尽可能一次锁定所需要的所有资源
  • 如果非常容易产生死锁，升级锁的粒度，使用表级锁
  • 如果业务处理不好，可以用分布式锁或者使用乐观锁

隔离级别和锁的关系

事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节

隔离级别 锁实现
读未提交 读取时不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
读已提交 读取时对数据加共享锁，语句执行完后释放
可重复读 读取时对数据加共享锁，事务执行完才释放（事务提交之前不释放）
串行化 锁定整个范围的键，并一直持有锁，直到事务完成。

MVCC机制

MVCC多版本并发控制（Multi-Version Concurrency Control）一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC在InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读写冲突，做到即使有读写冲突时也能不加锁，非阻塞并发读。
MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念。

当前读和快照读

当前读

像共享锁select ... lock in share mode, 排他锁select ... for update 这些操作都是一种当前读。当前读指的是 它读取的是记录的最新版本，读取时要保证其他并发事务不能修改当前记录，会对当前读取的记录进行加锁。

快照读

不加锁的select ...操作就是快照读，快照读的前提是隔离级别不是串行化（会退化成当前读）。
快照读的实现就是基于MVCC。可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。
既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

MVCC实现原理

MVCC模型在MySQL中的具体实现则是由 4个隐式字段，undo log ，Read View 等去完成的。

  • 隐式字段

    隐式字段
    		 作用
    DB_ROW_ID (6byte) 隐含的自增ID（隐藏主键），如果表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
    DB_TRX_ID (6byte) 最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
    DB_ROLL_PTR (7byte) 回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
    DELETED_BIT (1byte) 记录被更新或删除并不代表真的删除，而是删除flag变了

  • undo log
    主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里，当事务进行回滚时可以通过undo log 里的日志进行数据还原。

  • Read View
    Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
    Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

运行流程

https://zhuanlan.zhihu.com/p/52977862

主从复制

主从形式

mysql的主从复制非常灵活

主从复制步骤

  • 主库的增删改操作被写到binlog
  • 从库发起连接，连接到主库，创建一个I/O线程
  • 主库创建binlog dump线程，把binlog发送I/O线程
  • I/O线程读取主库传过来的binlog内容写入到中继日志文件relay log
  • SQL线程从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到从库

binlog的录入格式

格式 区别
statement 会记录每一条修改数据的sql语句
优点：不需要记录每一行的变化，减少了日志量，节约了IO，提高性能。出现最早，兼容较好。
缺点：由于sql的执行是有上下文的，因此在保存的时候需要保存上下文相关的信息。同时还有一些使用了函数之类的语句无法被记录复制。
row 会记录每一行数据被修改的细节
优点：所有更改都可以被复制，比statement更加安全。对主库而言只有少量行锁，从而实现较高的并发性。
缺点：有些操作，会导致大量行的改动(比如alter table)，会导致保存的信息太多，日志量太大
mixed 折中方案，普通操作使用statement，无法使用statement时使用row
新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

主从延迟

原因：一个服务器开放N个连接给客户端，这样有会有大并发的更新操作，但是从服务器的读取binlog的I/O线程和SQL线程都仅有一个，任务量大。当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表 很容易造成relaylog堆积，产生延迟。

解决方案：

  • 做sharding，通过scale out打散写请求。或考虑升级到MySQL 5.7+，开启基于逻辑时钟的并行复制。
  • 主服务器配置syncbinlog=1，innodbflushlogattrxcommit = 1 之类的设置等。
  • 选择更好的硬件设备作为slave
  • 把一台从服务器当作为备份使用，而不提供查询，那边他的负载下来了，执行relay log里面的SQL效率自然就高了。
  • 增加从服务器，还是分散读的压力，从而降低服务器负载。

分库分表

水平拆分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。
当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直拆分

可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

一致性哈希

分布式的存储系统，文件分布在哪台机器由哈希算法来决定。

  • 想要加一台机器时，如果是非一致性哈希，就需要停下来等所有文件重新分布一次才能对外提供服务。
  • 当一台机器掉线的时候尽管只掉了一部分数据，但所有数据访问路由都会出问题。这样整个服务就无法平滑的扩缩容，成为了有状态的服务。

要想实现无状态化，就要用到一致性哈希了

  • 简单来说，我们有50台机器，一致性哈希会假想我们有 232 台假想机器，如果分配到的是不存在的机器，就往下找到第一个真实存在的机器放进去。

一致性哈希可以用在任何需要动态扩容缩容（弹性）的场景：分表、分库、分布式系统 等~

性能优化

执行计划 EXPLAIN

显示数据库引擎对于SQL语句的执行的详细情况：其中包含了是否使用索引，使用什么索引，索引的相关信息等。

字段 描述
id 语句中各个子查询的执行优先级。不同时，越大越先被执行；相同时，顺序由上至下依次执行
为null时，表示一个结果集，不需要使用他查询
select_type 查询类型：simple，primary，subquery，derived，union，union_result
table 查询涉及到的数据表
partitions 查询涉及到的分区
type 访问类型：可以看到有没有走索引
all，index，range，index_merge，ref_or_null，ref，eq_ref，system，const
possible_keys 可能使用的索引
key 实际使用的索引。如果为NULL，则没有使用索引
key_len 索引字段的最大可能长度，并非实际使用长度。在不损失精确性的情况下，长度越短越好。
ref 查询的表的连接匹配条件（哪些列或常量被用于查找索引列上的值）
rows 预估为了找到所需的行而要读取的行数
filtered 返回结果的行占需要读到的行(rows 列的值)的百分比。
Extra 额外信息：using index，using where，using tempoary，using filesort 等。[参考]

慢查询日志

慢查询日志（slow_query_log）是用于记录执行时间超过临界时间的SQL日志，能够快速定位慢查询。

1
2
3
4
5
6
7
8
9
show variables like 'slow_query_log';  --慢查询日志是否开启
--| slow_query_log |    OFF     |

set GLOBAL slow_query_log = on;        --开启慢查询日志

show variables like 'long_query_time'; --临界时间值（单位秒）
--| long_query_time | 10.000000 |

set long_query_time= 0.5;              --设置临界时间

一旦SQL超过了临界时间就会被记录到xxx-slow.log中。

超大分页解决方向？

  • 数据库层面：limit 100w,10 在MySQL中不是跳过前100w行，而是取100w+10行，再丢弃100w行
    这种情况可以减少load的数据列：利用延迟关联或者子查询优化sql（先利用索引覆盖定位id字段，再通过id查询）
  • 从需求的角度减少这种请求：设计成直接跳转到几百万页之后的具体某一页后
    只允许逐页查看或者按照给定的路线走，做到 可预测，可缓存。

CPU飙升如何处理？

  • 先使用top命令确认一下是不是mysqld导致的
  • show processlist 看看里面跑的session情况
    找出消耗高的sql，看看执行计划是否准确, index是否缺失，或者实在是数据量太大造成。
    kill掉这些线程，进行相应的调整。
  • 也有一种情况是，sql消耗不是很高，但是有大量的会话突然连接进来
    这种情况就结合应用来分析调整，比如限制连接数