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


Оглавление (нажмите, чтобы открыть):

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

У меня большой запрос с разными таблицами, заданными с помощью соединений и с WHERE CLAUSES.

Теперь, исходя из моего понимания, лучшим индексом является просмотр WHERE CLAUSE и добавление его в качестве индекса

У нас будет указатель на поле «имя».

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

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

С другой стороны:

требуется INDEX(name) для обеих таблиц.

Если name является PRIMARY KEY для каждой таблицы, то эти индексы являются избыточными и не должны добавляться.

Единого индекса для нескольких таблиц нет. Первое, что вы могли бы сделать, — создать индекс для table1 в field и другой для table2 в field . Если это еще не достаточно быстро, в зависимости от вашей схемы базы данных, вы можете установить внешний ключ.

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

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

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

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

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

Кроме того, кто-нибудь может посоветовать лучше, увидев ваш фактический запрос.

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

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

Сегодня идет много разговоров о Dig Data и других новых технологиях. NoSQL и облачные решения это супер, но много популярного софта (такого как WordPress, phpBB, Drupal) до сих пор работает на MySQL. Миграция на новейшие решения может вылиться не только в изменении конфигурации на серверах. К тому же, эффективность MySQL до сих пор на уровне, особенно версия Percona.

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

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

Для кого эта статья?

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

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

Делать бэкапы MySQL просто, используя утилиту mysqldump:

Вы можете узнать больше о mysqldump .

Что делает запрос медленным?

Вот общий список факторов, влияющих на скорость выполнения запросов и нагрузки сервера:

  • индексы таблиц;
  • условие WHERE(и использования внутренних функций MySQL, например, таких как IF или DATE);
  • сортировка по ORDER BY;
  • частое повторение одинаковых запросов;
  • тип механизма хранения данных (InnoDB, MyISAM, Memory, Blackhole);
  • не использование версии Percona;
  • конфигурации сервера ( my.cnf / my.ini );
  • большие выдачи данных (более 1000 строк);
  • нестойкое соединение;
  • распределенная или кластерная конфигурация;
  • слабое проектирование таблиц.

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

Что такое индексы?

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

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

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

Уменьшаем частое повторение одинаковых запросов

Наиболее быстрый и эффективный способ, который я нашел для этого — это создание хранилища запросов и результатов их выполнения с помощью Memcached или Redis. С Memcache вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

Теперь тяжелый запрос, использующий LEFT JOIN, будет выполняться только раз за каждые 86 400 секунд (то есть раз в сутки), что значительно уменьшит нагрузку MySQL сервера, оставив ресурсы для других соединений.

Примечание: Допишите p: в начале аргумента хоста MySQLi для создания постоянного соединения.

Распределенная или кластерная конфигурация

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

Слабое проектирование таблиц

Создание схем баз данных не является сложной работой, если следовать таким золотым правилам, как работа с ограничениями и знание того, что будет эффективным. Например, хранение изображений в ячейках типа BLOB очень смущает — лучше храните путь к файлу в ячейке VARCHAR, это является гораздо лучшим решением.

Обеспечение правильного проектирования для нужного использования является первостепенным в создании вашего приложения. Храните различные данные в различных таблицах (например, категории и статьи) и убедитесь, что отношения к другу (many to one) и один ко многим (one to many) могут быть легко связаны с идентификаторами (ID). Использование FOREIGN KEY в MySQL идеально подходит для хранения каскадных данных в таблицах.

При создании таблицы помните следующее:

  • Создавайте эффективные таблицы для решения ваших задач, а не заполняйте таблицы лишними данными и связями.
  • Не ожидайте от MySQL выполнения вашей бизнес логики или програмности — данные должны быть готовы к вставке строки вашей скриптовым языком. Например, если вам нужно отсортировать список в случайном порядке, сделайте это в массиве PHP, не используя ORDER BY из арсенала MySQL.
  • Используйте индексные типы UNIQUE для уникальных наборов данных и применяйте ON DUPLICATE KEY UPDATE, чтобы хранить дату обновленной, например, для того, чтобы знать, когда строка была в последний раз изменена.
  • Используйте тип данных INT для сохранения целых чисел. Если вы не укажете размер типа данных, MySQL сделает это за вас.

Основы оптимизации

Для эффективной оптимизации мы должны применять три подхода к вашему приложению:

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

Анализ может быть сделан несколькими путями. Сначала мы рассмотрим наиболее очевидные способы, чтобы заглянуть под капот вашей MySQL, в котором выполняются запросы. Самый первый инструмент оптимизации в вашем арсенале это EXPLAIN. Если добавить этот оператор перед вашим запросом по SELECT, результат запроса будет таким:

Колонки, вы видите, сохраняют важную информацию о запросе. Колонки, на которые вы должны обратить наибольшее внимание это possible_keys и Extra.

Колонка possible_keys покажет индексы, в которые MySQL имел доступ, чтобы выполнить запрос. Иногда нужно назначить индексы, чтобы запрос выполнялся быстрее. Колонка Extra покажет, были ли использованы дополнительные WHEREили ORDER BY. Наиболее важно обратить внимание, есть ли Using Filesort в выводе.

Что делает Using Filesort, указано в справке MySQL:

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

Лишний проход замедлит ваше приложение, этого нужно избегать, чего бы это ни стоило. Другой критический результат Extra, который мы должны избегать — это Using temporary. Он говорит о том, что MySQL пришлось создать временную таблицу для выполнения запроса. Очевидно, это ужасное использования MySQL. В таком случае результат запроса должен быть сохранен в Redis или Memcache и не выполняться пользователями лишний раз.

