mysql学习笔记

最新推荐文章于 2024-09-11 06:54:51 发布

九友九

最新推荐文章于 2024-09-11 06:54:51 发布

阅读量653

点赞数 12

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44275684/article/details/141090482

版权

数据库基础知识

范式

第一范式: 属于第一范式关系的所有属性都不可再分，即数据项不可分。
第二范式: 要求实体的属性完全依赖于主关键字。(其余的字段和主键关系很强)
要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。(订单中存放产品名称) ,要进行关联

反范式

为了性能和读取效率而适当的违反对数据库设计范式的要求
为了查询的性能(索引命中会多)，允许存在部分（少量）冗余数据
对比:
反范式的实现
1. 缓存(缓存本身就是冗余)与数据汇总
  1. 冗余经常要使用的别的表的字段,不用关联
  2. 冗余方便统计的字段
2. 计算器
  1. 因为更新是只能更新一条数据,影响并发,可以利用槽的概念增加更新的数据条数,提高并发

优化

字段类型
1. 整数类型
  1. tinyint (8位)、smallint (16位)、mediumint (24位)、int (32位)、 bigint(64位) , 1、2、3、4、8个字节
  2. integer 和Int 是一样的, int(UNSIGNED)无符号会存更多的正数
  3. int(1) 和int(11) 存储的大小是一样的,只是为了显示字符的个数
2. 实数类型
  1. float(4个字节)、double(8个字节)、decimal(65个数字)
  2. DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存放的。所以CPU不支持对DECIMAL的直接计算，只是在MySQL中自身实现了DECIMAL的高精度计算
  3. 精度不敏感和需要快速运算的时候，选择FLOAT和 DOUBLE。
  4. 在数据量比较大的而且要求精度时，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可
3. 字符串类型
  1. var(定长)、varchar(不定长)、blob(二进制)、text(字符)、enum、set
  2. char (4)定长更省空间。没有记录的长度 (定长的CHAR类型不容易产生碎片)
  3. vachar (255)不定长：1-2额外字节记录字符串的长度。
  4. char和varchar选择
    1. 字符串列的最大长度比平均长度大很多,变化很大用varchar
    2. 定长的字符用char
  5. BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。对索引不友好,需要单独进行索引优化, 每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值

CREATE TABLE
enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL);

  6. 枚举中存储的整数不是字符串(类似于java的枚举),可以节约空间,类型选择enum,在值中定义具体的枚举内容,mysql在查询中进行显示的优化

日期和时间类型 (mysql的最小时间粒度为秒)
1. datetime 、timestamp (跟时区有关系)
2. datetime 存储日期范围：1001年~9999年
3. timestamp 存储日期范围：1970年~2038年,并且跟时区有关系。
4. 如果需要存储比秒更小粒度的日期和时间值, 可以使用BIGINT类型存储微秒级别的时间截
字段类型优化
1. 更小的通常更好
  1. 是有一个类型既可以用字符串也可以使用整型,优先选择整型.因为字符串牵涉到了字符集及校对规则等。
2. 避免为null
  1. 通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值(对系统的提升很小,但尽量)
  2. 因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节
  3. 如果计划在列上建索引，就应该尽量避免设计成可为NULL的列
字段命名规范
1. 不使用复数名词,因为数据库中的数据原本就是多条
2. 禁用保留字，如desc、range、match之类
3. 索引命名, 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名

索引

索引
1. InnoDB引擎常见的索引: B+树索引全文索引哈希索引
2. 哈希索引
  2. 哈希索引只能等值查询(查询很快),不能实现范围查询,也不能排序
  3. 使用组合索引的时候不能只通过一个字段查询,hash冲突
3. B+树索引(利用二分查找)
  1. B+树是多叉树
  2. 二分查找和顺序查找
    2. 顺序查找平均查找次数为（1+2+3+4+5+6+7+8+9+10)/10=5.5次。而二分查找法为(4+3+2+4+3+1+4+3+2+3)/10=2.9次。在最坏的情况下，顺序查找的次数为10，而二分查找的次数为4
  3. 树: 二叉树二叉查找树平衡二叉树(AVL树)
  4. B+树 (多叉平衡查找树)
    1. B树
    2. B+树
    3. B*树非叶子节点也用指针相连(oracle实现)
    4. https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 演示地址
    5. 标准的B+数叶子节点单链表。MySQL实现升级(双链表<—>)
    6. 右边的比左边的大,高度差不超过1
    7. 所有的元素都在叶子结点,右边大于左边
    8. 叶子结点按照链表相连
    9. 非叶子节点只保存索引和下一个页的指针信息
    10. 类似于跳表

数据库索引

B+树索引

聚集索引/聚簇索引
1. 通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。
2. 没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键
辅助索引/二级索引
1. 一个索引就会有一个B+树
2. 叶子节点并不包含行记录的全部数据,内容是相应行数据的聚集索引键
3. 查询具体的行内容需要回表
4. 回表的记录越多，使用二级索引的性能就越低,回表的次数过多,会进行全表扫描(查询优化器)
联合索引/复合索引
1. 联合索引叶子节点的内容(上边是note的索引内容):
2. 最左优先排 : 先按note列进行排序创建索引,note相同的内容的字段b的内容再进行排序
3. 叶子节点包含多个数据和主键
覆盖索引
1. 辅助索引中的列就可以将数据全部查询到,不需要进行回表
B+树的查找次数,取决于B+树的高度, B+树的高度一般为3、4层,故需要3、4次的IO查询

哈希索引

Hash索引知乎已查询一次
InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)
自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预

索引的作用

一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引,如果使用多个二级索引会导致回表的次数过多,导致查询性能降低
索引的列尽量小
创建索引应该选择选择性/离散性高的列, (离散性)不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值

前缀索引

针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度，需建立前缀索引。
语法：Alter table tableName add key/index (column(X)), X 表示第几个前缀
前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

后缀索引

MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引

索引的建立

只为用于搜索、排序或分组的列创建索引
只为出现在WHERE 子句中的列、连接子句中的连接列创建索, 又或者为出现在ORDER BY或GROUP BY子句中的列创建索引

三星索引

索引将相关的记录放到一起(缩短查询的叶子节点的范围)则获得一星（比重27%）查询的条件是索引的左字段可以实现
如果索引中的数据顺序和查找中的排列顺序一致则获得二星(查询出来的顺序和索引的顺序一致)（排序星）（比重23%） order_by的字段是索引的左字段可以实现
如果索引中的列包含了查询中需要的全部列则获得三星（覆盖索引,不需要回表）（比重50%）
优先不需要回表,顺序和索引一致和缩短查询的叶子节点范围看情况选择

Mysql调优

调优类型

架构调优: 数据合适不合适 ,es ,mq ,redis ; 是否需要读写分离, 数据的安全
mysql调优: 表设计,sql语句优化,索引使用
硬件和os系统调优: SSD硬盘,磁盘整列(RAID)

查询优化

慢查询原因

核心原因: 查询的数据太多了
优化数据的访问: 是否访问了不需要的数据(先返回全部结果集再进行计算) 例如 limit 10000:20
总是查询出所有的列(需要进行回表操作,会产生io操作,会降低查询性能), 数据做缓存可以查询所有的列
重复查询相同的数据 (可以用缓存)

慢查询标准

响应时间: 响应时间是两个部分之和:服务时间和排队时间
扫描的行数和访问类型(在EXPLAIN语句中的type列反应了访问类型)

解决方案

使用覆盖索引
优化数据库的表结构
重写复杂的sql

慢查询日志

慢查询是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志
show VARIABLES like ‘slow_query_log’; set GLOBAL slow_query_log=1; 开启慢查询
show VARIABLES like ‘%long_query_time%’; set global long_query_time=0; 设置慢查询的时间分界
show VARIABLES like ‘%slow_query_log_file%’; 查看慢查询存放日志的位置
show VARIABLES like ‘%log_queries_not_using_indexes%’; 没有使用索引的数据是否存放到慢sql中
慢查询工具: mysqldumpslow命令,对慢sql日志进行分析过滤

执行计划

union和union all
1. UNION 会合并两个或多个 SELECT 语句的结果集，但会去除重复的行 (会用到临时表)
2. UNION ALL 会合并两个或多个 SELECT 语句的结果集，包括重复的行
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id (in查询mysql优化器有时会将sql优化为连接查询) id为null说明使用了临时表
select_type
1. SIMPLE：简单的select查询，不使用 union及子查询
2. PRIMARY：最外层的 select查询
3. UNION： UNION 中的第二个或随后的 select查询，不依赖于外部查询的结果集
4. UNION RESULT： UNION结果集
5. SUBQUERY：子查询中的第一个select查询，不依赖于外部查询的结果集
6. DEPENDENT UNION (dependent union)： UNION 中的第二个或随后的 select 查询，依赖于外部查询的结果子查询中的union
7. DEPENDENT SUBQUERY：子查询中的第一个 select查询，依赖于外部查询的结果集
8. DERIVED：用于from 子句里有子查询的情况。MySQL会递归执行这些子查询，把结果放在临时表里。
type
1. 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type 列就明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：
2. system(查询系统表) > const(主键或者普通索引查询,只匹配到一条数据) > eq_ref(连接查询,被驱动表通过主键等值(主键)匹配) > ref(普通索引) > fulltext(全文索引)> ref_or_null(普通索引查询,普通索引中可以为null,查询条件中有为null) > index_merge (索引合并,不是用到两个索引) > unique_subquery(子查询和外部进行了关联) > index_subquery > range(进行了索引的范围查询) > index (使用了覆盖索引,但需要扫描全部的记录)> ALL(全表扫描)
3. 出现比较多的是system>const>eq_ref>ref>range>index>ALL
4. 一般来说，得保证查询至少达到range级别，最好能达到ref
possible_keys与key
1. possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引
key_length
1. key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度,可以分析联合索引用了哪几个
ref
1. 字段后匹配的内容比如 where order_no = ‘a’, ‘a’ 表示匹配的就是const(常量)
2. 这个也可能是in subQuery rang
rows : 扫描的行数
filtered: 使用了索引匹配到数据后,根据普通where条件匹配到的百分比,普通条件过滤后的百分比
Extra: 扩展信息,对sql的概括
Using where: 表示在检索行之后，MySQL 服务器将过滤它们，即它将使用 WHERE 子句来排除不满足条件的行。
Using temporary: 表示 MySQL 需要使用临时表来存储结果集，这通常发生在 GROUP BY 或 DISTINCT 操作中，如果结果集太大而无法放入内存，则可能会使用磁盘上的临时表。
Using filesort: 表示 MySQL 需要对结果进行额外的排序操作，而不是通过索引顺序来检索行。这通常是因为 ORDER BY 或 GROUP BY 子句中的列不是按索引顺序排列的。

