如何在Ubuntu16.04上使用ProxySQL缓存优化MySQL查询

来自菜鸟教程
跳转至：导航、​搜索

作为 Write for DOnations 计划的一部分，作者选择了 自由软件基金会 来接受捐赠。

介绍

ProxySQL 是一个支持 SQL 的代理服务器，可以定位在您的应用程序和数据库之间。 它提供了许多功能，例如 多个 MySQL 服务器之间的负载平衡 和用作查询的 缓存层 。 本教程将重点介绍 ProxySQL 的缓存功能，以及它如何优化 MySQL 数据库的查询。

MySQL 缓存 在存储查询结果时发生，以便在重复该查询时，无需对数据库进行排序即可返回结果。 这可以显着提高常见查询的速度。 但是在许多缓存方法中，开发人员必须修改其应用程序的代码，这可能会在代码库中引入错误。 为了避免这种容易出错的做法，ProxySQL 允许您设置 透明缓存 。

在透明缓存中，只有数据库管理员需要更改 ProxySQL 配置来为最常见的查询启用缓存，这些更改可以通过 ProxySQL 管理界面完成。 开发人员需要做的就是连接到协议感知代理，代理将决定是否可以从缓存中提供查询而无需访问后端服务器。

在本教程中，您将使用 ProxySQL 在 Ubuntu 16.04 上为 MySQL 服务器设置透明缓存。 然后，您将使用带和不带缓存的 mysqlslap 测试其性能，以演示缓存的效果以及执行许多类似查询时可以节省多少时间。

先决条件

在开始本指南之前，您需要以下内容：

第 1 步 — 安装和设置 MySQL 服务器

首先，您将安装 MySQL 服务器并将其配置为由 ProxySQL 用作服务客户端查询的后端服务器。

在 Ubuntu 16.04 上，可以使用以下命令安装 mysql-server： 

sudo apt-get install mysql-server

按 Y 确认安装。

然后将提示您输入 MySQL root 用户密码。 输入强密码并保存以备后用。

现在您已准备好 MySQL 服务器，您将对其进行配置以使 ProxySQL 正常工作。 您需要为 ProxySQL 添加一个 monitor 用户来监控 MySQL 服务器，因为 ProxySQL 通过 SQL 协议侦听后端服务器，而不是使用 TCP 连接或 HTTP GET 请求以确保后端正在运行。 monitor 将使用虚拟 SQL 连接来确定服务器是否处于活动状态。

首先，登录 MySQL shell： 

mysql -uroot -p

-uroot 使用 MySQL root 用户登录，-p 提示输入 root 用户的密码。 这个root用户和你服务器的root用户不同，密码是你安装mysql-server包时输入的密码。

输入root密码，按ENTER。

现在您将创建两个用户，一个名为 monitor 用于 ProxySQL，另一个用于执行客户端查询并授予他们正确的权限。 本教程将把这个用户命名为 sammy。

创建 monitor 用户： 

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

CREATE USER 查询用于创建可以从特定 IP 连接的新用户。 使用 % 表示用户可以从任何 IP 地址进行连接。 IDENTIFIED BY 设置新用户的密码； 输入您喜欢的任何密码，但请务必记住它以备后用。

创建用户 monitor 后，接下来创建 sammy 用户： 

CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

接下来，向您的新用户授予权限。 运行以下命令配置监视器： 

GRANT SELECT ON sys.* TO 'monitor'@'%';

GRANT 查询用于授予用户权限。 在这里，您仅将 sys 数据库中所有表的 SELECT 授予 monitor 用户； 它只需要这个权限来监听后端服务器。

现在将所有数据库的所有权限授予用户 sammy： 

GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

这将允许 sammy 进行必要的查询以稍后测试您的数据库。

通过运行以下命令应用权限更改： 

FLUSH PRIVILEGES;

最后，退出 mysql 外壳： 

exit;

您现在已经安装了 mysql-server 并创建了一个用户，ProxySQL 将使用它来监控您的 MySQL 服务器，并创建另一个用户来执行客户端查询。 接下来，您将安装和配置 ProxySQL。

第 2 步 — 安装和配置 ProxySQL Server

