优化 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密钥缓存是需要配置的主存区域。

For newly created tables, MyISAM is the default storage engine. In practical use, InnoDB advanced performance features mean that tables with InnoDB storage engines outperform the MyISAM tables for an operations-heavy database.

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

• 磁盘查找：作为磁盘读取操作的一部分，查找一段数据需要磁盘花费时间。对于现代磁盘，查找一段数据的平均时间通常低于 10 毫秒。因此，理论上，它应该是每秒 100 次搜索。随着技术的发展，新磁盘在磁盘时间上有所改进，但很难针对单个表进行优化。要优化寻道时间，必须在多个磁盘上分发数据。
• 磁盘读写：要从磁盘读写数据，需要磁盘处于正确的位置。一个磁盘每秒提供至少 10 到 20 MB 的吞吐量（吞吐量是指每秒读取或写入的数据量）。因此，读写吞吐量比寻道时间更容易优化，因为我们可以从多个磁盘并行读取。
• CPU 周期：我们必须在数据在主存中时对其进行处理，以获得所需的结果。对于大型表，内存量是最常见的限制因素。然而，对于小表，速度通常不是问题。
• 内存带宽：在一种不常见的情况下，当 CPU 需要的数据超过 CPU 高速缓存内存的容量时，主存带宽成为瓶颈。

本节重点介绍 MySQL 8 数据库服务器和客户端的优化，首先优化服务器，然后优化 MySQL 8 客户端实体。本节与数据库管理员更相关，以确保多台服务器的性能和可扩展性。它还将帮助开发人员准备脚本（包括设置数据库）和运行 MySQL 的用户进行开发和测试，以最大限度地提高生产率。

在本节中，我们将学习如何配置存储设备，以便将更多更快的存储硬件用于数据库服务器。一个主要的性能瓶颈是磁盘查找（在磁盘上找到读取或写入内容的正确位置）。当数据量增长到足以使缓存变得不可能时，磁盘种子的问题就会变得明显。我们需要至少一个磁盘搜索操作来读取数据，并且需要几个磁盘搜索操作来写入大型数据库中的数据，在这些数据库中，数据访问或多或少是随机进行的。我们应该使用适当的磁盘来调节或最小化磁盘寻道时间。

为了解决磁盘寻道性能问题，可以增加可用磁盘轴的数量、将文件符号链接到不同的磁盘或剥离磁盘。详情如下:

• 使用符号链接：使用符号链接时，我们可以为索引和数据文件创建 Unix 符号链接。对于MyISAM表，符号链接从数据目录中的默认位置指向另一个磁盘。这些链接也可以分条。这样可以缩短查找和读取时间。假设磁盘不会同时用于其他目的。InnoDB表不支持符号链接。但是，我们可以将InnoDB数据和日志文件放在不同的物理磁盘上。
• 条带化：在条带化中，我们有很多磁盘。我们将第一个块放在第一个磁盘上，第二个块放在第二个磁盘上，依此类推。（N%磁盘数）磁盘上的N块。如果条带大小完全对齐，则正常数据大小将小于条带大小。这将有助于提高性能。条带化取决于条带大小和操作系统。在理想情况下，我们将使用不同的条带大小对应用程序进行基准测试。条带化时的速度差异取决于我们使用的参数，如条带大小。性能的差异还取决于磁盘的数量。我们必须选择是要优化随机访问还是顺序访问。为了获得可靠性，我们可能会决定设置条带化和镜像（RAID 0+1）。RAID代表独立驱动器冗余阵列。这种方法需要 2 个N驱动器来保存N个数据驱动器。使用良好的卷管理软件，我们可以有效地管理此设置。
• 还有另一种方法。根据数据类型的重要性，我们可能会改变 RAID 级别。例如，我们可以将真正重要的数据（如主机信息和日志）存储在 RAID 0+1 或 RAID N 磁盘上，而将半重要的数据存储在 RAID 0 磁盘上。对于 RAID，奇偶校验位用于确保存储在每个驱动器上的数据的完整性。因此，如果要执行的写操作太多，RAID N 就会成为一个问题。在这种情况下，更新奇偶校验位所需的时间很长。
• 如果维护文件上次访问的时间不重要，我们可以使用-o noatime选项挂载文件系统。此选项跳过文件系统上的更新，从而缩短磁盘查找时间。我们还可以使文件系统异步更新。根据文件系统是否支持，我们可以设置-o async选项。