索引使用

查询优化器

一条sql在Mysql中的执行流程
优化: 优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等

索引使用

不在索引上进行任何操作,否则会使索引失效 (表达式或者函数) ,可以将函数或者表达式优化
尽量全值匹配, 搜索的条件和联合索引的条件全部匹配
最佳左前缀匹配, 搜索条件中必须出现左边的列才可以使用到这个B+树索引
范围条件放最后(放到右边),联合索引如果左边的字段使用了范围,后边的索引将不会生效
覆盖索引尽可能使用,尽量不要使用*,让覆盖索引生效
不等于要慎用, mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
Null/Not有影响 (设计表时列尽可能的不要声明为null)
1. is not null容易导致索引失效，is null则会区分被检索的列是否为null，如果是null则会走ref(普通索引)类型的索引访问，如果不为null，也是全表扫描。可以把not null转为in查询
字符类型要加引号,如果字符串不加引号会使索引失效,mysql会进行类型转换
like条件, 百分号不要放最前边
1. 使用覆盖索引,可以解决这个问题
2. 一个 insert_time ,order_status, expire_time 的联合索引
3. explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time like ‘%18:35:09’;
使用or关键字
如果or的字段是同一个字段,索引会生效
如果or的字段一个有索引,一个没有索引,会使索引失效, 可以把or转换为union all,至少可以让一个索引生效,使用覆盖索引也可以解决这个问题
如果or的字段两个都有索引,两个索引都会生效使用
使用索引列来做排序和分组
用来排序的列尽可能在一个联合索引里边,如果排序的两个字段都有各自的索引,联合排序会使索引失效
尽可能按照主键的顺序插入数据
插入一个比较小的数据,会使数据的叶变化大,B+树的转化会变大
优化Count查询
COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数
count统计一个具体的字段时,只统计这个字段不为null的行
优化limit分页查询
在偏移量非常大的时候,会影响性能
优化方案加where条件判断ID where id>xxx,然后limit 0 到 xxx
关于Null的特别说明
有的认为NULL值代表一个未确定的值，MySQL认为任何和NULL值做比较的表达式的值都为NULL，包括selectnull=null和select null!=null;
有的任务NULL表示一个值,count(某个字段),会将NULL值排除掉

事务和隔离级别

概念

事务特性: 原子性(要不全部成功,要不全部失败) 一致性(总体转账前后整体的金额不变) 隔离性(事务之间不能互相影响) 持久性(做的修改要永久的保存在数据库中)
事务并发引起的问题
1. 脏读: 当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。
2. 不可重复读: 在一个事务中执行查询内容,查询到的结果不一致
3. 幻读: 在一个事务中执行查询条数,查询到的结果不一致
事务隔离级别
1. 未提交读, 可能发生脏读、不可重复读和幻读问题
2. 已提交读, 可能发生不可重复读和幻读问题，但是不可以发生脏读问题
3. 可重复读, 可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。(mysql的可重复读解决了幻读的问题,但没完全解决)
4. 可串行化, 各种问题都不可以发生
设置隔离级别
1. SHOW VARIABLES LIKE ‘transaction_isolation’;
2. SELECT @@transaction_isolation;
3. SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
事务执行
1. 开始: begin START TRANSACTION
2. 回滚: rollback ; 提交: commit
保存点
1. 设置保存点: SAVEPOINT 保存点名称;
2. 回滚到保存点: ROLLBACK TO SAVEPOINT 保存点名称;
隐式提交
1. DDL语句(定义或修改数据库对象的数据定义)
2. 在一个会话里，一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务
3. …
MVCC
1. trx_id 事务ID undo日志,每一条操作会记录一条undo日志
2. undo日志,编号从0开始
3. 版本链: trx_id roll_poniter 回滚指针
4. 1. 读已提交每次生成的readView都不一样,第二次的80已经提交了,readView的m_ids中没有80了,所以看到了80的数据
  2. 可重复读,因为80虽然提交了,但还在m_ids中,所以还是看不到
5. ReadView的概念
  1. m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
  2. min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
  3. max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4
  4. creator_trx_id：表示生成该ReadView的事务的事务id。
6. 读已提交: 隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。
7. 可重复读: 只会在第一次执行查询语句时生成一个ReadView
8. 当执行查询sql时会生成一致性视图read-view
  1. 组成内容: min_trx_id [m_ids] max_trx_id creator_trx_id
9. 版本链比对规则：(一个事务在读取数据时,会生成一个ReadView视图,然后将版本链中的一条条数据依次根据readView视图判断是否可见)
  1. 如果被访问数据的事务ID（trx_id）等于creator_trx_id，说明当前事务正在访问自己修改过的数据，因此该版本的数据对当前事务可见。
  2. 如果被访问数据的事务ID小于min_trx_id，说明生成该版本数据的事务在当前事务开始之前已经提交，因此该版本的数据对当前事务可见。
  3. 如果被访问数据的事务ID大于或等于max_trx_id，说明生成该版本数据的事务在当前事务开始之后才启动，因此该版本的数据对当前事务不可见。
  4. 如果被访问数据的事务ID在min_trx_id和max_trx_id之间，则需要进一步判断该事务ID是否在活跃事务ID列表（m_ids）中：
    1. 如果在m_ids列表中，说明生成该版本数据的事务在当前事务开始时仍然活跃（即未提交），因此该版本的数据对当前事务不可见。(解决了脏读的问题)
    2. 如果不在m_ids列表中，说明生成该版本数据的事务在当前事务开始之前已经提交，因此该版本的数据对当前事务可见。(A,B,C三个事务,查询的时候A,B没有提交,C提交了,保证能够查询到C的数据,因为C的数据小于最大的事务ID,也不在m_ids链表中)
    3. 如果根据以上规则判断当前版本的数据不可见，系统会通过数据行的回滚指针（DB_ROLL_PTR）查找Undo Log链中的旧版本数据，并重复上述匹配逻辑，直到找到对当前事务可见的数据版本或确定该记录对当前事务完全不可见。
幻读问题: 事务A中: 查询一个数据发现没有,事务B中插入这条数据,然后提交事务
1. 然后事务A更新这条数据,发现更新成功了
2. 事务A中第一次查询查到的版本链为空,第二次查询的时候版本链有数据了

Mysql的锁

并发方案

读操作MVCC(并发读), 写操作进行加锁 (读写能够并行操作)
读和写都进行加锁 (读写不能并行操作)
锁定读: select for update (独占锁) lock in share mode (共享锁) update (排他锁) insert (排他锁/独占锁)、delete (排他锁)
insert : 一般情况下，新插入一条记录的操作并不加锁，InnoDB通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。当然，在一些特殊情况下INSERT操作也是会获取锁的
delete: 对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。
update:
1. 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化, 看成是一个获取X锁的锁定读。
2. 存储空间在修改前后发生变化,先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉,最后再插入一条新记录,这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。
3. 如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作

锁的种类

行锁: 锁一层
表锁: 锁整个楼
意向锁: 解决效率的问题 (防止遍历数据查看是否有锁)
1. 意向共享锁，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
2. 意向独占锁，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

MySQL中的行锁和表锁

MyISAM: 只支持表锁, 并不支持事务
表锁上锁: lock tables teacher read ; lock tables teacher write
元数据锁: DDL语句
AUTO-INC锁: 自增的方式插入, 上了表级别的锁(如果能够确定插入多少数据,会上轻量级锁)
行锁 : 索引上加才是行锁,不在索引上就是表锁
根据执行计划判断不需要使用行锁,也会使用表锁
间隙锁:
1. 图中为2~6和 6 ~ 10的记录加了gap锁，意味着不允许别的事务在这条记录前后间隙插入新记录

死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象
mysql会自动处理掉死锁
查看死锁 show engine innodb status\G
set global innodb_status_output_locks = ON; 打开死锁的输出

MySQL8新特性

MySQL 8.0 全内存访问可以轻易跑到 200W QPS，I/O 极端高负载场景跑到 16W QPS

账户与安全