现在您可以安装 ProxySQL 服务器，它将用作您查询的 缓存层 。 缓存层作为应用服务器和数据库后端服务器之间的一站； 它用于连接到数据库并将某些查询的结果保存在其内存中以供以后快速访问。

ProxySQL 发布 Github 页面 提供常见 Linux 发行版的安装文件。 对于本教程，您将使用 wget 下载 ProxySQL 版本 2.0.4 Debian 安装文件： 

wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

接下来，使用 dpkg 安装软件包： 

sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

安装后，使用以下命令启动 ProxySQL： 

sudo systemctl start proxysql

您可以使用以下命令检查 ProxySQL 是否正确启动： 

sudo systemctl status proxysql

你会得到类似这样的输出： 

Outputroot@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 0
   Memory: 0B
      CPU: 0

现在是时候将 ProxySQL 服务器连接到 MySQL 服务器了。 为此，请使用 ProxySQL 管理 SQL 接口，默认情况下侦听 localhost 上的端口 6032 并使用 admin 作为其用户名和密码。

通过运行以下命令连接到接口： 

mysql -uadmin -p -h 127.0.0.1 -P6032

提示输入密码时输入 admin。

-uadmin 将用户名设置为 admin，-h 标志将主机指定为 localhost。 端口是 6032，使用 -P 标志指定。

在这里，您必须明确指定主机和端口，因为默认情况下，MySQL 客户端使用本地套接字文件和端口 3306 进行连接。

现在您以 admin 身份登录到 mysql shell，配置 monitor 用户以便 ProxySQL 可以使用它。 首先，使用标准 SQL 查询来设置两个全局变量的值： 

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

变量 mysql-monitor_username 指定 MySQL 用户名，用于检查后端服务器是否处于活动状态。 变量 mysql-monitor_password 指向连接后端服务器时使用的密码。 使用您为 monitor 用户名创建的密码。

每次在 ProxySQL 管理界面中创建更改时，都需要使用正确的 LOAD 命令将更改应用到正在运行的 ProxySQL 实例。 您更改了 MySQL 全局变量，因此将它们加载到 RUNTIME 以应用更改： 

LOAD MYSQL VARIABLES TO RUNTIME;

接下来，SAVE 对磁盘数据库的更改以在重新启动之间保持更改。 ProxySQL 使用自己的 SQLite 本地数据库来存储自己的表和变量： 

SAVE MYSQL VARIABLES TO DISK;

现在，您将告诉 ProxySQL 关于后端服务器的信息。 表 mysql_servers 包含有关 ProxySQL 可以连接和执行查询的每个后端服务器的信息，因此使用标准 SQL INSERT 语句添加一条新记录，其中 [X208X 的值如下]、hostname 和 port： 

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

要应用更改，请再次运行 LOAD 和 SAVE： 

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

最后，您将告诉 ProxySQL 哪个用户将连接到后端服务器； 将 sammy 设置为用户，并将 sammy_password 替换为您之前创建的密码： 

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

表 mysql_users 保存有关用于连接到后端服务器的用户的信息； 您指定了 username、password 和 default_hostgroup。

LOAD 和 SAVE 的变化： 

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

然后退出 mysql 外壳： 

exit;

要测试您是否可以使用 ProxySQL 连接到后端服务器，请执行以下测试查询： 

mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

在此命令中，您使用 -e 标志执行查询并关闭连接。 该查询打印后端服务器的主机名。

注意： ProxySQL 默认使用端口 6033 来监听传入的连接。


输出将如下所示，将 your_hostname 替换为您的主机名： 

Output+----------------------------+
| hostname                   |
+----------------------------+
| your_hostname        |
+----------------------------+

要了解有关 ProxySQL 配置的更多信息，请参阅 如何在 Ubuntu 16.04 上使用 ProxySQL 作为 MySQL 的负载均衡器的第 3 步。

到目前为止，您已将 ProxySQL 配置为使用 MySQL 服务器作为后端，并使用 ProxySQL 连接到后端。 现在，您已准备好使用 mysqlslap 在不使用缓存的情况下对查询性能进行基准测试。

第 3 步 — 使用 mysqlslap 进行测试而不使用缓存

