DML 与 DDL

DML（Data Manipulation Language）指增删改操作，DDL指加字段/修改表结构的操作

表设计三范式

  • 第一范式：数据表中所有字段都是不可拆分的基本数据项
  • 第二范式：在满足第一范式的基础上，数据表中所有非主键字段，必须完全依赖全部主键字段，不能存在部分依赖主键的字段
  • 第三范式：在满足第二范式的基础上，数据表中不能存在可以被其他非主键字段派生出来的字段，或者说，不能依赖于非主键字段

    事务

    MVCC

    InnoDB 里每个事务有一个唯一的事务 ID，叫做 transaction id，它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的

而数据库中每行数据也是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并把 transaction id 赋值给这个数据版本的事务 ID，记为row trx_id，同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它

也就是说数据表中的一行记录，其实可以有多个版本（row），每个版本有自己的**row trx_id**

同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

事务的ACID特性

事务有 4 个主要特征，分别是原子性（Actomicity）、一致性（constency）、持久性（durablity）和隔离性（isolation）
原子性：表示事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断
一致性：表示数据的完整性不会因为事务的执行而受到破坏
隔离性：表示多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同
持久性：表示事务对数据的修改是永久有效的，不会因为系统故障而失效

事务的隔离级别

SQL 标准的事务隔离级别包括：读未提交(read uncommitted)、读提交(read committed)、可重复度(repeatable read)、串行化(serializable)

  • 读未提交是指，一个事务还没提交时，它所做的变更就能被别的事务看到
  • 读提交是指，一个事务提交后，它所做的变更才能被其他事务看到，这是 Oracle 的默认隔离级别
  • 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的，当然在可重复读隔离级别下，未提交的变更对其他事务也是不可见的，这是 MySQL 的默认隔离级别
  • 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

可重复读与读提交都是通过视图实现的，在可重复读级别下，这个视图是在启动的时候创建的，整个事务存在期间都使用这个视图；在读提交的级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。读未提交隔离级别直接返回记录上的最新值，没有视图概念；而串行化隔离级别下直接使用加锁来避免并行访问

幻读

幻读是指一个事务在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。更为具体一点：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读

比如有下面一张表：

  1. CREATE TABLE `t` ( 
  2.   `id` int(11) NOT NULL, 
  3.   `c` int(11) DEFAULT NULL, 
  4.   `d` int(11) DEFAULT NULL, 
  5.   PRIMARY KEY (`id`), KEY `c` (`c`)
  6. ) ENGINE=InnoDB;
  8. insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

下面来看下这个场景

session A session B session C
T1 begin;
select from t where d=5 for update; /Q1*/
result (5,5,5)
T2 update t set d=5 where id=0;
T3 select from t where d=5 for update; /Q2*/
result (0,0,5),(5,5,5)
T4 insert into t values(1,1,5);
T5 select from t where d=5 for update; /Q3*/
result (0,0,5),(1,1,5),(5,5,5)
T6 commit;

可以看到，session A 中开启了一个事务，并执行了三次select * from t where d=5 for update，这个语句使用的是当前读，并且加上写锁

其中 Q3 读到 id=1 这一行的现象，被称为“幻读”，这里我们对“幻读”做一个说明：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现
  2. 上面对 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读专指新插入的行


注意，这里幻读是我们假设**select * from t where id=5**这条语句只给 d=5 这一行加锁才出现的，且说的都是可重复读隔离级别下的。MySQL 在可重复隔离级别下也不会出现幻读，因为 MySQL 引入了间隙锁，解决了幻读的问题

START TRANSACTION 和 BEGIN 的区别

在 MySQL 中，可以使用 START TRANSACTION/BEGIN 开启事务，如

  1. START TRANSACTION -- 或使用 BEGIN 开启事务
  2. insert into testdb.user values(1,"hxy",12);
  3. insert into testdb.user values(2,"hxy2",13);
  4. COMMIT -- 提交事务，或使用 ROLLBACK 回滚事务

那么两者有什么区别呢，START TRANSACTION 跟 BEGIN 都可以手动开启事务，START TRANSACTION 是 SQL 的标准语法，而 BEGIN 可以理解为 START TRANSACTION 的别名，另外 START TRANSACTION 支持在后面添加修饰符控制事务的修饰符（如 START TRANSACTION WITH CONSISTENT SNAPSHOT），而 BEGIN 则没有该功能

