Система управления базами данных нужна компаниям разного масштаба: от игрового форума до решений в области здравоохранения. MySQL чрезвычайно важна для любого проекта, и почти перед каждым админом в какой-то момент встаёт задача её оптимизации. 

MySQL и инфраструктура среды

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

Начнём с изучения двух важных характеристик, которые нам понадобятся чуть позже. Первое: проверяем количество доступной оперативной памяти. Если используете Linux, примените команду free. Второе: выполнив операцию df", смотрим объём места на жёстком диске.

Запомнили два этих параметра, теперь подключаемся к MySQL. Проверяем, выполняются ли какие-либо ненужные запросы, применяя команду SHOW PROCESSLIST. Получаем что-то подобное:

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

Общие принципы оптимизации работы БД

Описанное выше не даёт сильного роста производительности. Чтобы сделать запросы быстрее, вы должны понимать, как работает MySQL в целом.

Чтобы повысить скорость работы проекта, необходимо оптимизировать CRUD операции — базовые запросы БД: создание (Create), чтение (Read), модификация (Update), удаление (Delete). В MySQL — это запросы: INSERT, SELECT, UPDATE и DELETE. Для ускорения производительности БД нужно сократить время отклика при выполнении запроса в базу данных. 

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

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

Чтобы INSERT работал быстрее, удаляем индексы. Чтобы SELECT работал быстрее, создаём индексы. 

Чтобы ускорить UPDATE, делаем батч-апдейт, то есть делаем много маленьких апдейтов вместо одного большого.

Чтобы оптимизировать работу DELETE, меняем команду DELETE на TRUNCATE. Последняя — убирает все строки или указанные секции в таблице, не записывая в журнал удаление отдельных строк. TRUNCATE требует меньших журналов транзакций и экономит ресурсы системы.

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

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

#1. Модификация файла my.cnf

Файл my.cnf содержит все необходимые параметры для работы MySQL. На него смотрим в первую очередь. 

Если сидите на Linux, файл my.cnf можно найти в одном из каталогов:

• /var/lib/mysql/my.cnf

• /etc/my.cnf

• /etc/mysql/my.cnf

• /usr/etc/my.cnf

Если используете Windows, поищите my.cnf в каталоге /bin/mysql/mysql *.*.*.

mysql *.`*.*`* — это версия вашего сервера MySQL.

Откройте файл и найдите параметры запуска InnoDB:

Все эти переменные связаны с InnoDB — одной из подсистем низкого уровня в СУБД MySQL, которая входит в стандартные сборки для разных ОС. Далее мы будем говорить про команды InnoDB. Скорее всего, вы используете именно этот движок, так как он дефолтный и самый быстрый из всех известных. 

Настройка параметров my.cnf

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

Параметр innodb-buffer-pool-size показывает размер памяти для кэширования данных и индексов таблиц InnoDB. Он должен составлять 50–60% от доступной оперативной памяти. Чем параметр больше, тем больше данных будет кэшироваться, и вставка данных будет происходить быстрее.

Значение innodb-data-file-path определяет путь, по которому хранится файл ibdata1. Это основной файл, связанный с InnoDB, где располагаются все необходимые данные. Увеличьте размер переменной innodb-data-file-path, чтобы она могла вместить все данные MySQL, — например, до 5–10 ГБ.

Если параметр innodb-file-per-table отсутствует, включите его и установите для него значение one. Это позволит хранить все таблицы в виде отдельных файлов, а размер буфера значительно уменьшится и будет содержать только метаданные.

Настройка innodb-flush-log-at-trx-commit определяет, как сбрасываются данные на диск. Значение по умолчанию 1 означает, что каждая транзакция должна сбрасывать буфер на диск. Это достаточно ресурсоёмко, зато соответствует требованиям ACID и безопасно. 

Стоит ли менять значение innodb-flush-log-at-trx-commit

Установка значения 2 у innodb_flush_log_at_trx_commit означает, что буфер журнала не сбрасывается на диск, а только в файловый кэш ОС при каждой фиксации транзакции. Это значение повышает производительность, если вы можете позволить себе потерять 1–2 секунды обновлений в случае аварии. 

Если значение innodb_flush_log_at_trx_commit равно 0, буфер журнала записывается и сбрасывается на диск один раз в секунду. Транзакции, журналы которых не были очищены, могут быть потеряны в результате сбоя.

Значение 0 повысит производительность, но вы рискуете потерять данные даже при аварийной остановке MySQL-сервера, в то время как при установке значения 2 вы потеряете данные только при аварии всей операционной системы или при отключении питания.

Параметр innodb-flush-method определяет метод записи данных из памяти на диск. Его модификация может привести к неожиданным и непредсказуемым последствиям, поэтому после изменений лучше проводить бенчмарки (тесты скорости). Самое распространенное решение — оставить параметр в значении O_DIRECT. Это предотвратит двойную буферизацию, когда данные одновременно кэшируются и самой ОС, и InnoDB.

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

#2 и #3. Проверка схемы и дизайна базы данных MySQL

Помимо настройки файла my.cnf, вы должны знать схему вашей базы данных, и как она спроектирована. Продолжим рассматривать всё на примере MySQL и движка InnoDB, но для других типов хранения применяется тот же принцип (например, если вы работаете с Percona Server, используйте Percona XtraDB).