使用网络文件系统（NFS时，可能会出现各种问题，具体取决于操作系统和 NFS 版本。详情如下:

• 数据不一致是 NFS 系统的一个问题。这可能是由于收到的消息顺序错误或网络流量丢失造成的。我们可以使用带有hard和intr装载选项的 TCP 来避免这些问题。
• 如果将 MySQL 数据和日志文件放在 NFS 驱动器上，可能会被锁定，无法使用。如果多个 MySQL 实例访问同一个数据目录，可能会导致锁定问题。MySQL 的不当关闭或断电是文件系统锁定问题的其他原因。最新版本的 NFS 支持建议和基于租约的锁定，这有助于解决锁定问题。但是，不建议在多个 MySQL 实例之间共享数据目录。
• 必须了解最大文件大小限制，以避免任何问题。对于 NFS 2，客户端只能访问较低的 2GB 文件。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表，索引文件打开一次。对于访问表的每个并发线程，数据文件打开一次。为每个并发线程分配一个表结构、每列的列结构和一个 3XN大小的缓冲区。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字节。
• 所有线程共享相同的基本内存。
• 所有 join 子句都在一个过程中执行。大多数连接都可以在没有临时表的情况下执行。临时表是基于内存的哈希表。包含BLOB数据的临时表和行长较大的表存储在磁盘上。
• 为每个请求分配一个读取缓冲区，该缓冲区对表执行顺序扫描。读取缓冲区的大小由read_buffer_size系统变量决定。
• 以任意方式读取行时会分配随机读取缓冲区，以避免磁盘查找。缓冲区大小由read_rnd_buffer_size系统变量决定。
• 一旦不再需要线程，分配给该线程的内存就会被释放。释放的内存将返回到系统，除非将线程放入线程缓存中。
• 当执行FLUSH TABLES或mysqladminflush table 命令时，MySQL 立即关闭所有未使用的表。当当前线程执行完成时，它将所有正在使用的表标记为关闭。这将释放使用中的内存。FLUSH TABLES仅在所有表关闭后返回。

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

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

If memory instruments are enabled on startup, it ensures memory allocations on startup are counted.

此查询将返回数百个内存工具。我们可以通过指定代码区域来缩小范围。以下是一个将结果限制在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存储引擎固有地支持多个并发插入，以减少读写器之间对表的争用。它允许{ ToR.T1}表在数据文件的中间插入行。如果表在数据文件的中间没有任何空闲块，则在文件的末尾插入行。这使得 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)

从前面的示例中，我们可以发现计算1000000次的1+1所用的时间是0.15 seconds。

有时，我们可能需要弄清楚 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不提供关于后台线程的信息。

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

| 信息 | 详情 | | 身份证件 | 与线程关联的客户端的客户端连接标识符。 | | 用户、主机 | 与线程关联的帐户。 | | 分贝 | 线程或NULL的默认数据库。 | | 命令，状态 | 它指示线程当前正在执行的操作。 | | 时间 | 它指示线程处于当前状态的时间。 | | 信息 | 它包含线程正在执行的语句的信息。 |

以下是与常规查询处理关联的线程状态值：

• After create：线程创建表时发生，包括内部临时表
• Analyzing：线程在计算MyISAM密钥分配时发生
• Checking permissions：检查服务器是否具有执行 SQL 语句所需的权限时发生
• Checking table：线程执行表检查操作时发生
• Cleaning up：线程处理完一条命令并释放内存时发生
• Closing tables：当线程将更改的表数据刷新到磁盘并关闭已使用的表时发生
• Altering table：服务器处理ALTER TABLE语句时发生
• Creating index：线程处理MyISAM表的ALTER TABLE ... ENABLE KEYS时发生
• Creating table：线程创建表时发生
• end：发生在ALTER TABLE、CREATE VIEW、DELETE、INSERT、SELECT或UPDATE语句清理结束之前
• executing：线程开始执行语句时发生
• 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倍字符集中最大字节长度的范围内分配可变的存储量。

如果我们没有可变长度的列，例如VARCHAR、TEXT或BLOB（对于MyISAM表），则使用固定大小的行格式。

表的主索引必须尽可能短。这样可以轻松识别每一行。它也很有效。对于InnoDB表，主键列在每个辅助索引项中重复。如果我们有一个较短的主键，那么在有许多次索引的情况下，它可以节省空间。

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

