MySQL

参考：
https://github.com/CyC2018/CS-Notes/blob/master/notes/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F%E5%8E%9F%E7%90%86.md
https://blog.csdn.net/adudeboke/article/details/81433391

事务

一、定义

满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

二、ACID特性

1. 原子性：事务开始后所有操作，要么全部做完，要么全部不做，不能停留在中间环节。一个事务是一个不可分割的整体
2. 一致性：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。其实一致性也是因为原子型的一种表现
3. 隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其它事务是不可见的。
4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况。

三、并发一致性问题

在并发环境下，事务的隔离性很难保证，会出现很多一致性问题

1.丢失修改

事务T1修改了数据，接着事务T2也修改了，那么B的修改覆盖了A的修改

2.脏读（读脏数据）

T1修改了一个数据，T2随后读取数据，然后T1回滚，那么T2读取到的就是脏数据（脏数据就是数据没用可以丢掉）

3.不可重复读

因为需要T2多次读取同一个数据，T1对数据进行修改，那么T2再次读取数据，此时读取的结果就不一样了

4.幻读

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

5.小结

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

四、封锁

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。
加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小（即锁会更多），系统开销就越大。
在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

1.读写锁

• 互斥锁 称为X锁，写锁，一个事务对数据对象加了X锁，可对其进行读和写，加锁期间其他事务不能对A加任何锁
• 共享锁，S锁，读锁，加了S锁，只能进行读取操作，不能进行更新操作，加锁期间其他事务也可以加S锁，但不能X锁

2.意向锁

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。
因此引入意向锁，可对表进行行级的加锁，粒度更小

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

• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。
  通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。
  

五、事务隔离级别

未提交读（READ UNCOMMITTED）
事务中的修改，即使没有提交，对其它事务也是可见的。
提交读（READ COMMITTED）
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
可重复读（REPEATABLE READ）
保证在同一个事务中多次读取同一数据的结果是一样的。
可串行化（SERIALIZABLE）
强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。
该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

mysql默认的事务隔离级别为repeatable-read

六、多版本控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

1.基本思想

加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。但是读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。
快照的实现原理（Copy on Write 技术）
参考：https://www.cnblogs.com/qcloud1001/p/9322321.html

七、范式

1.函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。
以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

不符合范式的关系，会产生很多异常，主要有以下四种异常：

• 冗余数据：例如 学生-2 出现了两次。
• 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
• 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

2.第一范式

属性不可分。比如属性中的电话还分成手机号码和座机号码，那么显然电话这一属性就变成可“分”的了

3.第二范式

每个非主属性完全函数依赖于键码。

可以通过分解来满足。

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。
Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

4.第三范式

索引

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
建立索引会占用磁盘空间的索引文件。
在用B+树中，索引相当于key，那么value也就是key字段对应的那一行记录的磁盘地址或内存地址

B+树原理

• 是一种多路搜索树，比起B树来它搜索效率更加稳定，因为只有叶子节点有关键词，中间节点只是起到索引作用（只存储索引）
• B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，然后数据库会把页读入到内存然后查找，
• 计算器在IO磁盘读取的时候，为了降低读取的次数（减少磁盘IO操作），默认一次会读取一个页的数据量，所以B树的节点都是存储一个页的节点，这样的查询效率才是最高的

https://www.jianshu.com/p/d90f6b028d0e
为什么mysql的索引使用B+树而不是B树呢？

• 范围查找更快，mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树的数据有一部分存在在非叶子节点上面，而且默认的B树的相邻的叶子节点之间是没有指针的，所以范围查找相对更慢。
• 降低树的高度，但是最底下一层的节点会更多，因为所有的数据都堆积在最底下一层了，用空间换速度。B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高
• 举个例子，如果一次访问需要访问4个节点，数据库设计者利用磁盘预读原理将节点的大小设计为一个页，那么读取一个节点只需一次IO操作，完成这次检索需要3次IO（根节点常驻内存），可见每个节点存放数据越多，树越矮，IO操作就少，检索效率提高，对于B树，每个子节点不仅包含索引元素，还包含索引对应的数据地址，那么在每个节点大小确定的情况下，存储的索引元素比B+树少，因此B+树拥有更好的性能。

索引

https://segmentfault.com/a/1190000014906118

1. 唯一索引：唯一索引列的值必须唯一，允许null，如果是联合索引则列值的组合必须唯一
2. 主键：是特殊的唯一索引，不允许null，
3. 普通索引：最基本的索引，可以创建表时指定，也可以修改表结构时指定，MySQL允许对>=2列及以上的列上创建索引，称为联合索引
4. 全文索引：查找的是文本中的关键字，而不是直接比对索引中的值，只有字段类型为char，varchar，text的字段才能设置全文索引。
5. 空间索引：用于地理数据存储

