MySQL笔记总结
数据库
一． MySQL引擎
数据库存储引擎是表的类型以及表在计算器上的存储方式，它最终作用于表。
1. InnoDB(具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎
)
(1) 优点
支持高并发
并发控制保证数据一致性的方式有两个；（行）锁和多版本并发控制
锁（读读并发）是为了保证一致性的；
操作数据前加锁，不允许其他并发任务操作，操作完成后释放锁。这样即可保持数据的一致性
针对一张表来说，使用行锁比使用表锁并发能力强
（行）锁：普通锁，串行执行（多个任务时，各个任务按顺序执行）；
共享锁和排他锁：多个读操作并行，而读写/写写不并行
多版本并发控制（读写并发）（mvcc multiversion concurrency control），通过读取旧版本数据来降低并发事务的锁冲突
什么时候可以看到mvcc现象
开启mvcc就会先去undo log里读数据
因此开启两个事务，一个事务查询数据，另一个事务并发修改数据，事务1查到的也是旧版本（修改前）的数据（即使没有加锁，读undo log的数据）；但加锁了可以得到最新的数据
mvcc是为了避免加锁（它没有锁），可以当成乐观锁
# 写任务：将数据克隆，按版本号区分
# 写任务操作克隆的数据，提交
# 并发读任务读取旧版本的数据，不至于阻塞
mvcc只会在read committed 和read repeatable下工作
对应到InnoDB就是redo undo 回滚段
redo log存储修改行为，在定期写到磁盘上，比一次事务提交后磁盘就立即随机读写性能高，且保证事务的acid
undo log事务未提交时，数据库将旧版本存放到undo log，若事务回滚/数据库崩溃则可利用undo log撤销未提交事务对数据库的影响。用于实现mvcc和事务的回滚--回滚段就是存储undo日志的地方
Bin log，服务器产生的日志，常用于数据恢复和数据库复制。主从架构就是用slave同步master的bin log实现的。
支持高并发的原因是快照读不加锁
快照就是回滚段里面的数据，这些数据是不会被修改的；select可以并发读取
支持事务，事务性数据库
InnoDB保证acid的方法：redo log保证持久性，undo log 保证原子性；锁和MVCC保证隔离性；保证上述三个后才能保证一致性；
自动灾难恢复 crash recovery
暂时记住，如果已经提交了一个事务但这时候数据库挂了，但是因为事务一致性，数据库挂了是可以恢复的。
InnoDB与myisam比较在什么情况下效率更高
主键索引就是InnoDB效率比较高，非主键索引则是myisam效率比较高
支持外键（但是一般也不建议用）
为什么mysql不建议用外键：首先是因为数据库需要额外维护外键，其次就是增删更新之后需要检查，额外消耗资源；最后
总结：需要事务支持，需要较高并发读取频率（默认情况等都选这个
1.1 外键：用于与另一张表的关联，用于保持数据的一致性。
1.2 主键（唯一，非空，不可重复）外键（多个，可空，可重复）和索引（可有多个唯一索引，可有一个空值，不可重复）
2. MylSAM
(1) 优点
写入速度快（尤其单线程？为什么？
它支持的不是表锁吗为什么写入速度还能快？
哦因为单线程不用锁吧，只有哪些被多线程访问的共享数据才需要加锁
然后多线程用这个就会傻了吧唧了
(2) 缺点
不支持事务安全
最大的缺陷是崩溃后无法安全恢复，因为不支持事务，InnoDB就是通过事务acid来保证一致性从而实现自动灾难恢复的。
myisam有主键索引吗
有，叶子结点存储的是数据的地址，所以跟普通索引一样
3. MEMORY（数据全部放在内存中）
适合目标数据小，访问频繁，丢失不产生实质性影响的数据。
(1) 优点
最快的响应时间
(2) 缺点
mysql守护进程崩溃时，所有的Memory数据都会丢失
生命周期短，一般为一次性的
4. 三者并发性能如何
innerDB并发性能最好
5. 三者数据存储位置
InnoDB在磁盘
Memory在内存
6. 三者是否支持事务
只有innerDB支持事务
7. 为什么不用Memory/MylSAM
Memory：无法保证数据安全性，且不能建立太大的表，不支持事务
MylSAM：不支持事务，不支持外键，最小锁粒度是表锁
二． 主键，外键，索引
0. 关系型数据库和非关系型数据库
(1) 前者容易理解，使用和维护方便，后者可以根据自己需求添加需要的字段
(2) 前者效率较低，性能欠佳；后者不适合持久存储
1. 主键
(1) 主键primary key，为了方便更快查找表中记录而设置（可唯一标识某一行的属性）
(2) 唯一键，以及它和主键的区别
唯一键 unique key，所有记录字段的值不可以重复出现
相似性：都可以确保列的唯一性
不同：一个表可以有多个唯一键，但只能有一个主键
唯一键可以为空，主键不可以
(3) 一旦插入到表中，最好不要修改（为什么
主键发生变更，导致数据在磁盘上的位置发生变更，就可能会引发页分裂，产生空间碎片，导致不必要的开销
(4) 不使用任何业务相关字段作为主键 为什么
因为有业务含义断裂都有可能变更，导致主键发生变更，导致数据在磁盘上的位置发生变更，就可能会引发页分裂，产生空间碎片。是不是还有可能导致不必要的开销
(5) 主键要么为自增数据类型，要么为全局唯一标识符（GUID globle unique identifier）（最好使用BIGINT，in最大值21亿多但数据库的数据可能会更多）
（5）为什么主键可以提高查询效率（相对普通索引）
因为普通索引是非聚簇索引，主键是聚簇索引，主键索引就只需要根据主键查找一次，然后就可以在b+树的叶子结点获取数据；普通索引首先要根据（普通）索引值遍历b+树，然后得到主键所在的地址，再通过主键索引遍历一次，一共是两次。
（纯手打表述也不知道是否准确）
非聚簇索引定位到对应主键时还要多一次目标记录寻址，所以IO次数比聚簇索引更多
1.1 数据库没有主键可以吗
主键不是非要定义，如果没定义，InnoDB选择第一个不包含null的唯一索引为主键，如果没有这样的唯一索引，则内置6字节的ROWID（行号）；反正都要生成不如自己指定
是否需要主键出要取决于业务需求和数据查询效率：主键可以提高查询的效率，合理的索引也可以
PS：用来存储大量数据，并需要经常查询这个表的，建立主键可以加快查询效率从而降低服务器的负担，如无此需求，则不需要，建立反而会占用服务器的资源
1.2 主键为什么通常建议使用自增id
聚簇索引数据的物理存放顺序和索引顺序一致；如果主键不是自增，那每次插入主键的值随机，mysql为了将新纪录插到合适的地址可能需要不断调整数据的物理地址，造成不必要的开销；如果是自增，只需要一页一页的写
1.3 索引的数据结构：B+树（B是Balanced），b树就是平衡树
（1）为什么不用红黑树，平衡二叉树
不用（平衡）二叉树主要是因为每查找一次就要从磁盘读取一次，因为平衡二叉树每个结点只存储一个key value，那如果要存储海量数据则高度会很高，磁盘io次数会很多，查找数据的效率会很低
红黑树是二叉树，不用二叉树的理由在上面了。
前置条件：是磁盘速度相对内存比较慢
数据库硬件的发展（伪内存）
总结就是存储key value太少，以及树的高度太高导致io会很频繁
（2）B+树，读的数据是一块儿的数据
这个是因为数据和索引一般存储在磁盘这种外围设备，（内存容易丢失数据）。但是磁盘又慢，所以就要尽量的减少从磁盘中读取的次数
以及磁盘是按磁盘块来读取的，不是一条一条读数据的
磁盘块包括很多扇区，一个扇区只能放一个文件
mysql设计，局部性原理，用b+树可以体现，范围查找红黑树和二叉树其实更慢；不用跳表是因为和二叉树高度和跳表差不多，但跳表也可以实现局部性原理
（3）多叉树是为了降低树的高度
b+树是多叉树
（4）树有多大？
一般B+树高度为1-3层就可以满足千万级存储需求
1.4 B树和b+树
(1) B树
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
最重要：每个节点都存有索引和数据，也就是对应的key和value。
B树的检索过程相当于对范围内的每个节点的关键字做二分查找，可能还没到叶子结点检索就结束。
b树的每个结点称为页（就是磁盘块）每个结点就能存储更多的key和value，且每个结点都能拥有很多的子结点，所以高度会变低，io次数将很少
(2) B+树
不同点：内部节点不存储数据，只存储索引（key），数据都存储在叶子结点上（value）
每个叶子结点都存有相邻叶子结点都指针，叶子结点本身依关键字大小自小到大连接
任何查找都是根结点到叶子结点到过程
(3) B+树的优势
单一阶段存储的元素更多（IO）次数更少
所有查询都要找到叶子结点（性能稳定）
所有叶子结点形成了一个有序链表，方便查找
B树叶子结点和非叶子结点都会保存数据，所以非叶子结点能保存的指针数量比b+树少，那从控制变量的角度说为了增加保存数据的量就只能增加树的高度，就导致IO操作变多
2. 外键
(1) 通过某列/字段，可以将数据与另一张表关联起来，这种列成为外键
(2) 定义外键约束，关系型数据库可以保证无法插入无效的数据
(3) 降低数据库的性能
(4) 系统不涉及分库分表，并发量不高，就可以考虑外键
3. 索引
(1) 概念：快速查询和检索数据的数据结构。使用索引可以提高查询效率（数据库不需要扫描整个表就能定位到符合条件的记录）
(2) 缺点是增删插数据时需要同时修改索引
(3) 唯一索引：确保某一列的值具有唯一性（例如身份证）
3.1 索引和主键的关系
主键相当于一本书的页码，索引则相当于目录
主键一定是唯一性索引，但唯一性索引不一定是主键
一个表中可以有多个唯一性索引，但只能有一个主键
主键列不允许空值，而唯一性索引列允许空值
3.2 聚簇索引和非聚簇索引：
1. 非聚簇索引；索引的存储和数据的存储分离（第一次只能找到索引，第二次才能找到数据），B+树叶子结点存储主键值的就是非主键索引。
2. 聚簇索引：找到了索引就找到了需要的数据，B+树叶子结点直接存储数据。
使用InnoDB查找数据理论上比myisam快；或者说聚簇索引查找数据理论上比非聚簇索引快：因为对于InnoDB来说，如果使用主键索引，叶子结点可以直接取数据，myisam 则因为存储是数据地址所以会多一次目标记录寻址。
对于一颗索引树，不是只查询一次，可能是三四次，所以可能是多读三四次。。所以多出来的IO不一定是一次
如果命中了普通索引，例如where name ，那就需要查到对应的id（主键），然后才能命中主键索引并查到id那一行的数据
3.3 上面两种索引和主键的关系
聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引替代，如果没有这样的索引，则会隐式定义一个主键来作为聚簇索引
3.4 其他类型的索引
唯一索引：属性列不可以出现重复的数据，单允许数据为null；一张表可以有多个唯一索引，建立它的目的是为了数据的唯一性
普通索引：快速查询数据
二级索引：叶子结点的内容为主键的值
三． 事务
1. 某些业务要求一系列操作必须全部执行，例如：转账；如果第一条语句成功第二条失败，则必须全部撤销，不会对数据库有任何的改动
2. ACID
Atomic原子性：要么全部执行要么全部不执行
Consistency一致性：事务完成后所有数据状态一致（a多了一百块那b就要少100块）
Isolation隔离性：多个事务并发，则每个事务做出的修改必须与其他事务隔离
Durability持久性：事务完成后对数据库的修改将被持久化存储
3. 事务隔离级别
（1） read uncommitted 读未提交：隔离级别最低；在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
（2） read committed 读已提交：事务能够看到的数据都是其他事务已经提交的，不会看到任何中间性状态。但一个事务可能会遇到不可重复读（Non Repeatable Read）的问题（就是第一次查余额100第二次就200）和幻读
（3） repeatable read 可重复读（mysql innoDB默认的隔离级别）：保证同一个事物多次读取的数据是一致的，所以可以预防脏读和不可重复读，但不可以预防幻读（这名字就很清晰啦）
（4） serializable：Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
（5） 虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。
3.1 脏读，不可重复读，幻读
（1）脏读（读取了未提交数据）就是a读取了b尚未提交的数据，如果b发生错误，执行回滚，则a事务读取到的就是脏数据；对应读。
（2）不可重复读（前后多次读取发现两次内容不一致，update）事务A在执行读取操作，数据内容不一致，系统不可以读取到重复的数据，成为不可重复读。对应读后读。
（3）幻读（前后多次读取发现数据总量不一致，insert，delete）：没锁执行select..where的时候可能发生；对应读后写；
幻读是怎么解决的：（当前读）使用next-key lock，读取完成后释放锁，再允许其他事务新增数据。
（4） 丢失的修改：第一个事务修改了数据，然后第二个也修改了，那么第一个事务的修改就丢失了
（5）不可重复读是读取到了其他事务更改的数据，针对“update”； 幻读是读取了其他事务新增的数据，针对insert和delete
隔离级别越低，事务所请求的锁就越少。
四． 锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制
五． 分库分表分片分区
1． Sharding 是把数据库横向扩展（Scale Out）到多个物理节点上的一种有效的方式，其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。Shard这个词的意思是“碎片”。如果将一个数据库当作一块大玻璃，将这块玻璃打碎，那么每一小块都称为数据库的碎片（DatabaseShard）。将整个数据库打碎的过程就叫做sharding，可以翻译为分片
1.1 分片分为垂直切分和水平切分，前者解决io之争，后者解决数据量增长出现的压力
2． 分表
把一张表分成多个小表
3． 分区
一张表的数据分成n个区块
4． 分表和分区的区别
4.1 mysql的分表完成后，每个小表都是完整的一张表
4.2 分表后总表只是一个外壳，数据存取都在分表；分区后还是一张表
4.3 分表后单表的并发能力提高；分区后突破了IO瓶颈
5． 分库：对数据库进行拆分，提高数据库写入能力
6． 存在的问题：
（1） 事务管理出现困难
（2） Join问题：无法join不同分库的表，也无法join分表力度不同的表
（3） 额外的管理负担
六． 主从同步（单独作为一个模块，和锁一样）
https://time.geekbang.org/column/article/215383
1. 目的是故障切换和读写分离（写在master读在slave）
2. MySQL 主库在收到客户端提交事务的请求之后，会先写入 Binlog，然后再提交事务，更新存储引擎中的数据，事务提交完成后，给客户端返回操作成功的响应。同时，从库会有一个专门的复制线程，从主库接收 Binlog，然后把 Binlog 写到一个中继日志里面，再给主库返回复制成功的响应。从库还有另外一个回放 Binlog 的线程，去读中继日志，然后回放 Binlog 更新存储引擎中的数据，这个过程和我们今天讨论的主从复制关系不大，所以我并没有在图中画出来。提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制。
在异步复制的情况下，为什么主库宕机存在丢数据的风险？为什么读写分离存在读到脏数据的问题？都是因为异步复制它没有办法保证数据能第一时间复制到从库上。
3. 先复制Binlog到从节点，主节点再提交事务（同步，不会丢数据）（同步复制在实际项目基本没法用，因为性能差和可用性差）
4. 主节点先提交事务，再复制Binlog到从节点（性能好，但是会丢数据）
5. 半同步复制（一主二从：只要数据成功复制到任意一个从库，主库的事务线程就直接返回了） after commit以及after sync
6. 默认mysql采用异步复制
七． MySQL重连机制
1. 短连接需要三次握手四次挥手，增加延时和额外IO，长连接可以避免每次创建连接的开销，节约时间和IO
2. Mysql一般情况是长连接机制
3. 断线重连：服务器出现某些原因导致数据库连接中断，需要重新连接数据库，并重新执行中断的数据库操作
4. 方法：mysql_query (给出一个错误码，返回失败)-mysql_connect-mysql_query（成）
5. 问题：mysql什么时候可以重连，什么时候不可以
一般开启事务，执行sql就不允许重连了；
Mysql对应server上的一个线程，如果断开了就会回收这个线程，并且将没有提交的事务会滚，相当于前面的sql是没有用的
为什么要手动回滚，
在事务执行的过程中不可以回滚；执行commit或者回滚之后才是结束了；然后你不相信它自动回滚，所以要手动回滚，把这个事务结束
（那开启了事务，但是断线了，不能重连该怎么办）回滚、
八． Sql语句之group by
数据量小则在内存（内排序） （可排序）
数据量大则在磁盘？（外排序）（数据量太大内存放不下）
九． 问题：mysql为什么要一个线程对应一个连接（mysql server的，先不看）
十． 写一个sql，开启事务，对事务加锁，提交和回滚
```
…
```
十一． Mysql的锁对应加在那一列是什么现象
（是不是表级锁页面锁行级锁）
mysql 默认情况是 auto commit（执行sql不需要自动提交），但开启了事务自动提交后就不可以回滚了。
假设update失败了，那也是一种回滚。
这时候就会失效，那就需要手动提交或者回滚。
Reference：
https://blog.csdn.net/sinat_26811377/article/details/99592382
https://www.liaoxuefeng.com/wiki/1177760294764384/1218728391867808
https://zhuanlan.zhihu.com/p/64368422
https://www.liaoxuefeng.com/wiki/1177760294764384
https://zhuanlan.zhihu.com/p/29150809
https://segmentfault.com/a/1190000020416577
浙公网安备 33010602011771号