Оптимизация базы sql - Новости с мира ПК
Vvmebel.com

Новости с мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Оптимизация базы sql

10 лучших приемов для оптимизации работы с MySQL

1. LIMIT 1 Когда нужно извлечь из таблицы уникальную строку

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

В таких случаях, использование метода LIMIT 1 может существенно увеличить производительность:

2. Оптимизация работы с базой с помощью обработки кэша запросов

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

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

3. Индексация полей поиска

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

Как вы понимаете, это правило также распространяется на часть строки поиска: такую как «l ast_name LIKE ‘% ‘». Когда поиск производится по началу строки, MySQL может использовать для этого столбца индексацию.

Вы также должны понимать, какие виды запросов не могут использовать обычные индексы. Например, при поиске слова (например, «WHERE post_content LIKE ‘%tomato%’»), применение обычного индекса вам ничего не даст. В таком случае лучше будет использовать поиск MySQL на полное соответствие или создать свой собственный индекс.

4. Индексирование и использование столбцов одинакового типа при объединении

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

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

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

5. По возможности не используйте запросы типа SELECT *

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

6. Пожалуйста, не используйте метод сортировки ORDER BY RAND()

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

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

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

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

7. Используйте столбцы типа ENUM вместо VARCHAR

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

Если у вас есть некоторое поле, которое содержит несколько разных значений одного вида, то вместо столбцов типа VARCHAR лучше использовать ENUM. Например, это может быть столбец « Статус », который содержит только такие значения, как « активно », « неактивно », « ожидание », « срок действия истек » и т.д.

Существует даже возможность задать сценарий, при котором MySQL будет «предлагать» изменить структуру таблицы. Когда у вас есть поле типа VARCHAR, система может автоматически рекомендовать изменить формат столбца на ENUM. Это можно сделать с помощью вызова функции PROCEDURE ANALYSE() .

Используйте для хранения IP-адресов поля типа UNSIGNED INT

Многие разработчики создают для этих целей поля типа VARCHAR (15) , в то время как IP-адреса можно было бы хранить в базе в виде десятичных чисел. Поля типа INT предоставляют возможность хранить до 4 байта информации, и при этом для них можно задать фиксированный размер поля.

Вы должны удостовериться, что ваши колонки имеют формат UNSIGNED INT , поскольку IP-адрес задается 32-мя битами.

В запросах можно использовать параметр INET_ATON () для преобразования IP-адресов в десятичные числа, и INET_NTOA () — наоборот. PHP имеет и другие аналогичные функции long2ip () и ip2long () .

8. Вертикальное секционирование (разделение)

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

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

Таким образом, ваша основная таблица пользователей заметно уменьшится в размерах. А как вы знаете, меньшие таблицы, обрабатываются быстрее.

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

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

9. Меньшие столбцы – быстрее

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

В MySQL Docs прописан ряд требований к хранению разных типов данных. Если ожидается, что таблица не будет содержать слишком большое количество записей, то нет причин хранить первичный ключ в полях типа INT, MEDIUMINT, SMALLINT , а в отдельных случаях даже TINYINT . Если в формате даты вам не нужны составляющие времени (часы : минуты), то используйте поля типа DATE вместо DATETIME.

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

10. Правильно выбирайте движок

Существует два основных движка баз данных MySQL: MyISAM и InnoDB . У каждого есть свои преимущества и недостатки.
MyISAM хорош для работы с «тяжелыми» приложениями, но хуже справляется с обработкой данных, если в базе большое количество записей.

Даже если вы обновляете всего одно поле одной из строк, вся таблица может быть заблокирована, и никакой другой процесс не сможет получить к ней доступ, пока не завершится этот запрос. В то же время MyISAM очень быстр в обработке запросов типа SELECT COUNT (*) .

InnoDB – это более сложный механизм и может медленнее, чем MyISAM работать с большинством приложений. Но он поддерживает функции, которые позволяют более эффективно работать с базами больших размеров.

Данная публикация представляет собой перевод статьи « 10 MySQL Best Practices for Optimization » , подготовленной дружной командой проекта Интернет-технологии.ру

Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