Чтобы избежать проблемы с Using Filesort мы должны увериться, что MySQL использует INDEX. Сейчас указано несколько ключей в possible_keys, из которых можно выбирать, но MySQL может выбрать только один индекс для финального запроса. Также индексы могут быть составлены из нескольких колонок, также вы можете ввести подсказки (хинты) для оптимизатора MySQL, указывая на индексы, что вы создали.

Оптимизатор MySQL будет использовать статистику, основанную на запросах таблиц, чтобы выбрать лучший индекс для выполнения запроса. Он действует достаточно просто, основываясь на встроенной статистической логике, поэтому имея несколько вариантов, не всегда делает правильный выбор без помощи хинтинга. Чтобы убедиться, что был использован правильный (или неправильный) ключ, воспользуйтесь ключевым словам FORCE INDEX, USE INDEX и IGNORE INDEX в вашем запросе. Вы можете прочитать больше о хинтинге индексов в справке MySQL .

Чтобы вывести ключи таблицы, используйте команду SHOW INDEX. Вы можете задать несколько хинтов для использования оптимизатором.

В дополнение к EXPLAIN существует ключевое слово DESCRIBE. Вместе с DESCRIBE можно просматривать информацию из таблицы следующим образом:

Для добавления индексов в MySQL надо использовать синтаксис CREATE INDEX. Есть несколько видов индексов. FULLTEXT Применяется для полнотекстового поиска, а UNIQUE — для хранения уникальных данных.

Чтобы добавить индекс в вашу таблицу, используйте следующий синтаксис:

Это создаст индекс на таблице books, которая будет использовать первые 10 букв из колонки, которая хранит названия книг и имеет тип varchar. В этом случае, любой поиск с запросом WHERE на название книги с совпадением до 10 символов будет давать такой же результат, как и просмотр всей таблицы от начала до конца.

Индексы имеют большое влияние на скорость выполнения запросов. Только назначения главного уникального ключа недостаточно — композитные ключи являются реальной областью применения в настройке MySQL, что иногда требует некоторых A/B проверок с использованием EXPLAIN.

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

Как только мы создали ключ на основе колонки username, в котором хранится имя пользователя и колонки active типа ENUM, определяющий, активен ли его аккаунт. Теперь все оптимизировано для запроса, который будет использовать WHERE для поиска валидного имени пользователя с активным аккаунтом (active = 1).

Насколько быстра ваша MySQL?

Включим профилирование, чтобы подробнее рассмотреть MySQL запросы. Это можно сделать, выполнив команду set profiling=1, после чего для просмотра результата надо выполнить show profiles.

Если вы используете PDO, выполните следующий код:

То же самое можно сделать с помощью mysqli:

Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

Этот запрос выполнялся 0.00024300 секунд. Это довольно быстро, поэтому не будем беспокоиться. Но когда числа становятся большими, мы должны смотреть глубже. Перейдите к вашему приложению, чтобы потренироваться на рабочем примере. Проверьте константу DEBUG в конфигурации вашей базы данных, а затем начните изучать систему, включив вывод результатов профилирования с помощью функций var_dump или print_r. Так вы сможете переходить со страницы на страницу в вашем приложении, получив удобное профилирование системы.

Полный аудит работы базы вашего сайта

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

Чтобы включить логирование в MySQL 5.1.6 используйте глобальную переменную log_slow_queries, также вы можете отметить файл для логирования с помощью переменной slow_query_log_file. Это можно сделать, выполнив следующий запрос:

Также это можно указать в файлах конфигурации /etc/my.cnf или my.ini вашего сервера.

После внесения изменений не забудьте перезагрузить MySQL сервер необходимой командой, например service mysql restart, если вы используете Linux.

В версиях MySQL после 5.6.1 переменная log_slow_queries обозначена как устаревшая и вместо нее используется slow_query_log. Также для более удобного дебаггинга можно включить вывод в таблице, задав переменной log_output значение TABLE, однако эта функция доступна только с MySQL 5.6.1.

Переменная long_query_time определяет количество секунд, после которых выполнение запроса считается медленным. Значение это 10, а минимум это 0. Также можно указать миллисекунды, используя дробь; сейчас я указал одну секунду. И теперь каждый запрос, который будет выполняться дольше 1 секунды, записывается в логи в таблице.

Логирование будет вестись в таблицах mysql.slow_log и mysql.general_log вашей MySQL базы данных. Чтобы выключить логирование, измените log_output на NONE.

Логирование на рабочем сервере

На рабочем сервере, который обслуживает клиентов, лучше применять логирование только на короткий период и для мониторинга нагрузки, чтобы не создавать лишней нагрузки. Если ваш сервис перегружен и необходимо безотлагательное вмешательство, попробуйте выделить проблему, выполнив SHOW PROCESSLIST, или обратитесь к таблице information_schema.PROCESSLIST, выполнив SELECT * FROM information_schema.PROCESSLIST;.

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

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

Логирование множества запросов

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

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

Горячий и холодный кэш

Количество запросов и нагрузка сервера имеет сильное влияние на исполнение, также может повлиять на время выполнения запросов. При разработке вы должны взять за правило, что выполнение каждого запроса должно быть не более доли миллисекунды (0.0xx или быстрее) на свободном сервере.

Применение Memcache имеет сильный эффект на нагрузку серверов, освободит ресурсы, которые выполняют запросы. Убедитесь, что вы используете Memcached эффективно и протестовали ваше приложение с горячим кэшем (подгруженными данным) и с холодным кэшем.

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

Мастер Йода рекомендует:  Развиваем навыки разработки шаблонов Shopify

Исправление медленных запросов

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