到了MySQL8中，用户创建与授权语句必须是分开执行，之前版本是可以一起执行。
grant all privileges on . to ‘lijin’@‘%’ identified by ‘Lijin@2022’; 之前的创建用户和授权一起执行
8.0 创建用户和授权分开
1. create user ‘lijin’@‘%’ identified by ‘Lijin@2022’;
2. grant all privileges on . to ‘lijin’@‘%’；
认证插件更新
1. MySQL 8.0中默认的身份认证插件是caching_sha2_password，替代了之前的mysql_native_password。
2. show variables like ‘default_authentication%’;
3. 可以通过在MySQL的服务端找到my.cnf的文件，把相关参数进行修改
4. MySQL 8.0开始允许限制重复使用以前的密码 --(show variables like ‘password%’😉

索引增强

MySQL 8.0开始支持隐藏索引 (invisible index)，不可见索引
应用场景: 软删除(索引的软删除)、灰度发布。
软删除：就是我们在线上会经常删除和创建索引，如果是以前的版本，我们如果删除了索引，后面发现删错了，我又需要创建一个索引，这样做的话就非常影响性能。在MySQL8中我们可以这么操作，把一个索引变成隐藏索引（索引就不可用了，查询优化器也用不上），最后确定要进行删除这个索引我们才会进行删除索引操作。
灰度发布：也是类似的，我们想在线上进行一些测试，可以先创建一个隐藏索引，不会影响当前的生产环境，然后我们通过一些附加的测试，发现这个索引没问题，那么就直接把这个索引改成正式的索引，让线上环境生效。
创建隐藏索引: create index j_idx on t1(j) invisible; --创建一个隐藏索引
set session optimizer_switch="use_invisible_indexes=on’; --在会话级别设置查询优化器可以看到隐藏索引
隐藏与正常变更:
1. alter table t1 alter index j_idx visible; --变成可见
2. alter table t1 alter index j_idx invisible; --变成不可见(隐藏索引)

降序索引

MySQL 8.0开始真正支持降序索引 (descendingindex) 。只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。另外MySQL8.0不再对GROUP BY操作进行隐式排序。
create table t2(c1 int,c2 int,index idx1(c1 asc,c2 desc)); 创建降序索引
1. show create table t2\G 查看建表信息
作用
1. explain select * from t2 order by c1,c2 desc; 执行这条sql
2. mysql8.0 Using index 完全使用了索引
3. mysql5.7: Using index; Using filesort; 使用了文件扫描 (性能相对较差)

函数索引

如果在查询中加入了函数，索引不生效，所以MySQL8引入了函数索引
8.0.13开始支持在索引中使用函数（表达式)的值。支持降序索引，支持JSON 数据的索引函数索引基于虚拟列功能实现。
create index func_idx on t3( (UPPER(c2)) ); --一个大写的函数索引(函数索引还要加一个括号)
1. show index from t3\G 展示表的索引信息
底层实现: 函数索引在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。

通用表表达式

MySQL8.0开始支持通用表表达式(CTE）(common table expression)，即WITH子句。
递归调用:

WITH recursive cte(n) as 
( 
  ## 这个是第一次的执行结果
  select 1
  # 下边是后边的执行结果
  union ALL
  # 执行的时候会把上一次的值传入到下一次的执行中
  # n < 10 是递归结束的条件
  select n+1 from cte where n<10
)
select * from cte;

案例: (展示层级关系)
1. 一个staff表，里面有id，有name还有一个 m_id，这个是对应的上级id。数据如下：
2. 如果我们想查询出每一个员工的上下级关系，可以使用以下方式
3. sql语句

with recursive staff_view(id,name,m_id) as
(
  ## 查询第一条数据
  select id ,name ,cast(id as char(200)) 
 from staff where m_id =0
  # 后边数据的查询
 union ALL 
  # 查询的时候关联上一个数据(join  staff) 查询出数据concat(s1.m_id,'-',s2.id)
 select s2.id ,s2.name,concat(s1.m_id,'-',s2.id)
 from staff_view as s1 join  staff as s2
 on s1.id = s2.m_id
)
select * from staff_view order by id

总结: 通用表表达式与派生表类似，就像语句级别的临时表或视图。CTE可以在查询中多次引用，可以引用其他CTE，可以递归。CTE支持SELECT/INSERT/UPDATE/DELETE等语句。

函数窗口

MySQL 8.0支持窗口函数(Window Function)，也称分析函数。窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果。聚合窗口函数: SUM /AVG / COUNT /MAX/MIN等等。
实例:
2. 以国家为统计

SELECT country,sum(sum) FROM sales GROUP BY country order BY country;

需求展示每个国家的信息,并且要在后边加一列根据这个国家分组查询后的总数(以国家为统计)

select year,country,product,sum,
  ### PARTITION by country 以国家为分组,进行sum
	sum(sum) over (PARTITION by country) as country_sum
 from sales
order by country,year,product,sum;

窗口函数（计算平局值）

select year,country,product,sum,
			sum(sum) over (PARTITION by country) as country_sum,
			avg(sum) over (PARTITION by country) as country_avg
 from sales
order by country,year,product,sum;

专用窗口函数
1. 序号函数：ROW_NUMBER() 带序号 RANK() 带序号,形同的数据序号相同
2. 窗口函数（排名）

SELECT
	YEAR,
	country,
	product,
	sum,
  # 以sum排序,展示 1,2,3, 4   row_number为函数窗口
	row_number() over (ORDER BY sum) AS 'rank',
        # 以sum排序,展示 1,1,2,3 相同的数据序号一样, rank()为函数窗口
        rank() over (ORDER BY sum) AS 'rank_1'
FROM
	sales;

SELECT
	YEAR,
	country,
	product,
	sum,
  # 根据国家分组,根据sum排序, rows unbounded preceding  累加前一个列的值
	sum(sum) over (PARTITION by country order by sum rows unbounded preceding) as sum_1
FROM
  # 按照国家和综合排序
	sales order by country,sum;

官网: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

原子DDL操作

drop create alter
例子: drop table t1,t2; 如果t1表存在,t2表不存在,执行这个sql的时候会报错
在mysql8.0之前,会把t1表删除, 在mysql8.0, 这个sql整个不执行,t1表还存在

json增强

https://dev.mysql.com/doc/refman/8.0/en/json.html 官网地址

其他改进

自增列持久化: MySQL 5.7 以及早期版本，InnoDB 自增列计数器（AUTO_INCREMENT）的值只存储在内存中。MySQL 8.0 每次变化时将自增计数器的最大值写入 redo log，同时在每次检查点将其写入引擎私有的系统表。解决了长期以来的自增字段值可能重复的 bug
死锁检查: MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量，用于控制系统是否执行 InnoDB 死锁检查。对于高并发的系统，禁用死锁检查可能带来性能的提高变量: innodb_deadlock_detect

Mysql体系架构

mysql的分支与变种

Drizzle:
1. Drizzle与MySQL有很大差别，进行了一些重大更改，甚至SQL语法的变化都非常大，设计目标之一是提供一种出色的解决方案来解决高可用性问题。在实现上，Drizzle清除了一些表现不佳和不必要的功能，将很多代码重写，对它们进行了优化，甚至将所用语言从C换成了C++。
2. Drizzle另一个设计目标是能很好的适应具有大量内容的多核服务器、运行Linux的64位机器、云计算中使用的服务器、托管网站的服务器和每分钟接收数以万计点击率的服务器并且大幅度的削减服务器成本
MariaDB
1. MariaDB视为MySQL的扩展集，它不仅提供MySQL提供的所有功能，还提供其他功能
Percona Server
1. Percona Server是个与MySQL向后兼容的替代品
2. 三个主要的目标：透明，增加允许用户更紧密地查看服务器内部信息和行为的方法。比如慢查询日志中特别增加的详细信息；性能，Percona Server包含许多性能和可扩展性方面的改进，还加强了性能的可预测性和稳定性。其中主要集中于InnoDB；操作灵活性，Percona Server使操作人员和系统管理员在让MySQL作为架构的一部分而可靠并稳定运行时提供了很多便利

mysql的替代

Postgre SQL
1. PostgreSQL 的稳定性极强，在崩溃、断电之类的灾难场景下依然可以保证数据的正确；在高并发读写，负载逼近极限下，PostgreSQL的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，表现的非常稳定，而 MySQL 明显出现一个波峰后下滑
2. PostgreSQL多年来在GIS(地理信息)领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PostgreSQL有大量字典、数组、bitmap 等数据类型
SQLite
1. 为物联网（IoT）下的数据库首选，并且是手机，PDA，甚至MP3播放器的下的首选

mysql的体系架构

整体架构

可以看出MySQL是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。
**连接池: **由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来(断掉不会立马销毁)，下次可以直接用已经建立好的连接，提升服务器性能。
**管理工具和服务: **系统管理和控制工具，例如备份恢复、Mysql复制、集群等
**SQL接口: **接受用户的SQL命令，并且返回用户需要查询的结果。比如select … from就是调用SQL接口
**解析器: **SQL命令传递到解析器的时候会被解析器验证和解析。解析器主要功能：
1. 将SQL语句分解成数据结构，后续步骤的传递和处理就是基于这个结构的。
2. 将数据结构做成解析树
**优化器: **查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。
**缓存器: **查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
存储引擎
文件系统

连接层

每个连接占用独立的线程
show VARIABLES like ‘%max_connections%’ 查看最大的连接数
服务器需要对其进行验证，也就是用户名、IP、密码验证, 还要验证是否具有执行某个特定查询的权限

Server层(SQL处理层)

SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能

缓存

缓存在生产环境建议不开启，除非经常有sql完全一模一样的查询, sql语句, 协议版本,字符集,连接的数据必须完全一致才能用到缓存
8.0放弃了
1. 只适合于只读的场景,任何对表的修改都会导致这些表的所有缓存无效
2. 匹配的条件比较苛刻

存储引擎

其插件式的表存储引擎,存储引擎是针对表的
InnoDB: InnoDB的性能和自动崩溃恢复特性
MylSAM: MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁(只支持表锁),对于只读的数据,可以使用MyISAM. 读的时候加的共享锁
Memory 引擎: 如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表(以前也叫做HEAP表) ,不支持行锁
Percona的 XtraDB存储引擎: 基于InnoDB引擎的一个改进版本，已经包含在Percona Server和 MariaDB中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的所有查询
TokuDB引擎: Tips, 分形树: 是一种写优化的磁盘索引数据结构。分形树的写操作（Insert/Update/Delete）性能比较好，同时它还能保证读操作近似于B+树的读性能, TokuDB分形树的写性能优于InnoDB的B+树，读性能略低于B+树。分形树核心思想是利用节点的MessageBuffer缓存更新操作，充分利用数据局部性原理，将随机写转换为顺序写，这样极大的提高了随机写的效率。
除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎
1. MySQL中只有MyISAM支持地理空间搜索
修改表的存储引擎: ALTER TABLE mytable ENGINE = InnoDB; (创建新的表,将数据进行复制)
1. INSERT INTO innodb_table SELECT * FROM myisam_table; 手动复制数据
存储引擎查询
看我的MySQL现在已提供什么存储引擎: show engines;
看我的MySQL当前默认的存储引擎: show variables like ‘%storage_engine%’;
比较:

mysql的目录和文件

mysql配置文件位置的查询 mysql --help|grep my.cnf
bin目录下
1. mysqld 启动mysql
2. mysqld_safe: 启动mysql,还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它
3. mysql.server 启动和关闭脚本
  1. mysql.server start | mysql.server stop
客户端命令
1. mysqladmin 执行管理操作的工具，检查服务器配置、当前运行状态，创建、删除数据库、设置新密码。
2. mysqldump 数据库逻辑备份程序。
3. mysqlbackup 备份数据表、整个数据库、所有数据库，一般来说mysqldump备份、mysql还原
参数的查看和修改
1. 可以通过命令show variables查看数据库中的所有参数，也可以通过LIKE来过滤参数名
2. MySQL数据库中的参数可以分为两类:
  1. 动态(dynamic)参数和静态(static)参数 (静态参数说明在整个实例生命周期内都不得进行更改)
  2. 作用范围又可以分为全局变量和会话变量

set [global || session ] system_var_name= expr
或者
set [@@global. || @@session.] system_var_name= expr
比如：
set read_buffer_size=524288;
set session read_buffer_size=524288;
set @@global.read_buffer_size=524288;

数据的目录
1. 查看数据保存到哪个目录: show variables like ‘datadir’;
2. 数据库在文件系统中的表示:
  1. information_schema 库没有对应的文件夹
  2. 每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹
  3. 新建一个数据库时，MySQL会帮我们做这两件事儿
    1. 在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹)。
    2. 在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥
3. 表在文件系统中的表示
  1. 每个表的信息其实可以分为两种: 表结构的定义(.frm文件), 表中的数据(不同的引擎不同)
  2. InnoDB(两个文件): xxx.frm(表定义文件) xxx.ibd(表的数据和表的索引)
    1. frm文件：表定义文件
    2. ibd文件：每个使用InnoDB存储引擎的表都有一个.ibd文件，用于存储该表的数据和索引。
  3. MyISAM(三个文件|数据和索引是分开存放的): xxx.frm (表定义文件) xxx.MYD(表的数据) xxx.MYI(表的索引)
    1. frm文件：存储表的定义。
    2. MYD文件：存储表的数据。
    3. MYI文件：存储表的索引。
4. 表空间:
  1. 系统一个或多个真实文件
  2. 每一个表空间有很多页,数据表,存放在某个表空间的某些页
  3. mysql的系统表空间: ibtmp1 (默认12M左右)
  4. 独立表空间: xxx.ibd文件表示某个表的独立表空间
  5. mysql8.0之前,所有表的数据都放在系统表空间中,8.0之后把每个表的数据放在独立表空间中

日志文件

会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志、Undo日志等等，日志文件记录了影响MySQL数据库的各种类型活动。
错误日志:
1. 位置: show variables like ‘log_error’;
慢查询日志:
1. 查询慢日志是否开启: show VARIABLES like ‘slow_query_log’;
2. 开启慢日志: set GLOBAL slow_query_log=1;
3. 查看慢日志超时时间: show VARIABLES like ‘%long_query_time%’;
4. 设置慢日志时间: set global long_query_time=0;
5. 对于没有使用索引是否记录到慢日志中: show VARIABLES like ‘%log_queries_not_using_indexes%’;
6. 位置: show VARIABLES like ‘%slow_query_log_file%’;
普通查询日志:
1. 查看是否开启: show variables like ‘%general%’
2. 开启普通查询日志: 开启通⽤⽇志查询： set global general_log = on;
二进制日志(binlog):
1. 二进制日志记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执⾏的消耗的时间，MySQL的⼆进制⽇志是事务安全型的 (查询语句不记录binlog)
2. 作用:
  1. 数据恢复
  2. 数据复制
  3. 安全审计: 判断是否有对数据库进行注入的攻击
3. log-bin参数该参数用来控制是否开启二进制日志，默认为关闭, 没办法通过命令修改,需要修改文件
  1. 查看是否开启: show variables like ‘log_bin’;
  2. 开启: 在my.conf文件下添加

# 文件的名称
log_bin=mysql-bin
# 文件记录的格式
binlog-format=ROW
server-id=1
expire_logs_days =30

文件种类:
1. xxx.000001 记录文件 | xxxx.index 索引文件
其他文件:
1. 每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个pid文件中。socket文件
2. 默认/自动生成的SSL和RSA证书和密钥文件

Mysql中的系统库

概论

performance_schema: 这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。
information_schema: 这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些是一些描述性信息，称之为元数据。
sys: 这个数据库通过视图的形式把information_schema和performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。
mysql: 主要存储了MySQL的用户账户和权限信息，还有一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

performance_schema

MySQL的performance_schema 是运行在较低级别的用于监控MySQL Server运行过程中的资源消耗、资源等待等情况的一个功能特性; 采集的资源比较底层: 磁盘文件、表I/O、表锁等等
通过监控和事件的方式实现, 当前和历史的数据,当前数据只保存在内存,不会保存到磁盘
performance_schema在MySQL 5.6及之前的版本中默认没有启用，在MySQL 5.7及之后的版本中才修改为默认启用。
要修改需要改my.cnf文件,然后重启mysql
查看是否开启: show variables like ‘performance_schema’;
分类:
1. 语句事件记录表: events_statements_xxxx
2. 等待事件记录表: events_wait_xxxx
3. 阶段事件记录表: events_stage_xxxx
4. 事务事件记录表: events_transaction_xxxx
5. 监视文件系统层调用的表：file_xxx
6. 监视内存使用的表: memory_xxx
7. 动态对performance_schema进行配置的配置表: setup_xxx
update setup_instruments set enabled=‘yes’,timed=‘yes’ where name like ‘wait%’; 开启某种类型的检测表
查看最近执行失败的SQL语句
1. 在performance_schema的语句事件记录表中针对每一条语句的执行状态都记录了较为详细的信息
2. 查看events_statements_history_long 和events_statements_history 表查看执行失败的sql语句
查看最近的事务执行信息
1. 开启事务的监控
  1. update setup_instruments set enabled=‘yes’,timed=‘yes’ where name like ‘%transaction%’;
  2. update setup_consumers set enabled=‘yes’ where name like ‘%transaction%’;
2. 查询事务数据: events_transactions_current, events_transactions_history 两张表
别的功能: 查看SQL语句执行阶段和进度信息、MySQL集群下复制功能查看复制报错详情

sys库

sys系统库提供了一些代替直接访问performance_schema的视图，所以必须启用performance_schema（将performance_schema系统参数设置为ON），sys系统库的大部分功能才能正常使用。
同时要完全访问sys系统库，用户必须具有以下数据库的管理员权限
对性能会有影响
查看慢sql慢在哪里(可以查看等待的session)
1. 如果我们频繁地在慢查询日志中发现某个语句执行缓慢，且在表结构、索引结构、统计信息中都无法找出原因时，则可以利用sys系统库中的撒手锏：sys.session视图结合performance_schema的等待事件来找出症结所在
2. 首先需要启用与等待事件相关功能：
  1. call sys.ps_setup_enable_instrument(‘wait’);
  2. call sys.ps_setup_enable_consumer(‘wait’);
3. select * from session where command=‘query’ and conn_id !=connection_id()\G 查看sys的session看哪个sql在等待耗时
查询表的增、删、改、查数据量和I/O耗时统计
1. select * from schema_table_statistics_with_buffer\G
小结
1. sys还可以查询查看InnoDB缓冲池中的热点数据、查看是否有事务锁等待、查看未使用的，冗余索引、查看哪些语句使用了全表扫描等等。

information_schema

提供了对数据库元数据、统计信息以及有关MySQL Server信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。
该库下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作
该库下的表:
1. COLUMNS: 提供查询表中的列（字段）信息。 table_schema 数据库名 table_name 表名 column_name 列名
2. TABLES: 表的信息
3. SCHEMATA: 提供查询MySQL Server中的数据库列表信息，一个schema就代表一个数据库
4. KEY_COLUMN_USAGE: 提供查询哪些索引列存在约束条件
5. VIEWS: 提供查询数据库中的视图相关信息。查询该表的账户需要拥有show view权限
6. PARTITIONS: 提供查询关于分区表的信息
7. INNODB_LOCKS: 提供查询InnoDB引擎中事务正在请求的且同时被其他事务阻塞的锁信息
8. INNODB_TRX: 提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁、事务什么时间点开始，以及事务正在执行的SQL语句文本信息等（如果有SQL语句的话
9. INNODB_LOCK_WAITS: 锁等待

