Оптимизация запросов MySQL. Вспомогательные инструменты: Percona Toolkit для неиспользуемых индексов
От автора: один мой знакомый решил оптимизировать свой автомобиль. Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода! Поэтому, чтобы ваша СУБД продолжала «ездить», оптимизация MySQL должна проходить правильно.
Когда оптимизировать и зачем?
Лишний раз лезть в настройки сервера и изменять значения параметров (особенно, если не знаете, чем это может закончиться) не стоит. Если рассматривать данную тему с «колокольни» улучшения производительности веб-ресурсов, то она настолько обширная, что ей нужно посвящать целое научное издание в 7 томах.
Но такого писательского терпения у меня явно нет, да и у вас читательского тоже. Мы поступим проще, и постараемся лишь слегка углубиться в чащи оптимизации MySQL сервера и его составляющих. С помощью оптимальной установки всех параметров СУБД можно достигнуть нескольких целей:
Увеличить скорость выполнения запросов.
Повысить общую производительность сервера.
Уменьшить время ожидания загрузки страниц ресурса.
Снизить потребление серверных мощностей хостинга.
Снизить объем занимаемого дискового пространства.
Постараемся всю тематику оптимизации разбить на несколько пунктов, чтоб было более-менее понятно, от чего «котелок» закипает .
Зачем настраивать сервер
В MySQL оптимизацию производительности следует начинать с сервера. Прежде всего, следует ускорить его работу и уменьшить время обработки запросов. Универсальным средством для достижения всех перечисленных целей является включения кэширования. Не знаете, «what is it»? Сейчас все поясню.
Если на вашем экземпляре сервера включено кэширование, то система MySQL автоматически «запоминает» введенный пользователем запрос. И в следующий раз при его повторении данный результат запроса (на выборку) будет не обработан, а взят из памяти системы. Получается, что таким образом сервер «экономит» время на выдачу ответа, и вследствие чего скорость реагирования сайта повышается. В том числе это касается и общей скорости загрузки.
В MySQL оптимизация запросов применима к тем движкам и CMS, которые работают на основе данной СУБД и PHP. При этом код, написанный на языке программирования, для генерации динамической веб-страницы запрашивает некоторые ее структурные части и содержимое (записи, архивы и другие таксономии) из БД.
Благодаря включенному кэшированию в MySQL выполнение запросов к серверу СУБД происходит намного быстрее. За счет чего и повышается скорость загрузки всего ресурса в целом. А это положительно отражается и на пользовательском опыте, и на позиции сайта в выдаче.
Включаем и настраиваем кэширование
Но давайте вернемся от «скучной» теории к интересной практике. Дальнейшую оптимизацию базы MySQL продолжим с проверки состояния кэширования на вашем сервере БД. Для этого с помощью специального запроса мы выведем значения всех системных переменных:
Совсем другое дело.
Сделаем маленький обзор полученных значений, которые пригодятся нам для оптимизации баз данных MySQL:
have_query_cache – значение показывает «ВКЛ» кэширование запросов или нет.
query_cache_type – отображает активный тип кэша. Нам нужно значение «ON». Это говорит о том, что кэширование включено для всех видов выборки (команда SELECT). Кроме тех, в которых используется параметр SQL_NO_CACHE (запрещает сохранение информации об этом запросе).
У нас все настройки заданы правильно.
Отмеряем кэш под индексы и ключи
Теперь нужно проверить, сколько отведено оперативной памяти под индексы и ключи. Рекомендуется устанавливать этот важный для оптимизации БД MySQL параметр на 20-30% от объема оперативки, доступной для сервера. Например, если под экземпляр СУБД выделено 4 «гектара», то смело ставьте 32 «метра». Но все зависит от особенностей определенной базы и ее структуры (типов) таблиц.
Для установки значения параметра нужно отредактировать содержимое конфигурационного файла my.ini, который в Денвере находится по следующему пути: F:\Webserver\usr\local\mysql-5.5
Файл открываем с помощью Блокнота. Затем находим в нем параметр key_buffer_size и устанавливаем оптимальный для вашей системы ПК (в зависимости от «гектаров» оперативки) размер. После этого нужно перезапустить сервер БД.
В СУБД используется несколько дополнительных подсистем (нижнего уровня), и все основные их настройки также задаются в данном файле конфигурации. Поэтому, если нужно провести в MySQL InnoDB оптимизацию, то добро пожаловать сюда. Более подробно эту тему мы изучим в одном из наших следующих материалов.
Измеряем уровень индексов
Использование индексов в таблицах значительно повышает скорость обработки и формирования ответа СУБД на введенный запрос. MySQL постоянно «измеряет» уровень применения индексов и ключей в каждой БД. Для получения данного значения используйте запрос:
SHOW STATUS LIKE "handler_read%"
SHOW STATUS LIKE "handler_read%" |
В полученном результате нас интересует значение в строке Handler_read_key. Если указанное там число маленькое, то это говорит о том, что индексы почти не используются в данной базе. А это плохо (как у нас ).
→ Оптимизация запросов MySQL
MySQL располагает большим набором функций для различных сортировок (ORDER BY ), группировок (GROUP BY ), объединений (LEFT JOIN или RIGHT JOIN ) и так далее. Все они безусловно удобны, но в условиях одноразовых запросов. К примеру, если лично Вам требуется что-то откопать в базе используя кучу таблиц и связок, то кроме вышеперечисленных функций можно и даже нужно применять условный операторы IF . Главная ошибка начинающих программистов это стремление применить такие запросы в рабочем коде сайта. В данном случае сложный запрос безусловно красив, но вреден. Все дело в том, что любые операторы сортировок, группировок, объединений или вложенных запросов, не могут выполняться в оперативной памяти, и используют жесткий диск для создания временных таблиц. А хард, как известно - самое узкое место сервера.
Правила оптимизации mysql запросов
1. Избегайте вложенных запросов
Это самая серьезная ошибка. Родительский процесс всегда будет ждать завершения дочернего и в это время держать коннект к базе, использовать диск и нагружать iowait. Два параллельных запроса в базу и выполнения нужных фильтраций в серверном интерпретаторе (Perl , PHP и т. д.), выполнятся на порядок быстрее чем вложенный.
Примеры на perl , как делать не следует:
My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { my $groupNAME = $dbh->selectrow_array("SELECT groupNAME FROM groups WHERE groupID = $row"); ### Допустим нужно собрать названия групп ### и добавить их в конец массива с данными push @row => $groupNAME; ### Делаем еще что-нибудь... }
или не в коем случае вот так:
My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(SELECT groupID FROM groups WHERE groupNAME = "Первая" OR groupNAME = "Вторая" OR groupNAME = "Седьмая")");
Если есть необходимость подобных действий, во всех случаях лучше использовать хеш, массив или любой другой путь для фильтрации.
Пример на perl, как делаю обычно я:
My %groups; my $sth = $dbh->prepare("SELECT groupID,groupNAME FROM groups WHERE groupID IN(2,3,7)"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { $groups{$row} = $row; } ### А теперь выполням основную выборку без вложенного запроса my $sth2 = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth2->execute(); while (my @row = $sth2->fetchrow_array()) { push @row => $groups{$row}; ### Делаем еще что-нибудь... }
2. Не сортируйте, не группируйте и не фильтруйте в базе
По возможности не применяйте в своих запросах операторы ORDER BY, GROUP BY, JOIN. Все они используют временные таблицы. Если сортировка или группировка необходима только для вывода элементов, например по алфавиту, лучше выполнить эти действия в переменных интерпретатора.
Примеры на perl, как сортировать не следует:
My $sth = $dbh->prepare("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7) ORDER BY elementNAME"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { print qq{$row => $row}; }
Пример на perl, как сортирую обычно я:
My $list = $dbh->selectall_arrayref("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7)"); foreach (sort { $a-> cmp $b-> } @$list){ print qq{$_-> => $_->}; }
Так намного быстрее. Особенно заметна разница если данных много. В случае, если нужно отсортировать в perl по нескольким полям, можно применить сортировку Шварца . Если требуется произвольная сортировка ORDER BY RAND() - используйте сортировку random в perl .
3. Используйте индексы
Если от сортировки в базе можно отказаться в некоторых случаях, то от WHERE навряд ли удастся. Поэтому, для полей, по которым будет идти сравнение, необходимо устанавливать индексы. Делаются они просто.
Таким запросом:
ALTER TABLE `any_db`.`any_tbl` ADD INDEX `text_index`(`text_fld`(255));
Где 255 - длина ключа. Для некоторых типов данных он не требуется. Подробности в документации к MySQL.
MySQL по-прежнему является самой популярной в мире реляционной базой данных, но в то же время и наиболее не оптимизированной. Многие люди остаются с настройками по умолчанию, не "копая" глубже. В этой статье мы рассмотрим некоторые советы по оптимизации MySQL в сочетании с некоторыми новинками, которые вышли относительно недавно.
Оптимизация конфигурации
Первое, что каждый пользователь MySQL должен сделать для повышения производительности - это настроить конфигурацию. Однако, большинство этот шаг пропускают. В 5.7 (текущая версия) настройки по умолчанию стали намного лучше, чем у её предшественников, но улучшить их по-прежнему можно и несложно.
Мы надеемся, что вы используете Linux или что-то вроде Vagrant -box (как наш Homestead Improved), а, соответственно, ваш конфигурационный файл будет находиться в /etc/mysql/my.cnf . Вполне возможно, что ваша установка на самом деле будет подгружать дополнительный файл конфигурации в этот. Так что посмотрите, если файл my.cnf содержит немного, то посмотрите в /etc/mysql/mysql.conf.d/mysqld.cnf .
Ручной тюнинг
Следующие настройки должны быть сделаны "из коробки". Согласно этим советам , добавьте в файл конфига в раздел :
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.
Прим. переводчика:
На моей локальной машине, кроме этого, выдал ещё вот такой ворнинг:
О том, что параметр 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
Вспомогательные инструменты: 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 летал.
Мы разобрались с оптимизацией конфигурации, мы прокачались по индексам, и мы избавились от некоторых узких мест. Все это было в основном теорией, однако, всё это применимо на реальных приложениях.
Управление индексами, то есть как они создаются и поддерживаются — может значительно повлиять на производительность sql запросов.
Очень часто можно применить следующие оптимизации:
- удалить неиспользуемые индексы
- определить неиспользуемые вообще и неэффективные индексы
- улучшить индексы
- избегать вообще sql запросов!
- упрощать sql запросы
- и магия варианты кеширования
Объединение DDL запросов
Запросы, меняющие структуру данных как правило являются блокирующими таблицу. Исторически, выполнение запроса ALTER требовало создания новой копии таблицы, что может быть очень затратным по времени и по объему данных на диске. Поэтому вместо трех запросов с маленькими альтерами намного выгоднее выполнять один объединенный. Это может сэкономить значительное количество времени на задачах по администрированию баз.
Удаление дублирующихся индексов
Дублирующиеся индексы вредны по двум причинам: все запросы на изменение данных будут медленнее, поскольку выполняется двойная работа для поддержания полноты индекса. Кроме того, это создает лишнюю нагрузку на файловую систему, поскольку размер базы становится большим физически и приводит к увеличение времени создания бэкапов и времени восстановления.
Несколько простых условий могут привести к дублированию индексов. Например, mysql не нужен индекс на полях PRIMARY.
Дублирующий индекс также может существовать, если левая часть одного из индексов полностью совпадает с другим индексом.
Утилита pt-duplicate-key-checker из perkona-toolkit — это простой и быстрый способ проверить свою структуру базы на наличие лишних индексов.
Удаление неиспользуемых индексов
Кроме индексов, которые не используются никогда, поскольку являются дублями, могут быть недублирующиеся индексы, которые просто никогда не используются. Такие индексы влияют также, как и дублирующиеся индексы. В стандартном mysql нет никаких способов определить какие индексы неиспользуются, однако в некоторых версиях есть подобная возможность, например при использовании Google MySQL patch.
В этом патче была введена фишка: SHOW INDEX_STATISTICS.
А в обычном mysql сначала необходимо собрать все используемые sql запросы, прогонять их и смотреть план выполнения, собирать при этом информацию о используемых в каждом случае индексах и сводить это в единую таблицу. В любом случае, это полезный опыт.
Оптимизация индексных полей.
Помимо создания новых индексов для повышения производительности, можно повысить быстродействие через дополнительные оптимизации структуры. В эти оптимизации входит использование специальных данных и типов полей. Профит в данном случае — это меньшая нагрузка на диск и больший объем индексов, который может помещаться в оперативной памяти.
Типы данных
Некоторые типы могут быть заменены безболезненно на текущей существующих базах.
BIGINT vs INT
Когда PRIMARY ключ определён как BIGINT AUTO INCREMENT — как правило нет никаких причин использовать именно его. Тип данных INT UNSIGNED AUTO_INCREMENT может хранить максимум числа до 4294967295. Если у вас реально будет больше записей чем это число, вам скорее всего понадобится другая архитектура.
От такого изменения с BIGINT на INT UNSIGNED каждая строка таблицы начинает занимать в 2 раза меньше места на диске, кроме того с 8 байт до 4 снижается размер, занимаемый PRIMARY ключом.
Это пожалуй одно из самых ощутимых простых улучшений, которые можно делать достаточно безболезненно.
DATETIME vs TIMESTAMP
Тут все просто: timestamp — 4 байта, datetime — 8 байт.
По возможности надо использовать, потому что:
- дополнительная проверка целостности данных
- такое поле будет использовать всего 1 байт для хранения 255 уникальных значений
- такие поля удобнее читать:)
Исторически, использование enum полей приводило к зависимости базы от изменений возможных значений в enum. Это был блокирующий DDL запрос. Начиная с версии MySQL 5.1 добавление новых вариантов к enum очень быстрое и не связано с размером таблицы.
NULL vs NOT NULL
Если вы не уверены, что колонка может содержать неопределенное значение (NULL), лучше определять ее как NOT NULL. Индекс на такой колонке будет меньше по размеру и будет легче обрабатываться.
Автоматичесие конвертации типов
Когда вы выбираете тип данных для джойнящихся полей, бывает, что тип данных в поле неопределен. Встроенная конверсия может быть абсолютно лишним оверхедом.
Для целочисленных полей, убедитесь что SIGNED и UNSIGNED совпадают, для переменных типов полей, лишней работой может быть конвертация кодировки при джоине, поэтому их тоже обязательно проверять. Частая проблема это автоконвертация между кодировками latin1 и utf8.
Типы колонок
Некоторые типы данных часто хранятся в неправильных колонках. Изменение типа при этом может привести к более эффективному хранению, особенно когда эти колонки включаются в индекс. Рассмотрим несколько типичных примеров.
IP адрес
IPv4 адрес может храниться в поле INT UNSIGNED, которое займет всего 4 байта. Часто встречается ситуация, когда ip адрес хранят в поле VARCHAR(15), которое занимает 12 байт. Одно это изменение может сократить размер на 2/3. Функции INET_ATON() и INET_NTOA служат для конвертации между строкой с ip адресом и числовым значением.
Для IPv6 адресов, которые все сильнее наступают, важно хранить их 128битное цифровое значение в полях BINARY(16) и не использовать VARCHAR для человекочитаемого формата.
Хранение md5 полей как CHAR(32) является повсеместной практикой. Если вы используете поле VARCHAR(32) вы еще дополнительно добавляете лишний оверхед длины строки для каждого значения. Однако md5 строка — это шестнадцатиричное значение — и его можно хранить эффективнее используя функции UNHEX() и HEX(). В этом случае данные можно хранить в полях BINARY(16). Такое простое действие снизит размер поля с 32 байт до 16 байт. Подобный принцип можно применять к любым шестнадцатиричным значениям.
Основано на книге Рональда Брэдфорда.
От того, насколько хорошо оптимизированы запросы к базе данных mySQL, сильно зависит степень нагрузки на сервер, а значит и скорость загрузки сайта. Разгрузить сервер и уменьшить время загрузки вашего сайта поможет оптимизация mySQL запросов.
Зачем оптимизировать запросы к базе данных
Владельцы сайтов, находящихся под управлением самописных систем администрирования просто обязаны хорошо понимать, какие запросы к базе данных выполняются быстро и легко, а какие сильно повышают нагрузку на сервер и значительно замедляют скорость загрузки сайта.
Вникнуть в суть дела не помешает и тем веб-мастерам, которые используют известные системы администрирования и любят подключать всевозможные плагины сторонних разработчиков, а так же кастомизировать темы под себя, к примеру, на самой популярной бесплатной CMS – WordPress.
Некоторые действия можно выполнить разными способами, например, посчитать количество найденных в таблице записей можно при помощи функции mysql_num_rows (но делать этого не рекомендуется), а можно и при помощи конструкции SELECT COUNT(). Нами лично было проведено исследование, в котором мы создали огромную таблицу данных, содержащую несколько сотен тысяч записей и весящую более одного гигабайта, а затем попробовали посчитать количество строк указанными способами.
Результат был виден невооруженным глазом, ведь в случае использования mysql_num_rows, страница подвисала секунд на 5, после чего выводился результат. Во втором же случае мы получали результат в виде количества записей в таблице практически моментально. Нам даже не пришлось замерять время загрузки скрипта при помощи микротаймера, ведь результат был более чем очевиден.
То же самое касается и других конструкций. Некоторые операции с базой данных можно выполнить двумя, тремя, четырьмя и более способами и каждый из них будет отличаться именно скоростью, в то время как результат во всех случаях будет одинаково верным.
Как оптимизировать запросы к базе данных
Для того, чтобы понять, как именно оптимизировать запросы и какие конструкции работают быстрее, а какие медленней, мы снова проведем небольшой опыт и сделаем это прямо сейчас.
Нам придется обратиться за помощью к интерфейсу популярного и очень удобного phpmyadmin. Для того, чтобы начать, нам нужно выбрать одну из имеющихся баз данных и создать в ней тестовую таблицу. Ее название в нашем случае будет довольно банальным – test.
CREATE TABLE `test` (`ID` INT NOT NULL AUTO_INCREMENT , `TITLE` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `ANNOUNCEMENT` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `TEXT` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , PRIMARY KEY (`ID`)) ENGINE = MYISAM ;
Теперь, когда тестовая таблица у нас уже есть, нужно наполнить ее абстрактными данными. Как видно из структуры только что созданной нам таблицы, нам потребуются следующие данные для заполнения:
- Заголовок
- Анонс
- Полный текст
За абстрактными текстами мы по привычке пойдем на сервис Яндекс.Рефераты , созданный как раз для подобных целей. Нам посчастливилось наткнуться на тему «Торсионный фотон в XXI веке», ее и возьмем.
Мы указали выбранную случайно тему в качестве заголовка, в качестве анонса взяли один средненький абзац текста, а в роли полного текста статьи у нас с вами будет текст, длиной в 4000 символов с пробелами. Для подсчета количества символов в тексте мы воспользовались нашим собственным сервисом и вам рекомендуем считать именно в нем, т.к. там есть возможность учитывать пробелы или нет.
Получившийся запрос мы сюда копировать не будем, т. к. это будет более 4000 символов не уникального текста, взятого у самого Яндекса, что довольно дерзко, да и вам это тоже не нужно. Лучше мы набросаем простейший цикл на PHP, который быстро добавит в базу данных столько записей, сколько мы захотим. Для начала это будет 100000 статей.
Чем меньше запросов к базе данных, тем лучше
Уже на этом этапе мы покажем вам распространенную ошибку, которую сами же сейчас специально и допустим.
For($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }
В качестве запроса мы вставили скопированный из phpmyadmin код, который был отображен на экране после единичного добавления первой статьи вручную. Сразу хочется отметить, что таким образом запросы к базе данных строить не стоит. Мы это сделали лишь потому, что нам просто нужно было быстро наполнить таблицу случайными данными, а этот запрос пишется быстрее, чем тот, который более оптимален. В этом цикле у нас получилось 99999 отдельных обращений к базе данных (первое мы осуществили вручную из phpmyadmin), что является очень плохим тоном.
Гораздо более правильным решением будет сделать ту же операцию, использовав всего одно обращение к базе данных, перечислив все строки через запятую.
INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст"), (NULL, "Заголовок", "Анонс", "Полный текст"), (NULL, "Заголовок", "Анонс", "Полный текст"), …
Если вернуться к нашему первому способу, то он бы выглядел вот так:
INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст") INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст") INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст") …
Чувствуете разницу? Вариант, в котором используется только одно обращение к базе данных и является оптимальным. Скорость работы этих двух способов, приводящих к одному и тому же результату отличается в разы и видна без всяких измерений невооруженным глазом, поверьте, это действительно так.
Производить выборку только необходимых скрипту полей
Здесь все очень просто – та или иная функция нуждается в определенных данных из целевой таблицы. Очень часто оказывается так, что нужно вытащить вообще все поля, особенно, если таблица довольно большая и этих полей больше 10.
SELECT * FROM `test`
В данном запросе звездочка означает то, что будут извлечены данные из всех полей таблицы test. А что, если этих полей в таблице 20-30 штук или больше? Скрипту скорее всего необходимы лишь некоторые из них, а все остальные, которые не будут никак использоваться, будут выбраны зря. Такая операция будет выполняться медленнее, чем если бы вы указали через запятую только те поля, которые вам действительно нужны в данный момент.
SELECT `ID`, `TITLE` FROM `test`
В этом примере мы вообще не будем касаться анонса и полного текста статьи, что заметно ускорит работу скрипта. Таким образом мы с вами делаем вывод, что под оптимизацией запросов к базе данных понимается так же конкретное указание необходимых полей в запросах и отказ от универсальности в виде звездочки.
Объединение нескольких запросов в один
Мы уже с вами обсудили, что хорошая оптимизация работы с mySQL подразумевает использование минимально возможного количества запросов и привели пример с добавлением данных в таблицу.
Помимо добавления, данный прием можно и нужно использовать и при выборке данных. А теперь давайте приведем самый простой пример. Представьте себе, что у в вашей базе данных есть две таблицы – первая таблица хранит в себе информацию о зарегистрированных пользователях, а вторая содержит статьи, написанные этими пользователями.
Допустим, вам нужно вывести на экран какую-нибудь случайную статью, а снизу подписать ее именем автора. Связь таблиц между собой в данном случае очевидна и происходит по идентификатору пользователя, т. е. ID пользователя в таблице users должен соответствовать полю USER_ID в таблице posts. Данная связь является стандартной и должна быть понятна всем, без исключения.
Итак, чтобы выбрать случайную статью, вы пишете запрос следующего вида:
$rs_post = mysql_query("SELECT `ID`, `USER_ID`, `TITLE`, `TEXT` FROM `posts` ORDER by RAND() LIMIT 1");
Из таблицы posts случайным образом выберется одна статья. После чего наши действия будут иметь примерно такой вид:
$row_post = mysql_fetch_assoc($rs_post); $userID = $row_post["USER_ID"];
Теперь переменная $userID содержит идентификатор пользователя, являющегося автором этой статьи и для того, чтобы получить его данные, например NAME (имя) и SURNAME (фамилию), вы будете обращаться к таблице users и запрос будет выглядеть примерно так:
$rs_user = mysql_query("SELECT `NAME`, `SURNAME` FROM `users` WHERE `ID` = "".$row_post["USER_ID"]."" LIMIT 1");
Кстати, не забывайте обрамлять одинарными кавычками переменные в запросах, особенно это нужно делать, когда данные поступают извне, при помощи GET или POST. Это создаст дополнительное препятствие для злоумышленников и является одной из мер, направленных на защиту от SQL-инъекций . Итак, вернемся к нашему примеру. После того, как запрос к базе данных был сделан, далее все просто – получаем имя и фамилию и выводим в качестве подписи к статье. Задача выполнена.
Но эти два запроса можно оптимизировать, превратив в один. Для этого мы воспользуемся конструкцией LEFT JOIN:
SELECT `posts`.`ID`, `posts`.`USER_ID`, `posts`.`TITLE`, `posts`.`TEXT`, `users`.`NAME`, `users`.`SURNAME` FROM `posts` LEFT JOIN `users` ON `posts`.`USER_ID` = `users`.`ID` ORDER by RAND() LIMIT 1
Как видите, ничего сложного в приведенной конструкции нет и все интуитивно понятно. Единственное, на что хочется обратить ваше внимание – это явное указание таблиц в паре с полями, т. к. идет выборка сразу из нескольких таблиц. Если же названия каких-то полей совпадают, то следует использовать так называемые mySQL алиасы , чтобы потом не путаться при выводе результата.
Заключение
Как видите, оптимизировать запросы к базе данных можно и нужно. Если вы думаете, что раз у вас все и так быстро работает, то нет смысла что-либо менять, подождите, когда база данных вашего сайта вырастет в несколько раз, а вместе с этим вырастет и посещаемость. Большая посещаемость подразумевает более частые одновременные обращения к базе данных, размер которой также влияет на скорость выполнения операций.
Плохая оптимизация запросов может обнаружиться чуть позже, когда сайт достаточно разрастется, а со временем изменения вносить станет все тяжелей, ведь размеры файлов и количество функций только прибавляется. На сайт добавляют новые фичи, направленные на удобство пользователей. Иными словами, если дело дойдет до определенной точки кипения, можно уже и концов не сыскать и чтобы оптимизировать все обращения к базе данных, раскиданные по сотням файлов, потребуется несколько дней, а может и недель. Поэтому лучше сразу стараться делать хорошо, чтобы не создавать себе лишних проблем в будущем.