数据库基础知识

BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。
哈希索引：类似键值对的形式，一次即可定位。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分

聚簇索引（聚集索引）

索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。

查询速度非常快：因为B+树就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引，聚簇索引少了一次读取数据的 IO 操作。

对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快

依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定慢。

更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价较大，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引（非聚集索引）

索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

更新代价小：非聚簇索引的更新代价没有聚簇索引大，非聚簇索引的叶子节点是不存放数据的

依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据

可能会二次查询(回表):这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

按照应用维度划分

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，

AVL 树

平衡二叉查找树，特点是保证任何节点的左右子树高度之差不超过 1，它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了查询性能。并且在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。 磁盘 IO 是一项耗时的操作，在设计数据库索引时，需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

每个节点非红即黑；
根节点总是黑色的；
每个叶子节点都是黑色的空节点（NIL 节点）；
如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
从根节点到叶节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

红黑树并不追求严格的平衡，而是大致的平衡。正因如此红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，不需要像 AVL 树进行 O(logn) 次数的旋转操作。

TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。使得其在最坏情况下的查找、插入和删除的时间复杂度都是 O(log n)。

B 树& B+树

B 树也称 B-树,全称为多路平衡查找树 ，B+ 树是 B 树的一种变体。目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

SQL语言

1.编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTRING(cust_contact, 1, 2), SUBSTRING(cust_city, 1, 3))) AS user_login 
FROM Customers

2.返回 2020 年 1 月所有订单的订单号（order_num）和订单日期（order_date），按订单日期升序排序

SELECT order_num, order_date FROM Orders WHERE month(order_date) = '01' AND YEAR(order_date) = '2020' ORDER BY order_date

3.联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数，并按产品名称升序排序。

SELECT prod_name, COUNT(order_num) AS orders FROM Products LEFT JOIN OrderItems USING(prod_id) GROUP BY prod_name ORDER BY prod_name

4.将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。

SELECT prod_id, quantity FROM OrderItems WHERE quantity = 100 UNION SELECT prod_id, quantity FROM OrderItems WHERE prod_id LIKE 'BNBG%'

5.请把 exam_record 表中 2021 年 9 月 1 日==之前==开始作答的==未完成==记录全部改为被动完成，即：将完成时间改为'2099-01-01 00:00:00'，分数改为 0。

UPDATE exam_record SET submit_time = '2099-01-01 00:00:00', score = 0 WHERE DATE(start_time) < "2021-09-01" AND submit_time IS null

6.索引

-- 添加B-Tree索引：
        CREATE INDEX idx_name(索引名) ON 表名 (字段名);   -- idx_name为索引名
-- 创建唯一索引：
        CREATE UNIQUE INDEX idx_name ON 表名 (字段名);
-- 创建一个主键索引：
        ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
-- 创建一个全文索引
        ALTER TABLE 表名 ADD FULLTEXT INDEX idx_name (字段名);
-- 通过以上示例，可以看出create 和 alter 都可以添加索引

ALTER TABLE examination_info
    ADD INDEX idx_duration(duration),
    ADD UNIQUE INDEX uniq_idx_exam_id(exam_id),
    ADD FULLTEXT INDEX full_idx_tag(tag);

-- 使用 DROP INDEX 删除索引
DROP INDEX idx_name ON 表名;
-- 使用 ALTER TABLE 删除索引
ALTER TABLE employees DROP INDEX idx_email;

7.请统计有未完成状态的试卷的未完成数 incomplete_cnt 和未完成率 incomplete_rate。

SELECT exam_id, count(submit_time IS NULL OR NULL) incomplete_cnt, ROUND(count(submit_time IS NULL OR NULL) / count(*), 3) complete_rate --舍去小数 FROM exam_record GROUP BY exam_id HAVING incomplete_cnt <> 0

8.请查询姓名的姓是 “张” 的，且根据创建时间倒序排列的前 10 条数据；将你刚查出来的同学的 score 成绩统一修改为 100 分；删除成绩 < 90 的同学

-- 查询姓是“张”的前 10 条数据，根据创建时间倒序排列
SELECT * FROM students
WHERE name LIKE '张%'
ORDER BY created_at DESC
LIMIT 10;

-- 将查询到的同学的 score 成绩统一修改为 100 分
UPDATE students
SET score = 100
WHERE name LIKE '张%';

-- 删除成绩低于 90 分的同学
DELETE FROM students
WHERE score < 90;

9.表名为test，有id（主键自增）和email两个字段，找出email重复的email 并输出id和email

SELECT id, email FROM test WHERE email IN ( SELECT email FROM test GROUP BY email HAVING COUNT(*) > 1 );

10.查询成绩表中三门课总分最高的三人