Если вы пока не нашли медленного запроса, проверьте настройки long_query_time, если вы пользуетесь этим методом логирования. Иначе, проверив все ваши запросы профилирования (set profiling=1), составьте список запросов, отнимают больше времени, чем доля миллисекунд (0.000x секунд) и начнем из них.

Вот шесть самых распространенных проблем, которые я находил, оптимизируя MySQL запросы:

ORDER BY и filesort

Предотвращение filesort иногда невозможно из-за выражения ORDER BY. Для оптимизации сохраните результат в Memcache, или выполните сортировку в логике вашего приложения.

Использование ORDER BY вместе с WHERE и LEFT JOIN

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

Применение ORDER BY по временным колонками

Просто не делайте этого. Если вам нужно объединить результаты, сделайте это в логике вашего приложения; не используйте фильтрацию или сортировку во временной таблице запроса MySQL. Это требует много ресурсов.

Игнорирование индекса FULLTEXT

Использование LIKE это самый лучший способ сделать полнотекстовый поиск медленным.


Беспричинный выбор большого количества строк

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

Чрезмерное использование JOIN вместо создания композитных таблиц или представления

Когда в одном запросе вы пользуетесь больше чем тремя-четырьмя операторами LEFT JOIN, спросите себя: все ли здесь верно? Продолжайте, если у вас есть на то веская причина, например — запрос используется не часто для вывода в панели администратора, или результат вывода может быть сохранен в кэше. Если же вам нужно выполнять запрос с большим количеством операций объединения таблиц, тогда лучше задуматься о создании композитных таблиц из необходимых столбиков или использовать представления.

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

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

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Страниц: 1

#1 08.12.2020 21:24:11

Оптимизация индексов и MySQL в целом

Стоял настроенный «более-менее» сервер чуть более полу года. SELECT запросы летали в прямом смысле. Средняя скорость самых частых запросов типа:

180,000к записей. Тип MyiSAM.

После аварийного выключения сервера провайдером сразу крашнулись самые большие таблицы имеющие больше 30,000к записей.
Восстановить удалось только с помощью консоли и функции myisamchk -r -f table.MYI.

Сразу же после успешного восстановления, по непонятным причинам упала производительность MySQL в целом. Конфиг my.cnf не изменялся. Нагрузка на сервер была такой же как и раньше 30-50%. Мои запросы(которые сверху да и запросы к маленьким таблицам тоже) начали выполняться не за доли тысячной секунды, а от 0.50 сек. и выше. Сразу же почувствовалось проседание всего сайта в скорости.

Решил покрутить значения в my.cnf и заодно делать запросы замеряя функцией microtime(). Кэш выключил ибо меня больше интересует мгновенная выдача неизвестного результата для MySQL.
Во время настройки заметил парадокс, что чем старее я выбираю >Сервер 1Gb RAM
1 CPU
Debian 8
MySQL 5.5.53

query_cache_size = 1M
query_cache_min_res_unit = 3800
query_cache_limit = 64M
query_cache_type = OFF

max_heap_table_size = 30M
tmp_table_size = 30M

max_connections = 200
thread_cache_size = 100

read_buffer_size = 256K
read_rnd_buffer_size = 3M
join_buffer_size = 16M
sort_buffer_size = 800K

myisam_sort_buffer_size = 128M
table_open_cache = 270

[ !! ] Maximum reached memory usage: 2.1G (218.11% of installed RAM)
[ !! ] Maximum possible memory usage: 4.2G (428.94% of installed RAM)
[ !! ] Overall possible memory usage with other process exceeded memory
[ !! ] Slow queries: 7% (52K/667K)
[ OK ] Highest usage of available connections: 48% (96/200)
[ OK ] Aborted connections: 0.00% (0/86540)
[ !! ] Query cache may be disabled by default due to mutex contention.
[ OK ] Sorts requiring temporary tables: 0% (0 temp sorts / 46K sorts)
[ OK ] No joins without indexes
[ OK ] Temporary tables created on disk: 9% (56 on disk / 606 total)
[ OK ] Table cache hit rate: 51% (270 open / 522 opened)
[ OK ] Open file limit used: 44% (452/1K)
[ OK ] Table locks acquired immediately: 99% (209K immediate / 211K locks)

——— MyISAM Metrics —————————————————————————-
[ !! ] Key buffer used: 20.4% (43M used / 213M cache)
[ OK ] Key buffer size / total MyISAM indexes: 204.0M/22.3M
[ OK ] Read Key buffer hit rate: 99.8% (1M cached / 3K reads)
[ !! ] Write Key buffer hit rate: 16.2% (44K cached / 37K writes)

(Накрутил RAM больше чем нужно , производительность слегка увеличилась и то только для запросов id которых от 0 до 1000, но это все-равно не то, что я хотел.)

Отредактированно kingkobra97 (08.12.2020 22:15:57)

#2 08.12.2020 22:25:42

Re: Оптимизация индексов и MySQL в целом

покажите:
show create table `имя таблицы`;

#3 08.12.2020 23:27:51

Re: Оптимизация индексов и MySQL в целом

vasya написал:

покажите:
show create table `имя таблицы`;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table const PRIMARY PRIMARY 4 const 1

Профайлинг

+———————————+———-+
| Status | Duration |
+———————————+———-+
| starting | 0.000047 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000086 |
| checking permissions | 0.000007 |
| Opening tables | 0.000027 |
| System lock | 0.000010 |
| Waiting for query cache lock | 0.000035 |
| init | 0.000052 |
| optimizing | 0.000015 |
| statistics | 0.071843 |
| preparing | 0.000028 |
| executing | 0.000004 |
| Sending data | 0.000087 |
| end | 0.000010 |
| query end | 0.000003 |
| closing tables | 0.000011 |
| freeing items | 0.000010 |
| Waiting for query cache lock | 0.000004 |
| freeing items | 0.000033 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000001 |
| storing result in query cache | 0.000003 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+———————————+———-+

