MySQL8 管理手册（五）

原文：zh.annas-archive.org/md5/D5BC20BC3D7872C6C7F5062A8EE852A4

译者：飞龙

协议：CC BY-NC-SA 4.0

第十二章：优化 MySQL 8

在上一章中，我们了解了安全性，这是任何生产级应用程序的重要方面。该章节以安全性介绍和识别常见安全问题开始。随后，该章节涵盖了 MySQL 8 中的访问控制机制、账户管理和加密。我们在该章节的后部分了解了各种 MySQL 8 安全插件。安全性是每个生产级应用程序的重要基准。这就是为什么上一章是重要的。

沿着类似的线路，为了开发高度优化的数据库，本章重点介绍了优化方法。它从 MySQL 8 优化的概述开始。它带领读者了解 MySQL 8 服务器和客户端的优化，优化数据库结构，以及优化常见查询和数据库表。在本章的后面部分，重点放在了缓冲和缓存技术上。

以下是要涵盖的主题列表：

• MySQL 8 优化概述

• 优化 MySQL 8 服务器和客户端

• 优化数据库结构

• 优化查询

• 优化表

• 利用缓冲和缓存

MySQL 8 优化概述

让我们从理解 MySQL 8 优化开始。优化是识别性能瓶颈并实施优化解决方案以克服这些问题的过程。MySQL 8 中的优化涉及在多个不同级别进行性能测量、配置和调整。对于管理员来说，在不同级别优化性能是一项重要任务，比如单个 SQL 查询、整个数据库应用程序、数据库服务器或分布式数据库服务器。在 CPU 和内存级别进行性能优化可以提高可伸缩性。它还允许数据库处理更复杂的查询而不会使数据库服务器变慢。

数据库的性能取决于多个因素。在数据库级别，这些因素可以是表、查询和配置。数据库服务器的启动和数据库查询执行是这些构造影响 CPU 或在硬件级别执行 I/O（输入/输出）操作的事件之一。这是 MySQL 8 数据库管理员的责任：确保硬件性能达到最佳水平。需要确保硬件以最大效率使用。在软件级别上，性能优化从学习通用规则和指南以及用时性能测量开始。逐渐地，我们了解各种数据库操作的内部。我们可以用 CPU 周期和 I/O 操作来衡量性能。为了获得最佳的数据库性能，我们可以在基本级别优化软件和硬件配置。在高级水平上，我们可以通过开发自定义存储引擎和硬件设备来改进 MySQL 本身，从而扩展 MySQL 生态系统。

优化数据库

使数据库以最佳速度运行的最重要因素是什么？答案是，基本的数据库设计。以下是数据库设计需要注意的检查表：

• 数据库列必须是正确的数据类型。表必须为所需的目的具有适当的列。对数据库执行频繁操作的应用程序具有较少的列的许多表，而分析大量数据的应用程序具有许多列的有限表。

• 正如我们在之前的章节中学到的，数据库索引在增强查询性能方面起着重要作用。因此，为了提高查询执行效率，正确的索引放置非常重要。

• 我们在早期章节中讨论了数据库存储引擎，比如 MyISAM 或 InnoDB。对于每个单独的表使用适当的存储引擎是重要的。InnoDB 对于事务性数据库表更为合适，而 MyISAM 对于定义非事务性数据库表更为合适。存储引擎的选择在定义数据库的性能和可伸缩性方面起着至关重要的作用。

• 在 MySQL 8 数据类型章节中，我们详细了解了行格式。对于每个表来说，拥有适当的行格式是非常重要的。行格式的选择取决于所选择的存储引擎。压缩表占用更少的磁盘空间，并且需要更少的磁盘 I/O 操作。对于 InnoDB 表，压缩适用于所有读写操作。相反，只有对于只读的 MyISAM 表才适用压缩。

• MySQL 数据库支持多种锁定策略。锁定可以是表级别的，也可以是行级别的。应用程序必须使用适当的锁定策略。通过在适当的地方授予共享访问权限，可以实现并发运行数据库操作。此外，应该可以请求独占访问，以便可以执行关键的数据库操作，并保持数据完整性和优先级。在这种情况下，存储引擎的选择再次变得重要。InnoDB 存储引擎处理大多数锁定问题而无需用户参与。它允许更好的并发性，并减少了对代码的实验和调整的数量。

• 内存区域必须使用正确的缓存大小。它应该足够大，以容纳频繁访问的数据，同时又不会过载物理内存并导致分页。InnoDB 缓冲池和 MyISAM 关键缓存是需要配置的主要内存区域。

对于新创建的表，MyISAM 是默认的存储引擎。在实际使用中，InnoDB 的高级性能特性意味着使用 InnoDB 存储引擎的表在操作繁重的数据库中表现优于 MyISAM 表。

优化硬件

增长是每个软件应用的本质。随着应用程序的增长，数据库也会增长。数据库在执行操作时变得越来越繁忙。在某一点上，数据库应用程序最终会达到硬件限制。管理员必须评估调整应用程序或重新配置服务器以避免这些问题的可能性。还应该评估是否增加更多的硬件资源会有所帮助。系统瓶颈通常来自以下来源：

• 磁盘搜索：作为磁盘读取操作的一部分，查找数据需要时间。现代磁盘的平均查找数据时间通常低于 10 毫秒。因此，理论上应该是每秒 100 次搜索。随着技术的进步，新的磁盘在磁盘时间上有所改进，但很难为单个表进行优化。要优化搜索时间，需要将数据分布在多个磁盘上。

• 磁盘读写：要从磁盘读取或写入数据，需要磁盘处于正确的位置。一个磁盘至少提供每秒 10 到 20MB 的吞吐量（吞吐量是每秒读取或写入的数据量）。因此，读取和写入吞吐量比搜索时间更容易优化，因为我们可以从多个磁盘并行读取。

• CPU 周期：我们必须在主内存中处理数据以获得所需的结果。对于大表来说，内存的数量是最常见的限制因素。然而，对于小表来说，速度通常不是问题。

• 内存带宽：在罕见的情况下，当 CPU 需要的数据超过 CPU 缓存内存的容量时，主内存带宽成为瓶颈。

优化 MySQL 8 服务器和客户端

本节重点介绍了 MySQL 8 数据库服务器和客户端的优化，从优化服务器开始，然后优化 MySQL 8 客户端实体。本节更适合数据库管理员，以确保多个服务器的性能和可伸缩性。这也将帮助准备脚本的开发人员（包括设置数据库）和运行 MySQL 进行开发和测试的用户，以最大限度地提高生产力。

优化磁盘 I/O

在本节中，我们将学习如何配置存储设备，以将更多和更快的存储硬件专用于数据库服务器。磁盘寻址（查找磁盘上正确位置以读取或写入内容）是一个主要的性能瓶颈。当数据量足够大以至于无法进行缓存时，磁盘寻址的问题就会显现出来。在大型数据库中，数据访问基本上是随机进行的，因此我们需要至少进行一次磁盘寻址操作来读取数据，以及进行多次磁盘寻址操作来写入数据。我们应该使用适当的磁盘来调节或最小化磁盘寻址时间。

为了解决磁盘寻址性能问题，可以增加可用磁盘轴承的数量，将文件符号链接到不同的磁盘，或者进行磁盘分区。以下是详细信息：

• 使用符号链接：在使用符号链接时，我们可以为索引和数据文件创建 Unix 符号链接。在MyISAM表的情况下，符号链接从数据目录中的默认位置指向另一个磁盘。这些链接也可以进行条带化。这将改善寻址和读取时间。假设磁盘没有同时用于其他目的。符号链接不支持InnoDB表。但是，我们可以将InnoDB数据和日志文件放在不同的物理磁盘上。

• 条带化：在条带化中，我们有许多磁盘。我们将第一个块放在第一个磁盘上，第二个块放在第二个磁盘上，依此类推。第N块放在（N % 磁盘数）磁盘上。如果条带大小完全对齐，正常数据大小将小于条带大小。这将有助于提高性能。条带化取决于条带大小和操作系统。在理想情况下，我们会使用不同的条带大小对应用程序进行基准测试。条带化的速度差异取决于我们使用的参数，如条带大小。性能差异还取决于磁盘数量。我们必须选择是要优化随机访问还是顺序访问。为了获得可靠性，我们可能决定设置条带化和镜像（RAID 0+1）。RAID代表独立驱动器冗余阵列。这种方法需要 2 x N驱动器来容纳N驱动器的数据。通过良好的卷管理软件，我们可以有效地管理这种设置。

• 还有另一种方法。根据数据类型的重要性，我们可以改变 RAID 级别。例如，我们可以将非常重要的数据，如主机信息和日志，存储在 RAID 0+1 或 RAID N 磁盘上，而将较重要的数据存储在 RAID 0 磁盘上。在 RAID 的情况下，奇偶校验位用于确保存储在每个驱动器上的数据的完整性。因此，如果要执行太多写操作，RAID N 将成为一个问题。在这种情况下，更新奇偶校验位所需的时间较长。

• 如果维护文件上次访问时间不重要，我们可以使用-o noatime选项挂载文件系统。此选项跳过文件系统上的更新，从而减少磁盘寻址时间。我们还可以使文件系统异步更新。根据文件系统是否支持，我们可以设置-o async选项。

使用 NFS 与 MySQL

在使用网络文件系统（NFS）时，可能会出现各种问题，这取决于操作系统和 NFS 版本。以下是详细信息：

• NFS 系统存在数据不一致性的问题。这可能是因为接收到的消息顺序不正确或网络流量丢失。我们可以使用带有hard和intr挂载选项的 TCP 来避免这些问题。

• MySQL 数据和日志文件如果放在 NFS 驱动器上，可能会被锁定并变得无法使用。如果多个 MySQL 实例访问相同的数据目录，可能会导致锁定问题。MySQL 的不正确关闭或断电是文件系统锁定问题的其他原因。最新版本的 NFS 支持咨询和基于租约的锁定，有助于解决锁定问题。但是，不建议在多个 MySQL 实例之间共享数据目录。

• 必须了解最大文件大小限制，以避免任何问题。使用 NFS 2，客户端只能访问文件的较低 2 GB。NFS 3 客户端支持更大的文件。最大文件大小取决于 NFS 服务器的本地文件系统。

优化内存的使用

为了提高数据库操作的性能，MySQL 分配缓冲区和缓存内存。默认情况下，MySQL 服务器在具有 512 MB RAM 的虚拟机（VM）上启动。我们可以修改 MySQL 的默认配置，使其在有限内存系统上运行。

以下列表描述了优化 MySQL 内存的方法：

• 用于缓存InnoDB数据的表、索引和其他辅助缓冲区的内存区域称为InnoDB缓冲池。缓冲池分为页面。页面包含多行。缓冲池实现为页面的链表，以实现高效的缓存管理。使用算法从缓存中删除很少使用的数据。缓冲池大小是系统性能的重要因素。innodb__buffer_pool_size系统变量定义了缓冲池大小。InnoDB在服务器启动时分配整个缓冲池大小。建议将系统内存的 50%至 75%用于缓冲池大小。

• 使用MyISAM，所有线程共享关键缓冲区。key_buffer_size系统变量定义了关键缓冲区的大小。索引文件为服务器打开的每个MyISAM表打开一次。对于访问表的每个并发线程，数据文件只打开一次。为每个并发线程分配一个表结构，每个列的列结构以及一个 3 x N大小的缓冲区。MyISAM存储引擎为内部使用维护了一个额外的行缓冲区。

• 优化器通过扫描来估计多行的读取。存储引擎接口使优化器能够提供有关记录缓冲区大小的信息。缓冲区的大小可以根据估计的大小而变化。为了利用行预取功能，InnoDB使用可变大小的缓冲能力。它减少了锁定和 B 树导航的开销。

• 通过将myisam_use_mmap系统变量设置为 1，可以为所有MyISAM表启用内存映射。

• 内存临时表的大小可以由tmp_table_size系统变量定义。堆表的最大大小可以使用max_heap_table_size系统变量定义。如果内存表变得太大，MySQL 会自动将表从内存转换为磁盘上。磁盘上临时表的存储引擎由internal_tmp_disk_storage_engine系统变量定义。

• MySQL 配备了 MySQL 性能模式。这是一个监视 MySQL 低级执行的功能。性能模式通过根据实际服务器负载调整其内存使用来动态分配内存，而不是在服务器启动时分配内存。一旦分配了内存，直到服务器重新启动才会释放。

• 服务器用于管理客户端连接的每个线程都需要特定的空间。堆栈大小由thread_stack系统变量控制。连接缓冲区由net_buffer_length系统变量控制。结果缓冲区由net_buffer_length控制。连接缓冲区和结果缓冲区以net_buffer_length字节开始，但根据需要扩大到max_allowed_packets字节。

• 所有线程共享相同的基本内存。

• 所有连接子句都在单次执行中执行。大多数连接可以在不使用临时表的情况下执行。临时表是基于内存的哈希表。包含BLOB数据和行长度较大的表存储在磁盘上。

• 为每个请求分配一个读取缓冲区，该请求对表进行顺序扫描。读取缓冲区的大小由read_buffer_size系统变量确定。

• 在以任意方式读取行时分配了一个随机读取缓冲区，以避免磁盘寻道。缓冲区大小由read_rnd_buffer_size系统变量确定。

• 线程分配的内存在线程不再需要时被释放。释放的内存会被返回给系统，除非线程被放入线程缓存中。

• 当执行FLUSH TABLES或mysqladmin flush-table 命令时，MySQL 会立即关闭所有未使用的表。当当前线程执行完成时，它标记所有正在使用的表将被关闭。这会释放正在使用的内存。FLUSH TABLES仅在所有表关闭后才返回。

可以监视 MySQL 性能模式和内存使用情况的 sys 模式。在执行此操作之前，我们必须在 MySQL 性能模式上启用内存工具。可以通过更新性能模式setup_instruments表的ENABLED列来完成。以下是查询 MySQL 中可用内存工具的查询：

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%';

如果在启动时启用了内存工具，则可以确保在启动时对内存分配进行计数。

此查询将返回数百个内存工具。我们可以通过指定代码区域来缩小范围。以下是将结果限制为InnoDB内存工具的示例：

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%'; 
+-------------------------------------------+---------+-------+ 
|                    NAME                   | ENABLED | TIMED | 
+-------------------------------------------+---------+-------+ 
|     memory/innodb/adaptive hash index     |    NO   |   NO  | 
|     memory/innodb/buf_buf_pool            |    NO   |   NO  | 
| memory/innodb/dict_stats_bg_recalc_pool_t |    NO   |   NO  | 
|   memory/innodb/dict_stats_index_map_t    |    NO   |   NO  | 
| memory/innodb/dict_stats_n_diff_on_level  |    NO   |   NO  | 
|         memory/innodb/other               |    NO   |   NO  | 
|         memory/innodb/row_log_buf         |    NO   |   NO  | 
|          memory/innodb/row_merge_sort     |    NO   |   NO  | 
|             memory/innodb/std             |    NO   |   NO  | 
|      memory/innodb/trx_sys_t::rw_trx_ids  |    NO   |   NO  |
+-------------------------------------------+---------+-------+ 

以下是启用内存工具的配置：

performance-schema-instrument='memory/%=COUNTED'

以下是在性能模式中的memory_summary_global_by_event_name表中查询内存工具数据的示例：

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G;

EVENT_NAME: memory/innodb/buf_buf_pool
COUNT_ALLOC: 1
COUNT_FREE: 0
SUM_NUMBER_OF_BYTES_ALLOC: 137428992
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 1
HIGH_COUNT_USED: 1
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 137428992
HIGH_NUMBER_OF_BYTES_USED: 137428992

它通过EVENT_NAME对数据进行汇总。

以下是查询 sys 模式以按代码区域聚合当前分配的内存的示例：

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS        
  code_area, sys.format_bytes(SUM(current_alloc))        
  AS current_alloc        
  FROM sys.x$memory_global_by_current_bytes        
  GROUP BY SUBSTRING_INDEX(event_name,'/',2)        
  ORDER BY SUM(current_alloc) DESC; 
+---------------------------+---------------+ 
| code_area                 | current_alloc | 
+---------------------------+---------------+ 
| memory/innodb             | 843.24 MiB    | 
| memory/performance_schema | 81.29 MiB     | 
| memory/mysys              | 8.20 MiB      | 
| memory/sql                | 2.47 MiB      | 
| memory/memory             | 174.01 KiB    | 
| memory/myisam             | 46.53 KiB     | 
| memory/blackhole          | 512 bytes     | 
| memory/federated          | 512 bytes     | 
| memory/csv                | 512 bytes     | 
| memory/vio                | 496 bytes     | 
+---------------------------+---------------+

优化网络的使用

MySQL 数据库服务器打开网络接口以与客户端连接，并开始监听这些接口。连接管理器线程负责处理客户端连接请求。连接管理器线程还处理 Unix 平台上的套接字文件。连接管理器线程处理共享内存连接请求，另一个线程处理 Windows 系统上的命名管道连接请求。不会为服务器不监听的接口创建线程。

连接管理器线程为每个客户端连接分配一个线程。该线程对该客户端连接进行身份验证并处理请求处理。管理器线程首先在线程缓存中检查是否有可用于客户端连接的线程。如果缓存中没有可用线程，则创建一个新线程。一旦客户端请求被处理并且连接结束，为服务客户端连接创建的线程将被返回到线程缓存，除非缓存已满。

在这种线程连接模型中，线程的数量与当前连接的客户端数量相同。它也有缺点。当服务器需要扩展以处理比当前处理的连接数更多的连接时，线程的创建和处理变得昂贵。在这种线程连接模型中，每个线程都需要服务器和内核资源。

有些服务器变量可用于设置服务器以实现优化的网络使用。thread_cache_size是定义线程缓存大小的系统变量。线程缓存大小的默认值为 0。这意味着对于每个新连接，都需要设置一个线程，并在连接终止时进行处理。如果我们将thread_cache_size设置为 10，它将启用 10 个非活动连接线程进行缓存。当与其关联的客户端的连接终止时，线程连接变为非活动状态。

