《MySQL 性能优化》之理解 MySQL 体系结构

MySQL性能优化 专栏收录该内容
7 篇文章 52 订阅

作为专栏文章《MySQL 性能优化》的第一篇,本文介绍 MySQL 的服务器体系结构,包括物理结构、逻辑结构以及插件式存储引擎。

实例和数据库

我们通常所说的 MySQL 数据库服务器由一个实例(instance)以及一个数据库(database)组成。实例包括一组后台进程/线程和许多内存结构,用于管理数据库;数据库由一组磁盘文件组成,用于存储数据和日志等信息。MySQL 使用典型的客户端/服务器(Client/Server)结构,下图显示了一个简单的 MySQL 体系结构:

mysql
客户端通过通过实例中的后台进程访问 MySQL 数据库。MySQL 采用单进程多线程架构,也就是说一个 MySQL 实例在操作系统中就是一个进程(mysqld)。在 Linux 系统中使用ps命令进行查看:

[root@sqlhost ~]# ps -ef|grep mysql|grep -v grep
mysql     7898     1  1 Feb03 ?        00:12:12 /usr/sbin/mysqld

严格来说,一个 MySQL 实例管理的是多个数据库(也叫模式,Schema)包括系统数据库 mysql、information_schema、performance_schema、sys 以及用户创建的数据库等。使用SHOW DATABASES或者SHOW SCHEMAS命令查看当前实例中的数据库:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.14 sec)

数据库就像是一个容器,存储了各种对象。例如,数据表(Table)、视图(View)、存储过程(Stored Procedure)以及触发器(Trigger)等。其中,表是存储数据的主要对象。它们之间的关系如下图所示:

mysql database

MySQL 物理结构

MySQL 的物理结构主要包括两个目录:软件的安装目录和数据目录,以及配置文件和日志文件等。

mysql

📝不同平台、不同安装方式(源码安装、二进制解压)的目录结构有所不同,具体可以参考 MySQL 官方文档

安装目录

安装目录(Base Directory)是 MySQL 服务器的安装路径,Linux 上使用 RPM 包安装的默认位置为 /usr/。安装目录中主要包含以下内容:

文件或目录描述
bin/mysql 客户端和实用程序目录
sbin/mysqld 服务器程序目录
share/man/Unix 帮助手册页目录
include/mysql/头文件目录
lib/mysql/库文件目录
share/mysql/各种字符集、语言相关的错误信息目录

我们可以使用下面的命令查看安装目录:

mysql> show global variables like "%basedir%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr/ |
+---------------+-------+
1 row in set (0.00 sec)

数据目录

数据目录(Data Directory)是 MySQL 存储数据库文件的位置,Linux 上使用 RPM 包安装的默认位置为 /var/lib/mysql/。数据目录中主要包含以下内容:

文件或目录描述
#innodb_temp/InnoDB 会话临时表空间目录
mysql/系统数据库 mysql 文件目录
performance_schema/性能数据库 performance_schema 文件目录
sys/sys 数据库文件目录
其他子目录每个数据库对应一个文件目录,存储该数据库中的文件
auto.cnf当前服务器实例的 UUID,用于主从复制
binlog.*二进制日志 binary log 相关文件
*.pemSSL 连接相关的证书和密钥文件
ib_buffer_pool缓冲区 buffer pool 中数据页的页号转储文件
ibdata1InnoDB 表空间文件
ib_logfile0
ib_logfile1
InnoDB 事务日志(REDO)文件
ibtmp1InnoDB 临时表空间文件
mysql.ibd系统数据库 mysql 数据文件
mysql.sockUnix 套接字文件
undo_001
undo_002
InnoDB UNDO 表空间文件

我们可以使用以下命令查看数据目录:

mysql> show global variables like "%datadir%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

配置文件

MySQL 服务器进程和各种工具程序启动时,需要通过配置文件(my.cnf 或者 my.ini)读取各种参数。Linux 上使用 RPM 包安装的默认配置文件为 /etc/my.cnf,可以使用mysqld --verbose --help查看读取配置文件的顺序:

[root@sqlhost ~]# mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