Отредактированно kingkobra97 (08.12.2020 23:31:48)

#4 08.12.2020 23:52:22

Re: Оптимизация индексов и MySQL в целом

это профайлинг запроса (select..) или плана запроса (explain select..)?

#5 08.12.2020 23:53:10

Re: Оптимизация индексов и MySQL в целом

vasya написал:

это профайлинг запроса (select..) или плана запроса (explain select..)?

это (select..), а нужен и (explain select..)?

#6 08.12.2020 23:57:58

Re: Оптимизация индексов и MySQL в целом

нужен профайлинг для select, но в показанном я не вижу выполнение более 0.5 сек, поэтому и уточнил

#7 09.12.2020 00:00:43

Re: Оптимизация индексов и MySQL в целом

можно попробовать:
ANALYZE TABLE `имя таблицы`;
пересоздать таблицу
проверить диск на ошибки

#8 09.12.2020 00:09:25

Re: Оптимизация индексов и MySQL в целом

Тот запрос выполнялся 0.07, что уже очень много. Должен как минимум 0.007.
Вот более тяжелый запрос. Оптимизировать тут нечего. Раньше он просто летал.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table range PRIMARY PRIMARY 4 NULL 10 Using where; Using filesort

+———————————+———-+
| Status | Duration |
+———————————+———-+
| starting | 0.000335 |
| Waiting for query cache lock | 0.000014 |
| checking query cache for query | 0.000111 |
| checking permissions | 0.000014 |
| Opening tables | 0.000029 |
| System lock | 0.000014 |
| Waiting for query cache lock | 0.000032 |
| init | 0.000050 |
| optimizing | 0.000019 |
| statistics | 0.000089 |
| preparing | 0.000030 |
| executing | 0.000010 |
| Sorting result | 1.575109 |
| Sending data | 0.000209 |
| end | 0.000015 |
| query end | 0.000007 |
| closing tables | 0.000014 |
| freeing items | 0.000016 |
| Waiting for query cache lock | 0.000007 |
| freeing items | 0.000617 |
| Waiting for query cache lock | 0.000011 |
| freeing items | 0.000006 |
| storing result in query cache | 0.000008 |
| logging slow query | 0.000006 |
| cleaning up | 0.000007 |
+———————————+———-+

Отредактированно kingkobra97 (09.12.2020 00:14:27)

#9 09.12.2020 00:23:40

Re: Оптимизация индексов и MySQL в целом

Как проверить диск на ошибки? Я больше склоняюсь к диску потому, что запросы выполняются хаотично. То быстро, то медленно. Кэша естественно нет поэтому это не из-за него. Сколько бы я не крутил my.cnf не могу найти золотую середину. В день когда началось все это, по логам я увидел подобное.

161201 14:23:11 [ERROR] /usr/sbin/mysqld: Table ‘./db/all_visits’ is marked as crashed and should be repaired

Через некоторое время

161201 14:23:12 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘./db/users.MYI’; try to repair it

И ошибка под ним же

161201 14:31:11 [ERROR] Got an error from thread_ >

После я восстановил таблицу all_visits, но что-то снова пошло не так

161202 21:39:51 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘./db/all_visits.MYI’; try to repair it

Через час сломались мои самые большие таблицы

161202 22:55:57 [ERROR] /usr/sbin/mysqld: Table ‘./db/all_visits’ is marked as crashed and should be repaired
161202 22:55:57 [ERROR] /usr/sbin/mysqld: Table ‘./db/table’ is marked as crashed and should be repaired

Очень странное поведение.

Отредактированно kingkobra97 (09.12.2020 00:25:24)

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

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

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

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

INDEX можно создать сразу при создании таблицы или сделать это позже.

Сразу при создании таблицы это может быть так (резонно ожидаем самое большое количество запросов на выборку с ограничением по цене, поэтому индекс создаем для колонки PRICE):

CREATE TABLE REAL_ESTATE (type VARCHAR(20), city VARCHAR(20), floorspace INT, district VARCHAR(20), street VARCHAR(20), rentorsale VARCHAR(20), PRICE VARCHAR (20), INDEX (PRICE));

Другой способ создания индексов применим к уже существующим таблицам

CREATE INDEX PRICE on PEOPLE(PRICE);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Созданные для таблицы индексы можно легко посмотреть — PEOPLE в примере — имя таблицы

+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
| PEOPLE | 1 | PRICE | 1 | PRICE | A | 7 | NULL | NULL | YES | BTREE | | |
+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

UNIQUE MySQL INDEX

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

CREATE UNIQUE INDEX PRICE on PEOPLE(PRICE);

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Сейчас если просмотреть индексы в поле Non_unique будет другое значение

+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
| PEOPLE | 0 | PRICE | 1 | PRICE | A | 7 | NULL | NULL | YES | BTREE | | |
+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
1 row in set (0.00 sec)

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

CREATE UNIQUE INDEX PRICE on PEOPLE(PRICE DESC);

Составные индексы MySQL

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

Вновь удалим наш индекс для таблицы PEOPLE и создадим новый — в этот раз составной.

CREATE INDEX PRICE_AND_CITY on PEOPLE(PRICE, CITY);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Для запроса сразу выполним EXPLAIN

EXPLAIN SELECT PRICE, CITY FROM PEOPLE WHERE CITY = «Yekaterinburg» AND PRICE Запись опубликована 31.12.2020 автором admin в рубрике MySQL.