参考：MySQL 官网文档、stackexchange 回答

索引/索引优化

索引分类

  • 主键索引（聚集索引）
  • 唯一索引
  • 普通索引
  • 联合索引
  • 全文索引
  • 哈希索引

回表与覆盖索引

假设我们有这样一个用户表，定义如下：

  1. create table T (
  2.   ID int primary key,
  3.   k int NOT NULL DEFAULT 0, 
  4.   s varchar(16) NOT NULL DEFAULT '',
  5.   index k(k)
  6. )engine=InnoDB;
  8. insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

如果执行select * from T where k between 3 and 5，这条语句的执行流程为：

  1. 在 k 索引树上找到 k = 3 的记录，取 ID = 300
  2. 再到 ID 索引树上查找 ID = 300 的记录
  3. 在 k 索引树上找到 k = 5 的记录，取 ID = 500
  4. 再到 ID 索引树上查找 ID = 500 的记录
  5. 在 k 索引树上取下一个值 k = 6，不满足条件，查询结束

在这个过程中，回到主键索引树搜索的过程，我们称之为回表，可以看到，这个过程中查了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了 2 次（步骤 2 和 4）

这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么有什么办法避免回表过程呢？答案就是索引覆盖

如果执行的语句是select ID from T where k between 3 and 5，这时由于需要的 ID 的值已经在索引树 k 上了，因此可以直接提供查询结果，不需要回表。也就是说在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称之为覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常见的优化手段，常见的需求如下：在一个市民表上，假设我们有根据身份证号查询姓名的场景，市民表的定义如下

  1. CREATE TABLE `tuser` (
  2.   `id` int(11) NOT NULL,
  3.   `id_card` varchar(32) DEFAULT NULL,
  4.   `name` varchar(32) DEFAULT NULL,
  5.   `age` int(11) DEFAULT NULL,
  6.   `ismale` tinyint(1) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   KEY `id_card` (`id_card`),
  9.   KEY `name_age` (`name`,`age`)
  10. ) ENGINE=InnoDB

如果使用select name from tuser where id_card='4****************1'，这时由于索引树 id_card 上没有 name 的信息，就需要到主键索引上再根据 id 查询一次。此时如果给表增加一个（身份证号、姓名）的联合索引就能避免回表过程

  1. alter table tuser add key `id_card_name` (`id_card`,`name`);

当然，索引字段的维护是有代价的。因此，在建立冗余索引时就需要权衡考虑了

最左前缀原则

比如我们有以下一个（name,age）的联合索引树
image.png
可以看到索引项是按照索引定义里面出现的字段顺序进行排序的，当我们需要查询所有名字是“张三”的人时，可以快速定位到 ID4 这行记录，然后向后遍历得到所需要的结果

那么，当我们要查的只是姓“张”的人时，SQL 为 select * from tuser where name like '张%'，这也能够用上（name,age）这个索引，根据索引查到符合条件的第一条哦记录是 ID3，然后向后遍历，直到不满足条件为止

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索，这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

索引下推

还是以为市民表为例

  1. CREATE TABLE `tuser` (
  2.   `id` int(11) NOT NULL,
  3.   `id_card` varchar(32) DEFAULT NULL,
  4.   `name` varchar(32) DEFAULT NULL,
  5.   `age` int(11) DEFAULT NULL,
  6.   `ismale` tinyint(1) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   KEY `id_card` (`id_card`),
  9.   KEY `name_age` (`name`,`age`)
  10. ) ENGINE=InnoDB

如果有这样一个需求：“检索出表中姓张，且年龄是10岁的所有男孩”，那么 SQL 语句是这么写的

  1. select * from tuser where name like '张%' and age=10 and ismale=1

我们已经知道，根据前缀索引原则，这个语句在搜索的时候，会根据索引树找到第一个满足条件的记录，然后根据主键（id）进行回表，查询出所有字段，然后判断其他条件是否满足

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表，到主键索引上找出数据行，再对比字段值