服务器可以处理的 SQL 语句的复杂性受线程堆栈大小的限制。MySQL 8 服务器可以使用--thread_stack=N启动，为每个线程设置N字节的堆栈大小。

设置线程缓存大小后，监视其影响变得至关重要。Threads_cached和Threads_created是用于查找线程缓存中的线程数以及因无法从缓存中获取而创建的线程数的状态变量。以下是查找服务器状态变量值的示例命令：

mysql> show global status;
+-----------------------------+--------+
| Variable_name               |  Value |
+-----------------------------+--------+
| Aborted_clients             |     0  |
| Aborted_connects            |     1  |
| Acl_cache_items_count       |     0  |
| Binlog_cache_disk_use       |     0  |
| Binlog_cache_use            |     0  |
| Binlog_stmt_cache_disk_use  |     0  |
| Binlog_stmt_cache_use       |     0  |
| Bytes_received              |    443 |
| Bytes_sent                  |    346 |
| Threads_cached              |     0  |
| Threads_connected           |     1  |
| Threads_created             |     1  |
| Threads_running             |     2  |
+-----------------------------+--------+

下面是过滤status变量的示例：

mysql> show status like '%Thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   |     0 |
| Performance_schema_thread_classes_lost   |     0 |
| Performance_schema_thread_instances_lost |     0 |
| Slow_launch_threads                      |     0 |
| Threads_cached                           |     0 |
| Threads_connected                        |     1 |
| Threads_created                          |     1 |
| Threads_running                          |     2 |
+------------------------------------------+-------+

优化锁定操作

如前几章所讨论的，MySQL 8 使用锁定机制来管理争用。当多个线程中同时执行查询尝试同时获取一个表时，就会发生争用。如果这些查询同时在表上执行，表数据将处于不一致状态。MySQL 8 支持两种类型的锁定：内部锁定和外部锁定。

MySQL 服务器内部由多个线程执行内部锁定，以管理对表内容的争用。这种类型的锁定完全由 MySQL 服务器执行，不涉及任何其他程序。那么，为什么它被称为内部锁定？在外部锁定的情况下，MySQL 服务器和其他程序锁定表文件，以决定哪些程序可以同时访问表。

以下是内部锁定的两种方法：

• 行级锁定。

• 表级锁定。

MySQL 中的行级锁定支持多个会话的同时写访问。这使得多用户和高并发应用程序成为可能。在单个表上执行多个并发写操作时，很可能会发生死锁。

为了避免这种死锁情况，锁定机制在事务开始时使用SELECT ... FOR UPDATE语句获取每组要修改的行的锁定。如果事务锁定多个表，MySQL 会在每个事务内以相同的顺序应用语句。InnoDB数据库引擎会自动检测死锁条件并回滚受影响的事务。考虑到这一点，死锁会影响性能。

在高度并发的系统中，死锁检测可能会导致减速。在这种情况下，禁用死锁检测会更有效。当发生死锁时，我们可以依靠innodb_lock_wait_timeout设置来进行事务回滚。使用innodb_deadlock_detect配置选项，我们可以禁用死锁检测。

以下是行级锁定的优点：

• 当不同会话访问表中的不同行时，锁冲突的数量较少

• 要回滚的更改数量较少

• 可以长时间锁定单个表行

表级锁定由 MySQL 用于MyISAM，MEMORY和MERGE表。在表级锁定的情况下，MySQL 一次只允许一个会话更新这些表。通过表级锁定，这些存储引擎适用于只读或单用户应用程序。这些存储引擎在查询开始时一次性请求所有所需的锁，以避免任何死锁。它总是以相同的顺序锁定表。表级锁定的主要缺点是影响并发性。如果其他会话需要修改表，则必须等到并发数据更改语句完成。

表级锁定的优点如下：

• 与行级锁定相比，它需要更少的内存

• 当用于表的大部分时，速度很快，因为只需要一个锁

• 如果频繁执行GROUP BY操作，速度很快

MySQL 授予表写锁的策略如下：

1. 如果表上没有写锁，则在表上放置写锁

2. 如果表已经有写锁，则将锁请求放入写锁队列

MySQL 授予表读锁的策略如下：

1. 如果表上没有读锁，则在表上放置读锁

2. 如果表已经有读锁，则将锁请求放入读锁队列

对表的更新比表的检索给予更高的优先级。当锁被释放时，写锁请求首先可用，然后是读锁请求。

以下是分析表锁争用的示例：

mysql> SHOW STATUS LIKE 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate |     5 |
| Table_locks_waited    |     0 |
+-----------------------+-------+

MyISAM存储引擎天然支持多个并发插入，以减少读者和写者对表的争用。它允许MyISAM表在数据文件中间插入行。如果表在数据文件中间没有空闲块，则行将插入到文件末尾。这使得 MySQL 能够同时在同一表上执行INSERT和SELECT查询。concurrent_insert是全局系统变量，控制MyISAM存储引擎允许执行并发INSERT和SELECT语句的行为。如果将此系统变量设置为AUTO，则允许并发INSERT和SELECT。

如果无法进行并发插入，并且我们想在表tab1上执行多个INSERT和SELECT操作，可以使用临时表temp_tab1来保存tab1表数据，并使用temp_tab1表中的行更新tab1表。以下是演示此场景的示例：

mysql> LOCK TABLES tab1 WRITE, temp_tab1 WRITE;
mysql> INSERT INTO tab1 SELECT * FROM temp_tab1;
mysql> DELETE FROM temp_tab1;
mysql> UNLOCK TABLES;

性能基准测试

在衡量性能时，我们必须考虑以下因素：

• 在衡量单个操作或一组操作的速度时，重要的是在数据库工作负载繁重的情况下模拟场景以进行基准测试

• 在不同的环境中，测试结果可能不同

• 根据工作负载，某些 MySQL 功能可能无法提高性能

MySQL 8 支持衡量单个语句的性能。如果要衡量任何 SQL 表达式或函数的速度，则使用BENCHMARK()函数。以下是该函数的语法：

BENCHMARK(loop_count, expression)

BENCHMARK函数的输出始终为零。速度可以通过 MySQL 在输出中打印的行来衡量。以下是一个例子：

mysql> select benchmark(1000000, 1+1);
+-------------------------+
| benchmark(1000000, 1+1) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.15 sec)

从上面的例子中，我们可以发现计算1+11000000 次所需的时间为0.15 秒。

检查线程信息

有时，我们可能需要弄清楚 MySQL 服务器在做什么。因此，有必要找出进程列表。进程列表是 MySQL 服务器内当前正在执行的线程集合。

获取进程列表信息的来源如下：

• SHOW [FULL] PROCESSLIST语句。以下是进程列表信息的示例：

mysql> show processlist;
+----+-----------------+-----------------+------+---------+--------+
| Id |       User      |       Host      |   db | Command |  Time  |
+----+-----------------+-----------------+------+---------+--------+
+------------------------+-----------------------+
|         State          |         Info          |
+------------------------+-----------------------+
+----+-----------------+-----------------+------+---------+--------+
| 4  | event_scheduler |      localhost  | NULL | Daemon  | 214901 |
+----+-----------------+-----------------+------+---------+--------+
|  8 |  root           | localhost:58629 | NULL |  Query  |     0  |
+----+-----------------+-----------------+------+---------+--------+
+------------------------+-----------------------+
| Waiting on empty queue |         NULL          |
+------------------------+-----------------------+
| starting               | show full processlist |
+------------------------+-----------------------+

• SHOW PROFILE语句。

• INFORMATION_SCHEMA PROCESSLIST表：

mysql> select * from information_schema.processlist;
+----+-----------------+-----------------+------+---------+--------+
| ID |       USER      |       HOST      |  DB  | COMMAND |  TIME  |
+----+-----------------+-----------------+------+---------+--------+
+------------------------+----------------------------------------------+
|         STATE          |                         INFO                 |
+------------------------+----------------------------------------------+
+----+-----------------+-----------------+------+---------+--------+
|  8 | root            | localhost:58629 | NULL | Query   |      0 |
+----+-----------------+-----------------+------+---------+--------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 215640 |
+----+-----------------+-----------------+------+---------+--------+
+------------------------+----------------------------------------------+
| executing              | select * from information_schema.processlist |
+------------------------+----------------------------------------------+
| Waiting on empty queue | NULL                                         |
+------------------------+----------------------------------------------+

• mysqladmin processlist命令。

• 性能模式线程表，阶段表和锁表。

我们必须能够查看用户线程的信息。需要PROCESS权限才能查看正在执行的线程的信息。要访问线程，不需要互斥访问。对 MySQL 服务器性能的影响较小。访问INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST需要互斥访问，并会影响性能。线程还提供后台线程的详细信息。INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST不提供有关后台线程的信息。

以下表格显示了每个进程列表条目中包含的信息：

信息	详情
Id	与线程关联的客户端连接标识符。
用户，主机	与线程相关的帐户。
db	线程的默认数据库或NULL。
命令，状态	表示线程当前正在做什么。
时间	表示线程在当前状态下已经多久。
信息	包含线程执行的语句的信息。

以下是与一般查询处理相关的线程状态值：

• 创建后：当线程创建表时发生，包括内部临时表

• 分析：当线程正在计算MyISAM键分布时发生

• 检查权限：当检查服务器是否具有执行 SQL 语句所需的权限时发生

• 检查表：当线程执行表检查操作时发生

• 清理：当线程处理完一个命令并释放内存时发生

• 关闭表：当线程刷新更改的表数据到磁盘并关闭已使用的表时发生

• 修改表：当服务器处理ALTER TABLE语句时发生

• 创建索引：当线程处理MyISAM表的ALTER TABLE ... ENABLE KEYS时发生

• 创建表：当线程正在创建表时发生

• end：在结束之前发生，但在清理ALTER TABLE，CREATE VIEW，DELETE，INSERT，SELECT或UPDATE语句之前

• 执行：当线程开始执行语句时发生

• init：在ALTER TABLE，DELETE，INSERT，SELECT和UPDATE语句初始化之前发生

以下是复制主服务器线程的binlog转储线程中的常见状态列表：

• 完成读取一个binlog；切换到下一个binlog

• 主服务器已将所有binlog发送到从属服务器；等待更多更新

• 向从属服务器发送binlog事件

• 等待最终终止

以下是从属服务器 I/O 线程的常见状态列表：

• 检查主服务器版本

• 连接到主服务器

• 将主事件排队到中继日志

• 在失败的binlog转储请求后重新连接

• 在失败的主服务器事件读取后重新连接

• 在主服务器上注册从属服务器

• 请求binlog转储

• 等待轮到自己提交

• 等待主服务器发送事件

• 等待主服务器更新

• 等待从属服务器退出的互斥

• 等待从属服务器 SQL 线程释放足够的中继日志空间

• 在失败的binlog转储请求后等待重新连接

• 在失败的主服务器事件读取后等待重新连接

以下是从属服务器 SQL 线程的常见状态列表：

• 杀死从属服务器

• 在重放LOAD DATA INFILE之前制作临时文件（追加）

• 在重放LOAD DATA INFILE之前制作临时文件（创建）

• 从中继日志读取事件

• 从属服务器已读取所有中继日志；等待更多更新

• 等待来自协调器的事件

• 等待从属服务器退出的互斥

• 等待从属服务器工作线程释放挂起事件

• 等待中继日志中的下一个事件

• 等待MASTER_DELAY秒，直到主服务器执行事件

优化数据库结构

作为数据库管理员，我们必须寻找有效的方法来组织表模式、表和列。我们最小化 I/O，提前规划，并将相关项目放在一起，以调整应用程序代码，以保持性能高并增加数据量。通常从高效的数据库设计开始，这样可以更容易地编写高性能的应用程序代码。这也使得数据库在应用程序发展或重写时能够自我维持。

优化数据大小

为了最小化磁盘上的空间，我们应该开始设计数据库表。这会带来巨大的性能改进，因为它减少了要写入和从磁盘读取的数据量。较小的表通常需要较少的主内存，而在查询执行期间活动处理内容。表数据空间的任何减少都会导致需要更小的索引，这样可以更快地处理。

正如在 MySQL 8 数据类型章节中讨论的那样，MySQL 支持许多不同的存储引擎和行格式。我们可以决定每个表要使用的存储和索引方法。选择适当的表格式是一个很大的性能提升。

表列

我们应该为表列使用尽可能小的数据类型。这是最有效的方法。MySQL 支持专门的数据类型来节省内存和磁盘空间。例如，我们应该尽可能使用整数类型来获得较小的表。比较MEDIUMINT和INT，MEDIUMINT是一个更好的选择，因为它使用的空间比INT少 25%。

我们必须尽可能声明列为NOT NULL。这样可以更好地使用索引，并消除测试每个值是否为NULL的开销。这会导致更快的 SQL 操作。我们也可以节省每列一个位的存储空间。如果我们真的需要，应该使用NULL。NULL值不应该作为每列的默认设置而被允许。

通过以下技术可以获得表的巨大性能提升并最小化存储空间需求：

行格式

默认情况下，在创建InnoDB表时使用DYNAMIC行格式。我们可以配置innodb_default_row_format以使用除DYNAMIC之外的行格式。我们还可以在CREATE TABLE或ALTER TABLE语句中明确指定ROW_FORMAT选项。

行格式包括COMPACT、DYNAMIC和COMPRESSED。它们减少了行存储空间，但在某些操作上增加了 CPU 使用。对于平均工作负载，受到缓存命中率和磁盘速度的限制，它会更快。如果受到 CPU 速度的限制，它会更慢。

行格式还优化了使用可变长度字符集时CHAR数据类型列的存储。使用REDUNDANT行格式，CHAR(N)列值占用字符集中最大字节长度的N倍。InnoDB存储引擎在N到N倍字符集中最大字节长度范围内分配可变数量的存储空间。

如果在MyISAM表的情况下没有可变长度列，例如VARCHAR、TEXT或BLOB，则使用固定大小的行格式。

索引

表的主索引必须尽可能短。这样可以轻松识别每一行。这也很有效。在InnoDB表的情况下，主键列在每个次要索引条目中都会被复制。如果我们有一个较短的主键，那么在有许多次要索引的情况下可以节省空间。

我们应该只创建那些提高查询性能的索引。索引改善信息检索，但会减慢插入和更新操作。必须要注意索引的性能影响来创建索引。如果需要通过组合列进行搜索来访问表，最好在组合列上创建复合索引，而不是在每个列上单独创建索引。最常用的列应该是索引的第一部分。如果在表的选定操作中经常使用许多列，建议将具有最多重复项的列作为索引中的第一列。这样可以更好地压缩索引。

如果一个长字符串列应该有一个唯一的前缀作为前几个字符，建议只索引前缀，使用 MySQL 对列的最左边部分进行索引的支持。更短的索引更受青睐，不仅因为它们需要更少的空间，而且因为它们在索引缓存中提供更多的命中，并且需要更少的磁盘查找。

连接

如果一个表经常被扫描，如果可行的话，将表拆分成两个表是有益的。这尤其适用于动态格式表。还可以使用较小的静态格式表，用于在扫描表时搜索相关行。

具有相同信息的列应该在不同的表中声明，具有相同的数据类型。这加快了基于匹配列的连接。

列名必须保持简单，以便在表之间使用相同的名称。这简化了连接查询。例如，在客户表中，我们应该使用name作为列名，而不是使用customer_name。为了使名称可移植到其他 SQL 服务器，我们应该保持列名短于 18 个字符。

规范化

表列中的数据必须保持非冗余，考虑规范化理论中的第三范式。如果列包含重复的长数值，例如名称或地址，最好分配唯一的 ID，并在多个较小的表中重复这些 ID。在搜索时，应该通过在连接子句中引用 ID 来使用连接查询。

在应用程序中，如果偏好速度而不是磁盘空间或使用多个数据副本的维护成本，建议复制信息或创建摘要表以获得更快的速度。一个例子是商业智能系统，从大型表中分析数据。在这种情况下，规范化规则并不严格遵循。

优化 MySQL 数据类型

以下是优化数字数据类型的指南：

• 数字列必须优先于字符串列，用于存储唯一 ID 或其他可以表示为字符串或数字的值。它更快，占用更少的内存来传输和比较，因为与字符串相比，大的数字值存储在较少的字节中。

• 从数据库中访问信息比从文本文件中访问信息更快。当使用数字数据时，这一点尤为真实。数据库中的信息以比文本文件更紧凑的格式存储。因此，它需要更少的磁盘访问。

以下是优化字符和字符串数据类型的指南：

• 二进制排序顺序（逻辑顺序）应该用于更快的比较和排序操作。二进制运算符也可以在查询中使用二进制排序顺序。

• 对于InnoDB表，当我们使用随机生成的值作为主键时，如果可行的话，应该以升序值作为前缀，例如日期和时间。在这种情况下，主键值在物理上更接近。InnoDB可以更快地插入或检索这些值。

• 对于预计保存少于 8KB 数据的列值，应使用二进制 VARCHAR 数据类型而不是 BLOB。如果原始表没有任何 BLOB 列，GROUP BY 和 ORDER BY 子句会生成临时表。这些临时表可以使用 MEMORY 存储引擎。

• 为了在运行查询时避免字符串转换，应尽可能在比较来自不同列的值时使用相同的字符集和排序声明列。

• 如果表包含不经常在检索操作中使用的字符串列，应考虑将字符串列拆分为单独的表。在检索操作中，应根据需要使用外键进行连接查询。当检索任何行的值时，MySQL 读取包含该行的所有列的数据块。当我们保持行较小，仅包含经常使用的列时，可以使更多的行适应每个数据块。这些紧凑的表减少了内存使用和磁盘 I/O。

以下是优化 BLOB 数据类型的指南：

• 检索和显示信息时，BLOB 列的性能要求可能会有所不同。因此，应考虑将 BLOB 特定表存储在不同的存储设备或单独的数据库实例中。例如，需要在大型顺序磁盘读取中检索 BLOB。因此，传统硬盘驱动器或 SSD 设备可能更适合需求。