在此步骤中，您将下载一个测试数据库，以便您可以使用 mysqlslap 对其执行查询，以测试没有缓存的延迟，为查询速度设置基准。 您还将探索 ProxySQL 如何在 stats_mysql_query_digest 表中保存查询记录。

mysqlslap 是一个负载仿真客户端，用作 MySQL 的负载测试工具。 它可以使用自动生成的查询或在数据库上执行的一些自定义查询来测试 MySQL 服务器。 自带MySQL客户端包，无需安装； 相反，您将下载一个仅用于测试目的的数据库，您可以在该数据库上使用 mysqlslap。

在本教程中，您将使用 示例员工数据库 。 您将使用这个员工数据库，因为它具有一个可以说明查询优化差异的大型数据集。 该数据库有六个表，但它包含的数据有超过 300,000 条员工记录。 这将帮助您模拟大规模生产工作负载。

要下载数据库，首先使用以下命令克隆 Github 存储库： 

git clone https://github.com/datacharmer/test_db.git

然后进入 test_db 目录并使用以下命令将数据库加载到 MySQL 服务器中： 

cd test_db
mysql -uroot -p < employees.sql

此命令使用 shell 重定向读取 employees.sql 文件中的 SQL 查询，并在 MySQL 服务器上执行它们以创建数据库结构。

你会看到这样的输出： 

OutputINFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:32

将数据库加载到 MySQL 服务器后，测试 mysqlslap 是否正在使用以下查询： 

mysqlslap -usammy -p -P6033 -h127.0.0.1  --auto-generate-sql --verbose

mysqlslap 与 mysql 客户端有相似的标志； 以下是此命令中使用的：

  • -u 指定用于连接服务器的用户。
  • -p 提示输入用户密码。
  • -P 使用指定端口连接。
  • -h 连接到指定的主机。
  • --auto-generate-sql 让 MySQL 使用自己生成的查询执行负载测试。
  • --verbose 使输出显示更多信息。

您将获得类似于以下内容的输出： 

OutputBenchmark
    Average number of seconds to run all queries: 0.015 seconds
    Minimum number of seconds to run all queries: 0.015 seconds
    Maximum number of seconds to run all queries: 0.015 seconds
    Number of clients running queries: 1
    Average number of queries per client: 0

在此输出中，您可以看到执行所有查询所花费的平均、最小和最大秒数。 这为您提供了有关多个客户端执行查询所需的时间量的指示。 在此输出中，只有一个客户端用于执行查询。

接下来，通过查看 ProxySQL 的 stats_mysql_query_digest 找出最后一条命令中执行了哪些查询 mysqlslap。 这将为我们提供查询的 digest 之类的信息，这是 SQL 语句的规范化形式，以后可以引用以启用缓存。

使用以下命令进入 ProxySQL 管理界面： 

mysql -uadmin -p -h 127.0.0.1 -P6032

然后执行此查询以查找 stats_mysql_query_digest 表中的信息： 

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

您将看到类似于以下内容的输出： 

+------------+----------+-----------+--------------------+----------------------------------+
| count_star | sum_time | hostgroup | digest             | digest_text                      |
+------------+----------+-----------+--------------------+----------------------------------+
| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
| 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
+------------+----------+-----------+--------------------+----------------------------------+
2 rows in set (0.01 sec)

上一个查询从 stats_mysql_query_digest 表中选择数据，该表包含有关 ProxySQL 中所有已执行查询的信息。 在这里，您选择了五列：

  • count_star：这个查询被执行的次数。
  • sum_time：此查询执行的总时间（以毫秒为单位）。
  • hostgroup：用于执行查询的主机组。
  • digest：执行查询的摘要。
  • digest_text：实际查询。 在本教程的示例中，第二个查询使用 ? 标记代替可变参数进行参数化。 因此，select @@version_comment limit 1 和 select @@version_comment limit 2 被分组为具有相同摘要的相同查询。

现在您知道如何检查 stats_mysql_query_digest 表中的查询数据，退出 mysql shell： 

exit;

您下载的数据库包含一些带有演示数据的表格。 现在，您将通过选择 from_date 大于 2000-04-20 的任何记录并记录平均执行时间来测试 dept_emp 表上的查询。

