Mysql

Mysql

1 架构演变

1. 单点式
   
2. 主从式
   
3. 分库分表式
   
4. 优缺点分析
   1）单点式，由于只部署了一台mysql，QPS会有限制，存储的数据量也会有限制，但是不用考虑数据的一致性。用于数据量少，QPS也少的业务场景下非常适合
   2）主从结构，每个库里面的数据都是一样的，从表负责读，主表负责写，因此对写的支持也有一定局限性，但是可以很好的支持读的功能，另外因为理论上还是一张表，因此当数据量足够大的时候，还是会出现查询效率慢的情况，初次主从结构还需要保持数据的一致性，也加大了技术难度和维护成本。
   3）分库分表式，分库分表式在主从结构的基础上，利用水平拆分或者垂直拆分，将数据分散到多个数据库中，理论上可以通过无限制的增加节点，从而保证执行过程的效率，然而也存在和主从结构一样的数据一致性的问题，除此，数据的拆分依据也是头疼问题，数据的查询机制使用连表查时也需要判断对应的是哪个数据库，增加了开发和技术上的难度

2 索引数据结构

mysql采用B+树作为索引结构。B+只在叶子结点存储数据，与B树相比节省了大量空间

B树和B+树的区别

1. B树的每个节点存储了key和data，key是一条数据记录的键值，是唯一的，data存储的是数据记录除key以外的数据。而B+树只在叶子节点存储data数据，这样非叶子节点就能存储更多的key。所以B+树相较于B树来说更加的矮胖，因为索引树很大不能一次IO读取进内存，树的深度越浅，查找数据时IO的次数就越少，效率就更快。

2. B+树的每个叶子节点的指针指向相邻的叶子节点，构成一个有序链表，可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树叶子节点指针为null，则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
   

阶数概念

1）每个结点最多有m-1个关键字。

2）根结点最少可以只有1个关键字。

3）非根结点至少有Math.ceil(m/2)-1个关键字。

4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

2 执行过程

1. 连接器
   连接器主要和身份认证和权限相关的功能相关，维护连接状态。数据库服务器进程和客户端进程可能运行在不同的主机中，它们之间必须通过网络来进行通讯。MySQL采用TCP作为服务器和客户端之间的网络通信协议。如果我们的服务器进程和客户端进程都运行在同一台操作系统为类Unix的机器上的话，我们可以使用Unix域套接字文件来进行进程间通信。命名管道和共享内存（windows才有）
   主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
2. 查询缓存
   查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
   连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。
   MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。
   所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。
   MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。
3. 分析器
   第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
   第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
4. 优化器
   优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
5. 执行器
   当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。
6. binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制日志。

3 执行引擎

MyISAM（在5.5版本之前）是MySQL关系数据库管理系统的默认储存引擎。它从旧的ISAM代码扩展出许多有用的功能。在新版本的MySQL中，InnoDB引擎由于其对事务，参照完整性，以及更高的并发性等优点开始广泛的取代MyISAM。

1. MyISAM
   每一个MyISAM表都对应于硬盘上的三个文件。这三个文件有一样的文件名，但是有不同的扩展名以指示其类型用途：.frm文件保存表的定义，但是这个文件并不是MyISAM引擎的一部，而是服务器的一部分；.MYD保存表的数据；.MYI是表的索引文件。
   在5.5版本之前，MyISAM是MySQL的默认存储引擎，MyISAM并发性比较差，使用场景比较少主要的特点是：
   • 不支持事务操作，ACID事务也就不存在了，这一设计是为了性能和效率考虑的。
   • 不支持外键操作、如果强行加增加外键，MySQL不会报错，只不过外键不起作用。
   • MyIASM默认的锁粒度是表级锁，所有并发性能比较差加锁比较快，锁冲突比较少，不太容易发生死 锁的情况。
   • MyIASM会在磁盘上存储文件，文件名和表名相同，扩展名分别是 .frm（存储定义） .MYD（MYData,存储数据）、MYI(myIndex存储索引)。这里需要特别注意的是MyISAM只缓存索引文件，并不缓存数据文件。
     Full-Text:他的出现是为了解决针对文本的查询效率较低的问题。
     B-Tree索引：所有的索引节点都按照平衡二叉树的数据结构来存储，所有的索引数据节点，都在叶子节点中。
     R-Tree索引：它的存储方式和B-Tree所以有写区别的，主要设计用于存储空间和多维的数据的字段，相对于BTree索引，RTree优势在于范围查找。
     MyISAM 支持的索引类型有全局索引（Full-Text）、B-Tree索引、R-Tree索引
   • 当系统宕机或者mysql进程崩溃后，MyISAM引擎表很容易受到损坏。不得不用外部命令myisamchk来进行修复。
   • 增删改查性能方面：select性能较高，适用查询较多的情况
   • myisam引擎内存主要存放索引信息，不存放数据信息，因此在检索表时会先访问索引信息，然后在去磁盘读取数据信息。
2. InnoDB
   • 支持事务操作，具有事务ACID特性，默认的隔离级别是可重复读，通过MVCC(并发版本控制)来实现。能够解决脏读和不可重复读的问题。
   • InnoDB支持外键操作
   • InnoDB默认的锁颗粒度 行级锁，并发性能比较好，会发生死锁
   • 和MyISAM一样的是，InnoDB存储引擎也有，.frm文件存储表结构定义，但是不同是，InnoDB的表数据与索引数据是存储在一起的，都是位于B+Tree的叶子节点上，而MyISAM的表数据结构和索引数据分开的。
   • InnoDB 有安全的文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。
   • 增删改查性能方面，如果执行大量的增删改操作，推荐使用InnoDB存储引擎，它在删除操作时是对行删除，不会重新建表
   • innodb的所有数据都存在叶子节点，只能通过主键找到，其他的索引也需要先找到主键然后才能找到数据。
   • redo log
     • redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
       对于业务代码来讲，执行的事务是OK的，甚至前端都接受到了请求成功的响应。那结果修改的数据没同步回磁盘，MySQL宕机了会不会致使真实数据和逻辑上的数据不一致呢？性能其实不会的！
       MySQL使用redo log解决了这个问题，redo故名思义：重作。
       当发生事务（增、删、改）时会致使缓存页变成脏页，于此同时MySQL会将事务涉及到的：对 XXX表空间中的XXX数据页XXX偏移量的地方作了XXX更新。
       因此MySQL意外宕机重启也不要紧。只要在重启时解析redo log中的事务而后重放一遍。将Buffer Pool中的缓存页重作成脏页。后续再在合适的时机将该脏页刷入磁盘便可。

如下面id = 2的update语句执行过程。