Python后端技术栈(六)--数据库

正文共：9051 字 10 图 预计阅读时间：23 分钟

每日分享

What makes you different or weird—that’s your strength.

那些让你与众不同或怪异的，就是你的力量所在。

小闫语录：

有些人也许一生都在追随寻找自我的脚步，最后却迷失在寻找的过程中。那么怎么去发现你的特质，让你的亮点闪光？其实很简单，正如今天分享的语句所说，那些让你与众不同或怪异的，就是你的力量所在。

1.6数据库

上篇文章传送门『我是个链接』

上篇文章对网络编程中的一些经典问题做了总结，比如各种网络协议、IO 多路复用模型、并发库等等。

本篇文章将开始数据库的相关内容，开始咯~

1.6.1 MySQL

1.6.1.1 MySQL 基础知识点

1.事务的原理，特性，事务并发控制

2.常用的字段、含义和区别

3.常用数据库引擎之间区别

1.6.1.2事务 Transaction

1.事务是数据库并发控制的基本单位

2.事务可以看做是一系列 SQL 语句的集合

3.事务的特性就是要么全部执行成功，要么全部执行失败（回滚）

我们最常见的就是转账操作这样一个使用案例。比如 A 给 B 转账，第一步是从 A 的账户查询一下余额还够不够，然后扣款。第二步是 B 的账户里面增加对应的金额。

从代码的层面来说，下面举一个 SQLAlchemy 框架执行回滚操作的例子：

session.begin()
try:
    item1 = session.query(Item).get(1)
    item2 = session.query(Item).get(2)
    item1.foo = 'bar'
    item2.bar = 'foo'
    session.commit()
except:
    session.rollback()
    raise

1.6.1.3事务的 ACID 特性

原子性（Atomicity）：一个事务中所有操作全部完成或失败

一致性（Consistency）：事务开始和结束之后数据完整性没有被破坏

隔离性（Isolation）：允许多个事务同时对数据库修改和读写

持久性（Durability）：事务结束之后，修改时永久的不会丢失

1.6.1.4事务的并发控制可能出现的四种异常情况

1.幻读（phantom read）：一个事务第二次查出现第一次没有的结果

也就是读取到了之前不存在的数据（新增或删除）。

2.非重复读（nonrepeatable read）：一个事务重复读两次得到不同结果

3.脏读（dirty read）：一个事务读取到另一个事务没有提交的修改

4.丢失修改（lost updata）：并发写入造成其中一些修改丢失

1.6.1.5四种事务隔离级别

为了解决并发控制异常，定义了 4 种事务隔离级别：

1.读未提交（read uncommitted）：别的事务可以读取到未提交改变

2.读已提交（read committed）：只能读取已经提交的数据

3.可重复读（repeatable）：同一个事务先后查询结果一样

4.串行化（Serializable）：事务完全串行化的执行，隔离级别最高，执行效率最低

串行化的隔离级别最高，但是执行效率是最低的

1.6.1.6如何解决高并发场景下的插入重复

高并发场景下，写入数据库会有数据重复的问题。

1.使用数据库的唯一索引

2.使用队列异步写入

3.使用 redis 等实现分布式锁

1.6.1.7乐观锁和悲观锁

1.悲观锁就是先获取锁再进行操作。一锁二查三更新（select for update）

2.乐观锁先修改，更新的时候发现数据已经变了就会回滚（check and set）

乐观锁根据版本号或者时间戳进行实现。比如我进行修改操作最开始的时候版本号是 1，但是当我准备执行写入的时候发现版本号变为了 2 ，这时就说明其他事务对数据进行了修改，它就会执行回滚操作。

3.需要根据响应速度、冲突频率、重试代价来判断使用哪一种。

1.6.1.8 MySQL 常用数据类型

字符串类型

CHAR 用来存储定长的数据类型

VARCHAR 用来存储变长的数据类型

TEXT 则是用来存储文章或者新闻等数据

数值类型

时间类型

1.6.1.9数据库引擎 InnoDB vs MyISAM

1.MyISAM 不支持事务，InnoDB 支持事务

2.MyISAM 不支持外键，InnoDB 支持外键

3.MyISAM 只支持表锁，InnoDB 支持行锁和表锁

