数据库原理

SQL语言的分类

1. DQL 数据查询语言

   DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。

2. DML 数据操纵语言

   DML主要有三种形式：INSERT，UPDATE，DELETE。

3. DDL 数据定义语言

   用来创建数据库中的各种对象—–表、视图、索引、同义词、聚簇等，如：
   CREATE TABLE/VIEW/INDEX/SYN/CLUSTER。

4. DCL 数据控制语言

   DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

ACID

1. Atomicity 原子性

   事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

   回滚可以用undo log实现：undo log记录事务所执行的修改操作，在回滚时反向执行即可。

2. Consistency 一致性

   数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

3. Isolation 隔离性

   一个事务所做的修改在最终提交之前对其它事务不可见。

4. Durability 持久性

   一旦事务提交，其所做的修改将会永远保存到数据库中，即使系统崩溃，事务的执行结果也不能丢失。

   系统崩溃可以用redo log恢复：与undo log记录数据的逻辑修改不同，redo log记录的是数据页的物理修改。

MySQL默认采用AUTOCOMMIT，即如果不显示使用Start Transaction来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

并发一致性问题

并发修改，读脏数据，不可重复读，幻影读

封锁

MySQL中提供了两种锁粒度：行级锁，表级锁。

尽量只锁定需要修改的那部分数据，而不是所有资源，锁定的数据量越少，系统的并发程度就越高。但因为获取锁，释放锁，检查锁状态都会增加系统开销，所以封锁粒度越小，系统开销越大。

在选择封锁粒度时，需要在锁开销和并发度之间做一个权衡。

封锁类型

读写锁

Exclusive 互斥锁，X锁，又称写锁。

Shared 共享锁，S锁，又称读锁。

• 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁；
• 一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁；

意向锁

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁；

隔离级别

1. READ UNCOMMITED 未提交读

   事务中的修改，即使没有提交，对其他事务也是可见的。

2. READ COMMITED 提交读

   一个事务只能读取已经提交的事务所做的修改。

3. REPEATABLE READ 可重复读

   保证在同一个事务中多次读取同一数据的结果是一样的。

4. SERIALIZABLE 可串行化

   强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

隔离级别能解决的并发一致性问题

	脏读	不可重复读	幻影读
未提交读
提交读	可解决
可重复读	可解决	可解决
可串行化	可解决	可解决	可解决

　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

多版本并发控制

Multi-Version Concurrency Control 是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。

写操作更新最新版本快照，而读操作去读旧版本快照，没有互斥关系，类似于CopyOnWrite。在MVCC中，DELETE，INSERT，UPDATE会为数据行新增一个版本快照。为了解决脏读和不可重复读问题，MVCC规定只能读取已经提交的快照。

版本号

系统版本号SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自增；

事务版本号TRX_ID：事务开始时的系统版本号；

1 实现原理
InnoDB的MVCC可以是通过在每行记录中保存两个隐藏的列来实现的,创建事物id，删除事物id。每开始一个新的事务，系统版本号(可以理解为事务的ID)就会自动递增，事务开始时刻的系统版本号会作为事务的ID。

Innodb的最基本行记录（row）中包含一些额外的存储信息：DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

列名	长度	备注
DATA_TRX_ID	6字节	标记了最新更新这条行记录的transaction id，每处理一个事务，事物值自动+1
DATA_ROLL_PTR	7字节	指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针
DB_ROW_ID	6字节	innodb自动产生聚集索引时，聚集索引包括这一列，否则聚集索引中不包括这个值。
DELETE BIT		位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候

2 Select
当隔离级别是REPEATABLE READ时select操作，InnoDB查询时必须保证每行数据符合两个条件：

InnoDB只查找版本号必须小于等于事务版本的数据行。这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行。

行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除。

3 实现
MVCC有下面几个特点（看起来有点乐观锁的味道）：

a、每行数据都存在一个版本。

b、每次数据更新时都更新该版本 修改时Copy出当前版本随意修改，个事务之间无干扰。

