MySQL по-прежнему является самой популярной в мире реляционной базой данных, но в то же время и наиболее не оптимизированной. Многие люди остаются с настройками по умолчанию, не "копая" глубже. В этой статье мы рассмотрим некоторые советы по оптимизации MySQL в сочетании с некоторыми новинками, которые вышли относительно недавно.

Оптимизация конфигурации

Первое, что каждый пользователь MySQL должен сделать для повышения производительности - это настроить конфигурацию. Однако, большинство этот шаг пропускают. В 5.7 (текущая версия) настройки по умолчанию стали намного лучше, чем у её предшественников, но улучшить их по-прежнему можно и несложно.

Мы надеемся, что вы используете Linux или что-то вроде Vagrant-box (как наш Homestead Improved), а, соответственно, ваш конфигурационный файл будет находиться в /etc/mysql/my.cnf. Вполне возможно, что ваша установка на самом деле будет подгружать дополнительный файл конфигурации в этот. Так что посмотрите, если файл my.cnf содержит немного, то посмотрите в  /etc/mysql/mysql.conf.d/mysqld.cnf.

Ручной тюнинг

Следующие настройки должны быть сделаны "из коробки". Согласно этим советам, добавьте в файл конфига в раздел [mysqld]:

innodb_buffer_pool_size = 1G # (здесь поменяйте примерно 50%-70% от общего объема оперативы)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # можно поменять на 2 или 0
innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size. Пул буферизации (buffer pool) является эдаким "складом" для кэширования данных и индексов в памяти. Он используется, чтобы хранить часто используемые данные в памяти. И когда вы используете выделенный или виртуальный сервер, на котором зачастую именно БД является узким местом, то есть смысл отдать ей бОльшую часть оперативы. Следовательно, мы даем ей 50-70% всей RAM. В документации MySQL есть руководство по настройке этого пула.
• innodb_log_file_size. Настройка размера лог-файла хорошо описана здесь, но в двух словах это количество хранимых данных в логах, прежде чем его почистят. Обратите внимание, что лог в этом случае – это не записи об ошибках, а некий дельта-слепок изменений, которые ещё не были сброшены на диск в основные файлы innodb. MySQL пишет в фоновом режиме, но это все же влияет на производительность в момент записи. Большой лог-файл означает более высокую производительность из-за малого количества создаваемых новых и небольших контрольных точек, но при этом более длительное время восстановления в случае краша (больше данных должно быть переписано в БД).
• innodb_flush_log_at_trx_commit описан тут и показывает, что происходит с файлом логов. Значение 1 – самое безопасное, т. к. лог сбрасывается на диск после каждой транзакции. При значениях 0 и 2 – меньше гарантируется ACID, но больше производительность. Разница не является достаточно большой, чтобы перевесить преимущества стабильности при 1.
• innodb_flush_method. В довершение всего того, что касается сброса данных, эту настройку нужно установить в O_DIRECT – чтобы избежать двойной буферизации. Советую всегда это делать, пока система ввода-вывода остаётся очень медленной. Хотя на большинстве хостингах, типа DigitalOcean, вы будете иметь SSD-диски, поэтому система ввода-вывода будет более производительна.

Есть инструмент от Percona, который поможет нам найти оставшиеся проблемы автоматически. Обратите внимание, что если мы бы запустили его без этой ручной настройки, то только 1 из 4 настроек была бы определена, т. к. другие 3 зависят от предпочтений пользователя и окружающей среды приложения.

Variable Inspector

Установка variable inspector на Ubuntu:

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

Для других систем, следуйте этим инструкциям.

Затем запустите toolkit:

pt-variable-advisor h=localhost,u=homestead,p=secret

Вы увидите такой результат:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

Прим. переводчика:
На моей локальной машине, кроме этого, выдал ещё вот такой ворнинг:

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.

О том, что параметр innodb_flush_method нужно установить в O_DIRECT и почему говорилось выше. И если вы придерживались последовательности тюнинга как в статье, то вы не увидите это предупреждение.

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

Примечание: в новых версиях размер binlog-а по умолчанию 1G и этого ворнинга не будет.

max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'

• max_binlog_size. Определяет насколько большими будут бинарные логи. В них записываются ваши транзакции и запросы и делаются контрольные точки. Если транзакция превышает максимум, то лог может превышать свой размер при сохранении на диск; в противном случае MySQL будет поддерживать его в рамках этого лимита.
• log_bin. Эта опция включает запись бинарных логов в целом. Без неё невозможны снапшоты или репликации. Обратите внимание, что это может очень сказаться на дисковом пространстве. server-id – это необходимая опция при включении бинарного лога, поэтому логи "знают" с какого сервера они пришли (для репликации), а binlog-format - это просто способ, которым они записываются.

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