Параметры InnoDB

Ранее мы установили значение буфера innodb-buffer-pool-size, равное 60% от объёма оперативной памяти. Теперь обратим внимание на innodb-log-file-size — это размер файлов журнала транзакций, которые нужны для восстановления базы при сбое. Значение этого параметра должно составлять четверть объёма, выделенного под буфер. Когда MySQL восстанавливает данные в InnoDB, она сканирует файлы журнала. Чем больше размер буфера, тем выше скорость восстановления. 

InnoDB поддерживает блокировку на уровне строк — когда изолируются только определённые строки таблицы, на которые влияет транзакция. Этот режим, в отличие от блокировки на уровне таблицы, позволяет работать со строками при обновлении данных.

Индексы колонок

Как минимум некоторые колонки таблицы, где вы используете SELECT запрос, уже могут быть проиндексированы. Индексы позволяют MySQL быстро находить данные на основе значений в этих столбцах. Это отличный способ ускорения выполнения операций SELECT. Поэтому проиндексируйте все колонки, которые идут после пункта WHERE, или хотя бы первый столбец, если хотите сэкономить место. 

Типы данных и целые числа

Чтобы сэкономить место на диске, используйте символьные типы данных CHAR (диапазон значений signed — от 128 до 127, для unsigned — от 0 до 255) или VARCHAR (переменный максимальный размер — от 1 до 65 535 символов) вместо текстовых — TEXT (фиксированный максимальный размер – 65 535 символов). 

То же самое и с целыми числами. Использование целочисленного типа данных SMALLINT вместо INT — также сбережёт место на жёстком диске.

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

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

#4 и #5. Индексы и разделы

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

Разделы ускоряют запросы SELECT: разбивают таблицы на более мелкие, начинающиеся с определенного символа, и выполняют запросы через них. 

Индексы ускоряют запросы SELECT с WHERE. Но индексы влияют на скорость обработки запросов INSERT, UPDATE и DELETE, так как вставляемые или изменяемые данные должны обновляться еще и внутри самого индекса или раздела.

Создание индексов

Самый распространенный тип индексов — B-дерево (B-tree) — лучше использовать со знаком равенства =.

Покрывающие индексы (covering indexes) в запросе включают все колонки, участвующие в запросе. Например, покрывающий индекс в столбцах a1, a2 и a3 соответствует следующему запросу:

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

Разбиение таблиц

MySQL поддерживает несколько типов разделения таблиц (partitioning).

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

HASH (разбиение по хэшу) делит таблицу на несколько подтаблиц в соответствии с количеством столбцов. Например, PARTITION BY HASH(id) PARTITIONS 8; разбивает таблицу на несколько разных подтаблиц на уровне базы данных с восемью разделами.

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

Разделение таблиц RANGE можно заменить LIST, HASH, или другим способом.

Партиционирование также позволяет пользователям удалять все данные из одного раздела с помощью команды ALTER TABLE demo TRUNCATE PARTITION partition_name.

Ещё немного лайфхаков

Запросы COUNT(*) выполняются быстрее, только при использовании механизма хранения MyISAM. Это единственное преимущество MyISAM, поскольку он сохраняет количество строк в своих метаданных. Никакие другие системы хранения данных этого не делают.

Для ускорения запросов SELECT с символами подстановки (wildcards) указывайте их в конце поискового запроса. 

Символы подстановки (wildcards) ускоряют SELECT, если ставить их в конце запроса. % лимитирует то, что нужно искать, и ускоряет выполнение операции. Вот как должен выглядеть запрос:

Индекс UNIQUE (уникальные записи) гарантирует уникальность значений в одном или нескольких столбцах. При вставке неуникального значения в уникальный индекс MySQL выдаст ошибку.

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

Иногда в MySQL нужно импортировать много данных (например, из CSV-файла) — в таких случаях лучше использовать не INSERT, а LOAD DATA INFILE. Этот запрос будет выполнен гораздо быстрее.

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

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

С какими сложностями можно столкнуться

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

Настройка my.cnf требует знаний о внутрянке Linux и наличия довольно мощного сервера. Вы не сможете повысить производительность, если оперативная память ограничивается 256 МБ, или у вас всего 2 ГБ дискового пространства.

Модификация my.cnf и работа с механизмами хранения потребуют от вас глубоких знаний пространства MySQL, а также понимания, что делает каждый параметр и какое у него значение.

В этом плане пользователям Windows попроще, так как my.ini — эквивалент my.cnf — содержит много комментариев , а вот пользователям Linux придётся разбираться с настройками самостоятельно.

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

Между 4 байтами и 8 байтами на символ будет огромная разница — это нужно иметь в виду, если дело касается большого набора данных. Для дополнительной информации следует обратиться к документации MySQL.

Индексы и разделы обычно ускоряют операции SELECT за счёт замедления всего остального, включая INSERT, UPDATE и DELETE, поскольку все эти запросы также должны вставлять, обновлять или удалять данные в индексах и разделах. Оба подхода могут быть полезными, если использовать их с умом.

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

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