设计与使用

范式

第一范式（原子性）：数据表中的每一列（每个字段）都不可以再拆分。

第二范式（唯一性）：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式（排除冗余性）：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。非主键属性间不存在函数依赖。

UML

使用

存储函数，存储过程

游标

视图：视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成

基础架构

日志系统

redo log vs. binlog

binlog

binlog主要用于实现mysql主从复制、数据备份、数据恢复。

binlog记录了数据库所有的ddl语句和dml语句，但不包括 select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到 binlog日志中。

数据定义语言：简称DDL(Data Definition Language)
- 作用：用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
数据操作语言：简称DML(Data Manipulation Language)
- 作用：用来对数据库中表的记录进行更新。关键字：insert，delete，update等
数据查询语言：简称DQL(Data Query Language)
- 作用：用来查询数据库中表的记录。关键字：select，from，where等
数据控制语言：简称DCL(Data Control Language)
- 作用：用来定义数据库的访问权限和安全级别，及创建用户。

redo log

WAL, crash-safe 能力

对比

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的(用于归档)，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

两阶段提交

www.51cto.com/article/705…

如果没有两阶段提交，那么 binlog 和 redolog 的提交，无非就是两种形式：

先写 binlog 再写 redolog。
先写 redolog 再写 binlog。

这两种情况我们分别来看。

假设我们要向表中插入一条记录 R，如果是先写 binlog 再写 redolog，那么假设 binlog 写完后崩溃了，此时 redolog 还没写。那么重启恢复的时候就会出问题：binlog 中已经有 R 的记录了，当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就会同步到 R 这条记录;但是 redolog 中没有关于 R 的记录，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，即数据库中没有该行记录，这就造成了数据不一致。

相反，假设我们要向表中插入一条记录 R，如果是先写 redolog 再写 binlog，那么假设 redolog 写完后崩溃了，此时 binlog 还没写。那么重启恢复的时候也会出问题：redolog 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个事务是有效的，通过该记录将数据恢复到数据库中;但是 binlog 中还没有关于 R 的记录，所以当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就不会同步到 R 这条记录，这就造成了数据不一致。

那么按照前面说的两阶段提交就能解决问题吗?

我们来看如下三种情况：

情况一：一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态的时候崩溃了，此时：

由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

情况二：假设写完 binlog 之后崩溃了，此时：

redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

情况三：假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。

由此可见，两阶段提交能够确保数据的一致性。

undo log

事务

隔离级别

MVCC

可见性规则

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况
1. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
2. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

（数组是创建readview时，活跃的事务id集合）

可重复读隔离级别：只需要在事务开始的时候创建readview，之后事务里的其他查询都共用这个一致性视图；
读提交隔离级别：每一个语句执行前都会重新算出一个新的readview。

索引

聚簇索引，二级索引：why自增主键？性能：顺序，少页分裂；空间：少
联合索引：基于多个列建立的索引，最左前缀原则
索引覆盖：查询列要被所建的索引覆盖，不必读取数据行；主键直接查；联合索引
索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
普通索引，唯一索引：
- 查询过程：无大差别。
- 更新过程：唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。
  - The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索

锁

全局锁，表级锁，行锁

全局锁：Flush tables with read lock (FTWRL)，做全库逻辑备份
表级锁：
- 表锁
- 元数据锁（MDL锁）：
  - 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
  - MDL 锁是系统默认会加的。
行锁（Innodb引擎有）：
- 两阶段锁：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
- 死锁：
  - 解决策略：
    - 策略1:直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
    - 策略2: 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
  - 死锁检测：热点行的性能问题（死锁检测浪费性能问题）：1.确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。2.控制并发度 3.将一行改成逻辑上的多行，减少锁冲突。

乐观锁，悲观锁

悲观锁：行锁，表锁，读锁，写锁，以及syncronized实现的锁均为悲观锁

乐观锁：使用版本号机制或CAS算法实现

共享锁，独占锁（S, X）

意向锁

意向共享锁，意向独占锁，IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

间隙锁

REPEATABLE READ隔离级别下解决幻读问题。

集群

数据复制

主从

同步复制与异步复制
配置新的从节点：一致性快照 + binlog
处理节点失效：
- 从节点失效：追赶式恢复
- 主节点失效：节点切换
复制日志的实现

数据分区

垂直分区

水平分区

查询优化

单表访问

访问方法

const：通过主键或者唯一二级索引列与常数的等值比较来定位一条记录
ref：普通的二级索引列与常数进行等值比较
ref_or_null：找出某个二级索引列的值等于某个常数的记录，还想把该列的值为null的记录也找出来。
range：利用索引进行范围匹配。
index：遍历二级索引记录的执行方式（不用回表）
all：全表扫描，直接扫描聚簇索引。

索引合并 index_merge

Intersection合并：某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集（读取二级索引的操作是顺序I/O，而回表操作是随机I/O）
Union合并
Sort-Union合并：先按照二级索引记录的主键值进行排序，之后按照Union索引合并。

要求：1.二级索引等值匹配（因为这样查询出的结果集是按照主键值排序的 O(n)）2.主键范围

连接

连接简介

驱动表，被驱动表：驱动表只需要访问一次，被驱动表可能被访问多次
内连接：驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集
外连接：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
- 左连接
- 右链接

连接原理

嵌套循环连接
使用索引加快连接速度
- eq_ref：对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式
基于块的嵌套循环连接
- join buffer

基于成本的优化

什么是成本

CPU，IO

单表查询的成本