MySQL Tuner

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

Установить его просто. Нужно просто скачать его:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

При запуске ./mysqltuner.pl он спросит вас имя пользователя и пароль администратора базы данных и выведет информацию быстрого сканирования. Например, вот мой раздел InnoDB:

[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/11.2M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 96.65% (19146 hits/ 19809 total)
[!!] InnoDB Write Log efficiency: 83.88% (640 hits/ 763 total)
[OK] InnoDB log waits: 0.00% (0 waits / 123 writes)

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

Убедитесь, что вы перезапустили MySQL после любого изменения конфигурации:

sudo service mysql restart

Индексы

Далее, обратим внимание на индексы – главная болевая точка многих админов БД любителей! Особенно тех, кто сразу стал использовать ORM и никогда не нюхал чистого SQL.

Примечание: термины ключи и индексы могут быть использованы как взаимозаменяемые.

Вы можете сравнить индексы MySQL с оглавлением в книге, которое позволяет вам легко найти нужную страницу, которая содержит информацию, которую вы ищете. Если нет никаких индексов, вам придется пролистывать всю книгу в поиске нужной страницы.

Как вы можете себе представить, найти по оглавлению намного быстрее, чем пролистывать каждую страницу. Таким образом, добавление индексов в базу в целом ускоряет select-запросы. Однако, этот индекс должен быть создан и сохранен. А значит, запросы update и insert будут медленнее и это займёт немного больше места на диске. В целом вы не заметите разницы при обновлениях и вставках, если вы проиндексировали вашу таблицу правильно, а поэтому важно добавлять индексы в нужные места.

Таблицы, которые содержат всего несколько строк, на самом деле нет смысла индексировать. Вы можете себе представить, что пролистать 5 страниц - это ненамного медленнее, чем сначала сходить в оглавление, получить номер страницы, а затем открыть эту страницу.

Так как же мы узнаем, какие индексы нужно добавить, и какие виды индексов существуют?

Уникальные/первичные индексы

Первичные индексы являются основными индексами, которые используются по умолчанию при поиске по данным. Для учетной записи пользователя это могут быть идентификатор пользователя, или логин, или даже основной email. Первичные индексы являются уникальными. Уникальные индексы – это индексы, которые не могут иметь повторов на всём наборе данных.

Например, если пользователь выбрал конкретный username, никто больше не может выбрать его. Добавление уникального индекса на столбец username  решает эту проблему. MySQL будет "ругаться", если кто-то повторно попытается вставить строку с именем пользователя (username), которое уже существует.

...
ALTER TABLE `users` 
ADD UNIQUE INDEX `username` (`username`);
...

Первичные ключи/индексы, как правило, задаются при создании таблицы, а уникальные индексы позднее через изменение (ALTER) таблицы.

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

...
ALTER TABLE `users`
ADD UNIQUE INDEX `usercountry` (`username`, `country`),
...

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

Регулярные индексы

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

...
ALTER TABLE `users`
ADD INDEX `usercountry` (`username`, `country`),
...

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

Индексы также помогают увеличить скорость сортировок и группировок.

Полнотекстовые индексы

Полнотекстовые индексы (FULLTEXT) используются для полнотекстового поиска. Их поддерживают только InnoDB и MyISAM и только для столбцов с типами CHAR, VARCHAR и TEXT.

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

Обратные индексы (по убыванию)

Начиная с версии 8+, MySQL поддерживает обратные индексы, что означает, что он может хранить индексы в порядке убывания. Это может пригодиться, когда у вас есть огромные таблицы, из которых чаще всего нужны последние добавленные данные. Конечно, всегда можно отсортировать по убыванию, но это будет немного медленнее. А вот это еще больше ускорит.

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

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

Вспомогательные инструменты: Explain

Если вы смотрите в сторону оптимизации запросов, инструмент EXPLAIN будет бесценен. Запрос, впереди которого вы просто поставили EXPLAIN, будет обрабатываться не как запрос, а как анализ его исполнения. MySql отобразит вам анализ используемых индексов и покажет вам соотношение попаданий и промахов. А также сколько строк он должен пробежать/сравнить, чтобы получить результаты, которые вы ищете.

EXPLAIN SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Вы можете расширить отчёт с помощью EXTENDED:

EXPLAIN EXTENDED SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Подробнее вы можете узнать в документации.

Вспомогательные инструменты: Percona Toolkit для выявления дублирующих индексов

Ранее установленный нами Percona Toolkit также имеет инструмент для обнаружения дублирующих индексов, который может пригодиться при использовании сторонних CMS или просто проверить себя – вдруг вы случайно добавили больше индексов, чем нужно. Например, установка WordPress по умолчанию имеет дублирующие индексы в таблице wp_posts:

pt-duplicate-key-checker h=localhost,u=homestead,p=secret

# ########################################################################
# homestead.wp_posts
# ########################################################################

# Key type_status_date ends with a prefix of the clustered index
# Key definitions:
#   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
#   PRIMARY KEY (`ID`),
# Column types:
#      `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default 'post'
#      `post_status` varchar(20) collate utf8mb4_unicode_520_ci not null default 'publish'
#      `post_date` datetime not null default '0000-00-00 00:00:00'
#      `id` bigint(20) unsigned not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

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

Вспомогательные инструменты: Percona Toolkit для неиспользуемых индексов

Percona Toolkit может также обнаружить неиспользуемые индексы. Если вы логируете медленные запросы (см. раздел "узкие места" ниже), вы можете запустить утилиту и она будет проверять, используют ли эти запросы индексы в таблицах и как именно.

pt-index-usage /var/log/mysql/mysql-slow.log

Подробную информацию об использовании этой утилиты см. здесь.

Узкие места

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

Для начала, давайте включим логирование медленных запросов:

slow_query_log  = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1

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

Как только в этом логе появятся некоторые данные, вы можете проанализировать их на предмет использования индексов с помощью вышеуказанной утилиты pt-index-usage или с помощью pt-query-digest, которая выведет примерно такие результаты:

pt-query-digest /var/log/mysql/mysql-slow.log

# 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz
# Current date: Thu Feb 13 22:39:29 2014
# Hostname: *
# Files: mysql-slow.log
# Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________
# Time range: 2014-02-13 22:23:52 to 22:23:59
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            3ms   267us   406us   343us   403us    39us   348us
# Lock time          827us    88us   125us   103us   119us    12us    98us
# Rows sent             36       1      15    4.50   14.52    4.18    3.89
# Rows examine          87       4      30   10.88   28.75    7.37    7.70
# Query size         2.15k     153     296  245.11  284.79   48.90  258.32
# ==== ================== ============= ===== ====== ===== ===============
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x728E539F7617C14D  0.0011 41.0%     3 0.0004  0.00 SELECT blog_article
#    2 0x1290EEE0B201F3FF  0.0003 12.8%     1 0.0003  0.00 SELECT portfolio_item
#    3 0x31DE4535BDBFA465  0.0003 12.6%     1 0.0003  0.00 SELECT portfolio_item
#    4 0xF14E15D0F47A5742  0.0003 12.1%     1 0.0003  0.00 SELECT portfolio_category
#    5 0x8F848005A09C9588  0.0003 11.8%     1 0.0003  0.00 SELECT blog_category
#    6 0x55F49C753CA2ED64  0.0003  9.7%     1 0.0003  0.00 SELECT blog_article
# ==== ================== ============= ===== ====== ===== ===============
# Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______
# Scores: V/M = 0.00
# Time range: all events occurred at 2014-02-13 22:23:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         37       3
# Exec time     40     1ms   352us   406us   375us   403us    22us   366us
# Lock time     42   351us   103us   125us   117us   119us     9us   119us
# Rows sent     25       9       1       4       3    3.89    1.37    3.89
# Rows examine  24      21       5       8       7    7.70    1.29    7.70
# Query size    47   1.02k     261     262  261.25  258.32       0  258.32
# String:
# Hosts        localhost
# Users        *
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'blog_article'\G
#    SHOW CREATE TABLE `blog_article`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

Если вы предпочитаете анализировать эти логи вручную, вы можете сделать то же самое, но сначала вам нужно экспортировать лог в более анализируемый формат. Это можно сделать так:

mysqldumpslow /var/log/mysql/mysql-slow.log

С дополнительными параметрами можно отфильтровать данные, чтобы экспортировать только нужное. Например, топ-10 запросов, отсортированных по среднему времени выполнения:

mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log

Остальные параметры см. в документации.

Заключение

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

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