题干参考自：作者：御光飘扬
链接：www.nowcoder.com/discuss/573… 来源：牛客网

1 函数依赖和数据库范式

函数依赖

姓名"函数依赖"于学号：学号 → 姓名
完全函数依赖：X F→ Y (X中去掉一个属性就不行了)
部分函数依赖：X P→ Y (不是完全函数依赖，X里有冗余)
传递函数依赖：X T→ Z (假如 Z 函数依赖于 Y，且 Y 函数依赖于 X)

下图中：分数完全函数依赖于（学号，课名），姓名、系名部分函数依赖于学号，系主任传递函数依赖于学号。数据库第一二三范式到底在说什么？ - 刘慰的文章 - 知乎

键（码）

某表中，若除 K 之外的所有属性都完全函数依赖于 K
候选键（简称为键）；（任选一个做）主键；
主属性：在任意候选键里出现过；
非主属性：不是主属性

数据库范式

1NF: 每个属性都不可再分（列不可再分）
2NF: 通过“模式分解”，消除了非主属性对于码的部分函数依赖
3NF：在2NF的基础之上，消除了非主属性对于码的传递函数依赖
BCNF：在 3NF 的基础上消除（另外一个候选码的）主属性对于码的部分与传递函数依赖

ER图

基础（实体矩形；属性圆形；关系菱形）
- er图到底怎么画……？ - 亿图图示的回答 - 知乎
高级（弱实体；桥接实体；复合属性；多值属性；派生属性；可选属性；联系属性）
- 数据库ER图基础概念整理

2 索引

索引的特点

一张表可以建立任意多个索引，每个索引可以是任意多个字段的组合。索引可能会提高查询速度（如果查询时使用了索引），但一定会减慢写入速度。因为需要将列值的数据复制一遍，还会增加存储开销。在较短的表上，使用索引查找可能还不如顺序查找快。在较长的表上，可能索引放不进内存也会出现问题（需要分库分表）

索引的优点：1. 天生排序，2. 加快查找速度。
索引的缺点：1. 占用空间，2. 降低更新表的速度。

索引的分类(按形式)

通俗易懂索引、单列索引、复合索引、主键、唯一索引、聚簇索引、非聚簇索引、唯一聚簇索引的区别与联系 - Pickle Pee的文章 - 知乎 MySQL索引分类，90%的开发都不知道 - TencentDB腾讯云数据库的文章 - 知乎

单列索引：只包含一个字段的索引叫做单列索引。
复合索引：包含两个或以上字段的索引叫做复合索引（或组合索引）。建立复合索引时，字段的顺序极其重要。
唯一索引：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。非唯一索引在命中一个查询值后，还需查看下一个值是否符合条件。
主键索引：唯一 + 非空
聚集索引：唯一 + 非空（默认是主键。每个表只有一个唯一的聚集索引）

索引的分类(按存储)

什么是索引回表，如何避免？ - 野鸽知了的回答 - 知乎

聚集索引：聚簇索引的顺序就是数据在硬盘上的物理顺序。因此一张表只允许存在一个聚簇索引。默认是主键，如果表中没有定义主键InnoDB会选择一个唯一的非空索引代替。若不存在则隐式创建一个主键。
非聚集索引：也叫二级索引，辅助索引。如果索引里不包含全部要查找的字段，则需要根据索引叶子节点存的主键值，再到聚集索引里查找需要的字段，这个过程也叫做回表。

覆盖索引，回表查询，与最左前缀原则

回表：如果索引里不包含全部要查找的字段，则需要根据索引叶子节点存的主键值，再到聚集索引里查找需要的字段，这个过程也叫做回表。
覆盖索引：辅助索引信息里完全包含查询需要的信息，不经过回表查询就从辅助索引里直接返回信息，可以极大提升查询速度。本质不是一种索引类型，而是一种优化的查询方法。一般来说，使用复合索引的目的就是为了覆盖索引，减少回表。
最左前缀原则：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此复合索引就可以被用到。当遇到范围查询(>、<、between、like)会停止匹配。
index类型的索引树扫描：select某联合索引的一部分(select c; select a,c)但又不遵循最左匹配原则时(select a,b; select a,b,c)，可能会采用index类型的扫描，它可以避免回表，但效率远不如正常索引查询高。与all不同的是，index是对索引树进行扫描，而all是对磁盘的数据进行全表扫描

