1、逻辑架构
1.1、连接器(Connectors)
MySQL连接器为客户端程序提供与MySQL服务器的连接。 连接器使您能够连接和执行来自另一种语言或环境发出的MySQL语句,包括ODBC,Java(JDBC),Perl,Python,PHP,Ruby等MySQL实例。
以 JAVA 为例,mysql-connector-java
是MySQL的JDBC驱动包,通过该jar包实现JDBC连接MySQL。Oracle收购MySQL后,为MySQL开发了很多连接器,如Connector/C++
、Connector/NET
等。分别为不同语言环境提供与MySQL的交互。
若主机上安装了MySQL,则可以通过该主机上的黑窗口打开远程MySQL服务。
mysql -h127.0.0.1 -P3306 -uroot -p123456
参数 | 含义 | 说明 |
---|---|---|
h | host | MySQL服务 |
P | port | 端口 |
u | username | 用户名 |
p | password | 密码 |
1.2、系统管理与控制工具(Management Serveices & Utilities)
系统管理和控制工具集合。
1.3、连接池(Connection Pool)
当一个应用需要进行数据库请求操作,则我们需要创建一个数据库对连接,请求完成后,关闭该连接即可。然而创建连接过程的开销相对较大,当一个应用频繁访问数据库时,则频繁建立、关闭连接会极大的消耗系统的性能,因为对于连接的使用成了系统性能的瓶颈。线程池通过监听并接收MySQL Server等数据库连接请求,转发所有请求到线程管理模块。线程管理模块负责管理并维护(如线程创建、线程cache)数据库连接线程,当收到请求后会为其分配或创建一个线程为其单独服务。这些线程的主要工作就是负责MySQL Server与客户端之间的通信。
1.4、SQL接口(Sql Interface)
用于接收用户的MySQL命令,并且返回客户需要查询的结果,如SELECT * FROM tb_name;
就是通过调用 Sql Interface 实现查询功能。SQL接口、解析器、优化器、缓存、存储引擎 共同组成了MySQL Server。
1.5、解析器(Parser)
我们通常将客户端传入的SQL命令称之为Query,Query不能被MySQL直接执行。连接线程接收到客户端的一个Query后,会直接将该Query传递给解析器,解析器对Query进行语义和语法的解析,并分解成数据结构,以方便进行优化和生成执行计划。在解析的过程中:
- 若分解失败,则说明这个SQL语句是不合理的。
- 若分解成功,则会根据分解结果获取操作类型,并针对性的转发到对应的处理模块。
解析器的实质主要做了两件事情:词法和语法分析,分别由 lex 和 yacc 实现的,是一个很长的脚本。mysql使用 lex 词法分析器,yacc 语法分析器进行分析,最后保存到 lex 对象结构中。
1.6、查询优化器(Optimizer)
在MySQL语句执行Query之前,MySQL会采用选取 -> 投影 -> 联接
的方式,根据请求的Query语句和数据库中的一些统计数据,在一系列算法的基础上进行分析,得出一个最优的策略,告诉后面的程序如何取得这个 Query的结果。如:
SELECT name, age FROM users WHERE gender='1';
选取:该 SELECT 查询会根据条件选取,而不是查询所有数据之后再进行条件过滤。
投影:该 SELECT 查询会先根据 name 和 age 进行属性投影,而不是将属性全部取出以后再进行过滤。
联接:将这两个查询条件联接起来生成最终查询结果。
1.7、查询缓存(Cache & Buffer)
通过哈希算法计算客户端传入的 SELECT 类 Query 请求,获取对应hash值hash(querySql)
。并与该 SELECT 类Query得出的结果集一一对应,将结果 Cache 到内存中。在后续查询中,若查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等。在读比例非常高的应用系统中,缓存可以极大的提高查询的性能。
然而,该hash值(也就是Query)所取数据的基表发生任何数据变化时,都会使该Query对应的cache失效。这就意味着,对于写操作频繁的表,缓存是不合适的;同时,缓存提高查询性能的原因是因为查询的结果集存放在内存中,这就意味着缓存方式对于内存来说是一个挑战,对于查询结果较大,或者系统内存拮据的情况,缓存的做法是不合适的;再者,有很多优秀的工具,如Redis、MongoDB等,他们对于内存数据的操作要远优于该缓存机制。所以MySQL的查询缓存功能显得没那么强大,MySQL8.0后不再使用。
1.8、可插拔式存储引擎(Pluggable Storage Engines)
基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类,但正是因为它成功地将各种数据处理高度抽象化,才成就了今天 MySQL 可插拔存储引擎的特色。
MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持,这些标准与存储引擎本身无关,可能是每个数据库系统本身都必需的,如SQL分析器和优化器等,而存储引擎是底层物理结构的实现,每个存储引擎开发者都可以按照自己的意愿来进行开发。
存储引擎是基于表的,而不是数据库。最常用的就是InnoDB、MyISAM,创建存储引擎的方式如下:
-- MySQL5.5后默认为InnoDB,此处以MyISAM为例
create table product(...) engine=MyISAM;
- 常见存储引擎
存储引擎 | 说明 |
---|---|
MyISAM | 高速引擎,拥有较高的插入、查询速度,但不支持事务、不支持行锁。支持3种不同的存储格式。包括静态型、动态型和压缩型。 |
InnoDB | MySQL5.5版本后默认的存储引擎,支持事务和行级锁定,事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全,比MyISAM处理速度稍慢、支持外键(FOREIGN KEY)。 |
ISAM | MyISAM的前身,MySQL5.0以后不再默认安装。 |
MRG_MyISAM(MERGE) | 将多个表联合成一个表使用,在超大规模数据存储时很有用。 |
Memory | 内存存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据,意味着数据可能会丢失。 |
Falcon | 一种新的存储引擎,支持事物处理,传言可能是InnoDB的替代者。 |
Archive | 将数据压缩后进行存储,非常适合存储大量的独立的,作为历史记录的数据,但是只能进行插入和查询操作。 |
CSV | CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据。 |
- MyISAM与InnoDB比较
\ | InnoDB | MyISAM |
---|---|---|
存储文件 | .frm 表定义文件 .ibd 数据文件和索引文件 | .frm 表定义文件 .myd 数据文件 .myi 索引文件 |
锁 | 表锁、行锁 | 表锁 |
事务 | 支持 | 不支持 |
CURD | 读、写 | 读多 |
count | 扫表 | 专门存储的地方 (加where也扫表) |
索引结构 | B+ Tree | B+ Tree |
外键 | 支持 | 不支持 |
2、物理架构
2.1、MySQL数据文件
-
查看数据文件存放目录(data目录)
show variables like '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec)
-
共享表空间
每一个数据库的所有的表数据,索引文件全部放在该文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为ibdata1
,初始化为12M。mysql> show variables like 'innodb_data%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | +-----------------------+------------------------+ 2 rows in set (0.00 sec)
[root@centos-7 ~]# ll -ctr /var/lib/mysql 总用量 176152 ...... -rw-rw----. 1 mysql mysql 79691776 8月 15 23:38 ibdata1 ......
-
独立表空间
进入data目录下的数据库目录下后,可以查看到各个数据库表到数据文件,如下图。各个存储引擎对应的存储文件都不相同,如:以MyISAM为存储引擎的表,会生成以下三类文件:.frm
、.myd
、.myi
;以InnoDB为存储引擎的表,会生成以下两种文件:.frm
、.ibd
。
2.2、MySQL日志文件
-
错误日志(error log)
MySQL错误日志默认是开启的,而且从5.5.7以后无法关闭错误日志。错误日志记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。可以通过以下Query查看错误日志目录:mysql> show variables like 'log_error'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | log_error | /var/log/mysqld.log | +---------------+---------------------+ 1 row in set (0.00 sec)
-
二进制日志(bin log)
bin log 日志通过记录数据库所有的 DDL 语句和 DML 语句的方式记录数据变化,用来进行数据备份、恢复、主从。语句以事件的形式保存,描述了数据的变更顺序。同时,bin log 还包括了每个更新语句的执行时间信息。
(1)DDL:直接记录到 bin log 日志。
(2)DML:必须通过事务提交才能记录到 bin log 中。mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000004 | 478421400 | | mysql-bin.000005 | 9653 | | mysql-bin.000006 | 340631484 | +------------------+-----------+ 3 rows in set (0.00 sec)
-
通用查询日志(general query log)
记录任何操作,性能消耗严重,故生产中不可开启,仅限于平时开发纠错时使用。 -
慢查询日志(slow query log)
记录查询较慢的语句,默认是关闭的。需要通过设置参数进行开启。可用于SQL调优,用于定位慢的SELECT,可以通过修改配置文件去定义“慢”的条件。mysql> show variables like 'slow_query_log%'; +---------------------+------------------------------+ | Variable_name | Value | +---------------------+------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/test-slow.log | +---------------------+------------------------------+ 2 rows in set (0.00 sec)
-
重做日志(redo log)
用于记录事务操作的数据变更,无论事务是否提交。在实例和介质失败(media failure)时,redo log 文件就能派上用场。如数据库宕机,InnoDB存储引擎会使用redo log恢复到宕机前的时刻,以此来保证数据的完整性。mysql> show variables like 'innodb%log%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | innodb_log_buffer_size | 8388608 | # 日志缓冲池的大小,默认为8M | innodb_log_file_size | 50331648 | # 日志组的大小,默认为5M | innodb_log_files_in_group | 2 | # 日志组的数量,默认为2 | innodb_log_group_home_dir | ./ | # 日志组所在的路径,默认为./,表示在数据库的data目录下 | innodb_mirrored_log_groups | 1 | # 指定了日志镜像文件组的数量,默认1 | ...... | ... | +----------------------------------+-----------+ 12 rows in set (0.00 sec)
[root@centos-7 ~]# ll -ctr /var/lib/mysql 总用量 176152 ...... -rw-rw----. 1 mysql mysql 50331648 6月 26 22:10 ib_logfile1 ......
-
回滚日志(undo log)
回滚日志的主要作用有如下两点:
(1)保存了事务发生之前的数据的版本,可以用于回滚。
(2)为多版本并发控制(MVCC)技术下的非锁定读提供支持。
undo log 的存放位置视参数innodb_file_per_table
而定:
(1)ON:undo log 将放在每个表的.ibd
文件中。
(2)OFF:存放在共享表空间中。
MySQL5.6后,undo log 的存放位置还可以通过变量innodb_undo_directory
来自定义存放目录。默认值为".",表示data目录。mysql> show variables like 'innodb_log_group_home_dir'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_group_home_dir | ./ | # 日志组所在的路径,默认为./,表示在数据库的data目录下 +---------------------------+-------+ 1 row in set (0.00 sec)
-
中继日志(relay log)
做主从时,相当于从机的bin log。
3、文件写入顺序
- 日志文件:
日志文件写入顺序:顺序写入。顺序写入只需要一次寻道(磁道地址),所以其执行速度快,耗费时间短。 - 数据文件:
数据文件写入顺序:随机写入。随机写入需要多次寻道,所以其执行速度慢,耗费时间长。但数据文件中存在大量的新增或删除的操作,若使用顺序写入,则在进行删除操作的时候,会删除连续磁道中的片段,导致该片段无法复用,从而产生磁道碎片。数据磁盘落盘可利用日志文件去提升IO性能。