mysql原理

来自B站视频讲解的个人记录
原视频

mysql结构

计算机语言通过驱动与mysql建立连接，然后发送sql指令，mysql执行。但是每一次执行都需要建立连接，完成后释放 频繁的连接建立和释放比较浪费资源。所以使用一个连接池，建立一个更持久的连接。 sql接口接收到命令传给解释器，sql优化器会根据当前表的数据，和数据库的种种状态来调整sql的执行，使效率提高。 在8.0之前的版本中，有缓存机制，即之前的数据会被缓存，但是表内容一旦改变就失效，这个机制8.0正式去除。 存储引擎是mysql最核心的部分，是可以更换的部分，现在常用的为InnoDB，事物控制、读写效率、多用户并发、索引搜索等众多方面都有不错的表现。 作用是控制内存和外存，完成数据的读取和写入。

数据写入原理

由于内存和外村的速度差距，所有的逻辑处理和读写都是操作内存中的数据

写入前会原来的记录值写入Undo Log，磁盘中日志文件，然后写入内存中的Buffer Pool。这样的目的是为了实现事务相关操作。 写入道BufferPool的内容会被存储引擎按照一定的策略将内容复制到磁盘中持久的存储。 InnoDB中这个写的过程通过，使用了O_DIRECT方式，跳过了操作系统的页缓存，自行实现缓存，写入的单位为“页”，与OS的页类似，但单个页的容量更大 > O_DIRECT 无缓冲IO，不会经过OS Cache 当写入成果之后，RedoLog中的内容会被打上Commit标记

到目前为止系统还存在一个明显问题，如果系统意外关闭，那么BufferPool中没有被及时写入磁盘的内容会丢失，为此在内容有设有RedoLogBuffer，为了安全其需要持久化，磁盘中有RedoLog，RedoLogBuffer拥有独立的写入磁盘策略，每次系统重启时，会先从RedoLog中去恢复数据。

---

上述过程都是在InnoDB内部发生了，程序员并不能操作细节，如果我们执行了错误的指令（删除了不该删的数据，如何修正？
BinLog就是为结局此问题而生，注意，其不是存储引擎的部分，属于独立模块，提供了变更历史查询、数据库备份和恢复、主从复制等功能
BinLog中记录的操作的逻辑，RedoLog记录修改的值。RedoLog和BinLog是同时的

存储结构

不同的存储引擎的存储结构是不一样的，此处我们讲的是InnoDB
当我们创建一个表时，会创建两个文件 table.frm和table.ibd。前者存储表结构等信息，后者存储数据和索引（表空间）。8.0之后去掉了frm，表结构定义也定义道ibd中。
另外5.7才开始为每一个表设置一个ibd文件，称为独立表空间。在此之前，所有的信息都在系统表空间（共享表空间），除此之外还有

• Undo表空间
• 通用表空间
• 临时表空间

ibd用页来作为读写和索引单位，同时也是InnoDB和磁盘的最小读写单位，B+树的索引节点将指向一个页，一个页16kb，每个页由行组成，行的大小是不固定的，且行也有独立的格式，每个行存储记录信息。不同的页有不通的结构。最常用的用来存储数据和索引的“索引页”

当随着我们的数据量增大，页的数量增大时，会引入新的结构**区**，一个区有32个页，之后在申请空间一次申请一个区，进一步又引入了组，一个组256个区

最后还有数据结构段
段是逻辑概念，用于区分“叶子节点段”、“非叶子节点段”对应B+树中的叶子节点和非叶子节点

sql的执行原理

1.首先客户端通过连接驱动和连接池建立连接，两者之前使用半双工通信，一次必须接受完整消息，超出限制的将被拒绝。 2.到缓存中去查询结果。缓存的存在是为了提高效率，但是其命中率低效果差，现已经被弃用 3.SQL解析器对SQL语句进行分析，词法分析和语法分析，对预期正确性进行验证 4.预处理器中创建模版语句，然后提交参数。这样设计为了提高重复操作的效率 5.对SQL语句进行优化，降低IO和CPU的消耗 6.执行期根据优化后的命令调用API去操作数据 7.使用TCP返回信息给客户端