索引的数据结构

面试官：为什么MySQL的索引要使用B+树，而不是其它树？比如B树？
为什么用B+树
- 相对于b树/二叉树来说,B树每个节点能存储的节点数更多，层级更低。
- 相对于b树来说,每次查询是一定要到叶子节点，查询就更稳定
- 相对于b树/二叉树/Hash来说,叶子节点有双向链表，便于范围查询。
- 哈希索引在等值查询上有绝对优势，但是无法范围查询，也无法用于排序，不支持最左前缀匹配原则。
B树和B+树的优缺点：不要一下子把B树喷的一文不值
- 为什么Mongodb索引用B树，而Mysql用B+树? - 孤独烟的文章 - 知乎
- 在做单一数据查询的时候，使用B树平均性能更好。但是，由于B树中各节点之间没有指针相邻，因此B树不适合做数据遍历操作。Mongodb中范围查询少，单一数据查询多，因此使用B树。
- B+树的数据只出现在叶子节点上，因此在查询单条数据的时候，查询速度非常稳定。在做单一数据的查询上，其平均性能不如B树。但是，B+树的叶子节点上有指针进行相连，适合做范围查询。

3 事务

程序员，知道Mysql中ACID的原理吗？ - 孤独烟的文章 - 知乎

原子性Atomicity

一个事务(commit)是一个不可分割的工作单位，其中的操作要么都做，要么都不做。即要么转账成功，要么转账失败，是不存在中间的状态.

undo log：回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句。
回滚的时机：事务调用了rollback，或因为意外原因执行失败。

一致性Consistency

一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。比如主键不能为空、参照完整性，余额必须是正数。

从数据库层面，必须要实现AID三大特性，才有可能实现一致性。C是目的，AID是手段。
在开发者层面，注意在事务里不能写出违反约束的bug。(如转账故意不给B账户加钱)

隔离性Isolation

多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

MVCC 和 Next-key Lock 是 Innodb 实现RR级别一致性的工具。

持久性Durability

事务一旦提交commit，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。（对内存进行操作commit了，但是突然宕机，内存中修改的数据全部丢失了但是没有改变数据库）

当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。
- redo log 只记录事务对数据页做了哪些修改，文件更小并且是顺序IO，而且也可以内存中攒一些再写磁盘。总之比实时更新刷表强。
当数据库宕机重启的时候，会将redo log中已经提交事务的更改写到数据文件（如果已经写了就是是多做了一次无用操作，可以用checkpoint优化）

细谈隔离性：事务并发导致的几类问题

软件架构设计：大型网站技术架构与业务架构融合之道 - - 知乎
 MySQL事务隔离级别和实现原理（看这一篇文章就够了！） - 老刘的文章 - 知乎

脏读：读到了其他事务未提交的数据（可能会回滚，可能不会存入数据库）
不可重复读：在同一事务内不同的时刻读到的同一批数据不一样（如其他事务改了这批数据并提交了）
幻读：在同一事务内两次查询返回不同的结果集合。假设事务A对某些行的内容作了更改，此时事务B插入了与事务A更改前的记录相同的记录行并提交。这时在事务A中查询，会发现好像刚刚的更改未起作用。
丢失更新：两个事务同时修改同一条记录，其中一个被另一个的结果覆盖。这需要开发者手动加锁解决。

事务隔离级别

读未提交: 读未提交隔离级别是不加锁的，所以它的性能是最好的。但解决不了任何事务并发问题。
读已提交: 只读已经提交的数据。解决了脏读问题。但是仍然可能其他事务中途commit，导致不可重复读。
可重复读：事务不会读到其他事务对已有数据的修改，即使其他事务已提交。一般MVCC数据库在RR的隔离级别下对应的隔离级别是snapshot isolation，这种情形下也是不会有幻读的。
串行化：读的时候加共享锁，写的时候加排他锁。解决幻读问题，解决丢失更新问题。

MVCC

Innodb里面每行数据可以有多个版本，字段trx_id记录生成这个版本的事务ID。
这些不同版本的数据不是物理存在的，而是每次通过undo log动态算出来的。
MVCC的核心在于每个事务自己维护的一个事务ID数组。

MVCC实现快照读的可重复读

