首页  思维导图  详情

数据储存体系梳理

2023-02-03 16:38:25   0  举报





AI智能生成

1.存储工具发展历程. 2.针对关系型数据库SQL知识,做一个系统思维导图

数据库

商业战略

业务流程

工作流程

作者其他创作

大纲/内容

数据库认识

存储工具

RDBMS

传统关系型数据库,支持事务,但是单表数据量超过六千万条读写性能急剧下降

Mysql

MariaDB开源免费轻量级数据库

Orcal

闭源收费 /偏重/大型数据库

PostgreSQL

开源免费,有丰富的几何类型,空间GIS领域常用

NoSQL

常用于OLAP场景,处理亿级数据量,但普遍不支持事务.

Mongo

文档类型数据库 bson结构. 最像关系型数据库的NoSql

Redis

key value结构,消息队列

内存数据库,缓存场景使用

Kafka

与redis类似,大数据场景下的消息队列,常用于对接flink做实时计算.

Hive

数据仓库,数据存储在HDFS,常用于离线计算场景,自带计算引擎HQL

Elastic Search / ClickHouse /Doris

搜索类型数据库,读性能高

Hbase/Cassandra

列式存储,写性能高

NewSQL

除了要兼顾存储海量数据，也要具备 RDBMS 的 ACID 特性以及对于 SQL 的支持能力

Doris

百度开源的大规模并行处理（MPP）分析数据库,Doris 植根于 Apache Impala 和 Google Mesa

TiDB

分布式关系型数据库,支持混合事务和分析处理工作负载。它与MySQL兼容，并且可以提供水平可扩展性、强一致性和高可用性。

Lakehouse

湖仓一体存储架构,数据湖存储非结构化数据,比如监控的音视频,图像数据

Hudi

主要支持 Upserts、Deletes 和 Incremental 数据处理

Iceberg

解决hive的ACID以及upsert角度出发来解决数据高效更新的问题

表

行

一组相关的数据

列

相同类型的数据

主键

一个表只能有一个主键

主键也是一个索引字段

基本原则:不使用任何业务相关的字段作为主键

业务字段变化不可控,未来如果要修改,就会对业务产生严重影响,比如身份证号做主键,升为后业务改动大. 除外确定该字段未来无变化

常用主键

普通自增主键ID

UUID

通用唯一识别码 (Universally Unique Identifier)

外键

表的外键就是另一表的主键，外键将两表联系起来

索引

概念

用来快速地寻找那些具有特定值的记录,加快查询效率

创建索引

CREATE INDEX <索引的名字> ON tablename (列的列表);

建表指定索引

CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

唯一索引

索引列的所有值都只能出现一次，即必须唯一

例如身份证号、邮箱地址,这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

只添加一个唯一约束而不创建唯一索引

前缀索引

有些值过长比如text,设定统一前缀,截取前缀作为索引

数据类型

char

定长字符

varchar

可变长字符类型,最大长度2000

默认长度255,需要更大可以指定

varchar2

同上,最大长度4000,空字符串处理为null,oracle推荐使用

nvarchar

同上,纯英文和数字用/varchar，有中文使用nvarchar。

text

可变长字符类型,与varchar的区别是只能加前缀索引

int

整型

double

双精度小数

占8 个字节，用64 位二进制描述

float

单精度小数

占4 个字节，用32 位二进制描述

blob

Binary Large Object）表示二进制类型的大对象

DATE

日期类型

TIMESTAMP

YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳

mysql存储引擎

innodb

支持事务,这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

InnoDB 支持外键

mysql8默认索引

myisam

只适合读场景,不支持事务

事务（transaction）

多条语句作为一个整体进行操作的功能叫做事务

数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败

满足条件

原子性(Atomicity)

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样

一致性(Consistency)

保证多个节点中数据的值是一致的,需要靠各种锁来达成“一致性”,这块在分布式环境常用,也是最难理解的

隔离性(Isolation)

数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别. 级别越高，执行效率就越低

Read uncommitted

最低隔离级别,会出现脏读

指一个事务读取到另一个事务未提交的数据。

Read committed

Oracle默认隔离级别. 可能出现不可重复读

指一个事务对同一行数据重复读取两次，但得到的结果不同。

Repeatable read

MySQL默认隔离级别. 事务可能会遇到幻读（Phantom Read）的问题

幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

Serializable

脏读、不可重复读、幻读都不会出现。

由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

持久性(Durability)

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

SQL语句基础

增

INSERT INTO table_name
VALUES (value1,value2,value3,...)

按照表字段顺序依次增加

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

根据列出的字段按顺序增加

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录,就要使用on duplicate update

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

INSERT IGNORE INTO ...

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略,使用insert ignore into

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

把其他表查询结果插入到当前库,前提是查询结果的数据结构类型与当前表结构一致

删

DELETE FROM table_name
WHERE some_column=some_value;

改

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

查

SELECT * FROM table_name;

SELECT 想要的字段 FROM table_name;

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

强制使用指定索引

SQL实用函数

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT * FROM Websites
WHERE name LIKE 'G%';

between

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