服务器运行使用的所有配置选项都可以使用SHOW VARIABLES命令查看,例如以下命令可以查看 InnoDB 缓冲池相关的配置:

mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 8388608        |
+-------------------------------------+----------------+
11 rows in set (0.00 sec)

除此之外,MySQL 还使用一些其他的文件:

  • 错误日志文件,例如 /var/log/mysqld.log;
  • 慢查询日志文件,例如 /var/lib/mysql/HOSTNAME-slow.log;
  • 服务器进程 PID 文件,例如 /var/run/mysqld/mysqld.pid;

MySQL 逻辑结构

MySQL 使用典型的客户端/服务器(Client/Server)结构,逻辑结构图如下所示:

mysql logical
MySQL 体系结构大体可以分为三层:客户端、服务器层以及存储引擎层。其中,服务器层又包括了连接管理、查询缓存 、SQL 接口、解析器、优化器、缓冲与缓存以及各种管理工具与服务等。

具体来说,每个组件的作用如下:

  • 客户端,连接 MySQL 服务器的各种工具和应用程序。例如 mysql 命令行工具、mysqladmin 以及各种驱动程序等。
  • 连接管理,负责监听和管理客户端的连接以及线程处理等。每一个连接到 MySQL 服务器的请求都会被分配一个连接线程。连接线程负责与客户端的通信,接受客户端发送的命令并且返回服务器处理的结果。
  • 查询缓存 ,用于将执行过的 SELECT 语句和结果缓存在内存中。每次执行查询之前判断是否命中缓存,如果命中直接返回缓存的结果。缓存命中需要满足许多条件,SQL 语句完全相同,上下文环境相同等。实际上除非是只读应用,查询缓存的失效频率非常高,任何对表的修改都会导致缓存失效;因此,查询缓存在 MySQL 8.0 中已经被删除。
  • SQL 接口,接收客户端发送的各种 DML和 DDL 命令,并且返回用户查询的结果。另外还包括所有的内置函数(日期、时间、数学以及加密函数)和跨存储引擎的功能,例如存储过程、触发器、视图等。
  • 解析器,对 SQL 语句进行解析,例如语义和语法的分析和检查,以及对象访问权限检查等。
  • 优化器,利用数据库的统计信息决定 SQL 语句的最佳执行方式。使用索引还是全表扫描的方式访问单个表,多表连接的实现方式等。优化器是决定查询性能的关键组件,而数据库的统计信息是优化器判断的基础。
  • 缓存与缓冲,由一系列缓存组成的,例如数据缓存、索引缓存以及对象权限缓存等。对于已经访问过的磁盘数据,在缓冲区中进行缓存;下次访问时可以直接读取内存中的数据,从而减少磁盘 IO。
  • 存储引擎,存储引擎是对底层物理数据执行实际操作的组件,为服务器层提供各种操作数据的 API。MySQL 支持插件式的存储引擎,包括 InnoDB、MyISAM、Memory 等。
  • 管理工具,MySQL 提供的系统管理和控制工具,例如备份与恢复、复制、集群等。

我们使用 mysql 客户端工具连接到 MySQL 服务器:

[root@sqlhost ~]# mysql -h 192.168.56.104 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

以上方式是通过 TCP/IP 网络协议连接到 MySQL 服务器,需要提供服务器的 IP、端口以及用户名和密码等信息。如果提供的信息不正确,将会返回错误消息。另外,我们也可以通过命名管道或者 UNIX 套接字进行连接。

连接成功之后就可以执行各种语句和命令,我们以一个查询语句为例:

SEELCT * FROM employees;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEELCT * FROM employees' at line 1

以上语句通过 SQL 查询接口提交给服务器的处理线程,解析器进行语法检查时发现 SELECT 写成了 SEELCT,提示的是语法错误。示例表来自《SQL 入门教程》示例数据库

如果语句没有问题,通过解析器之后生成解析树,然后发送给优化器;优化器判断是否进行查询重写,并且根据统计信息决定访问表的方式和顺序等。我们可以通过 EXPLAIN 命名了解优化器选择的执行计划:

mysql> EXPLAIN SELECT * FROM employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  107 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这里我们查询的是整个表的数据,因此 MySQL 采用全表扫描的方式(type = ALL)获取数据。关于执行计划的解释,可以参考这篇文章

最后,由存储引擎获取表中的数据;如果数据已经被缓存,可以直接从缓冲区获取。

MySQL 存储引擎

插件式存储引擎是 MySQL 的一大特点体系结构,每个存储引擎都提供了各自的功能,用户可以根据业务或者应用场景为数据表选择不同的存储引擎。也就是说,存储引擎的设置是在表级别的;因此也被称为表类型(table type)。

📝从 MySQL 5.5 之后,默认的存储引擎是 InnoDB。InnoDB 是一个通用的存储引擎,除非有特殊需求,推荐使用 InnoDB。

MySQL 插件式存储引擎结构允许在 MySQL 服务器运行时装载和卸载一个存储引擎,使用SHOW ENGINES语句可以查看当前服务器支持的存储引擎:

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

其中,Support 列显示了是否支持该存储引擎以及默认的存储引擎。MySQL 8.0 支持的存储引擎如下:

  • InnoDB:默认的存储引擎。InnoDB 是一个事务安全(ACID 兼容)的存储引擎,支持事务的提交、回滚以及故障恢复功能。InnoDB 行级锁(无需升级到更高粒度的锁)以及类似 Oracle 的一致性非锁定读功能能够增加多用户并发性和性能。InnoDB 使用聚集索引存储用户数据,减少了常见的基于主键查询时的 I/O 操作。为了维护数据完整性,InnoDB 还支持 FOREIGN KEY 参照完整性约束。
  • MyISAM:这种类型的表占用很少的磁盘空间。表级锁限制了它的并发读/写性能,因此通常用于只读或者以读为主的 Web 应用和数据仓库。
  • Memory:将所有数据存储在 RAM 中,用于需要快速查找的非关键数据。Memory 之前被称为 HEAP 存储引擎。它的使用场景越来越少;InnoDB 中的 buffer pool 内存区提供了将大部分或全部数据保留在内存中的一个通用持久性方法,NDBCLUSTER 为海量分布式数据集提供了快速的键-值查找。
  • CSV:这种类型的表实际上就是逗号分隔符文本文件。CSV 表可以导入或导出 CSV 格式的数据,用于脚本和应用程序之间的数据交换。由于 CSV 表不支持索引,通常可以在日常操作中将数据存储在 InnoDB 表中,只在导入或导出阶段使用 CSV 表。
  • Archive:这种压缩格式的无索引表主要用于存储和检索大量的很少使用的历史、归档或者安全审计数据。
  • Blackhole:Blackhole 存储引擎不会实际存储数据,类似于 Unix 中的 /dev/null 设备。查询永远不会返回数据。这种表可以用于特定的复制环境,DML 语句需要发送到从服务器,但是主服务器自身不保存数据。
  • NDB (也就是 NDBCLUSTER):这种集群数据库引擎主要用于对服务可用性要求极高的场景。
  • Merge:支持 MySQL DBA 或者开发人员将许多结构一致的 MyISAM 表作为一个逻辑对象使用。可以用于 VLDB 环境,例如数据仓库。
  • Federated:提供访问远程数据库的功能,可以将多个 MySQL 物理服务器组合成一个逻辑数据库。非常适合分布式环境或者数据集市。
  • Example:这是 MySQL 源代码中的一个示例存储引擎,用于演示如何编写新的存储引擎。它主要面向开发者,这种类型的表无法存储数据,也不会返回任何结果。

存储引擎的设置不在服务器级别,也不在数据库(模式)级别;用户可以为不同的表指定不同的存储引擎。例如,一个应用程序大多数的表使用 InnoDB 存储引擎;同时创建一个 CSV 表,用于将数据导出到电子表格文件中;另外创建一些 MEMORY 表作为临时存储区。

下表描述了 MySQL 中常见存储引擎支持的功能特性。