c、保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）。

Innodb的MVCC实现方式如下：

a、事务以排他锁的形式修改原始数据，

b、把修改前的数据存放于undo log，通过回滚指针与主数据关联

c、修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

索引

B+ Tree 原理

B Tree Balance tree，平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree是基于B Tree和叶子节点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能。在B+ Tree中，一个节点的key从左到右非递减排列。

查找操作：首先在根节点进行二分查找，找到一个key所在的指针，然后递归的在指针所指向的节点进行查找，直到找到叶子节点，然后在叶子节点上进行二分查找，找出key对应的data。

与红黑树对比

1）B+ Tree有更低的树高

因为红黑树的出度为2，而B+ Tree的出度一般很大，所以红黑树的树高h比B+ Tree大非常多。

2）磁盘访问效率

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。因为进行寻道的次数与树高成正比，B+ Tree相对于红黑树有更低的树高，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以B+ Tree更适合磁盘数据的读取。

3）磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

与B Tree对比

B Tree一个节点可以存储多个元素，相对于完全平衡二叉树整体树高降低，磁盘IO效率提高，而B+ Tree相比于B Tree，只是把非叶子节点冗余，为了提高范围查找的效率，并且存在指针指向下一个节点的叶子节点。

总结：MySQL选用B+ Tree作为索引，提高查询索引时的磁盘IO效率，和范围查询的效率，并且B+ Tree中的元素是有序的。

MySQL索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

B+ Tree 索引

因为B+ Tree的有序性，所以除了用于查找，还可以用于排序和分组。

是大多数MySQL存储引擎的默认索引类型。InnoDB的B+ Tree索引分为主索引和辅助索引。

主索引的叶子节点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有聚簇索引。

辅助索引的叶子节点的data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找主键值，然后再到主索引中进行查找。

Hash 索引

能以O(1)时间进行查找，但是失去了有序性，所以

• 无法用于排序和分组；
• 只支持精确查找，无法用于部分查找和范围查找；

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

适用场景：等值查询场景，就只有（key, value）的情况，例如Redis等NoSQL的中间件。

全文索引

用于查找文本中的关键词，而不是直接比较是否相等。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

查找条件使用MATCH AGAINST，而不是普通的WHERE。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引的优点

大大减少了服务器需要扫描的数据行数；

帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）；

将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）；

索引的使用条件

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

对于中到大型的表，索引就非常有效；

但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术；

存储引擎

InnoDB

是MySQL默认的事务型存储引擎，实现了四个标准的隔离级别，默认是可重复读 REPEATABLE READ，在可重复读隔离级别下，通过MVCC+Next-key Locking 可防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，对查询性能有很大提升。

MyISAM

• 设计简单，数据以紧密格式存储；

• 提供了大量特性，包括压缩表，空间数据索引等；

• 不支持事务；

• 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

比较：

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句；
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁；
• 外键：InnoDB 支持外键；
• 备份：InnoDB 支持在线热备份；
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢；
• 其它特性：MyISAM 支持压缩表和空间数据索引；

MySQL引擎对索引的支持

Storage Engine	Permissible Index Types
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	HASH, BTREE(see note in text)

但是innoDB存储引擎支持hash索引是自适应的，innoDB存储引擎会根据表的使用情况自动为表生成hash索引，不能人为干预是否在一张表中生成hash索引。

调优方案

排除缓存干扰

MySQL 8.0之前存在缓存，在执行SQL时需加上SQL NoCache，得出的才是真实的查询时间。（缓存失效的场景：对表更新时，这个表的所有缓存会被清空）

Explain

1）为什么rows和总行数不一样？

MySQL中数据的单位都是页，通过采样统计的方法，InnoDB默认选择N个数据页，统计这些页面上不同值的平均值，然后乘这个索引的页面数，就得到了rows。

2）为什么MySQL索引不一定走到最优索引？

