数据库系统知识点集合

数据库常用的存储引擎

InnoDB和MyISAM

InnoDB拥有行级别的锁定机制但是MyISAM只能锁一整张表；
InnoDB拥有更好的错误恢复机制；
InnoDB实现了事务、外键和约束，但是MyISAM没有；
在InnoDB中数据和索引是集中存储的而在MyISAM中数据和索引是分开存储的（索引结构的叶子节点指向了数据的对应行），即InnoDB中采用了聚簇索引机制（将数据存储与索引放到了一块，找到索引也就找到了数据）。myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

数据库的三大范式

第一范式：确保每列保持原子性，每个字段都是不可再分的原子值；

第二范式：确保表中的每列都和主键相关。非prime列不能和candidate key中任何一个子集合相关。

↑它违反了第二范式，因为Manufacturer country是non-prime的，但是它跟{Manufacturer}相关，但不和{Manufacturer, Model}整个相关。

第三范式：确保每列都和主键列直接相关而不是间接相关，每个非关键字列都独立于其他非关键字列，并依赖于关键字，第三范式指数据库中不能存在传递函数依赖关系

↑违反了第三范式。因为非候选键winner’s data of birth与winner相关，winner和tournament以及year相关，形成了间接相关，应该对表进行拆分。任何非主属性不依赖于其它非主属性。

数据库的存储数据类型

整数：TINYINT SMALLINT MEDIUMINT INT BIGINT分别占用8、16、24、32、64位存储空间。值得注意的是，INT(10)中的10只是表示显示字符的个数，并无实际意义。
浮点数：FLOAT、DOUBLE及DECIMAL为浮点数类型，DECIMAL是利用字符串进行处理的，能存储精确的小数。相比于FLOAT和DOUBLE，DECIMAL的效率更低些。FLOAT、DOUBLE及DECIMAL都可以指定列宽，例如FLOAT(5,2)表示一共5位，两位存储小数部分，三位存储整数部分。
字符串：CHAR & VARCHAR。前者定长，后者变长。相对于定长的字符串而言，变长的字符串能够更加节省空间。对于经常变更的数据使用CHAR更好，因为CHAR不容易产生碎片。避免使用BLOB或TEXT，因为查询时候会使用临时表，造成严重的性能开销。
日期类型：
比较常用的有year、time、date、datetime、timestamp等，datetime保存从1000年到9999年的时间，精度位秒，使用8字节的存储空间，与时区无关。timestamp和UNIX的时间戳相同，保存从1970年1月1日午夜到2038年的时间，精度到秒，使用四个字节的存储空间，并且与时区相关。

数据库索引机制

为加快数据检索速度我们可以对表中一个列或多个列建立search index，如B+树（所有数据都在叶节点）或者哈希表（redis等nosql好像是用的哈希，哈希索引一般多用于精确的等值查找）。对于经常查询但是不怎么变化的数据我们可以采用索引的方式。但存在缺点是占用物理空间，对索引的维护也是一个overhead。

聚簇索引：以主键作为B+树索引键值，叶子节点保存完整的数据记录。如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。**聚簇索引只能有一个。**可以建立辅助索引，在 B+Tree 的叶子节点中只存了索引列和主键的信息。
非聚簇索引：以非主键的列作为B+树索引，叶子节点记录了主键，然后再从聚簇索引中找到对应的数据记录。

为什么不用红黑树？第一，红黑树是二叉树，深度比B+树高，意味着磁盘I/O可能会更大一些。第二、红黑树一个节点只能存一个数据，但是B+树的节点可以存储大量数据。第三，B+树利用了磁盘预读取机制，将一个节点的大小设置成一个页，就能过通过一次IO完全载入。计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

哈希表 vs B+树：

哈希表不支持排序，不支持范围查询；
哈希表不支持模糊查询；
哈希表中存在哈希冲突，可能会造成性能的不稳定。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+ 树索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如：快速的哈希查找。

MySQL的索引失效情况：

条件中有or（要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）
多列索引时不满足最左前缀原则：mysql 建立多列索引（联合索引）有最左前缀的原则，即最左优先
如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引；如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引；
like查询是以%开头
如果查询列类型是字符串，但在条件中未将数据使用引号引用起来，即字符串和数字相比较
mysql优化器认为用全表扫描要比使用索引快

事务的特性