• 为了减少不使用 BLOB 列的查询的内存需求，对于具有多个列的表，应考虑将 BLOB 拆分为单独的表，并根据需要使用连接查询进行引用。

• 如果表列是一个包含文本数据的大型 blob，应首先考虑压缩。如果整个表由存储引擎（如 InnoDB 或 MyISAM）压缩，就不应该使用这种技术。

优化多个表

我们学习了在某些情况下将表拆分为多个表以加快查询执行的技术。这种技术并不适用于所有情况，因为如果表的数量达到数千个，管理所有这些表的开销将成为另一个性能噩梦。

在本节中，我们将看到 MySQL 如何打开和关闭表。以下显示了如何在 MySQL 服务器上发现打开的文件：

> mysqladmin status
Uptime: 262200 Threads: 2 Questions: 16 Slow queries: 0 Opens: 111 Flush tables: 2 Open tables: 87 Queries per second avg: 0.000

MySQL 8 服务器是多线程的。可能会有许多客户端同时为一个表发出查询。MySQL 为每个并发会话独立打开表，以最小化同一表上具有不同状态的多个客户端会话的问题。这提高了性能，尽管需要额外的内存。每个打开 MyISAM 表的客户端需要一个额外的文件描述符。

table_open_cache 系统变量确定所有线程的打开表的数量。通过增加这个值可以增加 mysqld 需要的文件描述符的数量。max_connections 系统变量确定允许的最大同时客户端连接数。在某种程度上，这两个系统变量影响 MySQL 服务器可以保持打开的文件的最大数量。如果我们增加这两个值，可能会受到操作系统对每个进程打开文件数量的限制。

以下是 MySQL 关闭未使用表的情况：

• 当表缓存已满且有线程尝试打开不在表缓存中的表时。

• 当表缓存包含的条目多于 table_open_cache 系统变量中指定的条目，并且缓存中的表不再被任何线程使用时。

• 当有人发出 FLUSH TABLES 语句或执行 mysqladmin flush-tables 或 mysqladmin refresh 命令时，表刷新操作会发生。MySQL 在此事件上关闭表。

MySQL 8 服务器在表缓存已满时使用以下过程来定位缓存条目：

• 从最近最少使用的表开始释放未使用的表。

• 如果需要打开一个新表，表缓存已满且无法释放表，则根据需要临时扩展缓存。如果在表缓存处于临时扩展状态时，表从已使用状态转换为未使用状态，则关闭该表并从表缓存中释放。

以下是查找打开表数量的示例：

mysql> SHOW GLOBAL STATUS LIKE '%Opened_Tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 112   |
+---------------+-------+

在 MySQL 中使用内部临时表

在处理 SQL 语句时，MySQL 8 服务器在某些情况下创建临时内部表。以下是服务器创建临时表的条件：

• UNION 语句

• 使用TEMPTABLE算法、UNION或聚合的视图

• 派生表

• 公共表达式

• 为子查询或半连接材料化创建的表

• 包含ORDER BY和GROUP BY子句的语句

• 带有DISTINCT和ORDER BY的语句

• 使用SQL_SMALL_RESULT修饰符的查询

• 从相同表中选择并插入的INSERT ... SELECT语句

• 多表UPDATE语句

• GROUP_CONCAT()或COUNT(DISTINCT)表达式

EXPLAIN语句可用于确定语句是否需要临时表。EXPLAIN语句有限制。它不会指示语句是否需要为派生或材料化临时表创建临时表。

Created_tmp_tables状态变量跟踪在内部内存中创建的临时表的数量。当 MySQL 服务器创建临时表时，它会增加Created_tmp_tables状态变量中的值。Created_tmp_disk_tables是另一个状态变量，用于跟踪在磁盘上创建的表的数量。

根据查询条件，服务器阻止在内存中使用临时表。在这种情况下，服务器在磁盘上创建表。以下是一些实例：

• 如果表具有BLOB或TEXT列

• 如果SELECT列表中的字符串列的最大长度大于 512 字节，并且使用了UNION或UNION ALL

• 如果SHOW COLUMNS和DESCRIBE语句使用BLOB作为列的类型

在以下条件下，UNION会在不创建临时表的情况下进行评估：

• 联合是UNION ALL而不是UNION或UNION DISTINCT

• 没有全局ORDER BY子句

• 在SELECT查询中，联合不在顶层查询块

优化查询

与表类似，数据库查询是任何数据库的最关键元素。应用程序使用查询与数据库交互。查询也称为可执行的 SQL 语句。本节重点介绍了改进查询执行性能的技术。

优化 SQL 语句

SQL 语句用于执行任何数据库应用程序的核心逻辑。无论语句是直接通过解释器发出还是通过 API 在后台提交，都无关紧要。本节概述了改进数据库中读写数据的 SQL 操作性能的准则。

SELECT语句在数据库中执行所有查找操作。考虑到SELECT语句的频率，调整这些语句变得至关重要。调整技术必须应用于像CREATE TABLE...AS SELECT、INSERT INTO...SELECT和DELETE语句中的WHERE子句等构造。

以下是优化查询的主要考虑因素：

• 为了优化SELECT ... WHERE查询，首先要检查的是是否可以添加索引。我们应该在SELECT查询的WHERE子句中使用的列上添加索引。这将加快评估、过滤和检索结果。策略应该是构建一小组可以加速应用程序中许多相关查询的索引。这也避免了浪费的磁盘空间。

• 索引对于引用使用连接和外键的不同表的查询非常重要。EXPLAIN语句可用于确定在SELECT语句执行中使用了哪些索引。

• 下一步应该是隔离和调整查询的部分；例如，需要大量时间的函数调用。根据查询的结构，函数调用可以针对表中的每一行或结果集中的每一行进行。

• 查询中全表扫描的次数必须最小化，特别是对于大表。

• 应定期使用ANALYZE TABLE语句来保持表统计信息的最新。优化器提供了构建高效查询执行计划所需的信息。

• 如果基本指导方针不能解决性能问题，应该通过阅读EXPLAIN计划并调整索引、WHERE子句、连接子句等内部细节来调查查询。

• 应避免将查询转换为难以理解的方式，特别是当优化器自动执行一些相同的转换时。

• InnoDB缓冲池、MyISAM键缓存和 MySQL 查询缓存必须有效地用于重复查询，以便在第一次后从内存中检索结果后更快地运行。内存区域的大小和属性必须进行调整，因为 MySQL 用于缓存。

• 如果查询使用缓存内存区域运行得更快，我们仍然应该进一步优化它，以便需要更少的缓存内存。这使应用程序更具可扩展性，使应用程序能够处理更多的同时用户、更大的请求等，而不会出现性能下降。

• 如果查询的速度受其他会话同时访问表的影响，我们应该处理锁定问题。

以下是优化WHERE子句的指导方针。这些优化适用于SELECT、DELETE或UPDATE查询中的WHERE子句：

• 不必要的括号应该被移除。以下是一个括号移除的例子：

 ((a AND b) AND c OR (((a AND b) AND (c AND d)))) 
        -> (a AND b AND c) OR (a AND b AND c AND d)

• 常量折叠是在编译时而不是运行时评估值的过程。如果我们已经将一个常量值赋给一个变量，然后在表达式中使用该变量，我们应该使用常量值。以下是一个常量折叠的例子：

 (a<b AND b=c) AND a=5 
        -> b>5 AND b=c AND a=5

• 由于常量折叠，我们应该移除常量条件。以下是一个常量条件移除的例子：

 (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) 
        -> B=5 OR B=6

优化索引

索引的基本用途是快速查找具有特定列值的行。如果索引不存在，MySQL 将从第一行开始并读取整个表以查找所有匹配的行。这需要更多时间，取决于表有多大。如果索引存在于适当的列中，MySQL 能够快速确定在数据文件中寻找的位置，而不必查看整个表数据。

以下是 MySQL 使用索引的操作列表：

• 基于WHERE子句快速查找匹配行。

• 在选择多个索引以消除考虑的行时，MySQL 使用行数最少的索引（最具选择性的索引）。

• 如果表具有复合索引，优化器使用索引的最左前缀来查找行。例如，在一个有三列索引的表中（在 col1、col2、col3 上），优化器可以查找具有索引搜索能力的行（col1）、（col1，col2）和（col1，col2，col3）。

• MySQL 在使用连接从其他表中提取行时使用索引。如果索引声明为相同类型和大小，MySQL 可以在列上有效地使用它们。当声明为相同大小时，VARCHAR和CHAR被视为相同。

• MySQL 还使用索引查找索引列key_col的最小值（MIN（））或最大值（MAX（））。预处理器检查是否在所有关键部分上使用WHERE key_part_N = constant来优化它。

• 还可以优化查询以检索值而不需要查询数据行。（覆盖索引是为查询提供所有结果的索引。）如果查询仅使用某个索引中包含的表中的那些列，所选值将从索引树中获取。这将更快地检索值。

查询执行计划

MySQL 优化器考虑优化技术，以有效地执行查询中涉及的查找，具体取决于表、列和索引的细节，以及WHERE子句中的条件。查询也可以在不读取大表上的所有行的情况下执行。SQL 连接也可以在不比较每个行的组合的情况下执行。查询执行计划是 MySQL 优化器选择执行最有效查询的一组操作。它也被称为EXPLAIN计划。作为管理员，目标是识别查询执行计划的方面，以确定查询是否经过优化。

EXPLAIN语句用于确定查询执行计划。以下是EXPLAIN语句提供的信息集：

• EXPLAIN语句与SELECT、DELETE、INSERT、UPDATE和REPLACE语句一起工作。

• 当EXPLAIN与 SQL 语句一起使用时，MySQL 显示有关查询执行计划的 MySQL 优化器的信息。这意味着 MySQL 解释了语句执行的过程。它包括有关表如何连接以及连接顺序的信息。

• 如果EXPLAIN显示了命名连接中语句执行的执行计划，而不是可解释的 SQL 语句，则使用FOR CONNECTION连接 ID。

• EXPLAIN为SELECT语句显示了额外的执行计划信息。

• EXPLAIN还可用于检查涉及分区表的查询。

• EXPLAIN支持FORMAT选项，可用于选择输出格式。TRADITIONAL格式以表格格式显示输出。这是默认的格式选项。JavaScript 对象表示（JSON）格式选项以 JSON 格式生成信息。

根据EXPLAIN语句的输出，可以确定在表中添加索引的位置，以便语句执行更快。还可以确定优化器是否按优化顺序连接表。使用SELECT STRAIGHT_JOIN开始语句，而不仅仅是SELECT，以向优化器提供使用与SELECT语句中命名表的顺序相对应的连接顺序的提示。由于STRAIGHT_JOIN禁用半连接转换，它可能会阻止索引的使用。

优化器跟踪是另一个工具，用于查找有关查询执行的信息。优化器跟踪可能提供与EXPLAIN不同的信息。优化器跟踪的格式和内容会根据版本而有所不同。

以下表格显示了EXPLAIN语句的输出格式：

列	JSON 名称	细节
id	select_id	SELECT标识符
select_type	None	SELECT类型
table	table_name	输出行的表
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可能选择的索引
key	key	实际选择的索引
key_len	key_length	所选键的长度
ref	ref	与索引进行比较的列
rows	rows	预计要检查的行数
filtered	filtered	表条件过滤的行的百分比
Extra	None	附加信息

参考：dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-output-column-table

优化表

数据库表是任何数据库的最基本构建块。在本章节的这一部分，我们将专注于优化表。本节提供了通过表优化技术来改善性能的详细指南。

InnoDB 表的优化

在生产环境中，InnoDB存储引擎是首选，特别是在可靠性和并发性很重要的情况下。它是 MySQL 表的默认存储引擎。本节重点介绍了优化InnoDB表的数据库操作。

以下是优化InnoDB表的指南：

• 应考虑使用OPTIMIZE TABLE语句来重新组织表并压缩浪费空间，一旦数据达到稳定大小或表增加了数十兆字节。对于重新组织的表来说，执行完整的表扫描需要更少的磁盘 I/O。

• OPTIMIZE TABLE语句会复制表中的数据并重建索引。这是有益的，因为它可以改善索引内数据的打包，并减少磁盘上表空间的碎片。收益可能会有所不同，取决于每个表中的数据。在某些情况下，收益可能是显著的，而在其他情况下则不是。收益也可能随着时间的推移而减少，直到进行下一次表优化。如果表很大或正在重建的索引不适合缓冲池，操作可能会很慢。

• 在InnoDB表中，长主键会浪费大量磁盘空间，应该避免使用。

• 在InnoDB表中，应优先选择VARCHAR数据类型，而不是CHAR数据类型来存储可变长度字符串，或者用于预期包含NULL值的列。CHAR(N)列始终占用N个字符来存储数据，即使值为NULL。较小的表更适合适应缓冲池并减少磁盘 I/O。

• 考虑为大表或包含大量重复文本或数字数据的表使用COMPRESSED行格式。

MyISAM 表的优化

对于只读或读取频率较高的数据，或低并发操作，MyISAM存储引擎最适合。这是因为表锁限制了同时进行更新的能力。在本节中，重点将放在优化要在MyISAM表上执行的查询上。

以下是加快MyISAM表查询的指南：

• 避免在频繁更新的MyISAM表上执行复杂的SELECT查询。这样可以避免由于写入者和读取者之间的争用而导致的表锁定问题。

• MyISAM存储引擎支持并发插入。如果表数据文件中间没有空闲块，我们可以在其他线程从表中读取数据的同时向其中INSERT新行。如果重要的是能够进行并发读写操作，可以考虑使用该表来避免删除行。另一个选择是在删除行后执行OPTIMIZE TABLE来对表进行碎片整理。这种行为可以通过设置concurrent_insert系统变量来进行控制或修改。

• 对于频繁更改的MyISAM表，应避免所有可变长度列。如果表中包含至少一个可变长度列，则使用动态行格式。

• myisamchk --sort-index --sort-records=1命令可用于对索引进行排序。它还根据索引对数据进行排序。如果我们有唯一索引，并且希望按照索引的顺序读取所有行，这样可以使查询运行更快。第一次对大表进行这种排序会花费很长时间。

• 如果我们通常按照expression1，expression2等顺序检索行，可以使用ALTER TABLE ... ORDER BY expression1, expression2,..，这样会提高性能，如果在对表进行大量更改后使用此选项。

内存表的优化

MySQL 的MEMORY表应该只用于经常访问的非关键数据，而且是只读且很少更新的数据。应该对应用程序进行基准测试，以确认额外的性能是否值得冒失去数据的风险。

我们应该检查针对每个表的查询类型，以获得MEMORY表的最佳性能。我们还应该指定每个相关索引的使用类型。它可以是 B 树索引或哈希索引。在CREATE INDEX语句上使用USING BTREE或USING HASH子句。

利用缓冲和缓存

这一部分关注使用缓冲和缓存技术来提高性能。

InnoDB 缓冲池优化

InnoDB存储引擎维护一个称为缓冲池的存储区域，用于在内存中缓存数据和索引。了解InnoDB缓冲池的工作原理非常重要，以便利用它将经常访问的数据保留在内存中。这是 MySQL 调优的一个重要方面。

以下是改进InnoDB缓冲池性能的一般指导方针：

• 在理想情况下，缓冲池的大小应该足够大，同时留出足够的内存供服务器上的其他进程运行，而不会过度分页。有了更大的缓冲池，InnoDB的功能也更多，比如内存数据库。在这种情况下，它只需从磁盘读取数据一次，然后在后续读取中从内存中访问数据。

• 我们可以考虑将缓冲池分成多个部分，用于具有大内存的 64 位系统。这样可以在并发操作期间最大程度地减少内存争用。

• 经常访问的数据应该保留在内存中。

• 可以控制InnoDB何时以及如何执行预读请求，以异步方式将页面预取到缓冲池中。InnoDB使用两种预读算法来提高 I/O 性能。线性预读可以根据顺序访问缓冲池中的页面来预测可能很快需要的页面。随机预读可以根据缓冲池中的页面来预测可能需要的页面，而不考虑页面读取的顺序。innodb_read_ahead_threshold配置参数控制线性预读的灵敏度。我们可以通过将innodb_random_read_ahead设置为ON来启用随机预读。

• innodb_buffer_pool_read_ahead确定读入InnoDB缓冲池的页面数。innodb_buffer_pool_read_ahead_evicted确定后台预读线程读入缓冲池的页面数，随后被查询访问。innodb_buffer_pool_read_ahead_rnd确定InnoDB发起的随机预读次数。

MyISAM 键缓存

MyISAM存储引擎采用了一种被许多数据库管理系统支持的策略，以最小化磁盘 I/O。MyISAM使用缓存机制将最常访问的表块保留在内存中。

• 为索引块维护了一个称为键缓存的特殊结构。最常用的索引块被放置在包含多个块缓冲区的结构中。

• MySQL 依赖于本机操作系统文件系统缓存来存储数据块。

key_buffer_size系统变量确定关键缓存的大小。如果设置为零，则不使用关键缓存。如果key_buffer_size值太小而无法分配最小顺序的块缓冲区，则也不使用关键缓存。关键缓存结构中的所有块缓冲区大小相同。这个大小可以等于、大于或小于表索引块的大小。通常情况下，这两个值中的一个是另一个的倍数。

当需要从任何表索引块访问数据时，服务器首先检查它是否在关键缓存的某个块缓冲区中可用。如果数据可用，服务器将从关键缓存中访问数据，而不是从磁盘上访问。如果数据不可用，服务器选择包含不同表索引块的缓存块缓冲区，并通过复制所需的表索引块来替换其中的数据。一旦新的索引块在缓存中可用，就可以访问索引数据。

MySQL 服务器遵循最近最少使用（LRU）策略。根据这个策略，在选择要替换的块时，它选择最近最少使用的索引块。关键缓存模块包含 LRU 链中的所有使用的块（一个特殊列表）。列表按使用时间排序。当访问块时，它是最近使用的。块被放置在列表的末尾。当需要替换块时，列表开头的块是最近最少使用的。因此，顶部的块成为首选的驱逐候选。

