Mysql架构

猫哥灬01

已于 2022-07-05 16:30:10 修改

阅读量223

点赞数

分类专栏： Mysql 文章标签： mysql 架构数据库

于 2020-01-29 14:17:34 首次发布

本文链接：https://blog.csdn.net/qq_29857681/article/details/104094239

版权

Mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

技术架构

（1）MySQL向外提供的交互接口（Connectors）

（2）管理服务组件和工具组件(Management Service & Utilities)

（3）连接池组件(Connection Pool)

（4）SQL接口组件(SQL Interface)

（5）查询分析器组件(Parser)

（6）优化器组件（Optimizer）

（7）缓存主件（Caches & Buffers）

（8）插件式存储引擎（Pluggable Storage Engines）

（9）物理文件（File System）

sql执行流程

一条SQL进入MySQL服务器，会依次经过连接池模块（进行鉴权，生成线程），查询缓存模块（是否被缓存过），SQL接口模块（简单的语法校验），查询解析模块，优化器模块（生成语法树），然后再进入innodb存储引擎。

进入innodb后，首先会判断该SQL涉及到的页是否存在于缓存中，如果不存在则从磁盘读取相应索引及数据页加载至缓存。如果是select语句，读取数据(使用一致性非锁定读)，并将查询结果返回至服务器层。如果是DML语句，读取到相关页，先试图给这个SQL涉及到的记录加锁。加锁成功后，先写undo 页，逻辑地记录这些记录修改前的状态。然后再修改相关记录，这些操作会同步物理地记录至redo log buffer。

InnoDB存储引擎

对于表的创建，数据的存储，检索，更新等都是由MySQL存储引擎完成的

InnoDB 会将数据库文件按页(每页16k)读取到缓冲池, 然后用LRU算法来保留在缓冲池中的数据,如果缓冲池中数据有修改, 首先修改缓冲池中的页(发生修改后, 该页即为脏页), 然后按照一定的频率将缓冲池的脏页刷新到磁盘;

InnoDB主要分为2部分, 后台线程和各种内存池, 后台线程主要负责同步内存池和磁盘文件中的数据

后台线程有7个:

1. master_thread

提交日志缓冲数据, 合并插入缓存, 刷新脏页到磁盘,产生checkpoint

高并发下的问题:

InnoDB默认每次只会刷新100个脏页到磁盘, 合并20个插入缓存, 在高并发下系统会处理不过来

可以设置2个参数: innodb_io_capacity 控制每次刷新到磁盘的脏页数

innodb_max_dirty_pages_pct 控制触发刷新脏页的阈值 = 脏页/总页的比例

2. 4个IO线程

insert buffer thread , log thread, read thread, write thread

3. lock监控线程

关键特性

插入缓冲

问题: 非聚集索引是逻辑上的连续, 插入新数据时是随机写, 性能较慢

解决: 主要针对非聚集索引的数据插入操作, 会首先插入到插入缓冲区, 按照一定的频率进行插入缓冲区数据和非聚集索引页子节点的合并操作, 然后将多个操作批量提交, 提高性能

IBUF_POOL_SIZE_PER_MAX_SIZE 修改插入缓冲区大小

两次写

问题: mysql进行刷盘操作时,会先写日志,然后物理刷盘. 如果此时物理的磁盘数据有异常, 则会导致数据写入不一致.

解决: mysql进行刷盘时, 会先顺序写磁盘共享表空间, 再随机写各表磁盘数据

在执行redo日志时, 会先判断该数据页是否有异常, 如果有异常首先根据磁盘共享表空间的数据进行磁盘数据修复, 然后再执行redo日志, 保证数据的可靠性.

自适应hash索引

问题: 当频繁等值查询时, 会先根据索引二分查询, 然后回表, 这里主要针对二分查询进行优化

解决: Innodb存储引擎会监控对表上索引的查找，如果发现某索引被频繁访问，索引成为热数据，建立哈希索引可以带来速度的提升. 复杂度为O(1);

分区

分区和分片一样,都是根据片键按照某种分区规则,将数据均衡分散存储.

好处: 1. 从存储角度, 可以存储更多的数据, 水平扩展性好 2. 从查询角度, 可以多个区并行查询, 提高查询性能

分区类型 range(按照范围分区)、list(按照某些值分区)、hash(hash分区)、key(使用mysql提供的hash函数进行分区)、

子分区(对分区内的数据进行再分区, 通常数据非常大时使用)

eg: 根据月份进行分区

CREATE TABLE `xxxxxxxx` (     
`crttm` int(11) NOT NULL,     
`srvid` int(11) NOT NULL,     
`evtid` int(11) NOT NULL,     
`aid` int(11) NOT NULL,     
`rid` int(11) NOT NULL,     
`itmid` int(11) NOT NULL,     
`itmnum` int(11) NOT NULL,     
`gdtype` int(11) NOT NULL,     
`gdnum` int(11) NOT NULL,     
`islmt` int(11) NOT NULL,  
KEY `crttm` (`crttm`),  
  KEY `itemid` (`itmid`),  
  KEY `srvid` (`srvid`),  
  KEY `gdtype` (`gdtype`)  
) ENGINE=myisam DEFAULT CHARSET=utf8  
PARTITION BY RANGE (crttm)   
(  
PARTITION p201303 VALUES LESS THAN (unix_timestamp('2013-04-01')),  
PARTITION p201304 VALUES LESS THAN (unix_timestamp('2013-05-01')),  
PARTITION p201305 VALUES LESS THAN (unix_timestamp('2013-06-01')),  
PARTITION p201306 VALUES LESS THAN (unix_timestamp('2013-07-01')),  
PARTITION p201307 VALUES LESS THAN (unix_timestamp('2013-08-01')),  
PARTITION p201308 VALUES LESS THAN (unix_timestamp('2013-09-01')),  
PARTITION p201309 VALUES LESS THAN (unix_timestamp('2013-10-01')),  
PARTITION p201310 VALUES LESS THAN (unix_timestamp('2013-11-01')),  
PARTITION p201311 VALUES LESS THAN (unix_timestamp('2013-12-01')),  
PARTITION p201312 VALUES LESS THAN (unix_timestamp('2014-01-01')),  
PARTITION p201401 VALUES LESS THAN (unix_timestamp('2014-02-01'))  
);

为啥不用分区而使用分库分表?

1. mysql分区没有分库分表可控.