mysql系统库

主要表:
1. user：包含用户账户、全局权限和其他非权限列表（安全配置字段和资源控制字段）。
2. db：数据库级别的权限表。该表中记录的权限信息代表用户是否可以使用这些权限来访问被授予访问的数据库下的所有对象（表或存储程序)
3. tables_priv：表级别的权限表
4. columns_priv：字段级别的权限表
5. procs_priv：存储过程和函数权限表
6. proxies_priv：代理用户权限表
7. innodb_table_stats表提供查询与表数据相关的统计信息
8. innodb_index_stats表提供查询与索引相关的统计信息
要更改权限表的内容，应该使用账号管理语句（如：CREATE USER、GRANT、REVOKE等）来间接修改，不建议直接使用DML语句修改权限表(还需要改内存中的用户权限对象)
日志信息表
1. general_log表提供查询普通SQL语句的执行记录信息
2. slow_log表提供查询执行时间超过long_query_time设置值的SQL语句、未使用索引的语句
InnoDB中的统计数据
1. 统计数据的存储方式
  1. 永久性的统计数据，这种统计数据存储在磁盘上
  2. 非永久性的统计数据，这种统计数据存储在内存中
  3. 系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据
  4. SHOW VARIABLES LIKE ‘innodb_stats_persistent’;
2. 基于磁盘的永久性统计数据
  1. 当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：
  2. innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据
    1. n_rows: 表中的条数是统计出来的,选几个叶子节点(默认是20个叶子节点),求平均值,计算出一个叶中的数据条数,然后根据有几个叶子节点,计算出总的数据条数
  3. innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。
3. 统计的方式
  1. 利用定时任务进行统计
  2. 改动的数量超过了多少进行统计
  3. 手动触发统计: ANALYZE TABLE xxx, 这个过程是同步的

Mysql执行原理

索引合并

前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge

Intersection合并(and)

sql语句: SELECT * FROM order_exp WHERE order_no = ‘a’ AND expire_time = ‘b’;
使用Intersection合并步骤
1. 从idx_order_no二级索引对应的B+树中取出order_no='a’的相关记录
2. 从idx_expire_time二级索引对应的B+树中取出expire_time='b’的相关记录
3. 计算出这两个结果集中id值的交集
4. 生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户
为什么不先根据一个索引查出数据,然后过滤另一个条件的数据
1. 只读取一个二级索引的成本:
  1. 按照某个搜索条件读取一个二级索引
  2. 根据从该二级索引得到的主键值进行回表操作
  3. 然后再过滤其他的搜索条件
2. 读取多个二级索引的成本
  1. 按照不同的搜索条件分别读取不同的二级索引
  2. 将从多个二级索引得到的主键值取交集
  3. 最后根据主键值进行回表操作
3. 虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少. 读索引比回表操作的成本更低
使用交集合并的条件
1. 二级索引列必须是等值匹配的情况, 对于联合索引来说，在，不能出现只匹配部分列的情况。如果不是等值匹配,是范围匹配,取出来的主键没有顺序性,要回表时依旧会耗费性能
2. 主键列可以是范围匹配: 主键索引虽然是范围,但是能确定数据的范围
问题
1. 两个带有索引的查询条件,执行查询逻辑,为什么只有两个都是等值匹配才会发生索引合并,一个条件是范围匹配就不会索引合并了. 两个普通索引查出来的主键值取交集后时回表的数据减少,即使一个是范围匹配,主键值取交集也会使ID值减少,回表的次数减少,为什么不会索引合并呢
解答
1. 索引合并（Index Merge）优化是数据库在执行查询时可能采用的一种策略，但它并不总是发生，即使两个条件都使用了索引。是否进行索引合并主要取决于查询优化器的决策，它会考虑多种因素，包括查询成本、数据分布、索引类型等。
2. 当查询条件中包含等值匹配和范围匹配时，数据库优化器可能会认为使用单一索引（通常是选择性更高的索引）进行过滤，然后再对结果进行进一步的处理（如排序、聚合或与其他表连接），比进行索引合并更为高效。这是因为索引合并通常涉及额外的开销，比如合并不同索引产生的结果集。
3. 此外，即使两个索引产生的主键值交集可能会减少回表的次数，优化器也可能认为这种减少并不足以抵消索引合并带来的额外开销。特别是在范围匹配的情况下，优化器可能认为使用范围查询的索引进行过滤，然后再对结果进行等值匹配的检查，比尝试合并两个索引更为高效。
4. 总的来说，是否进行索引合并是一个复杂的决策过程，涉及多种因素的权衡。优化器会根据当前的统计信息和成本模型做出最佳决策。是否进行了索引合并，可以使用数据库提供的执行计划工具（如MySQL的EXPLAIN或PostgreSQL的EXPLAIN ANALYZE）来查看查询的执行计划
上边只是索引合并的条件,但不一定会走索引合并,具体结果得看查询优化器

Union合并(or)

sql: SELECT * FROM order_exp WHERE order_no = ‘a’ OR expire_time = ‘b’
条件: 必须是等值匹配, 主键列可以是范围匹配

Sort-Union合并

sql: SELECT * FROM order_exp WHERE order_no< ‘a’ OR expire_time> ‘z’
合并的过程
1. 先根据order_no< 'a’条件从idx_order_no二级索引中获取记录，并按照记录的主键值进行排序
2. 再根据expire_time>'z’条件从idx_expire_time二级索引中获取记录，并按照记录的主键值进行排序
3. 两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了
多了一步对二级索引记录的主键值排序的过程

联合索引替换Intersection索引合并

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_order_no和idx_expire_time是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢

连接查询

误区:

业务至上，管他三七二十一，再复杂的查询也用在一个连接语句中搞定
敬而远之，上次慢查询就是因为使用了连接导致的，以后再也不敢用了

连接的本质

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
像这样的结果集就可以称之为 笛卡尔积 。平时的查询是带条件的,所以可以进行连接查询

连接查询的过程

sql: SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < ‘d’;
确定驱动表: 第一个确定查询的表就是驱动表
1. 查表,数据从磁盘进内存
遍历驱动表的结果, 到被驱动表中查找匹配记录
驱动表遍历一次,被驱动表遍历1到n次
使用小表做驱动表的优点(小表驱动大表):
1. 减少内存消耗: 数据库通常会将驱动表的数据加载到内存中
2. 减少磁盘/IO操作: 使用小表作为驱动表, 可以更快地读取到内存中
3. 利用索引提高查询效率: 驱动表的字段有索引,更快查询数据到内存,被驱动表关联的字段有索引,更快的与驱动表的数据进行匹配(解决小区驱动大表,查找大表的问题)
4. 减少比较关联的次数: 如果驱动表的数据量小，那么需要进行的关联比较次数就少。相反，如果驱动表的数据量大，那么关联比较的次数就会显著增加。
5. 当使用小表作为驱动表时，查询优化器更容易找到有效的优化策略，因为小表的数据量小、结构简单，更容易处理
使用大表做驱动表的情况(大表驱动小表)
1. A表数据量大,B表数据量小; 大表适合做驱动表的误区: 如果A表作为被驱动表需要每次都和B表中的一条条数据进行匹配,A表会被遍历多次,影响性能
  1. 这个说法其实是不准确的。在数据库查询中，尤其是涉及到连接（JOIN）操作时，通常推荐让小表作为驱动表，而不是大表。
  2. 原因是，如果让大表作为被驱动表，那么对于驱动表中的每一条记录，数据库都需要去遍历大表来寻找匹配项。由于大表的记录数很多，这种遍历过程会非常耗时，导致查询性能下降。
  3. 相反，如果让小表作为驱动表，那么对于小表中的每一条记录，数据库只需要去大表中寻找匹配项一次。由于小表的记录数较少，这种遍历和匹配的过程会相对较快，从而提高查询性能。
  4. 因此，正确的说法应该是：在连接操作中，通常推荐让小表作为驱动表，以减少对大表的遍历次数，提高查询性能。而不是说大表适合做驱动表。
2. 如果查询条件能够充分利用大表上的索引，并且小表上没有合适的索引或者索引的选择性很差，那么将大表作为驱动表可能会获得更好的性能, 在这种情况下，数据库可以利用大表上的索引快速定位到匹配的数据行，减少了对小表的扫描次数。
小表驱动大表确实可以减少关联比较的次数
1. 但这种表述可能有些容易引起误解。更准确的说法是，小表驱动大表可以减少“对每个大表记录的关联比较次数”的总和，而不是说每次关联比较的次数减少了。
2. 解释一下这个概念：
  1. 当小表作为驱动表时，数据库会遍历小表中的每一条记录，并对于每一条记录，都去与大表进行匹配。由于小表的记录数较少，因此这种遍历和匹配的过程会相对较快。
  2. 相比之下，如果大表作为驱动表，那么数据库需要遍历大表中的每一条记录，并与小表进行匹配。由于大表的记录数较多，这种遍历和匹配的过程会相对较慢，并且会消耗更多的资源。
3. 因此，虽然每次关联比较（即将一条驱动表记录与一条被驱动表记录进行比较）的时间复杂度是固定的，但小表驱动大表可以减少总的关联比较次数（即总的遍历和匹配过程），因为小表的记录数较少。这样，总的查询时间就会减少，查询性能就会提高。

内连接和外连接

内连接: select * from a,b where – | inner join | join | cross join
外连接: left join | right join
1. 左外连接,左边的是驱动表
2. 右外连接,右边的是驱动表这两个说法是错误的

Mysql对连接的执行

嵌套循环连接