如果选择用于替换的块已被修改，则该块被视为脏块。在替换之前，块内容被刷新到它们来自的表索引中。

根据以下条件，线程可以同时访问关键缓存缓冲区：

• 未被更新的缓冲区可以被多个会话访问。

• 正在更新的缓冲区会导致需要等待更新完成的会话使用它

• 只要会话是独立的并且不相互干扰，多个会话可以发起请求，导致缓存块替换。

通过这种方式，对关键缓存的共享访问显著提高了性能。

总结

在本章中，我们详细学习了优化 MySQL 8 组件的技术。该章节从优化的基础知识开始，包括硬件和软件优化指南。我们还讨论了 MySQL 8 服务器和客户端、数据库结构、查询和表的优化指南。我们还涵盖了属于不同存储引擎的表的优化，如MyISAM、InnoDB和MEMORY。我们学习了工具，如EXPLAIN和EXPLAIN ANALYZE，以了解查询执行计划。在本章的后部分，我们学习了缓冲和缓存技术以提高性能。

现在是时候转到下一章了。下一章将重点介绍扩展 MySQL 8 的技术。该章节将详细介绍 MySQL 8 插件，这些插件有助于扩展默认的 MySQL 8 功能。它还将解释调用这些插件的服务。该章节将讨论添加新功能、调试和移植方法。这对数据库管理员来说将是一个重要的章节。

第十三章：扩展 MySQL 8

在上一章中，我们学习了如何优化 MySQL 8。我们还了解了需要进行哪些配置才能实现优化，以及如何利用缓存和缓冲进行优化。我们逐步进行了用例研究，以实现以下组件的优化：

• 优化 MySQL 8 服务器和客户端

• 优化数据结构

• 优化查询

• 优化表

在本章中，我们将学习如何扩展 MySQL 8。我们将检查允许扩展的 MySQL 8 组件，并了解如何根据特定业务需求自定义 MySQL 8。在扩展 MySQL 8 之前，您将了解基本组件以及用于扩展 MySQL 8 的 MySQL 插件 API 的功能。以下是本章涵盖的主题列表：

• 扩展 MySQL 8 的概述

• 扩展插件并使用服务来调用它们

• 添加新功能

• 调试和移植

扩展 MySQL 8 的概述

在这一部分，您将学习如何根据自己的需求扩展 MySQL 8 中最令人兴奋的主题之一。在尝试扩展 MySQL 8 之前，您应该充分了解 MySQL 8 的几个组件。以下是扩展 MySQL 8 重要的组件列表：

• MySQL 8 内部

• MySQL 8 插件 API

• MySQL 8 组件和插件的服务

• 向 MySQL 8 添加新功能

• 调试和移植 MySQL 8

MySQL 8 内部

在开始处理 MySQL 代码之前，您应该了解一些事项。要贡献或跟踪 MySQL 开发，您应该按照系统或操作系统平台的源代码分发安装说明。源代码包括内部文档，这对于从开发人员的角度了解 MySQL 的内部工作非常重要。您还可以订阅 internals 邮件列表，网址为lists.mysql.com/internals，其中包括从事 MySQL 代码工作的人员，您还可以讨论与 MySQL 开发相关的主题或发布补丁：

• MySQL 8 线程：MySQL 服务器创建线程，例如连接管理器线程、信号线程、读写线程（如果使用 InnoDB 存储引擎）、调度器线程来处理连接以及复制和事件处理。

• MySQL 8 测试套件：MySQL 8 提供了包含在 Unix 源代码分发中的测试系统，以帮助用户和开发人员对 MySQL 代码进行回归测试。您还可以使用测试框架编写自己的测试用例。

MySQL 8 插件 API

MySQL 8 通过插件 API 提供对服务器组件本身的支持。插件可以在服务器启动期间加载，也可以在运行时加载和卸载；无需重新启动服务器。该 API 非常通用，不限制插件在限制方面可以做什么，而是允许它们做的比内置组件更多。该 API 支持用于组件的接口，例如存储引擎插件、全文解析器插件、服务器扩展等。

插件接口利用 MySQL 8 数据库中的plugin表来存储有关已安装插件的信息，通过使用INSTALL PLUGIN语句来永久安装插件。在 MySQL 8 安装过程中，将创建plugin表。对于单个服务器调用，也可以使用--plugin--load选项安装插件，但使用此选项不会将已安装的插件记录到plugin表中。

MySQL 8 还为客户端插件提供支持 API，用于特定目的，例如通过不同的身份验证方法启用客户端的服务器连接。

MySQL 8 组件和插件的服务

MySQL 8 服务器插件可以访问和启动服务器插件服务；同样，服务器组件也可以访问和请求组件服务。MySQL 8 插件服务接口通过公开服务器功能来补充 API 插件，插件可以调用这些功能。以下是插件服务的特点：

• 这些服务使插件能够使用普通函数调用访问服务器代码，并且还可以调用用户定义的函数。

• 这些服务是可移植的，可以在多个平台上运行

• 服务提供版本支持，防止插件和服务之间的不兼容性

• 这些服务还支持测试插件服务

MySQL 提供了两种插件和组件的服务类型，如下所示：

1. 锁定服务：提供两个级别的锁定服务接口，即 C 级别和 SQL 级别。接口在锁定命名空间、锁定名称和锁定模式属性上工作。

2. 密钥环服务：密钥环服务提供了一个接口，用于安全存储内部服务器组件和插件以便以后检索的敏感信息。

向 MySQL 8 添加新函数

您可以向 MySQL 8 添加自己的函数，可以使用三种支持的函数类型之一来完成。新函数可以像调用内置函数ABS()一样调用，而不管您新增了哪种函数类型都是如此。以下是 MySQL 8 中支持的三种新函数类型的列表：

1. 通过用户定义函数（UDF）接口添加函数。

2. 将函数添加为本机（内置）MySQL 函数。

3. 通过创建存储函数添加函数。

调试和移植 MySQL 8

将 MySQL 8 移植到其他操作系统目前受到许多操作系统的支持；支持的操作系统列表提供在www.mysql.com/support/supportedplatforms/database.html。如果您添加了新的端口并且在新的端口上遇到问题，您可以使用 MySQL 8 的调试。

根据您遇到问题的位置，可以以不同的方式开始调试——可能是在 MySQL 服务器中或在 MySQL 客户端中。根据问题的位置，您可以分别在 MySQL 服务器或客户端开始调试，并从DBUG包中获取帮助来跟踪程序的活动。

扩展插件并使用服务调用它们

在本节中，您将了解插件 API、其接口和 MySQL 服务如何相互交互，并在 MySQL 8 中提供扩展。插件在 MySQL 8 架构中也被视为组件，因此您可以使用它们提供可插拔的功能。插件 API 和插件服务接口有以下区别：

• 插件 API 使服务器能够使用插件。服务器启动和调用插件，因此插件可以扩展服务器的功能或注册自己以接收服务器处理通知。

• 插件服务接口允许插件调用服务器代码。插件启动和调用服务函数，以便许多插件可以利用相同的服务器功能，而无需为功能单独实现。

编写插件

要创建一个插件库，必须提供所需的描述符信息，因为它指定了库文件包含哪些插件。还必须为指定的每个插件编写接口函数。

每个服务器插件必须具有通用描述符，提供信息给插件 API，并具有特定类型的描述符，提供指定插件类型的接口信息。用于指定通用描述符的结构对所有插件类型都是相同的，而特定类型的描述符可以根据插件行为或功能的要求而变化。服务器插件接口允许插件公开系统变量和状态。

客户端插件的架构与服务器端插件略有不同。例如，每个插件必须具有描述符信息，但通用和特定类型的描述符之间没有单独的区分。

插件可以用 C 或 C++或任何其他可以使用 C 调用约定的语言编写。插件是动态加载和卸载的，因此操作系统必须动态支持您动态编译调用应用程序的地方。特别是对于服务器插件，这意味着mysqld必须动态链接。

由于我们无法确定哪个应用程序将使用插件，因此客户端插件编写者应避免对调用应用程序的符号的依赖。

以下是支持的插件创建类型，可以实现多种功能：

• 身份验证

• 密码验证和强度检查

• 协议跟踪

• 查询重写

• 安全钥匙存储和检索

• 存储引擎

• 全文解析器

• 守护进程

• INFORMATION_SCHEMA表

• 半同步复制

• 审计

组件和插件服务

您可以通过查看 MySQL 8 源代码分发的include/mysql/components和相应的services目录来识别 MySQL 提供的组件服务和函数。

同样，您可以通过查看 MySQL 8 源代码分发的include/mysql目录和相关文件来识别 MySQL 提供的插件服务和函数，如下所示：

• plugin.h文件包括services.h文件，services.h文件包含其中所有可用的特定服务头文件

• 特定服务头文件的名称将以service_xxx.h的形式命名

以下是 MySQL 8 中可用的组件服务列表：

• component_sys_variable_register，component_sys_variable_unregister：用于注册和注销系统变量

• log_builtins，log_builtins_string：用于日志组件服务

• mysql_service_udf_registration，mysql_service_udf_registration_aggregate：用于在组件和插件中启用标量和聚合用户定义函数的注册和注销

• mysql_string：用于字符串服务 API

• pfs_plugin_table：用于动态性能模式表操作

以下是 MySQL 8 中可用的插件服务列表：

• get_sysvar_source：用于检索系统变量设置

• locking_service：用于使用 C 语言和 SQL 级接口实现锁定，具有命名空间、名称和模式属性

• my_plugin_log_service：用于将错误消息写入日志

• my_snprintf：用于字符串格式化，以保持输出在各个平台上的一致性

• status_variable_registration：用于注册状态变量

• my_thd_scheduler：用于线程调度器选择

• mysql_keyring：用于钥匙存储服务

• mysql_password_policy：用于密码强度和验证检查

• plugin_registry_service：用于访问组件注册表和相关服务

• security_context：用于管理线程安全上下文

• thd_alloc：用于内存分配

• thd_wait：用于报告休眠或停滞

现在，您对插件服务和组件服务有了清楚的了解。MySQL 8 提供以下类型的服务来支持插件和组件服务：

1. 锁定服务

2. 钥匙存储服务

以下部分详细介绍了两种类型的服务。

锁定服务

锁定服务接口提供两个级别：C 级别和 SQL 级别。该接口在锁命名空间、锁名称和锁模式属性上工作。C 语言接口可作为插件服务从用户定义的函数或服务器插件调用，SQL 级别接口用作一组用户定义的函数，映射到调用服务例程。

以下是锁定接口的特征：

• 锁命名空间、锁名称和锁模式是锁的三个属性。

• 通过形成锁命名空间和锁名称的组合来识别锁。

• 锁模式可以是读或写。读锁是共享的，而写锁是排他的。

• 锁名称和命名空间最多可以有 64 个字符，并且必须是非 NULL 和非空字符串。

• 锁名称和命名空间被视为二进制字符串，因此比较将区分大小写。

• 提供了获取和释放锁的函数，不需要特殊权限来调用这些函数。

• 在不同会话中的锁获取调用期间检测死锁；选择一个调用者并终止其锁获取请求，优先选择持有读锁的调用者会话，而不是持有写锁的会话。

• 典型的会话可以通过单个锁获取调用请求多个锁获取。它为请求提供原子行为，并且如果所有锁都被获取则成功，如果任何锁获取失败则失败。

• 同一锁标识符的多个锁可以由会话获取，其中锁实例可以是写锁、读锁或读写锁的混合。

• 通过显式调用释放锁函数或者如果会话终止则隐式释放会话中获取的锁。

• 在给定命名空间中释放的所有锁都在会话内一起释放。

密钥环服务

密钥环服务提供了一个接口，用于安全地存储内部服务器组件和插件以便以后检索的敏感信息。在密钥环服务中，来自密钥库本身的记录由数据组成——密钥和可以访问密钥的唯一标识符。标识符由以下两部分组成：

1. key_id：名称。以mysql_开头的key_id或密钥 ID 值由 MySQL 服务器保留。

2. user_id：user_id代表每个会话的有效user_id。如果没有用户上下文，则可以为NULL，并且该值不一定需要是实际的user，而是取决于应用程序。

以下是密钥环服务函数的常见特征：

• 每个函数返回 1 表示失败，返回 0 表示成功

• user_id和key_id参数的唯一组合指示在密钥环中使用哪个密钥

• 通过key_type参数值提供有关密钥的附加信息，例如其预期用途、加密方法或其他信息

• 在密钥环服务函数中，用户名称、密钥 ID、类型和值被视为二进制字符串，因此比较是区分大小写的

以下是可用的密钥环服务函数列表：

• my_key_generate(): 正如其名称所示，它生成给定类型和长度的新随机密钥，并存储在密钥环中。该函数由参数key_id、user_id、key_type和key_len组成，以及以下函数语法：

 bool my_key_generate(const char *key_id, const char*key_type, 
          const char *user_id, size_t key_len)

• my_key_fetch(): 对参数值进行解密并从密钥环中检索密钥及其类型。该函数由参数key_id、user_id、key_type、key和key_len组成，以及以下函数语法：

 bool my_key_fetch(const char *key_id, const char **key_type, 
          const char* user_id, void **key, size_t *key_len)

• my_key_remove(): 从密钥环中删除关联的密钥。该函数由参数key_id和user_id组成，以及以下函数语法：

 bool my_key_remove(const char *key_id, const char* user_id)

• my_key_store(): 对参数值进行混淆并将密钥存储在密钥环中。该函数包括参数key_id、user_id、key_type、key和key_len，以及以下函数语法：

 bool my_key_store(const char *key_id, const char *key_type, 
          const char* user_id, void *key, size_t key_len)

添加新函数

可以在 MySQL 8 中添加任何三种支持的类型的新函数。每种类型都有其自己的优点和缺点。应该根据函数的要求决定在何处以及应该添加或实现哪种类型的函数。

以下是 MySQL 8 中支持的三种新函数类型的列表，我们将在下一节中进行讨论：

1. 通过用户定义函数接口添加函数。

2. 将函数添加为本机（内置）MySQL 函数。

3. 通过创建存储函数添加函数。

用户定义函数接口的特点

用户定义的函数接口为用户目的函数提供独立的功能。

MySQL 用户定义函数接口提供以下功能和能力：

• 函数可以接受整数、字符串或实数值的参数，并且可以返回相同类型的值

• 可以定义简单函数以一次操作一行，也可以是聚合函数以操作行组

• 函数被提供信息以使它们能够检查传递的参数的类型、名称和数量

• 在将参数传递给给定函数之前，还可以要求 MySQL 强制参数

• 如果函数导致任何错误或返回NULL，可以进行指示

添加新的用户定义函数

UDF 函数必须用 C 或 C++编写，并且底层操作系统必须支持动态加载行为。有一个文件sql/udf_example.cc，定义了五个 UDF 函数，并包含在 MySQL 源分发中。分析该文件将让您了解 UDF 的调用约定如何工作。用户定义的函数相关符号和数据结构在include/mysql_com.h文件中定义，并且该文件包含在mysql.h头文件中。

UDF 中包含的典型代码在运行服务器中执行，因此在编写 UDF 代码时适用所有约束-服务器代码。当前适用的约束可能在服务器升级时得到修订，并且这可能导致需要重写 UDF 代码，因此在编写 UDF 代码时要小心。

为了使用 UDF，必须动态链接mysqld。对于在 SQL 语句中使用的任何函数，必须有底层的 C 或 C++函数。遵循将 SQL 和 C/C++代码分开的约定，其中大写的xxx()表示 SQL 函数调用，而小写的xxx()表示 C/C++函数调用。

当您使用 C++时，将您的 C 函数封装如下所示：extern "C" { ... }，以确保您的 C++函数名称在完成的用户定义函数中可读。

要编写和实现接口函数名XXX()，必须有主函数xxx()，并且还需要从以下功能中实现一个或多个功能：

• xxx(): 生成函数结果的主函数

• xxx_init(): 主函数xxx()的初始化函数，可用于以下任何目的：

• 检查要传递给XXX()的参数数量

• 在调用主函数时，使用声明验证参数类型

• 在需要时为主函数分配内存

• 结果的最大长度验证

• 为结果设置最大的十进制数限制

• 指定结果是否可以为NULL

• xxx_deinit(): 代表主函数的去初始化，并在需要时释放主函数的初始化函数分配的内存

在 MySQL 8 中，聚合 UDF 按以下顺序处理：

1. 调用xxx_init()以便它分配所需的内存来存储结果信息。

2. 按照GROUP BY函数指定的表/结果进行排序。

3. 调用xxx_clear()来重置每个新组中第一行的当前聚合值。

4. 调用xxx_add()来将参数添加到当前的聚合值。

5. 调用xxx()来获取按组更改或在处理最后一行后的聚合数据结果。

6. 重复步骤 3-5，直到处理完所有指定/结果行。

7. 调用xxx_deinit()来释放为 UDF 分配的任何内存。

所有函数必须是线程安全的，包括主要函数以及其他所需的附加函数，以及初始化和去初始化函数。

与上述顺序类似，以下是在添加新的用户定义函数时需要注意的重要方面：

• UDF 参数处理

• UDF 返回值和错误处理

• UDF 编译和安装

• UDF 安全预防措施

添加新的本机函数

为了使用包含新本机函数的修改源代码进行编译，需要源分发文件。当迁移到另一个 MySQL 版本时，也需要重复这一过程。

如果在语句中引用新的本机函数，并且还要在从服务器上复制，确保每个从服务器都有新的本机函数可用，否则当尝试调用新的本机函数时，从服务器上的复制将失败。

以下是在sql目录的源分发文件中添加新本机函数的步骤：

1. 函数的子类需要在item_create.cc中添加：

• 在参数数量固定的情况下，子类应该从Create_func_arg0、Create_func_arg1、Create_func_arg2或Create_func_arg3中创建，具体取决于您的本机函数需要的参数数量。您可以参考Create_func_abs、Create_func_uuid和Create_func_pow类。

