MySQL架构研究及性能优化

概括

MySQL 是一个基于 C 和 C++ 编程语言开发的关系数据库管理系统，由 MySQL AB 创建，该公司由 David Axmark、Allan Larsson 和 Michael Widenius 创立。最新版本是MySQL 9.0。

MySQL 是一个具有 GNU 许可证的开源项目。

MySQL是现在最流行、最实用的数据库管理系统，因为它开源、功能齐全、性能高。

建筑学

• MySQL架构图：来源

客户端层

• MySQL系统架构第一层。
• 这一层的重要服务是连接处理、身份验证和安全。
  • 连接处理：管理来自客户端的连接。当客户端想要连接到 MySQL 服务器时，会为该连接创建一个新线程。创建新连接时，服务器会缓存线程。
  • 身份验证：当客户端连接到服务器时，它必须提供身份验证信息才能建立连接。通常，客户端使用用户名和密码进行身份验证。
  • 安全性：MySQL 提供了很多功能来保护数据库并在身份验证后验证用户权限。

服务器层

• MySQL 的下一层负责处理查询语句并提供许多实用程序。
• 主要特点：
  • 解析器：当客户端请求查询语句时，服务器对其进行分析以创建查询树，重写查询，并定义执行查询语句的顺序和索引。
  • 优化器：它在执行之前优化查询语句。优化器并不关心使用什么引擎，但存储引擎会影响特定查询的优化。
  • 查询缓存：查询缓存存储查询语句的结果。如果客户端再次请求相同的查询，服务器将返回缓存的结果，绕过解析和优化步骤。由于瓶颈问题，此功能在 8.0 或更高版本中已被弃用。
  • 服务和实用程序：
    • 备份与恢复
    • 安全性：提供商用户和权限系统
    • 复制：这是一个将数据从主服务器复制并同步到许多子服务器的过程。
    • 集群
    • 分区：使用特定逻辑将表分成许多部分。
    • Workbench：它是一个可视化工具，旨在与 MySQL 交互和使用。

存储层

• 这是我们在数据库中存储数据的方式的一层责任。
• 默认情况下，MySQL 使用 InnoDB 存储引擎。
• MySQL支持许多不同的存储引擎：
  • InnoDB（默认）
  • MyISAM
  • 记忆
  • CSV
  • 存档
  • 黑洞
  • 合并
  • 联邦

优化 MySQL 性能

优化SQL语句

解释声明

• 不执行的查询预测

explain sql_statememnt

• 执行查询后解释

explain analyze sql_statement

索引技术

• 它提升了查询和搜索时的性能。
• 为多列创建索引时，第一列非常重要。它决定查询语句的性能。例如，如果您在（姓名，生日）上创建索引，则使用“姓名”列的查询将利用该索引，但使用“生日”列的查询则不会。
• 创建新索引的语法：

create index idx_birthday on customers(birthday);

• 该命令将使查询优化器无法访问指定的索引，从而阻止查询执行计划考虑它：

alter table customers alter index idx_birthday invisible;

分割

• 当数据库大小大于2GB（或超过1000万条记录）时，应该对该数据库使用分区。
• 使用分区时，性能将会提高。
• 分区计划应该基于WHERE子句中经常使用的列。

优化数据库参数

缓冲区高速缓存命中

• 它是系统从缓冲区缓存而不是从物理存储加载的数据的百分比。
• 如果百分比低于90%，那么你必须对其进行优化。
• 取决于2个参数：
  • Innodb_buffer_pool_read_requests：发送到缓冲池的请求总数。命令：显示全局状态，如 '%Innodb_buffer_pool_read_requests%
  • Innodb_buffer_pool_reads：内存中缓冲池中找不到的必须从磁盘读取的请求总数。命令：显示全局状态，如 '%Innodb_buffer_pool_reads%
• 计算公式：

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

表缓存命中

• MySQL 会缓存表以便在内存中快速查询。
• 如果百分比低于80%，那么你必须对其进行优化。
• 取决于2个参数：
  • Open_tables：在缓存中打开的表总数。命令：显示全局状态，如“Open_tables”
  • Opened_tables：打开的表总数。命令：显示全局状态，如“Opened_tables”
• 计算公式：Open_tables/Opened_tables

表定义缓存命中

• 执行查询时，MySQL 需要有关表的信息，例如表名、列数和行数。此信息称为表定义。
• 如果百分比低于80%，那么你必须对其进行优化。
• 取决于2个参数：
  • Open_table_definitions：缓存中的总定义表。命令：显示全局状态，如“Open_table_definitions”
  • Opened_tables：请求的定义表总数。命令：显示全局状态，如“Opened_tables”
• 计算公式：Open_table_definitions/Opened_table_definitions

内存中的临时表

• 许多 SQL 语句（例如 Order、Group By...）必须使用临时表。
• 如果百分比低于80%，那么你必须对其进行优化。
• 取决于2个参数：
  • Created_tmp_disk_tables：在磁盘上创建的临时表总数。命令：显示全局状态，如“%Created_tmp_disk_tables%”
  • Created_tmp_tables：创建的临时表总数。命令：显示全局状态，如“%Created_tmp_tables%”
• 计算公式：(Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

结论

该博客提供有关 MySQL 的基于研究的知识。 MySQL 是一种关系数据库管理系统，以其低成本、广泛的实用程序和高性能而闻名，使其适用于广泛的项目。虽然本博客涵盖了 MySQL 的许多功能和方面，但还有更多内容值得探索。我将在未来的博客中更深入地研究其他功能和知识。

感谢您的阅读，我们下一篇博客见。

参考文件

• MySQL 文档
• MySQL 课程 - Tran Quoc Huy
• 维基百科 - MySQL

以上是MySQL架构研究及性能优化的详细内容。更多信息请关注PHP中文网其他相关文章！