4.MyISAM 支持全文索引，InnoDB 不支持

1.6.2 MySQL 索引原理及优化常见

深入原理理解而不是死记硬背。

1.6.2.1 MySQL 索引重点

1.索引的原理、类型和结构

2.创建索引的注意事项，使用原则

3.如何排查和消除慢查询

1.6.2.2什么是索引

索引就是数据表中一个或者多个列进行排序的数据结构。索引能够大幅提升检索速度（可以结合咱们之前提到的查找结构）。创建、更新索引本身也会耗费空间和时间。

1.6.2.3什么是 B - Tree

也许大家看到的教程或者是资料，在介绍的时候都是先介绍一下它的结构，再介绍一下它的特性，然后就结束了。我刚开始的时候也很懵逼，后来从网上看到了一段视频，老师的讲法不错，现在传授给大家。

咱们从查找结构的进化史着手。

1.线性查找：它就是一个个找，虽然实现简单，但是太慢。

2.二分查找：要求数组有序，同样它实现也比较简单。但是因为要求是有序的，插入特别慢，往前面插入一个数据，为了保持有序，后面的都需要调整，我们认为它的时间复杂度是O(n)。

3.HASH ：它最大的优点就是查询快，缺点就是占用空间（底层是冗余的数组存储）。其实是不太适合存储大规模的数据，虽然有些数据库是支持哈希索引的，但是我们发现没有大规模的去使用过它。

4.二叉查找树：它的插入和查询很快（时间复杂度是log(n)），同样它也无法存储大规模数据，而且它还有个致命的缺点，就是复杂度退化（极端情况，比如插入一个有序的数列，二叉树就退化为线性结构）。

5.平衡树：为了解决 bst（也就是二叉查找树） 退化的问题，出现了平衡树，也就是它有个平衡的操作。但它也有缺点，就是一个父亲只有两个子节点。节点非常多的时候，依然树高度很深。

6.多路查找树：一个父亲多个孩子节点（度）；节点过多树高不会特别深。因为树矮的原因，那么树的查找效率就提高了。

7.多路平衡查找树：B - Tree

下面正式介绍 B - Tree：

首先我们需要了解它是多路平衡查找树，每个节点最多 m （m >= 2）个孩子，称为 m 阶或者度（所以不要看树深度，要看孩子节点最多的那个节点有几个猴孩子）。然后它有一个特点，就是叶子节点具有相同的深度，这个就厉害了，也就是从根节点到任何一个节点的距离都是相同的。最后，叶子节点的数据 key 是从左到右递增的。

但是它有个缺点，就是范围查找会比较困难。所以数据库实际上采用了 B + Tree。

1.6.2.4 什么是 B + Tree

B + Tree 实际上是 B - Tree 的变形。也是我们 MySQL 数据库实际使用的索引数据结构。那么它和 B + Tree 有什么区别呢？第一是只在叶子节点带有指向记录的指针（为什么？因为可以增加树的度）。第二就是叶子节点通过指针相连，为什么？因为可以实现范围查询。

B - Tree 每个节点既存储有指针又存储有数据，B + Tree 不存数据，就可以把空间留给更多的指针，这样就增加了树的叶子节点，相当于增加了树的度。

现在有个问题：是不是树的度越多越好呢？因为度越多，可以减少树的高度？其实不是的，下面解释这个问题。

计算机中的硬盘其实是以块进行存储的，内存基本管理单位是页（4kb）。阶是根据磁盘块的大小进行确定的，此处和操作系统管理硬件的方式有关。简单的来说，为了让操作系统更好的加载和缓存数据，我们以磁盘块的大小来确定 B + Tree 的阶，也就是一个磁盘块可以存储多少个孩子。

1.6.2.5 MySQL 索引的类型

1.普通索引（CREATE INDEX）

2.唯一索引，索引列的值必须唯一（CREATE UNIQUE INDEX）

3.多列索引，相当于 B + Tree 的 key 是由多个列的值来组成的，比如 a,b,c 三个列可以理解为组成一个（a,b,c）的元组。

4.主键索引（PRIMARY KEY），一个表只能有一个

5.全文索引（FULLTEXT INDEX），但是 InnoDB 不支持。如果大家对搜索引擎的搜索原理比较了解的话，会明白全文索引一般是通过倒排索引的形式实现的。

