MySQL8 查询性能调优教程（十）
二十六、复制
MySQL 这些年来如此受欢迎的一个特性是支持复制，这允许您拥有一个 MySQL 实例，该实例自动从其来源接收更新并应用它们。通过快速事务和低延迟网络，复制可以接近实时，但请注意，由于除了 NDB 集群之外，MySQL 中没有同步复制，因此仍然存在潜在的巨大延迟。数据库管理员的一项经常性任务是提高复制的性能。多年来，MySQL 复制有了许多改进，包括一些可以帮助您提高复制性能的改进。
Note
本章重点介绍传统的异步复制。MySQL 8 还支持组复制及其衍生的 InnoDB 集群。深入研究组复制的细节超出了本书的范围；然而，讨论仍然普遍适用。关于组复制的详细信息，推荐查尔斯·贝尔(Apress) ( www.apress.com/gp/book/9781484238844 )的《?? 介绍 InnoDB 集群》一书，以及最新更新的 MySQL 参考手册( https://dev.mysql.com/doc/refman/en/group-replication.html )。
本章将首先提供复制的高级概述，目的是介绍将用于复制监控部分的术语和测试设置。本章的另一半讨论了如何提高连接和应用线程的性能，以及如何使用复制将工作转移给副本。
复制概述
在开始提高复制性能之前，讨论一下复制的工作原理是很重要的。这将有助于就术语达成一致，并为本章剩余部分的讨论提供参考点。
Note
传统上，术语主和从被用来描述 MySQL 复制的源和目标。最近，术语已经转向使用单词源和复制品。同样，在副本上，用于处理复制事件的两种线程类型传统上被称为 I/O 线程和 SQL 线程，而当前的术语是连接线程和应用线程。本书将尽最大可能使用新术语；然而，旧的术语在某些上下文中仍然存在。
复制的工作方式是记录在复制源上所做的更改，然后将这些更改发送到副本，在副本中，连接线程存储数据，一个或多个应用线程应用这些数据。图 26-1 显示了复制的简化概述，省略了与存储引擎和实施细节相关的所有内容。
图 26-1
复制概述
当事务提交其更改时，这些更改将被写入 InnoDB 特定文件(重做日志和数据文件)和二进制日志。二进制日志由一系列文件和一个索引文件组成，该索引文件列出了二进制日志文件。一旦事件被写入二进制日志文件，它们就被发送到副本服务器。可能有多个副本，在这种情况下，事件会发送到所有副本。
在副本上，连接线程接收事件并将它们写入中继日志。中继日志的工作方式与二进制日志相同，只是它被用作临时存储，直到应用线程可以应用事件。可以有一个或多个施放器螺纹。也可能是副本从多个源复制(称为多源复制)，在这种情况下，每个复制通道有一组一个连接线程和一个或多个应用线程。(也就是说，最常见的是每个副本一个源。)可选地，副本将更改写入其自己的二进制日志，这使其能够成为复制链更下游的副本的源。在这种情况下，通常称之为中继实例。图 26-2 显示了一个设置的例子，一个副本从两个源接收更新，其中一个是中继实例。
图 26-2
具有两个复制流的复制拓扑
这里源 1 复制到中继实例，后者又复制到副本实例。源 2 也复制到副本实例。每个通道都有一个名称来区分它们，在多源复制中，每个通道都必须有一个唯一的名称。默认通道名称是一个空字符串。在讨论监控时，将使用如图所示的复制设置。
监控
当您遇到复制性能问题时，第一步是确定延迟是在前面部分描述的一系列步骤中引入的。如果您已经在 MySQL 的早期版本中使用了复制，您可以跳转到SHOW SLAVE STATUS命令来检查复制的健康状况；然而，在 MySQL 8 中，这是最后一个需要检查的监控信息源。
在 MySQL 8 中，复制监控信息的主要来源是性能模式，它包含几个表，描述副本上每个复制步骤的复制配置和状态。性能模式表的一些优点如下:
-
状态表包括关于复制延迟的更详细的信息，其形式为时间戳，对于复制过程中的每个步骤具有微秒分辨率，并且具有来自原始和即时源的时间戳。
-
您可以使用
SELECT语句查询这些表。这允许您查询您最感兴趣的信息，并且您可以操纵数据。当您有多个复制通道时，这是一个特别的优势，在这种情况下，当在控制台中检查时，SHOW SLAVE STATUS的输出会很快变得难以使用，因为输出会滚动到屏幕之外。 -
数据被分成逻辑组，每组一个表。配置和应用流程有单独的表，配置和状态也有单独的表。
Note
SHOW SLAVE STATUS中的Seconds_Behind_Master列传统上用于测量复制延迟。它实际上显示了自事务在原始源上开始以来已经过去了多长时间。这意味着，只有当所有事务都非常快并且没有中继实例时，它才真正起作用。即使这样，它也不提供延迟原因的任何信息。如果您仍然使用Seconds_Behind_Master来监控复制延迟，那么建议您开始切换到性能模式表。
当您第一次开始使用 Performance Schema 复制表时，很难描绘出表之间的关系以及它们与复制流的关系。图 26-3 显示了单个复制通道的复制流程，并添加了与其包含的信息相对应的复制表。图 26-3 中的表格也可用于组复制设置，在这种情况下，group_replication_applier通道用于节点在线时的事务处理，而group_replication_recovery通道用于恢复期间。
图 26-3
复制过程及其监控表
事件从直接来源到达图的顶部，并由具有两个表replication_connection_configuration和replication_connection_status的连接线程处理。连接线程将事件写入中继日志，应用在应用复制过滤器时从中继日志中读取事件。复制过滤器可以在replication_applier_filters和replication_applier_global_filters表中找到。总体敷贴器配置和状态可在replication_applier_configuration和replication_applier_status表中找到。
在并行复制的情况下(也称为多线程从属)，协调器处理事务，并使它们对工作器可用。可以通过replication_applier_status_by_coordinator表监控协调器。如果副本使用单线程复制，则跳过协调器步骤。
最后一步是应用工人。在并行复制的情况下，每个复制通道有slave_parallel_workers个线程，每个线程在replication_applier_status_by_worker表中都有一行，其中包含其状态。
本节的其余部分将介绍连接和应用的性能模式复制表，以及日志状态和组复制表。
连接表
当复制事件到达复制副本时，第一步是将它们写入中继日志。这是由连接线程处理的。
有两个性能模式表提供与连接相关的信息:
-
replication_connection_configuration: 每个复制通道的配置。 -
replication_connection_status: 复制通道的状态。这包括显示最后一个和当前队列事务最初提交时间、在直接源实例上提交时间以及写入中继日志时间的时间戳。每个通道有一行。
复制连接表包括与到直接上游源的连接相关的信息以及在原始源上提交最新接收的事件时的时间戳。在简单的复制设置中，直接源和原始源是相同的，但是在链式复制中，这两者是不同的。清单 26-1 显示了上一节讨论的复制设置中relay通道的两个连接表的内容示例。输出已被重新格式化，以提高本书的可读性。包含用于source2复制通道的行的原始格式化输出包含在文件listing_26_1.txt中。
mysql> SELECT *
FROM performance_schema.replication_connection_configuration
WHERE CHANNEL_NAME = 'relay'\G
*************************** 1\. row ***************************
CHANNEL_NAME: relay
HOST: 127.0.0.1
PORT: 3308
USER: root
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: YES
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30
TLS_VERSION:
PUBLIC_KEY_PATH:
GET_PUBLIC_KEY: NO
NETWORK_NAMESPACE:
COMPRESSION_ALGORITHM: uncompressed
ZSTD_COMPRESSION_LEVEL: 3
1 row in set (0.0006 sec)
mysql> SELECT *
FROM performance_schema.replication_connection_status
WHERE CHANNEL_NAME = 'relay'\G
*************************** 1\. row ***************************
CHANNEL_NAME: relay
GROUP_NAME:
SOURCE_UUID: cfa645e7-b691-11e9-a051-ace2d35785be
THREAD_ID: 44
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 26
LAST_HEARTBEAT_TIMESTAMP: 2019-08-11 10:26:16.076997
RECEIVED_TRANSACTION_SET: 4d22b3e5-a54f-11e9-8bdb-ace2d35785be:23-44
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
LAST_QUEUED_TRANSACTION: 4d22b3e5-a54f-11e9-8bdb-ace2d35785be:44
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-08-11 10:27:09.483703
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-08-11 10:27:10.158297
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2019-08-11 10:27:10.296164
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2019-08-11 10:27:10.299833
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.0006 sec)
Listing 26-1The replication connection tables
配置表很大程度上对应于您在使用CHANGE MASTER TO语句设置复制时可以给出的选项，并且数据是静态的，除非您显式地更改配置。状态表主要包含随着事件的处理而快速变化的易变数据。
状态表中的时间戳特别重要。有两组，第一组显示最后排队事件的时间戳，第二组显示当前排队事件的时间戳。事件正在排队意味着它正在被写入中继日志。例如，考虑最后一个排队事件的时间戳:
-
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 事件在原始源上提交的时间(源 1 )。 -
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 事件在即时源上发生的时间(中继)。 -
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 此实例开始对事件进行排队的时间，即收到事件并且连接线程开始将事件写入中继日志的时间。 -
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 连接线程完成将事件写入中继日志的时间。
时间戳的分辨率为微秒级，因此它允许您详细了解事件从原始源到中继日志的时间。零时间戳('0000-00-00 00:00:00')意味着没有要返回的数据；例如，当连接线程完全最新时，这可能发生在当前排队的时间戳上。应用表提供了关于事件在副本中经历的更多细节。
应用表
应用线程更复杂，因为它们都处理事件过滤和应用事件，并且支持并行应用。
在撰写本文时，存在以下包含应用线程信息的性能模式表:
-
replication_applier_configuration: 此表显示了每个复制通道的应用线程的配置。目前唯一的设置是已配置的复制延迟。每个通道有一行。 -
replication_applier_filters: 每个复制通道的复制过滤器。这些信息包括过滤器的配置位置和激活时间。 -
replication_applier_global_filters: 适用于所有复制通道的复制过滤器。这些信息包括过滤器的配置位置和激活时间。 -
replication_applier_status: 申请人的整体状态，包括服务状态、剩余延迟(当配置了所需的延迟时)、事务的重试次数。每个通道有一行。 -
replication_applier_status_by_coordinator: 使用并行复制时，协调器线程看到的应用状态。最后处理的事务和当前处理的事务都有时间戳。每个通道有一行。对于单线程复制，此表为空。 -
replication_applier_status_by_worker: 每个工人的申请人状态。有上一次应用的事务和当前正在应用的事务的时间戳。当配置并行复制时，每个通道有一个工作线程(工作线程的数量用slave_parallel_workers配置)行。对于单线程复制，每个通道有一行。
在高层次上，应用表遵循与连接表相同的模式，增加了过滤器配置表和对并行应用的支持。清单 26-2 显示了relay复制通道的replication_applier_status_by_worker表的内容示例。为了提高可读性，输出已被重新格式化。输出也可以在本书的 GitHub 库的文件listing_26_2.txt中找到。
mysql> SELECT *
FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = 'relay'\G
*************************** 1\. row ***************************
CHANNEL_NAME: relay
WORKER_ID: 1
THREAD_ID: 54
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION:
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION:
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2\. row ***************************
CHANNEL_NAME: relay
WORKER_ID: 2
THREAD_ID: 55
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION: 4d22b3e5-a54f-11e9-8bdb-ace2d35785be:213
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-08-11 11:29:36.1076
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-08-11 11:29:44.822024
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2019-08-11 11:29:51.910259
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2019-08-11 11:29:52.403051
APPLYING_TRANSACTION: 4d22b3e5-a54f-11e9-8bdb-ace2d35785be:214
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-08-11 11:29:43.092063
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-08-11 11:29:52.685928
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2019-08-11 11:29:53.141687
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
Listing 26-2The replication_applier_status_by_worker table
时间戳遵循的模式与您之前看到的上次处理的事务和当前事务的信息相同。注意，对于第一行，所有时间戳都是零，这表明应用不能利用并行复制。
对于第二行中最后应用的全局事务标识符为 4d 22 B3 e 5-a54f-11e 9-8 BDB-ace2d 35785 be:213 的事务，可以看到，该事务于 11:29:36.1076 在原始源上提交，于 11:29:44.822024 在即时源上提交，于 11:29:51.910259 开始在该实例上执行，并于 11:29 完成执行这表明每个实例增加了大约 8 秒的延迟，但是事务本身只花了半秒钟就执行了。您可以得出结论，复制延迟不是由应用单个大型事务引起的，而是由于中继和复制副本实例处理事务的速度不如原始源，延迟是由早期长时间运行的事件引起的，复制尚未跟上，或者延迟是由复制链的其他部分引起的。
日志状态
与复制相关的一个表是log_status表，它提供关于二进制日志、中继日志和 InnoDB 重做日志的信息，使用日志锁返回对应于同一时间点的数据。该表是在考虑备份的情况下引入的，因此查询该表需要有BACKUP_ADMIN特权。清单 26-3 展示了一个使用JSON_PRETTY()函数的示例输出，以便于阅读作为 JSON 文档返回的信息。
mysql> SELECT SERVER_UUID,
JSON_PRETTY(LOCAL) AS LOCAL,
JSON_PRETTY(REPLICATION) AS REPLICATION,
JSON_PRETTY(STORAGE_ENGINES) AS STORAGE_ENGINES
FROM performance_schema.log_status\G
*************************** 1\. row ***************************
SERVER_UUID: 4d46199b-bbc9-11e9-8780-ace2d35785be
LOCAL: {
"gtid_executed": "4d22b3e5-a54f-11e9-8bdb-ace2d35785be:1-380,\ncbffdc28-bbc8-11e9-9aac-ace2d35785be:1-190",
"binary_log_file": "binlog.000003",
"binary_log_position": 199154947
}
REPLICATION: {
"channels": [
{
"channel_name": "relay",
"relay_log_file": "relay-bin-relay.000006",
"relay_log_position": 66383736
},
{
"channel_name": "source2",
"relay_log_file": "relay-bin-source2.000009",
"relay_log_position": 447
}
]
}
STORAGE_ENGINES: {
"InnoDB": {
"LSN": 15688833970,
"LSN_checkpoint": 15688833970
}
}
1 row in set (0.0005 sec)
Listing 26-3The log_status table
LOCAL列包含关于已执行的全局事务标识符和二进制日志文件以及在该实例上的位置的信息。REPLICATION列显示与每个通道一个对象的复制过程相关的中继日志数据。STORAGE_ENGINES列包含关于 InnoDB 日志序列号的信息。
组复制表
如果您使用组复制，那么有两个额外的表可以用来监控复制。一个表包含组成员的高级信息，另一个表包含成员的各种统计信息。
这两个表是
-
replication_group_members: 成员的高层概述。每个成员对应一行，数据包括当前状态以及它是主要成员还是次要成员。 -
replication_group_member_stats: 较低级别的统计数据，如队列中的事务数量、所有成员上提交的事务、本地或远程发起的事务数量等等。
replication_group_members表对于验证成员的状态非常有用。replication_group_member_stats表可用于查看每个节点如何看待已经完成的工作，以及是否存在高比率的冲突和回滚。这两个表都包含集群中所有节点的信息。
现在您已经知道如何监控复制，您可以开始优化连接和应用线程了。
这种联系
连接线程处理到直接复制源的出站连接，接收复制事件，并将事件保存到中继日志。这意味着优化连接过程围绕着复制事件、网络、维护关于已收到哪些事件的信息以及写入中继日志。
复制事件
当使用基于行的复制时(默认和推荐)，事件包括有关已更改的行和新值(映像之前和之后)的信息。默认情况下，更新和删除事件包括“完成之前”图像。这使得副本可以应用事件，即使源和副本具有不同顺序的列或者具有不同的主键定义。但是，它确实会使二进制日志变得更大，从而也使中继日志变得更大，这意味着更多的网络流量、内存使用和磁盘 I/O。
如果您不需要完整的前图像，您可以将binlog_row_image选项配置为minimal或noblob。值minimal表示只有识别行所需的列包含在之前的图像中，之后的图像只包含被事件改变的列。使用noblob时，除了blob和text列之外的所有列都包含在之前的图像中，而blob和text列只有在它们的值改变时才包含在之后的图像中。使用minimal对性能来说是最佳的，但是要确保在生产系统上做出改变之前进行彻底的测试。
Caution
在生产中进行配置更改之前，确保您已经验证了您的应用可以与binlog_row_image = minimal一起工作。如果应用不使用该设置，将导致复制副本上的复制失败。
还可以在会话范围内设置binlog_row_image选项，因此可以根据需要更改选项。
网络
MySQL 中用于复制的网络的主要调优选项是使用的接口和是否启用压缩。如果网络过载，它会很快使复制落后。避免这种情况的一种方法是为复制流量使用专用的网络接口和路由。另一种选择是启用压缩，这可以减少传输的数据量，但代价是增加 CPU 负载。这两种解决方案都是使用CHANGE MASTER TO命令实现的。
当您定义如何连接到复制源时，您可以使用MASTER_BIND选项来指定用于连接的接口。例如，如果您想使用副本服务器上 IP 地址为 192.0.2.102 的接口从 192.0.2.101 的源进行复制，那么您可以使用MASTER_BIND='192.0.2.102':
CHANGE MASTER TO MASTER_BIND='192.0.2.102',
MASTER_HOST='192.0.2.101',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_SSL=1;
根据需要替换地址和其他信息。
Caution
不启用 SSL 来提高网络性能可能很诱人。如果您这样做，包括认证信息和您的数据在内的通信将在不加密的情况下传输，任何访问网络的人都可以读取这些数据。因此，对于任何处理生产数据的设置来说，所有通信都是安全的是非常重要的——对于复制来说，这意味着启用 SSL。
MySQL 8.0.18 和更高版本使用MASTER_COMPRESSION_ALGORITHMS选项启用压缩，该选项采用一组允许的算法。支持的算法有
-
uncompressed: 禁用压缩。这是默认设置。 -
zlib: 使用 zlib 压缩算法。 -
zstd: 使用 ztd 1.3 版压缩算法。
如果包含了zstd算法，那么可以使用MASTER_ZSTD_COMPRESSION_LEVEL选项来指定压缩级别。支持的级别为 1–22(包括 1 和 22)，默认值为 3。将复制连接配置为使用压缩级别为 5 的zlib或zstd算法的示例如下
CHANGE MASTER TO MASTER_COMPRESSION_ALGORITHMS='zlib,zstd',
MASTER_ZSTD_COMPRESSION_LEVEL=5;
在 MySQL 8.0.18 之前，您使用slave_compressed_protocol选项指定是否使用压缩。如果源和副本都支持该算法，将选项设置为1或ON会使复制连接使用zlib压缩。
Tip
如果您在 MySQL 8.0.18 或更高版本中启用了slave_compressed_protocol选项，它将优先于MASTER_COMPRESSION_ALGORITHMS。建议禁用slave_compressed_protocol并使用CHANGE MASTER TO命令来配置压缩，因为它允许您使用zstd算法，并使压缩配置在replication_connection_configuration性能模式表中可用。
维护来源信息
副本需要跟踪它从源接收到的信息。这是通过mysql.slave_master_info表完成的。也可以将信息存储在文件中，但从 8.0.18 开始，这种做法已被否决，并且不被鼓励。使用文件还会降低副本从崩溃中恢复的弹性。
关于维护这些信息的性能，那么重要的选项是sync_master_info。这指定了信息更新的频率，默认值为每 10000 个事件更新一次。您可能认为与复制的源端的sync_binlog类似，在每个事件之后同步数据是很重要的；然而，事实并非如此。
Caution
没有必要设置sync_master_info = 1，这样做是复制延迟的一个常见来源。
不需要非常频繁地更新信息的原因是，通过丢弃中继日志并从应用到达的点开始获取所有内容，可以从信息丢失中恢复。因此，默认值 10000 是好的，很少有理由更改它。
Tip
复制可以从崩溃中恢复的确切规则非常复杂，并且会随着新改进的加入而不断变化。您可以在 https://dev.mysql.com/doc/refman/en/replication-solutions-unexpected-slave-halt.html 中查看最新信息。
编写中继日志
中继日志是接收复制事件的连接和应用处理它们之间的复制事件的中间存储。主要有两个因素影响中继日志的写入速度:磁盘性能和中继日志同步到磁盘的频率。
您需要确保写入中继日志的磁盘有足够的 I/O 容量来支持读写活动。一种选择是将中继日志存储在单独的存储器上，以便其他活动不会干扰中继日志的写入和读取。
中继日志同步到磁盘的频率由sync_relay_log选项控制，该选项相当于sync_binlog的中继日志。默认设置是每 10000 个事件同步一次。除非对并行应用线程使用基于位置的复制(GTID 禁用或MASTER_AUTO_POSITION=0)，否则没有理由更改sync_relay_log的值，因为可以恢复中继日志。对于基于位置的并行复制，您将需要sync_relay_log = 1，除非在操作系统崩溃的情况下重建副本是可以接受的。
这意味着从性能角度来看，建议启用全局事务标识符，并在执行CHANGE MASTER TO时设置MASTER_AUTO_POSITION=1。否则，保留与主信息和中继日志相关的其他设置的默认值。
该施放器
应用是复制滞后的最常见原因。主要问题是，在源上所做的更改通常是高度并行工作负载的结果。相比之下，默认情况下，applier 是单线程的，因此单线程必须跟上数据源上潜在的数十或数百个并发查询。这意味着对抗由应用引起的复制延迟的主要工具是启用并行复制。此外，还将讨论主键的重要性、放宽数据安全设置的可能性以及复制过滤器的使用。
Note
当您为中继日志存储库使用一个表并为mysql.slave_relay_log_info表使用 InnoDB 时，更改sync_relay_log_info设置没有任何效果(两者都是默认的和推荐的)。在这种情况下，该设置实际上被忽略，并且在每次事务处理后更新信息。
并行应用
将应用配置为使用几个线程来并行应用事件是提高复制性能的最有效的方法。然而，这并不像将slave_parallel_workers选项设置为大于 1 的值那么简单。在源和复制副本上还有其他选项需要考虑。
表 26-1 总结了影响并行复制的配置选项，包括该选项应设置在源上还是副本上。
表 26-1
与并行复制相关的配置选项
|选项名称和配置位置
|
描述
|
| --- | --- |
| binlog_transaction_dependency_tracking在源上设置 | 要在二进制日志中包含哪些关于事务间依赖关系的信息。 |
| binlog_transaction_dependency_history_size在源上设置 | 上次更新行时信息保留的时间。 |
| transaction_write_set_extraction在源上设置 | 如何提取写集合信息？ |
| binlog_group_commit_sync_delay在源上设置 | 等待更多事务在组提交功能中组合在一起的延迟。 |
| slave_parallel_workers在副本上设置 | 为每个通道创建多少个应用线程 |
| slave_parallel_type在副本上设置 | 是通过数据库还是逻辑时钟实现并行化。 |
| slave_pending_jobs_size_max在副本上设置 | 有多少内存可用于保存尚未应用的事件。 |
| slave_preserve_commit_order在副本上设置 | 是否确保复制副本按照与源相同的顺序将事务写入其二进制日志。启用此功能需要将slave_parallel_workers设置为LOGICAL_CLOCK。 |
| slave_checkpoint_group在副本上设置 | 检查点操作之间要处理的最大事务数。 |
| slave_checkpoint_period在副本上设置 | 检查点操作之间的最长时间(毫秒)。 |
最常用的选项是源上的binlog_transaction_dependency_tracking和transaction_write_set_extraction以及副本上的slave_parallel_workers和slave_parallel_type。
源上的二进制日志事务相关性跟踪和写集提取选项是相关的。transaction_write_set_extraction选项指定如何提取写集合信息(关于哪些行受事务影响的信息)。写集也是组复制用于冲突检测的对象。将此设置为XXHASH64，这也是组复制所需的值。
binlog_transaction_dependency_tracking选项指定二进制日志中有哪些事务依赖信息。这对于并行复制来说非常重要，因为它能够知道哪些事务可以安全地并行应用。默认情况下，使用提交顺序并依赖提交时间戳。为了在根据逻辑时钟进行并行化时提高并行复制性能，请将binlog_transaction_dependency_tracking设置为WRITESET。
binlog_transaction_dependency_history_size选项指定了行散列的数量，这些散列提供了关于哪个事务最后修改了给定行的信息。默认值 25000 通常已经足够大了；但是，如果对不同行的修改率非常高，那么增加依赖关系历史记录的大小是值得的。
在副本服务器上，使用slave_parallel_workers选项启用并行复制。这是将为每个复制通道创建的应用工作线程的数量。将这个值设置得足够高，以使复制能够跟上，但不要设置得太高，以至于您最终会有空闲的工作线程，或者您会看到来自过于并行的工作负载的争用。
在副本上更新通常需要的另一个选项是slave_parallel_type选项。这指定了事件应该如何在应用工作器之间拆分。缺省值是DATABASE，顾名思义，它根据更新所属的模式来分割更新。另一种方法是LOGICAL_CLOCK,它使用二进制日志中的组提交信息或写集信息来确定哪些事务一起应用是安全的。除非您有几层副本，并且在二进制日志中不包含写集信息，否则，LOGICAL_CLOCK通常是最佳选择。
如果在未启用写集的情况下使用LOGICAL_CLOCK并行化类型，则可以在源上增加binlog_group_commit_sync_delay,以便在组提交特性中将更多的事务组合在一起，代价是提交延迟更长。这将为并行复制提供更多的事务，以便在工作线程之间进行分配，从而提高效率。
复制滞后的另一个主要原因是缺少主键。
主键
当您使用基于行的复制时，处理事件的应用工作器必须找到必须更改的行。如果有一个主键，这是非常简单和有效的——只需一个主键查找。但是，如果没有主键，则有必要检查所有行，直到找到所有列的值都与复制事件的 before 映像中的值相同的行。
如果表很大，这样的搜索代价很高。如果事务修改了一个相对较大的表中的许多行，在最坏的情况下，它可能会使复制看起来好像已经停止了。MySQL 8 使用了一种优化，它使用哈希来匹配表中的一组行；但是，有效性取决于一个事件中修改的行数，它永远不会像主键查找那样高效。
强烈建议您向所有表添加一个显式主键(或一个非唯一键)。没有主键不会节省磁盘空间或内存，因为如果您自己不添加主键，InnoDB 会添加一个隐藏的主键(不能用于复制)。隐藏主键是一个 6 字节的整数，并使用一个全局计数器，所以如果您有许多带有隐藏主键的表，计数器会成为一个瓶颈。此外，如果您想要使用组复制，严格要求所有表都有一个显式主键或一个 not- NULL惟一索引。
Tip
启用sql_require_primary_key选项，要求所有表都有一个主键。该选项在 MySQL 8.0.13 和更高版本中可用。
如果不能向某些表添加主键，那么每个复制事件中包含的行数越多，哈希搜索算法的效果就越好。通过增加复制的源实例上的binlog_row_event_max_size的大小，可以增加事务处理在同一表中修改大量行时组合在一起的行数。
放松数据安全
当事务被提交时，它必须被保存在磁盘上。在 InnoDB 中，通过重做日志来保证持久性，通过二进制日志来保证复制的持久性。在某些情况下，在副本上放松对更改已被持久化的保证可能是可以接受的。这种优化的代价是，如果操作系统崩溃，您将需要重建副本。
InnoDB 使用选项innodb_flush_log_at_trx_commit来确定每次提交事务时是否刷新重做日志。默认(也是最安全的设置)是在每次提交后刷新(innodb_flush_log_at_trx_commit = 1)。刷新是一项昂贵的操作，甚至一些 SSD 驱动器也难以跟上繁忙系统所需的刷新。如果您能承受丢失一秒钟的已提交事务，您可以将innodb_flush_log_at_trx_commit设置为 0 或 2。如果您愿意进一步推迟刷新，您可以增加innodb_flush_log_at_timeout，它设置刷新重做日志之间的最大时间间隔(以秒为单位)。默认值和最小值是 1 秒。这意味着如果发生灾难性故障，您可能需要重建副本，但好处是应用线程可以比源线程更便宜地提交更改，因此更容易跟上。
二进制日志同样使用sync_binlog选项，该选项也默认为 1，这意味着在每次提交后刷新二进制日志。如果您不需要副本上的二进制日志(注意，对于组复制，必须在所有 notes 上启用二进制日志)，您可以考虑完全禁用它，或者降低日志同步的频率。通常，在这种情况下，最好将sync_binlog设置为 100 或 1000，而不是 0，因为 0 通常会导致整个二进制日志在旋转时被一次刷新。刷新 1gb 可能需要几秒钟；与此同时，有一个互斥锁阻止提交事务。
Note
如果放松复制副本上的数据安全设置，确保在将复制副本提升为复制源时(例如，如果需要执行维护)，将它们重新设置为更严格的值。
复制筛选器
如果不需要副本上的所有数据，可以使用复制筛选器来减少应用线程所需的工作，并减少磁盘和内存需求。这也有助于副本与源保持同步。有六个选项可以设置复制过滤器。选项可分为三组，一组是做选项，一组是忽略选项，如表 26-2 所示。
表 26-2
复制筛选器选项
|选项名称
|
描述
|
| --- | --- |
| replicate-do-db``replicate-ignore-db | 是否包含作为值给出的模式(数据库)的更改。 |
| replicate-do-table``replicate-ignore-table | 是否包含作为值给出的表的更改。 |
| replicate-wild-do-table``replicate-wild-ignore-table | 类似于replicate-do-table和replicate-ignore-table选项，但是支持_和%通配符，就像编写LIKE子句一样。 |
当您指定其中一个选项时，您可以选择性地在纲要/表格前加上规则应该套用的频道名称和冒号。例如，忽略对source2通道的world模式的更新
[mysqld]
replicate-do-db = source2:world
这些选项只能在 MySQL 配置文件中设置，并且需要重启 MySQL 才能生效。您可以多次指定每个选项来添加多个规则。如果您需要动态地更改配置，您可以使用CHANGE REPLICATION FILTER语句来配置过滤器，例如:
mysql> CHANGE REPLICATION FILTER
REPLICATE_IGNORE_DB = (world)
FOR CHANNEL 'source2';
Query OK, 0 rows affected (0.0003 sec)
如果需要包含多个数据库，可以指定一个列表，因此需要用括号将world括起来。如果多次指定同一规则，则适用后者，忽略前者。
Tip
要查看CHANGE REPLICATION FILTER的完整规则，请参见 https://dev.mysql.com/doc/refman/en/change-replication-filter.html 。
复制筛选器最适合基于行的复制，因为很清楚哪个表受某个事件的影响。当您有一个语句时，该语句可能会影响多个表，因此对于基于语句的复制，并不总是清楚过滤器是否应该允许该语句。应该特别注意replicate-do-db和replicate-ignore-db，因为对于基于语句的复制，它们使用默认模式来决定是否允许使用语句。更糟糕的是将复制过滤器与行和语句事件混合使用(binlog_format = MIXED)，因为过滤器的效果可能取决于变更复制的格式。
Tip
当您使用复制过滤器时，最好使用binlog_format = row(默认值)。有关评估复制过滤器的完整规则，请参见 https://dev.mysql.com/doc/refman/en/replication-rules.html 。
关于如何提高复制性能的讨论到此结束。还有一个主题与迄今为止讨论的主题相反——如何通过使用副本来提高源的性能。
将工作卸载到副本
如果一个实例因读取查询而过载，提高性能的一个常用策略是将一些工作卸载到一个或多个副本上。一些常见的情况是将副本用于读取扩展，将副本用于报告或备份。本节将对此进行探讨。
Note
使用复制(例如，使用组复制的多主模式)不是一种横向扩展写入的方式，因为所有更改仍必须应用于所有节点。例如，对于写入横向扩展，您需要对数据进行分片，就像在 MySQL NDB 集群中所做的那样。分片解决方案超出了本书的范围。
读取横向扩展
复制最常见的用途之一是允许读取查询使用副本，这样可以减少复制源的负载。这是可能的，因为副本与源具有相同的数据。需要注意的主要事情是，即使在最好的情况下，从在源上提交事务到副本发生更改，也会有一个小的延迟。
如果您的应用对读取陈旧数据很敏感，那么可以选择组复制或 InnoDB 集群，后者在 8.0.14 版和更高版本中支持一致性级别，因此您可以确保应用使用所需的一致性级别。
Tip
为了更好地解释如何使用组复制一致性级别，组复制开发者强烈推荐位于 https://lefred.be/content/mysql-innodb-cluster-consistency-levels/ 的 Lefred 的博客以及博客顶部的博客链接。
使用副本读取还可以帮助您使应用和 MySQL 更接近最终用户，从而减少往返延迟，因此用户可以获得更好的体验。
任务分离
复制副本的另一个常见用途是在复制副本上执行一些高影响任务，以减少复制源上的负载。两个典型的任务是报告和备份。
当您使用复制副本进行报告查询时，您可能会受益于以不同于源的方式配置复制副本，从而针对其所用于的特定工作负载对其进行优化。也可以使用复制筛选器来避免包含来自源的所有数据和更新。更少的数据意味着副本必须应用更少的事务和写入更少的数据，并且您可以将更大比例的数据读入缓冲池。
使用副本进行备份也很常见。如果复制副本专用于备份，那么只要复制副本能够在下一次备份之前赶上，您就不必担心由于磁盘 I/O 或缓冲池污染而导致的锁定和性能下降。您甚至可以考虑在备份过程中关闭副本并执行冷备份。
摘要
本章介绍了复制的工作原理，如何监控和提高复制过程的性能，以及如何使用复制在几个实例之间分配工作。
本章开头提供了复制概述，包括术语介绍，并显示了在何处可以找到复制的监控信息。在 MySQL 8 中，监控复制的最佳方式是使用一系列性能模式表，这些表根据线程类型以及是配置还是状态来划分信息。还有专用于日志状态和组复制的表。
可以通过在复制事件中只包含有关更新行的 before 值的最少信息来减小复制事件的大小，从而优化连接线程。然而，这并不适用于所有的应用。您还可以对网络和中继日志的编写进行更改。建议使用启用了自动定位的基于 GTID 的复制，这允许您放松中继日志的同步。
对应用性能最重要的两件事是启用并行复制和确保所有表都有一个主键。并行复制可以通过更新影响的模式进行，也可以通过逻辑时钟进行。后者通常表现最佳，但也有例外，因此您需要根据您的工作负载进行验证。
最后，我们讨论了如何使用副本来分担原本必须在复制源上执行的工作。您可以将复制用于读取扩展，因为您可以将副本用于读取数据，并将源专用于需要写入数据的任务。您还可以将副本用于高度密集的工作，如报告和备份。
最后一章将通过使用缓存来减少工作量。
二十七、缓存
最便宜的查询是那些您根本不执行的查询。本章研究如何使用缓存来避免执行查询或降低查询的复杂性。首先，我们将讨论高速缓存是如何无处不在，以及如何存在不同类型的高速缓存。然后讲述了如何使用缓存表和近似值在 MySQL 中使用缓存。接下来的两节考虑了两个提供缓存的流行产品: Memcached 和 ProxySQL 。最后，讨论了一些缓存技巧。
缓存无处不在
即使您认为您没有实现缓存，您也已经在几个地方使用了缓存。这些缓存是透明的，在硬件、操作系统或 MySQL 级别维护。这些缓存中最明显的是 InnoDB 缓冲池。
图 27-1 展示了高速缓存如何存在于整个系统中的例子，以及如何添加自定义高速缓存的例子。这幅图——包括交互——绝不是完整的，但它足以说明缓存是多么普遍，以及它可以在多少地方出现。
图 27-1
可以进行缓存的示例
左下角是 CPU，它有几个级别的缓存，缓存用于 CPU 指令的指令和数据。操作系统实现了一个 I/O 缓存，InnoDB 有自己的缓冲池。所有这些缓存都是返回最新数据的缓存的例子。
还有一些缓存可能会提供稍微陈旧的数据。这包括在 MySQL 中实现缓存表，在 ProxySQL 中缓存查询结果，或者直接在应用中缓存数据。在这些情况下，您通常会定义一个时间段来考虑数据是否足够新，当它达到给定的年龄时—生存时间(TTL)——缓存条目就会失效。Memcached 解决方案很特别，因为它有两个版本。常规的 Memcached 守护进程使用生存时间或一些依赖于应用的逻辑来清除太旧的数据；然而，还有一个特殊的 MySQL 版本，它作为一个插件工作，可以从 InnoDB 缓冲池中获取数据，并将数据写回缓冲池，因此数据永远不会过时。
在应用中使用可能过期的数据似乎是错误的。然而，在许多情况下，这完全没问题，因为不需要精确的数据。如果您有一个显示销售数字仪表板的应用，如果数据是执行查询时的最新数据还是几分钟前的数据，会有多大的区别？当用户读完这些图时，它们可能已经有点过时了。重要的是销售数字是一致的，并且定期更新。
Tip
仔细考虑您的应用的要求是什么，并记住，与说服用户他们不再能得到最新的结果相比，从放宽对数据必须是最新的要求开始更容易，如果需要的话，可以变得更严格。如果使用不会自动更新为最新值的缓存数据，可以考虑存储数据的当前时间并显示给用户，这样用户就知道数据上次刷新的时间。
接下来的三个部分将介绍更多具体的缓存示例，从在 MySQL 中实现自己的缓存开始。
MySQL 内部的缓存
实现缓存的逻辑位置是在 MySQL 内部。如果缓存的数据与其他表一起使用，这尤其有用。缺点是它仍然需要从应用到数据库的往返来查询数据，并且需要执行查询。本节介绍了在 MySQL 中缓存数据的两种方法:缓存表和直方图统计。
缓存表
缓存表可用于预先计算数据，例如，用于报告或仪表板。它主要用于经常需要的复杂聚合。
有几种方法可以使用缓存表。您可以选择创建一个表来存储与其配合使用的要素的结果。这使得它使用起来很便宜，但也相对不灵活，因为它只能用于这一个功能。或者，您可以创建需要连接在一起的构建块，以便它们可以用于多种功能。这使得查询稍微贵了一点，但是您可以重用缓存的数据并避免复制数据。这取决于您的应用，哪种方法是最好的，您可能最终会选择一种混合方法，其中一些表可以单独使用，而其他的表可以结合在一起使用。
填充缓存表有两种主要策略。您可以定期完全重建表，也可以使用触发器持续更新数据。完全重建表的最佳方式是创建缓存表的新副本，并在重建结束时使用RENAME TABLE交换表，因为这样可以避免删除事务中潜在的大量行，并避免碎片随着时间的推移而累积。或者，当缓存数据所依赖的数据发生变化时，您可以使用触发器来更新缓存数据。如果使用不完全最新的数据是可以接受的，则在大多数情况下，重建缓存表是首选，因为这样不容易出错，并且刷新是在后台完成的。
Tip
如果通过删除事务中的现有数据来就地重建缓存表，那么要么禁用索引统计信息的自动重新计算，并在重建结束时使用ANALYZE TABLE，要么启用innodb_stats_include_delete_marked选项。
一种特殊的情况是包含在不缓存数据的表中的缓存列。缓存列很有用的一个例子是存储属于某个组的最新事件的时间、状态或 id。假设您的应用支持发送文本消息，并且您为每条消息存储了历史记录，例如它在应用中的创建时间、发送时间以及接收者确认消息的时间。在大多数情况下，只需要最新的状态和到达状态的时间，因此您可能希望将其与消息记录本身一起存储，而不是必须显式地查询它。在这种情况下，您可以使用两个表来存储状态:
CREATE TABLE message (
message_id bigint unsigned NOT NULL auto_increment,
message_text varchar(1024) NOT NULL,
cached_status_time datetime(3) NOT NULL,
cached_status_id tinyint unsigned NOT NULL,
PRIMARY KEY (message_id)
);
CREATE TABLE message_status_history (
message_status_id bigint unsigned NOT NULL auto_increment,
message_id bigint unsigned NOT NULL,
status_time datetime(3) NOT NULL,
status_id tinyint unsigned NOT NULL,
PRIMARY KEY (message_status_id)
);
在现实世界中，可能有更多的列和外键，但是对于这个例子，这些信息就足够了。当消息的状态改变时，会在message_status_history表中插入一行。您可以查找消息的最新行来找到最新状态，但是这里已经创建了一个业务规则来用最新状态和更改时间更新消息表中的cached_status_time和cached_status_id。这样，要返回到消息的应用细节(除非需要历史记录)，您只需要查询message表。您可以通过应用或触发器更新缓存的列，或者如果您不需要缓存的状态完全是最新的，您可以使用后台作业。
Tip
使用一种命名方案，明确哪些数据被缓存，哪些没有被缓存。例如，您可以用cached_作为缓存表和列的前缀。
另一个可以考虑缓存的例子是直方图统计。
直方图统计
回想一下第 16 章，直方图统计是对一列中每个值出现的频率的统计。您可以利用这一点，将直方图统计用作缓存。如果一个列最多有 1024 个唯一值，这是非常有用的，因为这是支持的最大存储桶数，所以 1024 是可用于单一直方图的最大值数。
清单 27-1 展示了一个使用直方图返回world数据库中印度(CountryCode = IND)城市数量的例子。
-- Create the histogram on the CountryCode
-- column of the world.city table.
mysql> ANALYZE TABLE world.city
UPDATE HISTOGRAM on CountryCode
WITH 1024 BUCKETS\G
*************************** 1\. row ***************************
Table: world.city
Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'CountryCode'.
1 row in set (0.5909 sec)
mysql> SELECT Bucket_Value, Frequency
FROM (
SELECT (Row_ID - 1) AS Bucket_Number,
SUBSTRING_INDEX(Bucket_Value, ':', -1)
AS Bucket_Value,
(Cumulative_Frequency
- LAG(Cumulative_Frequency, 1, 0)
OVER (ORDER BY Row_ID))
AS Frequency
FROM information_schema.COLUMN_STATISTICS
INNER JOIN JSON_TABLE(
histogram->'$.buckets',
'$[*]' COLUMNS(
Row_ID FOR ORDINALITY,
Bucket_Value varchar(42) PATH '$[0]',
Cumulative_Frequency double PATH '$[1]'
)
) buckets
WHERE SCHEMA_NAME = 'world'
AND TABLE_NAME = 'city'
AND COLUMN_NAME = 'CountryCode'
) stats
WHERE Bucket_Value = 'IND';
+--------------+---------------------+
| Bucket_Value | Frequency |
+--------------+---------------------+
| IND | 0.08359892130424124 |
+--------------+---------------------+
1 row in set (0.0102 sec)
mysql> SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'world'
AND TABLE_NAME = 'city';
+------------+
| TABLE_ROWS |
+------------+
| 4188 |
+------------+
1 row in set (0.0075 sec)
mysql> SELECT 0.08359892130424124*4188;
+--------------------------+
| 0.08359892130424124*4188 |
+--------------------------+
| 350.11228242216231312 |
+--------------------------+
1 row in set (0.0023 sec)
mysql> SELECT COUNT(*)
FROM world.city
WHERE CountryCode = 'IND';
+----------+
| COUNT(*) |
+----------+
| 341 |
+----------+
1 row in set (0.0360 sec)
Listing 27-1Using histograms as a cache
如果您认为对COLUMN_STATITICS的查询看起来很熟悉，那么它是从第 16 章中列出单一直方图的存储桶信息时使用的查询派生而来的。有必要在子查询中收集直方图信息，否则无法计算频率。
您还需要总行数。您可以使用来自information_schema.TABLES视图的近似值，或者缓存表格的SELECT COUNT(*)结果。在这个例子中，估计city表有 4188 行(您的估计可能不同)，加上印度的频率，表明表中大约有 350 个印度城市。精确的计算显示有 341 个。偏差来自于总行数估计值(在city表中有 4079 行)。
对于包含最多 1024 个唯一值的大型表，使用直方图作为缓存非常有用，尤其是在该列上没有索引的情况下。这意味着它并不匹配所有的用例。然而，它确实展示了一个跳出框框思考的例子——当您试图寻找缓存解决方案时，这是非常有用的。
对于更高级的缓存解决方案，您需要查看第三方解决方案或在应用中实现自己的解决方案。
Memcached
Memcached 是一个简单但高度可伸缩的内存键值存储，是一种流行的缓存工具。传统上，它主要用于 web 服务器，但也可以用于任何类型的应用。Memcached 的一个优点是它可以分布在多个主机上，这允许您创建一个大的缓存。
Note
Memcached 只在 Linux 和 Unix 上得到官方支持。
在 MySQL 中使用 Memcached 有两种方法。您可以使用常规的独立 Memcached，也可以使用 MySQL InnoDB Memcached 插件。本节将展示一个使用这两者的简单示例。有关完整的 Memcached 文档，请参见位于 https://memcached.org/ 的官方主页和位于 https://github.com/memcached/memcached/wiki 的官方 wiki。
独立 Memcached
独立的 Memcached 是来自 https://memcached.org/ 的官方守护进程。它允许您将其用作分布式缓存，或者让缓存非常靠近应用(可能在同一台主机上),从而降低查询缓存的成本。
安装 Memcached 有几个选项，包括使用操作系统的包管理器和从源代码编译。最简单的是在 Oracle Linux、Red Hat Enterprise Linux 和 CentOS 7 上使用您的软件包管理器:
shell$ sudo yum install memcached libevent
根据memcached的要求，包含了libevent包。在 Ubuntu Linux 上，这个包叫做libevent-dev。你可能已经安装了libevent和/或memcached，在这种情况下，软件包管理器会让你知道没什么可做的。
您可以使用memcached命令启动守护进程。例如，使用所有默认选项启动它
shell$ memcached
如果您在生产中使用它，您应该配置systemd或您正在使用的任何服务管理器，以便在操作系统启动和关闭时启动和停止守护程序。对于测试来说，只从命令行启动就可以了。
Caution
Memcached 中没有安全支持。将缓存的数据限制为不敏感的数据，并确保 Memcached 实例只在内部网络中可用，并使用防火墙来限制访问。一种选择是将 Memcached 部署在与应用相同的主机上，并阻止远程连接。
现在，您可以通过将从 MySQL 检索的数据存储在缓存中来使用 Memcached。有几种编程语言支持 Memcached。对于这个讨论，Python 将与pymemcache模块 1 和 MySQL 连接器/Python 一起使用。清单 27-2 显示了如何使用pip安装模块。根据您正在使用的 Python 的确切版本和您已经安装的内容，输出可能会有所不同，Python 命令的名称取决于您的系统。在撰写本文时，pymemcache支持 Python 2.7、3.5、3.6 和 3.7。该示例使用作为额外软件包安装在 Oracle Linux 7 上的 Python 3.6。
shell$ python3 -m pip install --user pymemcache
Collecting pymemcache
Downloading https://files.pythonhosted.org/packages/20/08/3dfe193f9a1dc60186fc40d41b7dc59f6bf2990722c3cbaf19cee36bbd93/pymemcache-2.2.2-py2.py3-none-any.whl (44kB)
|████████████████████████████████| 51kB 3.3MB/s
Requirement already satisfied: six in /usr/local/lib/python3.6/site-packages (from pymemcache) (1.11.0)
Installing collected packages: pymemcache
Successfully installed pymemcache-2.2.2
shell$ python36 -m pip install --user mysql-connector-python
Collecting mysql-connector-python
Downloading https://files.pythonhosted.org/packages/58/ac/a3e86e5df84b818f69ebb8c89f282efe6a15d3ad63a769314cdd00bccbbb/mysql_connector_python-8.0.17-cp36-cp36m-manylinux1_x86_64.whl (13.1MB)
|████████████████████████████████| 13.1MB 5.6MB/s
Requirement already satisfied: protobuf>=3.0.0 in /usr/local/lib64/python3.6/site-packages (from mysql-connector-python) (3.6.1)
Requirement already satisfied: setuptools in /usr/local/lib/python3.6/site-packages (from protobuf>=3.0.0->mysql-connector-python) (39.0.1)
Requirement already satisfied: six>=1.9 in /usr/local/lib/python3.6/site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.11.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.17
Listing 27-2Installing the Python pymemcache module
在您的应用中，您可以通过键查询 Memcached。如果找到了键，Memcached 返回与键一起存储的值，如果没有找到，您需要查询 MySQL 并将结果存储在缓存中。清单 27-3 展示了一个查询world.city表的简单例子。该程序也可以在本书的 GitHub 库中包含的文件listing_27_3.py中找到。如果你想执行这个程序，你需要更新connect_args中的连接参数来反映连接到你的 MySQL 实例的设置。
from pymemcache.client.base import Client
import mysql.connector
connect_args = {
"user": "root",
"password": "password",
"host": "localhost",
"port": 3306,
}
db = mysql.connector.connect(**connect_args)
cursor = db.cursor()
memcache = Client(("localhost", 11211))
sql = "SELECT CountryCode, Name FROM world.city WHERE ID = %s"
city_id = 130
city = memcache.get(str(city_id))
if city is not None:
country_code, name = city.decode("utf-8").split("|")
print("memcached: country: {0} - city: {1}".format(country_code, name))
else:
cursor.execute(sql, (city_id,))
country_code, name = cursor.fetchone()
memcache.set(str(city_id), "|".join([country_code, name]), expire=60)
print("MySQL: country: {0} - city: {1}".format(country_code, name))
memcache.close()
cursor.close()
db.close()
Listing 27-3Simple Python program using memcached and MySQL
该程序首先创建一个到 MySQL 和memcached守护进程的连接。在这种情况下，要查询的连接参数和 id 是硬编码的。在真实的程序中，您应该从配置文件或类似文件中读取连接参数。
Caution
不要在应用中存储连接详细信息。尤其不要硬编码密码。在应用中存储连接细节既不灵活也不安全。
然后程序尝试从 Memcached 中获取数据；请注意，当 Memcached 使用字符串作为键时，整数是如何转换为字符串的。如果找到了键，则通过在|字符处拆分字符串，从缓存的值中提取国家代码和名称。如果在缓存中找不到该键，则从 MySQL 获取城市数据并存储在缓存中，将缓存中的值保持为 60 秒。为每个案例添加了打印语句，以显示数据是从哪里获取的。
每次重启memcached后第一次执行程序时，它会查询 MySQL:
shell$ python3 listing_27_3.py
MySQL: country: AUS - city: Sydney
在长达一分钟的后续执行中，将在缓存中找到数据:
shell$ python3 listing_27_3.py
memcached: country: AUS - city: Sydney
当您测试完 Memcached 后，您可以在运行memcached的会话中使用 Ctrl+C 停止它，或者向它发送一个SIGTEM (15)信号，例如:
shell$ kill -s SIGTERM $(pidof memcached)
在这个例子中直接使用 Memcached 的好处是，您可以拥有一个守护进程池，并且可以在靠近应用的地方运行守护进程，甚至可以在与应用相同的主机上运行。缺点是您必须自己维护缓存。另一种方法是使用 MySQL 提供的memcached插件，它将为您管理缓存，甚至自动将写入保存到缓存中。
MySQL InnoDB Memcached 插件
MySQL 5.6 中引入了 InnoDB Memcached 插件，作为一种无需解析 SQL 语句的开销就能访问 InnoDB 数据的方法。该插件的主要用途是让 InnoDB 通过缓冲池处理缓存，并使用 Memcached 作为查询数据的机制。以这种方式使用插件的一些好处是，对插件的写入被写入底层 InnoDB 表，数据总是最新的，并且您可以同时使用 SQL 和 Memcached 来访问数据。
Note
在安装 MySQL InnoDB Memcached 插件之前，请确保您已经停止了独立的 Memcached 进程，因为它们默认使用相同的端口。如果不这样做，您将继续连接到独立进程。
在安装 MySQL memcached守护进程之前，必须确保像独立 Memcached 安装一样安装libevent包。一旦安装了libevent，就需要安装innodb_memcache模式，其中包括用于配置的表。您可以通过获取 MySQL 发行版中包含的share/innodb_memcached_config.sql文件来执行安装。该文件相对于 MySQL 基本目录，可以通过系统变量basedir找到，例如:
mysql> SELECT @@global.basedir AS basedir;
+---------+
| basedir |
+---------+
| /usr/ |
+---------+
1 row in set (0.00 sec)
如果您已经使用来自 https://dev.mysql.com/downloads/ 的 RPM 安装了 MySQL，命令是
mysql> SOURCE /usr/share/mysql-8.0/innodb_memcached_config.sql
Note
请注意，该命令在 MySQL Shell 中不起作用，因为该脚本包含不带分号的USE命令，而 MySQL Shell 在脚本中不支持分号。
该脚本还创建了test.demo_test表，该表将在接下来的讨论中使用。
innodb_memcache模式由三个表组成:
-
cache_policies: 定义缓存应该如何工作的缓存策略的配置。默认情况下，将它留给 InnoDB。这通常是推荐的方法，可以确保您永远不会读取过时的数据。 -
config_options: 插件的配置选项。这包括在为值和表映射分隔符返回多个列时使用哪个分隔符。 -
containers: 映射到 InnoDB 表的定义。您必须为所有想要与 InnoDBmemcached插件一起使用的表添加一个映射。
containers桌是你会用的最多的桌子。默认情况下，该表包含一个对test.demo_test表的映射:
mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1\. row ***************************
name: aaa
db_schema: test
db_table: demo_test
key_columns: c1
value_columns: c2
flags: c3
cas_column: c4
expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.0007 sec)
在查询表格时，您可以使用name来引用由db_schema和db_table定义的表格。key_columns列定义了 InnoDB 表中用于键查找的列。您可以在value_columns列中指定希望包含在查询结果中的列。如果包含多列，则使用在config_options表中带有name = separator的行中配置的分隔符(默认为|)来分隔列名。
很少需要cas_column和expire_time_column列，这里不再进一步讨论。最后一列unique_idx_name_on_key是表中唯一索引的名称，最好是主键。
Tip
这些表格的详细描述及其用途可以在 https://dev.mysql.com/doc/refman/en/innodb-memcached-internals.html 中找到。
您现在已经准备好安装插件本身了。您可以使用INSTALL PLUGIN命令来完成(记住这在 Windows 上不起作用):
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.09 sec)
这个语句必须使用传统的 MySQL 协议(默认端口 3306)来执行，因为 X 协议(默认端口 33060)不允许您安装插件。就这样——InnoDBmemcached插件现在已经准备好测试了。最简单的测试方法是使用telnet客户端。清单 27-4 显示了一个显式指定容器并使用默认容器的简单例子。
shell$ telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
get @@aaa.AA
VALUE @@aaa.AA 8 12
HELLO, HELLO
END
get AA
VALUE AA 8 12
HELLO, HELLO
END
Listing 27-4Testing InnoDB memcached with telnet
为了便于查看这两个命令，在每个命令之前都插入了一个空行。第一个命令使用@@在键值前指定容器名。第二个命令依赖于使用默认容器的 Memcached(按容器名的字母升序排序时的第一个条目)。您可以通过按 Ctrl+]然后按quit命令退出 telnet:
^]
telnet> quit
Connection closed.
默认情况下，守护进程使用端口 11211 作为独立的 Memcached 实例。如果您想更改端口或任何其他 Memcached 选项，您可以使用daemon_memcached_option选项，该选项带有一个带有memcached选项的字符串。例如，将端口设置为 22222
[mysqld]
daemon_memcached_option = "-p22222"
该选项只能在 MySQL 配置文件或命令行中设置，因此需要重启 MySQL 才能使更改生效。
如果您向containers表添加新条目或更改现有条目，您将需要重启memcached插件，使其再次读取定义。您可以通过重启 MySQL 或卸载并安装插件来实现:
mysql> UNINSTALL PLUGIN daemon_memcached;
Query OK, 0 rows affected (4.05 sec)
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.02 sec)
实际上，你将主要使用应用中的插件。如果您习惯于使用 Memcached，用法很简单。作为一个例子，考虑清单 27-5 ，它展示了一些使用pymemcache模块的 Python 命令。请注意，该示例假设您已经将端口重新设置为 11211。
shell$ python3
Python 3.6.8 (default, May 16 2019, 05:58:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-36.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from pymemcache.client.base import Client
>>> client = Client(('localhost', 11211))
>>> client.get('@@aaa.AA')
b'HELLO, HELLO'
>>> client.set('@@aaa.BB', 'Hello World')
True
>>> client.get('@@aaa.BB')
b'Hello World'
Listing 27-5Using the InnoDB memcached plugin with Python
交互式 Python 环境用于通过memcached插件查询test.demo_test表。创建连接后，使用get()方法查询现有行，并使用set()方法插入新行。在这种情况下，不需要设置超时，因为set()方法最终会直接写入 InnoDB。最后，再次检索新行。请注意，与需要自己维护缓存的常规 Memcached 相比，这个示例是多么简单。
您可以通过在 MySQL 中查询来验证新行是否真的插入到表中:
mysql> SELECT * FROM test.demo_test;
+----+--------------+----+----+----+
| c1 | c2 | c3 | c4 | c5 |
+----+--------------+----+----+----+
| AA | HELLO, HELLO | 8 | 0 | 0 |
| BB | Hello World | 0 | 1 | 0 |
+----+--------------+----+----+----+
2 rows in set (0.0032 sec)
使用 MySQL InnoDB Memcached 插件还有更多内容。如果您打算使用它，建议您在 https://dev.mysql.com/doc/refman/en/innodb-memcached.html 阅读参考手册中的“InnoDB memcached 插件”部分。
另一个支持缓存的流行工具是 ProxySQL。
ProxySQL
ProxySQL 项目 2 由 René Cannaò创建，是一个高级代理，支持负载平衡、基于查询规则的路由、缓存等。缓存功能基于查询规则进行缓存，例如，您可以设置想要缓存具有给定摘要的查询。根据您为查询规则设置的生存时间值，缓存会自动过期。
你从 https://github.com/sysown/proxysql/releases/ 下载 ProxySQL。在撰写本文时，最新的版本是 2.0.8 版，这是示例中使用的版本。
Note
ProxySQL 仅正式支持 Linux。有关支持的发行版的完整文档，包括安装说明，请参见 https://github.com/sysown/proxysql/wiki 。
清单 27-6 展示了使用 ProxySQL GitHub 存储库中的 RPM 在 Oracle Linux 上安装 ProxySQL 2.0.8 的示例。在其他 Linux 发行版上，安装过程是类似的，使用的是发行版的 package 命令(当然，根据使用的 package 命令，输出会有所不同)。安装完成后，将启动 ProxySQL。
shell$ wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm
...
Length: 9340744 (8.9M) [application/octet-stream]
Saving to: 'proxysql-2.0.8-1-centos7.x86_64.rpm'
100%[===========================>] 9,340,744 2.22MB/s in 4.0s
2019-11-24 18:41:34 (2.22 MB/s) - 'proxysql-2.0.8-1-centos7.x86_64.rpm' saved [9340744/9340744]
shell$ sudo yum install proxysql-2.0.8-1-centos7.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining proxysql-2.0.8-1-centos7.x86_64.rpm: proxysql-2.0.8-1.x86_64
Marking proxysql-2.0.8-1-centos7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.0.8-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================
Package Arch Version Repository Size
==============================================================
Installing:
proxysql x86_64 2.0.8-1 /proxysql-2.0.8-1-centos7.x86_64 35 M
Transaction Summary
==============================================================
Install 1 Package
Total size: 35 M
Installed size: 35 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : proxysql-2.0.8-1.x86_64 1/1
warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
Verifying : proxysql-2.0.8-1.x86_64 1/1
Installed:
proxysql.x86_64 0:2.0.8-1
Complete!
shell$ sudo systemctl start proxysql
Listing 27-6Installing and starting ProxySQL
您只能通过其管理界面来配置 ProxySQL。这使用了mysql命令行客户端，对 MySQL 管理员来说有一种熟悉的感觉。默认情况下，ProxySQL 使用端口 6032 作为管理接口，管理员用户名为admin，密码设置为admin。清单 27-7 展示了一个连接到管理界面并列出可用模式和表的例子。
shell$ mysql --host=127.0.0.1 --port=6032 \
--user=admin --password \
--default-character-set=utf8mb4 \
--prompt='ProxySQL> '
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ProxySQL> SHOW SCHEMAS;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
ProxySQL> SHOW TABLES;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
24 rows in set (0.00 sec)
Listing 27-7The administration interface
当表在模式中分组时，您可以直接访问表，而无需引用模式。SHOW TABLES的输出显示了main模式中与 ProxySQL 的配置相关联的表。
配置过程分为两个阶段，首先准备新配置，然后应用它。应用更改意味着将它们保存到磁盘，如果您想要持久化它们并将其加载到运行时线程中的话。
名称中带有runtime_前缀的表是用于推送到运行时线程的配置的。配置 ProxySQL 的一种方式是使用类似于在 MySQL 中设置系统变量的SET语句，但是您也可以使用UPDATE语句。第一步应该是更改管理员密码(也可以选择管理员用户名)，这可以通过设置清单 27-8 中所示的admin-admin_credentials变量来完成。
ProxySQL> SET admin-admin_credentials = 'admin:password';
Query OK, 1 row affected (0.01 sec)
ProxySQL> SAVE ADMIN VARIABLES TO DISK;
Query OK, 32 rows affected (0.02 sec)
ProxySQL> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> SELECT @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:password |
+---------------------------+
1 row in set (0.00 sec)
Listing 27-8Setting the password for the administrator account
admin-admin_credentials选项的值是用冒号分隔的用户名和密码。SAVE ADMIN VARIABLES TO DISK语句保存更改，LOAD ADMIN VARIABLES TO RUNTIME命令将更改应用到运行时线程。有必要将变量加载到运行时线程中，因为出于性能原因，ProxySQL 会在每个线程中保存变量的副本。您可以像在 MySQL 中查询系统变量一样查询当前值(无论是已应用的还是待定的)。
您可以配置 MySQL 后端实例，ProxySQL 可以使用这些实例来定向mysql_servers表中的查询。对于此讨论，将使用与 ProxySQL 在同一主机上的单个实例。清单 27-9 展示了如何将它添加到 ProxySQL 可以路由到的服务器列表中。
ProxySQL> SHOW CREATE TABLE mysql_servers\G
*************************** 1\. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT ",
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.01 sec)
ProxySQL> INSERT INTO mysql_servers
(hostname, port, use_ssl)
VALUES ('127.0.0.1', 3306, 1);
Query OK, 1 row affected (0.01 sec)
ProxySQL> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.36 sec)
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
Listing 27-9Adding a MySQL instance to the list of servers
这个例子展示了如何使用SHOW CREATE TABLE来获取关于mysql_servers表的信息。表定义包括有关可以包含的设置和允许值的信息。除主机名外，所有设置都有默认值。清单的剩余部分在localhost端口 3306 上为 MySQL 实例插入一行，要求使用 SSL。然后，更改被保存到磁盘，并加载到运行时线程中。
Note
SSL 只能从 ProxySQL 到 MySQL 实例使用，不能在客户端和 ProxySQL 之间使用。
您还需要指定哪些用户可以使用该连接。首先，在 MySQL 中创建一个用户:
mysql> CREATE USER myuser@'127.0.0.1'
IDENTIFIED WITH mysql_native_password
BY 'password';
Query OK, 0 rows affected (0.0550 sec)
mysql> GRANT ALL ON world.* TO myuser@'127.0.0.1';
Query OK, 0 rows affected (0.0422 sec)
ProxySQL 目前不支持caching_sha2_password身份验证插件，这是 MySQL 8 中的默认设置，当您使用 MySQL Shell 连接时(但是使用mysql命令行客户端有支持)，因此您需要使用mysql_native_password插件创建用户。然后在 ProxySQL 中添加用户:
ProxySQL> INSERT INTO mysql_users
(username,password)
VALUES ('myuser', 'password');
Query OK, 1 row affected (0.00 sec)
ProxySQL> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.06 sec)
ProxySQL> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
现在可以通过 ProxySQL 连接到 MySQL 了。默认情况下，SQL 接口使用端口 6033。除了端口号和可能的主机名之外，通过 ProxySQL 的连接方式与平常一样:
shell$ mysqlsh --user=myuser --password \
--host=127.0.0.1 --port=6033 \
--sql --table \
-e "SELECT * FROM world.city WHERE ID = 130;"
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
ProxySQL 以类似于性能模式的方式收集统计信息。您可以查询stats_mysql_query_digest和stats_mysql_query_digest_reset表中的统计数据。这两个表的不同之处在于，后者只包含自上次查询该表以来的摘要。例如，获取按总执行时间排序的查询
ProxySQL> SELECT count_star, sum_time,
digest, digest_text
FROM stats_mysql_query_digest_reset
ORDER BY sum_time DESC\G
*************************** 1\. row ***************************
count_star: 1
sum_time: 577149
digest: 0x170E9EDDB525D570
digest_text: select @@sql_mode;
*************************** 2\. row ***************************
count_star: 1
sum_time: 5795
digest: 0x94656E0AA2C6D499
digest_text: SELECT * FROM world.city WHERE ID = ?
2 rows in set (0.01 sec)
如果您看到一个想要缓存其结果的查询，您可以添加一个基于查询摘要的查询规则。假设您想要缓存通过ID(摘要0x94656E0AA2C6D499)查询world.city表的结果，您可以添加如下规则:
ProxySQL> INSERT INTO mysql_query_rules
(active, digest, cache_ttl, apply)
VALUES (1, '0x94656E0AA2C6D499', 60000, 1);
Query OK, 1 row affected (0.01 sec)
ProxySQL> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.09 sec)
ProxySQL> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
active列指定在评估可以使用的规则时，ProxySQL 是否应该考虑该规则。digest是您想要缓存的查询的摘要，cache_ttl指定在结果被认为过期之前应该使用多长时间，并且结果被刷新。生存时间被设置为 60000 毫秒(1 分钟),以便在缓存失效之前有时间执行几次查询。将apply设置为 1 意味着当查询匹配这个规则时，将不会评估后面的规则。
如果您在一分钟内执行了几次查询，您可以查询表stats_mysql_global中的缓存统计信息，以查看缓存是如何使用的。输出的一个例子是
ProxySQL> SELECT *
FROM stats_mysql_global
WHERE Variable_Name LIKE 'Query_Cache%';
+--------------------------+----------------+
| Variable_Name | Variable_Value |
+--------------------------+----------------+
| Query_Cache_Memory_bytes | 3659 |
| Query_Cache_count_GET | 6 |
| Query_Cache_count_GET_OK | 5 |
| Query_Cache_count_SET | 1 |
| Query_Cache_bytes_IN | 331 |
| Query_Cache_bytes_OUT | 1655 |
| Query_Cache_Purged | 0 |
| Query_Cache_Entries | 1 |
+--------------------------+----------------+
8 rows in set (0.01 sec)
您的数据很可能会有所不同。它显示缓存使用了 3659 个字节，对缓存进行了六次查询，其中五次查询的结果都是从缓存返回的。六个查询中的最后一个需要对 MySQL 后端执行查询。
您可以设置两个选项来配置缓存。这些是
-
mysql-query_cache_size_MB: 缓存的最大大小，以兆为单位。这是一个软限制，清除线程使用它来决定从缓存中清除多少个查询。因此内存使用量可能会暂时大于配置的大小。默认值为 256。 -
mysql-query_cache_stores_empty_result: 是否缓存没有行的结果集。默认值为 true。这也可以在查询规则表中针对每个查询进行配置。
您可以像前面更改管理员密码一样更改配置。例如，将查询缓存限制为 128 兆字节
ProxySQL> SET mysql-query_cache_size_MB = 128;
Query OK, 1 row affected (0.00 sec)
ProxySQL> SAVE MYSQL VARIABLES TO DISK;
Query OK, 121 rows affected (0.04 sec)
ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
这首先准备配置更改，然后将其保存到磁盘，最后将 MySQL 变量加载到运行时线程中。
如果你想使用 ProxySQL，建议你在 https://github.com/sysown/proxysql/wiki 查阅 ProxySQL GitHub 项目的 wiki。
缓存提示
如果您决定为 MySQL 实例实现缓存，有几件事情需要考虑。本节研究一些通用的缓存技巧。
最重要的考虑是缓存什么。本章前面的缓存单行主键查找结果的例子并不是从缓存中获益最多的查询类型的好例子。一般来说，查询越复杂和昂贵，查询执行得越频繁，查询就越适合。使缓存更有效的一个方法是将复杂的查询分成更小的部分。这样，您可以分别缓存复杂查询的每个部分的结果，这使得它更有可能被重用。
您还应该考虑查询返回多少数据。如果查询返回一个很大的结果集，您可能最终会使用所有可用于缓存单个查询的内存。
另一个考虑是在哪里有缓存。缓存离应用越近，效率就越高，因为它减少了花费在网络通信上的时间。缺点是，如果您有多个应用实例，您将不得不在复制缓存和拥有远程共享缓存之间做出选择。例外情况是，如果您需要将缓存的数据用于其他 MySQL 表。在这种情况下，最好将缓存以缓存表或类似的形式保存在 MySQL 中。
摘要
本章概述了 MySQL 的缓存。它首先描述了从 CPU 内部到专用缓存进程，缓存是如何无处不在的。然后讨论了如何在 MySQL 中使用缓存表和直方图进行缓存。
这两个主要部分讨论了如何使用 Memcached 和 ProxySQL 进行缓存。Memcached 是一个内存中的键值存储，可以在应用中使用，也可以使用 MySQL 中包含的特殊版本，该版本允许您直接与 InnoDB 交互。ProxySQL 结合了路由和缓存机制，根据您定义的查询规则透明地存储结果集。
最后，介绍了一些关于缓存的注意事项。执行查询越频繁，执行的代价越大，缓存带来的好处就越多。第二个需要考虑的问题是，缓存离应用越近越好。
MySQL 8 查询性能调优之旅的最后一章到此结束。希望这是一次有收获的旅程，你觉得已经准备好在工作中使用这些工具和技术了。请记住，您对查询调优练习得越多，就越擅长。查询调优愉快。
Footnotes [1](#Fn1_source)https://pypi.org/project/pymemcache/