Главное меню » Базы данных » База данных MySQL » Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

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

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

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

1. Определение таблиц для оптимизации

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

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

В приведенном выше выводе:

  • Отображает список всех таблиц, которые имеет минимум 500MB неиспользованного пространства. Как мы видим выше, в данном примере, есть 3 таблицы, которые имеют более чем 500MB неиспользованного пространства.
  • Колонка data_length_mb показывает общий размер таблицы в MB. Например, размер таблицы EMPLOYEE составляет около 21GB.
  • Столбец data_free_mb отображает общее неиспользуемое пространство в этой конкретной таблице. Например, таблица EMPLOYEE имеет 19MB неиспользованного пространства.
  • Все эти три таблицы (EMPLOYEE, DEPARTMENT и MANAGERS) сильно фрагментированы, и они должны быть оптимизированы, чтобы освободить неиспользуемое пространство.
Читать еще:  Как сделать свою видеокарту

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

Размер файла будет таким же, как вы видите в колонке “data_length_mb” в приведенном выше выводе.

В этом примере файл EMPLOYEE.MYD занимает 21GB на уровне файловой системы, но он имеет много неиспользованного пространства. Если мы оптимизируем эту таблицу, размер этого файла должен сильно уменьшится.

2. Дефрагментация с помощью команды OPTIMIZE TABLE

Есть два способа оптимизации таблицы.

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

Следующий пример позволит оптимизировать таблицу EMPLOYEE.

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

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

  • Оптимизация таблицы может быть выполнена для InnoDB или MyISAM или таблиц архивов.
  • Для таблиц MyISAM, будет произведен анализ таблицы, и будет дефрагментировать соответствующий MySQL файл данных, и освободит неиспользуемое пространство.
  • Для таблиц InnoDB, после оптимизации таблицы надо будет просто выполнить alter table, чтобы восстановить пространство.
  • Если у вас есть индексы, они также изменят страницы индекса, и обновят статистику.

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

В приведенной выше оптимизации, таблица EMPLOYEE представляет собой таблицу MyISAM.

Для этого примера, перед оптимизацией, вы увидите следующий файл с расширением .MYD для таблицы.

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

После завершения команды оптимизируют таблицы, вы не увидите временную таблицу. Вместо этого вы увидите оригинальный файл EMPLOYEE.MYD, который оптимизирован и с уменьшенным размером файла.

3. Дефрагментация с помощью команды mysqlcheck

Второй метод для оптимизации таблицы используется команда mysqlcheck, как показано ниже.

Следующий пример позволит оптимизировать таблицу отделов. Вы выполнить эту команду из командной строки Linux (а не в MySQL).

Примечание: Внутренняя команда mysqlcheck использует команду “OPTIMIZE TABLE”.

В приведенном выше примере:

  • mysqlcheck это команда, которая выполняется из командной строки Linux.
  • -o параметр, чтобы указать, что mysqlcheck должен выполнять операцию “OPTIMIZE TABLE”.
  • AndreyExBase является база данных
  • DEPARTMENT является таблица внутри базы данных AndreyExBase, которая должна быть оптимизирована
  • -u root указывает на то, что команда mysqlcheck должна использовать “root” в качестве пользователя mysql для подключения
  • -p указывает пароль для root MySQL. Обратите внимание, что нет пространства между опцией -p и паролем.

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

4. Дефрагментация всех таблиц или всех баз данных

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

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

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

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

5. После оптимизации

После оптимизации, используя следующий запрос, проверьте total-size и unused-space-size для трех таблиц, которые мы оптимизировали в этом примере.

Как видно из приведенных выше выходных данных, то data_length_mb сильно уменьшилась для этих таблиц после оптимизации. Кроме того, data_free_mb теперь равна 0, так как нет никакой фрагментации больше.

Размер файла для этих таблиц теперь намного меньше по сравнению с исходным размером. Теперь мы оптимизировали много неиспользуемого пространства на уровне файловой системы для этих таблиц.

В этом примере, мы освободили около 37GB неиспользованного пространства после оптимизации этих трех таблиц.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