1.6.2.6什么时候创建索引

我们在建表的时候需要根据查询需求来创建索引。像经常用作查询条件的字段（WHERE 条件），经常用作表连接的字段，经常出现在 order by，group by 之后的字段。

1.6.2.7创建索引有哪些需要注意

1.需要非空字段 NOT NULL。因为MySQL 很难对空值做查询优化，反应到底层就是 B + Tree 无法对空值进行比较，也就无法利用索引了。

大家会发现很多互联网公司建表的规范都是要求索引字段有默认值。比如某某军规之类的，都是为了更好的利用索引结构。

2.作为索引的字段要区分度高，离散度大，尽量不要有大量相同值。

联系到 B + Tree ，很多字段一样的时候，说明 B + Tree 中很多 key 是相同的，这个时候又需要大量的比较，效率就非常的低。尤其是枚举，一共才几个值，创建什么索引，完全没有必要~

3.索引的长度不要太长（比较耗费时间）

作为索引在 B + Tree 中是以 key 的形式存在，为什么主键的索引要使用自增的 int 值呢？因为 int 值很容易比较，但是字符串就不一样了，因为字符串比较的时候不像 int 值，它需要从头到尾一个一个比。字符串类型的字段创建索引，一定要指定长度，不能太长，否则比较的时候非常耗费时间。

1.6.2.8什么时候索引会失效

在编写代码的过程中会出现一些慢查询的问题，这时候就是索引没有使用对。那么索引什么时候可能会失效呢？那就是在模糊匹配、类型隐转和最左匹配的时候。

1.以 % 开头的 LIKE 语句，模糊搜索。

因为这时候 B + Tree 的 key 是没有办法直接去比较的。

2.出现隐式类型转换（在 Python 这种动态语言查询中需要注意参数类型和 SQL 查询中的类型是不是一样的，不一样会出现慢查询）

3.没有满足最左前缀原则。

B + Tree 的数据项是复合的数据结构，比如 (name,age,sex) 的时候，B + Tree 是按照从左到右的顺序来建立搜索树的，当 (张三,20,F) 这样的数据来检索的时候，B + Tree 会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当 (20,F) 这样的没有 name 的数据来的时候，B + Tree 就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询 再比如当 (张三, F) 这样的数据来检索时，B + Tree 可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

注意：总结为一句话就是当 B + Tree 的 key 没有办法直接比较的情况下，索引会失效。

1.6.2.9什么是聚集索引和非聚集索引

聚集还是非聚集指的是 B + Tree 叶节点存的是指针还是数据记录。比如 MyISAM 索引和数据分离，使用的就是非聚集索引；InnoDB 数据文件就是索引文件，主键索引就是聚集索引。

InnoDB 中的辅助索引其实是在叶子节点中保存一个主键，查找的时候先找到主键，然后根据主键找到数据。这就是为什么在 InnoDB 中使用辅助索引要慢一些。

1.6.2.10如何排查慢查询

首先我们要明白，慢查询其实是缺少索引，索引不合理或者业务代码实现导致的。我们可以通过下面三种手段来排查：

1. show_query_log_file 开启并且查询慢查询日志。

2.通过 explain 排查索引问题。

3.调整数据修改索引；业务代码层限制不合理访问。

1.6.3 SQL 语句

1.6.3.1 重点内容

SQL 语句其实还是以各种常用连接为重点：

1.内连接（INNER JOIN）：两个表都存在匹配时，才会返回匹配行。

2.外连接（LEFT/RIGHT JOIN）：返回一个表的行，即使另一个没有匹配。

3.全连接（FULL JOIN）：只要某一个表存在匹配就返回。

1.6.3.2内连接

内连接也就是 INNER JOIN，它就是将左表和右表能够关联起来的数据连接后返回。类似于求两个表的『交集』，虽然有些不恰当，明白意思即可。如下的语句：

select * from A inner join B on a.id=b.id;

我们来两张示例表，进行后面的演示：

# A 表        
+------+------+
| id   | val  |
+------+------+
|    1 | ab   |
|    2 | a    |
+------+------+
# B 表        
+------+------+
| id   | val  |
+------+------+
|    1 | ab   |
|    3 | b    |
+------+------+