for each row in e1 {   #此处表示遍历满足对e1单表查询结果集中的每一条记录，N条
    for each row in e2 {   #此处表示对于某条e1表的记录来说，遍历满足对e2单表查询结果集中的每一条记录，M条
            for each row in t3 {   #此处表示对于某条e1和e2表的记录组合来说，对t3表进行单表查询，L条
            if row satisfies join conditions, send to client
        }
    }
}

如果被驱动表做全表扫描,效率会很慢
使用索引加快连接速度
1. 被驱动表加索引加快连接的速度
2. 加快被驱动表的查询速度
基于块的嵌套循环连接
1. 扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足
2. 被驱动表的数据过多,而且不能使用索引,只能全表扫描,在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉. 而采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表(这个过程无法避免,必须要执行的,优化点将驱动表放缓存,被驱动表加载一次内存和驱动表的多条数据匹配)
3. 正常嵌套循环,在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉
4. 加载到内存中的被驱动表的数据一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了. 将驱动表的数据加载到JOIN BUFFER, 驱动表的多条数据和加载到内存中的被驱动表中的一条数据进行匹配, 最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录。
6. 这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。 (show variables like ‘join_buffer_size’ 😉
7. join buffer 加载的数据只有主键和关联的条件

Mysql查询成本

成本的概念

I/O成本: 表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本, 一次加载的内存大小为16kb,一个页
CPU成本: 读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
MySQL规定读取一个页面花费的成本默认是1.0（I/O成本）; 读取以及检测一条记录是否符合搜索条件的成本默认是0.2（CPU成本）

单表的查询成本

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划
基于成本的优化步骤
1. 根据搜索条件，找出所有可能使用的索引 (possible keys)
2. 计算全表扫描的代价
  1. SHOW TABLE STATUS LIKE ‘order_exp’ 拿到表的数据条数的估计值
    1. rows 数据条数 data_length 数据长度
  2. 叶子节点的数量 = 表数据的长度(data_length)/16(叶的大小)/1024
  3. I/O成本 = 叶子节点的数量 * 1(I/O)成本 + 微调值(1.1)
  4. CUP成本 = 数据条数(rows) * 0.2(CPU成本) + 微调值(1.0)
3. 计算使用不同索引执行查询的代价
  1. 使用二级索引需要加回表的操作
  2. 使用到了索引,而且固定了一个范围区间,查询成本(I/O成本)只有 1
  3. 回表的成本:
    1. 找到范围,找到第一条数据(最左边的数据) ,定位数据, 开销忽略 ==0
    2. 找到最右边的数据开销忽略 ==0
    3. 估算最左边和最右边的数据条数
    4. 读取二级索引的记录成本: CPU成本 = 数据条数 * 0.2(CPU成本) + 0.01(微调值)
    5. 每一次回表,相当于访问一个页面 IO成本 = 数据条数 * 1(I/O成本)
    6. 回表后数据根据条件进行过滤,CPU的开销 = 数据条数 * 0.2(CPU成本)
  4. IO成本: 索引固定范围区间 + 回表I/O成本
  5. CPU成本 : 数据过滤的成本 + 读取二级索引的记录成本
4. 对比各种执行方案的代价，找出成本最低的那一个
一般来说,mysql进行查询的时候更倾向于使用一个索引
explain 展示查询成本
1. 在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON

连接查询成本

两表连接的成本计算 = 单次查询驱动表的成本 + 删除 * 单次访问被驱动表的成本
1. 单次查询驱动表的成本 (驱动表的扇出 fanout)
  1. 没有条件,计算全表的数据
  2. 查询条件有索引, 使用索引查询数据量
  3. 查询条件没有索引, 只能猜测有多少条数据, 猜的过程称之为 condition filtering
2. 多次查询被驱动表的成本
多表连接的成本计算:
1. 多表连接查询的顺序可以有多种
2. 提前结束某种连接成本
  1. 维护一个全局变量: 当前最小的连接查询成本,计算后边的连接过程中发现已经大于了最小的连接查询成本就结束后边的判断
3. 连接的深度: 系统变量 show variables like ‘optimizer_search_depth’,默认值为62
  1. 如果超过了这个数值的表数量连接,超过的部分就不进行连接表的穷举
  2. 如果小于这个数量,会进行多种连接顺序的穷举
  3. 可以将这个值调小
4. 启发式规则: 根据之前的经验,选择某一个顺序

调节成本常数

SHOW TABLES FROM mysql LIKE ‘%cost%’; 维护了CPU和I/O的成本
mysql.server_cost : server_cost表中在server层进行的一些操作对应的成本常数
mysql.engine_cost : engine_cost表表中在存储引擎层进行的一些操作对应的成本常数

Mysql查询重写

条件简化:

编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。
移除不必要的括号
1. ((a = 5 AND b =c) OR ((a > c) AND (c < 5)))
2. (a = 5 and b =c) OR (a > c AND c < 5)
常量传递
1. a = 5 AND b >a
2. a = 5 AND b >5
移除没有用的条件
1. (a < 1 and b= b) OR (a = 6 OR 5 != 5)
2. (a < 1 and TRUE) OR (a = 6 OR FALSE)
3. a < 1 OR a =6
表达式计算
1. a = 5 + 1
2. a = 6
3. ABS(a) > 5 | -a < -8 不会进行优化
4. 只有搜索条件中常数使用某些运算符连接起来才可能使用到索引

常量表检测

使用主键等值匹配,或者唯一性的二级索引等值匹配查询速度比较快,会被认为常量表
可以将多表查询优化为单表查询

SELECT
	*
FROM
	table1
INNER JOIN table2 ON table1.column1 = table2.column2
WHERE
  # 主键查询
	table1.primary_key = 1;
=========================由多表查询优化为了单表查询============================
SELECT
	table1表记录的各个字段的常量值,
	table2.*
FROM
	table2
where  table2.column2 = table1表column1列的常量值

外连接消除

如果外连接查询出来的内容和内连接查询出来的数据相同,会将外连接优化为内连接

InnoDB引擎底层解析

三大特性: 双写机制 BufferPool 自适应Hash索引
表中的数据到底存到了哪里
表中的数据以什么格式存放的
InnoDB是以什么方式来访问的这些数据
InnoDB中的事务、锁等的原理是怎样

InnoDB记录存储结构和索引页结构

InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

行格式:

InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。
我们可以查看默认值：show variables like ‘innodb_default_row_format’; 默认是Dynamic
创建表的时候可以指定: CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
Compact的行格式
1. 1. 记录的真实数据: 列1…列n的值
  2. 额外的信息: 变长字段长度列表(多个字段的内容), 数据为Null的列表(用01001表示)
  3. 记录头信息: 5个字节(40位),不同的位代表不同的意思
    2. 删除一个数据,刚开始只是打了一个标记, (delete_mask)
  4. 隐藏信息: 额外信息和真实数据之间
    1. DB_ROW_ID : 非必须，6字节，表示行ID，唯一标识一条记录
      1. 隐藏的ID,如果有ID,则是ID.如果没有,会生成一个ID
    2. DB_TRX_ID: 必须，6字节，表示事务ID
    3. DB_ROLL_PTR: 必须，7字节，表示回滚指
Dynamic和Compact格式很相似,只是处理数据溢出时不同
1. 页的大小 16kb, 16384个字节
2. CREATE TABLE test_varchar( c VARCHAR(60000) )
3. 数据溢出: 往一个字段插入60000个字符, 只一个字段的内容就超过了一个页的大小(超过了16kb)
4. 在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用20个字节存储指向这些页的地址。这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页
5. Dynamic和Compressed行格式，不会记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址

InnoDB数据页格式

File Header (文件头部) Page Header (页面头部) Infimum+Supremum (最小记录和最大记录)
User Records 用户记录 (实际存储的记录内容) Free Space 空闲空间(页中未使用的空间)
Page Directory 页面目录 File Trailer 文件尾部 (校验页是否完整)

InnoDB的表空间

表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件

独立表空间

表空间中的页可以达到2³²个页, InnoDB中还有一个区的概念, 对于16KB的页来说，连续的64个页就是一个区，也就是说一个区默认占用1MB空间大小
页 16kb 一个页中包含多条数据
1个组 = 256个区 1个区 = 64个页 1个页 = 16kb行数据
B+树的叶子节点是双向链表组成的,内存空间不是连续的,两个页之间的距离可能会很远
区在物理上的地址是连续的,为了使B+树页子节点的数据尽量在连续内存空间中(多个页在一个区中), 让随机IO变为顺序IO
段 : 包含多个分组
1. InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。
2. 存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段
3. 段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念

系统表空间

全局只有1个系统表空间
InnoDB中的数据字典,记录看到的系统表内容

双写缓冲区/双写机制

生效的时机: (断电数据恢复)
1. InnoDB需要将一个页的数据写入到磁盘的时候
工作原理:
1. 写入双写缓冲区：
  1. 在数据页被写入磁盘的最终位置之前，InnoDB首先将其复制到双写缓冲区中
  2. 这个操作是顺序写的，意味着数据被连续地写入磁盘，这通常比随机写要快
2. 从双写缓冲区写入最终位置：
  1. 一旦数据页被安全地写入双写缓冲区，InnoDB接着将这些页从双写缓冲区写入到最终的数据文件中。
  2. 这个步骤是随机写，因为数据页被写入到数据文件的不同位置。
3. 崩溃恢复：
  1. 如果在写入数据页的过程中发生系统崩溃，InnoDB可以在恢复过程中检查双写缓冲区
  2. 如果发现数据文件中的数据页损坏，InnoDB可以使用双写缓冲区中的完整副本来恢复这些页
写入双写缓冲区这个步骤数据是否已经写入到了磁盘
1. 是的，写入双写缓冲区这个步骤中，数据确实已经被写入到了磁盘。在InnoDB存储引擎中，双写缓冲区位于磁盘上的系统表空间中。当InnoDB需要写入一个数据页时，它首先将该数据页复制到双写缓冲区中，并确保这个复制操作是顺序写入磁盘的。这意味着，在数据页被写入到其最终位置之前，它在双写缓冲区中已经有了一个完整的副本，并且这个副本已经被安全地存储在了磁盘上。这样做的目的是为了防止在系统崩溃或断电等异常情况下，数据页只被部分写入到其最终位置，从而导致数据损坏。如果发生这种情况，InnoDB可以在恢复过程中使用双写缓冲区中的完整副本来恢复损坏的数据页。
在系统表空间中,有多个区: 1号区,2号区,3号区,4号区… 一个区中有64个页, 对应的编号从0开始
系统表空间中有两个特殊的区(双写缓冲区): 选了2号区(extent1)和3号区(extent2) ; 对应页编号为64到128, 大小是2M(磁盘空间是连续的)
它是一种特殊文件flush技术，带给InnoDB存储引擎的是数据页的可靠性。它的作用是，在把页写到数据文件之前，InnoDB先把它们写到一个叫doublewrite buffer（双写缓冲区）的连续区域内，在写doublewrite buffer完成后，InnoDB才会把页写到数据文件的适当的位置。如果在写页的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。
在正常的情况下, MySQL写数据页时，会写两遍到磁盘上，第一遍是写到doublewrite buffer(数据已经写入到磁盘,因为是顺序写,对性能的影响不是很大)，第二遍是写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个页数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了
虽然叫双写缓冲区，但是这个缓冲区不仅在内存中有，更多的是属于MySQL的系统表空间，属于磁盘文件的一部分
数据丢失的场景: InnoDB一个页的大小是16kb,操作系统操作一个页的大小是4kb,如果一个页的数据写入磁盘,操作系统会操作4次写入,如果写入了一半系统断电,会造成一个页的数据,8kb写入到了磁盘,8kb的数据丢失了
doublewrite是在一个连续的存储空间, 所以硬盘在写数据的时候是顺序写，而不是随机写，这样性能影响不大，相比不双写，降低了大概5-10%左右。
redo log恢复: 数据库不会校验页面是不是合法的(缺失部分), redo log是页的物理操作, 指针800处写AAA的记录,不是页的全量记录
参数配置:
innodb_doublewrite：控制是否启用双写缓冲区，默认值为ON，即启用。
innodb_doublewrite_dir（MySQL 8.0.20中引入）：定义InnoDB创建双写文件的目录。
innodb_doublewrite_buffer_size：控制双写缓冲区的大小，默认值可能因MySQL版本而异，但通常是足够大的，以容纳多个数据页(默认是2M)
innodb_doublewrite_files：定义双写文件的数量，默认情况下，为每个缓冲池实例创建两个双写文件

Butter Pool

基本概念

InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。
在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，他们给这片内存起了个名，叫做Buffer Pool（中文名是缓冲池)
查看大小: show variables like ‘innodb_buffer_pool_size’;(大小为256M)
1. Buffer Pool中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是16KB
2. 控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息，当然还有一些别的控制信息
3. 一个控制块对应一个缓存页
free链表: 空闲链表 (管理缓存页的分配)
表空间的编号和页的编号对一个缓冲区中的一个key
flush链表: (脏页面,数据修改了的缓存) (管理缓存页的修改)
1. 修改: 先修改内存,后写磁盘
2. 结构和free链表的结构相似
判断一个页是否在Buffer Pool中
1. InnoDB使用哈希表来快速判断一个页是否在Buffer Pool中
2. 哈希表的键是页的编号（如表空间ID和页号），值是对应的控制块的指针
3. 当需要访问一个页时，InnoDB首先计算该页的哈希值，并在哈希表中查找对应的控制块
4. 如果找到了控制块，说明该页在Buffer Pool中；如果没有找到，说明该页不在Buffer Pool中，需要从磁盘上读取

