sql数据库的相关概念与底层介绍

本文中的数据库指的是磁盘数据库。如果有sql语言（CRUD，增删改查）的使用经验会更容易理解本文的知识点。

数据库与redis的区别

数据库：数据存储长期在磁盘中，小部分频繁需要的数据会被临时提取在内存中。

Redis：数据存储在内存中。

OLTP与OLAP

两种数据库事务处理方式。

OLTP：一般针对增、删、改事务。要求实时性高、稳定性强、确保数据及时更新成功；

OLAP：查询事务。一般用于做数据分析。

SQL

定义

SQL是一种编程语言，带有特殊目的，structured query language，结构化查询语言。

除了能在mysql数据库中使用，sql还有可能可以在其他关系型数据库中使用。

sql主要用于管理关系型数据库系统。

关系型数据库：通常使用表存储数据，例如mysql。‘

非关系型数据库：存储结构比较广泛，例如redis，使用的是键值对存储。

语句分类

D：data，L：language。

DQL：Q，query，数据查询语言；

DML：M，Manipulate，数据操作语言；

DDL：D，Define，数据定义语言，一般用于表创建。

DCL：C，control，数据控制语言，一般用于用户权限控制。

TCL，Transaction Control Language - 事务控制语言，commit：事务提交，rollback：事务回滚；

数据库术语

数据库：数据库是一些关联表的集合；数据库系统使用B+树来组织和存储数据。

数据表：表是数据的矩阵；

列：一列包含相同类型的数据；

行：或者称为记录是一组相关的数据；

主键：主键是唯一的；一个数据表只能包含一个主键；

外键：外键用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作用；而 innoDB 完整支持外键；

复合键：或称组合键；将多个列作为一个索引键；

索引：用于快速访问数据表的数据；索引是对表中的一列或者多列的值进行排序的一种结构；索引可以分为主键索引和非主键索引，B+树通常以索引列的值作为key。

MySQL体系结构

流程图

Mysql体系结构主要是从磁盘读取数据，或者往磁盘存储数据经历的流程。

最上层是mysql驱动，它允许应用程序与mysql数据库进行连接、发送查询、接收结果等操作。有不同的实现语言。

进入到mysql之后，第一层是server层，主要功能是计算，也就是对驱动发过来的sql语句进行词法句法分析。

第二层是引擎层，引擎层向server层提供一套统一接口。不同的存储引擎在读效率、写效率、事务支持（ACID特性）、锁定级别、索引结构、数据压缩和存储的有无与存储等方面存在差异。

第三层是文件系统，包括了数据文件和数据库日志。

MySQL 内部连接池

管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求

网络处理流程：主线程接收连接，接收连接交由连接池处理；

主要处理方式：IO多路复用 select + 阻塞的 io；主线程负责接收客户端连接，然后为每个客户端 fd 分配一个连接线程，负责处理该客户端的 sql 命令处理；

当多个线程同时操作同一条记录时，可能会引发数据一致性的问题，这被称为并发访问的冲突。需要用一些手段加以控制才能确保数据一致性。

管理服务和工具组件

系统管理和控制工具，例如备份恢复、MySQL 复制、集群（不常用）等；

SQL接口：将 SQL 语句解析生成相应对象；如：DML，DDL，存储过程，视 图，触发器等；

查询解析器：将 SQL 对象交由解析器验证和解析，并生成语法树；

查询优化器：SQL 语句执行前使用查询优化器进行优化；

缓冲组件: 用于提高性能的各种缓存机制，包括查询缓存、InnoDB缓冲池和MyISAM键缓存等。查询缓存被标记为已弃用，并在MySQL 8.0.20版本中完全移除。这是因为查询缓存在具有高并发和频繁更新的环境下存在一些性能问题。

面试常见问题：

数据的设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

范式一：确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值。例如：某表中有一个地址字段，如果经常需要访问地址字段中 的城市属性，则需要将该字段拆分为多个字段，省份、城市、 详细地址等；

范式二：一般用于组合索引的表。确保表中的每列都和主键相关，而不能只与主键的某一部分相关。

例如：下图的表中前两列共同作为了组合索引。

按照范式二可以拆分如下：

范式三：确保每列都和主键直接相关，而不是间接相关；减少数据冗余；范式三和范式二是有交集的关系。以范式二的表为例子，可以拆分如下：

反范式：范式可以避免数据冗余，减少数据库的空间，减小维护数据完 整性的麻烦；但是采用数据库范式化设计，可能导致数据库业 务涉及的表变多，并且造成更多的联表查询，将导致整个系统 的性能降低；因此基于性能考虑，可能需要进行反范式设计。

CRUD流程

以后的文章中对引擎层部分做详细介绍，此处暂时了解即可。

DDL操作是无法回滚（比如truncate，drop），但是DML语言可以（比如delete）。

下图对应的引擎是innodb。

数据库视图

创建语法：CREATE VIEW <视图名><视图名> AS <select语句>

也就是把select语句查询到的结果当作一个视图。注意，这个视图只是指向了select到的数据，但是它本身并没有占用内存空间。

作用：

1. 可复用，减少重复语句书写；类似程序中函数的作用；
2. 重构利器.假如因为某种需求，需要将 user 拆成表 usera 和表 userb；如果应用程序使用 sql 语句：select * from user 那就会提示该表不存在；若此时创建视图 create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;则只需要更改数据库结构，而不需要更改应用程序；
3. 逻辑更清晰，屏蔽查询细节，关注数据返回结果。
4. 权限控制，通过视图只能查询表，不能修改表。