MySQL 中常见的存储引擎对索引类型的支持情况：

索引类型	InnoDB	MyISAM	Memory
PRIMARY KEY	支持	支持	支持
INDEX	支持	支持	支持
Unique	支持	支持	支持
FULLTEXT	支持	支持	不支持
SPATIAL	支持	支持	不支持

常见存储引擎支持的索引的数据结构类型：

存储引擎	允许的数据结构类型
InnoDB	B-tree
MyISAM	B-tree
Memory	B-tree，Hash(默认)

MyISAM索引实现

MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的聚集索引区分。

InnoDB 索引实现

为什么叫做聚集索引呢？从图中可以看出叶节点的data域存储了完整的数据记录，而MyISAM索引文件和数据文件是分离的，索引文件仅仅保存数据记录的地址，而InnoDB表数据文件本身就是按B+树组织的这一种数据结构保存。

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

索引操作

查看：
show index from table_name;
创建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
例如：
CREATE INDEX order_customerId ON orders (userId);

删除：
drop index index_name on table_name;
alter table table_name drop index index_name;

索引使用策略

1. 最左前缀原理：MySQL对联合索引很敏感，例如建立了（a，b，c，d）顺序的索引，假如执行where查询语句a=1 and b = 2 and c > 3 and d = 4那么遇到范围查找条件（>,<,between,like）就会停止索引匹配，也就是索引失效。如果中间某个条件未提供例如a=1 and b = 2 and d = 4那么由于c未提供，所以d字段也没办法使用索引。
2. 如何选择列作为索引：选择区分度高的列作为索引，区分度公式为count(distinct col)/count(*)唯一键的区分度为1
3. 更新非常频繁的字段不适合作为索引，毕竟维护索引需要空间时间成本
4. 尽量扩展索引而不是新建索引，比如表已经有a的索引，现在要加（a，b）的索引，那么只需要修改原来的a索引

总结索引优点

• 索引通常小于实际数据行的大小，可减少数据访问量
• 一些存储引擎例如MyISAM将索引缓存在内存，数据在磁盘，只访问索引可以不使用系统调用
• 对于InnoDB，如果辅助索引能够覆盖查询就不需要访问主索引。
• 对于索引底层数据结构是B+树的，可以将随机IO变为顺序IO，因为B+树索引是有序的，会将相邻的数据存储在一起。

聚集索引和非聚集索引

https://www.jianshu.com/p/fa8192853184
通俗点讲
聚集索引：将数据存储与索引放到了一块，找到索引也就是找到数据了
非聚集索引：将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行

聚集索引具有唯一性，因此一个表只有一个聚集索引。
聚集索引默认是主键，但有时主键只是无意义的非空增长字段，由于InnoDB只是聚集在同一页面的记录，这种非空增长字段不在同一页面中，那么相邻键值页面可能相距甚远，我们最好可以用id，这样B+树也可以排序好，或者可以用这个表最常用的查询条件来选择作为聚集索引，最终目的就是在相同结果集情况下，尽可能减少逻辑IO操作。
主键通常建议使用自增id？？？聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

看上去聚集索引好像效率比较低因为要查2次，
但是呢，

• 由于行数据和叶子节点存储在一起，同一页的其他数据会因为查询某一行数据而被加载进来，下次访问会直接在内存命中
• 辅助索引是采用主键作为“指针”，而不是地址值，那么当出现行移动或者数据页分裂时，辅助索引维护工作会减少。不过主键会占用更多的空间，但是这就是典型的用空间换时间。

也有劣势

• 插入新行或者主键被更新导致分页，需要维护索引
• 如果采用UUID（随机ID）作为主键，那么数据存储很稀疏，可能查找数据被全表扫描还慢

查询性能优化

1. 用explain命令可以查看sql语句的执行情况
2. 减少使用select *、使用limit以减少请求的数据量
3. 开启数据库缓存，特别是需要经常被查询的语句
4. 建立索引，如果能根据实际情况建立出覆盖索引是更好的，就无须回表查询
5. 选择合适的存储引擎，innodb支持行锁其他只支持表锁，myisma不支持事务，对事务完整性要求低的可以使用，innodb支持事务，外键，对数据一致性要求比较高的可以使用
6. 表结构优化

• 垂直拆分：把主键和一些常用的字段放到一个表中，把主键和其他的字段放到另一个表中。
• 水平拆分:根据某一列的值把数据放到多个独立的表中，比如历史数据放到另一张表里。减少大多数查询读取的数据量，降低索引层数，提高查询速度。

存储引擎

InnoDB

MySQL默认的事务型存储引擎，默认隔离级别是可重复读，通过MVCC+Next-key Locking防止幻影读
主索引是聚集索引，对查询性能有很大提升
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

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

不支持事务。

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