如果 trx_id 小于低水位，表示这个版本在事务启动前已经提交，可见；
如果 trx_id 大于高水位，表示这个版本在事务启动后生成，不可见；
如果 trx_id 大于低水位，小于高水位，分为两种情况：
- 若 trx_id 在数组中，表示这个版本在事务启动时还未提交，不可见；
- 若 trx_id 不在数组中，表示这个版本在事务启动时已经提交，可见。
MySQL是如何实现可重复读的? - 超超不会飞的文章 - 知乎

可重复读vs读已提交

可重复读是在事务的第一次快照读的时候生成一个快照
读提交则是每次快照读的时候都重新生成一次快照。

MySQL在RR隔离级别下的幻读问题

MySQL并没有实现严格的snapshot isolation，它允许并发的事务更新已提交的数据。这样，MySQL会产生一些其它MVCC数据库没有的异常。即，允许在快照读之后执行当前读，并且更新 snapshot 镜像的版本，这时会可能读到和之前快照读不一样的数据。并且在这之后再执行快照读，也是读的更新之后的版本了。严格意义上，这不是幻读问题，而是多版本问题。

mysql> SELECT * FROM char_encode WHERE glyph = 'a';
+-------+-----------+
| glyph | codepoint |
+-------+-----------+
| a     |        97 |
+-------+-----------+
1 row in set (0.03 sec)

// UPDATE 隐式调用 SELET FOR UPDATE. 当前读。 
mysql> UPDATE char_encode SET codepoint = codepoint + 1 WHERE glyph
    -> = 'a';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

// 在当前读之后再执行快照读，与第一次快照读不一致
mysql> SELECT * FROM char_encode WHERE glyph = 'a';
+-------+-----------+
| glyph | codepoint |
+-------+-----------+
| a     |       101 |
+-------+-----------+

4 锁

乐观锁和悲观锁

乐观锁和悲观锁只是概念，并非具体的锁。

悲观锁：读之前就上锁。但是可能造成死锁，且在高并发情况下效率低。如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁：读之前不上锁，等到要更新的时候判断原数据是否被改动了（版本变新），如果是就重做。可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，不然经常回滚也效率低。

按锁的粒度分类

表锁：DDL语句使用这个粒度。另外,意向锁也加在表上。
行锁：锁一个记录，通常使用这个粒度。
间隙锁(Gap Locks)：锁一个范围。防止其他事务在这个范围内插入或修改记录，从而不会出现幻读现象。
临键锁（Next-key Lock）：记录锁+Gap锁。左开右闭的一个区间。
插入意向锁（II Gap Locks）：和Gap锁或Next-key锁冲突。正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

按锁的性质分类

共享锁：在所有的Select后面都加上 for update 实现了读锁 (普通select使用快照，不加锁。读非快照的最新版本才加锁)
排他锁：在所有的Select后面都加上 lock in share mode 实现了写锁。
意向锁（Intention Locks）：对表内行加的锁，在表上加相同种类意向锁。便于其他想给整个表加锁的事务提前得知行锁的存在，预判兼容性。
自增锁：AUTO_INC锁互不兼容，也就是说同一张表同时只允许有一个自增锁。自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

上锁的原理

把MySQL中的各种锁及其原理都画出来 - Java架构师想静静的文章 - 知乎 # TODO: 这个文章的分析很详细。但不建议现在这个时间紧的时候看。肯定不会问的。
MySQL事务隔离级别和实现原理（看这一篇文章就够了！） - 老刘的文章 - 知乎在 Mysql 中，行级锁并不是直接锁记录，而是锁索引。
如果一条语句操作了主键索引：Mysql 就会锁定这条主键索引；
如果一条语句操作了非主键索引：MySQL会先锁定该非主键索引，再锁定相关的主键索引。
如果没有索引：InnoDB 会通过隐藏的聚簇索引来对表中所有数据加锁，实际效果跟表锁一样。因为没有了索引，找到某一条记录就得扫描全表，要扫描全表，就得锁定表。

死锁形成的必要条件

互斥条件：在一个时间只能有一个进程使用资源。
请求和保持条件：进程保持至少一个资源正在等待获取其他进程持有的额外资源。
不剥夺条件：一个资源只能在进程已经完成了它的任务之后，被自愿释放。
循环等待条件：存在n个进程，进行循环等待所占资源。

避免死锁

# TODO: 看教材，记得有相关内容，按顺序加，一次加所有锁等

