数据库相关知识点整理，助力拿到心仪的offer

后端 5月前 0 44

本文正在参加「技能专题19期漫谈数据库技能」活动

1. 数据库的业务

1.1 什么是数据库业务？

业务是指一组逻辑上相关的操作，这些操作要么悉数完结，要么悉数不完结。

业务是数据库办理体系履行过程中的一个逻辑工作单位，是用户定义的一个操作序列，这些操作要么都履行，要么都不履行，是一个不可分割的工作单位。

1.2. 业务的特性

业务的ACID特性:

原子性（Atomicity）：业务是一个不可分割的工作单位，业务中包含的诸操作要么悉数完结，要么悉数不完结，不会完毕在中间某个环节。
共同性（Consistency）：业务有必要使数据库从一个共同性状态变换到别的一个共同性状态。
阻隔性（Isolation）：一个业务的履行不能被其他业务搅扰，多个业务并发履行时，业务之间要彼此阻隔。
持久性（Durability）：一旦业务提交，则其所做的修正将会永久保存在数据库中，即便数据库发生故障也不会丢失。

1.3. 业务的阻隔等级

数据库业务的阻隔等级是指在一个业务处理过程中，对数据的修正被其他业务所看到的程度。业务的阻隔等级从低到高顺次为：

读未提交（Read Uncommitted）: 一个业务能够读取另一个业务修正但还未提交的数据。
读已提交（Read Committed）: 一个业务只能读取另一个业务已经提交的数据。
可重复读（Repeatable Read）: 一个业务履行过程中屡次读取同一数据，成果始终是共同的。在可重复读阻隔等级下，不答应呈现幻读，但答应呈现不可重复读。
串行化（Serializable）: 一切的业务顺次逐个履行，业务之间不能并发履行。

阻隔等级越高，业务之间的并发越少，可是并发功能越差。

1.4. 业务的并发问题

业务的并发问题主要有以下几种：

脏读（Dirty Read）: 一个业务读取了另一个业务未提交的数据。
不可重复读（Nonrepeatable Read）: 一个业务屡次读取同一数据，成果不共同。
幻读（Phantom Read）: 一个业务屡次读取同一范围的数据，成果不共同。
虚读（Lost Update）: 一个业务读取了另一个业务已经提交的数据。

不可重复读和幻读的差异：

不可重复读是指在一个业务内，屡次读取同一数据，成果不共同；
幻读是指在一个业务内，屡次读取同一范围的数据，成果不共同。

1.5. 业务的并发处理

业务的并发处理主要有以下几种：

串行化：一切的业务顺次逐个履行，业务之间不能并发履行。
乐观锁：在业务提交时，查看数据是否被其他业务修正，假如被修正则抛弃提交，不然提交业务。
悲观锁：在业务履行时，对数据加锁，其他业务不能修正数据，直到业务完毕。
两阶段确定协议：在业务履行前，对数据加锁，直到业务完毕。
多版别并发操控（MVCC）：在读取数据时，读取当前版别的数据，而不是最新版别的数据。
时刻戳：在业务提交时，查看数据是否被其他业务修正，假如被修正则抛弃提交，不然提交业务。
快照阻隔（Snapshot Isolation）：在业务履行前，对数据加锁，直到业务完毕。

2. 数据库索引

2.1. 索引的效果

索引的效果主要有以下几种：

加速数据检索速度；
经过索引能够协助咱们防止排序和分组操作；
经过索引能够将随机 I/O 变为顺序 I/O；
经过索引能够协助咱们防止表和表之间的衔接操作。

2.2. 索引的分类

索引的分类主要有以下几种：

单值索引：索引列只包含单个值。
多值索引：索引列包含多个值。
仅有索引：索引列的值有必要仅有，但答应有空值。
主键索引：一张表只能有一个主键索引，主键索引的值有必要仅有，不答应有空值。
组合索引：索引列是多个列的组合。
掩盖索引：索引包含了一切需求查询的列。
全文索引：索引列是全文索引列。
哈希索引：索引列是哈希索引列。
空间索引：索引列是空间数据类型。
其他索引：索引列是其他数据类型。