而 MySQL 5.6 之后，引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引失效的场景

  1. LIKE 使用通配符，且通配符在前面的无法使用索引，如select * from user where name like '%宇'

    日志

    redo log 与 WAL

    WAL的全称是 Write-Ahead Logging，它的关键点是先写日志，再写磁盘。具体的来说，当有一条记录要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了，同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里，而这个更新往往是在系统比较空闲的时候做

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件大小为 1GB，那么 redo log 总共就可以记录 4GB 的操作，从头开始写，写到末尾就回到开头循环写
image.png
write pos 是当前记录的位置，一边写一边后移，写到 3 号文件末尾就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦出记录前要把记录更新到数据文件中，write pos 跟 checkpoint 中间的表示未使用部分，可以用来记录新的操作，如果 checkpoint 追上 write pos，表示 redo log 满了，这时候不能在执行新的更新操作，得停下来擦掉一些记录，把 checkpoint 往前推一下

redo log 是 InnoDB 引擎才有的，有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称之为**crash-safe**

binlog 归档日志

redo log 是 InnoDB 引擎特有的日志，而 Serer 层也有自己的日志，称为binlog（归档日志）

binlog 与 redo log 有以下三点不同：

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

binlog 的使用场景
在实际应用中，binlog 的主要使用场景有两个，分别是主从复制和数据恢复

  1. 主从复制：在 master 端开启 binlog，然后将 binlog 发送到各个 slave 端，slave 端重放 binlog 从而达到主从一致
  2. 数据恢复：通过 mysqlbinlog 工具来恢复数据

    除了这两个场景外，也有一些工具利用 binlog 来对数据进行异构，比如 logstash 读取 binlog，将 mysql 的数据异构到 elasticsearch 中

binlog 的日志格式
binlog 有三种格式，分别是statement、row 和 mixed，在 MySQL 5.7.7 之前，默认的格式是 statement，MySQL 5.7.7 之后，默认是 row，日志格式通过 binlog-format 指定

  • statement：基于 SQL 语句的复制（statement-based replication, SBR），每条会修改数据的 sql 都会被记录到 binlog 中
    • 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，从而提高了性能
    • 缺点：在某些情况下会导致主从不一致，比如执行 sysdate()、sleep() 等
  • row：基于行的复制（row-based replication, RBR），不记录每条 sql 的上下文信息，仅需记录哪条数据被修改了，记两条，更新前跟更新后的都有
    • 优点：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题
    • 缺点：会产生大量的日志，比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度
  • mixed：基于 statement 和 row 两种模式的混合复制（mixed-based replaction, MBR），一般的复制使用 statement 模式保存 binlog，对于 statement 模式无法复制的操作使用 row 模式保存 binlog

    现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row，这么做的理由有很多，最直接的好处就是数据恢复

两阶段提交

两阶段提交是为了让两份日志（redo log 和 binlog）的逻辑一致，举个例子，当我们执行下面 update 语句

  1. update T set c=c+1 where id=2;

我们来看执行器和 InnoDB 引擎在执行这个 update 时的内部执行流程

  1. 执行器先找引擎 ID=2 这一行，ID 是主键，引擎直接用树搜索找到这一行，如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 出于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

这里给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的
image.png
步骤 3 和步骤 4 将 redo log 的写入拆成了两个步骤，prepare 和 commit，这就是“两阶段提交”

undo log

undo log 有两个作用：提供回滚和多个行版本控制（MVCC）

undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的记录

锁

全局锁

全局锁就是对整个数据库实例加锁，MySQL 提供了一个加全局读锁的方法（FTWRL），命令是

  1. Flush tables with read lock

该命令会让整个数据库处于只读状态，之后其他线程的以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（建表、修改表结构）和更新类事务的提交语句

全局锁的最典型场景是做全库逻辑备份

  • 如果在主库上备份，那么备份期间不能执行更新，业务就得停摆
  • 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

除了使用 FTWRL 的方式对数据库进行备份，还有其他方式么？

  1. 使用 MySQL 自带的 mysqldump 工具，当 mysqldump 使用 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，由于 MVCC 的支持，这个过程是可以正常更新的
  2. 执行 set global readonly=true 让全库处于只读状态

两种备份方式的区别

  1. 使用mysqldump -single-transaction 的方式要求存储引擎为 innodb，因为只有 innodb 才支持 MVCC
  2. 在一些系统中 readonly 的值会被用来做其他逻辑，比如判断一个库是主库还是备库，因此，set global readonly=true 的影响方面更大
  3. 执行 FTWRL 命令之后，如果执行该命令的客户端异常断开，那么 MySQL 会释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一致保持 readonly 状态，这样会导致整个库处于不可写状态，风险高

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，另外一种是元数据锁（meta data lock, MDL）