假如走A索引要扫描100行，B索引只要20行，但是它可能走A索引，因为优化器在选择时发现走A索引没有额外的代价，而B索引需要回表。可使用force index。

覆盖索引

在建立的索引上就已经有需要的字段了，就可以避免了回表。

联合索引

需要注意索引占用的空间。

最左匹配原则

可以对现有的索引进行最大化的利用。

索引下推

在MySQL5.6引入的索引下推优化，在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

前缀索引

当需要将很长的字段作为索引时，为了节省空间，可以采用倒序或者截取字符串使区分度提高。

flush

当MySQL突然卡顿时，可能发生了flush，即redo log更新到磁盘。

更新之前，当内存数据页与磁盘数据页内容不一致时，这个页被称为脏页；

内存数据写入到磁盘后，内容和磁盘上的数据页内容一致，称为干净页；

flush的时机：

• InnoDB的redo log写满了，这时系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写；
• 系统内存不足，当需要新的内存页，而内存不够用时，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘；
• MySQL认为系统“空闲”时，只要有机会就刷一点“脏页”；
• MySQL正常关闭时，MySQL会把内存的脏页都flush到磁盘上，这样下次启动时，就可以直接从磁盘上读数据，提高启动速度。

如何控制flush的时机：

innodb_io_capacity会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试。

刷脏页时，如果旁边也是脏页，会一起刷掉。innodb_flush_neighbors=0这个参数可以不产生连带制。在MySQL 8.0中，innodb_flush_neighbors的默认值已经是0了。

索引失效的情况

• 对字段做了函数操作就不会走索引了，因为破坏了索引值的有序性，因为优化器就决定放弃走树搜索功能。

• 隐式类型转换

  select * from t where id=1

  如果id是字符型 1是数字型，此时会走全表扫描。因为MySQL底层对比较进行转换，相当于加了CAST(id as signed int)。

• 隐式字符编码转换

  如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。转换的过程相当于加了CONVERT(id USING utf8mb4)。

• where语句中索引列使用了负向查询，可能会导致索引失效

  负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。

• like通配符可能会导致索引失效

  like查询以%开头时，会导致索引失效。

各种log

bin log

记录了数据库表结构和表数据变更，比如update，delete，insert，truncate，create，它不会记录select。存储着每条变更的SQL语句（还有事务id等）。

作用：复制和恢复数据。

• 在一主多从结构中，实现从服务器与主服务器数据保持一致；
• 恢复数据库数据；

三种模式：

• Row Level 行模式

  记录每一行数据被修改的形式。（批量修改时 记录的是单条语句）

  缺点：会产生大量的日志内容。

• Statement Level（默认）

  记录每一条修改数据的SQL语句。（记录的是批量修改的SQL语句事件）

  缺点：使用了某些特定的函数或者功能时，会导致复制存在问题。

• Mixed

  MySQL会根据执行的每一条具体的SQL来区分对待日志的记录形式，在Statement和Row之间选一种。

redo log

内存写完后会写一份redo log，记载了在某页上做了什么修改。

作用：当修改时，数据写完内存但还没真正写到磁盘时，数据库挂了，此时可以根据redo log对数据进行恢复，因为redo log是顺序IO，所以写入速度很快，并且redo log记载的是物理变化（XX页做了XX修改），文件体积小，恢复速度快。

bin log和redo log 区别：

• 存储的内容：

  bin log记录的是update，delete，insert这样的SQL语句，即逻辑变化；

  redo log记录的是物理修改的内容（XX页修改了XX），即物理变化；

• 功能：

  bin log的作用是复制和恢复；

  redo log的作用是为了持久化；

• 产生：

  bin log无论MySQL引擎，都会存在；在事务提交时才记录；

  redo log是MySQL的InnoDB引擎产生的；事务开始时 记录每次的变更信息；

  MySQL通过两阶段提交保证redo log和bin log的数据是一致的：

  阶段1：InnoDB redo log写盘，InnoDB事务进入prepare状态；

  阶段2：bin log写盘，InnoDB事务进入commit状态；