Поиск

Последние статьи


Рубрики

  • Apache (20)
  • AWS (10)
  • Bash (13)
  • CRON (5)
  • CSS / изображения (6)
  • DNS (8)
  • ELK (1)
  • FastCGI (4)
  • JS (6)
  • Memcached (1)
  • MongoDB (3)
  • MySQL (52)
  • Networking (43)
  • Nginx (39)
  • PHP (13)
  • Python (28)
  • Ruby (6)
  • Search (1)
  • Virtualization / Containers (27)
  • Базы данных (12)
  • Безопасность (25)
  • Высокие нагрузки (23)
  • Контроль версий (14)
  • Мониторинг (17)
  • Обзоры сервисов (23)
  • Оптимизация (1)
  • Ошибки (24)
  • Почтовые серверы (10)
  • Работа сайтов (17)
  • Распределенные системы (8)
  • Телефония (10)
  • Типовые задачи (54)
  • Ядро и ОС (23)
Подключить мониторинг

Уведомления позволят узнать о проблемах на вашем сайте вперед клиентов

Оптимизация MySQL

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


PROCEDURE ANALYSE()

Стандартная функция, которая анализирует поля таблицы в запросе и выводит советы по оптимальной длине/типу полей. Например SELECT * FROM table PROCEDURE ANALYSE() .

Известно, что использование типа полей занимающего большую память делает более медленной работу с таблицами. Enum быстрее чем Varchar, varchar быстрее чем text, medium int быстрее чем int и т.д. Часто можно оптимизировать типы в зависимости от данных в таблице.


Определение не эффективных индексов

Индексы в таблицах — большое благо, но не стоит забывать что кроме того, что индексы ускоряют выборки из таблицы (SELECT) они замедляют обновление таблиц (UPDATE) и добавление новых полей (INSERT), так как при каждом обновлении данных все индексы перестраиваются. Также индексы занимают место на диске. В таблицах где данные часто обновляются/добавляются использование индексов должно быть сбалансированным.

С помощью этого хитрого запроса можно увидеть 10 самых малоэффективных индексов во всей базе данных :

SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `inde name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != ‘mysql’ GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != ‘mysql’ AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01))

Взято отсюда: https://pastebin.com/f6b1c381c. Уберите LIMIT 10, чтобы увидеть статистику по всем индексам.

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

Очень просто, добавляем к нашему запросу слово EXPLAIN. Например: EXPLAIN SELECT * FROM table WHERE var=value ORDER BY field. Очень полезно для просмотра эффективности индексов.

Полный отказ от ORDER BY rand();

Конструкция ORDER BY rand(); отдаёт поля из таблицы в случайном порядке. Основная засада в том, что при выполнении этой конструкции не используются индексы и осуществляется полное сканирование таблицы. Уже при нескольких тысячах записей могут начаться проблемы. Разумно переписать код, чтобы все записи брались из базы данных, добавлялись в массив и сортировались в массиве (shuffle в PHP). Если записей очень много, имеет смысл сделать кеширование — например делать случайную сортировку раз в час.

Отключить InnoDB если он не нужен

Это движки таблиц. MyISAM – быстрее и проще, InnoDB – умеет много полезного, например транзакции, отсутствие блокировок. Если все эти фишки не нужны, то кто-то советует оставаться на MyISAM, кто-то наоборот переходить на InnoDB. Я пока ещё этот вопрос для себя не решил и использую MyISAM.

В любом случае если InnoDB не используется стоит отключить его поддержку, т.к. сервер кушает лишнюю память и работает медленее. Для отключения добавляем skip-innodb в конфиг (/etc/mysql/my.cnf).

Медленные запросы

Некоторые запросы выполняются очень медленно. Скорей всего о большинстве них вы и не подозреваете, т.к. вы тестировали когда в таблице было несколько сотен записей, а сейчас там несколько десятков тысяч и т.д. Найти такие запросы, на которые стоит обратить самое пристальное внимание, можно если включить логгирование Slow Queries. В конфиг файле (/etc/mysql/my.cnf) прописываем:

log_slow_queries = /var/log/mysql/mysql-bin.log
long_query_time = 2

2 – это количество секунд, больше которых выполняемый запрос будет считаться медленным и добавляться в лог. Если добавить ещё строчку log-queries-not-using-indexes в логи будут добавляться все запросы, для которых не использованы индексы.

Затем перегружаем MySQL сервер (/etc/init.d/mysql restart) и в /var/log/mysql будут добавлятся файлы логов с медленными запросами за последние 7 дней (по умолчанию).

Кеширование в MySQL

Стоит конечно писать скрпиты так, чтобы запросов в базу данных было как можно меньше, но часть ваших проблем на себя может взять MySQL. Правильная настройка кеширования приведёт к тому, что повторяющиеся запросы будут браться из кеша и выполняться очень быстро. У меня например 75% запросов выполняется из кеша. Конечно, этот процент зависит от типа запросов, от размера самого кеша, от частоты запросов к базе данных и т.д. Обычно стандартная конфигурация не всегда подходит, т.к. для разной нагрузки нужны разные настройки. Чтобы узнать что менять, смотрим следующий пункт.

Автоматический тюнинг

С помощью простой утилитки mysqltuner можно обнаружить самые основные узкие места в конфигурации MySQL.

Выполняем на сервере:

wget https://mysqltuner.com/mysqltuner.pl
chmod u+x mysqltuner.pl
./mysqltuner.pl

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

Также полезные советы можно посмотреть в phpMyAdmin если нажать ссылку “Текущее состояние MySQL” на главной странице.

Мастер Йода рекомендует:  Что может Python сделать за секунду

Минимизирование изменения таблиц

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

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

Оптимизация таблиц

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

Полезные ссылки по Оптимизации MySQL:

Используем EXPLAIN для оптимизации запросов к MySql

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

Что включает в себя вывод EXPLAIN.

Чтобы использовать EXPLAIN, нужно добавить его перед вашим запросом:

EXPLAIN: выборка всех данных из таблицы

Хоть в это и трудно поверить, но в 10 строчках, которые возвращает запрос, хранится много полезной информации. Что же выводит EXPLAIN?

  • id — порядковый идентификатор каждого SELECT, находящегося внутри запроса (в случае использования вложенных подзапросов)
  • select_type – тип SELECT запроса. Возможные значения:
    • SIMPLE – запрос содержит простую выборку без подзапросов и UNION ‘ов
    • PRIMARY – запрос является внешним запросов в JOIN
    • DERIVED – запрос SELECT является частью подзапроса внутри выражения FROM
    • SUBQUERY – первый SELECT в подзапросе
    • DEPENDENT SUBQUERY — первый SELECT , зависящий от внешнего подзапроса
    • UNCACHEABLE SUBQUERY – некешируемый подзапрос
    • UNION – SELECT является вторым или последующим в UNION
    • DEPENDENT UNION – SELECT является вторым или последующим запросом в UNION и зависит от внешних запросов/li>
    • UNION RESULT – SELECT является результатом UNION ‘а
  • table – таблица, которой относится текущая строка
  • type – тип связывания таблиц. Это один из самых важных столбцов в результате, потому что по нему можно вычислить потерянные индексы или понять, как можно улучшить запрос.
    Возможные значения:
    • system – таблица содержит только одну строку (системная таблица);
    • const — таблица содержит не более одной соответствующей строки, которая будет считываться в начале запроса. Поскольку имеется только одна строка, оптимизатор в дальнейшем может расценивать значения этой строки в столбце как константы. Таблицы const являются очень быстрыми, поскольку они читаются только однажды;
    • eq_ref — для каждой комбинации строк из предыдущих таблиц будет cчитываться одна строка из этой таблицы. Это наилучший возможный тип связывания среди типов, отличных от const . Данный тип применяется, когда все части индекса используются для связывания, а сам индекс — UNIQUE или PRIMARY KEY ;
    • ref — из этой таблицы будут считываться все строки с совпадающими значениями индексов для каждой комбинации строк из предыдущих таблиц. Тип ref применяется, если для связывания используется только крайний левый префикс ключа, или если ключ не является UNIQUE или PRIMARY KEY (другими словами, если на основании значения ключа для связывания не может быть выбрана одна строка). Этот тип связывания хорошо работает, если используемый ключ соответствует только нескольким строкам;
    • fulltext – объединение, использующее полнотекстовый ( FULLTEXT ) индекс таблиц;
    • ref_or_null – то же самое, что и ref , только содержащее строки со значением NULL в полях;
    • index_merge – объединение, использующее список индексов для получения результата запроса;
    • unique_subquery – результат подзапроса в выражении IN возвращает одну строку, используемую в качестве первичного ключа;
    • index_subquery – то же самое, что и unique_subquery, только в результате больше одной строки;
    • range – в запросе происходит сравнение ключевого поля с диапазоном значений (используются операторы BETWEEN , IN , >, >=);
    • index – в процессе выполнения запроса сканируется только дерево индексов;
    • all – в процессе выполнения запроса сканируются все таблицы. Это наихудший тип объединения и обычно указывает на отсутствие надлежащих индексов в таблице;
  • possible_keys – показаны возможные индексы, которые могут использоваться MySQL для поиска данных в таблице. На самом деле, значение этого столбца, очень часто помогает оптимизировать запросы. Если значение равно NULL, значит, никаких индексов не используется.
  • key – отображается текущий ключ, используемый MySQL в данный момент. В этом столбце может отображаться индекс, отсутствующий в possible_keys . Оптимизатор запросов MySQL всегда пытается найти оптимальный ключ, который будет использоваться в запросе. При объединении нескольких таблиц, MySQL может использовать индексы, также не указанные в possible_keys .
  • key_len – содержит длину ключа, выбранного оптимизатором запросов MySQL. Если значение key равно NULL , то key_len тоже NULL . По значению длины ключа можно определить, сколько частей составного ключа в действительности будет использовать MySQL. Подробнее об этом можно почитать в руководстве по MySQL .
  • ref – показаны поля или константы, которые используются совместно с ключом, указанным в столбце key .
  • rows – количество строк, которые анализируются MySQL в процессе запроса. Это еще один важный показатель, указывающий на необходимость оптимизации запросов, особенно тех, которые содержат JOIN и подзапросы.
  • extra – содержит дополнительную информацию о процессе выполнения запроса. Если значениями этого столбца являются ”Using temporary”, “Using filesort” и т.п, то это говорит о том, что это «проблемный» запрос, требующий оптимизации. С полным список значений этого столбца можно ознакомиться в руководстве по MySQL .

Вы можете добавить ключевое слово EXTENDED после EXPLAIN , чтобы увидеть дополнительную информацию о выполнении запроса. После запроса EXPLAIN бывает полезно выполнить запрос SHOW WARNING , показывающий предупреждения и сообщения, касающиеся последнего запроса, а именно перобразований, сделанных оптимизатором запросов MySQL.

Расширенный вариант EXPLAIN EXTENDED

Результат запроса SHOW WARNINGS

Оптимизация производительности с помощью EXPLAIN.

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

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

Анализируем запрос с помощью EXPLAIN


Если вы взглянете на рисунок выше, то увидите все признаки «плохого» запроса. Но даже если я поправлю запрос, результаты не сильно изменятся, потому что в таблицах отсутствуют индексы. Тип объединения равен ”ALL” (напоминаю, что это наихудший вариант). Это значит, что MySQL не может найти ни одного ключа, который может участвовать в объединении, поэтому значение столбцов possible_keys и key равно NULL . Хуже всего то, что в процессе запроса MySQL будет сканировать все записи во всех таблицах, об этом говорит значение столбцов rows . При выполнении запроса будут просмотрены 91.750.822.240 записей (7 × 110 × 122 × 326 × 2996), чтобы получить результат из 4 записей. Это действительно ужасно, и будет только хуже, когда количество записей в базе данных будет увеличиваться.

А сейчас давайте добавим первичные ключи у всех таблиц и выполним запрос еще раз. Как правило, при создании индексов, обращают внимание на поля, по которым происходит объединение ( JOIN ), — это отличные кандидаты, для присвоения индексов, потому что MySQL всегда «просматривает» их при поиске связанных записей.

Теперь давайте выполним наш сложный запрос еще раз после добавления индексов. Результат будет следующий:

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

После добавления индексов, количество сканируемых записей снизилось до 4 (1 × 1 × 4 × 1 × 1). Это говорит о том, что для каждой записи с ключом orderNumber из таблицы orderdetails MySQL сможет найти связанные записи во всех таблицах, используя индексы, а не сканируя все таблицы полностью.

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

Давайте рассмотрим еще один запрос. Объединим 2 запроса SELECT к таблицам products и productvariants с помощью UNION , при этом в каждом из запросов будет участвовать таблица productline. В таблице productvariants хранятся разновидности товара. В ней содержатся поля productCode (ссылка на записи в таблице products) и поле с ценой buyPrice.

EXPLAIN с UNION

Вы можете увидеть некоторые проблемы в этом запросе. Сканируются все записи из таблиц products и productvariants. Так как в этих таблицах нет индексов по полям productLine и buyPrice, значения possible_keys и key , которые выводит EXPLAIN , имеют значения NULL .

Статус таблиц products и productlines проверяется после UNION , если перенести их внутрь UNION , это уменьшит количество обрабатываемых записей. Давайте добавим еще несколько дополнительных индексов и повторим запрос.

Результат EXPLAIN после добавления индексов

Как вы видите, сейчас количество обрабатываемых строк значительно снизилось с 2.625.810 (219 × 110 × 109) до 276 (12 × 23), что дает огромный прирост производительности. MySQL не будет использовать индексы в этом запросе, из-за условий в WHERE . После переноса этих условий внутрь UNION , использование индексов стало возможным. Все это говорит о том, что не всегда достаточно создавать индексы, MySQL не сможет использовать их в определенных запросах.

Заключение

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

Индексирование таблиц MySQL: создание индекса MySQL, удалить индекс и другие операции

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

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

Индексы – это специфические объекты базы данных, позволяющие значительно повысить скорость поиска значений из таблиц базы данных. MySQL index представляет из себя структуру, в которой хранятся значения одного (в некоторых случаях — нескольких) столбца таблицы и ссылок на строки, где эти значения расположены. Так как для хранения индексов чаще всего используются бинарные деревья, поиск среди них занимает чрезвычайно мало места.

Принцип работы индексов очень прост. Для примера рассмотрим запрос:
SELECT Name FROM Persons WHERE Points ON ( [(length)]. )
где:

[UNIQUE | FULLTEXT] – определяет, будет ли индекс содержать только уникальные значения (UNIQUE), или в нем будут присутствовать и повторяющиеся значения (FULLTEXT). По умолчанию используется режим FULLTEXT. Length – определяет длину символов поля для индексирования. Если Length оставить пустым, то в индекс попадет поле целиком вне зависимости от длины.

— уникальный идентификатор индекса. Если это поле не определено, ему будет присвоено имя первого подлежащего индексации столбца.

В версиях MySQL младше 3.22 эта команда не активна, а в более поздних – в плане создания индексов работает аналогично команде ALTER TABLE. При работе с ALTER TABLE добавление записей происходит при помощи команды ADD INDEX, MySQL при помощи этой команды позволяет создавать индексы PRIMARY KEY (создать индекс такого типа при помощи CREATE INDEX нельзя).

Удаление индекса в MySQL

В MySQL удалить индекс можно при помощи такого оператора:
DROP INDEX ON

Индексирование таблиц MySQL – обширная тема, а умение правильно работать с MySQL index – целое искусство. Администраторы из RigWEB все знают особенности индексирования MySQL на хостинге, VPS и выделенных серверах и умеют применять свои знания на практике, поэтому если у Вас остались вопросы — обращайтесь к нам!

Индексы в MySQL

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

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

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

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

Таким образом, индексы в MySQL – это определенная сортировка данных в таблице для ускорения поиска данных. Сортировка происходит на «низком» (машинном) уровне, и нам нет необходимости вникать в ее процессы. Все, что нам требуется – это указать MySQL, какие поля нужно индексировать.

Как определить, для каких полей нужно создавать индексы?

Индексы, прежде всего, нужно создавать по тем полям, которые часто попадают в условие «where» ваших sql-запросов.

Например, допустим, ваша таблица с товарами имеет следующую структуру:

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

В этом случае для оптимизации запросов целесообразно создать индекс для поля cat_id. Первое поле – id всегда имеет уникальное значение и для него целесообразно создать «первичный ключ» (Primary Key).

Как создать индексы в базе данных MySQL

Для создания индекса в MySQL можно воспользоваться интерфейсом phpMyAdmin. Напротив нужного поля в режиме редактирования структуры вы можете увидеть различные опции по добавлению индексов для поля.

Выберите, к примеру, «добавить индекс» и он будет добавлен к выбранному полю.

Второй способ – это создать SQL-запрос по типу:

ALTER TABLE table_name ADD INDEX [index_name] (index_col_name. )

ALTER TABLE users ADD INDEX i_name (username);

Виды (типы) индексов в MySQL

  • Первичный ключ (PRIMARY KEY) – это основной ключ, который в таблице может быть только один. Он позволяет идентифицировать уникальные записи в таблице. Значения, которые находятся в столбце, где поля имеют PRIMARY KEY, не могут повторяться. Нередко первичный ключ назначают для полей с идентификатором id.
  • Уникальный ключ (UNIQUE) – по сути, это альтернатива первичному ключу: значения, которые содержатся в таких полях также не могут повторяться и иметь значение NULL.
  • Составной индекс – позволяет включать в индекс несколько полей, по которым часто происходит выборка. Например, если в условиях часто фигурирует два параметра:
    SELECT username FROM users WHERE city = ‘5’ AND age > ’18’
    При использовании таких запросов составной индекс по полям city и age поможет ускорить выборку данных.

Недостатки использования индексов:

  1. За все приходится платить, не исключение и индексы в MySQL. В процессе индексирования MySQL создает дополнительные таблицы, в которых хранятся отсортированные данные. Однако, в большинстве случаев выгоды от быстродействия более ощутимы, чем издержки физической памяти.
  2. Запросы на вставку данных в таблицу выполняются чуть дольше, поскольку MySQL требуется создать новые индексы и реорганизовать данные в предназначенных для этого таблицах. Но поскольку запросы на вставку выполняются реже, чем на извлечение данных, то серьезных потерь это обычно не приносит.

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

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

Оптимизируйте запросы для кэша запросов

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

Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых недетерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.

Мастер Йода рекомендует:  Справочник по ошибкам Oracle

Используйте EXPLAIN для ваших запросов SELECT

Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:

После добавления индекса для поля group_id:

Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.

LIMIT 1, когда нужна единственная строка

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

Индексируйте поля, по которым ищите

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

Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE ‘a%’». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE ‘%apple%’»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.

Индексируйте поля для объединения и используйте для них одинаковые типы столбцов

Если ваше приложение содержит много объединений таблиц, вам необходимо проиндексировать в обеих таблицах поля, используемые для объединения. Это повлияет на то, как MySQL делает внутреннюю оптимизацию объединений.
Так же эти столбцы должны быть одного типа. Например, если вы объединяете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать хотя бы один из индексов. Даже кодировки символов должны быть одного типа для строковых столбцов.

Не используйте ORDER BY RAND() для больших таблиц

(Имеется в виду выборка единственной строки. Примечание переводчика)

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

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

query(«SELECT MAX(id) FROM `table`»));
$result_set = $mysqli->query(«SELECT * FROM `table` WHERE ` «);
?>