优化步骤：

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价：
1. 聚簇索引占用的页面数（IO成本）：Data_length = 聚簇索引的页面数量 x 每个页面的大小
2. 该表中的记录数（CPU成本）：Rows 估计值
计算使用不同索引执行查询的代价：
1. 唯一二级索引, 再普通索引
  - 范围区间数量：单点或者范围：查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
  - 需要回表的记录数（估计的）：
    1. index dive：这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式。
    2. 如果区间最左记录和区间最右记录相隔不太远，那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量（计算方法如图）就可以了。
3. 基于索引统计数据的成本计算
4. （MySQL也会为表中的每一个索引维护一份统计数据）
5. - index dive 不适合单点范围太多的查询，比如 In
  - 估计回表的记录数 = 一个值的重复次数 * in的参数个数
6. 一个值的重复次数 ≈ Rows ÷ Cardinality
对比各种执行方案的代价，找出成本最低的那一个。

连接查询的成本

condition filtering：
- 查询优化器使用全表扫描的话，就直接使用表中记录的数量作为扇出值
- 查询优化器如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。
- condition filtering：就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条。
两表连接的成本分析：
- 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
- 1.尽量减少驱动表的扇出。2.对被驱动表的访问成本尽量低
多表连接的成本分析：
- 提前结束某种顺序的成本评估：会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。
- 系统变量optimizer_search_depth：如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析
- 根据某些规则压根儿就不考虑某些连接顺序：启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析。

Innodb统计数据

基于磁盘的永久性统计数据

innodb_table_stats

innodb_index_stats
- Cardinality

基于内存的非永久性统计数据

innodb_stats_method决定着在统计某个索引列不重复值的数量时如何对待NULL值。

基于规则的优化

条件简化

移除不必要的括号，常量传递（constant_propagation），等值传递（equality_propagation），移除没用的条件（trivial_condition_removal），表达式计算，HAVING子句和WHERE子句的合并。
常量表检测：
- 把通过这两种方式查询的表称之为常量表：
  - 查询的表中一条记录没有，或者只有一条记录。
  - 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。
- 在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉。

外连接消除

reject null, 交换驱动与被驱动表的顺序
- 在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

子查询优化

按返回的结果集区分子查询：

标量子查询
行子查询
列子查询
表子查询

按与外层查询关系来区分子查询：

不相关子查询
相关子查询

子查询在布尔表达式中的使用

IN子查询优化

物化表
1. 不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。
2. 该临时表的列就是子查询结果集中的列。
3. 写入临时表的记录会被去重
4. 基于内存的物化表有哈希索引，基于磁盘的有B+树索引

物化表转内连接
- 以下俩等价：

将子查询转换为semi-join：

对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配

Table pullout （子查询中的表上拉）：当子查询的查询列表处只有主键或者唯一索引列时
- 当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中
DuplicateWeedout execution strategy （重复值消除）：临时表去重
LooseScan execution strategy （松散扫描）
Semi-join Materialization execution strategy：先把外层查询的IN子句中的不相关子查询进行物化，然后再进行外层查询的表和物化表的连接
FirstMatch execution strategy （首次匹配）
- 就是说先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。

in转exists
1. 只要我们的IN子查询是放在WHERE或者ON子句中的，那么IN -> EXISTS的转换就是没问题的。
2. 转换是为了使用索引。

explain 执行计划输出中各列详解

table：EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名
id：查询语句中每出现一个SELECT关键字，设计MySQL的大叔就会为它分配一个唯一的id值
select_type
type：记录对表的访问方法
possible_keys和key
key_len
ref
- unique_subquery：是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery
- index_subquery：与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引
rows：如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数
filtered：驱动表扇出值的百分比
extra：juejin.cn/book/684473…

面试题

分析联合索引

www.cnblogs.com/zouzhibin/p…

CREATE TABLE person_info( 
id INT NOT NULL auto_increment, 
name VARCHAR(100) NOT NULL, 
birthday DATE NOT NULL, 
phone_number CHAR(11) NOT NULL, 
country varchar(100) NOT NULL, 
PRIMARY KEY (id), 
KEY idx_name_birthday_phone_number (name, birthday, phone_number) );

SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';

这样只能用到name列的索引，birthday和phone_number的索引就用不上了，因为name值相同的记录先按照birthday的值进行排序，birthday值相同的记录才按照phone_number值进行排序。

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

由于B+树中的数据页和记录是先按name列排序的，所以我们上边的查询过程其实是这样的：

找到name值为Asa的记录。
找到name值为Barlow的记录。
哦啦，由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来喽～
找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

上边这个查询可以分成两个部分：

通过条件name > 'Asa' AND name < 'Barlow'来对name进行范围，查找的结果可能有多条name值不同的记录，
对这些name值不同的记录继续通过birthday > '1980-01-01'条件继续过滤。

这样子对于联合索引idx_name_birthday_phone_number来说，只能用到name列的部分，而用不到birthday列的部分，因为只有name值相同的情况下才能用birthday列的值进行排序，而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。

SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;

如果我们查询的需求是先按照name列进行升序排列，再按照birthday列进行降序排列的话，比如说这样的查询语句。

这样如果使用索引排序的话过程就是这样的：

先从索引的最左边确定name列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
如果name列等于最小的值的记录不足10条，再继续往右找name值第二小的记录，重复上边那个过程，直到找到10条记录为止。

累不累？累！重点是这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，设计MySQL的大叔觉得这样还不如直接文件排序来的快，所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。

MySQL核心知识点