db数据库

db

一 概述

1 简介

该目录主要是数据库的笔记

3 常识

3.1 不同数据库对sql的扩展

通常也称为Postgres dialect(数据库方言)，是他们之间在遵循 SQL 规范的前提下，都有各自的扩展特性。比如MySQL的分页是用关键字limit， 而Oracle用的是ROWNUM。

  1. oracle：PL/SQL

  2. DB2：SQL/PL

  3. SQLSever:Transac-SQL(T-SQL)

3.2 数据集、样本数据库

许多数据库系统都为自家的产品提供了样本数据库，比如MySQL的sakila数据集，PostgreSQL的sakila数据集

3.3 E-R模型（ER Model）

ER模型，全称为实体联系模型、实体关系模型或实体联系模式图（ERD）（英语：Entity-relationship model）由美籍华裔计算机科学家陈品山发明，是概念数据模型的高层描述所使用的数据模型或模式图。

3.4 去IOE

IOE指的(I:IBM,O:Oracle,E:EMC)，比如淘宝就从Oracle转成了MySQL就是去IOE

三 基础

0 架构

数据库的分类和对比：

  1. 传统关系型数据库

    1. 缺点

      1. 关系模式难以应付复杂的数据：比如人，书，狗等，可能需要很多表来维护他们的关系

  2. 非关系型数据库(NoSQL) 2. 时序数据库 1. 什么场景适合时序数据库 1. 可能不需要ACID

8 事务

什么是事务：在数据库的使用中，我们常常把一系列操作的集合看作是一个独立的单元，这种构成单一逻辑工作单元的集合被称为事务。

为什么需要事务:Transactions are not a law of nature; they were created with a purpose, namely to simplify the programming model for applications accessing a database. By using transactions, the application is free to ignore certain potential error scenarios and concurrency issues, because the database takes care of them instead (we call these safety guarantees).(事务的产生,是为了当应用程序访问数据库的时候,事务能够简化我们的编程模型,不需要我们去考虑各种各样的潜在错误和并发问题.当我们使用事务时,要么提交,要么回滚,我们不会去考虑网络异常了,服务器宕机了,同时更改一个数据怎么办?因此事务本质上是为了应用层服务的.而不是伴随着数据库系统天生就有的.)

事务的ACID：

  1. 原子性（Atomicity）：原子性表示，一个事务所包含的操作集合是单一的，不可拆分的，即一个事务当中的所有操作应该被视为一个不可分割的整体，要么全成功，要么全部失败。而且事务中任何一个操作失败，都要保证数据库回滚到整个事务执行前的状态。

    1. 最简单的例子比如转账...

  2. 一致性（Consistency）：数据库的一致性表示数据的状态是正确的，一致性是指系统从一个正确的状态,迁移到另一个正确的状态。这里的正确的状态指的是我们预期的正确的状态(也就是说预期的正确的状态是由我们决定而不是DBMS来决定)。事务通过AID来保证C，也就是说满足了AID，自然也就满足了C。

    1. 教材上对一致性的描述是"ensuring the consistency is the responsibility of user, not DBMS.", "DBMS assumes that consistency holds for each transaction".(保证一致性是使用者的责任，而不是数据库的，数据库假设每一个事务都符合一致性)

    2. 例子：依然以转账为例

      1. 假如数据库约束了账户不能为负数，A的账户只有90元，A转账给B100元就会不满足这个约束，此时不满足一致性，事务执行就会失败。

      2. 假如我们在应用层约束了账户不能为负数，A的账户只有90元，A转账给B100元，我们在应用层检查发现转账后不满足这个约束，即不满足一致性，我们就执行事务回滚。

      3. 假如没有约束账户不能为负数，A的账户只有90元，A转账给B100元，就会成功。

  3. 隔离性（Isolation）：隔离性确保事务并发执行后的系统状态与这些事务以某种次序串行执行以后的状态是等价的，这么说有点抽象，概括来说就是，如果有多个事务并发执行，即使我们确保事务的原子性和一致性，这些操作在执行时也并不是严格的串行，而是以某种不可见的形式交叉执行，这种不可见行很可能会导致最终状态的不一致。

    1. 常见问题

      1. 脏读：脏读是指一个事务读到了另一个事务执行的中间结果。依然以转账为例：当我们转账的事务没有执行完，另一个事务就读取了它的中间结果，很有可能就造成脏读。因为万一之前的事务回滚，那么新读取到的结果就是错的，和A账号回滚之后的余额不一致。

      2. 不可重复读：如果在一个事务当中，我们读取了某个数据两次。刚好在这中间，有另一个事务修改了这条数据，那么同样会引起数据错误，因为这两次读取到的结果不一致。比如有个事务T1是读取A的余额两次，第一次A的余额都是100，但是读第二次之前另外有个事务将A的余额增加到了200，导致T1第二次读A余额的时候得到的是200，也就是当前事务还没完的时候另外一个事务对数据进行了修改。这个时候程序就错乱了。

        1. 解决办法是：针对当前修改的数据进行隔离，同一时刻只允许一个事务对该条数据进行修改，以保证数据的一致性。

      3. 幻读：就是一个事务读取两次，读到的数据条数不一致。这点和不可重复读非常类似，不过不同的是不可重复读针对的是确定的某一条数据，而幻读指的是对整个数据库或者是整个表而言。

        1. 解决办法：因为幻读是其他事务修改新增或者修改其他数据产生的，所以要排除掉这种情况，只针对我们修改的数据进行加锁和隔离是不够的。我们需要将整个数据库，或者是分区进行隔离，同一时刻，只允许一个事务对一个分片或者是数据表进行修改。

      4. 更新丢失：当我们针对一条数据进行修改的时候。同时也有另一个事务在修改同一条内容，会导致后者覆盖前者的内容。比如说账户里原本100元，A事务往账户里添加10元，B事务往账户里扣除20元。A修改成110的同时，被B事务的80所覆盖，导致A的操作就像是没有执行过一样，引起更新丢失。这个问题在并发场景当中也最为经典。

        1. 解决办法：做好隔离操作，在一个写入完成之前，禁止其他事务的读入。事实上更新丢失是并发场景下最容易出现的错误，而且如果设计不合理，出现了错误也会非常难排查。

  4. 持久性/永久性（Durability）:指的是事务完成了之后，这个事务对数据库所作出的修改就被持久地保存进了数据库当中，不会再被回滚操作影响。即使出现了各种事故，比如机房断电、网络故障等等意外情况，数据库当中的数据也不能丢失。