• 在参数数量可变的情况下，子类应该从Create_native_func中创建。您可以参考Creat_func_concat类。

1. 在 SQL 语句中引用的函数名称需要在item_create.cc中注册，通过向数组添加以下行：static Native_func_registry func_array[]：

• 如果需要，可以为同一个函数注册多个名称。您可以参考LOWER和LCASE的行，它们是Create_func_lcase的别名。

1. 根据您的函数返回类型是字符串还是数字，在item_func.h文件中，需要声明从Item_str_func或Item_num_func继承的类。

2. 根据您的函数在item_func.cc文件中定义为字符串还是数字函数，需要添加以下声明之一：

 double Item_func_newname::val()
 longlong Item_func_newname::val_int() 
        String *Item_func_newname::Str(String *str)

• • 如果您的对象是从任何标准项继承的，那么您可能只需要定义前面的函数之一，因为父对象将处理其他函数。您可以参考定义了val()函数的Item_str_func类，该函数在::str()函数的返回值上执行atof()函数。

1. 如果函数是非确定性的 - 也就是说，如果对于固定给定的参数，返回的结果在不同的调用中会有所不同 - 那么需要在项目构造函数中包含以下语句，表示函数结果不应被缓存：current_thd->lex->safe_to_cache_query=0;。

2. 您可能还需要为您的本机函数定义以下对象函数：

• void Item_func_newname::fix_length_and_dec()

• 函数至少应包括对给定参数的max_length计算。

• 如果您的主要函数不能返回任何NULL值，还应该设置maybenull = 0。

• 您可以参考Item_func_mod::fix_length_and_dec。

所有函数都必须具有线程安全性。在没有受到互斥保护的情况下，您不应该在函数中使用任何静态或全局变量。

调试和移植

将 MySQL 8 移植到其他操作系统目前受到许多操作系统的支持。最新支持的操作系统列表提供在www.mysql.com/support/supportedplatforms/database.html。如果您已添加或尝试添加新的端口（受支持的平台）并遇到问题，您可以使用 MySQL 8 的调试来查找并解决问题。

首先，在调试mysqld之前，您应该让测试程序mysys/thr_lock工作。这可以确保您的线程安装有远程工作的可能性！

根据您遇到问题的位置，可以在 MySQL 服务器或 MySQL 客户端中开始调试。根据问题的位置，您可以分别在 MySQL 服务器或 MySQL 客户端中开始调试，并且可以从DEBUG包中获得程序活动的跟踪帮助。

MySQL 源代码包括使用Doxygen编写的内部文档，这对于理解 MySQL 的开发者视角非常有帮助。

在本节中，您将看到以下主题的详细信息：

• 调试 MySQL 服务器

• 调试 MySQL 客户端

• DBUG包

调试 MySQL 服务器

如果您在 MySQL 中使用了一些非常新的功能并遇到了一些问题——比如服务器崩溃——您可以尝试使用--skip-new选项运行mysqld。此选项告诉 MySQL 服务器禁用所有新的可能不安全的功能。

在mysqld无法启动的情况下，请验证my.cnf文件，因为它们可能会干扰设置！您可以使用mysqld --print-defaults选项检查my.cnf中的参数，然后使用--no-defaults选项启动mysqld以避免使用它们。

如果mysqld开始占用内存或 CPU 或挂起，您可以检查mysqladmin processlist status，并查找是否有某个查询执行时间过长。如果您遇到性能问题或问题，并且新客户端无法连接，您可以使用mysqladmin -i10 process list status。

您还可以使用调试命令mysqladmin，它会将有关查询使用、内存使用和正在使用的锁的信息转储到 MySQL 日志文件中，并且可以为您解决一些问题。如果您没有为调试编译 MySQL，此命令也可以提供一些有用的信息。

如果您遇到表变慢的问题，您应该尝试使用myisamchk或OPTIMIZE_TABLE来优化表。如果有任何慢查询，您应该使用EXPLAIN来查找并修复查询中的问题。

在调试 MySQL 8 时，以下是需要考虑的重要领域：

• 为调试编译 MySQL：在出现非常特定的问题时，您可以尝试调试 MySQL。为此，您必须使用-DWITH_DEBUG=1选项配置 MySQL。调试配置会自动启用大量额外的安全检查功能，以监视mysqld的健康状况。

• 创建跟踪文件：您可以尝试通过创建跟踪文件来找到问题。为此，您必须使用带有调试支持的mysqld编译。然后，您可以使用--debug选项，在 Unix 上将在/tmp/mysqld.trace中添加跟踪日志，在 Windows 上将在\mysqld.trace中添加跟踪日志。

• 使用 PDB 和 WER 创建 Windows 崩溃转储：程序数据库文件包含在 ZIP 存档调试二进制文件和测试套件中，作为 MySQL 的单独分发。这些文件提供了有关 MySQL 安装问题的调试信息。它们可以与 WinDbg 或 Visual Studio 一起使用来调试mysqld。

• 在 gdb 下调试 mysqld：当您遇到线程问题或mysqld服务器在ready for connections之前挂起时，可以使用此选项。

• 使用堆栈跟踪：当mysqld意外死机时，您也可以使用此选项找出问题。

• 使用服务器日志查找mysqld错误原因：您可以通过启用常规查询日志来使用此选项 - 在此之前，您应该使用myisamchk实用程序检查所有表，并从日志中验证是否存在任何问题。

• 创建一个测试用例，如果您遇到表损坏：当您遇到表损坏问题时使用此选项，仅适用于MyISAM表。

调试 MySQL 客户端

在您在 MySQL 客户端遇到问题的情况下，您也可以在 MySQL 客户端内部进行调试，但是为了这样做，您必须具有集成的调试包。您需要配置 MySQL 以启用 MySQL 客户端中的调试，使用-DWITH_DEBUG=1。

在运行 MySQL 客户端之前，您应该设置环境变量MYSQL_DEBUG如下：

shell> MYSQL_DEBUG=d:t:O,/tmp/client.trace 
shell> export MYSQL_DEBUG

这将使 MySQL 客户端在 Unix 的/tmp/client.trace或 Windows 的\client.trace中生成一个跟踪文件。

在您自己的客户端代码出现问题的情况下，您可以尝试使用已知可用的客户端运行您的查询来连接服务器。为此，您应该以调试模式运行mysqld：

shell> mysql --debug=d:t:O,/tmp/client.trace

如果您想要为问题发送错误报告，此跟踪将提供有用的信息。

在某些情况下，如果您的客户端在一些合法的代码处崩溃，您可以检查您的mysql.h头文件是否与您的 MySQL 库文件匹配。这是一个非常常见的错误，使用旧的mysql.h文件从旧的 MySQL 安装中与新的 MySQL 库一起，导致此问题。

DBUG 包

Fred Fish 最初创建了 MySQL 服务器和大多数 MySQL 客户端的DBUG包。如果 MySQL 配置为调试模式，此包使得生成有关程序正在执行的信息的跟踪文件成为可能。

有调试选项可供指定，以便使用DBUG包将特定信息写入跟踪文件。它可以在程序调用中使用-#[debug_options]选项或--debug[=debug_options]选项。

大多数 MySQL 程序将使用默认值，如果指定了--debug或-#选项，而没有指定debug_options值。服务器默认值是 Windows 上的d:t:i:O,\mysqld.trace和 Unix 上的d:t:i:o,/tmp/mysqld.trace。此默认值的效果如下所列：

• d：启用所有调试宏的输出

• t: 跟踪函数调用和退出

• i：在跟踪文件中添加PID到输出行

• o,/tmp/mysqld.trace，O,\mysqld.trace：在 Unix 和 Windows 中分别设置调试输出文件

在大多数情况下，对于大多数客户端程序，无论平台如何，都应使用默认的debug_options值d:t:o,/tmp/myprogram_name.trace。对于 Windows，请使用\myprogram_name.trace。

以下是一些在 shell 命令行上指定的调试控制字符串的示例：

--debug=d:t 
--debug=d:f,main,subr1:F:L:t,20 
--debug=d,input,output,files:n 
--debug=d:t:i:O,\\mysqld.trace

摘要

在本章中，您学习了如何通过自定义函数和 API 扩展 MySQL 8。您还了解了编写函数以及插件服务和 API 的相关特性。您现在可以创建自己的函数或插件，满足特定的业务需求，并在函数不符合预期时进行调试和测试。

在下一章中，您将学习 MySQL 8 的最佳实践和基准测试。您将了解基准测试和用于基准测试的工具。您还将学习 MySQL 8 一些非常重要功能的最佳实践，例如 memcached、复制、数据分区和索引。

第十四章：MySQL 8 最佳实践和基准测试

在上一章中，你学习了如何扩展 MySQL 8。它涵盖了许多有趣的方面，比如扩展插件并通过 MySQL 8 中的服务调用它们，向 MySQL 8 添加和调试新功能等等。在本章中，我们将介绍 MySQL 8 的最佳实践，这是一个备受期待的版本，承诺解决之前版本的许多不足之处，并具有令人兴奋的新功能。MySQL 8 承诺不仅是一个独立的数据库，而且还将在包括大数据解决方案在内的各个领域发挥重要作用。我们将学习如何在 MySQL 8 中实施最佳实践以最大程度地利用其功能。基准测试将进一步增进我们的理解。

我们将在本章中涵盖以下主题：

• MySQL 基准测试和工具

• memcached 的最佳实践

• 复制的最佳实践

• 数据分区的最佳实践

• 查询和索引的最佳实践

由于突出的优化和改变，MySQL 8 直接从 MySQL 5.7 的发布中提升了版本。MySQL 8 将不再有文件限制，这之前限制了你可以拥有的数据库数量。还有许多令人兴奋的功能，我们在《MySQL 8 简介》第一章中已经介绍了。MySQL 8 现在可以在一个数据库中存储数百万个表。它还可以快速修改表。

我很兴奋地阅读这一章，因为 MySQL 8 的最佳实践不仅影响着你的数据库性能、可伸缩性、安全性和可用性，而且整体上还会暴露出你的系统对最终用户的表现。这是我们的最终目标，不是吗？让我们看看在我们的测试实验室中得出的一些基准测试结果，这些结果肯定会让你眼前一亮：

MySQL 基准测试和工具

我们已经研究了 MySQL 8 中的各种新功能和改进。这让我们更加兴奋，因为性能一直是我们渴望的。由于 MySQL 8 尚未普遍可用，Oracle 还没有发布其基准测试结果。我们没有等待它这样做，而是在一些领域进行了自己的分析。

MySQL 的最佳配置实践是锦上添花；没有樱桃，蛋糕看起来就不完整。除了配置，基准测试还帮助我们验证并找到瓶颈并解决它们。让我们看看一些特定领域，这将帮助我们了解配置和性能基准测试的最佳实践。

资源利用

IO 活动、CPU 和内存使用是你不应该错过的东西。这些指标帮助我们了解系统在进行基准测试和扩展时的表现。它还帮助我们推导每个事务的影响。

延长基准测试时间

我们可能经常希望快速查看性能指标；然而，确保 MySQL 在较长时间的测试中表现一致也是一个关键因素。有一些基本的东西可能会影响性能，比如内存碎片化、IO 的退化、数据积累后的影响、缓存管理等等。

我们不希望我们的数据库因为清理垃圾而重新启动，对吧？因此，建议长时间运行基准测试以验证稳定性和性能。

复制生产设置

让我们在一个生产复制环境中进行基准测试。等等！在复制环境中禁用数据库复制，直到我们完成基准测试。搞定！我们得到了一些不错的数字！

经常发生的情况是，我们并没有完全模拟我们将在生产环境中配置的所有内容。这可能会证明是代价高昂的，因为我们可能无意中在一个可能在生产中产生不利影响的环境中进行基准测试。在进行基准测试时，在你的复制环境中复制生产设置、数据、工作负载等。

吞吐量和延迟的一致性

吞吐量和延迟是相辅相成的。重点应该放在吞吐量上；然而，随着时间的推移，延迟可能是需要注意的。在InnoDB的早期版本中注意到了性能下降、缓慢或停顿。自那时以来它已经有了很大的改进，但由于可能有其他情况取决于你的工作负载，所以始终关注吞吐量和延迟是很好的。

Sysbench 可以做更多

Sysbench 是一个很好的工具，可以模拟你的工作负载，无论是成千上万的表、事务密集型、内存中的数据等等。它是一个很好的模拟工具，并为你提供了很好的表示。

虚拟化世界

我想保持简单；裸金属与虚拟化并不相同。因此，在进行基准测试时，根据你的环境来衡量你的资源。如果你进行比较，你可能会惊讶地看到结果的差异。

并发

大数据坐落在沉重的数据工作负载上；高并发是重要的。MySQL 8 在每个新版本中都在扩展其最大 CPU 核心支持，根据你的需求和硬件资源优化并发应该得到关注。

隐藏的工作负载

不要错过在后台运行的因素，比如用于大数据分析的报告、备份以及在基准测试时进行的即时操作。这些隐藏的工作负载或过时的基准测试工作负载的影响可能会让你的日子（和夜晚）变得痛苦。

你的查询的神经

哎呀！我们错过了优化器吗？还没有。优化器是一个强大的工具，它会读取你的查询的神经并提供建议。这是我在对查询进行更改之前在生产环境中使用的工具。当你需要优化复杂的查询时，它就是救星。

这些是我们应该注意的几个领域。现在让我们看一下我们在 MySQL 8 上进行的一些基准测试，并将它们与 MySQL 5.7 上的基准测试进行比较。

基准测试

首先，让我们从所有的InnoDB表中获取所有列名。以下是我们执行的查询：

SELECT t.table_schema, t.table_name, c.column_name
FROM information_schema.tables t,
information_schema.columns c
WHERE t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND t.engine='InnoDB';

以下图表显示了当有四个实例时，MySQL 8 的性能比 MySQL 5.7 快一千倍：

接着，我们还进行了基准测试，以查找静态表元数据。以下是我们执行的查询：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'chintan%';

以下图表显示了 MySQL 8 的性能比 MySQL 5.7 快大约 30 倍：

这让我们渴望更详细地了解一下。因此，我们想做最后一次测试，以找到动态表元数据。

以下是我们执行的查询：

SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'chintan%';

以下图表显示了 MySQL 8 的性能比 MySQL 5.7 快大约 30 倍：

MySQL 8.0 为表带来了巨大的性能改进。扩展到一百万个表，这是许多大数据需求的需要，现在是可以实现的。一旦 MySQL 8 可用于一般用途，我们期待着更多的基准测试被正式发布。

现在让我们看看我们的下一个主题，这将让你的生活更轻松。这一切都是关于考虑 memcached 的最佳实践。

memcached 的最佳实践

现在可以使用InnoDB memcached 插件进行多个get操作，这将真正有助于提高读取性能。现在可以在单个 memcached 查询中获取多个键值对。频繁的通信流量也已经最小化，因为我们可以一次获取多个数据。

对于 memcached 配置最佳实践，您应该考虑的关键要点现在将要介绍。

资源分配

对于 memcached 的内存分配不应超过可用物理内存，也不应忽视其他可能使用内存的资源。如果我们过度分配内存，memcached 有很高的机会从交换空间中分配内存。这可能导致在插入或提取值时出现延迟，因为交换空间存储在磁盘上，比内存慢。

操作系统架构

由于操作系统架构为 32 位，需要谨慎。我们知道，在 32 位操作系统架构中，资源分配存在限制。

同样，具有 32 位操作系统架构的 4 GB RAM 的 memcached 不应设置超过 3.5 GB RAM，因为这可能会导致性能异常和崩溃。

默认配置

一些关键的默认配置参数应该根据您的需求进行调整：

• 内存分配：默认情况下，这是 64 MB；而应该根据您的需求和测试重新配置

• 连接：默认情况下，这是 1,024 个并发连接；而应该根据您的需求和测试重新配置

• 端口：默认情况下，这在端口11211上监听；而应该出于安全考虑监听另一个端口

• 网络接口：默认情况下，这接受来自所有网络接口的连接；为了安全起见，应该限制

最大对象大小

您应该考虑配置最大对象大小，默认情况下为 1 MB。但是，它可以增加到 128 MB。这纯粹取决于您要存储的数据类型，因此应允许其最大对象大小。允许将开销数据存储在 memcached 中可能会产生不利影响，因为可能有更多的数据需要检索，这可能会导致失败。

积压队列限制

积压队列限制是关于如果达到允许的连接限制，应该保留在 memcached 队列中的连接数。理想情况下，您允许的连接数应该配置得足够满足大部分需求。当 memcached 出现意外的高负载时，积压队列限制可能会有所帮助。理想情况下，它不应超过总连接数的 20%，否则可能会影响系统从 memcached 中获取信息的体验，因为会出现严重延迟。

大页支持

在支持大内存页的系统上，您应该启用 memcached 来利用它们。大页支持有助于分配大数据块来存储数据，并且还减少了使用这种方法的缓存未命中调用的数量。

敏感数据

在 memcached 中存储敏感数据可能构成安全威胁，因为可以访问 memcached 的人可以查看敏感信息。您显然应该采取预防措施来限制 memcached 的曝光。您还可以在将敏感信息存储在 memcached 之前对其进行加密。

限制曝光

Memcached 没有许多内置的安全功能。其中一项措施涉及在所需边界内暴露 memcached 访问。如果您的应用服务器需要与 memcached 通信，它只允许从该服务器访问 memcached，借助系统防火墙规则，如 IP Tables 或类似技术。

故障转移

Memcached 没有良好的故障转移技术。建议您配置应用程序以使其能够故障转移到不可用节点，并在另一个实例中重新生成数据。最好至少配置两个 memcached 以避免由于实例不可用而导致的故障。

命名空间

您可以利用 memcached 提供的命名空间，在将数据存储在 memcached 中之前基本上添加前缀。当您有多个应用程序与 memcached 通信时，这将会有所帮助。这是有帮助的，使用一些基本的命名约定原则，您可以得出一个解决方案。如果有存储名和姓的数据，您可以分别使用前缀，例如 FN 和 LN。这将帮助您轻松地从应用程序中识别和检索数据。