order by

SELECT * FROM Websites
ORDER BY alexa; 默认升序

ORDER BY field DESC 降序

如果都用降序，必须用两个desc

ORDERBY f1 DESC, f2 DESC

limit

经常与order by 配合使用
SELECT
employee_id, first_name, last_name
FROM
employees
ORDER BY first_name DESC
LIMIT 5;

配合offset(偏移量)实现分页,跳过3条
SELECT
employee_id, first_name, last_name
FROM
employees
ORDER BY first_name DESC
LIMIT 5 OFFSET 3;

group by

having分组条件过滤

join

inner join

join加了on条件默认就是inner join,没加条件就是cross join. 内关联,两表交集

left join

左外连接

首先返回所有左表数据,对于右表返回满足条件的数据,如果没有相应的数据,就返回空值

实例

SELECT d.dept_id, e.dept_id, d.dept_name, e.emp_name
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
ORDER BY d.dept_id DESC;

right join

右外连接

首先返回右表中所有的数据；对于左表，返回满足连接条件的数据，如果没有相应的数据就返回空值。

full join

展示两表所有数据. 相当于左右连接结果的并集

cross join

笛卡尔积,两个表的交叉连接相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。

交叉连接可能会导致查询结果的数量急剧增长，从而引起性能问题；通常应该使用连接条件进行过滤，避免产生交叉连接。

union

去重并集,两表字段顺序要保持一致

SELECT emp_id
FROM excellent_emp
WHERE year = 2018
UNION
SELECT emp_id
FROM excellent_emp
WHERE year = 2019;

union all

使用场景

语法与union一致,联合字段顺序要一致

UNION ALL 不需要进行重复值的排除，性能比 UNION 更好；尤其是数据量比较大的情况下。

代替or优化执行效率

别名

常用函数

sum

聚合函数一般配合group by使用

max

min

avg

count

now/sysdate

当前时间

trim()----去重空格

concat(x,y)——拼接字符串

substr(x,y)与substr(x,y,z)——截取字符串

upper

转大写

lower

转小写

having

条件过滤，用来筛选前面GROUP BY的结果

select name,sum(money) from test1 group by name having sum(money) > 600;

按name分组,展示大于600的name

NVL()

Oracle常用. 空值转换函数NVL（表达式1，表达式2）如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值,对标MysqlI的FNULL()

decode()

decode相当于：case when then else end语句

select
    sum(decode(name,'a',id,0)) id_1,
    sum(decode(name,'b',id,0)) id_2,
    sum(decode(name,'c',id,0)) id_3 from t_decode;

select
    case name when 'a' then id else 0 end as id_1,
    case name when 'b' then id else 0 end as id_2,
    case name when 'c' then id else 0 end as id_3
from t_decode;

is null/ not null

不可能用比较运算符，如=、<或<>，来测试NULL值。

我们将不得不使用 "IS NULL "和 "IS NOT NULL "操作符来代替。

实例:查询address列不为空的数据

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

and/ or / not

国家为 "Germany"或 "Spain"的 "Customers"中选择所有字段:

SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain' LIMIT 5;

从国家不是 "Germany"的 "Customers"中选择所有字段:

SELECT * FROM Customers
WHERE NOT Country='Germany' LIMIT 5;

SQL高级

子查询

表子查询

一般使用in not in,后边跟上sql子句

SELECT emp_name
FROM employee
WHERE job_id IN (SELECT job_id FROM employee WHERE dept_id = 3);

ALL、ANY/SOME 运算符

结合比较运算符（=、!=、<、<=、>、>=）使用

SELECT emp_name, hire_date
  FROM employee
WHERE hire_date > ALL (SELECT e.hire_date
                          FROM employee e
                          JOIN department d ON (d.dept_id = e.dept_id)
                         WHERE d.dept_name = '研发部');

子查询返回了研发部所有员工的入职日期；“> ALL”表示比结果中的所有值都大，也就是大于结果中的最大值。

EXISTS

EXISTS 运算符用于判断查询子句是否有记录，如果有一条记录存在就返回 True，否则返回 False。

使用场景

和In类型,都是判断子查询是否有结果,exists效率更高

实例

SELECT d.dept_name
  FROM department d
WHERE EXISTS ( SELECT 1
                  FROM employee e
                 WHERE e.sex = '女'
                   AND e.dept_id = d.dept_id)
ORDER BY dept_name;

EXISTS 之后是一个关联子查询，先执行外查询找到 d.dept_id；然后依次将 d.dept_id 传递给子查询，判断该部门是否存在女性员工；子查询一旦找到任何数据立即返回结果。EXISTS 只判断结果的存在性，因此子查询的 SELECT 列表中的内容无所谓，通常使用一个常量值。该查询的结果表明“研发部”和“财务部”存在女性员工。

视图

虚拟表,对视图只能进行select操作,简化复杂的SQL操作

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL查询执行原理

SQL关键字顺序

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

执行顺序

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
FROM player JOIN team ON player.team_id = team.team_id #顺序1
WHERE height > 1.80 #顺序2
GROUP BY player.team_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7