mysql总结

sql执行过程

注：8.0版本mysql已经去掉了缓存功能。
1、未命中缓存
服务器连接->查询缓存->解析器（sql语法校验、生成语法树等）->查询优化器->执行计划->查询执行引擎->访问磁盘文件
2、命中缓存
服务器连接->查询缓存-查询执行引擎->访问磁盘文件

索引

索引是帮助Mysql高效获取数据的数据结构。

优点：
1、如同书目录，提高数据检索效率，降低数据库IO次数。
2、唯一索引，可以确保数据唯一性。
3、减少查询中分组和排序的时间，进而降低CPU消耗。

缺点：
1、创建索引，维护索引消耗时间。
2、索引需要占用一定的磁盘空间。
3、删除，插入数据降低更新表的时间。

索引的数据结构为B+树。

索引的存储存在数据页的概念。
1、每一个节点都会有多条数据，之间用单向链表管理。每层数据页之间双向链表关联。
2、主键索引，为聚簇索引，节点数据包含数据库行所有数据。而为聚餐索引包含的是与主键索引关系及地址，回表操作获取数据。
3、非底层节点，可以理解为目录页，则通过record_type表示数据的类型，0：普通数据，1：目录数据，2：表示当前页开始，3：表示当前页结束。
聚簇索引

二级索引（非聚簇索引）

B+树每个节点为一个数据页，是与内存交互的最小单位。

数据页结构：
1、文件头（38字节）：
① 页号
② 页类型--索引页，系统页，undo log页等。
③ 前一页，后一页。
④ 校验和，可以用来校验内存中页与磁盘页是否有变化，文件头和尾都有FIL_PAGE_SPACE_OR_CHECKSUM这个属性，来校验页数据是否一致完整。
⑤ FIL_PAGE_LSN(log Sequence_number日志序列号)。记录日志修改位置。
2、文件尾（8字节）： FIL_PAGE_LSN(log Sequence_number日志序列号)。记录日志修改位置。同文件头⑤一样，记录日志修改位置，如果不一致说明数据存在不一致，有问题了。
3、空闲空间：
存储用户记录，随着不断存储增加，空闲空间逐渐变小。
4、用户记录：
指定行格式，记录之间是单链表。如何知道记录行格式，则就在记录头信息中。
5、最大最小记录：
当前页的最小，最大记录。heap_no 最小为0，最大则为1.
6、Page Directory(页目录)
记录为单向链表，顺序查找效率低，因此专门给记录做个目录，通过二分法方式，提高效率。页目录把记录分成N个组，每个组4-8条记录，每组最大记录放入页目录中。
7、Page Header(页面头部)
为了能得到一个数据页存储记录的状态信息，比如本页已存储了多少条记录，第一条记录的地址，页目录存储多少个槽位等等。特意定义了一个叫Page Header的部分。
这一部分占用固定字节56个。专门存储各种状态信息

InnoDB行格式
1、COMPACT行格式（紧凑，5.1之后为默认格式）
2、COMPRESSED（压缩） 和 DYNAMIC（动态格式，5.7、8.0默认格式）
3、Barracuda（冗余）

记录头信息：
delete_mask:标记当前记录是否被删除，占1个二进制位，0：未被删除，1：已删除。
min_rec_mask:为叶子节点（目录页），住建目录项的最小记录设置为1，其他为0.
record_type:0普通记录，1目录，2：最小，3最大。
heap_no:当前记录在本页中的位置。两位伪记录0（最小）,1（最大记录）。其他记录从2开始。
n_owned:页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为owned字段。
next_record:记录头信息里该属性非常重要，他表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

页上层结构：

1、页固定大小16kb
2、区中为64个页，1M大小。表示顺序IO，提高磁盘读取的效率。
3、段中包含多个区，逻辑划分，把连续的区划分为同一段中。
4、碎片区，不同区在同一段中，不归属任意段，表索引的创建，数据较少的情况，划分不同的区极大浪费空间，因此不属于段，由表空间独立管理，称为碎片区。
所以现在段不能仅定义为某些去的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

主从复制：
所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库，主库对外提供读写的操作，从库对外提供读的操作。
为什么使用MySQL主从?
对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 5.7 时，大概可以支撑 500 的 TPS 和 10000 的 QPS，当遇到一些活动时，查询流量骤然，就需要进行主从分离。
大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式，主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。
当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份，整体场景总结如下：

1、读写分离：从库提供查询，减少主库压力，提升性能；
2、高可用：故障时可切换从库，保证服务高可用；
3、数据备份：数据备份到从库，防止服务器宕机导致数据丢失。

主从复制原理：
MySQL 的主从复制是依赖于 binlog，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。
主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步地完成。