От скорости работы баз данных (БД) зависит быстрота отклика сайта. Ведь замедленная обработка запросов влияет на PHP, следовательно — накапливается огромное количество операций, с которыми сервер может не справиться.

Управлять данным процессом позволяет использование систем управления базами данных или СУБД. Одной из самых широко применяемых СУБД является MySQL — ПО с открытым исходным кодом, созданное компанией MySQL AB (Oracle) ещё в 1995 году. Оптимизация MySQL позволяет избежать проблем с производительностью сервера и значительно ускорить интернет-ресурс.

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

Зачем оптимизировать работу MySQL

  • Увеличение скорости обработки и выполнения запросов. Скорость работы сайта прямо пропорционально зависит от времени обработки и выполнения SQL-запроса к базе данных, которое должно быть минимальным.
  • Предотвращение перегрузки сервера. При перегрузке сервера работа web-ресурса или приложения будет нестабильной. Хостер может заблокировать ресурс, чтобы последний не нарушал работу всего сервера, на котором также работают и другие сайты.
  • Уменьшение времени ожидания загрузки web-страницы. Когда идет огромное количество SQL-запросов к базе данных, происходит существенное замедление работы сайта, что недопустимо для коммерческого или представительского интернет-ресурса.
  • Экономия ресурсов хостинга. Если MySQL не оптимизирована, то происходит значительный перерасход использования ресурсов сервера (процессорного времени, оперативной памяти). На этом основании хостер имеет право заблокировать работу ресурса.
  • Возможность масштабировать ресурс. При расширении сайта будет невозможно обеспечить хорошее качество его работы. Например, арендатор VPS загрузил на сервер интернет-магазин, в котором 100 видов товаров. Через некоторое время бизнес расширяется и появляется возможность предложить потребителю 10000 разновидностей. После загрузки и доработки интернет-магазин начинает работать медленно, постоянно происходят ошибки при помещении товаров в корзину и т. д.

Какие ресурсы желательно оптимизировать

  • Средне и высоконагруженные сайты с посещаемостью от 10 000 человек в сутки.
  • Сервера разработчиков веб-пиложений и сервисов.
  • Интернет-магазины с базой товаров, превышающей 100 единиц.
  • Высоконагруженные прокси/VPN сервера на основе VPS.
  • Системы работы с финансами и бухгалтерские приложения (продукты «1 C», ПланФакт, «Моё дело», Livesklad).
  • Приложения для мониторинга сервисов компьютерных сетей и серверов (Zabbix, Prometheus, Grafana).
  • Сайты с высокой посещаемостью и регистрациями, превышаемыми 100 пользователей в сутки.
  • Контент-биржи (Text.ru, Advego, Copylancer).
  • Видео- и фотохостинговые ресурсы (YouTube, Vimeo, Imgur).
  • Рекламно-баннерные площадки (myTarget, Adfox).
  • Финансово-экономические игры с возможностью вывода денег (Your Real Town, Surfer Money).

Скорость работы баз данных

Чтобы оптимизация СУБД MySQL дала результат, нужно начать с анализа работы баз данных. Настройки сервиса содержатся в файле /etc/my.cnf .

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

Информация указана в строчках:

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

Чтобы увидеть актуальные данные, сервер перезапускается. Сведения находятся в логе:

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

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

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

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

Первоначальная настройка

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

Чтобы скрипт работал и показывал текущие проблемы, необходимо загрузить три файла через wget :

Первый файл — это скрипт написан на Perl. Два остальных — сведения о простых паролях и уязвимостях, которые позволяют найти проблемы с безопасностью.

Далее необходимо провести тест базы данных. Оптимизация производительности будет основана на выявленных проблемах. Тест запускается скриптом # perl ./mysqltuner.pl . Он выдает полную статистику работы базы. Все проблемные места обозначаются красным восклицательным знаком [!] .

Следует помнить, что параметры изменяются согласно рекомендациям, которые выдает утилита. Нельзя бездумно копировать параметры из статьи и применять их к собственной базе данных. В ином случаем можно столкнуться с рядом практических проблем. Например, недостаточным размером буфера движка таблиц (InnoDB buffer pool).