缓存机制

开始利用 memcached 中的缓存的最简单方法之一是使用两列表；您可以利用 memcached 提供的命名空间，基本上添加前缀。第一列将是主键，并且数据库架构应该是通过主键映射以及唯一约束的地址要求的唯一标识符。如果您想通过组合多个列值来获得单个项目值，您应该确保选择适当的数据类型。

具有单个WHERE子句的查询可以轻松映射到 memcached 查找，同时在查询本身中使用=或IN运算符。在使用多个WHERE子句或解析复杂操作（如<、>、LIKE和BETWEEN）的情况下，memcached 可以帮助您解决挑战。建议您使用传统的 SQL 查询将这些复杂操作添加到数据库中。

将整个对象缓存在 memcached 中，而不是选择缓存来自 MySQL 8 的单个行，这将是有益的。例如，对于博客网站，应该在 memcached 中缓存博客端的整个对象。

Memcached 常规统计信息

为了帮助您更好地了解 memcached 的统计信息，我们将提供健康和性能的概述。下表显示了 memcached 返回的统计信息及其含义：

用于定义每个统计信息值的术语是：

• 32u：32 位无符号整数

• 64u：64 位无符号整数

• 32u:32u：由冒号分隔的两个 32 位无符号整数

• 字符串：字符字符串

统计	数据类型	描述
pid	32u	memcached 实例的进程 ID。
uptime	32u	该 memcached 实例的正常运行时间（以秒为单位）。
time	32u	当前时间（作为时期）。
version	字符串	该实例的版本字符串。
pointer_size	字符串	该主机指定的指针大小（32 位或 64 位）。
rusage_user	32u:32u	该实例的总用户时间（秒：微秒）。
rusage_system	32u:32u	该实例的总系统时间（秒：微秒）。
curr_items	32u	该实例存储的当前项目数量。
total_items	32u	在该实例的生命周期内存储的项目总数。
bytes	64u	该服务器用于存储项目的当前字节数。
curr_connections	32u	当前打开连接的数量。
total_connections	32u	服务器运行以来打开的连接总数。
connection_structures	32u	服务器分配的连接结构的数量。
cmd_get	64u	检索请求（get操作）的总数。
cmd_set	64u	存储请求（set操作）的总数。
get_hits	64u	请求并找到的键的数量。
get_misses	64u	请求但未找到的项目数。
delete_hits	64u	已删除并找到的键的数量。
delete_misses	64u	删除但未找到的项目数。
incr_hits	64u	增加并找到的键的数量。
incr_misses	64u	增加但未找到的项目数。
decr_hits	64u	已减少并找到的键的数量。
decr_misses	64u	减少但未找到的项目数。
cas_hits	64u	已比较并交换并找到的键的数量。
cas_misses	64u	已比较并交换但未找到的项目数量。
cas_badvalue	64u	已比较并交换，但比较（原始）值与提供的值不匹配的键的数量。
驱逐	64u	从缓存中移除的有效项目数量，以释放内存供新项目使用。
bytes_read	64u	该服务器从网络中读取的总字节数。
bytes_written	64u	该服务器发送到网络的总字节数。
limit_maxbytes	32u	该服务器允许用于存储的字节数。
threads	32u	请求的工作线程数量。
conn_yields	64u	连接的让步次数（与-R 选项相关）。

参考：dev.mysql.com/doc/refman/8.0/en/ha-memcached-stats-general.html

这些是一些有用的项目，应该随时掌握 memcached 的最佳实践。现在是时候继续前进，看看复制的最佳实践了。

复制的最佳实践

MySQL 8 在复制方面取得了一些重大进展。MySQL 8 主要关注的是扩展性、性能和安全性，以及数据的最高完整性，这有望成为大数据领域的一个改变者。

组复制中的吞吐量

组复制基本上负责在大多数组复制成员同时确认接收事务后提交事务。如果写入的总数不超过组复制成员的容量，这将导致更好的吞吐量。如果容量规划不合适，受影响的成员会出现滞后，与组内其他成员相比。

基础设施规模

基础设施规模是性能和最佳实践清单的常见成功因素。如果基础设施规模不合适或在组复制的节点之间不均匀，可能会对复制基本拓扑产生不利影响。在考虑所需的组件吞吐量时，应考虑每个组件。

恒定的吞吐量

实现恒定的吞吐量是一个很好的成功因素。如果您开始经历影响组复制中其他成员的工作负载，可能是您的主服务器不断接受额外的工作负载并滞后，然后在耗尽所有资源之前可能返回到可接受的水平。此外，您可以实施一个排队方法，可以防止资源耗尽，并且只允许您将工作负载传递给根据容量预定义的成员。

在考虑排队方法时，您不能允许队列呈指数增长。这会影响最终用户，因为数据更新会有延迟。但是，您需要根据自己的需求和业务需求来决定，以实现系统的恒定吞吐量。

矛盾的工作负载

基本上，组复制旨在允许来自组中任何成员的更新。基于行重叠的事务回滚会检查每个事务；其余的将被提交并发送以更新到组中的其他成员。如果同一行上频繁发生多次更新，可能会导致多次回滚。您可能会遇到循环情况，其中一个服务器更新，请求其他服务器更新，并且同时另一个服务器已经为同一行更新。这将导致回滚。

为了防止这种情况，您可以让组中的最后一个成员应用更新，然后再进行下一个成员。您可以从先前执行更新的相同节点路由类似的更新，以防止循环回滚条件的发生。

写扩展性

通过分享写操作来分配您的写入工作负载，这可能会导致更好的吞吐量和更好的写入性能可伸缩性。这将取决于您在系统中预期的矛盾工作负载。当您的高峰工作负载是可以共享负载的情况下，这是有帮助的。通常情况下，如果您对写入可伸缩性进行了良好的容量规划，您将看到微不足道的改进。

请参考下面的图表：

您会注意到，通过多主分发，您的负载具有更好的吞吐量。它还考虑了多主配置中的组大小。

数据分区的最佳实践

一般来说，分区是将任何东西逻辑上分成多个子组，以便每个子组可以独立识别并组合成一个单一分区。

现在让我们学习不同的分区方法以及分区如何在存在大型数据表的情况下提供帮助。

对于任何组织来说，以一种能够提供可伸缩性、性能、可用性和安全性的方式存储数据非常重要。例如，在一个访问量很高的电子商务商店中，经常会有成千上万的订单。因此，为了维护日常订单交付并显示当前订单的仪表板，需要查询显示过去五年的订单表；使用当前数据执行这个过程将需要很长时间。在这里，历史订单数据用于分析用户行为或趋势，但这将需要在有限的数据集上执行。

有各种方法可以实现高可用性、可伸缩性和高性能架构的最佳解决方案；关键因素是分区。在数据库中，每个表中的数据存储在物理文件组中。因此，将这些数据表从单个文件组分割为多个文件组可以减少文件大小，并帮助我们创建一个可伸缩和高性能的数据库。

以下是在数据库中使用分区的关键好处：

• 可伸缩性：由于数据将在多个分区之间共享，服务器可以配置为使用多个节点，并且可以在多个节点之间配置分区。这样做将消除任何硬件限制，并允许数据库大规模扩展以容纳大量数据。

• 高性能：由于数据存储在多个分区中，每个查询将在数据的一小部分上执行。例如，在一个订单历史超过两年的电子商务商店中，要获取本月下的订单列表只需要检查一个分区而不是整个订单历史，从而减少查询执行时间。为了在多个分区上获取查询，我们也可以并行运行，从而减少从数据库获取数据的总时间。

• 高可用性：在分区中，数据被分割到多个文件组中。每个文件组在逻辑上连接在一起，但可以独立访问和处理。因此，如果一个文件组或分区损坏，或者服务器中的一个节点失败，那么我们不会失去对整个表的访问权限，而只是数据库的一部分不可用，从而消除了系统故障的可能性，使您的系统高度可用。

• 安全性：可能有些表中的数据需要高安全措施以避免数据窃取或数据泄漏。通过分区，您可以为一个或多个分区提供额外的安全性，以避免任何安全问题，从而提高数据的可访问性和安全性。

一般来说，分区是将任何东西逻辑上分成多个子组，以便每个子组可以独立识别并组合成一个单一分区。让我们了解在关系型数据库管理系统中分区意味着什么。

分区通常用于将数据分成多个逻辑文件组，以提高性能、可用性和可管理性。在处理大数据时，数据通常以数十亿条记录的形式存在。因此，为了提高数据库的性能，最好将数据分成多个文件组。这些文件组可以在单台机器上或跨多台机器共享，并由一个键标识。这些文件组被称为分区数据。

表中的数据可以通过两种方式进行分区：

• 水平分区

• 垂直分区

水平分区

当表中的行数非常大时，表可以被分成多个分区；这被称为水平分区。使用水平分区时，表的每个分区包含相同数量的列。可以同时访问所有分区，也可以单独访问每个分区。

垂直分区

在垂直分区中，表的列被分区以实现性能和更好地管理数据库。垂直分区可以通过两种方式实现。第一种是通过规范化表。可以将表中的列分成多个表，通过数据的划分。第二种是通过为表中定义的列创建单独的物理文件组。MySQL 8 目前不支持垂直分区。

让我们看看与分区相关的一些好处：

• 如果表包含历史数据，比如应用程序的日志，六个月前的数据对于应用程序的活跃性没有任何意义。如果基于月份创建分区，可以轻松地删除其中一个分区。

• 在前面的日志案例中，如果我们想要在两个日期之间过滤数据，MySQL 优化器可以识别特定的分区，从中找到过滤记录，这可能会导致查询结果更快，因为要检查的行数大大减少。

• MySQL 8 还支持在特定分区上查询数据。当您知道需要查询所需数据的分区时，可以减少要检查的记录数。

在 MySQL 中修剪分区

修剪是数据的选择性提取。由于我们有多个分区，在检索过程中将遍历每个分区，这是耗时的并且会影响性能。在搜索时，一些分区也会被包括在内，而请求的数据在该分区内并不可用，这是一个额外的过程。修剪在这里有助于仅搜索具有相关数据的分区，这将避免在检索过程中不必要地包括这些分区。

这种优化避免了扫描可能没有匹配值的分区，被称为分区修剪。在分区修剪中，优化器分析 SQL 语句中的FROM和WHERE子句，以消除不必要的分区，并扫描与 SQL 语句相关的数据库分区。

查询和索引的最佳实践

很难为参考和重用编写最佳查询。它将始终根据您的应用程序性质、架构、设计、表结构等而变化。但是，在编写 MySQL 查询时可以采取预防措施，以获得更好的性能、可扩展性和完整性。

让我们来看看在设计或编写 MySQL 查询时应该牢记的一些最佳实践。

数据类型

数据库表可能包含多个具有数字或字符串等数据类型的列。MySQL 8 提供了各种数据类型，而不仅仅限于数字或字符串：

• 小尺寸是好的。由于 MySQL 将数据加载到内存中，大数据量会对其性能产生不利影响。较小的数据集可以在内存中容纳更多数据，并减少资源利用的开销。

• 修复您的长度。如果不修复数据类型长度，每次需要时它都必须去获取所需的信息。因此，无论在哪里，您都可以使用 char 数据类型来限制数据长度。

非空

MySQL 不太喜欢非空数据。非空列使用更多存储空间，影响性能，并需要在 MySQL 中进行额外处理。

优化引用空数据的查询也很困难。当空数据列被索引时，它会为每个条目使用额外的字节。

索引

索引很重要，因为它可以改善您设计不良的查询和表结构的性能，甚至可以将一个设计良好的查询变成一个性能不佳的查询，这也会影响性能。

搜索字段索引

通常，我们在 MySQL 查询中用作过滤器的字段上进行索引。这显然有助于更快地读取，但可能会对写入/更新产生不利影响，因此只索引您需要的内容将是一个明智的决定。

数据类型和连接

MySQL 可以对不同数据类型进行连接，但如果要求 MySQL 使用不同的数据类型进行连接字段，性能可能会受到影响，因为它必须将每一行从一种类型转换为另一种类型。

复合索引

如果一个查询要引用表的多个列，那么为这些列创建一个复合索引可能会有所帮助。复合索引按照第一列、第二列等结果集中的列进行引用。

列的顺序在查询的性能中起着重要作用，因此在设计表结构和索引时，您需要有效地使用它。

缩短主键

对于主键来说，小尺寸也是好的。缩短主键会像我们讨论数据类型那样有益。由于主键较小，您的索引大小将更小，因此缓存的使用量将更少，因此可以在内存中容纳更多数据。

最好使用数字类型，因为这些比字符要小得多，以实现缩短主键的目标。在进行连接时可能会有所帮助，因为通常会引用主键进行连接。

索引一切

索引一切是一个好主意；然而，MySQL 不会这样做。您知道吗，如果 MySQL 要扫描的索引高于 30%，它将执行全表扫描吗？不要对不需要索引的值进行索引。

我们需要记住，索引在正确使用时有助于获取数据；然而，在编写/更新数据时，它是一种负担。

获取所有数据

select *... - 啊！除非真的需要，否则不要使用这个。到目前为止，我的经验还没有需要这个。获取所有数据会减慢执行时间，并且严重影响 MySQL 服务器的资源利用率。您需要提供一个特定的列名或适当的条件。

让应用程序来完成工作

让应用程序也为 MySQL 完成工作。您可以通过让应用程序进行排序来避免使用order by等子句。在 MySQL 中进行排序比在应用程序中慢得多。您可以确定应该由应用程序处理的查询。

数据的存在

使用EXISTS子句检查数据的存在要快得多。EXISTS子句将在从获取的数据中获取第一行后立即返回输出。

限制自己

限制自己只获取需要的数据。在获取数据时，始终确保使用适当的限制，因为获取不需要的数据将毫无用处，并影响性能。在你的 SQL 查询中使用LIMIT子句。

分析慢查询

这是一个要遵循的良好实践。随着数据的增长，我们可能会错过要么优化要么意识到对查询产生不利影响的查询。你可能会对需要获取的数据的需求发生变化，而我们可能会忽视查询的影响。始终密切关注可以在 MySQL 中配置并优化的慢查询是很好的。

查询成本

你的查询成本是多少？解释是对这个问题的正确回答。使用explain查询参数来了解你的查询受到了什么影响——无论是全表扫描、索引扫描、范围访问等等。明智地利用explain提供的信息，进一步优化查询。这是 MySQL 的一个奇妙、快速、方便的工具。如果你知道你已经尽力了，索引将成为一个救世主，根据你的需求进一步优化它。

编写查询的最佳实践始于需求、设计、实施和持续维护。这是一个我们无法分散的完整生命周期。理解模式、索引和分析起着重要作用。对我们来说，响应时间和最佳资源利用率至关重要。

我个人喜欢深入研究这个领域，远远超出我们在这里所能提及的范围——这是一个关系的世界！你的查询将会遇到表的行或列，或者与另一个表连接。除此之外，如果你没有做对，你会试图从一个不需要的子集中找到一个关系。我们怎么能忘记适当使用的索引是救世主呢？所有这些加在一起将展示我们的关系，并迅速响应请求的查询。

总结

我相信在阅读本章时，你已经记住了需要注意的事项，或者在回忆这些事项，如果你的 MySQL 8 实现中有任何遗漏。在这些章节中，我们讨论了 MySQL 8 的最佳实践，这些最佳实践在实施、使用、管理和故障排除等各个阶段都会有所帮助，并且会成为 MySQL 8 最佳实践的指引；这些可能会根据不同的用例而有所不同。适当的测试和验证将有助于确认实施最佳实践的好处。

我们广泛涵盖了一些关于 MySQL 8 基准测试和一些配置参数以及 memcached 最佳实践的令人兴奋的主题。我们讨论了 MySQL 复制的最佳实践，其中我们经历了一些关键要点。最后，还讨论了 MySQL 查询和索引的指针，以及数据分区的最佳实践。这一章中的任何内容都不足以涵盖所有内容，但提供的指针是必要的。

到目前为止，我们应该对 MySQL 8 有了很好的理解；现在是解决问题的时候了。

现在让我们转到下一章，看看我们如何可能遇到许多常见问题，识别错误代码以及用于排除 MySQL 8 故障的真实场景。

第十五章：MySQL 8 故障排除

在上一章中，我们学习了 MySQL 8 数据库、基准测试和最佳实践的一个重要方面。基准测试有助于比较当前数据库性能与预期性能矩阵。我们了解了什么是基准测试，以及可以用来查找 MySQL 8 服务器基准性能的工具。在本章的后面部分，我们学习了关于 memcached、复制、分区和索引的最佳实践。最佳实践有助于确保 MySQL 8 数据库的最佳配置。

在本章中，重点将放在理解在使用 MySQL 8 数据库时可能遇到的常见错误上。错误可能是服务器错误或客户端错误。我们将探讨一种确定问题发生的方法。我们还将学习错误的故障排除和解决技术。在本章的后面部分，我们将探讨这些技术适用的真实场景。以下是要涵盖的主题列表：

• MySQL 8 常见问题

• MySQL 8 服务器错误

• MySQL 8 客户端错误

• MySQL 8 故障排除方法

• 真实场景

MySQL 8 常见问题

在故障排除时，首先要做的是找出导致问题的程序或设备。

以下是表明硬件或内核问题的症状：

• 键盘无法正常工作。可以通过按下大写锁定键来检查。如果大写锁定键上的灯不亮，那么键盘有问题。同样，鼠标不动表示鼠标有问题。

• ping是一个操作系统命令，用于检查一台计算机从另一台计算机的可访问性。执行 ping 命令的计算机称为本地计算机，而被 ping 的计算机称为远程计算机。如果远程计算机不响应本地计算机的 ping，表示存在硬件或网络相关的问题。

• 如果除了 MySQL 之外的程序无法正常工作，可能表明操作系统内核程序有问题。

• 如果系统意外重启，可能表明操作系统或硬件有问题。在典型情况下，用户级程序不应该能够使系统崩溃。

