📃 数据库和 SQL
!本篇文章过于久远，其中观点和内容可能已经不准确，请见谅!~
想分享的是数据库相关的基本知识和 SQL 的相关概念，完备的回顾下相关能力，温故知新~~~
刚开始写代码自学的时候，目的就是做网站，所以接触了很多的后台 PHP 和数据库相关的能力，工作中很长一段也承担了中间层和后台管理系统的搭建，所以可以说挺熟悉的。但是没有完备系统的整理过数据库的相关能力，平时 SQL 查询没问题，但是一旦跟别人讨论就变得没法十分确定了，这里整理一下，温故知新~
Database、DB 是数据库，我们平时叫的数据库，更多表示数据库管理系统（Database Management System，DBMS），例如 Mysql、Sqlite、Oracle、dBASE、PostgreSQL 等等非常多。
数据库的概念其实只要能够系统的存储和读取数据就能被称为数据库，例如强迫症的硬盘，分门别类的存储各种文件，想找某个资料，三两下点击就能找出来，这也算是一个数据库。这个事情交给程序做，能够更快更好的新增、更新、查询、删除等操作，涉及到存储设计、健壮可靠性、算法、各种能力、代数集合数学等非常多的考虑，这是一个成熟数据库的本质。
一般意义上分类主要说的是
关系型数据库(Relational database) 和 非关系型数据库(Not Only SQL) 等，这里也不延伸其他类型的存储分类。关系型数据库的
关系 主要指的是数据之间的关系模型，用结构化的信息存储方式来安排数据的存储，也就是通过 字段 和 记录 两个概念确定每一个数据，比如班级成绩的数据，就可以用 小明(记录)的语文成绩(字段)，来找到具体的数值。关系型数据库的理解和使用非常简单，存储关系也更容易实现，所以目前大多数据库都是使用的这个模型。
比如经典的
Mysql 伴随着 PHP 的发展在 Web2.0 高速发展的阶段被广泛运用，直至现在依然占据很大的市场。关系型数据库 和其对应的查询语言 SQL 也是本篇文章重点讨论的内容。
非关系型数据库的概念比较宽泛，而且实现和侧重都有不同，目前来说
非关系 并不表示没有关系，而是 Not Only SQL ，不仅仅表示关系的意思，大部分特点在于非关系型、分布式、开源、水平可扩展、高读增性能。现代互联网的发展，导致数据量大幅上升，传统的关系型数据库由于需要保证联合查询和一致性，规范已经不能满足使用需求了。包括分布式、主从、冷热多地等手段满足使用，但是维护性和扩展性也在降低。数据的存储需求愿意牺牲一部分体验来获得另一部分的能力提升，比如
高性能并发读写、海量数据访问、可拓展性分布式等都是新的需求。这些新需求下的数据库形式就是以 Redis 和 MongoDB 为代表的这些产品，大多开源性能好，能够满足很多新的存储需求。
在读取和新增数据上性能更好。但是在牺牲比如一致性、查询性能的条件下，面对复杂统计、一致性、事务支持等需求时，自然没有关系型更好。
所以无论是关系型还是非关系型数据库，适合业务的才是正确的选择，核心需要一致性、较小量的数据可以用关系型，非结构化数据和性能要求较高的可以考虑非关系型数据库。
这些都不在展开了，都很容易理解
- 海量数据和分布式存储
- 高并发读写和性能
- 易扩展和灵活的数据模型
- 高可用
这些元素是新的需求，在这些需求下满足 ACID 的要素是难点：
- Atomicity 原子性：指事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。
- Consistency 一致性：事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
- Isolation 隔离性：多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。
- Durability 持久性：对数据库所作的更改便持久的保存在数据库之中。
再多的理论也不如实例演进看起来舒服，以通常的电商业务为例。
无论什么时候，如果你的业务现在可控，单机服务永远是最快的实现路径。
一个数据库包含了用户、商品、订单等数据表，灵活可控，查询和写入都不会涉及到复杂的功能。
->+------------------------------------------------------------------+| SERVER || +---------------------------------------+ +--------------+ || | DB | | API Server | || | +------+ +-------+ +--------+ | | | || | | user | | goods | | trades | | +-> | ...... | || | | | | | | | ... | | | || | | | | | | | | <-+ | | || | | | | | | | | | | || | +------+ +-------+ +--------+ | | | || +---------------------------------------+ +--------------+ |+------------------------------------------------------------------+
每天的用户访问并不会对数据库造成太大的压力，在发展初期这个选择是没问题的。
查询和写入在数据库中是两种业务形态，查询大部分比写入多得多，所以可以考虑将查询的流量拆分，也就是扩展多个只读数据库作为从数据源，从主数据库同步数据。
->+-------------------------------+ +-------------------------------+| SERVER MASTER | | SERVER SLAVE 1 (READ ONLY) || +-------------------------+ | | +-------------------------+ || | DB | | | | DB | || | +------+ +-------+ | | +---> | | +------+ +-------+ | || | | user | | goods | | | | | | user | | goods | | || | | | | | ... | | | | | | | | ... | || | | | | | | | <---+ | | | | | | | || | | | | | | | | | | | | | | || | +------+ +-------+ | | | | +------+ +-------+ | || +-------------------------+ | | +-------------------------+ |+-------------------------------+ +-------------------------------+
这样能够将很多的读查询分到很多的服务器上，加上负载均衡，能够满足业务初期阶段查询请求量激增的问题，算是分布式初级功能。
一段时间后业务量上来了，交易量变大了、订单变多了，一个服务器没办法承载存储、计算或者带宽了，就算拆分读写也解决不了每一个实例都在膨胀的问题。
这个时候也很容易的发现，商品、用户和订单之间的业务关联性没有那么大，包括中间层和数据库，都可以拆分到不同的服务器上，以此将总的请求分别分流到几台机器上。
->+----------------+ +-----------------+ +------------------+| SERVER 1 | | SERVER 2 | | SERVER 3 || +----------+ | | +-----------+ | | +------------+ || | DB | | | | DB | | | | DB | || | +------+ | | | | +-------+ | | | | +--------+ | || | | user | | | | | | goods | | | | | | trades | | || | | | | | | | | | | | | | | | | || | | | | | | | | | | | | | | | | || | | | | | | | | | | | | | | | | || | +------+ | | | | +-------+ | | | | +--------+ | || +----------+ | | +-----------+ | | +------------+ |+----------------+ +-----------------+ +------------------+
这样我们在业务中的影响不会太大，数据的获取通过不同的接口来拿，请求和查询不会压在一台服务器上，此时能够满足我们的需求了，这也算是分布式的雏形了。
不同业务的查询请求和计算能力不在局限在一台机器，但是好像数据量变大的问题依然没有解决，用户注册量激增、交易记录变大，一个数据表变得巨长无比，查询效率也很受影响。
于是我们将同一个数据表再拆分到不同的服务器的不同数据库中，用 hash 的方式来映射查询，比如用户 id 尾号为 hash 函数，能够将用户数据拆分到 10 个数据库机器中，分别分担数据量和请求查询。
->+----------------+| SERVER || +-----------+ || | DB | | +----------------+ +-----------------+ +-----------------+| | +-------+ | | | SERVER 1 | | SERVER 2 | | SERVER 3 || | | user1 | | | | +-----------+ | | +-----------+ | | +-----------+ || | | | | | | | DB | | | | DB | | | | DB | || | | | | | | | +-------+ | | | | +-------+ | | | | +-------+ | || | | | | | +------> | | | user1 | | | | | | user2 | | | | | | user3 | | || | | | | | | | | | | | | | | | | | | | | | | || | | | | | | | | | | | | | | | | | | | | | | || | | | | | | | | | | | | | | | | | | | | | | || | | | | | | | +-------+ | | | | +-------+ | | | | +-------+ | || | | | | | | +-----------+ | | +-----------+ | | +-----------+ || | | ... | | | +----------------+ +-----------------+ +-----------------+| | | ... | | || | +-------+ | || +-----------+ |+----------------+
这样我们就将整个数据库从单台机器，变成了用户信息分布到了不同的服务器的不同数据表中，这已经是一个标准的分布式应用了，查询瓶颈的问题很自然的能水平降解，再有性能问题只需要添加机器就能解决。不过架构复杂度在增大，稳定性在降低，这是无法避免的问题。
在这个节点，数据库的业务形态已经和传统的很不一样了，所以为了更好的理解这种新的分布式数据，稳定业务的开发心智负担，抽象了中间件的概念，将分布式的细节隐藏在底层的配置中，同时添加负载均衡、拆分规则、维护等功能。一定程度上封装了开发和业务中对数据的增删查改，能够满足这个阶段业务的需求。
读写分离、分库分表是中间件的业务底层，在配置完好之后，业务对数据的访问从需要自己计算 hash 到某个机器的某个数据表中脱离，只需要查询，让中间件去解析、然后通过路由分发查询真实的目的地，返回我想要的结果，调用方无需将数据库的分表逻辑嵌入到业务中，从而降低心智负担。
这个中间件包含很多的概念和能力：
- 兼容多种底层数据库规范
- 数据库主备切换
- 读写分离（带权重）
- 客户端或者服务端处理
- 灵活、动态的数据库拆分规则和配置
这个中间件的模式目前在大部分的分布式架构中都能满足很长一段时间的需求，毕竟架构上已经是水平可扩展的了。不过缺点是使用门槛、运维、配置的成本比较高。
传统的 分布式数据库 Mysql 之类，数据库系统部署在虚拟机和分布式存储之上，通过网络 io 将多数据库实例连接在一起，一定程度上能够能够承担更大的存储和容灾性，但是底层的架构并没有针对分布式的基础设施优化，在 网络、存储、计算上都有很大的存储空间。
分布式云数据库->+---------------++----------------+ Application +<-------------+| +-------+-------+ || ^ |v | |+-------+-------+ +---------------+ +---------------+| DB (Master) | | DB (Slave 1) | | DB (Slave 2) || +<----->+ +<---->+ |+-----+-+-------+ +-----+-+-------+ +-----+-+-------+| ^ | ^ | ^| | | | | |+----------------------------------------------------------------------+| | | | | | | || +--v-+-------+ +---v-+------+ +---v-+-------+ || | storage | | storage | | storage | || | ×n | | ×n | | ×n | || +------------+ +------------+ +-------------+ || Cloud storage |+----------------------------------------------------------------------+
云原生意思是土生土长根据现代云架构下的技术方案，虚拟化技术、分布式架构作为基础的应用程序。云数据库作为 Paas 提供完整的数据库服务，技术上是搭建在云设施基础上，配合对应的管理软件、客户端 SDK 等实现更可靠的服务，同时满足可伸缩、分布式、细粒度调度等，以此实现现代数据库的需求。
云原生数据库->+---------------++----------------+ Application +<-------------+| +-------+-------+ || ^ |v | |+------+--------+ +-------+-------+ +-------+-------+| DB (Master) | | DB (Slave 1) | | DB (Slave 2) |scale up | +<----->+ +<---->+ |+----+-+--------+ +-----+-+-------+ +----+-+--------+| ^ | ^ | ^v | v | v |+--------+-+----------------------+-+-------------------+-+------------+| +-----------------------------+ || | Share Storage | |scale out | | | || | ×n | || +-----------------------------+ Cloud storage |+----------------------------------------------------------------------+
相比传统的数据库上云的实现，比如在云服务器上安装数据库软件，云原生数据库强调在保证尽量的兼容情况下，从引擎层面，将底层的硬件接口、计算能力抽象到云基础设施上，从而从底层提供动态可伸缩的分布式能力，而不是只是设备上云，服务依然无法方便伸缩、可靠性依旧。
这些数据库大部分是依靠云服务厂商提供的服务，有一整套的技术方案保证性能、安全、健壮性、可扩展和成本。比如腾讯云数据库 CynosDB、阿里云 PolarDB、AWS Aurora、华为云 Taurus等。
数据库是传统的业务数据库，强调的是实时的存储和查询，数据一致性和实时性要求较高，与强调计算的 Hive 类大规模分布式计算和存储仓库是两个概念，即使 Hive 中数据有类似的 SQL 查询。
（Structured Query Language，结构化查询语言） 是什么的理论就不说了~~
SELECT [ALL|DISTINCT] <列名> as <别称> [,<表名> as <别称> ]....FROM <表名> as <别称> [,<表名> as <别称> ]....where <条件>GROUP BY <列名>HAVING <条件>ORDER BY <列名> [ASC|DESC]
- 应用程序与数据库服务器建立链接
- sql发送到数据库，数据库验证是否有执行的权限
- 进入语法解析器，进行词法与语法分析
- 进入优化器生成执行计划，部分dbms会检查是否有可重用的执行计划
- 根据执行计划依次扫描相关表中的行，不在数据缓冲区的走io
- 同时对于被扫描的行可能加锁，同时也可能会被其他sql阻塞
- 扫描的行足够放入查询缓存则开始运算或直接返回，不够则生成临时表，可能消耗io
- 对sql结果进行计算（可能）
- 将计算完成的结果全部写入网络io（可能）
- 如果事务完成则同步事务日志并释放锁，具体方式取决于dbms和当前配置11.关闭连接（可选）
SQL 查询，可以理解为对一些关系集合的计算，然后生成新的意图下的关系集合。
无论是 FROM、WHERE、GROUP BY、ORDER、LIMIT 都是对数据关系的描述意图，引擎会返回这个意图下的新的关系集合
数据库的查询对于业务是无感知的，只需要适用 SQL 声明需要的数据，其中无论发生了什么计算、缓存、聚合、遍历等计算都是不需要考虑的，更不用考虑怎么在业务中替代其中的实现，没有什么意义。
但是计算所消耗的性能和效率跟声明有关，表和字段的描述关系着底层查询的效率。比如查询全部字段确实比指定字段耗时，限制条数确实能减少查询耗时，但是这些都是跟业务相关，所以结合业务优化查询效率才是 SQL 语言的重点。
group 把表按某些字段统计缩小，join 则使用笛卡尔积将多个表连接展开。
SQL 是一门语言，但是不同数据库软件或者不同的引擎对语言的解析执行可能不甚相同，甚至有扩展的语法等，这点需要明确，不能思维定式，需要根据业务来适用不同的代码。(废话)
- 添加索引能显著提高查询性能，但是会降低数据的维护速度，写入效率会变低。
- 对查询过滤或排序较多、取值范围较多、简单数据类型的数据才添加索引
分布式的数据库从原理上对于某些业务不是太友好，比如多机器、多仓库、多表下的 JOIN 操作，如果有自动同步或者广播的逻辑，相当于单机操作性能没有损失，但是如果跨机器分表查询，这就需要一定的网络传输、缓存和计算能力拆分查询，然后聚合来完成这个任务。
这个需要结合业务合适的设计 SQL，与业务强关联。
where 查询的时候会被查询优化器预处理，根据索引、联合索引等优化，不需要在意 SQL 本身的排列顺序，如果不放心的话，可以将查询过滤较多的放左边。
- 执行 where 对全表数据做筛选，返回第 1 个结果集。
- 针对第 1 个结果集使用 group by 分组，返回第 2 个结果集。
- 针对第 2 个结果集中的每 1 组数据执行 select，有几组就执行几次，返回第 3 个结果集。
- 针对第 3 个结集执行 having 进行筛选，返回第 4 个结果集。
- 针对第 4 个结果集排序。
为了保证一致性的基础，数据库中包含很多锁的概念，在解锁之前数据不允许读或写，为了保证数据的一直和事务的隔离之类的。
一般情况下锁的存在是自动辅助数据库运行的，业务中不会主动涉及到其中的概念，但是业务中如果 SQL 用错了，可能因为锁的原因导致并行效率问题。
其中比较多的乐观锁和悲观锁，乐观锁在不锁定的情况下去读取或者更新数据，如果发现不对劲再执行回滚。悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务。
-- 标记事务开始BEGIN;-- 尝试执行 SQLUPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 1. 在业务代码中判断上面都成功之后COMMIT;-- 2. 如果某一个 sql 失败，或者数据检查出现问题之后ROLLBACK;
事务需要在业务中控制进程和分支：
// 类似下面的代码try {// 执行 sqlexecute("insert ...");execute("insert ...");// 都没问题的话 commitcommit();} catch (Exception e) {// 一旦出现问题，执行回滚rollback();}
简单示例，实际使用到事务的话业务都会很复杂，这里不深入了。
- 尽量避免使用 in 和 not inin和not in也会导致数据库进行全表搜索，增加运行时间。使用 EXISTS、between 代替
- 尽量避免使用 or
- 避免排序，尽量在过滤后排序
- 能写在 WHERE 子句里的条件不要写在 HAVING 子句里
- 使用 LIKE 尽量前置匹配
- where 的条件字段本身不要加函数
- 减少和/或消除 shuffle 操作
- 查询时优先过滤
- 在join时，尽量保证，join key，在左右两边至少有一个表中这些join key是唯一的
大部分的实例方案都是分库、分表，每个表又划分主从。所以看起来是一个数据库，底层的数据库实例要多得多。
数据库或数据表的划分能够通过配置或者自动扩展，根据数据的重要性来讲，主从的数量可能也并不一定唯一，可能有多地域的分布。
所以一个数据库有 10 张表，其中一张表分成了 10 个分布式数据库实例中，每个分布式实例节点可能还有在 3 个城市的 6 台从服务器同步数据。
一个查询在客户端数据库中间件创建，然后发送到最近的查询节点，查询节点根据查询的哈希函数找到真实的数据库节点，通过路由发送查询请求，如果目标数据库宕机，能够及时切换到同机房的另一台实时从数据库，又发现同机房的实例掉电无法触达，还能再切换到另一个城市的备份实例完成查询。
分布式数据库，表格拆分为多个存储节点，主键就没必须保证不重复，所以没办法让数据库自增维护。
第一想法是考虑使用 UUID 作为主键，这个最简单的实现，但是字符串作为数据库主键的话，索引性能会很低。
第二想法是每个数据节点的自增使用不同的步长，比如一个是 1、3、5、7、9，另一个是 2、4、6、8、10，这样递增而且不唯一。但是缺点是固定步长后就没办法再扩容了。
所以主键必须是数字型的，唯一性是最基本的要求，而且必须递增，再不济也可以浪费一点性能的趋势递增。
- 中心化的 id 派发思路
可以专门设定一个服务，生成唯一且递增的 id，这样能保证 id 没问题，生成的方法有很多：
1. 单个数据库自增，取这个递增值2. 使用 KV 存储，比如高性能的 Redis 自己写逻辑每次 +1 生成 id
单点的服务一般可靠性不是很理想，一旦出现宕机下线，那么会极大的影响服务，而且 Redis 的数据持久化问题、宕机恢复问题、请求阻塞、网络并发等方面的问题，这块很难做的很可靠。
- 数字 UUID
UUID 的一个很大的缺点是字符串类型，但是 UUID 的生成根据机房、时间戳等信息哈希出的字符串，有借鉴的意义。
雪花 snowflake 算法就可以使用二进制编码数据，将机房信息、时间戳、机器信息、自增数等作为二进制值，然后转换为 10 进制的数字。这样能够保证唯一、也能够保证趋势递增。
但是其中的时间戳信息的获取依赖于机房的时间，如果出现时间回拨可能出现重复问题。
还有更多的方案，都有各自的适用环境，这里不再深究。
一个表的数据按照某个规则分配到不同的数据库的不同的表里，一般把这个叫做 sharding / 分片。
分片策略类型非常多，大致分为 Hash + Mod、Range、Tag 等。
是否会查询所有的库和表是根据分片键决定的，如果 SQL 中不包括分片键，就会查询所有库和表，这个和是否有 like 没有关系。
在查询的时候，如果查询条件指定了 sharding 字段，那就可以直接定位到数据库，一般在查询有限数据的时候能够计算出来。
如果不指定 sharding 字段，比如模糊查询，join 操作等，那么就每个数据库和每个数据表都进行查询，然后各个查询结果汇总。如果有 order by, group 等后续处理的操作，就需要创建临时表，然后进行 order by, group 等等处理，这种操作的性能会差一些。
这块内容更多，慢慢再更~~
感谢您的阅读，本文由 Ubug 版权所有。如若转载，请注明出处：Ubug（https://ubug.io/blog/database-and-sql）