Читать еще:  Как узнать модель встроенной видеокарты

Дополнительная настройка производительности

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

Рабочие параметры

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

Для MariaDB

Для настройки производительности вручную необходимо выполнить изменения указанных ниже параметров в файле конфигурации наиболее популярной версии MySQL MariaDB – /etc/mysql/conf.d/app.cnf .

  • tmp_table_size — максимальная величина ОЗУ (оперативной памяти), выделяемая для хранения временных таблиц. Последние создаются при формировании SQL-запросов, которые осуществляют выбор данных из таблицы для дальнейших операций над ними. Значение необходимо выбирать экспериментальным путем, руководствуясь соотношением tmp_table_size = объем_ОЗУ * 0,75 (tmp_table_size = 64 М).
  • max_heap_table_size — максимальный размер таблицы, которая хранится в ОЗУ. Рекомендуемое значение параметра max_heap_table_size должно быть равным величине tmp_table_size.
  • query_cache_size — объем ОЗУ, выделяемого под кеш SQL-запросы. Его рекомендуется отключить для проектов с большим количеством записей, т. е. query_cache_size=0.
  • query_cache_type —параметр, активирующий или деактивирующий службу управления кешем в MySQL. Его рекомендуется отключить, установив query_cache_type в 0, т. е. query_cache_type = 0.
  • join_buffer_size — значение количества объема ОЗУ, которое выделяется для объединения таблиц баз данных. Рекомендуемое значение 8М.
  • sort_buffer_size — объем ОЗУ, выделяемый для выполнения операций сортировки данных. Рекомендуется установить значение, равное 10М .
  • max_connections — опция, определяющее максимальное число соединений с БД, приобретает значение, когда возникает сообщение об ошибке «Too many connections». Повышать это значение следует постепенно, вплоть до исчезновения ошибки.

Для InnoDB

Дополнительной оптимизации MySQL способствует настройка параметров наиболее популярной подсистемы MySQL для работы с таблицами InnoDB .

  • innodb_buffer_pool_size — если используются только таблицы InnoDB, необходимо установить максимально возможное значение с учетом технических характеристик системы. Оно должно быть 70-80% от доступной оперативной памяти. Например, при ОЗУ в 32 Гб: innodb_buffer_pool_size = 24G.
  • innodb_log_file_size — чем выше показатель, тем быстрее работают записи, то есть большее их количество помещается в файл лога. Поскольку файла всегда два, параметр задает значение только для одного. Например, innodb_log_file_size = 512M в сумме даст 1 G.
  • innodb_log_buffer_size — определяет размер буфера транзакций и изменяется только в том случае, если используются большие поля (TEXT, BLOB). 1М по умолчанию хватает в большинстве случаев.
  • innodb_flush_log_at_trx_commit — повышает пропускную способность записи данных в базу. Решает, сбрасывает ли MySQL каждую операцию в файл лога. В большинстве случаев подойдет вариант innodb_flush_log_at_trx_commit = 2. Следует учитывать два типа значения, где 1 — сохранность данных играет первую роль, 2 — небольшая потеря данных не критична благодаря дублированию.

Объединение таблиц

Часто проблема при работе с объемными базами данных возникает при выборке — попытке объединения (JOIN) столбцов из разных таблиц.

Чтобы ускорить JOIN больших таблиц MySQL, необходимо убедиться в том, что все столбцы проиндексированы. Скорость операции будет выше, если объединяются столбцы одного типа. При JOIN типов DECIMAL и INT, сервер не сможет использовать как минимум один индекс.

После завершения оптимизации тестирование проводится с помощью клиента MySQL:

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

Типы объединений

MySQL позволяет выполнять 3 типа объединений записей в двух таблицах ( table_A и table_B ):

  • внутреннее;
  • левостороннее внешнее;
  • правостороннее внешнее.

Пример № 1 — внутреннее

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

Чтобы выполнить SQL-запрос, нужно запустить терминал, а затем активировать консоль MySQL командой:

Далее необходимо ввести пароль и SQL-запрос: SELECT * FROM tableA INNER JOIN tableB ON tableA.name = tableB .name, где «name» — имя поля для таблицы А.

