Back to Question Center
0

Как оптимизировать MySQL: индексы, медленные запросы, конфигурация            Как оптимизировать MySQL: индексы, медленные запросы, параметры конфигурации: DatabasePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt

1 answers:
Как оптимизировать MySQL: индексы, медленные запросы, конфигурация

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

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

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

Предположим, вы используете хост на базе Linux или хороший брандмауэр, как наш Homestead Improved, поэтому ваш файл конфигурации будет находиться в / etc / mysql / my - macchina per le crepes professionale. cnf . Возможно, ваша установка фактически загрузит дополнительный файл конфигурации в этот файл конфигурации, поэтому посмотрите на это - если my. Файл cnf не имеет большого количества контента, файла / etc / mysql / mysql. конф. д / туздЫ. cnf .

Редактирование конфигурации

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

Если вы редактируете локально в поле Vagrant, вы можете скопировать файл в основную файловую систему, скопировав его в общую папку с помощью cp / etc / mysql / my. cnf / home / vagrant / Code и отредактируйте его с помощью обычного текстового редактора, а затем скопируйте его на место, когда это будет сделано. В противном случае используйте простой текстовый редактор, например, vim, выполнив sudo vim / etc / mysql / my. cnf .

Примечание: измените приведенный выше путь в соответствии с реальным местоположением файла конфигурации - возможно, что он фактически находится в / etc / mysql / mysql. конф. д / туздЫ. cnf

Ручные твики

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

  innodb_buffer_pool_size = 1G # (отрегулируйте значение здесь, 50% -70% от общей ОЗУ)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # может измениться на 2 или 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - пул буферов - это область хранения для кэширования данных и индексов в памяти. Он используется для частого доступа к данным в памяти, и когда вы используете выделенный или виртуальный сервер, где БД часто является узким местом, имеет смысл предоставить этой части вашего приложения (ов) большую часть ОЗУ. Следовательно, мы даем ему 50-70% всей ОЗУ. В документах MySQL содержится руководство по калибровке пула буферов.
  • размер файла журнала хорошо объяснен здесь, но в двух словах, сколько данных нужно хранить в журнале, прежде чем вытирать его. Обратите внимание, что журнал в этом случае не является журналом ошибок или чем-то, к чему вы могли бы привыкнуть, но вместо этого указывает время контрольной точки, потому что с MySQL записи происходят в фоновом режиме, но все равно влияют на производительность переднего плана. Большие файлы журналов означают лучшую производительность из-за создания новых и меньших контрольных точек, но более длительное время восстановления в случае сбоя (больше материала необходимо переписать в БД).
  • innodb_flush_log_at_trx_commit объясняется здесь и указывает, что происходит с файлом журнала. С 1 у нас самая безопасная настройка, потому что журнал сбрасывается на диск после каждой транзакции. С 0 или 2 это меньше ACID, но более показательно. Разница в этом случае недостаточно велика, чтобы перевешивать преимущества устойчивости установки 1.
  • innodb_flush_method - в первую очередь, что касается промывки, это устанавливается в O_DIRECT , чтобы избежать двойной буферизации. Это всегда должно быть сделано, если система ввода-вывода не отличается низкой производительностью.

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

Как оптимизировать MySQL: индексы, медленные запросы, конфигурацияКак оптимизировать MySQL: индексы, медленные запросы, параметры конфигурации:
База данныхPatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt
«/><h3 id= Инспектор переменных

Чтобы установить инспектор переменных на Ubuntu:

  wget https: // repo. Percona. ком / кв / Percona-release_0. 1-4. $ (lsb_release -sc) _all. дебютанткаsudo dpkg -i percona-release_0. 1-4. $ (lsb_release -sc) _all. дебютанткаОбновление sudo apt-getsudo apt-get install percona-toolkit    

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

Затем запустите инструментарий с помощью:

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

