Оптимизация запросов и таблиц MySQL и MariaDB

MySQL и MariaDB – это популярные системы управления базами данных (СУБД), использующие язык запросов SQL для ввода и извлечения данных.

В целом запросы SQL – простые команды, которые легко запомнить; однако не все запросы и функции БД работают с одинаковой продуктивностью. Этот вопрос становится все более важным по мере роста количества хранимой в БД информации.

Данное руководство охватывает некоторые простые способы оптимизации запросов MySQL и MariaDB.

Примечание: Предполагается, что MySQL или MariaDB уже установлены согласно инструкциям одного из руководств, которые можно найти в соответствующих разделах нашего Информатория.

Общая структура таблицы

Один из самых основных способов ускорить выполнение запросов касается структуры таблицы. То есть, при создании таблицы сначала необходимо найти наилучший способ организовать данные.

При этом нужно задать себе несколько вопросов.

Как в основном будет использоваться данная таблица?

Продумайте наперёд, как будут использоваться данные таблицы, чтобы определить наилучший способ проектирования структуры данных.

К примеру, данные, которые будут часто обновляться, лучше поместить в отдельную таблицу. В противном случае программа будет создавать кэш запросов для всей таблицы (внутренний кэш, поддерживаемый программой и обновляемый при появлении новой информации). Если обновляемые данные поместить в отдельную таблицу, то остальные (более постоянные) данные кэшировать не придётся.

Операции обновления, в целом, быстрее выполняются в небольших таблицах, а углубленный анализ сложных данных, как правило, лучше проходит в больших таблицах (так как операции слияния могут оказаться ресурсозатратными).

Какие типы данных будут храниться в таблице?

Иногда заранее установленные ограничения для типов данных могут в дальнейшем сэкономить немало времени.

К примеру, если в БД предварительно установлен лимит валидных записей для определённого поля значений, используйте тип enum вместо varchar. Этот тип данных является более компактным и, таким образом, быстро извлекается в случае запроса.

Например, если в системе есть несколько различных видов пользователей, можно создать отдельный столбец, который обрабатывает данные enum с возможными значениями: admin, moderator, poweruser, user и т.п.

Какие столбцы будут чаще запрашиваться?

Зная заранее, какие поля или столбцы будут неоднократно запрашиваться, можно значительно улучшить скорость обработки запроса.

При этом очень помогает индексация столбцов, которые будут часто использоваться для поиска. Индекс можно добавить при создании таблицы, используя следующий синтаксис:

CREATE TABLE example_table (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
address VARCHAR(150),
username VARCHAR(16),
PRIMARY KEY (id),
INDEX (username)
);

Такой синтаксис будет полезен, если пользователи часто запрашивают информацию по имени пользователя. Это создаст следующую таблицу:

explain example_table;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | YES  |     | NULL    |                |
| address  | varchar(150) | YES  |     | NULL    |                |
| username | varchar(16)  | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Как видите, для таблицы создано два индекса. Первый – это первичный ключ (в данном случае idfield). Второй был добавлен для поля username. Это позволит ускорить запросы, которые используют это поле.

Конечно, с практической точки зрения лучше заранее продумать, какие поля нужно проиндексировать. Однако можно также добавить индексы для уже существующих таблиц. Для этого используется синтаксис:

CREATE INDEX index_name ON table_name(column_name);

Также можно использовать синтаксис:

ALTER TABLE table_name ADD INDEX ( column_name );

Функция explain

Если запросы к БД довольно предсказуемы, проанализируйте запросы и создайте индексы для всех возможных столбцов. Для этого можно использовать функцию explain.

Для примера импортируйте образец БД MySQL:

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -u root -p -t < employees.sql

Откройте сессию MySQL, чтобы создать несколько запросов:

mysql -u root -p
use employees;

Сначала нужно отключить использование кэша MySQL, чтобы определить точное время выполнения этих задач.

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

Запустите простой запрос большого объёма данных:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.60 sec)

Чтобы просмотреть, как MySQL выполняет запрос, добавьте ключевое слово explain перед запросом:

EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Обратите внимание, поле key имеет значение NULL. Значит, для этого поля не используется индекс.

Добавьте индекс для key и снова запустите запрос:

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.14 sec)

Как видите, это значительно улучшает обработку запросов.

Также при использовании индексов следует обратить внимание на слияние таблиц mysql. При этом нужно создать индексы и указать тот же тип данных для всех столбцов, которые будут использоваться для слияния.

К примеру, у вас есть таблица по имени cheeses, и есть таблица ingredients. Эти таблицы можно объединить при помощи поля ingredient_id. Чтобы ускорить слияние таблиц, нужно создать индексы для этих полей.

Оптимизация запросов

Второй способ ускорить обработку запросов – это оптимизировать сами запросы. Некоторые операции требуют более интенсивных вычислений, чем другие. Как правило, получить один и тот же результат можно несколькими способами, некоторые из которых позволят сэкономить ресурсы и время.

В зависимости от цели использования запросов вам может понадобиться ограниченное количество результатов. К примеру, чтобы выяснить, зарабатывает ли кто-нибудь в компании меньше, чем $ 40000, можно использовать:

SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10022 |  39935 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

Такой запрос выполняется очень быстро, поскольку он останавливается на первом положительном результате.

Если в запросах используется оператор or, а два компонента относятся к разным полям, такие запросы могут обрабатываться дольше.

К примеру, чтобы найти работников, чьё имя или фамилии начинается на Bre, нужно запросить два разных столбца.

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

Эту операцию можно ускорить, если создать отдельный запрос для имени, отдельный – для фамилии, а затем объединить выход. Это можно сделать с помощью оператора union:

SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';

Иногда MySQL использует оператор union автоматически (как в приведённом выше примере). Чтобы узнать, использует ли MySQL этот оператор, обратитесь к функции explain.

Заключение

Существует огромное количество способов настроить таблицы и базы данных MySQL и MariaDB. Эта статья содержит всего несколько основных подходов, которые могут быть полезны для начала.

Эти СУБД имеют подробную документацию, в которой также можно найти советы по оптимизации и тонкой настройке запросов. Специфика такой отладки в значительной степени зависит от того, какую функциональность нужно оптимизировать. Определившись с требованиями и выяснив, какие операции будут часто выполняться, вы можете подогнать параметры этих запросов.

Tags: ,

Добавить комментарий