Пример № 2 — левостороннее

Суть объединения заключается в миграции данных из таблицы « table_B » , которых нет в таблице « table_A » , в последнюю . Используется комбинация зарезервированных слов LEFT-OUTER .

SQL-запрос будет иметь такой вид: SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.name = tableB.name . Если в « tableA » больше полей, чем в « tableB » , то в первую запишутся значения NULL.

Пример № 3 — правостороннее

Главной является таблица « tableB » . Для объединения необходимо применить ключевые слова RIGHT-OUTER .

В консоли СУБД нужно набрать SQL-запрос: SELECT * FROM tableB RIGHT OUTER JOIN tableA ON tableA.name = tableB.name . Недостающие поля заполняются константой NULL .

Заключение

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

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

Нужен производительный хостинг для высоконагруженных проектов на MySQL? Выбирайте VPS от Eternalhost — сервер на быстрых SDD, гарантированные ресурсы, запуск за 5 минут.

Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

Главное меню » Базы данных » База данных MySQL » Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

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

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

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

1. Определение таблиц для оптимизации

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

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

В приведенном выше выводе:

  • Отображает список всех таблиц, которые имеет минимум 500MB неиспользованного пространства. Как мы видим выше, в данном примере, есть 3 таблицы, которые имеют более чем 500MB неиспользованного пространства.
  • Колонка data_length_mb показывает общий размер таблицы в MB. Например, размер таблицы EMPLOYEE составляет около 21GB.
  • Столбец data_free_mb отображает общее неиспользуемое пространство в этой конкретной таблице. Например, таблица EMPLOYEE имеет 19MB неиспользованного пространства.
  • Все эти три таблицы (EMPLOYEE, DEPARTMENT и MANAGERS) сильно фрагментированы, и они должны быть оптимизированы, чтобы освободить неиспользуемое пространство.

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

Размер файла будет таким же, как вы видите в колонке “data_length_mb” в приведенном выше выводе.

В этом примере файл EMPLOYEE.MYD занимает 21GB на уровне файловой системы, но он имеет много неиспользованного пространства. Если мы оптимизируем эту таблицу, размер этого файла должен сильно уменьшится.

2. Дефрагментация с помощью команды OPTIMIZE TABLE

Есть два способа оптимизации таблицы.

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

Следующий пример позволит оптимизировать таблицу EMPLOYEE.

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

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

  • Оптимизация таблицы может быть выполнена для InnoDB или MyISAM или таблиц архивов.
  • Для таблиц MyISAM, будет произведен анализ таблицы, и будет дефрагментировать соответствующий MySQL файл данных, и освободит неиспользуемое пространство.
  • Для таблиц InnoDB, после оптимизации таблицы надо будет просто выполнить alter table, чтобы восстановить пространство.
  • Если у вас есть индексы, они также изменят страницы индекса, и обновят статистику.

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

В приведенной выше оптимизации, таблица EMPLOYEE представляет собой таблицу MyISAM.

Для этого примера, перед оптимизацией, вы увидите следующий файл с расширением .MYD для таблицы.

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

После завершения команды оптимизируют таблицы, вы не увидите временную таблицу. Вместо этого вы увидите оригинальный файл EMPLOYEE.MYD, который оптимизирован и с уменьшенным размером файла.

3. Дефрагментация с помощью команды mysqlcheck

Второй метод для оптимизации таблицы используется команда mysqlcheck, как показано ниже.

Следующий пример позволит оптимизировать таблицу отделов. Вы выполнить эту команду из командной строки Linux (а не в MySQL).

Примечание: Внутренняя команда mysqlcheck использует команду “OPTIMIZE TABLE”.

В приведенном выше примере:

  • mysqlcheck это команда, которая выполняется из командной строки Linux.
  • -o параметр, чтобы указать, что mysqlcheck должен выполнять операцию “OPTIMIZE TABLE”.
  • AndreyExBase является база данных
  • DEPARTMENT является таблица внутри базы данных AndreyExBase, которая должна быть оптимизирована
  • -u root указывает на то, что команда mysqlcheck должна использовать “root” в качестве пользователя mysql для подключения
  • -p указывает пароль для root MySQL. Обратите внимание, что нет пространства между опцией -p и паролем.