功能特性MyISAMMemoryInnoDBArchiveNDB
B-树索引✔️✔️✔️
备份/时间点恢复(1)✔️✔️✔️✔️✔️
集群数据库✔️
聚集索引✔️
压缩数据✔️ (2)✔️✔️
数据缓存N/A✔️✔️
加密数据✔️ (3)✔️ (3)✔️ (4)✔️ (3)✔️ (3)
外键✔️✔️ (5)
全文搜索索✔️✔️ (6)
空间数据类型✔️✔️✔️✔️
空间数据索引✔️✔️ (7)
哈希索引✔️❌ (8)✔️
索引缓存✔️N/A✔️✔️
锁的粒度表级表级行级行级行级
MVCC✔️
复制 (1)✔️有限(9)✔️✔️✔️
存储限制256TBRAM64TB384EB
T-树索引✔️
数据库事务✔️✔️
更新数据字典统计信✔️✔️✔️✔️✔️

注释:

  1. 在服务器层实现,而不是存储引擎层。
  2. 只有使用行压缩格式才支持 MyISAM 压缩表。使用行压缩格式的 MyISAM 表属于只读表。
  3. 在服务器层通过加密函数实现。
  4. 在服务器层通过加密函数实现;MySQL 5.7 开始支持表空间静态数据(data-at-rest)加密。
  5. MySQL Cluster NDB 7.3 开始支持外键约束。
  6. MySQL 5.6 开始支持 InnoDB 的 FULLTEXT 索引。
  7. MySQL 5.7 开始支持 InnoDB 的空间数据索引。
  8. InnoDB 内部使用哈希索引实现自适应哈希索引(Adaptive Hash Index)特性。
  9. 内存表的复制

下一篇我们来具体谈谈 InnoDB 存储引擎的体系结构。欢迎关注❤️、点赞👍、转发📣!

<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL性能、可用性、扩展性都提出了很高要求。使用MySQL解决大量数据以及高并发请求已经是程序员必备技能,也是衡量一个程序员能力和薪资标准一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引使用和优化技巧,能够避开很多实战中遇到“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数和通用表表达式。企业中许多报表统计需求,如果不采用窗口函数,用普通 SQL 语句是很难实现。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制和读写分离。在企业生产环境中,很少采用单台MySQL节点情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构实现是非常有必要。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作第一步,就是高效准备面试,面试篇主要从知识点回顾总结角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪工作机会。</span> </p>
相关推荐
<p style="font-size:14px;color:#333333;"> <strong><span style="font-size:16px;">课程简介</span></strong> </p> <p style="font-size:14px;color:#333333;"> MySQL 是最流行关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好 RDBMS(关系数据库管理系统)应用软件一, 广泛应用在各个领域。 </p> <p style="font-size:14px;color:#333333;"> 本课程作为MySQL高级课程, 主要讲解了MySQL视图/存储过程/触发器/索引等对象使用、常见SQL语句优化技巧 、应用优化、数据库优化、数据库日志等方面知识,并通过综合案例,对课程中知识进行一个整合应用。本课程旨在通过MySQL高级部分内容,可以在满足现有业务需求基础上,对MySQL底层体系结构, 及底层优化有一个深入理解 , 对系统整体性能进行提升。 </p> <p style="font-size:14px;color:#333333;"> <strong>简短简介 : </strong> </p> <p style="font-size:14px;color:#333333;"> <span></span>MySQL 是最流行关系型数据库一,广泛应用在各个领域。本课程主要讲解了MySQL常见对象,并详细讲解了优化策略,并通过案例使我们深入理解SQL优化策略,学习完本课程使我们能够独立自主完成各种SQL优化,提升系统性能。 </p> <p style="font-size:14px;color:#333333;"> <strong><span style="font-size:16px;">适应人群</span></strong> </p> <p style="font-size:14px;color:#333333;"> <span></span>具有一定MySQL基础, 对数据库高级知识、优化方面感兴趣学员。 </p> <p style="font-size:14px;color:#333333;"> <strong><span style="font-size:16px;">课程亮点</span></strong> </p> <p style="font-size:14px;color:#333333;"> <span></span>本课程系统讲解了SQL优化思路和实用技巧,并讲解了数据库方面优化内容, 全方面MySQL数据库进行优化。 </p>
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值