下面我们使用内连接进行查询：

mysql> select A.id as a_id,B.id as b_id,A.val as a_val,B.val as b_val from A inner join B on A.id=B.id;
+------+------+-------+-------+
| a_id | b_id | a_val | b_val |
+------+------+-------+-------+
|    1 |    1 | ab    | ab    |
+------+------+-------+-------+
1 row in set (0.01 sec)

1.6.3.3外连接

外连接包含两种，一种是左连接，一种是右连接：

1.左连接返回左表中所有记录，即使右表中没有匹配的记录

2.右连接返回右表中所有记录，即使左表中没有匹配的记录

3.没有匹配的字段会设置成 NULL

举个例子呢：

mysql> select A.id as a_id,B.id as b_id,A.val as a_val,B.val as b_val from A left join B on A.id=B.id;
+------+------+-------+-------+
| a_id | b_id | a_val | b_val |
+------+------+-------+-------+
|    1 |    1 | ab    | ab    |
|    2 | NULL | a     | NULL  |
+------+------+-------+-------+
2 rows in set (0.00 sec)

然后使用右连接试试：

mysql> select A.id as a_id,B.id as b_id,A.val as a_val,B.val as b_val from A right join B on A.id=B.id;
+------+------+-------+-------+
| a_id | b_id | a_val | b_val |
+------+------+-------+-------+
|    1 |    1 | ab    | ab    |
| NULL |    3 | NULL  | b     |
+------+------+-------+-------+
2 rows in set (0.00 sec)

1.6.4缓存及 Redis

1.6.4.1重点概览

1.为什么要使用缓存？使用场景？

2.Redis 的常用数据类型以及使用方式？

3.缓存使用问题：数据一致性问题；缓存穿透、击穿、雪崩问题。

1.6.4.2为什么使用缓存？

此处讨论内存缓存，常见的有 Redis 和 Memcached。为什么使用他们呢？

1.缓解关系数据库（常见的是 MySQL）并发访问的压力：热点数据

2.减少响应时间：内存 IO 速度比磁盘快

3.提升吞吐量：Redis 等内存数据库单击就可以支撑很大并发

从操作时间上来看，我们打开一个网站需要几秒时间，在数据库中查询一条有索引的记录需要的是十几毫秒，从硬盘、固态上读取数据也是毫秒级别，但是从内存上读数据的话就是微秒级别了。因此从操作时间看，完胜。

1.6.4.3 Redis 和 Memcached 主要区别

1.6.4.4 Redis 常用数据类型和适用场景

1.String（字符串）：用来实现简单的 KV 键值对存储，比如计数器

2.List（链表）：实现双向链表，比如用户的关注，粉丝列表

3.Hash（哈希表）：用来存储彼此相关信息的键值对

4.Set（集合）：存储不重复元素，比如用户的关注者

5.Sorted Set（有序集合）：实时信息排行榜经常使用

1.6.4.5 Redis 内置实现

Redis 底层是 C 语言进行编写的，我们需要了解 Redis 各种类型的底层实现方式。

1.String：整数或者 sds（Simple Dynamic String 这是 Redis 自己实现的字符串类型）

2.List：ziplist 或者 double linked list

ziplist：通过一个连续的内存块实现 list 结构，其中的每个 entry 节点头部保存前后节点长度信息，实现双向链表功能。数据量比较小的时候节省内存，但是数据量大的时候还需要使用双端链表了。

3.Hash：ziplist 或者 hashtable

4.Set：intset 或者 hashtable

5.SortedSet：skiplist 跳跃表

深入了解的话请参考：《Redis 设计与实现》

各种结构具体实现也可以看一下这篇文章『python技术面试题(九)』

1.6.4.6 Redis 实现的跳跃表是什么结构

Sorted Set 为了简化实现，使用了 skiplist 而不是平衡树实现。我们通过上图来了解一下跳跃表的原理：

可以看到它有三层。比如我们要查找 7 这个元素，先从第 1 层看到在 4 的右边，然后再看第 2 层， 7 在 6 的 右边，然后再看第三层就找到了。

过程虽然简单，但是实现起来相当复杂。

1.6.4.7 Redis 的持久化方式