5 具体语言用法

自行先看： www.runoob.com/sql/sql-cre…

like

选取 name 以字母 "G" 开始的所有客户."%" 符号用于在模式的前后定义通配符
SELECT * FROM Websites
WHERE name LIKE 'G%';

下面的 SQL 语句选取 name 以字母 "k" 结尾的所有客户：
SELECT * FROM Websites
WHERE name LIKE '%k';

下面的 SQL 语句选取 name 包含模式 "oo" 的所有客户：
SELECT * FROM Websites
WHERE name LIKE '%oo%';

下划线通配符，匹配任意一个字符
SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

使用REGEXP书写正则表达式
下面的 SQL 语句选取 name **不以** A 到 H 字母开头的网站：
SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

COUNT

-- 查询所有记录的条数
select count(*) from access_log;

-- 查询websites 表中 alexa列中不为空的记录的条数
select count(alexa) from websites;

-- 查询websites表中 country列中不重复的记录条数
select count(distinct country) from websites;

drop、truncate、delete

Delete：
- delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
- 谨慎使用drop与truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。
- 对于由FOREIGN KEY约束引用的表，应使用DELETE。因为TRUNCATE不记录在日志中，不激活触发器。
Drop:
- 删除表数据和表结构。将表所占用的空间全释放掉。
Truncate：
- 仅仅需要删除表内的数据，但并不删除表本身。当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小。
- TRUNCATE只能对TABLE，不能对VIEW
- truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。不会激活与表有关的删除触发器。执行速度快。
参考 MySQL—drop、truncate和delete的区别 - 月下阿蒙的文章 - 知乎

ORDER BY

SELECT * FROM Websites
ORDER BY country ASC,alexa DESC;

order by A,B        这个时候都是默认按升序排列
order by A desc,B   这个时候 A 降序，B 升序排列
order by A ,B desc  这个时候 A 升序，B 降序排列

GROUP BY

SELECT Websites.name, COUNT(access_log.aid) AS nums 
FROM access_log LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

根据 GROUP BY 的字段，计算聚合函数
没有GROUP BY就会整体作为一个大组计算聚合函数

HAVING

SELECT Websites.name, SUM(access_log.count) AS nums 
FROM Websites INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

在 SQL 中增加 HAVING 子句原因是，WHERE 无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据

各种连接JOIN

自连接：一张表，使用多个别名，表面上进行多表连接查询

SELECT b.lastStation,b.nextStation,a.lastStation,a.nextStation 
FROM bus_sche a, bus_sche b 
WHERE b.nextStation = a.lastStation;

内连接查询操作只列出与连接条件匹配的数据行，使用 INNER JOIN 或者直接使用 JOIN 进行连接.内连接可以没有连接条件，会保留所有结果（笛卡尔积），与后面分享的交叉连接差不多
- 等值连接 SELECT * from Table_A A JOIN Table_B B ON A.id = B.id;
- 不等连接 SELECT * from Table_A A JOIN Table_B B ON A.id > B.id;
- 自然连接：SELECT * from A NATURAL JOINB WHERE A.id = B.id;
  - 使用'='运算符，删除连接表中的重复列
  - 只能用where不能用on。
  - 在连接查询中通常WHERE没有ON效率高。ON匹配到第一条就结束，而WHERE会一直匹配。
交叉连接
- 不带 WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积
- SELECT * from Table_A CROSS JOIN Table_B;
外连接
- 外连接不只列出与连接条件相匹配的行
- 而且还加上左表(左外连接)或右表(右外连接)或两个表(全外连接)中所有符合搜索条件的数据行。
- 不能匹配其他表的字段都置空Null

各种关键字的执行顺序

SQL的书写顺序和执行顺序 - 张潇的文章 - 知乎

(8) SELECT (9)DISTINCT<Select_list>
(1) FROM <left_table> (3) <join_type>JOIN<right_table>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_list>
(11) LIMIT<limit_number>

先做JOIN连接，用ON筛选，如若外部连接再加回
WHERE过滤记录
GROUP BY分组, HAVING过滤组
真正SELECT出指定列，DISTINCT去重
最后可选ORDER BY，LIMIT前几个输出

mysql的数据类型

三大类：数值；字符串；日期时间
详见 Mysql数据库中有哪些数据类型? - 51Testing软件测试网的文章 - 知乎