要排除问题，可以执行以下一项或多项操作：

• 运行诊断工具检查硬件

• 确保相关的库文件是最新的

• 检查操作系统的更新、补丁或服务包的可用性

• 检查所有连接

ECC 内存是纠错码内存。它可以检测和纠正大多数常见的内部数据损坏问题。建议使用 ECC 内存以便在早期检测内存问题。

以下说明可能有助于进一步确定问题：

• 检查系统日志文件可能有助于发现问题的原因。如果 MySQL 出现问题，还必须检查 MySQL 日志文件。

• 可以使用特定于操作系统的命令来检查内存、文件描述符、磁盘空间或其他关键资源的问题。

• 如果一个有问题的运行进程即使我们执行了杀死它的命令仍不会死掉，那么操作系统内核中可能存在一个 bug。

• 如果硬件似乎没有问题，应该尝试确定可能导致问题的程序。使用特定于操作系统的命令，如 Windows 上的任务管理器，Linux 上的ps和top，或类似的程序，我们可以识别占用 CPU 或阻塞系统进程的程序。

• 即使键盘被锁定，也可以恢复对计算机的访问。可以通过从另一台计算机登录系统来实现。成功登录后执行kbd_mode -a命令。

MySQL 用户可以通过使用 MySQL 提供的多个渠道之一来报告问题。在检查了所有可能的替代方案之后，如果可以确定是 MySQL 服务器或 MySQL 客户端引起了问题，用户可以为邮件列表创建错误报告或联系 MySQL 支持团队。报告人必须提供有关错误、系统信息和行为以及预期行为的详细信息。报告人必须根据为什么似乎是 MySQL 错误的原因描述原因。如果程序失败，了解以下信息很有用：

• 使用top命令，检查所讨论的程序是否占用了所有的 CPU 时间。在这种情况下，我们应该允许程序运行一段时间，因为可能程序正在执行密集的计算指令。

• 观察 MySQL 服务器对客户端程序尝试连接时的响应。它停止响应了吗？服务器提供了任何输出吗？

• 如果发现 MySQL 服务器在mysqld程序中引起问题，请尝试使用mysqladmin程序连接以检查mysqld是否有响应。可以使用mysqladmin -u root ping或mysqladmin -u root processlist命令。

• 失败的程序是否发生了分段错误？

最常见的 MySQL 错误

本节提供了用户经常遇到的最常见的 MySQL 错误列表。

访问被拒绝

MySQL 提供了一个特权系统，用于验证从主机连接的用户，并将用户与数据库上的访问权限关联起来。权限包括SELECT、INSERT、UPDATE和DELETE，并能够识别匿名用户并授予 MySQL 特定功能的权限，例如LOAD DATA INFILE和管理操作。

访问被拒绝的错误可能是由许多原因引起的。在许多情况下，问题是由于客户端程序使用的 MySQL 帐户与 MySQL 服务器连接时获得了服务器的许可。

无法连接到[local] MySQL 服务器

在本节中，我们将重点关注遇到无法连接到 MySQL 服务器错误的情况。但在我们跳到特定错误的细节之前，有必要了解 MySQL 客户端如何连接到 MySQL 服务器。

在 Unix 系统上，MySQL 客户端连接到mysqld服务器进程有两种不同的方式。以下是这两种方法的详细信息：

• TCP/IP 连接：mysqld服务器进程在特定端口上监听客户端连接。MySQL 客户端使用指定的 TCP/IP 端口连接服务器。

• Unix 套接字文件：在这种连接模式下，Unix 套接字文件用于通过文件系统（/tmp/mysql.sock）进行连接。

与 TCP/IP 相比，套接字文件连接速度更快，但只能在连接到同一台计算机上的服务器时使用。要使用 Unix 套接字文件，我们不指定主机名或应指定特殊主机名 localhost。

以下是 MySQL 客户端在 Windows 上连接到 MySQL 服务器的方式：

• TCP/IP 连接：与 Unix 系统之前描述的一样，TCP/IP 连接在指定的端口号上运行。MySQL 客户端连接到 MySQL 服务器正在监听的端口。

• 命名管道连接：MySQL 服务器可以使用--enable-named-pipe选项启动。如果客户端在运行服务器的主机上运行，则客户端可以使用命名管道连接。MySQL是命名管道的默认名称。如果在连接到mysqld服务器进程时未提供主机名，则 MySQL 首先尝试连接到默认命名管道。如果无法连接到命名管道，则尝试连接到 TCP/IP 端口。在 Windows 上可以通过使用.作为主机名来强制使用命名管道。

MySQL 错误由预定义的唯一错误代码标识。相同的错误可能与不同的错误代码相关联。具有错误代码2002的无法连接到 MySQL 服务器错误表示三个问题之一。可能是 MySQL 服务器没有在系统上运行，或者提供的 Unix 套接字文件名不正确，或者提供的用于连接到服务器的 TCP/IP 端口号不正确。TCP/IP 端口可能被防火墙或端口阻止服务阻止。

错误代码2003也与无法连接到 MySQL 服务器相关联。它表示服务器拒绝了网络连接。应该检查 MySQL 服务器是否启用了网络连接，MySQL 服务器是否正在运行，并且服务器上是否配置了指定的网络端口。

以下命令可用于确保mysqld服务器进程正在运行：

> ps xa | grep mysqld

如果mysqld服务器进程没有运行，我们应该启动服务器。如果服务器已经运行，应使用以下命令：

> mysqladmin version
> mysqladmin variables
> mysqladmin -h `hostname` version variables
> mysqladmin -h `hostname` --port=3306 version 
> mysqladmin -h host_ip version
> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

在上述命令中，hostname是运行 MySQL 服务器的计算机的主机名。host_ip是服务器机器的 IP 地址。

与 MySQL 服务器的连接丢失

与 MySQL 服务器的连接丢失错误可能是由本节中解释的三种可能原因之一引起的。

错误的一个潜在原因是网络连接出现问题。如果这是一个频繁出现的错误，应该检查网络条件。如果错误消息中包含during query，那么可以肯定是由于网络连接问题导致了错误。

connection_timeout系统变量定义了mysqld服务器在连接超时响应之前等待连接数据包的秒数。很少情况下，当客户端尝试与服务器进行初始连接并且connection_timeout值设置为几秒时，可能会发生此错误。在这种情况下，可以通过根据距离和连接速度增加connection_timeout值来解决问题。SHOW GLOBAL STATUS LIKE和Aborted_connects可用于确定我们是否更频繁地遇到此问题。可以肯定地说，如果错误消息包含reading authorization packet，增加connection_timeout值就是解决方案。

可能会因为Binary Large OBject（BLOB）值大于max_allowed_packet而出现问题。这可能会导致客户端与 MySQL 服务器的连接丢失错误。如果观察到ER_NET_PACKET_TOO_LARGE错误，则确认应增加max_allowed_packet值。

密码输入错误时失败

当客户端程序在没有密码值的情况下使用--password或-p选项调用时，MySQL 客户端会要求密码。以下是命令：

> mysql -u user_name -p
Enter password:

在一些系统上，当在选项文件或命令行中指定密码时，密码可以正常工作。但是，在Enter password:提示符处交互输入时，密码无法正常工作。这是因为系统提供的用于读取密码的库将密码值限制为少量字符（通常为八个）。这是系统库的问题，而不是 MySQL 的问题。作为解决方法，将 MySQL 密码更改为八个或更少字符的值，或将密码存储在选项文件中。

主机 host_name 被阻止

如果mysqld服务器从中断的主机接收了太多连接请求，将出现以下错误：

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

max_connect_errors系统变量确定允许的连续中断连接请求的次数。一旦有max_connect_errors次失败的请求而没有成功的连接，mysqld就会认为出现了问题，并阻止主机进一步连接，直到发出FLUSH HOSTS语句或mysqladmin flush-hosts命令。

默认情况下，mysqld在 100 个连接错误后会阻止主机。可以通过在服务器启动时设置max_connect_errors值来进行调整，如下所示：

> mysqld_safe --max_connect_errors=10000

此值也可以在运行时设置，如下所示：

mysql> SET GLOBAL max_connect_errors=10000;

如果针对特定主机收到host_name被阻止的错误，首先应检查主机的 TCP/IP 连接是否存在问题。如果网络存在问题，则增加max_connect_errors变量的值是无济于事的。

连接过多

此错误表示所有可用连接都用于其他客户端连接。max_connections是控制与服务器连接数的系统变量。最大连接数的默认值为 151。我们可以为max_connections系统变量设置大于 151 的值，以支持超过 151 个连接。

mysqld服务器进程实际上允许比max_connections（max_connections + 1）值多一个连接。额外的一个连接被保留给具有CONNECTION_ADMIN或SUPER特权的帐户。管理员可以通过具有PROCESS特权的访问来授予该特权。有了这个访问权限，管理员可以使用保留的连接连接到服务器。他们可以执行SHOW PROCESSLIST命令来诊断问题，即使最大客户端连接数已用完。

内存不足

如果mysql没有足够的内存来存储 MySQL 客户端程序发出的查询的整个请求，服务器会抛出以下错误：

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

为了解决问题，我们必须首先检查查询是否正确。我们是否期望查询返回这么多行？如果不是，我们应该纠正查询并再次执行。如果查询是正确的且不需要更正，我们可以使用--quick选项连接mysql。使用--quick选项会导致mysql_use_result() C API 函数用于获取结果集。该函数会增加服务器的负载，减少客户端的负载。

数据包太大

通信数据包是以下内容之一：

• MySQL 客户端发送到 MySQL 服务器的单个 SQL 语句

• 从 MySQL 服务器发送到 MySQL 客户端的单行

• 从复制主服务器发送到复制从服务器的二进制日志事件

1 GB 数据包大小是可以传输到 MySQL 8 服务器或客户端的最大可能数据包大小。如果接收到大于max_allowed_packet字节的数据包，MySQL 服务器或客户端会发出ER_NET_PACKET_TOO_LARGE错误并关闭连接。

MySQL 客户端程序的默认max_allowed_packet大小为 16 MB。可以使用以下命令来设置更大的值：

> mysql --max_allowed_packet=32M

MySQL 服务器的默认值为 64 MB。值得注意的是，为此系统变量设置更大的值是没有害处的，因为额外的内存会根据需要分配。

表已满

表已满错误发生在以下条件之一：

• 磁盘已满

• 表已达到最大大小

MySQL 数据库中的实际最大表大小可以通过操作系统对文件大小的限制确定。

无法创建/写入文件

如果在执行查询时出现以下错误，则表示 MySQL 无法在结果集的临时目录中创建临时文件：

Can't create/write to file '\\sqla3fe_0.ism'.

错误的可能解决方法是使用--tmpdir选项启动mysqld服务器。以下是命令：

> mysqld --tmpdir C:/temp

作为替代，可以在 MySQL 配置文件的[mysqld]部分中指定如下：

[mysqld]
tmpdir=C:/temp

命令不同步

如果客户端函数的调用顺序错误，则会收到命令不同步的错误。这意味着命令无法在客户端代码中执行。例如，如果我们执行mysql_use_result()并在执行mysql_free_result()之前尝试执行另一个查询，则可能会出现此错误。如果我们在调用mysql_use_result()或mysql_store_result()函数之间执行两个返回结果集的查询，也可能会发生这种情况。

忽略用户

在mysqld服务器启动时或服务器重新加载授权表时，如果在用户表中找到具有无效密码的帐户，则会收到以下错误：

Found wrong password for user 'some_user'@'some_host'; ignoring user

由于 MySQL 权限系统忽略了该帐户，因此会出现问题。为了解决问题，我们应该为该帐户分配一个新的有效密码。

表 tbl_name 不存在

以下错误表示默认数据库中不存在指定的表：

Table 'tbl_name' doesn't exist
Can't find file: 'tbl_name' (errno: 2)

在某些情况下，用户可能会错误地引用表。这是可能的，因为 MySQL 服务器使用目录和文件来存储数据库表。根据操作系统文件管理的不同，数据库和表名可能区分大小写。

对于不区分大小写的文件系统，例如 Windows，在查询中使用的指定表的引用必须使用相同的字母大小写。

MySQL 8 服务器错误

本节重点介绍 MySQL 8 服务器错误。该部分描述了与 MySQL 服务器管理、表定义和 MySQL 8 服务器中已知问题相关的错误。

文件权限问题

如果在服务器启动时设置了UMASK或UMASK_DIR环境变量，则可能会出现文件权限问题。在表创建时，MySQL 服务器可能会发出以下错误消息：

ERROR: Can't find file: 'path/with/file_name' (Errcode: 13)

UMASK和UMASK_DIR系统变量的默认值分别为 0640 和 0750。如果这些环境变量的值以零开头，则表示 MySQL 服务器的值是八进制的。例如，八进制中的默认值 0640 和 0750 分别等于十进制的 415 和 488。

为了更改默认的UMASK值，我们应该启动mysqld_safe，如下所示：

> UMASK=384 # = 600 in octal 
> export UMASK 
> mysqld_safe

MySQL 服务器创建数据库目录时的默认访问权限值为0750。我们可以设置UMASK_DIR变量来修改这种行为。如果设置了此值，新目录将以UMASK和UMASK_DIR值的组合作为访问权限值创建。

以下是提供所有新目录组访问权限的示例：

> UMASK_DIR=504 # = 770 in octal 
> export UMASK_DIR 
> mysqld_safe &

重置根密码

如果在 MySQL 中从未设置根密码，则 MySQL 服务器连接为根用户时不需要密码。如果之前分配的密码被遗忘，可以进行重置。

以下是在 Windows 系统上重置root @ localhost帐户密码的说明：

1. 使用系统管理员凭据登录系统。

2. 如果 MySQL 服务器已经在运行，请停止服务器。如果 MySQL 服务器作为 Windows 服务运行，请按照开始菜单|控制面板|管理工具|服务找到服务。在服务中，找到 MySQL 服务并停止它。如果 MySQL 服务器没有作为 Windows 服务运行，请使用 Windows 任务管理器杀死 MySQL 服务器进程。

3. 一旦 MySQL 服务器停止，创建一个包含密码分配语句的单行文本文件，如下所示：

 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

1. 保存文件。例如，将文件保存为C:\mysql-root-reset.txt。

2. 按照开始菜单|运行|cmd 打开 Windows 命令提示符。

3. 在命令提示符中，使用--init-file选项启动 MySQL 服务器，如下所示：

 C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin" 
        C:\> mysqld --init-file=C:\\mysql-root-reset.txt

1. 一旦 MySQL 服务器重新启动，删除C:\mysql-root-reset.txt文件。

以下是在类 Unix 系统上重置根用户密码的说明：

1. 使用与 MySQL 服务器运行的相同用户登录系统。通常是mysql用户。

2. 如果 MySQL 服务器已经运行，请停止服务器。为此，找到包含 MySQL 服务器进程 ID 的.pid文件。根据 Unix 发行版的不同，文件的实际位置和名称可能不同。通常的位置是/var/lib/mysql/，/var/run/mysqld/和/usr/local/mysql/data/。通常，文件名以mysqld或系统主机名开头，并具有.pid扩展名。可以通过向mysqld服务器进程发送正常的 kill 命令来停止 MySQL 服务器。可以使用以下命令和.pid文件的实际路径名：

 > kill 'cat /mysql-data-directory/host_name.pid'

1. 一旦 MySQL 服务器停止，创建一个包含密码赋值语句的文本文件，如下所示：

 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

1. 保存文件。假设文件存储在/home/me/mysql-reset-root。由于文件包含 root 用户的密码，应确保其他用户无法读取它。如果我们没有使用适当的用户登录，我们应该确保用户有权限读取该文件。

2. 使用--init-file选项启动 MySQL 服务器，如下所示：

 > mysqld --init-file=/home/me/mysql-reset-root &

1. 一旦服务器启动，删除/home/me/mysql-reset-root中的文件。

以下是重置 root 用户密码的通用说明：

1. 如果 MySQL 服务器正在运行，请停止服务器。一旦停止，使用--skip-grant-tables特权重新启动 MySQL 服务器。除了--skip-grant-tables，--skip-networking选项会自动启用，以防止远程连接。

2. 使用mysql客户端程序连接到 MySQL 服务器。由于服务器是使用--skip-grant-tables启动的，因此不需要密码：

 > mysql

1. 在 MySQL 客户端本身中，要求服务器重新加载授予表。这将启用帐户管理语句：

 mysql> FLUSH PRIVILEGES;

1. 使用以下命令更改root @ localhost帐户密码：

 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 
          'NewPassword';

1. 重新启动服务器并使用 root 用户和新设置的密码登录。

MySQL 崩溃预防

作为标准发布实践，每个 MySQL 版本在发布之前都会在不同的平台上进行验证。假设 MySQL 可能有一些难以发现的错误。当我们遇到 MySQL 的问题时，如果我们尝试找出系统崩溃的原因，这将是有帮助的。首先要确定的是mysqld服务器进程是否崩溃，或者问题出现在 MySQL 客户端程序上。可以通过执行mysqladmin version命令来检查 MySQL 服务器运行了多长时间。以下是一个示例输出：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin version -u root -p
Enter password: *****
mysqladmin Ver 8.0.3-rc for Win64 on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2017, 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.

Server version 8.0.3-rc-log
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 9 days 4 hours 4 min 52 sec

Threads: 2 Questions: 4 Slow queries: 0 Opens: 93 Flush tables: 2 Open tables: 69 Queries per second avg: 0.000

resolve_stack_dump是一个实用程序，用于将数字堆栈转储解析为符号。为了分析mysqld服务器进程死机的根本原因，我们在堆栈跟踪错误日志中找到。可以使用resolve_stack_dump程序解决这个问题。必须注意的是，错误日志中找到的变量值可能不准确。

损坏的数据或索引文件可能会导致 MySQL 服务器崩溃。这些文件在执行每个 SQL 语句之前和客户端被通知结果之前使用write()系统在磁盘上进行更新。这意味着即使在mysqld崩溃的情况下，数据文件中的内容也是安全的。未刷新的数据在磁盘上的写入由操作系统负责。--flush选项可以与mysqld一起使用，以强制 MySQL 在每个 SQL 语句执行后将所有内容刷新到磁盘上。