Вы должны увидеть вывод, не похожий на этот:

  # WARN delay_key_write: Блоки индекса MyISAM никогда не сбрасываются до тех пор, пока это не будет необходимо. # ПРИМЕЧАНИЕ max_binlog_size: max_binlog_size меньше, чем значение по умолчанию 1 ГБ. # ПРИМЕЧАНИЕ sort_buffer_size-1: переменная sort_buffer_size, как правило, остается по умолчанию, если только эксперт не определяет, что ее необходимо изменить. # ПРИМЕЧАНИЕ innodb_data_file_path: автоматически расширяющиеся файлы InnoDB могут потреблять много дискового пространства, которое очень сложно восстановить позже. # WARN log_bin: двоичный журнал отключен, поэтому восстановление и репликация момента и времени невозможны.     

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

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

  max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. журналСервер-ID = 01 мастер-binlog-format = 'ROW'    
  • параметр max_binlog_size определяет, как будут вести себя большие двоичные журналы. Это журналы, которые регистрируют ваши транзакции и запросы и делают контрольные точки. Если транзакция больше макс, тогда при сохранении на диске журнал может быть больше, чем max, иначе MySQL будет держать их в этом пределе.
  • опция log_bin включает двоичный журнал в целом. Без него нет моментальных снимков или репликации. Обратите внимание, что это может быть очень напряженным на диске. Идентификатор сервера является необходимым параметром при активации двоичного ведения журнала, поэтому журналы знают, к какому серверу они пришли (для репликации) и формат - это просто способ записи журналов.

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

MySQL Tuner

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

Сальтат, просто загрузив его:

  wget https: // raw. githubusercontent. ком / главная / MySQLTuner жемчужно / мастер / mysqltuner. плchmod + x mysqltuner. пл    

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

  [-] Включен InnoDB. [-] Контекст потока InnoDB: 0[OK] Файл InnoDB для каждой таблицы активирован[OK] Пул буферов InnoDB / размер данных: 1. 0G / 11. 2M[!!] Ratio InnoDB размер файла журнала / InnoDB Размер пула буферов (50%): 256. 0M * 2/1. 0G должно быть равно 25%[!!] Пул буферов InnoDB <= 1G и Innodb_buffer_pool_instances (! = 1). 65% (19146 просмотров / всего 19809)[!!] Эффективность записи журнала InnoDB: 83. 88% (640 просмотров / 763 всего)[OK] Журнал InnoDB ждет: 0. 00% (0 ждет / 123 пишет)    

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


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

  sudo service mysql restart    

Индексы

Далее, давайте сосредоточимся на Индексах - главной болевой точке многих аддонов DB. Особенно те, кто сразу же переходит в ORM и, таким образом, никогда не подвергается действию необработанного SQL.

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

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

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

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

Итак, как мы узнаем, какие индексы добавлять, а какие типы индексов существуют?

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

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

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

 . , , ALTER TABLE `пользователи`ДОБАВИТЬ УНИКАЛЬНЫЙ ИНДЕКС `имя пользователя` (` username`) ;. , ,     

Классы / индексы Semalt обычно определяются при создании таблицы, а уникальные индексы определяются после факта, изменяя таблицу.

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

 . , , ALTER TABLE `пользователи`ДОБАВИТЬ УНИКАЛЬНЫЙ ИНДЕКС `usercountry` (` username`, `country`). , ,     

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

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

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

 . , , ALTER TABLE `пользователи`ДОБАВИТЬ ИНДЕКС `usercountry` (` username`, `country`). , ,     

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

Semalt также помогает в сортировке и группировке.

Полный текст

Индексы FULLTEXT используются для полнотекстового поиска.

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

По убыванию Индексы

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

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

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

Инструменты помощника: Объясните

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

  EXPLAIN SELECT Город. Имя FROM CityJOIN Страна ON (Город. CountryCode = Страна. Код)ГДЕ ГОРОД. CountryCode = 'IND' AND Country. Континент = 'Азия'    