表锁

加锁：lock tables <table_name> read/write
释放锁：与 FTWRL 类似，可以执行 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放
行为：lock tables 除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

MDL

加锁：MDL 不需要显示使用，在访问一个表的时候会自动加上
作用：MDL 的作用是保证读写的正确性与表结构的安全性，比如防止一个线程在遍历表中数据的时候，另外一个线程对该表结果做变更操作

MDL 是 MySQL 5.5 引入的，当对一个表做 CURD 操作的时候，加 MDL 读锁；当要对表做结构变更操作（如删除某一行）的时候，加 MDL 写锁

  • 读锁之间不互斥，因此可以有多个线程对一张表做增删改查
  • 读写锁之间，写锁之间是互斥的，用来保证表结构的安全性。因此当有两个线程同时对表结构做变更操作时，其中一个线程必须等另外一个线程执行完才能开始执行

    行锁

    MySQL 行锁是有存储引擎自己实现的，并不是所有存储引擎都支持行锁，比如 MyISAM 就不支持行锁，InnoDB 就支持行锁

行锁的两阶段锁：在 InnoDB 事务中，行锁是在需要的时候加上的，但并不是不需要了就立即释放，而是要等到事务结束才释放，这个就是两阶段锁协议

间隙锁

因为行锁只能锁住行，但是新插入记录这个动作要更新的是记录之间的“间隙”，因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁（Gap Lock）

顾名思义，间隙锁，锁的就是两条记录之间的空隙。数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体，间隙锁与行锁不太一样的是，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

间隙锁是前开后闭的区间，间隙锁是在可重复读级别下才会生效的，如果把隔离级别设置为读提交的话就没有间隙锁了，但同时，需要解决可能出现数据和日志不一致的问题，需要把 binlog 格式设置为 row

next-key-lock

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭的区间，间隙锁和 next-key lock的引入，虽然解决了幻读的问题，但是可能会导致同样的语句锁住的范围更大，这其实影响了并发度

死锁与死锁检测

什么是死锁？

当并发系统中，两个线程出现资源循环依赖，涉及的线程都在等待别的线程释放资源时，就会导致几个线程都处于无限等待的状态，称为死锁

当出现死锁时的解救策略

  • 第一种策略是，直接进入等待，知道超时，这个超时时间可以通过参数innodb_lock_wait_timeout来设置（默认是 50s，这个等待时间往往无法接受，如果要使用该策略，建议按照自己业务需求修改改值）
  • 另一种策略是直接发起死锁检测，发现死锁时，MySQL 主动回滚死锁链中的某一条事务，使得其他事务可以正常执行，将innodb_deadlock_detect设置为 on 时（默认为 on），表示开启死锁检测

加锁规则

MySQL 在加锁的时候，包含了两个“原则”、两个“优化”和一个“bug”（这里讨论的是RR隔离级别下的）

  1. 原则 1：加锁的基本单位是 next-key lock（next-key lock 是前开后闭区间的）
  2. 原则 2：查找过程中访问到的对象才加锁
  3. 优化 1：索引上的等值查询，给唯一索引加锁时，next-key lock 退化为行锁
  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足条件的时候，next-key lock 退化为间隙锁
  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止（听说在8.0.18版本已经修复该bug）

临时表

临时表分类：
MySQL 中有两种临时表，分别是内部临时表和外部临时表

  • 内部临时表主要用于性能优化，由系统自动产生，我们无法看到
  • 外部临时表通过 SQL 语句创建，我们可以使用

根据存储方式的不同，临时表也可以分为内存临时表和磁盘临时表

  • 创建临时表的时候，可以通过指定引擎类型ENGINE=MEMORY，来告诉 MySQL 临时表存储在内存中，默认存储引擎是 InnoDB，会把临时表存储在磁盘上
    1. CREATE TEMPORARY TABLE demo.mytrans
    2. (
    3. itemnumber int,
    4. groupnumber int,
    5. branchnumber int
    6. ) ENGINE = MEMORY;
    临时表特点：
  1. 临时表的创建语法需要用到关键字 TEMPORARY
  2. 临时表在创建完之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性
  3. 临时表在当前连接结束后，会被自动删除