假如数据库数据被删除，可以用redo log恢复数据么？

不可以，redo log存储的是物理数据的变更，如果内存数据已经刷到磁盘，那redo log的数据就无效了。所以redo log不会存储着历史所有数据的变更，文件的内容会被覆盖。

undo log

作用：回滚和MVCC

数据修改时，不仅记录了redo log，也记录了undo log，undo log存储的也是逻辑日志，例如insert一条数据，undo log会记录一条对应的delete日志；update一条数据，undo log会记录一条对应相反的update记录。

因为undo log存储着修改前的数据，相当于前一个版本，MVCC实现的是读写不堵塞，读的时候需要返回前一个版本的数据。

主从复制

主要涉及三个线程：Binlog Dump线程、I/O线程和SQL线程。

Binlog Dump线程跑在主库上，IO线程和SQL线程跑在从库上。

Binlog Dump线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中；
I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）；
SQL线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）；

过程

当在从库上启动复制时，首先创建I/O线程连接主库；

主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程；

I/O线程获取到事件数据后更新到从库的Relay Log中继日志中；

从库上的SQL线程读取Relay Log中更新的数据库事件并应用；

常见问题

MySQL的存储结构

MySQL的基本存储结构是页，各个数据页可以组成一个双向链表，而每个数据页中的记录又可以组成一个单向链表。每个数据页都会为存储在它内部的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以非主键列作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

select * from user where username = 'Tom';

没有进行任何优化的SQL语句：

1）定位到记录所在的页；（遍历双向链表）

2）从所在页内中查找相应的记录；（由于不是根据主键查询，所以需要遍历所在页的单链表）

索引的最左匹配原则

多个列组成索引（a, b, c, d），此时key也由多个列组成，索引只能用于查找key是否存在（相等），遇到范围查询（<，>，between，like左匹配）等就不能进一步匹配了，后续退化为线性查找。

因此，列的排列顺序决定了可命中索引的列数。

如有索引 (a, b, c, d) 查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a，b，c，无法命中d。因为c已经是范围查询了。

索引的缺点

创建和维护索引需要时间成本；

索引需要占用物理空间；

对表中的数据进行增删改的时候，索引需要动态维护，降低了数据的维护速度；

索引的分类

• 普通索引

  最基本的索引，没有任何限制。

  ALTER TABLE `table_name` ADD INDEX index_name (`column`)

• 唯一索引

  与普通索引类似，索引列的值必须唯一，允许有空值。

  ALTER TABLE `table_name` ADD UNIQUE [indexName] (`column`)

• 主键索引

  是一种特殊的唯一索引，不允许有空值。

  ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)

• 全文索引

  FULLTEXT索引用于全文搜索，只有InnoDB和MyISAM存储引擎支持 FULLTEXT索引，仅适用于CHAR，VARCHAR和TEXT列。

  ALTER TABLE `table_name` ADD FULLTEXT (`column`)

Redis跳表和MySQL索引

Redis使用跳表不用B+数的原因是：redis是内存数据库，而B+树纯粹是为了MySQL这种IO数据库准备的。B+树的每个节点的数量都是一个MySQL分区页的大小。

语句执行顺序

一、mySql的执行顺序

mysql执行sql的顺序从 From 开始，以下是执行的顺序流程

1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2、JOIN table2 所以先是确定表，再确定关联条件

3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6、HAVING 对分组后的记录进行聚合 产生中间表Temp5

7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6

8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

MySQL乐观锁和悲观锁

乐观锁

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。适用于读多写少的场景，提高吞吐量。

实现方式：

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

悲观锁

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

MySQL隐式和显示锁定

InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB会根据事务隔离级别在需要的时候自动加锁。

另外，InnoDB也支持通过特定的语句进行显示锁定，这些语句不属于SQL规范：

• SELECT … LOCK IN SHARE MODE
• SELECT … FOR UPDATE