缓存的淘汰机制

LRU链表(最近最少使用原则)
新进来的数据放到链表的头部,从尾部淘汰数据; 如果数据被使用到了,会将数据重新放到头部
预读: 就是InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到Buffer Pool中
1. 线性预读:
  1. InnoDB提供了一个系统变量innodb_read_ahead_threshold，如果顺序访问了某个区（extent）的页面超过这个系统变量的值，就会触发一次异步读取下一个区中全部的页面到Buffer Pool的请求。
  2. 读一个区的内容超过了56个页,会将下一个区的数据加载到内存中(采用异步线程的方式)
2. 随机预读:
  1. 如果Buffer Pool中已经缓存了某个区的13个连续的页面，不论这些页面是不是顺序读取的，都会触发一次异步读取本区中所有其他的页面到Buffer Pool的请求
  2. InnoDB提供了随机预读的开关: innodb_random_read_ahead系统变量，它的默认值为OFF
SQL语句全表扫描: 数据很多,会淘汰Buffer Pool中的数据,会影响缓存的命中
划分区域的LRU链表
2. old区域在LRU链表中所占的比例是37%，也就是说old区域大约占LRU链表的3/8
3. InnoDB规定，当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部。这样针对预读到Buffer Pool却不进行后续访问的页面就会被逐渐从old区域逐出，而不会影响young区域中被使用比较频繁的缓存页
4. 在进行全表扫描时，虽然首次被加载到Buffer Pool的页被放到了old区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到young区域的头部，这样仍然会把那些使用频率比较高的页面给顶下去
5. 对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部
6. 总结:
  1. 全表扫描的数据会频繁地进行数据访问(访问的时间间隔会小于配置的值)
    1. 配置的值: SHOW VARIABLES LIKE ‘innodb_old_blocks_time’;
  2. 新进来的缓存会放到old区的头部,第一次缓存访问会记录一个访问时间
  3. 如果第二次访问缓存数据,会判断第二次访问缓存的时间和第一次访问缓存的时间差值,如果大于某一个值说明这个数据是正常的,会将这个数据从old区移动到young区的头部,如果小于这个时间差值,说明时一次性加载大量数据,不会移动到young区
  4. 数据不停的加载到old区的头部,old区的尾部不停的进行数据淘汰,如果缓存第二次被访问的时间超过了一个值,会被放到young区,不会被立马淘汰

事务底层与高可用

redo log 确保事务的持久性
undo log确保事务的原子性

事务的基础

mysql的事务分为显式事务和隐式事务
默认的事务是隐式事务 update insert
show variables like ‘autocommit’; 自动提交,隐式事务
MySQL采用的是WAL（Write-ahead logging，预写式日志）机制来实现的
redo log称为重做日志，每当有操作时，在数据变更之前将操作写入redo log，这样当发生掉电之类的情况时系统可以在重启后继续操作。
undo log称为撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之前的状态

redo日志

基础内容

事务提交时写redo日志(顺序写入),再写磁盘
存储的位置: MySQL的数据目录（使用SHOW VARIABLES LIKE 'datadir’查看）下默认有两个名为ib_logfile0和ib_logfile1的文件，这个就是redo日志,默认大小都是48m
参数调节:
1. innodb_log_group_home_dir，该参数指定了redo日志文件所在的目录，默认值就是当前的数据目录
2. innodb_log_file_size，该参数指定了每个redo日志文件的大小，默认值为48MB
3. innodb_log_files_in_group，该参数指定redo日志文件的个数，默认值为2，最大值为100
默认设置为2个的原因: 轮流替换,一个写满了写另一个 01 10 01 10 循环写
已经提交的事务对数据库中数据所做的修改永久生效,即使系统崩溃也能生效, 没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好。
将第0号表空间的100号页面的偏移量为1000处的值更新为2 , 重做日志记录的内容

日志格式

redo日志本质上只是记录了一下事务对数据库做了哪些修改。 InnoDB们针对事务对数据库的不同修改场景定义了多种类型的redo日志
1. type：该条redo日志的类型，redo日志设计大约有53种不同的类型日志
2. space ID：表空间ID
3. page number：页号
4. data：该条redo日志的具体内容
简单的日志类型(物理日志)
1. 写隐藏ID(row_id)
  1. 内存中维护一个全局变量(插入表数据，+1)
  2. 这个变量值为256倍数，变量的值刷新到系统表空间页号是7页中(Max row ld) 属性。对这个页面的修改以redo 日志保存起来(MLOG_8BYTE)
  3. 当系统启动时，会将这个Max Row Id属性加载到内存中
2. MLOG_1BYTE LOG_2BYTE LOG_4BYTE LOG_8BYTE(表示在页面的某个偏移量处写入8字节的redo日志类型)
复杂的日志类型
1. 有时候执行一条语句会修改非常多的页面，包括系统数据页面和用户数据页面（用户数据指的就是聚簇索引和二级索引对应的B+树）

写入过程

redo log block和日志缓冲区:
1. 生成的redo日志都放在了大小为512字节的块（block）中; 写入redo日志时也不能直接直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间，翻译成中文就是redo日志缓冲区（内存），我们也可以简称为log buffer。这片内存空间被划分成若干个连续的redo log block，可以通过启动参数innodb_log_buffer_size来指定log buffer的大小，该启动参数的默认值为16MB。
redo日志刷盘时机:
1. 事务提交时，为了保证持久性，必须要把修改这些页面对应的redo日志刷新到磁盘
  1. 参数 innodb_flush_log_at_trx_commit 可以控制, 默认值为1
    1. 0：当该系统变量值为0时，只将数据写到mysql的缓冲区中,后边的写到系统缓冲区和写到磁盘交给异步任务去做,不安全
    2. 1：当该系统变量值为1时，将数据从内存写到磁盘,安全
    3. 2：当该系统变量值为2时，将数据写到操作系统的缓冲区,落盘操作由操作系统自己执行,不安全
