关系型数据库原理
一、概念
1. 关系型数据库
- 数据库(DB)
即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据
- 数据库管理系统（DBMS）
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
- 关系型数据库管理系统 (RDBMS)
建立在关系模型基础上，由多张相互连接的二维表组成的数据库。也就是将数据以表的形式存储并且这些表存在相互联系。表是由行和列组成。
- 结构化查询语言（SQL）
专门用来与数据库通信的语言。
2. 关系型数据库的设计范式
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
通俗的理解就是设计数据库表、表和表之间的关系的时候，需要遵循的规范，从而避免数据的冗余存储，避免造成大量存储空间浪费，所以定义了数据库的范式。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
(1) 第一范式（1NF）
所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。
说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。
(2) 第二范式(2NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例（行）或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。
(3) 第三范式（3NF）
第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不能包含已在其它关系已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
第三范式要求表与表之间的关系
3. 表连接
两张表通过某种关系建立连接，用来查询数据。
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加⼊结果集并返回给用户。
把多张表记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询
4. 事务
事务（Transaction）是一组SQL组成的执行单元（Unit），是数据库并发控制和恢复回滚的基本单位。一个事务中可能包含多个SQL，要么都失败，要么都成功。
把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务（英文名是：transaction）。
一个存储系统是否支持事务，测试标准：
ACID：
A：原子性；
C：一致性；
I：隔离性；
D：持久性；
5. 约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据；
保证数据库中数据的正确、有效性和完整性;
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束;
如将一个人的年龄写入表中，误将他的年龄写成260年，数据库表能够自动检测这种违反法则的机制叫做约束。
非空约束：限制该字段的数据不能为null
主键约束：一个或多个字段的组合，填入主键中的数据，必须不同于已存在的数据；不能为空；一个表只能存在一个；
外键约束：一个表中某字段中能插入的数据，取决于另外一张表的主键中的数据；
惟一约束：一个或多个字段的组合，填入惟一键中的数据，必须不同于已存在的数据；可以为空；一个表可以存在多个；
检查性约束：取决于表达式的要求；
6. 索引
没有索引，查询一条数据，只能遍历表中的所有行。
可以把一个字段抽取出来，进行重新排序，排序以后再查询。
将表中的一个或多个字段中的数据复制一份另存，并且此些需要按特定次序排序存储；
索引是帮助MySQL高效获取数据的排好序的数据结构;
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
| 索引结构 | 描述 |
|---|---|
| B+Tree | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash | 底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-Tree(空间索引) | 空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-Text(全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES |
数据结构与算法可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
7. 关系运算
当查询表中的数据时，涉及到表和表之间的连接运算。
选择：挑选出符合条件的行；
投影：挑选出符合需要的列；
连接：将多张表关联起来；
8. 锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、/0)的争用以外，数据也是种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
MySQL中的锁，按照锁的粒度分为以下三类：
- 全局锁:锁定数据库中的所有表。
- 表级锁:每次操作锁住整张表
- 行级锁:每次操作锁住对应的行数据。
死锁
两个或多个事务在同一组资源相互占用，并请求锁定对方占用的资源的状态
A线程需要一号资源和二号资源，锁了一号等待二号资源，B线程锁了二号等待一号资源，互相等待对方释放资源，造成死锁。
所谓死锁，是指多个进程在运行过程中因争夺资源而造成的一种僵局，当进程处于这种僵持状态时，若无外力作用，它们都将无法再向前推进。
因此我们举个例子来描述，如果此时有一个线程A，按照先锁a再获得锁b的的顺序获得锁，而在此同时又有另外一个线程B，按照先锁b再锁a的顺序获得锁
9. 分库分表
垂直分库:以表为依据，根据业务将不同表拆分到不同库中。
垂直分表:以字段为依据，根据字段属性将不同字段拆分到不同表中。
水平分库:以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
水平分表:以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
二、Mysql架构
1.Connectors
指的是不同语言中与SQL的交互，如php、java等。
2 Management Serveices & Utilities：
系统管理和控制工具
3 Connection Pool: 连接池
管理缓冲用户连接，线程处理等需要缓存的需求。
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。
而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。
4 SQL Interface: SQL接口。
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
5 Parser: 解析器。
SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a. 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
6 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果
他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
7 Cache和Buffer： 查询缓存。
热点数据载入内存中，加速查询过程。
Cache：主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
buffer与cache的区别？
缓存那里实际上有buffer和cache两个，那它们之间是否有什么不同呢？简单的说就是，buffer是写缓存，cache是读缓存。
8 、存储引擎接口
存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。
从图还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
注意：存储引擎是基于表的，而不是数据库。
三、SQL SELECT语句执行过程
MySQL 整个查询执行过程，总的来说分为 6 个步骤 :
SQL执行步骤：请求、缓存、SQL解析、优化SQL查询、调用引擎执行，返回结果
1、连接：客户端向 MySQL 服务器发送一条查询请求，与connectors交互：连接池认证相关处理。
2、缓存：服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
3、解析：服务器进行SQL解析(词法语法)、预处理。
4、优化：再由优化器生成对应的执行计划。
5、执行：MySQL 根据执行计划，调用存储引擎的 API来执行查询。
6、结果：将结果返回给客户端，同时缓存查询结果。
四、参考