以下是 MySQL 损坏表的原因之一：

• 如果数据文件或索引文件崩溃，则其中包含损坏的数据。

• MySQL 服务器进程中的一个错误导致服务器在更新过程中崩溃。

• 外部程序在没有表锁定的情况下与mysqld同时操纵数据和索引文件。

• 在更新过程中，MySQL 服务器进程被终止。

• 许多mysqld服务器正在系统上运行。这些服务器使用相同的数据目录。系统没有良好的文件系统锁定，或者外部锁定被禁用。

• 可能会发现数据存储代码中存在错误。我们可以尝试通过在已修复的表的副本上使用ALTER TABLE来更改存储引擎。

处理 MySQL 磁盘满

本节重点介绍 MySQL 对磁盘满错误和超出配额错误的响应。它更相关于MyISAM表中的写入。它可以应用于二进制日志文件和索引文件的写入。它排除了应被视为事件的行和记录的引用。

当磁盘满时，MySQL 执行以下操作：

• MySQL 确保有足够的空间可用来写入当前行。

• MySQL 服务器每 10 分钟在日志文件中写入一条条目。它会警告磁盘满的情况。

应采取以下措施来解决问题：

• 应该释放磁盘空间，以确保有足够的空间来插入所有记录。

• 我们可以执行mysqladmin kill命令来中止线程。下次检查磁盘时，线程将被中止。

• 可能会有一些线程在等待导致磁盘满的表。在多个被锁定的线程中，杀死等待磁盘满条件的线程将使其他线程继续运行。

• REPAIR TABLE或OPTIMIZE TABLE语句是前述条件的例外。其他例外包括在LOAD DATA INFILE或ALTER TABLE语句之后批量创建的索引。这些 SQL 语句可能会创建大量的临时文件。这可能会给系统的其余部分带来大问题。

MySQL 临时文件存储

TMPDIR环境变量的值在 Unix 上被 MySQL 用作存储临时文件的目录路径名。如果未设置TMPDIR，MySQL 会使用系统默认值，如/tmp、/var/tmp或/usr/tmp。

MySQL 在 Windows 上会检查TMPDIR、TEMP和TMP环境变量的值。如果 MySQL 找到已设置的变量，它将使用该值，并不会检查剩余的值。如果这三个变量都未设置，MySQL 将使用系统默认值，即C:\windows\temp\。

如果文件系统中的临时文件目录太小，我们可以使用mysqld --tmpdir选项来指定具有足够空间的文件系统上的目录。对于复制，在从服务器上，我们可以使用--slave-load-tmpdir并指定在复制LOAD DATA INFILE语句期间保存临时文件的目录。可以使用--tmpdir选项以轮询方式设置多个路径的列表。在 Unix 系统上，路径可以用冒号字符(:)分隔，而在 Windows 上，可以用分号字符(;)分隔路径。

为了有效地分配负载，多个临时目录路径应该属于不同的物理磁盘，而不是同一磁盘的不同分区。

对于作为复制从服务器工作的 MySQL 服务器，我们必须注意设置--slave-load-tmpdir选项，以免指向基于内存的文件系统中的目录，或者指向在服务器或服务器主机重新启动时清除的目录。为了复制临时表或LOAD DATA INFILE操作，复制从服务器需要在机器重新启动时其临时文件。如果临时文件目录中的文件丢失，复制将失败。

当mysqld服务器进程终止时，MySQL 会负责删除临时文件。在类 Unix 平台上，可以在打开文件后取消链接文件。这样做的一个主要缺点是该文件名不会出现在目录列表中。还有可能我们看不到占用文件系统的大文件。

ibtmp1是InnoDB存储引擎用来存储临时表的表空间文件的名称。该文件位于 MySQL 的数据目录中。如果我们想要指定不同的文件名和位置，可以在服务器启动时使用innodb_temp_data_file_path选项。

如果ALTER TABLE操作在InnoDB表上使用ALGORITHM=COPY技术，存储引擎会在相同目录中创建原始表的临时副本。临时表的文件名以#sql-前缀开头。它们只在执行ALTER TABLE操作时短暂出现。

如果使用ALGORITHM=INPLACE方法通过ALTER TABLE SQL 语句重建InnoDB表，则InnoDB存储引擎会在与原始表相同的目录中创建原始表的中间副本。中间表的文件名以#sql-ib前缀开头。它们只在执行ALTER TABLE操作时短暂出现。

innodb_tmpdir选项不能应用于中间表文件。这些中间文件始终在与原始表相同的目录中创建和存储。

使用ALGORITHM=INPLACE方法重建InnoDB表的ALTER TABLE SQL 语句会在默认的 MySQL 临时目录中创建临时排序文件。默认临时目录由 Unix 上的$TMPDIR，Windows 上的%TEMP%或--tmpdir选项指定的目录表示。如果临时目录不足以存储这样的文件，可能需要重新配置tmpdir。作为替代方案，可以使用innodb_tmpdir选项为在线InnoDB ALTER TABLE语句定义另一个临时目录。innodb_tmpdir选项可以在运行时使用SET GLOBAL或SET SESSION语句进行配置。

在复制环境中，如果所有服务器具有相同的操作系统环境，则应考虑复制innodb_tmpdir配置。在其他情况下，innodb_tmpdir设置复制可能会导致在线ALTER TABLE操作执行失败。如果操作环境不同，建议为每台服务器单独配置innodb_tmpdir。

MySQL Unix 套接字文件

MySQL 服务器使用/tmp/mysql.sock作为与本地客户端通信的 Unix 套接字文件的默认位置。根据不同的发行格式，如 RPMs 的/var/lib/mysql，可能会有所不同。

在几个 Unix 版本上，可以删除存储在/tmp目录和其他类似目录中的文件。如果套接字文件存储在文件系统上的这种目录中，可能会导致问题。

可以保护/tmp目录，以确保文件只能由所有者或 root 超级用户删除。这在几乎每个版本的 Unix 上都是可能的。可以在以 root 用户登录时设置/tmp目录的粘滞位。以下是执行相同操作的命令：

chmod +t /tmp

使用ls -ld /tmp命令，还可以检查粘滞位是否已设置。如果最后一个权限字符是t，则设置了该位。粘滞位用于定义 Unix 系统中的文件权限。

还有一种替代方法，即更改 Unix 套接字文件的位置。如果更改 Unix 套接字文件的位置，必须确保客户端程序也知道文件的新位置。以下是实现这一点的方法：

• 可以在全局或本地选项文件中设置路径，如下所示：

 [mysqld]
 socket=/path/to/socket

 [client]
 socket=/path/to/socket

• • 我们还可以在命令行上为mysqld_safe指定--socket选项，并在运行客户端程序时也可以这样做。

• MYSQL_UNIX_PORT环境变量可以设置为 Unix 套接字文件的路径。

• MySQL 也可以重新从源代码编译，以便将不同的 Unix 套接字文件位置作为默认值使用。

使用以下命令，可以确保新的套接字位置有效：

mysqladmin --socket=/path/to/socket version

时区问题

MySQL 服务器必须告知用户当前的时区，如果我们在使用SELECT NOW()时返回的是 UTC 时间而不是用户当前的时区。如果UNIX_TIMESTAMP()返回错误的数值也适用。这应该针对运行服务器的环境进行设置，例如mysqld_safe或mysql.server。

我们还可以使用--timezone=timezone_name选项与mysqld_safe一起设置服务器时区。也可以在启动mysqld之前将值分配给TZ环境变量来设置时区。

--timezone或TZ的允许值列表取决于系统。

MySQL 8 客户端错误

本节重点介绍 MySQL 8 客户端出现的错误。MySQL 客户端的工作是连接到 MySQL 服务器，以执行 SQL 查询并从 MySQL 8 数据库获取结果。本节列出了与查询执行相关的错误。

字符串搜索中的区分大小写

字符串搜索使用非二进制字符串的比较操作数的逻辑顺序，例如CHAR，VARCHAR和TEXT。二进制字符串的比较，如BINARY，VARBINARY和BLOB使用操作数中字节的数值。这基本上意味着对于字母字符，比较将区分大小写。

将非二进制字符串与二进制字符串进行比较将被视为二进制字符串之间的比较。

比较操作，如>=, >, =, <, <=, sorting和grouping取决于每个字符的排序值。具有相似排序值的字符被视为相同字符。以 e 和é为例。这些字符在提供的逻辑顺序中具有相同的排序值。这些被视为相等。

utf8mb4和utf8mb4_0900_ai_ci分别是默认的字符集和排序规则。默认情况下，非二进制字符串比较是不区分大小写的。这意味着如果我们使用col_name LIKE 'a%'搜索，我们将得到所有以 A 或 a 开头的列值。要使其区分大小写，我们必须确保其中一个操作数具有二进制或区分大小写的排序规则。例如，如果将列与字符串进行比较，并且两者都具有utf8mb4字符集，则可以使用COLLATE运算符来使其中一个操作数具有utf8mb4_0900_as_cs或utf8mb4_bin排序规则。以下是一个示例：

col_name COLLATE utf8mb4_0900_as_cs LIKE 'a%' 
col_name LIKE 'a%' COLLATE utf8mb4_0900_as_cs 
col_name COLLATE utf8mb4_bin LIKE 'a%' 
col_name LIKE 'a%' COLLATE utf8mb4_bin

为了将非二进制区分大小写字符串比较更改为不区分大小写，我们应该使用COLLATE来命名一个不区分大小写的排序规则。以下是COLLATE如何将比较更改为区分大小写的示例：

mysql> SET NAMES 'utf8mb4'; 
mysql> SET @s1 = 'MySQL' COLLATE utf8mb4_bin, @s2 = 'mysql' COLLATE utf8mb4_bin; mysql> SELECT @s1 = @s2;
+-----------+ 
| @s1 = @s2 | 
+-----------+ 
|         0 | 
+-----------+ 
mysql> SELECT @s1 COLLATE utf8mb4_0900_ai_ci = @s2; 
+--------------------------------------+ 
| @s1 COLLATE utf8mb4_0900_ai_ci = @s2 | 
+--------------------------------------+ 
|                                    1 | 
+--------------------------------------+

DATE 列的问题

在 MySQL 中，DATE值的默认格式是YYYY-MM-DD。标准 SQL 不允许任何其他格式。这是在UPDATE表达式和SELECT语句的WHERE子句中应该使用的格式。以下是日期格式的示例：

SELECT * FROM table_name WHERE date_col >= '2011-06-02';

当将常量字符串与DATE，TIME，DATETIME或TIMESTAMP使用<, <=, =, >=, >,或BETWEEN运算符进行比较时，MySQL 将字符串转换为内部长整数值。MySQL 这样做是为了实现更快的比较。然而，以下例外情况适用于此转换：

• 比较两列

• 将DATE，TIME，DATETIME或TIMESTAMP列与表达式进行比较

• 使用除列出的方法之外的比较方法，如IN或STRCMP()

在这些例外情况下，通过将对象转换为字符串值并执行字符串比较来进行比较。

NULL 值的问题

NULL值经常让新程序员感到困惑。NULL值在字符串的情况下被错误地解释为空字符串''。这是不正确的。以下是完全不同的语句的示例：

mysql> INSERT INTO my_table (phone) VALUES (NULL); 
mysql> INSERT INTO my_table (phone) VALUES ('');

在上面的例子中，两个语句都将值插入到同一列（phone 列）中。第一个语句插入一个NULL值，而第二个语句插入一个空字符串。第一个值可以被认为是电话号码未知，而第二个值表示该人已知没有电话，因此没有电话号码。

当NULL值与任何其他值进行比较时，它总是评估为假。包含NULL值的表达式总是返回NULL值。以下示例返回一个NULL值：

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

如果 SQL 语句的目的是搜索NULL列值，我们不能使用expression = NULL。以下是一个示例，返回零行，因为expression = NULL始终为假：

mysql> SELECT * FROM my_table WHERE phone = NULL;

要进行NULL值比较，应该使用IS NULL。以下示例演示了IS NULL的使用：

mysql> SELECT * FROM my_table WHERE phone IS NULL; 
mysql> SELECT * FROM my_table WHERE phone = '';

MySQL 8 故障排除方法

在本章的这一部分，我们将专注于 MySQL 8 的故障排除方法。我们为什么需要排除 MySQL 8 的故障？排除故障的原因如下：

• 更快地执行 SQL 查询

• 性能增强

• 资源的有效利用

主要的资源集包括 CPU、磁盘 IO、内存和网络。有两种方法来衡量 MySQL 的性能：

• 在查询集中的方法中，重要的是要衡量查询的执行速度。

• 在资源集中的方法中，查询使用更少的资源是很重要的。

让我们深入了解如何排除 MySQL 问题。

分析查询

EXPLAIN是提供 MySQL 执行 SQL 语句信息的 SQL 语句。EXPLAIN语句与INSERT、UPDATE、REPLACE、DELETE和SELECT语句一起使用。EXPLAIN语句的输出是对SELECT语句中提到或使用的每个表的信息行。输出按照 MySQL 在执行语句时读取这些表的顺序列出。所有连接都使用嵌套循环连接方法解析。在嵌套循环连接方法中，MySQL 从列表中的第一个表中读取一行，然后在列表中的第二个表中找到匹配的行，然后是第三个表，依此类推。一旦处理完列表中的所有表，MySQL 处理所选列的结果，并通过表的列表回溯，直到找到具有更多匹配行的表。它从这个表中读取下一行。这样的过程继续进行。

以下是来自EXPLAIN输出的列：

• id**：这表示查询中的SELECT的顺序号。它也被称为SELECT标识符。当行属于其他行的联合结果时，该值可能为NULL。输出在表列中显示<unionM, N>。这意味着该行是 ID 值M和N的联合。

• select_type：此输出列指示SELECT语句的类型。可能的值列表包括SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY和UNCACHEABLE UNION。

• table：此列指示输出中提到的表的名称。它可以具有诸如<unionM, N>、<derivedN>和<subqueryN>之类的值。

• partitions：这标识查询匹配记录的分区。对于非分区表，该值为NULL。

• 类型：这表示JOIN的类型。

• possible_keys：此输出列指示 MySQL 可能选择用于获取表中行的索引。如果没有匹配的索引，返回值将为NULL。

• key：此输出列指示 MySQL 实际用于从表中获取行的关键索引。

• ref：ref输出列指示用于与键输出列中提到的索引进行比较以选择表行的列或常量。

• rows：行输出列指示为成功执行查询需要检查的行数。

在EXPLAIN中有以下类型的连接：

• system：这意味着表只有一行。这是const连接类型的特殊情况。

• const：这意味着表至少有一行匹配。这一行在查询开始时被读取。由于只找到一行匹配，优化器的其余部分将这一行中的列值视为常量。由于 const 表只被读取一次，所以非常快。当PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时，使用 const。以下是一个使用tbl_name作为 const 表的示例：

 mysql> SELECT * FROM tbl_name WHERE primary_key=1; 
 mysql> SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

• ref：对于前面表的每个行组合，从ref表中读取所有具有匹配索引值的行。如果连接只使用了键的最左前缀，则使用ref。

现实世界的场景

MySQL 查询优化是指提高查询执行时间。例如，当一个查询性能不佳时，意味着查询执行时间比预期的时间长。查询执行时间很重要，但还有其他指标用于衡量性能。本节解释了应该测量什么以及如何尽可能精确地进行测量。

以下问题出现了：为什么我们应该优化查询？如果只需要百分之一秒，真的需要优化吗？是的，除非查询很少执行，否则确实需要优化。我们应该优化最昂贵的查询。

让我们讨论一个实时的例子。在某个应用程序中，我们有一个基于复杂查询生成的报告，花费了太多时间。执行时间是以分钟计算的。为了优化这样一个复杂的查询，我们考虑了以下方法：

1. 使用EXPLAIN分析查询计划：MySQL 提供了两种分析查询性能的方法。一种是EXPLAIN方法，我们已经在本章的前一部分学习过。另一个工具是SHOW STATUS。通常，我们应该优先使用EXPLAIN来理解SELECT查询的查询计划。在报告查询的情况下，我们将一些非SELECT查询转换为SELECT查询。这有助于我们理解非SELECT查询的查询执行计划。例如，通过在UPDATE查询中使用WHERE子句，我们可以将其转换为SELECT查询。我们还可以找到表上缺少的索引。

2. SHOW STATUS：SHOW STATUS语句输出 MySQL 的内部计数器。这些计数器在每次查询执行时由 MySQL 递增。借助这些计数器，我们可以了解服务器的聚合操作类型。它还有助于指示每个单独查询所做的工作。

以下是对 MySQL 服务器变量执行的测量：

• Select_：每次执行SELECT查询时，此计数器会递增。此计数器还可用于确定是否执行了表扫描。

• Key_read：此变量提供了关于键索引使用情况的额外信息。

• Last_query_cost：这个值表示上次执行的查询有多昂贵。

以下是执行查询优化的步骤：

1. 多次执行查询以确保返回相同的结果。

2. 执行SHOW STATUS。保存输出。

3. 执行查询。

4. 执行SHOW STATUS以观察与上一次执行的差异。

5. 如果需要，执行EXPLAIN。

应该分析以下参数以优化查询性能：

• 表索引

• 排序

• 整体性能

• 行级操作

• 磁盘 I/O 操作

总结

在这本书的最后一章中，我们学习了数据库的一个重要方面：解决我们在使用 MySQL 服务器或客户端时可能遇到的错误。我们从理解故障排除开始讨论。我们讨论了初步诊断错误的不同方法。我们了解了常见的 MySQL 错误以及错误消息的含义。我们还学习了如何修复这些错误。我们还了解了 MySQL 服务器和客户端的错误以及这些错误的修复方法。在本章的后半部分，我们学习了 MySQL 故障排除方法，并看了一个真实的案例。对于最后一章来说，这是相当重要的内容，是吧？这本书就到这里了。