如果一个长字符串列的前几个字符应该有一个唯一的前缀，那么建议只对前缀进行索引，使用 MySQL 对列最左边部分的索引支持。首选较短的索引，这不仅是因为它们需要较少的空间，而且因为它们在索引缓存中提供了更多的命中率，并且需要较少的磁盘搜索。

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

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

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

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

在应用程序中，如果首选的是速度，而不是磁盘空间或使用多份数据副本的维护成本，则建议复制信息或创建摘要表以提高速度。一个示例场景可能是一个商业智能系统，其中数据是从大型表中分析的。在这种情况下，没有严格遵守规范化规则。

以下是优化数值数据类型的指导原则：

• 数字列必须优先于字符串列，以存储唯一 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 8 服务器在处理 SQL 语句时创建临时内部表。以下是服务器创建临时表的条件：

• 工会声明
• 使用TEMPTABLE算法、UNION或aggregation的视图
• 派生表
• 通用表表达式
• 为子查询或半联接具体化创建的表
• 包含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查询中，union 不在顶级查询块中

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

SQL 语句用于执行任何数据库应用程序的核心逻辑。这些语句是通过解释器直接发布还是通过 API 在幕后提交并不重要。本节概述了提高在数据库中读写数据的 SQL 操作性能的指导原则。

SELECT语句执行数据库中的所有查找操作。考虑到SELECT语句的频率，以最高优先级调整这些语句变得非常重要。调优技术必须应用于DELETE语句中的CREATE TABLE...AS SELECT、INSERT INTO...SELECT和WHERE子句等结构。

以下是优化查询的主要注意事项：

• 为了优化SELECT ... WHERE查询，首先要检查是否可以添加索引。我们应该在SELECT查询的WHERE子句中使用的列上添加索引。这将加快评估、筛选和检索结果的速度。策略应该是构造一小组索引，以加速应用程序中使用的许多相关查询。它还避免了浪费磁盘空间。
• 索引对于使用联接和外键引用不同表的查询非常重要。EXPLAIN语句可用于确定在SELECT语句执行中使用了哪些索引。
• 下一步应该是隔离和优化查询的各个部分；例如，函数调用占用的时间过长。根据查询的结构，可以对表中的每一行或结果集中的每一行执行函数调用。
• 查询中的完整表扫描次数必须最小化，特别是对于大型表。
• ANALYZE TABLE语句应定期使用，以使表统计信息保持最新。优化器提供构建高效查询执行计划所需的信息。
• 如果基本准则不能解决性能问题，则应通过阅读EXPLAIN计划并调整索引、WHERE子句、join 子句等来调查查询的内部细节。
• 应避免以使查询难以理解的方式转换查询，尤其是当优化器自动执行某些相同的转换时
• 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的minimum(MIN())或maximum(MAX())值。预处理器检查是否在所有关键部件上使用WHERE key_part_N = constant对其进行优化。
• 还可以优化查询以检索值，而无需查询数据行。（覆盖索引是为查询提供所有结果的索引。）如果查询仅使用某个索引中包含的表中的列，则将从索引树中获取选定的值。这将提高检索值的速度。

MySQL 优化器根据表、列和索引的详细信息以及WHERE子句中的条件，考虑使用优化技术来高效执行查询中涉及的查找。也可以在不读取大型表上的所有行的情况下执行查询。也可以在不比较每一行组合的情况下执行 SQL 联接。查询执行计划是 MySQL 优化器选择执行最高效查询的一组操作。它也被称为EXPLAIN计划。作为管理员，目标是识别查询执行计划中指示查询是否优化的方面。

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

• EXPLAIN语句与SELECT、DELETE、INSERT、UPDATE和REPLACE语句一起使用。
• 当EXPLAIN与 SQL 语句一起使用时，MySQL 显示来自 MySQL 优化器的有关查询执行计划的信息。这意味着 MySQL 解释了执行语句的过程。它包括有关如何联接表以及联接顺序的信息。
• IfEXPLAIN显示指定连接中语句执行的执行计划，如果它与FOR CONNECTION连接 id 而不是可解释的 SQL 语句一起使用。
• 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 | 补充资料 |

Reference: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-output-column-table

数据库表是任何数据库最基本的构建块。在本章的这一节中，我们将重点关注优化表。本节提供了通过表优化技术改进性能的详细指南。

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