Читать еще:  Подключение видеокарты nvidia

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

4. Дефрагментация всех таблиц или всех баз данных

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

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

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

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

5. После оптимизации

После оптимизации, используя следующий запрос, проверьте total-size и unused-space-size для трех таблиц, которые мы оптимизировали в этом примере.

Как видно из приведенных выше выходных данных, то data_length_mb сильно уменьшилась для этих таблиц после оптимизации. Кроме того, data_free_mb теперь равна 0, так как нет никакой фрагментации больше.

Размер файла для этих таблиц теперь намного меньше по сравнению с исходным размером. Теперь мы оптимизировали много неиспользуемого пространства на уровне файловой системы для этих таблиц.

В этом примере, мы освободили около 37GB неиспользованного пространства после оптимизации этих трех таблиц.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Как оптимизировать базы данных MySQL, Oracle и MSSQL в известных движках

Блоги, сайты, интернет-страницы занимают неотъемлемую часть рекламного пиара любой компании в современном мире. Торможение веб-страниц совершенно не способствует повышению рейтинга и продвижению на веб-ресурсах. Чтобы избежать подобных проблем, необходимо оптимизировать работу сервера, удалив лишнюю и устаревшую информацию. Процедура оптимизации проводится в системе управления базами данных (СУБД) или движке, которые повышают производительность. Обеспечение наибольшего быстродействия с минимальными затратами является условием оптимальной работы.

Какие базы данных существуют

Самыми распространенными базами данных являются:

Из CMS-движков большую популярность получили:

Как можно провести оптимизацию баз данных

Существует много разновидностей хранилищ данных, но принципы по их оптимизации сводятся к работе над:

Оптимизация базы данных через операции в структуре сводится, в основном, к работе над таблицами с индексами.

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

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

Примеры оптимизации различных баз данных

Как провести оптимизацию в MySQL

Действия с таблицами – основная оптимизация базы данных MYSQL посредством phpMyAdmin. Последовательность выполнения оптимизации таблиц:

  1. найти базу и вывести перечень таблиц;
  2. из всего списка выбрать фрагментированные нажатием кнопки «требующие оптимизации»;
  3. оптимизировать соответствующей кнопкой.

Перед проведением процедуры рекомендуется провести копирование хранилища данных. Также возможна оптимизация работы SQL-запросом инструментом mysqlcheck с аргументом -o (optimize) в командной строке.

Оптимизировать базу данных возможно через журнал Slow query log, в котором указываются замедленные запросы. Чтобы включить в MySQL задается параметр 1 для slow_query_log. Чтобы закрыть – 0.

Выполнить действие – log-slow-queries=путь к нужному файлу .

Важно не забыть задать n long_query_time , который сортирует запросы по быстродействию и записывает медленные. Пролистав страницы журнала, нужно провести выборку запросов, которые помогут оптимизировать базу данных.

Оптимизация сервера через настройки проводится программой MySQLTuner, написанной на языке программирования Perl. Установки не требует. Скачивается и запускается посредством выбора команды perl mysqltuner.pl с введением логина, пароля от имени администратора. Результат выводится по блокам. Проблемные места указаны символом «!». В блоке Recommendations указано, что нужно оптимизировать.

Не стоит забывать о перезапуске сервера после внесения изменений.

Как увеличить производительность Tempdb

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

Оптимизируем базу данных необходимыми настройками рабочей среды:

  1. вид восстановления – simple, что автоматически производит резервирование места для журнала посредством понижения требований к размеру диска;
  2. установить авторасширение файлов;
  3. включить соответствующий шаг увеличения filegrowth, который составляет для файлов до 100 МБ – шаг 10 МБ, до 200 МБ – шаг 20 МБ, более 200 МБ – 10%, либо галочка автоувеличение;
  4. создавать только необходимое количество файлов;
  5. ускорение производительности возможно за счет одинакового размера файлов;
  6. размещение базу данных в месте, обеспечивающем быстрый доступ для ввода и вывода.

