Блог

MySQL: Оптимизация работы

Во многих проектах чаще всего используют базу данных – MySQL, её быстрее и легче начать использовать из “коробки”. Когда проект начинает работать с большим объемом данных в базе, чаще всего нанимают со стороны специалистов по оптимизации запросов и настройке базы данных. В интернете существует не мало материала, по оптимизации. Большинство которых будут показаны ниже.

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

Первое что чаще всего интересует это оптимизация настроек сервера. Первое что нужно проверить включено ли кэширование запросов, при повторном запросе, система вернет ответ из кэша, вместо того что бы повторно выполнять данный запрос. Чтобы проверить включено ли кэширование: подключаемся к серверу баз данных и выполняем запрос SHOW VARIABLES LIKE ‘%query_cache%’;

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

Значение перемен:

  • have_query_cache говорит включено ли кэширование
  • query_cache_type описывает какой тип кэширования используется
    • OFF(0) – кэширвоание отключено,
    • ON(1) – кэширование включено для всех запросов (SELECT с опцией SQL_NO_CACHE не кешируются),
    • DEMAND(2) – позволяет активировать кэширование запросов по требованию, (SELECT с опцией SQL_CACHE).

Желательно проверить значение объема памяти выделяемое сервером под буфер индексов key_buffer_size в my.cnf, многие рекомендуют устанавливать это значение в 15-20% от размера оперативной памяти на сервере. key_buffer_size – переменная которая устанавливает размер буфера для индексных блоков. Значение данной переменной нужно увеличивать, для повышения эффективности обработки индексов, как при операциях чтения, так и записи. Чтобы увеличить размер буфера, открываем в текстовом редакторе файл для тех у кого Linux /etc/mysql/my.cnf – и правим key_buffer_size  = 64M.  Для того что бы настройки применились необходимо перезапустить сервер базы данных (Linux /etc/init.d/mysqld restart).

!! Примечании: у меня сервер базы данных версия Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu), key_buffer_size – key_buffer.

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

Чтобы оптимизировать тяжелый запрос, сначала его нужно исследовать. Для этого нужно дописать перед SELECT слово EXPLAIN, и MySQL покажет план выполнения запроса. В первую очередь интерес представляет информация об использовании индексов.

Пример работы оператора EXPLAIN

mysql> USE mysql;
mysql> EXPLAIN SELECT * FROM db;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys |  key | key_len |  ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1  | SIMPLE      | db    | ALL  | NULL          | NULL |    NULL | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

Индексы – это структуры данных, создаваемые с целью повышения производительности поиска записей в таблицах. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. [источник]

Индексы – ключ к высокой производительности MySQL, их важность увеличивается по мере роста объема данных в базе. Индексы нужно создавать для столбцов, по которым

  • производится поиск в части WHERE
  • соединяются таблицы при JOIN
  • сортируются и группируются записи при ORDER BY и GROUP BY
  • производится поиск MIN() и MAX()

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

  • type (значение ALL – плохо)
  • key (NULL – плохо)
  • ref (NULL – плохо)
  • extra (Using filesort, Using temporary, Using where – плохо)

Описание всех значений и пример оптимизации запроса можно посмотреть в документации. Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name).

Проверить эффективность существующих индексов в базе данных можно с помощью запроса SHOW STATUS LIKE ‘handler_read%’;

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 66    |
+-----------------------+-------+
7 rows in set (0.00 sec)

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

Журнал медленных запросов

На момент разработки мы используем много различных инструментов, которые помогают улучшить и оптимизировать выполнение продукта, но быть готовым ко всему, невозможно. Поэтому, логирование запросов очень полезный инструмент, мы можем в любой момент выяснить какие запросы больше всего замедляют работу продукта, чаще всего используются, и оптимизировать их. Для этого необходимо активировать логирование длинных запросов. Для включения логирования в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение (1/ON); для отключения журнала (0/OFF). В более старых версиях используется log-slow-queries = /var/www/logs/mysql-slow-queries.log (путь указываете свой). Ещё одна очень важная настройка – параметр long_query_time – время выполнения, при превышении заданного времени, запрос считается медленным и записывается в журнал. Начиная с MySQL 5.1.21 может задаваться в микросекундах и может быть равен нулю.

Есть ещё пара полезных дополнительных настроек:

  • log-queries-not-using-indexes – запись в журнал запросов, не использующих индексы.
  • slow_query_log_file – имя файла журнала. По умолчанию host_name-slow.log

Обновляем конфигурационный файл /etc/mysql/my.cnf в секции [mysqld]
long_query_time = 1
slow_query_log = /var/www/logs/mysql-slow-queries.log
slow_query_log_file = /var/db/mysql/slow_queries.log
log-queries-not-using-indexes = 1

Пример для старых версий MySQL, все запросы дольше 1 секунды:
log-slow-queries = /var/db/mysql/slow_queries.log
long_query_time = 1

Для анализа журнала используйте утилиту mysqldumpslow, обрабатывают журнал и выводят агрегированную информацию о медленных запросах. mysqldumpslow – утилита из состава MySQL.

Вызывается таким образом: mysqldumpslow [параметры] [файл_журнала ...] .

Пример:

mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1

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

Некоторые параметры mysqldumpslow:

  • -t N – отображать только первые N запросов.
  • -g pattern – анализировать только запросы, которые соответствуют шаблону (как grep).
  • -s sort_type – как сортировать вывод. Значения sort_type: t или at – сортировать по суммарному или среднему времени выполнения запросов, c – по количеству выполненных запросов данного типа.

 

Источники:
http://my.activecloud.com/ru/index.php?/Knowledgebase/Article/View/83/1/mysql
http://hosting101.ru/articles/mysql-performance-1.html#a-explain
http://mdex-nn.ru/page/slow-query-log-mysql.html
http://vds-admin.ru/mysql/sistemnye-peremennye-mysql-servera-fail-mycnf#have_query_cache