数据库

SQL执行过程

  1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。

    这个功能在MySQL8.0里面已经被抛弃

  2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。

  3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。

  4. 执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

DDL（数据定义语言）

一般不直接使用DDL语句进行创建数据表，都在数据库可视化工具里面创建

数据库

创建：CREAT DATABASE

删除：DROP DATABASE

数据表

主键：唯一标识一条记录，不能重复，不能为空。

外键：确保了表于表之间引用的完整性，可以重复，可以为空

索引：提升数据检索速度

1
2
3
CREATE TABLE 表名 (
  字段名 数据类型 约束
)ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

引擎： InnoDB；字符集： utf8；排序规则：utf8_general_ci；行格式：Dynamic

1
2
3
4
ALTER TABLE 表名 ADD (字段);//添加字段
ALTER TABLE 表名 RENAME COLUMN 原字段名 to 修改后字段名	;//修改字段名
ALTER TABLE 原字段 MODIFY (修改后字段);//修改字段数据类型
ALTER TABLE 表名 DROP COLUMN 字段名;//删除字段

查询语句

1
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

执行顺序：FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

事务

基本性质

  1. A原子性：不可分割，对数据操作的基本单位

  2. C一致性：需要满足数据库的约束条件

  3. I隔离性：不同事务之间彼此独立

  4. D持久性：对数据的修改是持久的

并发异常情况

  1. 脏读：读到了别的事务未提交的数据
  2. 不可重复读：因为别的事务对数据进行修改导致同样的查询值不同
  3. 幻读：别的事务添加或删除数据导致查询到的数据数量不同

隔离级别

隔离级别 脏读 不可重复读 幻读
读未提交：事务未提交时就能被别的事务读到 允许 允许 允许
读已提交：事务提交后才能被别的事务看到，执行sql语句时创建视图 禁止 允许 允许
可重复读（MySQL)：事务执行过程中读到的数据和启动时读到的一样，事务启动时创建视图 禁止 禁止 允许
可串行化：会给正在读写的事务加锁，只有锁释放了别的事务才能读写 禁止 禁止 禁止

MVVC（多版本并发控制）

每个事务都有一个row trx_id，在相应隔离级别需要时会自动获取所有已提交事务的版本号，在操作数据时会先获取对应的版本号，如果这个版本号不在获取的版本号中就通过uodo log计算出上一个版本并再次对比，直到找出最新的一个在事务启动时已提交的版本

可重复读在事务启动时获取版本号，读已提交在执行sql时获取版本号

优点是读不加锁，读写不冲突，是一种乐观锁的实现方式

ORM（对象关系映射）

把底层的 RDBMS 封装成业务实体对象，提供给业务逻辑层使用，一旦定义好了对象模型，就可以让它们简单可复用，从而不必关注底层的数据库访问细节，我们只要将注意力集中到业务逻辑层面就可以了，即便数据库本身进行了更换，在业务逻辑代码上也不会有大的调整

范式

数据表设计的基本原则，范式设计越高阶，冗余度就越低，但设计出来的表就越多

  • 超键：能唯一标识元组的属性集叫做超键。
  • 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
  • 主键：用户可以从候选键中选择一个作为主键。
  • 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。
  • 主属性：包含在任一候选键中的属性称为主属性。
  • 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

第一范式（1NF）

数据库表中的任何属性都是原子性的，不可再分

第二范式（2NF）

数据表里的非主属性都要和这个数据表的候选键有完全依赖关系，也就是一张表是一个独立的对象，只表达一个意思

第三范式（3NF）

任何非主属性都不传递依赖于候选键

巴斯 - 科德范式（BCNF）

在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖关系

反范式

允许少量冗余，用空间换时间。当冗余信息有价值或者能大幅度提高查询效率的时候，就可以采取反范式的优化

索引