2.3. 索引的优缺陷

索引的优缺陷主要有以下几种：

长处：
- 索引能够大大加速数据的检索速度；
- 索引能够协助咱们防止排序和分组操作；
- 索引能够将随机 I/O 变为顺序 I/O；
- 索引能够协助咱们防止表和表之间的衔接操作。
缺陷：
- 索引会占用物理空间，假如建立了过多的索引，会导致物理空间的糟蹋；
- 索引会降低写入数据的速度，因为在写入数据时，数据库服务器还需求保护索引数据；
- 索引会降低更新数据的速度，因为在更新数据时，数据库服务器还需求保护索引数据；
- 假如对表中的数据进行了频繁的增删改操作，那么索引就会失效，反而降低了查询速度。

2.4. 索引的运用

索引的运用主要有以下几种：

运用掩盖索引: 经过索引能够协助咱们防止排序和分组操作。
运用组合索引: 索引列是多个列的组合。
运用索引下推: 经过索引能够协助咱们防止表和表之间的衔接操作。

3. 数据库的类型

3.1. 数据库的分类

数据库的分类主要有以下几种：

联系型数据库：联系型数据库是指选用了联系模型来安排数据的数据库，联系模型是一种依据二维表的数据模型，二维表中的每一行代表一条记录，每一列代表一种属性，二维表之间的联系能够经过行与行之间的联系来表明。
非联系型数据库：非联系型数据库是指没有选用联系模型来安排数据的数据库，非联系型数据库的数据模型能够是键值对、文档、图形、列存储等。
文档型数据库：文档型数据库是指选用了文档模型来安排数据的数据库，文档模型是一种依据文档的数据模型，文档是一种自包含的数据结构，文档中的数据能够是键值对、文本、二进制数据等。

3.2. 联系型数据库的分类

联系型数据库主要有以下几种：

MySQL：MySQL 是一种联系型数据库，它是一个联系数据库办理体系，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。
Oracle：Oracle 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Oracle 公司开发。
SQL Server：SQL Server 是一种联系型数据库，它是一个联系数据库办理体系，由美国微软公司开发。
PostgreSQL：PostgreSQL 是一种联系型数据库，它是一个联系数据库办理体系，由美国 PostgreSQL Global Development Group 开发。
DB2：DB2 是一种联系型数据库，它是一个联系数据库办理体系，由美国 IBM 公司开发。
SQLite：SQLite 是一种联系型数据库，它是一个联系数据库办理体系，由加拿大加州大学开发。
MariaDB：MariaDB 是一种联系型数据库，它是一个联系数据库办理体系，由瑞典 MariaDB Foundation 公司开发。
MemSQL：MemSQL 是一种联系型数据库，它是一个联系数据库办理体系，由美国 MemSQL 公司开发。
H2：H2 是一种联系型数据库，它是一个联系数据库办理体系，由德国 H2 Database Engine 公司开发。
HSQLDB：HSQLDB 是一种联系型数据库，它是一个联系数据库办理体系，由美国 HSQLDB 公司开发。
Derby：Derby 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Apache Derby 公司开发。
Firebird：Firebird 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Firebird Foundation 公司开发。
Sybase：Sybase 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Sybase 公司开发。
Informix：Informix 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Informix 公司开发。
Access：Access 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Microsoft 公司开发。
Teradata：Teradata 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Teradata 公司开发。
Vertica：Vertica 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Vertica Systems 公司开发。
Netezza：Netezza 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Netezza 公司开发。
Greenplum：Greenplum 是一种联系型数据库，它是一个联系数据库办理体系，由美国 Greenplum 公司开发。