临时表使用场景：
使用临时表可以把一个复杂的问题拆分成很多个前后关联的步骤，把中间的运行结果存储起来，用于之后的查询。这样一来，就把面向集合的 SQL 查询变成了面向过程的编程模式，大大降低了难度

表优化

MySQL 在存储数据时，并不是一条条去处理的，而是按照固定大小的页进行处理，如果数据记录占用了不必要的存储空间，就会导致一次读入的有效数据很少。那么无论如何改写语句，都无法提升这步操作的效率，这就要求对表的设计进行优化了，表的优化可以从以下这几点入手

  • 修改数据类型以节省存储空间
  • 在利大于弊的情况下增加冗余字段，减少连接查询
  • 把大表查询频繁高的字段和查询频率低的字段拆分成不同表，使得查询操作每次读取的记录比较小，查询效率自然也提高了
  • 尽量使用非空约束，这样做的好处是，可以省去判断是否为空的开销，提高存储效率，而且非空字段也容易创建索引，使用非空约束，甚至可以节省存储空间（每个字段 1 比特）

    存储过程

    存储过程就是把一系列 SQL 语句预先存储在 MySQL 服务器上，需要执行的时候，客户端只要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行
    优点：
  1. 执行效率高，因为 SQL 预先编译好存储在 MySQL 服务器上的，每次调用的时候不用重新编译
  2. 客户端不需要把 SQL 发送给服务器，减少 MySQL 暴露在网络上的风险，提高数据查询的安全性

缺点：

  1. 不易测试
  2. 难以调试和扩展
  3. 没有移植性
  4. 版本控制困难

    MyISAM 与 InnoDB 引擎区别

  • MySQL 的行锁是由存储引擎自己实现的，并不是所有存储引擎都支持行锁，比如 MyISAM 只支持表锁，InnoDB 除了支持表锁外，还支持行锁

    MySQL 8 新特性

    窗口函数

    窗口函数的作用类似于在查询中对数据进行分组，不同的是分组的操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中
    语法：
    1. 函数 OVER([PARTITION BY 字段])
    或
    1. 函数 OVER 窗口名 ... WINDOW 窗口名 AS ([PARTITION BY 字段名])
    使用示例：
    假设有一下一张表
    1. mysql> SELECT * FROM testdb.test1;
    2. +----+------+--------+------------+
    3. | id | city | county | salesvalue |
    4. +----+------+--------+------------+
    5. |  1 | 北京 | 海淀   |      10.00 |
    6. |  2 | 北京 | 朝阳   |      20.00 |
    7. |  3 | 上海 | 黄埔   |      30.00 |
    8. |  4 | 上海 | 长宁   |      10.00 |
    9. +----+------+--------+------------+
    想要计算区销售额，市销售额，市比率（区销售额/市销售额），总销售额，总比率（区销售额/总销售额）；可以使用窗口函数进行查询
    1. SELECT
    2.   city AS 城市,
    3.   country AS 区,
    4.   salesvalue AS 区销售额,
    5.   SUM( salesvalue ) OVER ( PARTITION BY city ) AS 市销售额,-- j计算市销售额
    6.   salesvalue / SUM( salesvalue ) OVER ( PARTITION BY city ) AS 市比率,-- 计算市比率
    7.   SUM( salesvalue ) OVER () AS 总销售额,-- 计算总销售额
    8.   salesvalue / SUM( salesvalue ) OVER () AS 总比率 -- 计算总比率
    9. FROM
    10.   testdb.test1 
    11. ORDER BY
    12.   city,
    13.   country;
    对于在需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好，假设上面的查询，在不使用窗口函数的情况下，可能需要创建多张临时表保存临时的计算结果，感兴趣可参考极客时间- MySQL必知必会

除了可以进行分组统计，窗口函数还有一些自己独有的函数，可以对分组内的数据进行处理，比较常用的有RANK()、DENSE_RANK()、ROW_NUMBER()

比如有这样一张学生成绩表：

  2. mysql> SELECT * FROM demo.test2;
  3. +----+---------+--------+
  4. | id | student | points |
  5. +----+---------+--------+
  6. |  1 | 张三    |     89 |
  7. |  2 | 李四    |     77 |
  8. |  3 | 王五    |     88 |
  9. |  4 | 赵六    |     90 |
  10. |  5 | 孙七    |     90 |
  11. |  6 | 周八    |     88 |
  12. +----+---------+--------+

