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

站长

2023年07月29日 23:44 · 阅读数 17

本文正在参加「技术专题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 语句不能使用聚合函数。