使用此命令运行测试： 

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

在这里，您使用了一些新标志：

  • --concurrency=100：设置要模拟的用户数量，在本例中为 100。
  • --iterations=20：这会导致测试运行 20 次并计算所有结果。
  • --create-schema=employees：这里选择了 employees 数据库。
  • --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'"：在这里你指定了在测试中执行的查询。

测试将需要几分钟。 完成后，您将获得类似以下的结果： 

OutputBenchmark
        Average number of seconds to run all queries: 18.117 seconds
        Minimum number of seconds to run all queries: 8.726 seconds
        Maximum number of seconds to run all queries: 22.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

你的数字可能有点不同。 将这些数字保存在某处，以便将它们与启用缓存后的结果进行比较。

在没有缓存的情况下测试 ProxySQL 之后，是时候再次运行相同的测试了，但这次启用了缓存。

第 4 步 — 使用带有缓存的 mysqlslap 进行测试

在这一步中，缓存将帮助我们减少执行类似查询时的延迟。 在这里，您将识别执行的查询，从 ProxySQL 的 stats_mysql_query_digest 表中获取它们的摘要，并使用它们来启用缓存。 然后，您将再次测试以检查差异。

要启用缓存，您需要知道将被缓存的查询的摘要。 使用以下命令登录到 ProxySQL 管理界面： 

mysql -uadmin -p -h127.0.0.1 -P6032

然后再次执行此查询以获取执行的查询列表及其摘要： 

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

你会得到类似这样的结果： 

Output+------------+-------------+-----------+--------------------+------------------------------------------+
| count_star | sum_time    | hostgroup | digest             | digest_text                              |
+------------+-------------+-----------+--------------------+------------------------------------------+
| 2000       | 33727110501 | 1         | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? |
| 1          | 601         | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname            |
| 1          | 0           | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ?         |
+------------+-------------+-----------+--------------------+------------------------------------------+
3 rows in set (0.00 sec)

看第一行。 这是关于执行了 2000 次的查询。 这是之前执行的基准查询。 获取其摘要并保存以用于添加查询规则以进行缓存。

接下来的几个查询将向 ProxySQL 添加一个新的查询规则，该规则将匹配上一个查询的摘要并为其输入一个 cache_ttl 值。 cache_ttl 是将结果缓存在内存中的毫秒数： 

INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

在此命令中，您将向 mysql_query_rules 表添加新记录； 此表包含执行查询之前应用的所有规则。 在此示例中，您正在为 cache_ttl 列添加一个值，该值将导致给定摘要的匹配查询缓存此列中指定的毫秒数。 您将 1 放在应用列中以确保将规则应用于查询。

LOAD 和 SAVE 这些改变，然后退出 mysql shell： 

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
exit;

现在启用了缓存，再次重新运行测试以检查结果： 

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

这将提供类似于以下内容的输出： 

OutputBenchmark
        Average number of seconds to run all queries: 7.020 seconds
        Minimum number of seconds to run all queries: 0.274 seconds
        Maximum number of seconds to run all queries: 23.014 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

在这里您可以看到平均执行时间的巨大差异：它从 18.117 秒下降到 7.020。

结论

在本文中，您使用 ProxySQL 设置透明缓存来缓存数据库查询结果。 您还测试了使用和不使用缓存的查询速度，以了解缓存的不同之处。

您在本教程中使用了一级缓存。 您也可以尝试 web 缓存，它位于 Web 服务器前面并缓存对类似请求的响应，将响应发送回客户端而不会影响后端服务器。 这与 ProxySQL 缓存非常相似，但级别不同。 要了解有关 Web 缓存的更多信息，请查看我们的 Web 缓存基础：术语、HTTP 标头和缓存策略入门 。

MySQL 服务器也有自己的查询缓存； 您可以在我们的 How To Optimize MySQL with Query Cache on Ubuntu 18.04 教程 中了解更多信息。

取自“https://cainiaojiaocheng.com/index.php?title=如何在Ubuntu16.04上使用ProxySQL缓存优化MySQL查询&oldid=30749”
Powered by MediaWiki