如何在Ubuntu 16.04上使用ProxySQL缓存优化MySQL查询

The author selected the Free Software Foundation to receive a donation as part of the Write for DOnations program.

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

介绍 (Introduction)

ProxySQL is a SQL-aware proxy server that can be positioned between your application and your database. It offers many features, such as load-balancing between multiple MySQL servers and serving as a caching layer for queries. This tutorial will focus on ProxySQL’s caching feature, and how it can optimize queries for your MySQL database.

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

MySQL caching occurs when the result of a query is stored so that, when that query is repeated, the result can be returned without needing to sort through the database. This can significantly increase the speed of common queries. But in many caching methods, developers must modify the code of their application, which could introduce a bug into the codebase. To avoid this error-prone practice, ProxySQL allows you to set up transparent caching.

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

In transparent caching, only database administrators need to change the ProxySQL configuration to enable caching for the most common queries, and these changes can be done through the ProxySQL admin interface. All the developer needs to do is connect to the protocol-aware proxy, and the proxy will decide if the query can be served from the cache without hitting the back-end server.

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

In this tutorial, you will use ProxySQL to set up transparent caching for a MySQL server on Ubuntu 16.04. You will then test its performance using mysqlslap with and without caching to demonstrate the effect of caching and how much time it can save when executing many similar queries.

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

先决条件 (Prerequisites)

Before you begin this guide you’ll need the following:

在开始本指南之前，您需要满足以下条件：

• One Ubuntu 16.04 server with at least 2 GB of RAM, set up with a non-root user with sudo privileges and a firewall, as instructed in our Ubuntu 16.04 Initial Server Setup guide.

  一台至少具有2 GB RAM的Ubuntu 16.04服务器，由具有sudo特权的非root用户和防火墙设置，如《 Ubuntu 16.04初始服务器设置指南》中所述 。

第1步-安装和设置MySQL服务器 (Step 1 — Installing and Setting Up the MySQL Server)

First, you will install MySQL server and configure it to be used by ProxySQL as a back-end server for serving client queries.

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

On Ubuntu 16.04, mysql-server can be installed using this command:

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

• sudo apt-get install mysql-server
  须藤apt-get install mysql-server

Press Y to confirm the installation.

按Y确认安装。

You will then be prompted for your MySQL root user password. Enter a strong password and save it for later use.

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

Now that you have your MySQL server ready, you will configure it for ProxySQL to work correctly. You need to add a monitor user for ProxySQL to monitor the MySQL server, since ProxySQL listens to the back-end server via the SQL protocol, rather than using a TCP connection or HTTP GET requests to make sure that the backend is running. monitor will use a dummy SQL connection to determine if the server is alive or not.

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

First, log in to the MySQL shell:

首先，登录到MySQL shell：

• mysql -uroot -p
  mysql -uroot -p

-uroot logs you in using the MySQL root user, and -p prompts for the root user’s password. This root user is different from your server’s root user, and the password is the one you entered when installing the mysql-server package.

-uroot记录您在使用MySQL root用户，并-p提示输入root用户的密码。 该root用户不同于服务器的root用户，密码是您在安装mysql-server软件包时输入的密码。

Enter the root password and press ENTER.

输入root密码，然后按ENTER 。

Now you will create two users, one named monitor for ProxySQL and another that you will use to execute client queries and grant them the right privileges. This tutorial will name this user sammy.

现在，您将创建两个用户，一个用户名为ProxySQL的监视器 ，另一个用户将用于执行客户端查询并为其授予正确的权限。 本教程将将此用户命名为sammy 。

Create the monitor user:

创建监视器用户：

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

  创建用户'monitor'@'％'由' monitor_password '标识；

The CREATE USER query is used to create a new user that can connect from specific IPs. Using % denotes that the user can connect from any IP address. IDENTIFIED BY sets the password for the new user; enter whatever password you like, but make sure to remember it for later use.

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

With the user monitor created, next make the sammy user:

创建了用户监视器后 ，接下来使该用户变得笨拙 ：

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

  创建用户' sammy '@'％'由' sammy_password '标识；

Next, grant privileges to your new users. Run the following command to configure monitor:

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

• GRANT SELECT ON sys.* TO 'monitor'@'%';
  SYS。*上的GRANT SEL