上面这些联系型数据库可能有缺失，个人计算的不是很全面，也可能有错误，欢迎咱们补充。

3.3. 非联系型数据库的分类

非联系型数据库主要有以下几种：

Redis：Redis 是一种非联系型数据库，它是一个依据内存的键值对数据库，由 C 言语编写。
MongoDB：MongoDB 是一种非联系型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
HBase：HBase 是一种非联系型数据库，它是一个依据列存储的数据库，由 Java 言语编写。
Neo4j：Neo4j 是一种非联系型数据库，它是一个依据图形的数据库，由 Java 言语编写。
Elasticsearch：Elasticsearch 是一种非联系型数据库，它是一个依据文档的数据库，由 Java 言语编写。
Memcached：Memcached 是一种非联系型数据库，它是一个依据内存的键值对数据库，由 C 言语编写。
Cassandra：Cassandra 是一种非联系型数据库，它是一个依据列存储的数据库，由 Java 言语编写。
CouchDB：CouchDB 是一种非联系型数据库，它是一个依据文档的数据库，由 Erlang 言语编写。
Riak：Riak 是一种非联系型数据库，它是一个依据键值对的数据库，由 Erlang 言语编写。
Hadoop：Hadoop 是一种非联系型数据库，它是一个依据文件的数据库，由 Java 言语编写。
InfluxDB：InfluxDB 是一种非联系型数据库，它是一个依据时刻序列的数据库，由 Go 言语编写。
LevelDB：LevelDB 是一种非联系型数据库，它是一个依据键值对的数据库，由 C++ 言语编写。
RocksDB：RocksDB 是一种非联系型数据库，它是一个依据键值对的数据库，由 C++ 言语编写。
Tarantool：Tarantool 是一种非联系型数据库，它是一个依据键值对的数据库，由 C 言语编写。
TimescaleDB：TimescaleDB 是一种非联系型数据库，它是一个依据时刻序列的数据库，由 C 言语编写。
VoltDB：VoltDB 是一种非联系型数据库，它是一个依据内存的键值对数据库，由 Java 言语编写。
Aerospike：Aerospike 是一种非联系型数据库，它是一个依据内存的键值对数据库，由 C 言语编写。
ArangoDB：ArangoDB 是一种非联系型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
FoundationDB：FoundationDB 是一种非联系型数据库，它是一个依据键值对的数据库，由 C++ 言语编写。
OrientDB：OrientDB 是一种非联系型数据库，它是一个依据图形的数据库，由 Java 言语编写。

非联系型数据库也是咱们常说的 NoSQL 数据库，它们的特色是不支撑 SQL 句子，而是支撑各种各样的 API，比方 Redis 支撑 Redis 指令，MongoDB 支撑 MongoDB 指令，等等。

3.4. 文档型数据库

文档型数据库是一种非联系型数据库，它是一个依据文档的数据库，其实和上面说到的非联系型数据库的差异就是文档型数据库的数据是以文档的方式存储的，而非联系型数据库的数据是以键值对的方式存储的。

文档型数据库的数据是以文档的方式存储的，文档是一种数据结构，它是一种类似于 JSON 的数据结构，它是一种键值对的数据结构，可是它的值能够是任意类型的数据，比方字符串、数字、数组、目标等等。

文档型数据库包含但不限于：

MongoDB：MongoDB 是一种文档型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
CouchDB：CouchDB 是一种文档型数据库，它是一个依据文档的数据库，由 Erlang 言语编写。
Couchbase：Couchbase 是一种文档型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
ArangoDB：ArangoDB 是一种文档型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
FoundationDB：FoundationDB 是一种文档型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
OrientDB：OrientDB 是一种文档型数据库，它是一个依据文档的数据库，由 Java 言语编写。
RethinkDB：RethinkDB 是一种文档型数据库，它是一个依据文档的数据库，由 C++ 言语编写。
MarkLogic：MarkLogic 是一种文档型数据库，它是一个依据文档的数据库，由 Java 言语编写。