Избегайте SELECT *

Чем больше данных считывается из таблицы, тем медленнее запрос. Это увеличивает время работы с хранилищем данных. Также, когда сервер базы данных установлен отдельно от web-сервера, будет большая задержка при передаче данных по сети.
Прописывайте, какие именно столбцы из запроса вам нужны.

Старайтесь всегда создать поле ID

В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.

Используйте NOT NULL, если это возможно

Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».

Подготовленные выражения

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

Подготовленные выражения по умолчанию фильтруют переменные, которые к ним привязаны, что является очень хорошей защитой ваших приложений против атак типа «инъекция SQL». Конечно, вы можете фильтровать переменные вручную тоже, но такие методы более подвержены ошибкам и забывчивости программистов.

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

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

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

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

Небуферизованные запросы

Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.

Хранение IP в UNSIGNED INT

Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).

Таблицы фиксированного размера (статичные) — быстрее

Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.

Вертикальное разделение

Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.
Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.
Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.
Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.

Разделяйте большие запросы DELETE и INSERT

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

Выбирайте правильный тип таблицы

Два основных типа таблиц — MyISAM и InnoDB, у каждого есть свои плюсы и минусы.
MyISAM хорошо считывает из таблиц большое количество данных, но он плох для записи. Даже если вы изменяете всего одну строку, блокируется вся таблица, и ни один процесс не может ничего из нее прочитать. MyISAM очень быстро выполняет запросы типа SELECT COUNT(*).
У InnoDB более сложный механизм хранения данных, и он может быть медленнее, чем MyISAM, для маленьких приложений. Но он поддерживает блокировку строк, что более эффективно при масштабировании. Так же поддерживаются некоторые дополнительные функции, такие операции как транзакции.
Подробнее:
MyISAM Storage Engine
InnoDB Storage Engine

Оптимизация производительности MySQL

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

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

Скорость работы MySQL

Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог — две секунды. Еще можно включить в лог запросы, которые не используют индексы:

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

systemctl restart mariadb

tail -f /var/log/mariadb/slow-queries.log

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

SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;

Можно его выполнить отдельно, в консоли mysql:

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

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

wget https://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

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

Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

query_cache_size=0
query_cache_type=0
query_cache_limit=1M

Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.

Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

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

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

systemctl stop mariadb

Затем переместите файлы лога в /tmp:

mv /var/lib/mysql/ib_logfile[01] /tmp

И запустите сервис:

systemctl start mariadb

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

systemctl status mariadb

Тестирование результата

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

> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = ‘yes’;

Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

Выводы

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

На завершение лекция про производительность MySQL от Percona:

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