原子性：事务包含的操作要么全部成功要么全部失败并回滚；
一致性：事务必须保证某种约束一直是有效的；
隔离性：事物之间是互不干扰的；
永久性：事务一旦提交，数据库中的数据改变就是永久的了。

ACID。Atomicity, Consistency, Isolativity, Durability.

脏读、幻读、不可重复读

脏读：指的是读到了其他事务还没有提交的数据；
不可重复读：指的是一个事务中前后读取数据不一致，读到了其他事物已提交的数据，主要是针对update操作；
幻读：指的是一个事务中前后读取的数据总量不一致，例如前后行数不同，针对insert操作。

事务的隔离级别

READ_UNCOMMITTED：读未提交，即能够读取到没有被提交的数据，会造成上述三种错误读；
READ_COMMITTED：读已提交，即能够读到那些已经被提交的数据，能够解决脏读；
REPEATABLE_READ：重复读取，在数据读取出来之后加锁，明确数据读取出来就是为了更新所用。解决了脏读、不可重复读。
SERIALIZABLE：**串行化。没有任何并发性。**所有的问题都能够解决了。

锁机制

共享锁：被加锁的对象可以被持有锁的事务读取，但是不能够修改。其他事务也可以继续加锁。shared_ptr
排它锁：被加锁的对象只能被持有锁的事务读取和修改，其他事物无法在该对象上加其他锁，也不能读写。unique_ptr

基于锁机制的封锁协议

一级封锁协议：事务在对需要修改的数据上面（就是在发生修改的瞬间）对其加共享锁（其他事务不能更改，但是可以读取-导致“脏读”），直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK)。
二级封锁协议：事务在对需要更新的数据上面（就是在发生修改的瞬间）对其加排它锁（无法被其他事务读写），避免了脏读。事务对当前被读取的数据上面加共享锁（当读到时加上共享锁），一旦读完该行，立即释放该行的共享锁。
三级封锁协议：事务在对需要更新的数据上面（就是在发生修改的瞬间）对其加排它锁（无法被其他事务读写）。事务在读取数据的瞬间必须先对其加上一把共享锁，但是事务结束的时候才释放，保证了可重复读。
四级封锁协议：对事务涉及的表直接加表锁。

数据库中水平切分和垂直切分的区别

水平切分：水平切分是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，水平切分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。
垂直切分：从逻辑上将数据库分割。垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。例如：将原来的电商数据库垂直切分成商品数据库、用户数据库等。

数据库的主从复制

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。

主从复制中主要涉及到三个线程：binlog、IO线程和SQL线程。

binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。
SQL 线程：负责读取重放日志并重放其中的 SQL 语句。

假如一个服务器开放Ｎ个连接给客户端，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务器上执行的时间稍长或者由于某个 SQL 要进行锁表就会导致主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

读写分离：提高并发性，降低主服务器的读写竞争可能，防止单点失效。读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

错误恢复

采用undo log和redo log。undo记录修改前的数值，redo记录修改后的数值，先进行一次redo，再进行一次undo。为了加快速度可以采用checkpoint机制，保证checkpoint之前的事务都已经被正确commit掉了。checkpoint错误恢复机制是反向扫end to start of checkpoint然后undo没有commit的事务，随后把夹杂在checkpoint之间的事务给undo了，最后把start of checkpoint to end的事务给redo了。

意向锁、共享锁、排它锁

意向锁是在存在行锁场景下的表锁快速失败机制。A锁了一行但是B想锁表的话，会导致锁定效率低，意向锁可以提前告知B的表锁不能锁。IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

此处XS是表级别的锁。

排他锁可以使用命令SELECT ... FOR UPDATE对几行的数据进行加锁。

乐观锁和悲观锁

悲观锁：总是假设最坏的情况，每次取数据的时候都认为别人会修改这个数据，所以总是对数据上锁。
乐观锁：总是假设事务读取数据的时候没有干扰，不去上锁。在更新的时候会判断一下是否有人更新了这个数据。乐观锁协议一般使用的是时间戳、版本号等机制。如果发现事务发现执行期间版本号发生了改变，那么久拒绝本次修改。
- 如果业务要求比较高的响应速度就不要使用悲观锁；
- 如果冲突频率不高，建议采用乐观锁；
- 如果重试代价很大使用悲观锁。