> For the complete documentation index, see [llms.txt](https://clloud.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://clloud.gitbook.io/notes/shu-ju-ku.md). # 数据库 ## 一、数据库系统架构 [数据库系统架构](http://coding-geek.com/how-databases-work/) ![image-20200514195604656](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/TpgeFYvxq6Fzfv7O.png) ## 二、索引 索引可以提高数据查询效率。 ### 1. 索引的数据结构 #### B+树索引 **B+树与B树的区别** * B+树非叶子节点的子树指针与关键字个数相同 * B+树的非叶子节点仅用来索引，数据都保存在叶子节点中 * B+树所有叶子节点均有一个链指针指向下一个叶子节点 **B+树更适合用来做存储索引** * B+树的磁盘读写代价更低 * B+树的查询效率更稳定 * B+树更有利于对数据库的扫描 ![](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/UZ9CeVpnml04aJT9aMK7wjuX2lKY1I81.png) ![](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/j3J94g2K46BrJXcVTQLWf7g0xlWaPVxY.png) #### 哈希索引 哈希索引能以 O(1) 时间进行查找，但是失去了有序性： * 无法用于排序与分组 * 只支持精确查找（`=`, `IN`, `<>`），无法用于部分查找和范围查找 ### 2. 创建索引的原则 #### 什么样的表需要创建索引 * 对于非常小的表，大部分情况下简单的全表扫描比建立索引更高效 * 对于中到大型的表，索引非常有效 * 对于特大型的表，由于数据变更需要维护索引，维护索引的代价会非常高 #### 选择哪些键作为索引 * 选择重复值少的键作为索引，如主键索引和唯一键索引；如果重复值较多，可以设置联合索引 * 为经常需要排序、分组和联合操作的字段建立索引 * 为 BLOB、TEXT 和 VARCHAR 类型的字段建立前缀索引 ### 3. MySQL中的索引 #### 密集索引和稀疏索引的区别 * 密集索引文件中的每个搜索码值都对应一个索引值，其叶子节点保存不仅仅是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引 * 稀疏索引文件只为索引码的某些值建立索引项，其叶子节点仅保存了键位信息以及该行数据的地址或者主键 ![image-20200428080818170](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/3ifDXTkGj2pzV5FMMAPKw9fUWtcJzjlf.png) #### MyISAM与InnoDB索引方面的区别 MySQL主流的存储引擎有两种，一种是`MyISAM`,另外一种是`InnoDB`，前者不管是主键索引、唯一键索引或者普通索引，其索引均属于稀疏索引，而`InnoDB`必须有且只有一个密集索引: * 若一个主键被定义，该主键则作为密集索引 * 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引 * 若不满足以上条件，`InnoDB`内部会生成一个隐藏主键(密集索引) * 非主键索引存储相关键位和其对应的主键值，包含两次查找 ![image-20200428081917886](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/AdfH0ubhECG91qfO8u7unjj2XqeuH2w4.png) #### 回表、索引覆盖、索引下推 MySQL的`InnoDB`引擎使用辅助键索引进行查询时，如果需要查询的列没有被该索引完全覆盖，那么就需要先通过该辅助键索引获取主键，再根据主键去取数据，这个过程称为**回表**。 回表需要两次查询，为了提高查询效率，可以对被查询的字段建立联合索引，这样只需要在辅助键索引树上就能获得所需的所有列的数据，无需回表，这个称为**索引覆盖**。 MySQL 5.6引入了**索引下推**技术，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录。 例如：数据表`user(id, name, sex, age)`有单列索引`sex`，执行以下查询语句时： ```sql SELECT * FROM user WHERE sex = 'female' AND age > 10; ``` 查询引擎会在辅助键索引`sex`中寻找所有的`female`，回表后再判断age是否符合条件。 而如果我们对该表建立联合索引`(sex, age)`，查询引擎可以在辅助键索引树中判断age是否符合条件，过滤掉不合格的记录，减少了回表次数。 #### 联合索引最左匹配原则 * MySQL会一直向右匹配，遇到范围查询(>、<、between、like)就停止匹配。例如建立(a, b, c, d)顺序的索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4, d是用不到索引的 * \= 和 in 可以乱序。例如建立(a, b, c)索引，查询条件a = 1 and b = 2 and c = 3 可以以任意顺序排列，MySQL的查询优化器会把查询条件优化成索引可以识别的形式 ### 4. 查询性能优化 #### 使用 explain 进行分析 [扩展阅读：MySQL 性能优化神器 Explain 使用分析](https://segmentfault.com/a/1190000008131735) 开启慢查询日志定位慢查询SQL，然后使用explain 用来分析 SELECT 查询语句，比较重要的字段有： * type 不同的 type 类型的性能关系如下: `ALL` < `index` < `range ~ index_merge` < `ref` < `eq_ref` < `const` < `system` `ALL` 类型是全表扫描， 因此在相同的查询条件下，它是速度最慢的 `index` 类型的查询只扫描了所有的索引，比`ALL`类型的稍快 * key : 使用的索引 * rows : 扫描的行数，原则上越少越好 #### 优化数据访问 * 只返回必要的列：最好不要使用 SELECT \* 语句 * 只返回必要的行：使用 LIMIT 语句来限制返回的数据 * 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的 ## 三、事务 事务指的是满足 ACID 特性的一组操作。 ### 1. 事务的四大特性 * 原子性（Atomicity） 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。 * 一致性（Consistency） 数据库在事务执行前后都保持一致性状态。 * 隔离性（Isolation） 一个事务所做的修改在最终提交以前，对其它事务是不可见的。 * 持久性（Durability） 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。 ### 2. 日志 [Undo Log、Redo Log、Bin log区别与作用](https://blog.csdn.net/u010002184/article/details/88526708) #### 回滚日志 (Undo Log) 保证事务的**原子性**。记录事务所执行的修改操作，事务执行失败时可以用于回滚。在 InnoDB 引擎中，还用Undo Log来实现 MVCC. #### 重做日志 (Redo Log) 确保事务的**持久性**。记录的是数据页的物理修改，数据库崩溃后可以用来恢复提交后的物理数据页，只能恢复到最后一次提交的位置。 #### 二进制日志 (Binary Log) * 用于数据库的基于时间点的还原 * 用于复制，在主从复制中，从库利用主库上的Binlog进行重放，实现主从同步 ### 3. 事务的隔离级别 #### 未提交读 事务中的修改，即使没有提交，对其它事务也是可见的。 #### 提交读 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。 #### 可重复读 保证在同一个事务中多次读取同一数据的结果是一样的。 #### 可串行化 强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。 #### 并发访问问题 **1. 更新丢失** 指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。 **2. 脏读** 指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。 **3. 不可重复读** 指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。 **4. 幻读** 幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。 #### 各隔离级别可以避免的并发访问问题 | 事务隔离级别 | 更新丢失 | 脏读 | 不可重复读 | 幻读 | | :-----: | :--: | :-: | :---: | :-: | | 未提交读 RU | √ | X | X | X | | 提交读 RC | √ | √ | X | X | | 可重复读 RR | √ | √ | √ | X | | 可串行化 S | √ | √ | √ | √ | ## 四、锁 ### 1. 锁的类型 * 按粒度区分：表级锁、行级锁 * 按级别区分：共享锁（读锁）、排它锁（写锁） * 按使用方式区分：乐观锁、悲观锁 ### 2. MyISAM与InnoDB锁方面的区别 * `MyISAM`默认是表级锁，不支持行级锁 * `InnoDB`默认是行级锁，也支持表级锁 #### 使用场景 | `MyISAM` | `InnoDB` | | ------------------- | --------------- | | 对数据增删改查的频率不高，查询非常频繁 | 数据增删改查相当频繁 | | 不需要支持事务 | 可靠性要求比较高，要求支持事务 | | 会频繁执行全表count语句 | | ### 3. 当前读和快照读 * 当前读 加了锁的增删改查语句，读取的是记录的最新版本，且读取后其他并发事务不能修改当前记录 `UPDATE`, `DELETE`, `INSERT` `SELECT .. LOCK IN SHARE MODE`（共享锁）`SELECT ... FOR UPDATE`(排它锁) * 快照读 不加锁的非阻塞读 `SELECT` 在非Serializable隔离级别下，快照读的实现是基于MVCC，可以避免加锁操作，提高并发性能 ### 4. InnoDB RC RR级别下如何实现快照读 读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系。 [多版本并发控制](https://cyc2018.github.io/CS-Notes/#/notes/数据库系统原理?id=五、多版本并发控制) ### 5. InnoDB RR级别下如何避免幻读 使用`Next-Key Lock`，即`Record Lock` + `Gap Lock`解决幻读问题。`Next-Key Lock`锁定一个前开后闭区间，不仅锁定一个记录上的索引，也锁定索引之间的间隙。 #### Record Lock 锁定一个记录上的索引，而不是记录本身 #### Gap Lock * 如果WHERE条件全部命中，只会加Record Lock * 如果WHERE条件部分命中或者全不命中，会加Gap Lock ## 五、关系数据库设计 ### 1. 三大范式 * 第一范式：属性不可分 * 第二范式：每个非主属性完全函数依赖于键码（要有主键） * 第三范式：非主属性不传递函数依赖于键码（消除传递依赖） ### 2. 数据完整性 * 实体完整性：表的每一行在表中是惟一的实体 * 域完整性：表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定 * 参照完整性：两个表的主关键字和外关键字的数据应一致 * 用户自定义完整性 ## 六、数据分片 [扩展阅读：How Sharding Works](https://medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6) ### 1. 水平分割 水平切分是将同一个表中的记录拆分到多个结构相同的表中。 它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。 **水平分割策略** * 哈希取模：hash(key) % N * 范围：可以是 ID 范围也可以是时间范围 * 映射表：使用单独的一个数据库来存储映射关系 ![image-20200502211548549](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/8jIC0ISdyAGVfYXe.png) ### 2. 垂直分割 垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。 在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。 ![image-20200502211916796](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/zgdpNQSLsrAFL5x3.png) ## 七、主从复制 主要涉及三个线程：Binlog 线程、I/O 线程和 SQL 线程。 * **Binlog 线程** ：负责将主服务器上的数据更改写入二进制日志 (Binlog) * **I/O 线程** ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志 (Relay log) * **SQL 线程** ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放 (Replay) ![image-20200514202932957](https://github.com/Clloud/Notes/tree/b085adfa6214a665114ad10c2bf8bd4a4052a6ea/images/K0PzEemDdnMvZBS4.png) ## 八、读写分离 MySQL读写分离有两种实现方式： * **基于程序代码实现** 在代码中根据SQL语句类别进行路由分类，在进行写操作是选择写的数据源，读操作时选择读的数据源。这类方法目前在生产环境中应用最广泛，性能较好。 [SpringBoot + MyBatis + MySQL实现读写分离](https://www.cnblogs.com/wuyoucao/p/10965903.html) * **基于中间代理实现** 代理位于客户端和MySQL服务器之间，代理服务器接到客户端的请求后通过判断后转发到相应的后端数据库。常用的代理中间件有MySQL Router、MyCat等。 ## 九、SQL [SQL教程](https://www.w3school.com.cn/sql/index.asp) [SQL在线练习](https://sqlzoo.net/)