以下是优化InnoDB表格的指南：

• 当数据达到稳定大小或表增加了数十兆字节时，应考虑使用OPTIMIZE TABLE语句重新组织表并压缩浪费的空间。它需要更少的磁盘 I/O 来对重新组织的表执行完整的表扫描。
• OPTIMIZE TABLE语句复制表中的数据并重建索引。这是有益的，因为改进了索引中数据的打包，并减少了磁盘上表空间中的碎片。根据每个表中的数据，好处可能会有所不同。值得注意的是，在某些情况下，收益是显著的，而在其他情况下，收益并不显著。增益也可能随着时间的推移而降低，直到下一个表优化完成。如果表很大或正在重建的索引不适合缓冲池，则操作可能会很慢。
• InnoDB表中的长主键会浪费大量磁盘空间。这应该避免。
• 在InnoDB表中，应优先选择VARCHAR数据类型而不是CHAR数据类型来存储可变长度字符串，或者优先选择预期包含NULL值的列。CHAR(N)列始终占用N个字符来存储数据，即使值为NULL。较小的表更适合放入缓冲池并减少磁盘 I/O。
• 考虑对于大表，或包含大量重复文本或数值数据的表，使用一个{ T0}0}行格式。

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

以下是加速查询MyISAM表的指导原则：

• 避免对频繁更新的MyISAM表执行复杂的SELECT查询。它可以防止由于写入程序和读取器之间的争用而发生的表锁定问题。
• MyISAM存储引擎支持并发插入。如果表数据文件在中间没有空闲块，那么我们可以在其他线程从表中读取的同时，在其中添加新的行。考虑使用表以避免删除行，如果重要的是能够执行并发读写操作。另一个选项是在删除行后执行OPTIMIZE TABLE对表进行碎片整理。可以通过设置concurrent_insert系统变量来控制或修改此行为。
• 避免频繁更改MyISAM表的所有可变长度列。如果表甚至包含单个可变长度列，则使用动态行格式。
• myisamchk --sort-index --sort-records=1命令可用于对索引进行排序。它还根据索引对数据进行排序。如果我们有唯一的索引，这会使查询运行得更快，我们希望根据索引按顺序读取所有行。当我们第一次用这种方式对一张大桌子进行排序时，需要很长时间。
• 如果我们通常按照expression1、expression2等顺序检索行，则使用ALTER TABLE ... ORDER BY expression1, expression2,..等。如果在对表进行大量更改后使用此选项，将提供更高的性能。

MySQLMEMORY表应考虑仅用于经常访问且只读且很少更新的非关键数据。应用程序应根据实际工作负载下的等效InnoDB或MyISAM表进行基准测试，以确认额外的性能值得冒数据丢失的风险。

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

本节重点介绍如何使用缓冲和缓存技术来提高性能。

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

以下是使用InnoDB缓冲池提高性能的一般准则：

• 在理想情况下，缓冲池的大小应该设置得足够大，同时为服务器上的其他进程留出足够的内存，以便在不进行过度分页的情况下运行。缓冲池越大，InnoDB功能就越多，比如内存中的数据库。在这种情况下，它从磁盘读取数据一次，然后在后续读取中访问内存中的数据。
• 我们可以考虑将缓冲池分割成具有大内存大小的 64 位系统的许多部分。这可以最大限度地减少并发操作期间的内存争用。
• 频繁访问的数据应保存在内存中。
• 可以控制InnoDB何时以及如何执行预读请求，以将页面异步预取到缓冲池中。InnoDB使用两种预读算法来提高 I/O 性能。线性预读根据缓冲池中按顺序访问的页面预测可能很快需要哪些页面。随机预读根据缓冲池中的页面预测何时可能需要页面，而不考虑页面的读取顺序。innodb_read_ahead_threshold配置参数控制线性预读的灵敏度。我们可以通过将innodb_random_read_ahead设置为ON来启用随机读取 a 磁头。
• innodb_buffer_pool_read_ahead确定读入InnoDB缓冲池的页数。innodb_buffer_pool_read_ahead_evicted确定预读后台线程读入缓冲池的页数，该线程随后被逐出而未被查询访问。innodb_buffer_pool_read_ahead_rnd确定InnoDB发起的随机读取 AHEAD 的数量。

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 功能。它还将解释调用这些插件的服务。本章将讨论添加新函数、调试和移植方法。这将是数据库管理员的重要一章。