SELECT student_id, course1 + course2 + course3 AS total_score FROM scores ORDER BY total_score DESC LIMIT 3;

事务

事务是数据库逻辑上的一组操作

特性

原子性（Atomicity）：事务的一次执行要么全部完成，要么完全不起作用；

一致性（Consistency）：执行事务前后，数据保持一致；

隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰；

持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发问题

脏读：一个事务对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据。

不可重复读：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读：与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。第一个事务就会发现多了一些原本不存在的记录，称为幻读。

隔离级别

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。

锁

表级锁和行级锁

表级锁： MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

共享锁和排他锁

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

MVCC

MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

读操作（SELECT）：当一个事务执行读操作时，它会使用快照读取。快照读取是基于事务开始时数据库中的状态创建的，因此事务不会读取其他事务尚未提交的修改。具体工作情况如下：

对于读取操作，事务会查找符合条件的数据行，并选择符合其事务开始时间的数据版本进行读取。
如果某个数据行有多个版本，事务会选择不晚于其开始时间的最新版本，确保事务只读取在它开始之前已经存在的数据。
事务读取的是快照数据，因此其他并发事务对数据行的修改不会影响当前事务的读取操作。

写操作（INSERT、UPDATE、DELETE）：当一个事务执行写操作时，它会生成一个新的数据版本，并将修改后的数据写入数据库。具体工作情况如下：

对于写操作，事务会为要修改的数据行创建一个新的版本，并将修改后的数据写入新版本。
新版本的数据会带有当前事务的版本号，以便其他事务能够正确读取相应版本的数据。
原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响。

事务提交和回滚：

当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。
当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。

版本的回收：为了防止数据库中的版本无限增长，MVCC 会定期进行版本的回收。回收机制会删除已经不再需要的旧版本数据，从而释放空间。

MVCC 通过创建数据的多个版本和使用快照读取来实现并发控制。读操作使用旧版本数据的快照，写操作创建新版本，并确保原始版本仍然可用。这样，不同的事务可以在一定程度上并发执行，而不会相互干扰，从而提高了数据库的并发性能和数据一致性。

MYSQL

MySQL 是一种关系型数据库，主要用于持久化存储系统中的一些数据。

InnoDB

InnoDB 是事务性存储引擎，支持行级锁安全性高。

MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

MYISAM

MySQL 5.5 之前的默认引擎，但不支持事务和行级锁，只有表级锁，不支持外键，而且最大的缺陷就是崩溃后无法安全恢复。

在某些读密集的情况下，使用 MyISAM 也是合适的。

NoSQL

Redis

Redis（Remote Dictionary Server )，即远程字典服务，是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库。redis被广泛应用于缓存，另外，Redis也经常用来做分布式锁

基本数据结构

String（字符串）
Hash（哈希）
List（列表）
Set（集合）
zset（有序集合）

指令

增加（添加）数据：
SET key value：设置指定 key 的值为指定的 value
HSET key field value：将哈希表 key 中的字段 field 的值设为 value
LPUSH key value [value ...]：将一个或多个值插入到列表 key 的头部
SADD key member [member ...]：向集合 key 中添加一个或多个成员

删除数据：
DEL key：删除给定的一个或多个 key 。
HDEL key field [field ...]：删除哈希表 key 中的一个或多个指定字段。
LPOP key：移除并返回列表 key 的头元素。
SREM key member [member ...]：移除集合 key 中的一个或多个 member 元素。

修改数据：
SET key value：设置指定 key 的值为指定的 value，如果 key 已经存在，则覆盖原有值。
HSET key field value：将哈希表 key 中的字段 field 的值设为 value。

查询数据：
GET key：获取指定 key 的值。
HGET key field：获取存储在哈希表中指定字段的值。
LRANGE key start stop：返回列表 key 中指定区间内的元素，区间以偏移量 start 和 stop 指定。
SMEMBERS key：返回集合 key 中的所有成员。

Redis为什么快

基于内存存储实现

内存读写是比在磁盘快很多的，Redis基于内存存储实现的数据库，相对于数据存在磁盘的MySQL数据库，省去磁盘I/O的消耗。

高效的数据结构

Mysql索引为了提高效率，选择了B+树的数据结构。其实合理的数据结构，就是可以让应用/程序更快。先看下Redis的数据结构&内部编码图

单线程

Redis是单线程模型的，而单线程避免了CPU不必要的上下文切换和竞争锁的消耗。也正因为是单线程，如果某个命令执行过长（如hgetall命令），会造成阻塞。

缓存穿透、缓存击穿、缓存雪崩

缓存穿透

简单说就是大量请求的 key 是不合理的，根本不存在于缓存中，也不存在于数据库中。导致这些请求直接到了数据库上，根本没有经过缓存这一层，对数据库造成了巨大的压力。

