- 前面的底层原理都在笔记本上可以翻看笔记本（为了方便还是作为文档补上）
- 08 索引的创建和设计原则
- 09 性能分析工具的使用
- 这个就是index 覆盖索引 这个key_part3也走了索引 只不过他本身是联合索引，他需要考虑前面两个字段 而且他查询的数据不需要回表操作
- 这个不走索引 因为他查询的字段本是联合索引是查不到的，他需要回表这个操作 所以他的type是ALL
- 8.分析优化执行器
- 9.mysql监控分析视图-sys schema
- 10 索引优化和查询优化
- 你没有看错，百分号可以走索引 当然必须是尾百分号可以，头%还是不走索引的走全表这点参照第9条
- 一般建议：
- 10.2关联查询优化
- 结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
- 结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
- 10.3子查询优化
- 10.4排序优化
10.5.filesort算法：双路排序和单路排序">
10.5.filesort算法：双路排序和单路排序- 10.6GROUP BY优化
- 10.7优化分页查询
- 10.8覆盖索引
- 不走索引 因为这里的*是需要回表操作的 优化器计算执行成本后，既然得回表还不如直接查全表来得快
- 走索引 因为如果走索引可以省去回表这个操作，优化器觉得执行成本比较低，所以走索引
- 不走索引 前模糊查询是不走索引的
- 走索引 我觉得这个和那上面那个是一样的
- 13.事务基础知识
- 2.1 如何开启？ 使用关键字：start transaction 或 begin、
# start transaction 后面可以跟：read only / read write (默认) / with consistent snapshot - set autocommit = false;
- 方式2：我们在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据
- 这个只会查出张三这个主键 因为受主键影响不能添加成功 回滚了，回滚到上一次commit
- 4.事务的常见分类
- 14mysql事务日志
- 15.锁 不管你有没有事务，都会产生锁，事务的隔离级别是依赖锁产生的
- 16多版本并发控制
- 17.其他数据库日志
- 18.主从复制
- 1.1如何提升数据并发能力
- 2.主从复制的原理
- 3.一主一从架构搭建
- 4.同步数据一致性问题
- 5.知识延展
- 19.数据库备份与恢复
- 完结撒花！！！！！
前面的底层原理都在笔记本上可以翻看笔记本（为了方便还是作为文档补上）
07 InnoDB的数据储存结构
页的内部结构
常见的页有 数据也 、系统页、undo和事务数据页
数据页的默认大小是16kb 分为7个部分
分别是： 1.文件头 2.页头 3.最大最小记录 4.用户记录 5.空闲空间 6.页目录 7. 文件尾
7部分的作用
第一部分 文件头和文件尾部
文件头顶信息 头部和尾部
1.文件头里的构成：
双向链表的字段
FIL_PAGE_SPACE_CHKSUM（校验和）
什么是校验和？他的作用是什么？
就是通过某种算法讲一个很长的值计算成一个很短的值 ，我们成为校验和（类似于hash算法和 md5加密）通过生成的算法来比较 如果一样的话就是相同的，如果不同的话就是不同的
作用：
同步数据的过程中时候断电后 通过校验和的比较校验和是否相同 来判断该页是否完成传输 是否同步到磁盘中
如果没有完整传输 ，需要重新传输
文件尾（8字节）校验和（）日志位置 都是为了校验文件的完整性
第二部分 最大最小记录
- 空闲空间：未记录数据列
- 用户记录：记录用户数据 （单链表）行格式
- 最小最大记录
第三部分 页目录（Page Header）
1.PAGE_DIRECTION
2.PAGE_N_DIRECTION行格式
1.指定行格式的语法
2.COMPACT行格式
delete_mask：标记是否被删除（逻辑删除） 0未删除 1删除 为什么是逻辑删除 是因为删除以后会使后续所有记录都会移动会消耗性能 而且可以重用和覆盖
min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mak值为1。
我们自己插入的四条记录的min rec mask值都是0，意味着它们都不是B十树的非叶子 节点中的最小记录。0为普通用户记录 也就是页子节点 1是目录节点 2是最小记录 3是最大记录 b+tree结构（聚簇索引）
heap_no
这个属性表示当前记录在本页中的位置。
从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。
怎么不见heap no值为0和1的记录呢？
MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以
有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代袁最大记录
。最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前
n_owned
页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为
nowned 宇段。
详情见page directory。
nex_record
记录头信息里该属性非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的
地址偏移量
删除操作
最小值为单独一组 最大记录和普通记录是一组 所以n_owned从5-4因为 第二条数据被删掉了
加回来
如果多条被删除 他们的next_record 会组成被删除的链表 方便被加回来或则覆盖
3.Dynamic和Compressed行格式（mysql默认用的行格式）
行溢出
InnoDB存储引擎可以将-条记录中的某业数据存储在真正的数据页面之外
极限的长度 varchar的极限长度是65535 在规定的as码表的情况下最长可以为65533字节 为什么有两个字节的缺失 是因为 65533+两个可变长字段的长度 如果varchar可以为null的情况下 varchar最长的字节为65532就是65532+2可变长度字段+1null标识
行溢出：如果一个varchar的长度为65533最长大于一个页的页的大小16kb（16384）65533>163854 一个字段大于一个页就会出现（行溢出）
区、段、碎片区
为什么要有区？
B+树的每一层中的页都会形成个双向链表，如果是以 页为单位 来分配存储空间的话，双向链表相邻的两个页
之间的 物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的
记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远
就是所谓的 随机I/0。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/0是非常慢的，所以我
们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的 顺序工/0。引入区的概念，一个区就是在物理位置上连续的 64个页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的
大小是 64*16KB=1MB。在表中数据量大 的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是
按照 区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间
的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机I//0，功大于过
我们没办法保证每个页都是连续的 我们可以保证申请一个大的地方 也就是一个区 一个区可以包含64个页 每个区也是双向链表连着
为什么要有段
对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节
点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非
叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的
区的集合就算是一个段（segment），存放非叶子节点的区的樂合也算是一个段。也就是说一个索引会生成2个
段
- 个叶子节点段
-
碎片区
默认情况下，一个使用InnoDB存储引擎的表只有一 个聚筷索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M （64* 16K6 =1024Kb）存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么？以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹 的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。
为了考虑以完整的区为单位分配给某个段对于 数据量较小 的表太浪费存储空间的这种情况，InnoDB提出了一个碎片区（fragment）区 的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。
所以此后为某个段分配存储空间的策略是这样的： 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
- 当某个段已经占用了 32个碎片区 页面之后，就会申请以完整的区为单位来分配存储空间。
所以现在段不能仅定义为是某些区的集合，更精确的应该是 莱些零散的页面 以及 一些完整的区 的集合。
区的分类
区大体上可以分为4种类型：
- 空闲的区(FREE）：现在还没有用到这个区中的任何页面。
- 有剩余空间的碎片区(FREE_ FRAG)：表示碎片区中还有可用的页面。
- 没有剩余空间的碎片区 (FULL_FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面。
- 附属于那个段的区 (FSEG)：每一个索引都可以分为叶子节点段和非叶子节点段。
处于 FREE 、FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，直属于表空间。而处于FSEG 状态的区是附属于某个段的。
表空间
表空间可以看做是InnoDB存储引l擎逻辑结构的最高层，所有的数据都存放在表空间中。
表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个表空间中可以有一个或多个段，但一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为 系统表空间 (Systemtablespace）、独立表空间 (File-per-table tablespace）、撒销表空间 (Undo Tablespace）和临时表空间(Temporary Tablespace)等
独立表空间
系统表空间
数据源页加载的三种方式
1.内存读取
2.随机读取 如果内存没有就需要在磁盘查找 随机查找需要10ms左右时间 如下图所示
3.顺序读取
顺序读取其实是一种批量读取的方式，因为我们请求的 数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话—次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/0 操作了。如果一个磁盘的吞吐量是 40MB/S，那么对于一次可以顺序读取2560 （40MB/16KB）个页，相当于一个页的读取时间为 0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取页的效率要高
08 索引的创建和设计原则
1.索引的声明与使用
1.1索引对的分类
mysql的索引包括普通索引、唯一索引、全文索引、单列索引、多列索引和表空间索引等
- 从逻辑上说。索引主要有4种， 普通索引、唯一索引、主键索引、全文索引
- 按照物理实现方式，索引分为2种：聚簇索引和非聚簇索引
-
1.普通索引
2.唯一索引
使用UNIQUE可以设置唯一性索引，限制该索引必须是惟一的 但容许有空值 在一张表里可以有多个唯一索引
3.主键索引
4单列索引
5.多列索引
多个字段组合创建的一个索引，
例如，在表中的字段id、name和 遵循最左原则 这个根据b+树的结构来的
比如 supplier_id 和phone 和name 三个是一个联合索引
如果查询 supplier_id = 1 AND phone = 2 是可以走索引
如果 phone = 2 and name = ‘哈哈’ 这是不走索引的6.全文索引
自然语言的全文索引 和 布尔全文索引 适合大型数据集 使用分词实现
7.空间索引
小结
1.2创建索引
隐式的方式创建索引，在声明有主健约束、唯一性约束、外健约束的字段上，会自动添加相关的索引
显式的方式的创建 UNIQUE、FULLTEXT和SPAITAL为可选参数、分别表示唯一索引、全文索引和空间索引
- INDEX与KEY为同义词，两者的作用相同，用来指定创建索引
- index_name 指定索引的名称，可选参数 如果不指定，nameMySql默认col_name为索引名
- col_name为需要索引的字段列，该列必须从数据中定义的多个列中选择
- length为可选参数 表示索引的长度 只有字符串类型的字段才能指定索引长度
-
1.3索引删除
删除索引可以在表执行insert和update操作时可以删除索引，然后进行插入等操作 效率高 执行完操作再添加索引
2.MySql8.0索引新特性
2.1支持降序索引（8.0真正的支持降序，仅限Innodb引擎）
2.2隐藏索引（5.7之后支持）
在5.7之前 只能通过显式的方式删除索引。此事如果删除索引出现错误，只能通过创建索引的方式将删除的索引创建回来，如果表中的数据量非常大在，这种操作就会消耗系统更多资源 操作成本非常高
8.点以上版本支持隐藏索引， 只需要将待删除的索引设置为隐藏索引，使查询优化器不在使用这个索引，确认这个索引隐藏起开不收系统影响，就可以彻底删除 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 如果你想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引3.索引的设计原则
3.1数据准备
3.2那些情况适合加索引
1.字段的数值有唯一性的限制
索引本身就可以起到约束的作用，如果某个字段是唯一性约束，就可以直接创建唯一性索引，或者主键索引
2.频繁的作为where查询的字段
3.经常GROUP BY 和ORDER BY的列
如果同时有 GROUP BY 和 ORDER 日 的情况：比如我们按照student_ id 进行分组，同时按照创建时间降序的方式进行排序，这时我们就需要同时进行 GROUP BY 和 ORDER BY，那么是不是需要单独创建 student _id 的索号和create time 的索引呢？
SELECT student_id, count(*) as num FROM student_infogroup by student_idorder by create_time desclimit 100;
以上sql 如果student_id 和create_time 各自有自己单独的索引 执行后之走student_id的索引条件 因为一个语句中只走一个条件 按照sql的执行顺序
#添加联合索引 ALTER TABLE student_info ADD INDEX id_sid_cre_time (student_id, create_time DESC); EXPLAINI SELECT student id, COUNT (*) AS num FROM student_ info GROUP BY student_id ORDER BY create_time DESC LIMIT 100; #0.257s因为上面student_id和create_time都有对应自己的索引也有联合索引 如果联合索引按照student_id和create_time循序来排序，就会使用有序的联合索引查询 如果不是的话是按照create_time和student_id排序查询的话就不走联合索引 而走单个索引即student_id这个单个索引
4.update 、delete 的 where 条件
5.DISTINCT字段需要创建索引
6.多表join连接 创建索引注意事项
连表尽量不要超过3张 每加一张表就增加一次嵌套的循环 严重查询效率 1张->两张->三张-> n->n方->n的立方->
- 对where条件创建索引，
-
7.使用列的类型小的创建索引
数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引!占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 工/0带
来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率
8.使用字符串前缀创建索引B+树素引中的记录需要把该列的完整字符串存储起末，更费时。而且字符串越长，在素引中占用的存储空问越大。
如果B+树素引中素引列存储的字符串很长，那在做字符串 比较时会占用更多的时间
9.区分度高（散列性高）的列适合做索引（也就是重复的少）
用白话来说就是，适合当索引的列的值要重复比较小的，这样根据这个索引排序数据，区分大，也就更好查询
10.使用最频繁的列要放在联合索引的左侧（理由你懂得请参照第2条）
11.联合索引优于单值索引（去看看第3条吧，求求了！！！！）
3.3限制索引的数目
建议单表不超过6个索引 原因：
每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大
- 索引会影响insert、delete、update语句的性能，因为表中数据修改的同时，索引也会进行调整和更新，会造成负担
优化器在选择如何优化查询是，会根据统一的信息，对于每一个可以用到的索引进行评估，生成一个计划，如果有很多索引都用于查询，会增加mysql优化器生成执行计划时间，降低查询性能
3.4那些情况不适合创建索引
1.在where中使用不到的字段，不要设置索引
2.数据量小的表最好不用索引
3.有大量重复数据的列上不要建立索引
4.避免对经常更新的表创建过多的索引
5.不建议用无序的值作为索引
6.很少使用的索引要删除
7.不要定义冗余或者重复的索引
09 性能分析工具的使用
1.数据库服务器优化步骤
流程分为两部分观察和行动两部分 S代表观察 A代表行动
小结：
2.查看系统性能参数
在MysQL中，可以使用 SHOW STATUS 语句查询一些MysQL数据库服务器的 性能参数、执行频率，
3.统计sql的查询成本 last_query_cost
4.定位执行慢的sql：慢查询日志
默认mysql是不开启慢查询日志，需要我们手动的设置这个参数，如果不是调优需要，一般不建议启动该参数，因为多少都会带来一定的性能影响
4..1开启慢查询日志参数
1.开启slow_query_log
show variables like '%slow_query_log';#查询是否开启慢日志设置 on开启 off为未开启 show variables like '%slow_query_log%';#查询是否开启慢日志设置，同时查看慢日志存放的位置 show variables like '%long_query_time%';#查看设置慢查询时间4.2查看慢查询条目数
show status like '%slow_queries%'4.3慢查询日志分析工具：mysqldumpslow
4.4关闭慢查询日志
4.5删除慢查询日志
5.查看sql执行成本：show profile 前面讲过
6.分析查询语句：EXPLAIN
6.1概述
定位到了慢查询的sql之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句，DESCRIBE和EXPLAIN是一样的两个工具，就是单词不一样
执行计划：mysql中有专门负责优化select语句的优化器模块主要功能就是：分析系统中收集到的统计数据，为客户端的请求query提供最优的执行计划
EXPLAIN就是帮助我们查看执行计划的语句1.能作甚么？
表的读取顺序
- 数据读取操作的操作类型
- 那些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
6.2EXPLAIN的字段
1.table：表名 查询的每一行记录都对应着一个单表
2.id：在一个大的查询语句中每个select关键字都对应一个唯一的id
有量select所以有第三条数据id就是2了
小结：
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好
3.select_type：select关键字对应的那个查询的类型，确定小查询在整个大查询中扮演的角色
SIMPLE类型：
语句中不含union或者子查询的查询都算simple
- 连接查询也算simple
PRIMARY:
- 对于包含UNION或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的查询
UNION：
- 对于包含UNION或者UNION ALL的大查询来说，他是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询都是UNION
UNION RESULT
- mysql使用临时表来完成union查询的去重工作，针对该临时表的查询就是union result
SUBQUERY
如果包含的子查询语句不能转成对应的多表连接的形式，该子查询不是相关子查询 那么该查询的第一个select关键字代表那个查询就是 SUBQUERY
explain select * from s1 where key1 in(select key1 from s2 ) or key3 = 'a';DEPENDENT SUBQUERY
如果包含的子查询语句不能转成对应的多表连接的形式，该子查询是相关子查询 那么该查询的第一个select关键字代表那个查询就是 SUBQUERY
explain select * from s1 where key1 in(select key1 from s2 where s1.key2 = s2.key2 ) or key3 = 'a';DEPENDENT UNION
在包含UNION或者、UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的“select_typeI的值就是^DEPENDENT UNION
DERIVED
对于包含派生表的查询，该表的子查询就是 DERIVED
explain select * from (select key1 ,count(*) as c from s1 group by key1)as derived_s1 where c>1;4.partitions 分区:匹配分区
5.type
执行计划的一条记录就代表着MySQL对某个表的 执行查询时的访问方法，又称“访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type 列的值是ref，表明MySQL 即将使用ref访问方法来执行对 s1 表的查询
完整的访问方法如下：system, const ,eq_ref, ref ,fulltext ,unique_subquery ,index_subquery ,range ,index ,ALLsystem 表示只有一条记录 并且该表使用的存储引擎的统计数据是精确的，比如myisam
- const 当我们工具主键或者唯一二级索引列与常数进行等值匹配时
- 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref
- 当通过普通的二级索引列与常量进行等值匹配时来查询某个表
- 当我们可以使用索引覆盖，但需要扫描全部的素引记录时，该表的访问方法就是、index’ ```sql CREATE TABLE s1 ( id INT AUTO INCREMENT, key1 VARCHAR ( 100 ), key2 INT , key3 VARCHAR ( 100 ), key_part1 VARCHAR ( 100 ), key_part2 VARCHAR ( 100 ), key_part3 VARCHAR ( 100 ), common_field VARCHAR ( 100 ), PRIMARY KEY ( id ) , INDEX idx_key1 ( key1 ) UNIOUE INDEX idx_key2 ( key2 ), INDEX idx_key3 ( key3 ) INDEX idx_key_part ( key_part1, key_part2, key_part3 ) )ENGINE = INNODB CHARSET = utf8 ;
这个就是index 覆盖索引 这个key_part3也走了索引 只不过他本身是联合索引，他需要考虑前面两个字段 而且他查询的数据不需要回表操作
EXPLAIN SELECT key_part2 FROM S1 WHERE key_part3 = ‘a’
这个不走索引 因为他查询的字段本是联合索引是查不到的，他需要回表这个操作 所以他的type是ALL
EXPLAIN SELECT key1,key_part2 FROM S1 WHERE key_part3 = ‘a’
- ALL 全表扫描
<a name="NSZVp"></a>
###### 6.possible_keys和key
possible_key可能用到的索引<br />key真正使用的索引
<a name="ApISw"></a>
###### 7.key_len
实际使用到的索引长度 帮你检查是否充分利用上的索引，值越大越好
<a name="BTwPR"></a>
###### 8.ref
当使用索引列等值查询是，与索引列进行等值匹配的对象信息
<a name="ks8m6"></a>
###### 9.rows
预估的需要读取的记录数 值越小越好
<a name="GW2nT"></a>
###### 10.filtered
某个表经过搜索条件过滤后剩余记录条数的百分比（越高越好）
<a name="IwFci"></a>
###### 11.Extra
- No table used
当查询句的没有FROM子句时将会提示该额外信息
- Impossible WHERE
```sql
EXPLAIN SELECT *FROM s1 WHERE 1!=1 条件永远不成立
- Using WHERE ```sql EXPLAIN SELECT * FROM s1 WHERE common_field = ‘a’ #common_field没有索引 显示Using WHERE
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ AND common_field = ‘a’ #此时key1有索引而common_field没有索引 显示依然是Using WHERE
- Using index
```sql
EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';#Using index
#虽然key1是一个二级索引 由于b+树的结构 key1索引还会和id也就是主键连接形成结构,所以本条sql通过key1这个索引来找key1，和id就直接能找到这两个字段，不需要回表操作
EXPLAIN SELECT key1,id,key2 FROM s1 WHERE key1 = 'a';
#虽然只增加了一个key2，但是完全不同，因为它直接通过索引找不到key2这个字段，所以查完以后还需要进行回表这一个操作，他就不属于索引覆盖所以Extra为NULL
7.EXPLAIN的进一步使用
7.1EXPLAIN可以为4种格式： 传统格式、JSON格式、TREE格式以及可视化输出 以上使用都是传统格式
JSON格式：
EXPLAIN FORMAT = JSON7.2SHOW WARNINGS 的使用
8.分析优化执行器
9.mysql监控分析视图-sys schema
9.1查询冗余索引
SELECT * FROM sys.schema_redundant_indexes9.2查看未使用过的索引
SELECT * FROM sys.schema_unused_indexes9.3查询索引使用情况
9.4查询表的访问量
select table_schema,table_name,SUM(io_read_requests+io_write_requests)as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;9.5查询占用bufferpool较多的表
9.6查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db = 'dbname'9.7查看行锁的情况
select * from sys.innodb_lock_waits;10 索引优化和查询优化
索引失效、没有充分利用到索引 — 索引建立
- 关联查询太多join —sql优化
- 服务器调优各个参数设置
- 数据过多 — 分库分表（主从复制 读写分离）
sql查询优化技术有很多，但是大方向上完全可以分成 物理查询优化和逻辑查询优化 两大块
- 物理查询优化是通过索引和表连接方式等技术进行优化
- 逻辑查询你优化就是通过sql等价变换提升查询效率，就是换一种写法执行效率可能更高
10.1索引失效案例（下面是优化）
sql语句是否使用索引、跟数据库版本、数据量、数据选择都有关系1.全值匹配
2.最佳左前缀匹配规则
CREATE INDEX id_age_classid_name ON student(age,class_id,NAME) #建立了联合索引 EXPLAIN SELECT * FROM student WHERE class_id = 4 and age = 30 and name = 'abcd' #由于建立的是联合索引 遵循最左前缀匹配原则 所以这条sql语句只能走 age 这一个索引 走不了name 因为B+树的原因 无法略过class_id这个字段 所以只能走age这个字段3.主键插入顺序
也就是主键AUTO_INCREMENT属性，在插入记录时储存自增长，这样主键占用小，顺序写入，减少页分裂4.计算、函数、类型转换导致索引失效
```sql EXPLAIN SELECT * FROM student WHERE name like ‘abc%’你没有看错，百分号可以走索引 当然必须是尾百分号可以，头%还是不走索引的走全表这点参照第9条
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
<a name="tPRhk"></a>
###### 5.类型转换也不行
```sql
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
6.范围条件右边的列索引失效
(<）(>) (>=) (<=) between等范围
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
#这种情况只会走age和classId这两个索引不会走abc 因为classId查的不是等值，所以后面的索引
会失效，只能查到范围前面的索引 如果想走全部的索引就得改变联合索引的位置
CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);
#这样查询就可以了 而且优化器会自动调整位置所以，你不需要管条件的位置
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
7.不等于索引失效(!=或者<>
!=和<>一样
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
8.is null 可以使用 is not null 不可以
9.like以通配符%开头索引失效
#可以走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
#不可以走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
10.OR 前后存在非索引的列，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
#如果想走索引就要保证or 两边都是索引列
11.数据库和表的字符集统一使用utf8mb4
一般建议：
- 对于单列索引，尽量选择针对当前query过滤性更好的索引
- 选择组合索引的时候，当前query中过滤最好的字段在索引字段顺序中，位置越靠前越好
- 在选择组合索引的时候，尽量选择能包含当前query中的where子句中更多字段的索引
- 选择组合索引的时候，如果某个字段可能出现范围查找时，尽量把这个字段放在索引次序的最后面
10.2关联查询优化
1.左外连接
需要类型一样EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;2.内连接
```sql结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
EXPLAIN SELECT SQL_NO_CACHE * FROMtypeINNER JOIN book ON type.card = book.card;
结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
<a name="QzUhq"></a>
##### 3.join语句原理
join方式连接多个表，本质就是各个表之间数据循环匹配 在5.5以后版本，Mysql通过引入BNLJ算法优化嵌套执行
<a name="EazjV"></a>
###### 3.1simple（简单嵌套循环连接）
由于没有索引所以每次拿出a表数据都会在被驱动表走全部扫描 所以说这种效率比较低<br /><br />没有索引所以''小表驱动大表"
<a name="gZqS3"></a>
###### 3.2 Index Nested-Loop Join（索引循环连接）
<br />
<a name="yHQnc"></a>
###### 3.3Block Nested-Loop Join (块嵌套循环连接)
<br /><br />
<a name="WyJpJ"></a>
###### 4.Join小结
1. 整体比较 INLJ>BNLJ>SNLJ
1. "小表驱动大表"是不准确的，应该说是过滤后的数据较小的表驱动较大的表 比如上面的例子
```sql
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
#这个看似是a驱动b 其实不然，mysql会在底层执行优化 ，如果a过滤后还有100条数据 b过滤后有500
#条数据，那确实是a驱动b （小表驱动大表） 但是如果说过滤完成以后，a有100条数据而b还有50 这个
#时候就是b驱动a了（也还是小表驱动动大表）
- 为被驱动表匹配的条件增加索引
4.增加join buffer size的大小
5.减少驱动表不必要的字段查询（字段越少，jion buff而所缓存数据就越多）4.hash join
从MysQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash jioin默认都会使用hash ioin
10.3子查询优化
- 执行子查询时，mysql会根据内查询的查询结果建立零时表 然后查询完了，再销毁零时表 浪费I/O和CPU 产生慢查询
- 子查询的结果集储的零时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
- 对于返回结果集比较大的子查询，其对查询性能的影响也就越大
MySql中，可以使用连接查询替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。
10.4排序优化
问题：在where条件字段加索引，为什么在order by字段上还要加索引？
回答：mysql支持两种排序方式，一种是FileSort 和 Index排序
- Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
- FileSort排序一般在内存中进行排序，占用CPU较多，如果待排序结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低
优化建议：
- sql中，可以WHERE子句和ORDER BY 子句使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY避免使用FileSort 排序
- 尽量使用index完成ORDER BY排序。如果WHERE和ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引
- 无法使用Index 是，需要对FileSort方式进行调优
那些不走索引
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME);
CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno);
#不走索引
EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10;
#不走索引
EXPLAIN SELECT * FROM student ORDER BY classid,NAME LIMIT 10;
#走索引 索引覆盖
EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;
#不走索引 需要回表操作 ，比走ALL更费时间 执行优化器直接走全表
EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno ;
#走索引
EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10;
#走索引
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;
#不走索引 DESC 和 ASC
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
#不走索引
EXPLAIN SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;
#不走索引
EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;
#走索引 倒着遍历
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;
#走索引 但是只考虑age过滤 如果age过滤的数据没剩多少 就不考虑order by的索引 如果多的话也
会走
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid;
#不走索引
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,NAME;
#不走索引
EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age;
#走索引 我明白可能是优化器 底层优化 先age排序之后 再
EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10;
10.5.filesort算法：双路排序和单路排序
10.6GROUP BY优化
- 和order by一致 group by及时没有过滤条件用到索引，也可以使用索引
- group by先排序分组，按照最左前缀原则
- 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数设置
- where效率高于having 所以能写where 限定条件就不要写在having中
- 减少order by 能不排序就不排序 放在程序去做 order by group by distinct 比较消耗cpu
- 包含order by group by distinct 这些查询语句，where过滤条件的结果请保持在1000行内，否则sql会很慢
10.7优化分页查询
EXPLAIN SELECT * FROM student LIMIT 2000000,10; #怎么优化 #优化方式一 根据主键索引取到记录然后再走回表操作 EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)a WHERE t.id = a.id; #优化方式二 适用于主键自增的表，可以把limit查询转成某个位置查询 EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;10.8覆盖索引
```sql CREATE INDEX idx_age_name ON student (age,NAME);不走索引 因为这里的*是需要回表操作的 优化器计算执行成本后，既然得回表还不如直接查全表来得快
EXPLAIN SELECT * FROM student WHERE age <> 20;走索引 因为如果走索引可以省去回表这个操作，优化器觉得执行成本比较低，所以走索引
EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;
不走索引 前模糊查询是不走索引的
EXPLAIN SELECT * FROM student WHERE NAME LIKE ‘%abc’;
走索引 我觉得这个和那上面那个是一样的
EXPLAIN SELECT id,age FROM student WHERE NAME LIKE ‘%abc’;
覆盖索引的利弊<br />好处：<br />1.避免innodb表进行索引的二次查询（回表）<br />innodb是以聚集索引的顺序来存储的，避免对主键二次查询（回表操作），减少IO操作，提升查询效率<br />2.可以把随机IO变成顺序IO加快查询效率<br />因为你的二级索引或者联合索引一般是放在一起的，这样就可以直接去找相应页的数据，这就叫顺序I/O<br />如果你要执行回表操作，你就需要拿着联合或者二级索引下挂的主键去到表里相对应的页子节点，因为主键可能很分散，所以每个页都要找，这就叫随机I/O<br />弊端：<br />有利有弊，索引的维护总是有代价的，这样会冗余 ，还有你的索引多了以后你每页(16kb)存的数据就会变少，时间换空间，空间换时间，亘古不变铁子！！！
<a name="z68MA"></a>
#### 10.9索引下推（ICP）
索引条件下推
```sql
#其实和覆盖索引一样，先走key1>‘z’查询数据，比如查出1000条不急着回表 然后再走后面key1 like
#‘%a’这个 ，这样比如过滤完就剩 10条 这样的话就只有10条数据去回表 ，减少随机I/O的次数
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
案例二
CREATE TABLE `people` (
`id` INT NOT NULL AUTO_INCREMENT,
`zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
`firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
`lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
`address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
INSERT INTO `people` VALUES
('1', '000001', '三', '张', '北京市'),
('2', '000002', '四', '李', '南京市'),
('3', '000003', '五', '王', '上海市'),
('4', '000001', '六', '赵', '天津市');
#其实和覆盖索引一样，先走key1>‘z’查询数据，比如查出1000条不急着回表 然后再走后面key1 like
#‘%a’这个 ，这样比如过滤完就剩 10条 这样的话就只有10条数据去回表 ，减少随机I/O的次数
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';
11.数据库的设计规范
1.为什么要数据库设计
2.范式
在关系型数据库中，关于 数据表设计的基本原则、规则就称为范式
2.1范式都包括那些
- 第一范式
- 第二范式
- 第三范式
- 巴斯-科德范式
- 第四范式
-
2.2第一范式
2.3第二范式
满足每一行数据记录，都是可唯一标识，而且所有非主键字段，都必须完全一开主键，不能只依赖主键的一部分
3.反范式
3.1概述
4.ER模型（使用PowerDesigner）
5.数据表的设计原则（三少一多）
5.1数据表的个数越少越好
5.2数据表中的字段个数越少越好
5.3数据表中联合主键的字段越少越好
5.4使用主键和外键越多越好
10.数据库对象编写建议
10.1关于库
10.2关于表、列
10.3关于索引
10.4sql编写
12.其他查询优化策略
12.1 EXISTS和IN的区分
#这种情况是A为大表B为小表效率高 SELECT * FROM A WHERE cc IN(SELECT cc FROM B) #这种情况A为小表B为大表效率高 SELECT * FROM A WHERE EXISTS(SELECT cc FROM B WHERE B.cc = A.cc)12.2 COUNT（*）与COUNT（具体字段）效率
SELECT COUNT(*)、SELECT COUNT(1) 和SELECT COUNT（具体字段）
count（*）和count（1）本质没有区别
- 如果储存引擎不同的情况下他的复杂度不一样 myisam的复杂度是O1级别 innodb的是全表扫描是O(n)的复杂度
在innodb的存储引擎下，最好不要count主键 尽量使用二级索引 因为你懂得索引即数据数据即索引 二级索引下挂的不是具体数据 加在到内存比较快，也比较小，所以选择二级索引，至于他怎么选择呢，如果你没有规定count那个字段，他会自动的分配给一个比较小的二级索引（key_len比较小）来统计
12.3关于SELECT*
12.4LIMIT1对优化的想影响
如果是全表扫描的情况下，你加了LIMIT1，就会再得到结果后立刻停止扫描，这样加快查询速度
如果建立唯一索引，不会全表扫描，就不需要加limit1这个 条件 因为你既是唯一也是索引，就每个都是唯一对应的，你这个加个limit1纯属浪费12.5多使用commit
只要有可能，在程序中尽量多使用commit，这样性能得到提高
commit所释放的资源回滚段上用于恢复的信息
- 被程序语句获得的锁
- redo /undo log buffer 中的空间
-
数据库调优的措施
1.如何定位调优问题
用户反馈
- 日志分析
- 服务器资源使用监控
-
2.调优的维度和步骤
选择合适的数据库管理系统DBMS（mysql sqlserver oracle等）
- 优化表设计 遵循三范式、表字段数据类型选择
- 优化逻辑查询
- 优化物理查询正确使用索引
- 使用redis缓存 memcached
-
3.优化mysql服务器 优化服务器硬件
优化服务器硬件
- 优化mysql的参数（innodb_buffer_pool_size key_buffer_size table_cache query_cache_size query_cache_type sort_buffer_size innodb_flush_log_at_trx_commit innodb_log_buffer_size max_connections back_log thread_cache_size wait_timeout interactive_timeout）
-
3.1拆分表：冷热数据分离
3.2增加中间表
3.3增加冗余字段
3.4优化数据类型
避免使用text、blob
- 避免使用enum类型
- 使用timestamp类型（他小于datetime 他4字节 datetime8字节）
-
3.5优化插入记录的速度
3.6使用非空约束
3.7分析表、检查表和优化表
分析表 在执行过程中会加只读锁 analyze table
- 检查表 在执行过程中会加只读锁 check table
优化表 optlmize table 整理碎片空间 ，细节和硬盘整理一样和gc也一样
4.大表优化
4.1限定查询的范围
4.2读、写分离
4.3垂直拆分
5.其他调优策略
5.1服务器语句超时处理
5.2创建全局通用表空间
13.淘宝数据库，主键如何设计的？
13.1自增ID的问题
可靠性不高
安全性不高
性能差
交互多
局部唯一性
13.2业务字段做主键
所以业务字段不好当主键值，会有重复，重用的风险13.3淘宝的主键设计
订单标的主键
时间+去重字段+用户后六位13.4推荐的主键设计
非核心业务：对应表自增id，比如监控啥的
核心业务：主键至少是全局单调递增全局唯一13.事务基础知识
acid
一致性
- 原子性
- 隔离性
-
1.数据库事务的概述
1.1存储引擎支持情况
1.2基本概念
事务：一组逻辑操作单元，使数据从一种状态变换另外一种状态
事务处理的原则：要么将commit 的记录，永久保存下来 要么将放弃所有修改，将事务rollback到最初状态1.3事务的acid特性
原子性：同生共死 要么全部提交 要么全部失败 没有灰色地带
- 一致性：数据从一个合法性状态变成另外一个合法性状态 从语义上是合法性状态 如果不合法就需要回滚 ```sql 举例1：A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什 么呢？因为你定义了一个状态，余额这列心须>=0。 不属于一致性
举例2：A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道 此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的总余额必须不变。
- 隔离性：事务的隔离性是指一个事务的执行 不能被其他事务干扰，即一个事务内部的操作及使用的数对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
- 持久性：就是永久性的改变 通过事务日志来保证的 日志包括重做日志和回滚日志
```sql
总结
ACID 是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是
我们的目的。
数据库事务，其实就是数据库设计者为了方便起见，把需要保证 原子性、隔离性、一致性和持久性的一个
或多个数据库操作称为一个事务。
1.4事务的状态
事务的状态大致分为几个
- 活动的
- 部分提交的 将加在到内存的数据页的数据修改但还没有i/o将数据刷入硬盘
- 失败的
- 中止的
- 提交的
2.如何使用事务
2.1显示事务
2.1 如何开启？ 使用关键字：start transaction 或 begin、
# start transaction 后面可以跟：read only / read write (默认) / with consistent snapshot
- read only 只读事务 (ps:临时表可以增删改查)
- read write 读写事务
- with consistent snapshot : 启动一致性读
- 保存点(savepoint)
2.2 隐式事务
关键字 ```sqlset autocommit = false;
SHOW VARIABLES LIKE ‘autocommit’;#默认是ON
UPDATE account SET balance = balance - 10 WHERE id = 1; #此时这条DML操作是一个独立的事务
UPDATE account SET balance = balance + 10 WHERE id = 2; #此时这条DML操作是一个独立的事务
如果关闭自动提交？<br />#方式1：
```sql
SET autocommit = FALSE; #针对于DML操作是有效的，对DDL操作是无效的。
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;
COMMIT; #或rollback;
方式2：我们在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据
START TRANSACTION;
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;
COMMIT; #或rollback;
2.3隐式提交数据的情况
- 数据定义语言DDL
- 隐式使用或修改mysql数据库中的表
- 事务控制或关于锁定的语句
- 加在数据的语句
- 关于mysql复制的一些语句
- 其它的一些语句
2.4使用举例：提交与回滚
```sql CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);
SELECT * FROM user3;
BEGIN; INSERT INTO user3 VALUES(‘张三’); COMMIT;
BEGIN; #开启一个新的事务 INSERT INTO user3 VALUES(‘李四’); #此时不会自动提交数据 INSERT INTO user3 VALUES(‘李四’); #受主键的影响，不能添加成功 ROLLBACK;
这个只会查出张三这个主键 因为受主键影响不能添加成功 回滚了，回滚到上一次commit
SELECT * FROM user3;
```sql
TRUNCATE TABLE user3; #DDL操作会自动提交数据，不受autocommit变量的影响。
SELECT * FROM user3;
BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;
INSERT INTO user3 VALUES('李四');# 默认情况下(即autocommit为true)，DML操作也会自动提交数据。
INSERT INTO user3 VALUES('李四'); #事务的失败的状态
ROLLBACK;
#这个能查出两个张三和李四 因为insert前面没有加begin 他就默认一条dml语句自动commit
#所以能查出张三李四
SELECT * FROM user3;
TRUNCATE TABLE user3;
SELECT * FROM user3;
SELECT @@completion_type;
SET @@completion_type = 1;
BEGIN;
INSERT INTO user3 VALUES('张三');
COMMIT;
SELECT * FROM user3;
INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四');
ROLLBACK;
SELECT * FROM user3;
这里我讲解下 MySQL 中completion_type 参数的作用，实际上这个参数有了种可能：
- completion=0，这是 默认情况。当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要使用 START TRANSACTION 或者 BEGIN 来开启.
- completion=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务。
- completion=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。
2.5savepoint 保存点
```sql CREATE TABLE user3(NAME VARCHAR(15),balance DECIMAL(10,2));
BEGIN INSERT INTO user3(NAME,balance) VALUES(‘张三’,1000); COMMIT;
SELECT * FROM user3;
BEGIN; UPDATE user3 SET balance = balance - 100 WHERE NAME = ‘张三’;
UPDATE user3 SET balance = balance - 100 WHERE NAME = ‘张三’;
SAVEPOINT s1;#设置保存点
UPDATE user3 SET balance = balance + 1 WHERE NAME = ‘张三’;
ROLLBACK TO s1; #回滚到保存点
SELECT * FROM user3;
ROLLBACK; #回滚操作
SELECT * FROM user3;
<a name="oShDq"></a>
#### 3.事务的隔离级别
<a name="lBzrO"></a>
##### 3.1数据准备
<a name="LLqsk"></a>
##### 3.2数据并发问题
- 脏写
对于两个事务 Session A、Session B， 如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写，示意图如下<br />
- 脏读
对于两个事务 Session A Session B, Session A 读取了已经被 Session B 更新但还没有被提支的字段。之后者<br />Session B 回滚，Session A 读取 的内容就是 临时且无效的<br />
- 不可重复读
对于两个事务Session A、 Session B, session A 读取 了一个字段，然后 Session B 更新 了该字段。之后 Session A再次读取同一个字段，值就不同了。那就意味着发生了不可重复读<br />
- 幻读 只是针对多了的情况
对于两个事务Session A、 Session B, Session A 从一个表中读取了一个字段，然后 Session B 在该表中插入了一些新的行。之后,如果 Session A 再次读取 同一个表,就会多出几行。那就意味着发生了幻读<br />
<a name="owRjK"></a>
##### 3.3sql的隔离级别
```sql
脏写>脏读>不可重复读>幻读
- read unconmited 读未提交 什么都解决不了
- read commied 读已提交 读取数据 查看数据判断是否commit了本行数据，如果commit了就读commit以后的数据，如果没有commit仅仅做了修改，就读commit以前的数据
- repeatable read 可重复读mysql默认隔离级别 这个先读，不管有没有提交都读未提交的数据
- serializable 串行化 （悲观锁）
3.4mysql支持的四种隔离级别
#查看隔离级别
5.7版本前
SELECT @@tx_isolation
5.7以后
select @@transaction_isolaction
3.5如何设置事务的隔离级别
set session transction_isolaction = 或 set global transaction_isolaction两种一种是设置当前的会话 一种是设置内存级别（global）两种 global当前会话失效后续的新会话配置
3.6数据并发问题
4.事务的常见分类
- 扁平事务
- 带有保存点的扁平事务
- 链事务
- 嵌套事务
-
14mysql事务日志
事务有4种特性: 原子性、一致性、隔离性和持久性 那是怎么实线的呢
事务的隔离性由锁机制实现
- 而事务的原子性、一致性、和持久性由事务的redo日志和undo日志来保证的
- redo log称为重做日志，提供再写入操作，恢复提交事务操作的页操作，用来保证事务的持久性
- undo log称为回归日志，回滚记录到某个特定版本，用来保证事务的原子性，一致性
- redo log：是存储引擎层(innodb)生成的日志，记录的是。物理级别 “上的页修改操作，比如页号xxx、偏移量yyy写入了 zzz数据工 主要为了保证数据的可靠性；
undo log：是存储引擎层(innodb)生成的日志，记录的是逻辑操作 日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一 条与之相反的DELETE操作。主要用于 事务的回滚 (undo log 记录的是每个修改操作的逆操作）和一致性非锁定读(undo log 回滚行记录到某种特定的版本—MVCC，即多版本并发控制)。
1.redo日志
InnoDB存储引擎是以 页为单位 来管理存储空间的。在真正访问页面之前，需要把在 磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须 先更新缓冲池 中的数据，然后缓冲池中的脏页（内存修改，磁盘没有修改） 会以一定的频率被刷入磁盘（checkPoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降大快，
1.1为什么需要redo日志
checkPoint 是按一定频率刷新到磁盘中 如果在你修改内存的数据准备做刷盘操作 但是宕机了，这样就会使commit的数据没有真正意义上的更新 这样就会出现持久性的问题
解决方法 在事务提交后及时的刷盘存在问题修改量和刷新磁盘工作量严重不成比例
- 随机io刷新较慢
1.2redo日志的好处、特点
1.好处
- redo日志降低刷盘频率
- redo日志占用的空间非常小
2.特点
参数设置：innnodb_log_bugger_size:
redo log bugger 大小，默认16km，最大值是4096m，最小值为1m
重做日志文件（redo log file）保存硬盘中，持久的
1.4redo log整体流程
innodb给出心innodb_flush_log_at_trx_commit参数，该参数控制commit提交事务时，如何将redo log buffer 中的日志刷新到redo log file中，支持三种策略
设置为0：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志同步）
- 设置为1：表示每次事务提交时都同步进行，刷盘操作
- 设置为2：表示每次事务提交时都只把redo log buffer内容写入page cache，不进行同步。由os自己决定什么时候同步到磁盘文件
1.5redo log的刷盘策略
1.6不同刷盘策略演示
page cache属于操作系统的，所以这种情况属于是mysql挂了没关系，因为已经写入操作系统了，如果操作系统挂了，那就无力回天
1.7写入redo log buffer 过程
1.补充概念：mini-transaction
MysQL把对底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对
应的B+树中插入一条记录的过程就是一条记录的过程就是一个 Mini-Transaction。一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo 日志作为一个不可分割的整体
一个事务可以包含若干条语句，每一条语句其实是由著干个mtr 组成，每一个mtr 又可以包含者干条redo日志，画个图表示它们的关系就是这样
向 log buffer 中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空问用完之后
再往下一个block中写。当我们想往 log buffer 中写入redo日志时，第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以InnoDB 的设计者特意提供了一个称之为 buf_free 的全局变量，该变量指明后续写入的redo日志应该写入到 10g buffer 中的哪个位置，如图所示
2.redo写入log buffer
一个mtr执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组，所以其实并不是每生成一条redo日志，就将其插入到log buffer中，而是每个mtr运行过程中产生的日志先暂时存到一个地方，当该mt结束的时候，将过程中产生的一组redo日志再全部复制到10g buffer中。我们现在假设有两个名为 T1、T2的事务，每个事务都包含2个mtr，我们给这几个mtr命名一下
- 事务T1的两个mtr分别称为mtr_T1_1和mtr_T1_2
- 事务T2的两个mtr分别称为mtr_T2_1和mtr_T2_2
每个mtr都会产生一组redo日志，用示意图来描述一下这些mtr产生的日志情况
不同事务可能是并发执行的，所以T1、T2之间的mtr可能交替执行的
3.redo log block的结构图
一个redo log block 由日志头 日志体 日志尾组成 日志头占12字节 日志尾占8字节所以block存储为512-12-8=492字节
1.8redo log file
1.相关参数设置
- innodb_log_group_home_dir：指定redo log 文件组所在的路径，默认为./ 默认有两个文件 ib_logfile0和ib_logfile1的文件
- innodb_log_files_in_geoup：指明redo log file的个数 ，默认为2 最大100个
- innodb_flush_log_at_trx_commit ：控制redo log 刷新到硬盘的策略，默认为1
- innodb_log_file_size:单个redo log文件大小设置 默认为48M。最大值为512g，是所有的file加在一起不超过512g，注意最大不能大过512G
2.日志文件组1
3.checkpoint
- write pos 当前记录的位置，一边写一边向后移
- checkpoint 是当前要擦除的位置，也是往后推移
如果write pos追上checkpoint，表示日志文件组满了。这个时候不能再写入新的redolog记录，mysql就需要停下来，清除一些记录
1.9redo小结
2.Undo日志
redo log是事务持久性的保证，undo log是事务原子性的保证，在事务中更新数据的前置操作其实就是先写入一个undo log
2.1如何理解undo日志
事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做，但是有些时候执行到一半会出现一些情况
- 事务执行过程中可能遇到错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误
- 手动输入rollback语句结束当前事务的执行
以上情况我们需要把数据改回原先的样子，称为回滚，看起开祥什么也没做，符合原子性
在insert、delect、update，需要做记录，把回滚时所需要的东西记下来
- 你插一条记录时，至少需要把这条记录的主键记下来，之后回滚的时候只需要把这个主键对应的记录删掉就好（对于每个insert，innodb储存引擎会完成一个delect）
- 你删除一条记录，至少要把这条记录内容都记下来，这样之后回滚时再把有这个内容组成的记录插入表里就好（对于每个delect，innodb存储引擎会执行一个insert）
- 你修改一条记录，至少要把修改这条记录前的旧值记录下来，这样之后回滚时再把这条记录更新为旧值就好了（对于每一个update,innodb储存引擎会执行一个相反的update，将修改前的记录放回去）
mysql吧这些为了回滚记录的这些内容称之为撤销日志或者回滚日志（undo log）select不需要修改数据，所以不需要undo log
2.2undo日志的作用
- 作用1：回滚数据
用户对undo日志可能 有误解：undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是 逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
这是因为在多用户井发系统中，可能会有数十、数百甚军数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一 个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将—个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。
- 作用2：mvcc
undo的另外一个作用就是mvcc，即在innodb存储引擎中mvcc的实现是通过undo来完成。当永华读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的版本信息，以此实现非锁定读取
2.3undo的存储结构
1.回滚段与undo页
- 回滚段与undo页 innodb对于undo log采取段的方式 也就是回滚段 （rollback segment）每个回滚段记录1024个 undo log segment 而每个undo log segment段进行undo页的申请
- 在innodb1.1，只有一个rollback segment 因此支持同时在线的事务限制为1024.
- 在1.1版本开始innodb支持最大128个rollback segment 所以支持同时在线的事务限制提高到了128*1024
undo页的重用
当我们开启一个事务需要写undo log的时候，就得先去undo log segment中去找到一 个空闲的位置，当有空位的时候，就去申请undo页，在这个申请到的undo 页中进行undo log的写入。我们知道mysql默认一页的大小是16k。为每一个事务分配一个页，是非常浪费的（除非你的事务非常长），假设你的应用的TPS（每秒处理的事务数目）为1000，那么1s就需要1000个页，大概需要16M的存储，1分钟大概需要1G的存储。如果照这样下去除非MysQL清理的非常勤快，否则随着时间的推移，磁盘空间会增长的非常快，而旦很多空间都是浪费的。于是undo页就被设计的可以 重用了，当事务提交时，并不会立刻删除undo页。因为重用，所以这个undo页可能混杂着其他事务的undo log。 undo log在commit后，会被放到一个链表 中，然后判断undo页的使用空间是否 小于3/4，如果小于3/4的话，则表示当前的undo页可以被重用，那么它就不会被回收，其他事务的undo 10g可以记录在当前undo页的后面。由于undo log是 离散的，所以清理对应的磁盘空间时，效率不高
2.回滚段与事务
- 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务
- 当一个事务开始的是够，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段
- 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚允许的情况下拓展新的盘区来使用
- 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间
当事务提交时，InnoDB存储引擎会做以下两件事情
将undo log放入列表中，以供之后的purge操作<br /> 判断undo log所在的页是否可以重用，若可以分配给下个事务使用3.回滚段中的数据分类
未提交的回滚数据 (uncommitted undo information)
- 已经提交但未过期的回滚数据(committed undo information）
- 事务已经提交并过期的数据 (expired undo information)
事务提交后并不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。
2.4undo的类型
- insert undo log
-
2.5undo log 的生命周期
2.详细生成过程
3.undo log 怎么回滚的
4.undo log的删除 针对insert undo log 因为insert操作的记录，只对事务本身可见，对其他事务不可见，所以undo log可以在事务提交后直接删除 不需要留下
针对update undo log 该undo log可能需要提供mvcc机制，因此不能在事务提交时就进行三处。提交放入undo log链表，等待purge线程进行删除
purge线程两个主要作用是：清理undo页 和 清除page里面带有Delete_Bit标识的数据行。在InnoDB中，事务 中的Delete操作实际上并不是真正的删除掉数据行，而是一种Delete Mark操作，在记录上标识Delete_ Bit，而 不删除记录。是一种"假删除”,只是做了个标记，真正的删除工作需要后台purge线程去完成15.锁 不管你有没有事务，都会产生锁，事务的隔离级别是依赖锁产生的
1.概述
2.mysql并发事务访问相同记录
2.1读读情况
2.2写写的情况
写-写 会出现脏写的情况 排队执行
这就是锁不加锁
- 获取锁成功，或者加锁成功
-
2.3读写或写读情况
怎么解决脏读、不可重复读、幻读这些问题，有解决方案
方案一：读操作利用版本并发控制（MVCC），写操作进行加锁
所谓的 MVCC，就是生成一个Readview，通过ReadView找到符合条件的记录版本（历史版本由 undo日志构建）。查询语句只能 读到在生成ReadView之前 己提交事务所做的更改，在生成ReadView之前末提交的事务或者之后才开启的事务所做的更改是看不到的。而 写操作 肯定针对的是 最新版本记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。
- 方案二：读和写都采用加锁方式
小结对比
采用MVCC方式的话，读-写操作彼此不冲突；性能更高
采用加锁方式，读-写操作彼此需要排队执行，影响性能
一般情况使用MVCC来控制读-写操作并发执行问题，特许情况是读写都加锁3.锁的不同角度分类
3.1从数据操作的类型划分：读数、写锁
共享锁/读锁 读-读 S锁
-
1.锁定读
对读取的记录加S锁
select........for share对读取的记录加X锁
select.......for update；nowait
如果遇到锁，就会报错，直接返回 select.......for update nowait；skip locked
select......for update skip locked; 如果遇到锁，会跳过锁，把没有上锁的记录查出来2.写操作
delete、update、insert
delete： 就是找到对应的b+树的记录位置，获取记录的x锁，再执行delete mark操作，就是软删除
update 三种情况
insert：一般不需要加锁，通过一种称之为 隐式锁 的结构来保护这条新插入的记录在本事务提交前不被别的事务访问
3.2从数据操作的粒度划分：表级锁、页级锁、行级锁
1.表锁
不依赖存储引擎 开销最大，粒度比较大，并发差
1.表级别的s锁，x锁 DDL出现阻塞的行为是因为ddl修改表结构，这样就会阻塞，他会加一个元数据锁MDL innodb支持行级页级锁， myisam只支持表锁
可以手动去加锁 加读锁 加写锁
innodb 一般不会加表锁 ，因为有更细粒度的锁
2.意向锁
innodb支持 多粒度锁，它允许行级锁和表级锁，而意向锁就是其他的一种表锁
如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了
意向锁的存在就是为了协调行锁和表锁的关系，支持多粒度锁的并存
- 意向锁是一种不与行级锁冲突表级锁，这一点非常重要
- 表明某个事务正在某些行持有锁或该事务准备去持有锁
- 意向共享锁
- 意向排它锁
3.自增锁 就是为了主键唯一且自增 设置的锁
1.simple inserts（简单插入）
2.bulk inserts （批量插入）
3.mixed-mode inserts(混合模式插入)
4.元数据锁（MDL锁)
当对一个表做增删改查操作的时候，加MDL读锁：当要对表做结构变更操作的时候，加 MDL写锁。
2.innodb行锁
优点：锁定丽都小，发生冲突概率低，可以实现高并发
缺点：锁的开销比较大，加锁比较慢，容易出现死锁
- 记录锁 s锁（共享锁） 和x锁（排他锁）
- 间隙锁 就是解决幻读提出来的（GAP锁 Gap Lock）
可以理解为在3和8之间加了一个锁，这个锁为了防止有人来insert记录，造成幻影记录和幻读
3.临键锁
就是记录锁和gap锁的合体
4.插入意向锁
插入的语句的也需要上锁，这就是插入意向锁
3.页锁
3.3从对待锁的态度划分：悲观锁，乐观锁
1.悲观锁
2.乐观锁
- 乐观锁的版本号机制
- 乐观锁的时间戳
3.两种锁的使用场景
- 乐观锁适合读操作多
-
3.4加锁的方式划分：显式锁、隐式锁
3.5全局锁
3.6死锁
1.概念
两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁
2.产生必要条件 两个以上事务
- 每个事务都有锁，并新申请锁
- 锁资源同时只能被同一个事务持有或者不兼容
- 事务之间因为持有锁和申请锁导致彼此循环等待
如何处理死锁
- 等待,直到超时 回滚（innodb_lock_wait_timeout=50s）
- 使用死锁检测进行死锁处理 主动检测机制
这个会检测是否有成环链的情况，如果有成环的情况下，就innodb选择最小的undo量最小的事务回滚，其他继续执行
4.如何避免死锁
- 合理设计索引，使业务sql尽可能通过索引定位更少的行，减少锁竞争
- 调整业务逻辑sql执行顺序，避免update、delete长时间持有锁
- 避免大事务，将大事务，拆解成小事务
-
4.锁的内存结构
5.锁监控
innodb_row_lock%16多版本并发控制
1.什么是MVCC
MVCC 多版本并发控制 MvCC 是通过数据行的多介版本管理来实现数据库的 并发控制 只有innodb支持mvcc机制
2.快照读和当前读
3.复习
3.2隐藏字段、undo log版本链
trx_id :每次一个事务对某条聚筷索引记录进行改动时，都会把该事务的 事务id 赋值给 trx_id隐藏列。
- roll_pointer:：每次对某条聚筷索引记录进行改动时，都会把旧的版本写入到 undo日志中，然后这个隐藏列就相当于一个指针，可以通过亡来找到该记录修改前的信息
->
4.MVCC实现原理之ReadView
MVCC的实现依赖于：隐藏字段、undo log 、read view
4.1什么事readview
ReadView 就是事务A在使用MVCC机制进行快照读操作时产生的读视图（read view和事务时一对一的）。当事务启动时，会生成数据库系统当前的
一个快照，InnoDB 为每个事务构造了一个数组，用来记录并维护系统当前活跃事务 的ID（“活跃’指的就是，启动了但还没提交）
4.2设计思路
使用Read uncommited隔离级别 和两种不使用read view机制
使用Serialzable隔离级别
使用read view机制的就两种隔离级别
- read committed
- repeatable read
- 都必须保证读到 已经提交了的 事务修改社的记录。假如另一个事务已经修改了记录但是尚末提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一 下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题
read view有4个重要的内容
- creator_trx_id 创建read view事务的id
- trx_ids 表示在生成read view时当前系统中活跃的读写事务的事务id列表
- up_limit_id 活跃的事务最小的事务id
- low_limit_id 表示生成readview时系统中应该分配给下一个事务的id值。low_limit_id是系统最大的事务id值，这里注意是系统中的事务id，需要区别于正在或缺的事务id
注意：low_limit_id并不是tx_ids中的最大值，事务id是递增分配的。比如，现在有id为1，2I 3这三个事 务，之后id为3的事务提交了。那么一个新的读事务在生成Readview时，trx ids就包括1和2，up_limit_id 的值就是1， low_limit_id的值就是44.3read view的规则
4.4MVCC整体操作流程
- 首先获取事务的自己版本号，也就是事务id
- 获取readview
- 查询的带的数据，然后于read view的事务版本号进行比较
- 如果不符合readview规则就需要从undo log 中获取历史快照
- 最后返回符号规则的数据
READ COMMITTED 隔离级别的read view的流程
5.举例说明
5.1Read committed隔离级别下
read committed ：每次读取数据前都生成一个readview
现在有两个事务id分别为10、20的事务在执行
Transaction 10
begin；
update student set name = ‘李四’ where id =1 ；
update student set name = ‘王五’ where id =1 ；
Transaction 20
begin；
更新别的一些表的记录。。。为什么要强调这个 是因为，这个操作的这个表中只有一条记录，上面那个事务操作数据库了，下边那个操作的同一条数据就是
显示不出来来读写这个mvcc机制了 而且事务的id是系统分配的 insert update delete 这些操作是被自动分配一个trx_id（自增）
而 select 不会被分配会变成0
这里开始操作
使用read committed隔离级别
begin；
select * from student where id= 1 得到值为‘张三’；
解释为什么是张三
这里可以看到，上面有两个事务没有提交 一个是为10 的事务 一个是为20的事务 当第三个事务开启的时候，他会生成一个快照，由于第三个事务是一个查询的语句所以 他的creator_trx_id是0 为什么是0 ？（你他妈要是能问出这个问题，老子砍死你）他的read view的trx_ids列表内容就是[10,20]别问我为什么图不一样，问就是自己想，up_limit_od为10，low_limit_id为21，这样我们活跃（未提交）的事务是10-21 看图 trx_id是不是10 是不是在10-20之间，是就跳过，再往下看8是不是10-20之间，发现不是，所以这就是已经提交的事务，所以，查出来的记录就是’张三’
Transaction 10
begin；
update student set name = ‘李四’ where id =1 ；
update student set name = ‘王五’ where id =1 ；
commit；
这个时候我们提交一下 再讲Transaction 为20的事务操作一下
Transaction 20
begin；
update student set name = ‘钱七’ where id =1 ；
update student set name = ‘宋八’ where id =1 ；
5.2Repeatable read隔离级别下
Transaction 10
begin；
update student set name = ‘李四’ where id =1 ；
update student set name = ‘王五’ where id =1 ；
Transaction 20
begin；
更新别的一些表的记录。。。为什么要强调这个 是因为，这个操作的这个表中只有一条记录，上面那个事务操作数据库了，下边那个操作的同一条数据就是
显示不出来来读写这个mvcc机制了 而且事务的id是系统分配的 insert update delete 这些操作是被自动分配一个trx_id（自增）
而 select 不会被分配会变成0
这里开始操作
使用read committed隔离级别
begin；
select * from student where id= 1 得到值为‘张三’；
第一次和read committed这个一样
Transaction 10
begin；
update student set name = ‘李四’ where id =1 ；
update student set name = ‘王五’ where id =1 ；
commit；
这个时候我们提交一下 再讲Transaction 为20的事务操作一下
Transaction 20
begin；
update student set name = ‘钱七’ where id =1 ；
update student set name = ‘宋八’ where id =1 ；
使用Repeatable read隔离级别
begin；
select * from student where id= 1 得到值为‘张三’；
这个场景下就不一样了 read committed这个隔离级别，事务每次都会新生成一 read view 这样才能确保每次read committed 而repeatable read不一样， 因为他是可重复读，所以他就只生成一个read view，所以查看的都是 ‘张三’
Transaction 10
begin；
update student set name = ‘李四’ where id =1 ；
update student set name = ‘王五’ where id =1 ；
commit；
这个时候我们提交一下 再讲Transaction 为20的事务操作一下
Transaction 20
begin；
update student set name = ‘钱七’ where id =1 ；
update student set name = ‘宋八’ where id =1 ；
commit；
使用Repeatable read隔离级别
begin；
select * from student where id= 1 得到值为‘张三’；
5.3如何解决幻读的
6.总结
- read committed 每次select都会生成readview
- repeatable read只在第一次进行select操作生成readview ，之后不生成就用第一次生成的read view
通过 MVCC 我们可以
- 读写之间阻塞的问题。-通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力
- 降低了死锁的概率。这是因为 MvCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行
- 解决快照读的问题。当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，不能看到这个时间点之后事务提交的更新结果
17.其他数据库日志
1.mysql支持的日志
mysql 8.0日志官网：https://dev.mysql.com/doc/refman/8.0/en/server-logs.html
1.1日志类型
二进制日志、错误日志、通用查询日志、慢查询日志、中继日志、数据定义语句日志
- 慢查询日志：
- 通用查询日志：
- 错误日志：
- 二进制日志：记录更改数据的语句，可以用于主从服务的数据同步，以及服务器遇到故障数据恢复
- 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作
-
1.2日志的弊端
日志会降低mysql数据的性能 要分资源做记录
-
2.慢查询日志
3.通用查询日志
3.1查看状态
3.2查看日志
3.3设置日志
3.4停止日志
3.5删除\刷新日志
4.错误日志
4.1启动日志
mysql数据库中，错误日志功能默认是开启的 而且无法关闭的
4.2查看日志
4.3删除\刷新日志
4.4mysql8.0新特性
5.二进制日志
binlog日志比较重要的日志 记录所有的ddl和dml的语句 但是不记录没有修改数据的语句包括（select show）等 以事件的形式记录在二进制文件中
binlog主要应用场景： 用于数据恢复
- 用于数据复制
数据备份、主备、主主、主从都离不开binlog binlog日志每次mysql重启一次，就会生成一个文件
5.1日志保存
- 设置保存时间
- 设置文件大小
- 设置名字
5.2查看日志
mysqlbinlog -v "/路径"
5.3使用日志恢复数据
5.4删除二进制日志
- 删除指定文件
-
5.5其他场景
二进制文件可以恢复数据，但是遇到大量数据，会有弊端
我们可以使用主从复制来配置服务器，甚至是一主多从的架构保证数据的安全性6.再谈二进制日志文件
6.1写入机制
binlog吸入时机也非常简单，事务执行中，先吧日志写在binlog cache ，事务提交的时候，再把binlogcache写入binlog文件中，因为binlog不能被拆分，为了一次写入，系统会给每一个线程分配一块内存作为binlog cache
page cache是系统中的文件
这个有风险，如果操作系统挂了， 就没有记录了
6.2b binlog 于redolog对比
redo log 它是物理日志，记录内容是在“在某个数据页做了什么修改”，属于innodb储存引擎层产生的
- 而binlog是逻辑之日，记录内容的语句的原始逻辑，类似于“给ID=2这一行的c字段加1”属于mysql server层
- 虽然都是持久化保证，但是侧重点是不同的
- redo log让innodb存储引擎拥有了崩溃恢复功能
- binlog保证了mysql集群架构的数据一致性
6.3两阶段提交
在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机 不一样
7.中继日志
18.主从复制
1.主从复制概述
1.1如何提升数据并发能力
使用缓存
1.2主从复制的作用
- 读写分离
- 数据备份
-
2.主从复制的原理
复制三部分 - master将写操作记录到二进制日志，叫做二进制日志时间
- slave将master的binary log events拷贝到它的中继日志（relay log）
-
2.2复制的基本原则
slave只能有一个master
-
3.一主一从架构搭建
3.1配置文件
binlog格式设置：
statement模式 将没一条修改的sql语句记录到binlog中，默认是binlog格式
binlog——format = statementrow模式 记录的是修改的哪几行，这个记录更详细，但是占用更大空间
binlog_format= row
mixed模式 statmemt和row的混合
binlog_format =mixed4.同步数据一致性问题
要求
读库和写库的数据哟之（最终一致）
- 写数据要写在写库
-
4.1理解主从延迟问题
4.2主从延迟问题的原因
4.3如何减少主从延迟
4.4如何解决一致性问题
异步复制
- 半同步复制
保证有一个从库写入成功，就叫半同步复制
- 组复制（MGR）
5.知识延展
19.数据库备份与恢复
1.物理备份和逻辑备份
- 物理备份：备份数据文件，恢复快，占用空间比较大
逻辑备份：myqldump 实际就是备份sql语句 速度慢，但是占空间小
2.mysqldump实现逻辑备份
2.1备份一个数据库
2.2备份全部数据库
2.3备份部分数据库
2.4备份部分表
2.5备份单表的部分数据
2.6排除某些表的备份
2.7只备份结构或者只备份数据
2.8备份中包含储存过程、函数、事件
3.mysql命令恢复数据
3.1单库备份中恢复单库
3.2全量恢复
3.3从全量中恢复部分库
3.4从单库恢复部分表
7.数据库迁移
7.1迁移方案
7.3迁移注意点
-
7.4迁移
8.删库，还能干点啥
8.1delect：误删行
使用flashback恢复
原理：修改binlog内容，拿回库重放1
8.2truncate/drop：误删库、表
8.3预防使用truncate/drop
权限分离
- 制定操作规范
- 设置延迟复制备库
8.5rm：误删mysql实例
删除一个实例，小事，再起节点就好完结撒花！！！！！
若有收获，就点个赞吧
0 人点赞