Вы можете продолжить это с помощью РАСШИРЕННЫЙ :

  EXPLAIN SELECT Город. Имя FROM CityJOIN Страна ON (Город. CountryCode = Страна. Код)ГДЕ ГОРОД. CountryCode = 'IND' AND Country. Континент = 'Азия'    

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

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

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

  pt-duplicate-key-checker h = localhost, u = homestead, p = secret# ################################################################################################### ######################## усадьба. wp_posts# ################################################################################################### ######################## Key type_status_date заканчивается префиксом кластеризованного индекса# Ключевые определения:# KEY `type_status_date` (` post_type`, `post_status`,` post_date`, `ID`),# ПЕРВИЧНЫЙ КЛЮЧ (`ID`),# Типы столбцов:# `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 не null default '0000-00-00 00:00:00'# `id` bigint (20) unsigned not null auto_increment# Чтобы сократить этот дублированный кластерный индекс, выполните:ALTER TABLE `homestead`. `wp_posts` DROP INDEX` type_status_date`, ADD INDEX `type_status_date` (` post_type`, `post_status`,` post_date`);    

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

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

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

  pt-index-usage / var / log / mysql / mysql-slow. журнал    

Подробное использование этих инструментов см. Здесь.

Узкие места

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

  slow_query_log = / var / log / mysql / mysql-slow. Semalt контролирует запросы, длительностью более 1 секунды, и те, которые не используют индексы.  

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

  pt-query-digest / var / log / mysql / mysql-slow. журнал# 360 мс время пользователя, 20 мс системное время, 24. 66M rss, 92. 02M vsz# Текущая дата: Чт 13 фев 22:39:29 2014# Имя хоста: *# Файлы: mysql-slow. журнал# В целом: всего 8, 6 уникальных, 1. 14 QPS, 0. 00x параллелизм ________________# Диапазон времени: 2014-02-13 22:23:52 по 22:23:59# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= == ===== =======# Время Exec 3ms 267us 406us 343us 403us 39us 348us# Время блокировки 827us 88us 125us 103us 119us 12us 98us# Выбранные строки 36 1 15 4. 50 14. 52 4. 18 3. 89# Строки проверяются 87 4 30 10. 88 28. 75 7. 37 7. 70# Размер запроса 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === ===============================# Профиль# Rank ID запроса Время ответа Вызов 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# ==== ================== ============= ===== ====== === ===============================# Запрос 1: 0 QPS, 0x параллелизм, ID 0x728E539F7617C14D в байте 736 ______# Оценка: V / M = 0. 00# Диапазон времени: все события произошли в 2014-02-13 22:23:52# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ====== = ======= =======# Count 37 3# Время Exec 40 1ms 352us 406us 375us 403us 22us 366us# Время блокировки 42 351us 103us 125us 117us 119us 9us 119us# Выбранные строки 25 9 1 4 3 3. 89 1. 37 3. 89# Строки проверяют 24 21 5 8 7 7. 70 1. 29 7. 70# Размер запроса 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# String:# Hosts localhost# Пользователи *# Распределение Query_time# 1us# 10us# 100us ############################################################################################## ################# 1ms# 10 мс# 100 мс# 1s# 10s +# Таблицы# 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_. выдержка AS excerpt3, b0_. external_link AS external_link4, b0_. описание AS description5, b0_. созданный AS created6, b0_. обновлено AS updated7 FROM blog_article b0_ ORDER BY b0_. созданный DESC LIMIT 10    

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

  mysqldumpslow / var / log / mysql / mysql-slow. журнал    

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

  mysqldumpslow -t 10 -s at / var / log / mysql / localhost-slow. журнал    

Другие параметры см. В документах.

Заключение

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

Пропустили ли мы какие-либо методы и советы? Дайте нам знать!

Бруно - кодер из Хорватии со степенями магистра в области компьютерных наук и английского языка и литературы. Он запускает криптовалютный бизнес в Bitfalls. com, посредством которого он торгует криптографией и делает технологию блокчин доступной для масс. Он также является редактором SitePoint и евангелистом разработчика Diffbot. ком.
March 1, 2018