6 优化手段和散装经验

查询缓慢和解决方式

（explain、慢查询日志、show profile等）

explain: 面试前必须知道的MySQL命令【explain】 - Java3y的文章 - 知乎
慢查询日志: 慢查询日志可以有效跟踪「执行时间过长」或者「没有使用索引」的查询语句，为优化查询语句提供帮助。MySQL 核心技术与最佳实践 - - 知乎
show profile: 找到耗时最长，性能最低，也就是最慢的环节，才能分析出慢SQL为什么这么慢. SQL 性能分析利器 show profile - 鞠骞的文章 - 知乎

explain的参数

这次是真拯救了我，MySQL索引优化，explain讲得非常清楚了 - Java程序猿阿谷的文章 - 知乎

id：数字越大越先执行，一样大则从上往下执行
select_type：
- simple：不需要union也不包含子查询的简单select语句。
- primary：如果SQL语句包含任何子查询，那么子查询的最外层会被标记为 primary
- subQquery: 在 select 或者 where 里包含了子查询，那么子查询就会被标记为 subQquery
- derived: 在 from 中包含的子查询，会被标记为衍生查询，会把查询结果放到一个临时表中
- union / union result: 如果有两个 select 查询语句，他们之间用 union 连起来查询，那么第二个 select 会被标记为 union，union 的结果被标记为 union result.
table：表名，如果是用了别名，则显示别名
type: type 是代表 MySQL 使用了哪种索引类型，不同的索引类型的查询效率也是不一样的. 依次从好到差: system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL(无索引)
possible_keys：此次查询中涉及字段上若存在索引，则会被列出来，表示可能会用到的索引，但并不是实际上一定会用到的索引。
key：查询真正使用到的索引
key_len：用于处理查询的索引最大可能长度，在不损失精确性的情况下，长度越短查询效率越高
ref：显示关联的字段。如果使用常数等值查询，则显示 const，如果是连接查询，则会显示关联的字段。
rows：执行计划中估算的扫描行数，不是精确值。
filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的百分比。
extra：该字段信息较多。
- using fileSort（文件排序）不好
- using temporary（使用临时表保存中间结果）不好
- USING index（使用了覆盖索引）好

加索引的合理使用

www.cnblogs.com/orchidbaby/…

如果表比较小的话，可能都不需要加索引。记录修改的时候需要维护索引，所以会有开销，要衡量建索引之后的得与失（空间+维护换时间）。
哪些字段可以建索引？一般都where、order by 或者 group by 后面的字段。
对于select from students where name='张三’and age=18;情况下：
1. name 和 age 各自单独建立索引: 一般来说mysql会选择其中一个索引，name的可能性比较大，因为mysql会统计每个索引上的重复度，选用低重复度的字段。
2. name和age的联合索引: 这种索引的切合度最好。但是相对单索引来说，维护的成本大,索引数据占用的存储空间也要更大。可是！有必要使用联合索引吗？一般没必要：学校有10000个学生，叫谢春花的会超过5个吗？5个找一个,比建立联合索引花销小的多。
什么情况下使用联合索引比较好呢？举一个例子，大学修课需要创建一个关系对应表，有2个字段, student_id 和 teacher_id，想要查询某个老师和某个学生是否存在师生关系。
- 一个学生会选50老师，一个老师会带200个学生.
- 如果只为student_id建立索引，经过索引会选出50条记录，然后在内存中where一下，去除其余的老师。相反如果只为teacher_id建立索引，经过索引会选出200条记录，然后在内存中where一下，去除其余的学生。
- 两种情况都不是最优的，因为使用索引后范围依然很大，这个时候使用联合索引最合适，通过索引直接找到对应记录，差不多提高了一倍效率。

大表优化

MySQL 对于千万级的大表要怎么优化？- zhuqz的回答 - 知乎
第一优化你的sql和索引；第二加缓存，memcached,redis；
第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高
第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的
第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

Redis

# TODO 待完善
为什么要用Redis？Redis为什么这么快？ - 胖狗子的文章 - 知乎

发布订阅
数据淘汰机制（好几种）
字典及渐进式rehash
RDB与AOF
为何高效（内存数据库、非阻塞IO、IO多路复用、单线程、hash表、跳表等）
Redis的几种数据结构

数据库常用知识点总结