详细的过程可以看这篇文章『python技术面试题(四)--redis持久化』

1.快照方式：把数据快照放在磁盘二进制文件中，dump.rdb

快照的实现方式是指定时间间隔把 Redis 数据库状态保存到一个压缩的二进制文件中，这个文件可以恢复状态。但是有个缺点，就是某个时间段内宕机之后，就会丢失很多数据。

2.AOP（Append Only File）：每一个写命令追加到 appendonly.aof 中

可以通过修改 Redis 配置实现。

1.6.4.8什么是 Redis 事务

和 MySQL 的事务有什么不同？

1.将多个请求打包，一次性、按序执行多个命令的机制

一次性指的是 Redis 执行命令的期间不会去执行其他客户端的请求

2.Redis 通过 MULTI ，EXEC ，WATCH 等命令实现事务功能

3.Python redis-py pipeline=conn.pipeline(transaction=True)

注意：虽然是原子性的操作，但是此处是不支持回滚的。

1.6.4.9 Redis 如何实现分布式锁

1.使用 setnx 实现加锁，可以通过 expire 添加超时的时间

2.锁的 value 值可以使用一个随机的 UUID 或者特定的命名

3.释放锁额时候，通过 UUID 判断是否是该锁，是则执行 delete 释放锁。

网上有超多版本去实现，以及现在比较流行的 Redlock

简单的说一下实现思路：一个线程在需要设置锁的时候，我们就设置一个键值对，此时表示有线程持有锁，释放锁的时候我们就把它删除。当其他线程去获取的时候，发现有这个键值对，就会进行重试或者等待。我们通过 Redis ，让不同的机器可以访问，这样就实现了分布式。

1.6.4.10使用缓存的模式

1.Cache Aside：同时更新缓存和数据库

2.Read/Write Through：先更新缓存，缓存负责同步更新数据库

3.Write Behind Caching：先更新缓存，缓存定期异步更新数据库

大部分使用的第一种方式，因为其他的比较麻烦。但是它也不完美，就是有数据库和缓存之间的数据一致性问题。先更新数据库后更新缓存，并发写操作可能导致缓存读取的是脏数据。一般都是先更新数据库，然后删除缓存，下次读取数据没有缓存的时候，再去重建缓存。

1.6.4.11如何解决缓存穿透问题

首先看一下什么是缓存穿透，就是大量查询不到数据的请求落到后端数据库，数据库压力增大。它的原因就是大量缓存查不到，就去数据库取，但是数据库也没有要查的数据。举个简单的例子：很多无脑爬虫通过自增 id 的方式爬取网站，但是我们很多 id 不是自增的，网站查不到相关 id 的数据。

解决方式也比较简单，对于没查到返回为 None 的数据也缓存。插入数据的时候删除响应缓存，或者设置较短的超时时间。

1.6.4.12如何解决缓存击穿问题

缓存击穿指的是某些非常热点的数据 key 过期，大量请求打到后端数据库。我们举个例子，像微博，访问量超大，一般都是通过多级缓存来抗住流量。大家经常会看到一种现象，某些明星一离婚或者一出轨，微博就挂了。

一般都是由于热点数据 key 失效导致大量请求打到数据库增加数据库压力。解决办法有下面两种：

1.分布式锁：获取锁的线程从数据库拉数据更新缓存，其他线程等待

2.异步后台更新：后台任务针对过期的 key 自动刷新

如果想让数据不过期，但是又不想出现一些脏数据，我们可以使用第 2 种方式。

1.6.4.13如何解决缓存雪崩问题

缓存雪崩就是缓存不可用或者大量缓存 key 同时失效，大量请求直接打到数据库。解决办法：

1.多级缓存：不同级别的 key 设置不同的超时时间

2.随机超时：key 的超时时间随机设置，防止同时超时

3.架构层：提升系统可用性。监控、报警完善

优质文章推荐:

redis操作命令总结

MySQL相关操作

SQL查询语句

前端中那些让你头疼的英文单词

Flask框架重点知识总结回顾

团队开发注意事项

浅谈密码加密

Django框架中的英文单词

Django中数据库的相关操作

DRF框架中的英文单词

DRF框架

Django相关知识点回顾

python技术面试题-腾讯