MTR是原子传输，自适应哈希索引会根据访问频率自动穿件hash索引来提高访问速度

页结构

相关岗位常问
mysql中为不同类型数据提供不同的页，有11种之多，我们需要注意的是数据页
mysql种索引即数据，数据页也被叫做索引页，B+树的索引将会指向这里

中间部分由数据行组成，这里的数据行和真实表中的数据行是一一对应的，行有4中。
行有两大部分，额外控制信息和真实数据

所有的行通过next_record链接起来。其中还有两个特殊的行用来作为链表的头和尾 ，构成一个单项链表。
我们查找数据首先可能想到顺序找，但是性能显然是不足的。所以基于主键使用了二分查找，但是表中行太多，二分查找的效率也低了。所以最终使用索引顺序查找思想，将其分组，组间二分，组内顺序。分组信息由页目录维持

行结构

索引

使用B+树结构，叶节点就是一个页，每个页中的行相当于节点内的数据，非叶子结点也是页，但是页中只有主键用来索引超找和指向页的地址

每个节点都是一个页，非页节点中不在存储所有数据，只有key和地址。通过这种方式是的方式是稳定，知道经过树高次搜索才能找到数据。
同时，因为一个页可以存储大量的key和页地址，等于我们把B+Tree的阶做的非常大，树就会很矮

聚簇索引，即为索引也是数据，是有主键key来构建的，即上图中的结构。叶子节点中是包含数据
非聚簇索引。我们新建了一个新的索引，这个所有中就算叶子节点中也只有主键key和聚簇索引key。在非聚簇索引中无法拿到真实数据，我们需要根据查到的聚簇索key引再去查聚簇索引，这个过程叫做回表

覆盖索引，我们需要查找数据都在索引中，比如查找key，就不需要回表

事务

将多个操作打包为一个不可分割的整体
应对这种传统的数据库，我们要求满足ACID。详见分布式理论的发展
C一致性是有Undolog来保证的
I 隔离性。分为“写与写”隔离、“写与读”隔离。分别通过锁和MVCC实现

脏读：A读取到了B还没有提交的事务
不可重复读：A两次相同读区之间，B对其进行了修改，导致前后两侧读区结果不一致
幻读：A两次相同读之间，B进行了删除或增加列，导致两侧结果行结构不一样

为了解决这些问题，可以设置不同的隔离级别

isolation	读未提交	读已提交	可重复读	串行读
脏读	n	y	y	y
不可重复读	n	n	y	y
幻读	n	n	n	y

D 持久性，通过redolog和doublewritebuffer解决

锁机制

锁偏向处理写

• 共享锁
  可以共享度，有了共享锁就不能加上排他性的锁
• 独占锁
  互斥锁
• xx意向锁
  会在整张表上加锁，这样其他操作在搜索是否有锁时效率高
• 自增锁
  保证主键等的唯一性
• GAP锁
  两行之间，防止有行插入
• Next Key锁
  GAP锁和精确行锁的结合

MVCC

multi version concurrency control 多版本并发控制机制，为了解决不可重复读和脏读出现，替代低级的锁
是基于Undolog链和Readview完成的
索引中行和undolog中行会链接成一个版本链

在读的使用利用ReadView选择一个版本读
读select的时候，创建一个ReadView，数据结构：
m_ids 当前活跃事务，即所有没有提交的事务
min_trx_id 最小事务，版本链尾的事务id
max_trx_id 下一个将要分配的事务
creator_trx_id 创建此ReadView的事务id

在链表中从第一条开始查找，查找规则，trx_id为当前查找行的id

1. creator_trx_id = trx_id，访问
2. trx_id< min_trx_id 说明要访问的数据已经被提交了，访问
3. trx_id>max_trx_id 则不能访问，结束
4. 如果trx_id在min和max中间，但是活跃表m_ids中又没有，说明创建ReadView之后被提交了，可以访问

注意：MVCC可以解决不可重复读，但是无法解决幻读，需要和锁配合

面试问题

varchar的长度限制到16383。mysql限制列的长度不能超过64KB，导致出现这个数据