我们需要对表中的数据进行排序，就可以使用排序函数，如：

  2. mysql> SELECT student,points,
  3.     -> RANK() OVER w AS 排序1,
  4.     -> DENSE_RANK() OVER w AS 排序2,
  5.     -> ROW_NUMBER() OVER w AS 排序3
  6.     -> FROM demo.test2
  7.     -> WINDOW w AS (ORDER BY points DESC);
  8. +---------+--------+-------+-------+-------+
  9. | student | points | 排序1 | 排序2 | 排序3 |
  10. +---------+--------+-------+-------+-------+
  11. | 赵六    |     90 |     1 |     1 |     1 |
  12. | 孙七    |     90 |     1 |     1 |     2 |
  13. | 张三    |     89 |     3 |     2 |     3 |
  14. | 王五    |     88 |     4 |     3 |     4 |
  15. | 周八    |     88 |     4 |     3 |     5 |
  16. | 李四    |     77 |     6 |     4 |     6 |
  17. +---------+--------+-------+-------+-------+

RANK() 函数把并列计算在内，并且并列影响排位
DENSE_RANK() 函数页计算并列，但是并列不影响排位
ROW_NUMBER() 函数不计算并列，只是简单排序

公用表达式

公用表达式是一个命名的临时结果集，它存在于单个查询语句中，主要作用是可以代替子查询，并且可以被后面的查询多次引用

公用表达式可以分为普通公用表达式和递归公用表达式

普通公用表达式语法：

  1. WITH 
  2. CTE名称 AS (子查询)
  3. SELECT|DELETE|UPDATE 语句;

普通公用表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表达式所引用

使用示例：比如我们以前经常如下使用子查询

  1. mysql> SELECT * FROM demo.goodsmaster
  2. -> WHERE itemnumber IN
  3. -> (SELECT DISTINCT itemnumber     -- 子查询，查出所有销售过的商品的商品编号
  4. -> FROM demo.dailystatistics);
  5. +------------+---------+-----------+---------------+------+------------+
  6. | itemnumber | barcode | goodsname | specification | unit | salesprice |
  7. +------------+---------+-----------+---------------+------+------------+
  8. | 1 | 0001 | 书 | 16开 | 本 | 89.00 |
  9. | 2 | 0002 | 笔 | 黑色 | 支 | 3.00 |
  10. | 3 | 0003 | 胶水 | 无机 | 瓶 | 15.00 |
  11. +------------+---------+-----------+---------------+------+------------+

这个查询也可以使用普通公用表达式完成

  1. mysql> WITH
  2. -> cte AS (SELECT DISTINCT itemnumber FROM demo.dailystatistics)
  3. -> SELECT *
  4. -> FROM demo.goodsmaster a JOIN cte
  5. -> ON (a.itemnumber = cte.itemnumber);
  6. +------------+---------+-----------+---------------+------+------------+------------+
  7. | itemnumber | barcode | goodsname | specification | unit | salesprice | itemnumber |
  8. +------------+---------+-----------+---------------+------+------------+------------+
  9. | 1 | 0001 | 书 | 16开 | 本 | 89.00 | 1 |
  10. | 2 | 0002 | 笔 | 黑色 | 支 | 3.00 | 2 |
  11. | 3 | 0003 | 胶水 | 无机 | 瓶 | 15.00 | 3 |
  12. +------------+---------+-----------+---------------+------+------------+------------+
  13. 3 rows in set (0.00 sec)

公用表表达式可以起到子查询的作用。以后如果遇到需要使用子查询的场景，你可以在查询之前，先定义公用表表达式，然后在查询中用它来代替子查询。而且，跟子查询相比，公用表表达式有一个优点，就是定义过公用表表达式之后的查询，可以像一个表一样多次引用公用表表达式，而子查询则不能

递归公用表达式语法：

  1. WITH RECURSIVE
  2. CTE名称 AS （子查询）
  3. SELECT|DELETE|UPDATE 语句;

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己

完整的数据库设计文档包含哪些内容

一般来说包含**需求分析、建模（ER）、逻辑设计（比如建库建表）、物理设计（比如索引）、实施、运维（容灾和备份）等，根据实际需求，可进一步细化