类似于目录，数据量多、重复度少的时候使用，能提高查询效率，但也要维护，存储在硬盘中

  1. 普通索引：存的是主键的值，找到主键的值再通过这个值从主键中检索一次，所以主键尽可能短可以减少普通索引的空间

  2. 唯一索引：更新时要判断唯一性，性能不如普通索引

  3. 主键索引：在唯一索引基础上增加非空约束，存的是整行数据，所以尽可能使用主键索引可以减少回表次数

    自增主键好处：可以避免B+树的分裂

  4. 全文索引

  5. 联合索引：把不同字段联合起来当成一个索引

    最左前缀原则：联合索引的最左n个字段或者字符可以单独拿出来当索引（所以联合索引的顺序很重要）

    索引下推：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

B树

一个 M 阶的 B 树（M>2）有以下的特性：

  1. 根节点的儿子数的范围是 [2,M]。
  2. 每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为 [ceil(M/2), M]，叶子节点只有关键字没有孩子
  3. k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个孩子（指针），根据关键字大小指向不同的孩子
  4. 所有叶子节点位于同一层。

B+树

  1. 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数 +1。
  2. 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
  3. 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中，非叶子节点既保存索引，也保存数据记录。
  4. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

对比：

  1. B+ 树查询效率更稳定，每次都会访问到叶子节点

  2. B+ 树的查询效率更高，B+树比B树矮

  3. 在查询范围上，B+ 树的效率也比 B 树高，因为所有关键字都出现在 B+ 树的叶子节点中，并通过有序链表进行了链接

Hash（Memory引擎）

键值key通过hash映射找到桶bucket，每个桶中存放一个链表，用拉链法解决hash冲突

hash索引不能进行范围查询，不支持联合索引的最左侧原则，不支持排序，适合等值查询

使用场景

  1. 字段的数值有唯一性的限制，可以创建唯一索引或者主键索引
  2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下（包括update和delete)
  3. 需要经常 GROUP BY 和 ORDER BY
  4. DISTINCT 字段
  5. 多表连接时，对用于连接的字段创建索引

失效情况

  1. 索引进行了表达式运算
  2. 索引使用了函数
  3. 在 WHERE 子句中，在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引
  4. 使用 LIKE 进行模糊查询的时候，第一个不能是%
  5. 与 NULL 或者 NOT NULL 进行判断
  6. 联合索引需要从左到右的使用索引中的字段

优化

  1. 在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列；
  2. 将 GROUP BY 和 ORDER BY 中的列加入到索引中；
  3. 将 SELECT 字段中剩余的列加入到索引片中。

锁

种类

按锁的粒度可以分为行锁、页锁、表锁、全局锁

按数据库管理方式分，可以分为共享锁（读锁）、排它锁（写锁）、意向锁

实现方式

乐观锁

认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁

实现方式是设定一个版本号，每次提交前与服务器上的版本号进行比对，如果不一致就重新获取再提交，每次改动让版本号加一

适合读操作多的场景

悲观锁

对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现

适合写操作多的场景

性能优化

  1. 观察是否是周期性波动，如果是就加缓存、更改缓存失效策略
  2. 开启慢查询，定位执行慢的语句
  3. 使用EXLPLAIN查看执行计划（执行时间）或者用show profile查看时间成本（等待时间）
  4. 如果是因为等待时间长，就调优服务器的参数，比如适当增加数据库缓冲池（使用频次高的数据池）
  5. 如果是因为执行时间长，尝试优化索引、数据表设计、连接语句
  6. 如果确实到了性能瓶颈，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等

引擎

InnoDB

支持事务和行级锁，是 MySQL 默认的存储引擎

MyISAM

只支持表级锁，不支持事务，更适合读取数据库的情况

选择

如果是小型的应用，需要大量的 SELECT 查询，可以考虑 MyISAM；如果是事务处理应用，需要选择 InnoDB

日志

redo log（重做日志）

InnoDB 引擎的日志，物理日志，记录的是“在某个数据页上做了什么修改”

具有crash safe的能力，即使数据库发生异常重启，之前提交的记录都不会丢失

数据先写到redo log再写到磁盘，基本结构：一个环，用两个指针表示当前写的位置和要擦除的位置，擦除前把数据存入磁盘中，两指针之间表示空余的容量

binlog（归档日志）

Server层的日志，所有引擎都能用，存储sql语句，用于从某个点开始归档

提交

两阶段提交，保证两份日志逻辑一致