2. InnoDB认为如果当前写入log buffer的redo日志量已经占满了log buffer总容量的大约一半左右，就需要把这些日志刷新到磁盘上
3. 后台有一个线程，大约每秒都会刷新一次log buffer中的redo日志到磁盘
4. 正常关闭服务器时等等

崩溃后的恢复

恢复机制:
1. 在服务器不挂的情况下，redo日志简直就是个大累赘，不仅没用，反而让性能变得更差
2. 比如一个事务执行三个操作 A, B, C ; 事务提交, 记录redo日志写入磁盘后, 将ABC三个操作的内容写入到磁盘,当写了A的操作到磁盘后,系统断电. 只有A的操作在磁盘,BC的操作没有到磁盘. mysql重启后,可以根据redo日志的内容,重新将BC的操作写到磁盘(即只要事务提交后更改是持久性的), 数据刷入到磁盘后,就会从redo日志中抹掉
崩溃时不使用binlog的原因:
1. binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性
2. redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用
3. redo log是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志
4. binlog只记录操作的日志,并不关心这条数据是否刷盘
与双写缓冲区的区别:
2. redo日志: 解决了事务提交时,事务多个操作写入磁盘过程中崩溃的问提,提交事务之前,现将操作记录写入到redo日志中(写入磁盘)
3. 双写缓冲区: 解决了mysql一个页(16k)的数据写入到磁盘,操作系统处理时以4k为单位,处理到一半系统崩溃的问题, 写页之前,现将页的数据写入到双写缓冲区的磁盘

数据写入磁盘的过程

写入Buffer Pool：
1. 当应用程序执行写入操作时，数据首先被写入到InnoDB的Buffer Pool中。Buffer Pool是InnoDB存储引擎中的一个内存区域，用于缓存数据表和索引的数据。
2. 写入Buffer Pool的操作是快速的，因为它是在内存中进行的。
记录Redo日志：
1. 在数据被写入Buffer Pool之前或同时，相关的修改也会被记录到Redo日志中。Redo日志用于确保事务的持久性。
2. Redo日志记录了事务的修改操作，以便在系统崩溃后能够恢复这些修改。
异步写入磁盘：
1. 数据在Buffer Pool中是异步写入到磁盘上的。这意味着写入操作不会立即发生，而是由InnoDB存储引擎在后台进行。
2. nnoDB会根据一定的算法（如LRU算法）决定哪些数据页需要从Buffer Pool中刷新到磁盘上。
刷新到磁盘：
1. 当满足一定的条件时（如Buffer Pool满了、系统空闲时、执行了FLUSH操作等），InnoDB会将Buffer Pool中的数据页刷新到磁盘上。
2. 在刷新过程中，数据页会首先被写入到双写缓冲区（Doublewrite Buffer）中，然后再以两次写的方式写入到磁盘上。这是为了防止在写入过程中发生部分写失败而导致数据页损坏。
使用Redo日志恢复：
1. 如果在系统崩溃后重启数据库，InnoDB会使用Redo日志来恢复已提交事务的修改。它会读取Redo日志文件，并重做（redo）所有已提交事务的修改，以确保这些修改被完整地应用到数据库中
概括:
1. 写Buffer Pool ===> 写redo 日志 ===> 从Buffer Pool 到双写缓冲区 ===> 从Buffer Pool到磁盘

undo日志

事务回滚的需求
1. 情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
2. 情况二：程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前的事务的执行。
每当我们要对一条记录做改动时（这里的改动可以指INSERT、DELETE、UPDATE），都需要把回滚时所需的东西都给记下来。比方说：
1. 插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉。
2. 删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中。
3. 修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值。
undo日志：为了实现事务的原子性，InnoDB存储引擎在实际进行增、删、改一条记录时，都需要先把对应的undo日志记下来。一般每对一条记录做一次改动，就对应着一条undo日志，但在某些更新记录的操作中，也可能会对应着多条undo日志
删除不是物理删除,只是改了一个修改指针,保证了多版本控制时删除数据的可见性
update 操作对应的undo日志
1. 不更新主键的情况: 就地更新,如果放不下新数据,先删除就记录,在插入新记录
2. 更新主键: 将旧记录进行delete mark操作, 创建一条新记录

binlog

MySQL中的binlog（二进制日志）记录数据的时机主要与事务的提交（commit）相关。binlog记录了MySQL服务器接收到的所有修改数据库的语句，如INSERT、UPDATE和DELETE等，以及可能导致数据变更的事件
事务提交时记录
1. 当MySQL执行一个事务时，它不会立即将修改操作写入磁盘上的binlog文件，而是先将这些操作缓存起来。
2. 一旦事务被提交（commit），MySQL就会将缓存中的所有修改操作写入binlog中。这样做的好处是可以减少磁盘I/O操作，提高性能，同时保证数据的一致性和完整性。
sync_binlog参数的影响
1. binlog的刷盘（写入磁盘）时机可以通过sync_binlog参数来控制。
  1. 如果sync_binlog的值为0，表示由操作系统决定何时将binlog写入磁盘，这可能会带来一定的数据丢失风险。
  2. 如果sync_binlog的值设置为1（默认值），则表示每次事务提交时都会将binlog同步写入磁盘，这是最安全但可能也是性能开销最大的设置。
  3. 如果sync_binlog的值设置为大于1的N，则表示每N个事务提交后才会将binlog同步写入磁盘，这可以在一定程度上平衡性能和安全性。
binlog的写入流程
1. 在事务进行过程中，MySQL会将binlog事件先写入到binlog cache中。binlog cache是内存中的一片区域，用于暂存binlog事件。
2. 当事务提交时，MySQL会将binlog cache中的事件写入到binlog文件中。如果此时binlog文件已满或达到其他条件（如max_binlog_size限制），MySQL会自动创建一个新的binlog文件继续记录。
binlog的格式
1. MySQL的binlog支持三种格式：Statement、Row和Mixed。
  1. Statement格式记录的是SQL语句本身。
  2. Row格式记录的是每一行数据的变化。
  3. Mixed格式则是根据SQL语句的具体情况和系统的配置来决定使用哪种格式记录。
binlog的用途
1. binlog主要用于数据恢复、数据复制和数据审计等场景
  1. 通过binlog，可以在数据库出现故障时恢复数据到某个特定的时间点
  2. 在主从复制架构中，主服务器会将binlog发送给从服务器，从服务器根据binlog中的事件来同步数据
  3. 通过分析binlog，还可以对数据库的操作进行审计和跟踪
与redolog的区别和联系
1. 区别
  1. 实现层面与存储引擎
    1. redo log：是InnoDB存储引擎特有的日志，用于保证事务的持久性。它记录了事务对数据页的物理修改，是物理日志
    2. binlog：是MySQL Server层实现的日志，与存储引擎无关，所有引擎都可以使用。它记录了所有的DDL和DML等修改数据库的语句，是逻辑日志
  2. 记录内容
    1. redo log：记录的是在某个数据页上做了什么修改，即物理修改的内容
    2. binlog：记录的是这个语句的原始逻辑，即SQL语句本身
  3. 写入方式
    1. edo log：采用循环写入的方式，空间固定，会用完。当write pos追上checkpoint时，就需要擦除旧的日志以腾出空间
    2. binlog：采用追加写入的方式，当binlog文件写到一定大小后会切换到下一个文件，不会覆盖以前的日志。
  4. 持久化策略
    1. redo log的持久化策略由innodb_flush_log_at_trx_commit参数控制，可以设置为0、1、2，分别表示不同的持久化时机。
    2. binlog的持久化策略由sync_binlog参数控制，同样可以设置为0、1、N，以控制事务提交时binlog的持久化行为。
2. 联系
  1. 共同保证数据安全性
    1. redo log和binlog共同协作，确保MySQL事务的数据安全性。redo log保证了事务的持久性，即使在数据库崩溃后也能恢复已提交的事务；binlog则记录了所有的修改操作，可用于数据恢复、复制等场景。
  2. 两阶段提交
    1. 在MySQL中，为了保证redo log和binlog之间的一致性，InnoDB存储引擎采用了两阶段提交方案。在事务提交过程中，首先会将redo log设置为prepare状态，然后写入binlog，如果binlog写入成功，再将redo log设置为commit状态。这样，就保证了在出现异常情况时，可以通过redo log和binlog来恢复数据的一致性。

Mysql8.0新特性底层原理

降序索引

8.0 使用了降序索引,如果查询语句进行降序排序时可以直接拿到数据,不需要额外的操作

Doublewrite Buffer 双写缓冲区的改进

在MySQL 8.0.20 版本之前，doublewrite 存储区位于系统表空间，从 8.0.20 版本开始，doublewrite 有自己独立的表空间文件，这种变更，能够降低doublewrite的写入延迟，增加吞吐量，为设置doublewrite文件的存放位置提供了更高的灵活性
系统表空间在存储中就是一个文件，那么doublewrite必然会受制于这个文件的读写效率
之前的系统表空间除了双写缓冲区的内容还有别的数据,8.0.20将其单独提取出来,会提升效率
配置:
1. innodb_doublewrite_dir 指定doublewrite文件存放的目录
2. innodb_doublewrite_files: 指定doublewrite文件数量，默认情况下，每个buffer pool实例，对应2个doublewrite文件