4. innodb 和 myisam 差异

4.1. innodb

InnoDB 是 MySQL 5.5 之后的默许存储引擎，它是一种业务型数据库，它支撑业务，支撑行级锁，支撑外键，支撑溃散修正才能和并发操控。

长处：

支撑业务，支撑行级锁，支撑外键，支撑溃散修正才能和并发操控。
支撑 MVCC，多版别并发操控，能够有用的解决幻读问题。
支撑 B+ 树索引和哈希索引。
支撑全文索引。
支撑自增长列。
支撑外键。

缺陷：

占用磁盘空间大，因为它是以页为单位进行存储的，而页的大小是固定的，一般是 16KB，所以假如数据量比较小的话，会造成磁盘空间的糟蹋。
不支撑全文索引的排序、分组和约束，不支撑全文索引的含糊查询等。

4.2. myisam

MyISAM 是 MySQL 5.5 之前的默许存储引擎，它是一种非业务型数据库，它不支撑业务，不支撑行级锁，不支撑外键，不支撑溃散修正才能和并发操控。

长处：

占用磁盘空间小，因为它是以表为单位进行存储的，而表的大小是不固定的，所以假如数据量比较小的话，会造成磁盘空间的糟蹋。
支撑全文索引的排序、分组和约束，支撑全文索引的含糊查询等。

缺陷：

不支撑业务，不支撑行级锁，不支撑外键，不支撑溃散修正才能和并发操控。
不支撑 MVCC，多版别并发操控，不能够有用的解决幻读问题。
不支撑 B+ 树索引和哈希索引。
不支撑自增长列。
不支撑外键。

4.3. innodb 和 myisam 差异

他们的差异主要有以下几点：

innodb 支撑业务，myisam 不支撑业务。
innodb 支撑行级锁，myisam 不支撑行级锁。
innodb 支撑外键，myisam 不支撑外键。
innodb 支撑溃散修正才能和并发操控，myisam 不支撑溃散修正才能和并发操控。
innodb 支撑 MVCC，多版别并发操控，能够有用的解决幻读问题，myisam 不支撑 MVCC，多版别并发操控，不能够有用的解决幻读问题。
innodb 支撑 B+ 树索引和哈希索引，myisam 不支撑 B+ 树索引和哈希索引。
innodb 支撑自增长列，myisam 不支撑自增长列。
innodb 支撑外键，myisam 不支撑外键。
innodb 占用磁盘空间大，myisam 占用磁盘空间小。
innodb 支撑全文索引的排序、分组和约束，支撑全文索引的含糊查询等，myisam 支撑全文索引的排序、分组和约束，支撑全文索引的含糊查询等。

能够看到上面列举的，innodb 和 myisam 长处和缺陷，它们的长处和缺陷是彼此敌对的，所以在实践的开发中，咱们需求依据自己的业务场景来挑选适宜的存储引擎。

5. where 和 having 的差异

5.1. where

where 子句用于指定查询条件，where 子句的条件能够是任何有用的 SQL 表达式。

5.2. having

having 子句用于指定分组后的查询条件，having 子句的条件能够是任何有用的 SQL 表达式。

5.3. where 和 having 的差异

where 子句用于指定查询条件，having 子句用于指定分组后的查询条件。

6. where 和 on 的差异

6.1. on

on 子句用于指定衔接条件，on 子句的条件能够是任何有用的 SQL 表达式。

6.2. where 和 on 的差异

where 子句用于指定查询条件，on 子句用于指定衔接条件。

7. delete 、truncate 和 drop 的差异

7.1. delete

delete 句子用于从表中删去行，delete 句子的语法格局如下：

DELETE FROM table_name
WHERE [condition];

特色：delete 句子能够删去表中的部分行，可是不会删去表结构，也不会开释表空间，不会自动提交业务。