详细流程如下：
①　用户做crud操作，写入数据库，更新结果记录到binlog中；
②　主从同步是主找从的，从库IO发起请求，主库的主进程看从库的master change中给的参数是否合法，如果合法主进程交给IO进程进行3操作，否则拒绝；
③　主库根据master的位置点，从这个位置点的binlog日志一直到binlog最后，将其准备发送给从库；
④　将找到的binlog日志发给从库，并且还会发送新的日志点；
⑤　从库收到binlog日志，将其写入relay-log（中继日志）中；
⑥　从库IO进程再向master info保存主库传过来的最后的binlog日志的位置点；
⑦　从库IO是循环发起请求的，发了再要，不会顾及SQL读取中继的操作。
从库IO根据新的日志点，向主库发起请求，主库执行3操作再，再发送新的binlog给从库，从库再执行5操作；
⑧　其实当第一次向relay-log中放数据时，SQL进程就已经知道，SQL进程将relay-log中的sql语句转换成数据，写入从库，从而实现同步；（relay-log和master info也不会交互）
⑨　SQL读取中继日志，并不会一次性全部读完，会把读取到的日志点存放到relay-log.info中。

中间插入数据场景 树如何操作
数据页和目录页是否满了，分三种情况

参照：https://blog.csdn.net/wwj17647590781/article/details/119918844

binlog的格式

• 基于SQL语句的复制(Statement)
• 基于行的复制(Row)
• 混合模式复制(Mixed)

二进制日志,它记录了数据库的所有改变,并以二进制的形式保存到磁盘中。它可以用来查看数据库的变更历史,数据库增量备份,恢复,Mysql主从复制。从5.7.22版本开始，Mysql默认的格式由STATEMENT改为了ROW。

Statement
每一条会修改数据的sql都会记录到binlog中。

• 优点:不需要记录每一行的变化,减少了binlog的日志量,节约了IO,提高了性能。
• 缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。

//查看binlog日志
> mysqlbinlog --base64-output=AUTO -v -d demo mysql-bin.000001
...
update rumenz set id=456 where id=123
...

Row

• 优点:binlog可以不记录执行sql语句的上下文相关信息,仅记录那一条数据(每个字段)被修改成了什么样子。所以row level的日志会非常清楚的记录每一行数据的变化细节,而且不会出现某些特定情况下,存储过程,函数,触发器无法被复制的过程。
• 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

//查看binlog日志
> mysqlbinlog --base64-output=AUTO -v -d demo mysql-bin.000001
...
### UPDATE `demo`.`rumenz`
### WHERE
###   @1=123
###   @2='qaz'
### SET
###   @1=456
###   @2='qaz'
# at 1096 
...

Mixed模式

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

//查看binlog日志
> mysqlbinlog --base64-output=AUTO -v -d demo mysql-bin.000001
...
update rumenz set id=456 where id=123
...

Mysql页分裂
https://www.pianshen.com/article/89941890104/

索引失效
‌1、使用不等于操作符‌：当查询条件中使用不等于操作符时，索引可能会失效，因为不等于操作需要扫描表中的大部分数据。
‌2、对索引列进行计算或函数操作‌：如果在查询条件中对索引列进行了计算或应用了函数，MySQL可能无法使用该索引，因为它需要计算每一行的值来确定是否满足条件。
‌3、隐式类型转换‌：当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL可能会进行隐式类型转换，导致索引失效。
‌4、使用LIKE操作符进行前缀模糊匹配‌：如果使用LIKE操作符进行模糊匹配，并且匹配模式以通配符开始（如LIKE '%xyz'），则索引会失效，因为MySQL需要扫描整个索引来查找匹配的行。
‌5、使用OR连接多个条件‌：当使用OR操作符连接多个条件时，如果条件涉及的列不是全部被索引覆盖，那么索引可能会失效。
‌6、选择性低的列‌：如果索引列的选择性很低（即列中不同值的比例很低），MySQL可能会认为全表扫描比使用索引更有效，从而不使用索引。
‌7、索引碎片过多‌：当索引碎片过多时，MySQL可能会选择不使用索引，因为碎片化的索引可能导致查询效率降低。
‌8、数据分布不均‌：如果数据在索引列上的分布非常不均匀，MySQL可能会认为全表扫描更有效，从而不使用索引。
‌9、复合索引未遵循最左前缀原则‌：对于复合索引，如果查询条件没有使用索引的最左列，或者查询顺序与索引列顺序不一致，那么索引可能会失效。
‌10、索引列包含NULL值‌：如果索引列包含NULL值，并且在查询中使用了NULL判断（如IS NULL或IS NOT NULL），那么索引可能会失效。