Автоматическая оптимизация в базе данных Oracle

Прогресс современного мира позволяет оптимизировать работу автоматически при помощи программ. На сервере Oracle такую процедуру проводят посредством Spotlight on Oracle и SQL Expert:

  1. запускается программа Spotlight on Oracle и создается учетная запись: файл User Wizard;
  2. в появившемся окне отображаются все процессы с указанием степени неоптимальности при помощи цвета от зеленого до красного;
  3. нажимая на соответствующие процедуры, выводится автоматическая рекомендация по устранению проблемы;
  4. пункт меню Navigator – Top Sessions позволяет выявить запросы, потребляющие больше всего ресурсов;
  5. затем открывают SQL Expert и заходим в функцию Optimize, которая выводит список альтернативных методов построения заданного сценария;
  6. анализ по времени выполнения запросов запускается при помощи нажатия функции SQL – Butch Run, с последующим выбором оптимального варианта.
  • Последовательность оптимизации WordPress

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

    1. Зайти в основную папку сайта, открыть файл с названием wp-config.php или wp-config-sample.php, воспользовавшись Notepad++ (или любым другим текстовым редактором). Теперь проведем SQL оптимизацию базы данных. В строчках, отвечающих за ревизию и срок хранения, например, постов в блоге, проставляются параметры: (‘WP_POST_REVISIONS’, false), (‘EMPTY_TRASH_DAYS’, 1) . Это означает, что ранее написанные файлы не будут сохраняться, а из корзины будут удаляться после одного дня хранения.
    2. Нужно удалить файлы, которые не вошли в пункт 2. Автоудаление начинается с момента введения новых параметров, не касается ранее сохраненных. Выбрать базу данных, во вкладке SQL ввести – DELETE FROM wp_posts WHERE post_type = «revision» . Нажимаем «Ок».
    3. Затем оптимизировать таблицы посредством ввода строки – OPTIMIZE TABLE wp_posts .
    4. Последующая оптимизация работы базы данных проводиться удалением спам-комментариев – DELETE FROM wp_comments WHERE comment_approved = ‘spam’ и уведомлений о ссылках на страницы – DELETE FROM wp_comments WHERE comment_type = ‘pingback’ .
    5. Оптимизируем папку с параметрами времени последнего внесения изменений и ранее сохраненных ссылок: DELETE FROM wp_postmeta WHERE meta_key IN(‘_edit_lock’, ‘_edit_last’,’_wp_old_slug’) .
    6. После установки DB Optimize, последующая оптимизация будет упрощена. Работает со многими базами данных Oracle, Microsoft SQL, Sybase и DB2. На web-сайте в панели управления находим оптимизатор, нажимаем «Optimize Now», процедуру перезапускают минимум 1 раз за месяц. После окончания желательно отключить, чтобы снизить нагрузку на сервер.

    Важно! До начала работы необходимо сохранить копию базы, чтобы при неправильных решениях была возможность начать с первых шагов. Открыть и экспортировать на ПК, сохранив все в виде файла.

    Как оптимизировать WordPress без изменения кода

    Более простой и быстрый способ оптимизировать базу – применение плагина WP Clean Up:

    1. установите плагин, проведите активацию;
    2. в настройках страницы находим Wp Clean Up;
    3. удаляем ненужное кнопкой Delete;
    4. проводим оптимизацию – Optimize.

    Внимание! При наличии на сайте черновиков и недавно добавленных комментарий, не рекомендуется ничего удалять из Draft и Moderated Comments.

    Общие принципы по оптимизации Joomla и Drupal

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

    • включить кэширование от суток до одной недели (в минутах);
    • установить сжатие страниц в общих настройках сервера – Gzip;
    • удалить лишние расширения, которые не используются;
    • использовать изображения с малыми размерами;
    • воспользоваться phpMyAdmin и привести хранилище к оптимальным вариантам;
    • зарегистрироваться на Яндекс.Вебмастере и выявить самые загруженные страницы.

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

    голоса
    Рейтинг статьи
  • Ссылка на основную публикацию
    Adblock
    detector