7.2. truncate

truncate 句子用于删去表中的一切行，truncate 句子的语法格局如下：

TRUNCATE TABLE table_name;

特色：truncate 句子能够删去表中的一切行，可是不会删去表结构，也不会开释表空间，会自动提交业务。

7.3. drop

drop 句子用于删去表，drop 句子的语法格局如下：

DROP TABLE table_name;

特色：drop 句子能够删去表，可是不会开释表空间，会自动提交业务。

7.4. delete 、truncate 和 drop 的差异

delete 、truncate 和 drop 都能够删去表中的数据。
drop 、truncate 会自动提交业务，delete 不会自动提交业务。
delete 、truncate 不会开释表空间，drop 会开释表空间。
delete 、truncate 不会删去表结构，drop 会删去表结构。

履行速度：drop > truncate > delete

8. inner join 、left join 、right join 和 full join 的差异

8.1. inner join

inner join 句子用于从两个或多个表中查询数据，inner join 句子的语法格局如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

特色：inner join 句子会回来两个或多个表中满足衔接条件的数据。

8.2. left join

left join 句子用于从两个或多个表中查询数据，left join 句子的语法格局如下：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

特色：left join 句子会回来左表中的一切数据，即便右表中没有匹配的数据。

8.3. right join

right join 句子用于从两个或多个表中查询数据，right join 句子的语法格局如下：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

特色：right join 句子会回来右表中的一切数据，即便左表中没有匹配的数据。

8.4. full join

full join 句子用于从两个或多个表中查询数据，full join 句子的语法格局如下：

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

特色：full join 句子会回来左表和右表中的一切数据，即便两个表中没有匹配的数据。

8.5. inner join 、left join 、right join 和 full join 的差异

inner join 句子会回来两个或多个表中满足衔接条件的数据。
left join 句子会回来左表中的一切数据，即便右表中没有匹配的数据。
right join 句子会回来右表中的一切数据，即便左表中没有匹配的数据。
full join 句子会回来左表和右表中的一切数据，即便两个表中没有匹配的数据。
inner join 、left join 、right join 和 full join 的履行速度顺次递减。

9. union 、union all 和 intersect 的差异

9.1. union

union 句子用于兼并两个或多个查询成果集，union 句子的语法格局如下：

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

特色：union 句子会兼并两个或多个查询成果集，可是会去除重复的数据。

9.2. union all

union all 句子用于兼并两个或多个查询成果集，union all 句子的语法格局如下：

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

特色：union all 句子会兼并两个或多个查询成果集，可是不会去除重复的数据。

9.3. intersect

intersect 句子用于兼并两个或多个查询成果集，intersect 句子的语法格局如下：

SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

特色：intersect 句子会兼并两个或多个查询成果集，可是只会回来两个查询成果集中都存在的数据。

9.4. union 、union all 和 intersect 的差异

union 句子会兼并两个或多个查询成果集，可是会去除重复的数据。
union all 句子会兼并两个或多个查询成果集，可是不会去除重复的数据。
intersect 句子会兼并两个或多个查询成果集，可是只会回来两个查询成果集中都存在的数据。
union 、union all 和 intersect 的履行速度顺次递减。

10. group by 和 having 的差异

10.1. group by

group by 是对查询成果进行分组的关键字，group by 句子的语法格局如下：

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

特色：group by 句子用于对查询成果进行分组。

10.2. having

having 是对分组后的成果进行挑选的关键字，having 句子的语法格局如下：

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

特色：having 句子用于对分组后的成果进行挑选。

10.3. group by 和 having 的差异

group by 句子用于对查询成果进行分组。
having 句子用于对分组后的成果进行挑选。
group by 句子有必要在 having 句子之前。
having 句子有必要在 order by 句子之前。
group by 句子能够运用聚合函数，having 句子不能运用聚合函数。

Go Java MySQL 数据库数据结构空间数据索引