DBMS如何保证事务的原子性和持久性：通过预写日志方式实现的，redo和undo机制是数据库实现事务的基础。redo日志用来在断电/数据库崩溃等状况发生时重演一次刷数据的过程，把redo日志里的数据刷到数据库里，保证了事务的持久性，undo日志是在事务执行失败的时候撤销对数据库的操作，保证了事务的原子性。实际上，现在的数据库默认会将所有的操作都当做事务来执行，因此基本上不用担心数据丢失的情况。

13 注释

许多数据库都提供了COMMENT命令，可作用于表，列，索引等

四 高级

11 读写分离

历史演变(What)：

  1. 最早的时候是双机热备：主机和从机通过TCP/IP网络连接，正常情况下主机处于工作状态，从机处于监视状态，一旦从机发现主机异常，从机将会在很短的时间之内代替主机，完全实现主机的功能。这个时候从机仅仅接收主机来的备份数据，读写操作都是走的主机，从机的空闲资源并没有被利用起来。

  2. 然后就是读写分离：基本的架构是让主数据库(master)处理事务性写操作(增、改、删（INSERT、UPDATE、DELETE）)，而从数据库(slave)处理读(SELECT查询)操作。主从同步--数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

    1. 一般是单向的同步：从保证ACID特性的角度来看，如果两台机器都需要向对方进行同步，那逻辑就非常复杂，而且还会大大降低性能。

    2. 一般是主采用读写账户;从采用只读账户，建立mysql链接时使用参数interpolateParams=true

为什么需要读写分离(Why)：

  1. 一般来说数据的写入是比较耗时的，而读是比较快的，如果读写都在同一个主机上，那么数据库的写入会影响查询的效率。

适合场景(When)：

  1. 更适合查询多，写入少的场景

优点：

  1. 对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。

如果实现主从复制(How):不同数据库有不同的实现

五 经验

1 他人之言

  1. 宁愿在代码中把两张表的数据组合到一起也不要用union

  2. 能用程序做就不要只想用sql

2 工具

2.1 DB连接工具

  1. dbeaver：基于java做的，跨平台

  2. navicat

  3. data grip

六 问题

  1. 不同数据库单表的极限是多少 目前我见得最多的就是oracle的四千万条数据

  2. 数据结构中的mergelist表示什么意思？

    1. 合并表(或归并表)

    2. 一般用于将两个顺序表或两个链表合成一个表.

  3. sqlDeveloper和pl/sql Developer？

  4. 表的M/O(Mandatory or Optional)： “Mandatory”表示此查询字段必填；“Optional”表示此查询字段可选。

  5. 一个 split 会产生 2GB 的垃圾?

七 未整理

  1. MongoDB

  2. 数据库读写分离,主从数据库,数据库索引

  3. 读写分离、负载均衡、数据水平拆分

PreviousC#Next1.sql

Last updated

Was this helpful?