Кэш запросов — важная функция MySQL , которая ускоряет извлечение данных из базы данных. Это достигается путем хранения SELECTоператоров MySQL вместе с извлеченным набором записей в памяти, а затем, если клиент запрашивает идентичные запросы, он может быстрее обслуживать данные без повторного выполнения команд из базы данных.
По сравнению с данными, считанными с диска, кэшированные данные из ОЗУ (оперативной памяти) имеют более короткое время доступа, что снижает задержку и улучшает операции ввода-вывода (I/O). Например, для сайта WordPress или портала электронной коммерции с большим числом вызовов чтения и нечастыми изменениями данных кэш запросов может значительно повысить производительность сервера базы данных и сделать его более масштабируемым.
В этом руководстве вы сначала настроите MySQL без кеша запросов и запустите запросы, чтобы увидеть, насколько быстро они выполняются. Затем вы настроите кэш запросов и протестируете свой сервер MySQL с его включением, чтобы увидеть разницу в производительности.
Примечание. Несмотря на то, что кеш запросов устарел, начиная с MySQL 5.7.20, и удален в MySQL 8.0, он по-прежнему является мощным инструментом, если вы используете поддерживаемые версии MySQL. Однако, если вы используете более новые версии MySQL, вы можете использовать альтернативные сторонние инструменты, такие как ProxySQL , для оптимизации производительности вашей базы данных MySQL.
Предпосылки
Прежде чем начать, вам понадобится следующее:
-
Один сервер Ubuntu 18.04, настроенный с брандмауэром и пользователем без полномочий root.
-
Сервер MySQL настроен. Убедитесь, что вы установили пароль root для сервера MySQL.
Шаг 1 — Проверка доступности кэша запросов
Перед настройкой кэша запросов проверьте, поддерживает ли ваша версия MySQL эту функцию. Во- первых, sshна ваш сервер Ubuntu 18.04:
ssh user_name@your_server_ip
Затем выполните следующую команду, чтобы войти на сервер MySQL в качестве пользователя root:
sudo mysql -u root -p
Введите корневой пароль сервера MySQL, когда будет предложено, а затем нажмите ENTER, чтобы продолжить.
Используйте следующую команду, чтобы проверить, поддерживается ли кэш запросов:
show variables like 'have_query_cache';
Вы должны получить вывод, подобный следующему:
Output
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.01 sec)
Вы можете видеть, что установлено значение have_query_cache, YESи это означает, что кеш запросов поддерживается. Если вы получаете вывод, показывающий, что ваша версия не поддерживает кеш запросов, см. примечание в разделе «Введение» для получения дополнительной информации.
Теперь, когда вы проверили и подтвердили, что ваша версия MySQL поддерживает кеширование запросов, вы перейдете к изучению переменных, управляющих этой функцией на вашем сервере базы данных.
Шаг 2 — Проверка переменных кэша запросов по умолчанию
В MySQL несколько переменных управляют кешем запросов. На этом шаге вы проверите значения по умолчанию, поставляемые с MySQL, и поймете, что контролирует каждая переменная.
Вы можете проверить эти переменные с помощью следующей команды:
show variables like 'query_cache_%' ;
Вы увидите переменные, перечисленные в вашем выводе:
Output
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.00 sec)
Значение query_cache_limit определяет максимальный размер отдельных результатов запроса, которые можно кэшировать. Значение по умолчанию — 1 048 576 байт, что эквивалентно 1 МБ.
MySQL не обрабатывает кэшированные данные одним большим куском; вместо этого он обрабатывается блоками. Минимальный объем памяти, выделяемой каждому блоку, определяется query_cache_min_res_unitпеременной. Значение по умолчанию — 4096 байт или 4 КБ.
query_cache_sizeуправляет общим объемом памяти, выделенной для кэша запросов. Если значение равно нулю, это означает, что кеширование запросов отключено. В большинстве случаев значение по умолчанию может быть установлено на 16 777 216 (около 16 МБ). Кроме того, имейте в виду, что query_cache_sizeдля размещения его структур требуется не менее 40 КБ. Выделенное здесь значение выравнивается по ближайшему 1024-байтовому блоку. Это означает, что сообщаемое значение может немного отличаться от того, что вы установили.
MySQL определяет запросы для кэширования, исследуя query_cache_typeпеременную. Установка этого значения в 0или OFFзапрещает кэширование или извлечение кэшированных запросов. Вы также можете установить его, 1чтобы включить кэширование для всех запросов, кроме тех, которые начинаются с инструкции SELECT SQL_NO_CACHE. Значение 2 указывает MySQL кэшировать только те запросы, которые начинаются с SELECT SQL_CACHEкоманды.
Переменная query_cache_wlock_invalidateопределяет, должен ли MySQL извлекать результаты из кэша, если таблица, используемая в запросе, заблокирована. Значение по умолчанию равно OFF.
Примечание. Эта query_cache_wlock_invalidateпеременная устарела, начиная с версии MySQL 5.7.20. В результате вы можете не увидеть это в своем выводе в зависимости от используемой версии MySQL.
Изучив системные переменные, которые управляют кэшем запросов MySQL, вы теперь проверите, как работает MySQL без предварительного включения этой функции.
Шаг 3 — Тестирование вашего сервера MySQL без кэша запросов
Целью этого руководства является оптимизация вашего сервера MySQL с помощью функции кэширования запросов. Чтобы увидеть разницу в скорости, вы будете запускать запросы и наблюдать за их производительностью до и после реализации функции.
На этом шаге вы создадите образец базы данных и вставите некоторые данные, чтобы увидеть, как MySQL работает без кэширования запросов.
Пока вы все еще находитесь на своем сервере MySQL, создайте базу данных и назовите ее sample_db, выполнив следующую команду:
Create database sample_db;
Output
Query OK, 1 row affected (0.00 sec)
Затем переключитесь на базу данных:
Use sample_db;
Output
Database changed
Создайте таблицу с двумя полями ( customer_idи customer_name) и назовите ее customers:
Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
Output
Query OK, 0 rows affected (0.01 sec)
Затем выполните следующие команды, чтобы вставить некоторые образцы данных:
Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Output
Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...
Следующим шагом является запуск профилировщика MySQL , который представляет собой аналитический сервис для мониторинга производительности запросов MySQL. Чтобы включить профиль для текущего сеанса, выполните следующую команду, установив для нее значение 1, которое включено:
SET profiling = 1;
Output
Query OK, 0 rows affected, 1 warning (0.00 sec)
Затем выполните следующий запрос, чтобы получить всех клиентов:
Select * from customers;
Вы получите следующий вывод:
Output
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | JANE DOE |
| 2 | JANIE DOE |
| 3 | JOHN ROE |
| 4 | MARY ROE |
| 5 | RICHARD ROE |
| 6 | JOHNNY DOE |
| 7 | JOHN SMITH |
| 8 | JOE BLOGGS |
| 9 | JANE POE |
| 10 | MARK MOE |
+-------------+---------------+
10 rows in set (0.00 sec)
Затем запустите SHOW PROFILESкоманду, чтобы получить информацию о производительности SELECTтолько что выполненного запроса:
SHOW PROFILES;
Вы получите вывод, аналогичный следующему:
Output
+----------+------------+-------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------+
| 1 | 0.00044075 | Select * from customers |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
Вывод показывает общее время, затраченное MySQL на получение записей из базы данных. Вы собираетесь сравнивать эти данные на следующих шагах, когда кеш запросов включен, поэтому запишите свой файл Duration. Вы можете игнорировать предупреждение в выходных данных, так как это просто указывает, что SHOW PROFILESкоманда будет удалена в будущем выпуске MySQL и заменена Performance Schema .
Затем выйдите из интерфейса командной строки MySQL.
quit;
Вы выполнили запрос с MySQL до включения кэширования запросов и записали Durationвремя, затраченное на получение записей. Затем вы включите кеш запросов и посмотрите, есть ли прирост производительности при выполнении того же запроса.
Шаг 4 — Настройка кэша запросов
На предыдущем шаге вы создали образец данных и выполнили SELECTоператор до того, как включили кеширование запросов. На этом шаге вы включите кеш запросов, отредактировав файл конфигурации MySQL.
Используйте nanoдля редактирования файла:
sudo nano /etc/mysql/my.cnf
Добавьте в конец файла следующую информацию:
/etc/mysql/my.cnf
...
[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K
Здесь вы включили кеш запросов, установив query_cache_typeзначение 1. Вы также установили предельный размер отдельного запроса 256Kи указали MySQL выделять мегабайты для кэша запросов, установив 10значение . query_cache_size10M
Сохраните и закройте файл, нажав CTRL+ X, Y, затем ENTER. Затем перезапустите сервер MySQL, чтобы изменения вступили в силу:
sudo systemctl restart mysql
Теперь вы включили кеширование запросов.
После того, как вы настроили кэш запросов и перезапустили MySQL, чтобы применить изменения, вы продолжите и протестируете производительность MySQL с включенной функцией.
Шаг 5 — Тестирование вашего сервера MySQL с включенным кэшем запросов
На этом шаге вы еще раз запустите тот же запрос, что и на шаге 3, чтобы проверить, как кэш запросов оптимизировал производительность вашего сервера MySQL.
Сначала подключитесь к серверу MySQL как пользователь root :
sudo mysql -u root -p
Введите пароль root для сервера базы данных и нажмите ENTER, чтобы продолжить.
Теперь подтвердите свою конфигурацию, установленную на предыдущем шаге, чтобы убедиться, что вы включили кеш запросов:
show variables like 'query_cache_%' ;
Вы увидите следующий вывод:
Output
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 262144 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.01 sec)
Переменная query_cache_typeустановлена в ON; это подтверждает, что вы включили кеш запросов с параметрами, определенными на предыдущем шаге.
Переключитесь на sample_dbбазу данных, которую вы создали ранее.
Use sample_db;
Запустите профилировщик MySQL:
SET profiling = 1;
Затем выполните запрос для получения всех клиентов не менее двух раз, чтобы получить достаточно информации для профилирования.
Помните, что как только вы запустите первый запрос, MySQL создаст кеш результатов, и поэтому вы должны запустить запрос дважды, чтобы активировать кеш:
Select * from customers;
Select * from customers;
Затем перечислите информацию о профилях:
SHOW PROFILES;
Вы получите вывод, подобный следующему:
Output
+----------+------------+-------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------+
| 1 | 0.00049250 | Select * from customers |
| 2 | 0.00026000 | Select * from customers |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)
Как видите, время, затрачиваемое на выполнение запроса, резко сократилось с 0.00044075(без кеша запросов на шаге 3) до 0.00026000(второго запроса) на этом шаге.
Вы можете увидеть оптимизацию от включения функции кеша запросов, подробно профилируя первый запрос:
SHOW PROFILE FOR QUERY 1;
Output
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000025 |
| Waiting for query cache lock | 0.000004 |
| starting | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions | 0.000008 |
| Opening tables | 0.000014 |
| init | 0.000018 |
| System lock | 0.000008 |
| Waiting for query cache lock | 0.000002 |
| System lock | 0.000018 |
| optimizing | 0.000003 |
| statistics | 0.000013 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 0.000048 |
| end | 0.000004 |
| query end | 0.000006 |
| closing tables | 0.000006 |
| freeing items | 0.000006 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000213 |
| Waiting for query cache lock | 0.000019 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000003 |
| cleaning up | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)
Выполните следующую команду, чтобы отобразить информацию о профиле для второго кэшированного запроса:
SHOW PROFILE FOR QUERY 2;
Output
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000024 |
| Waiting for query cache lock | 0.000003 |
| starting | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached | 0.000003 |
| checking permissions | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)
Выходные данные профилировщика показывают, что MySQL потребовалось меньше времени на второй запрос, потому что он смог получить данные из кэша запросов, а не читать их с диска. Вы можете сравнить два набора выходных данных для каждого из запросов. Если вы посмотрите на информацию профиля на QUERY 2, статус sending cached result to clientпоказывает, что данные были прочитаны из кеша, и таблицы не были открыты, так как Opening tablesстатус отсутствует.
Теперь, когда на вашем сервере включена функция кэширования запросов MySQL, вы почувствуете повышенную скорость чтения.
Вывод
Вы настроили кэш запросов, чтобы ускорить работу сервера MySQL в Ubuntu 18.04. Использование таких функций, как кеш запросов MySQL, может повысить скорость вашего веб-сайта или веб-приложения. Кэширование уменьшает ненужное выполнение операторов SQL и является настоятельно рекомендуемым и популярным методом оптимизации вашей базы данных.