预防方式

做好参数校验，一些不合法的参数请求直接抛出异常信息返回给客户端。
把不存在的key写到 Redis 中去并设置过期时间
布隆过滤器

通过它我们可以判断一个数据是否存在于海量数据中。可以看作由二进制向量和一系列哈希函数组成的数据结构。相比于我们平时常用的 List、Map、Set 等数据结构，它占用空间更少并且效率更高，但是缺点是其返回的结果是概率性的，而不是非常准确的。

布隆过滤器会使用一个较大的 bit 数组来保存所有的数据，数组中的每个元素都只占用 1 bit ，并且每个元素只能是 0 或者 1（代表 false 或者 true），这也是 Bloom Filter 节省内存的核心所在。

把所有可能存在的请求的值都存放在布隆过滤器中，当用户请求过来，先判断用户发来的请求的值是否存在于布隆过滤器中。不存在就直接返回请求参数错误信息给客户端，存在的话才会走下面的流程。

缓存击穿

请求的 key 对应的是热点数据，数据存在于数据库中，但不存在于缓存中（通常是因为缓存中的那份数据已经过期）。这就可能会导致瞬时大量的请求直接打到了数据库上，对数据库造成了巨大的压力

举个例子：秒杀进行过程中，缓存中的某个秒杀商品的数据突然过期，这就导致瞬时大量对该商品的请求直接落到数据库上，对数据库造成了巨大的压力。

预防方式

设置缓存中热点数据永不过期或者过期时间比较长。
针对热点数据提前预热，将其存入缓存中并设置合理的过期时间比如秒杀场景下的数据在秒杀结束之前不过期。
数据库写数据到缓存之前先获取互斥锁，保证只有一个请求会落到数据库上，减少数据库的压力。

缓存雪崩

缓存在同时大面积失效，导致大量的请求都直接落到了数据库上，对数据库造成了巨大的压力。

例子：缓存中的大量数据在同一时间过期，这个时候突然有大量的请求需要访问这些过期的数据。这就导致大量的请求直接落到数据库上

预防方式

设置不同的失效时间比如随机设置缓存的失效时间。
缓存永不失效（不太推荐，实用性太差）。
缓存预热，也就是在程序启动后或运行过程中，主动将热点数据加载到缓存中。

过期策略和内存淘汰策略

过期策略

定时删除：在设置键的过期时间时，同时创建一个定时器，当键过期时，定时器会立即删除该键。
惰性删除：在对键进行操作时，检查键是否过期，如果过期则删除。
定期删除：Redis会周期性地随机检查一批键，如果发现过期则删除。这个过程由配置参数控制。

淘汰策略

LRU（Least Recently Used，最近最少使用）：Redis会根据键最近被访问的时间来决定删除哪些键。当内存不足时，会优先删除最近最少被访问的键。LRU算法是Redis默认的淘汰策略。
LFU（Least Frequently Used，最近最少使用）：Redis会根据键被访问的频率来决定删除哪些键。当内存不足时，会优先删除访问频率最低的键。
TTL（Time-To-Live，生存时间）：Redis会根据键的过期时间来决定删除哪些键。当键的过期时间到期时，Redis会自动删除该键。
随机淘汰：Redis会随机选择一些键进行删除，这种策略没有明确的规则，适用于某些特定场景。

持久化机制

常用应用场景

缓存：Redis 可以作为缓存存储频繁访问的数据，以减轻后端数据库的压力，加速读取速度。

会话管理：存储用户会话信息，如用户登录状态、购物车内容等。当用户登录时，将用户的会话信息存储在 Redis 中，并设置过期时间，以保证会话的有效性。

计数器和排行榜：适用于实时计数和排行榜功能。

分布式锁

原子性操作：Redis 提供了一系列原子性操作，如 SETNX（SET if Not eXists）、EXPIRE（设置过期时间）等。在获取锁的过程中，可以利用 SETNX 命令尝试设置某个键值对，如果该键不存在，则设置成功，表示获取锁成功，否则表示锁已被其他客户端持有。
过期时间：Redis 键可以设置过期时间，可以使用 EXPIRE 命令为获取的锁设置过期时间，防止锁被持有者意外死锁，避免长时间占用资源。
分布式特性：Redis 是一个支持分布式的内存数据库，可以部署在多台服务器上，通过集群模式实现数据的分布式存储和处理。因此，通过 Redis 实现的分布式锁可以在多个节点之间共享，并且具有一致性和可靠性。
轻量级：Redis 是一个轻量级的内存数据库，具有高效的读写性能和低延迟的特点。使用 Redis 实现分布式锁不会对系统性能产生显著影响，并且可以满足高并发的需求。