Оптимальные методы оптимизации базы данных MySQL. Mysql оптимизация базы данных

Оптимизация MySql базы данных блога WordPress « Variable

Доброго здоровья всем читателям блога! Сегодняшняя тема очень важна для каждого вебмастера, а посему немедленно даю вам информацию по вопросу оптимизации Mysql базы данных, которая преследует цель ускорения загрузки страниц блога WordPress.Почему это так важно? На это есть несколько причины, важнейшие из них две. Во-первых, скорость загрузки вебстраниц влияет на ранжирование ресурса в поисковой выдаче, это ”медицинский” факт. Кстати, о раскрутке ресурсов в условиях действия алгоритмов поисковых систем я писал в конкурсной статье о продвижении сайта под Пингвином. Во-вторых, быстро загружаемые страницы задерживают посетителей на сайте, ведь приятно, когда быстро можно переходить из раздела в раздел, не тратя лишних нервов. Поэтому, если даже материал на сайте заслуживает всяческого уважения, но медленно открываются страницы, читатель скорее всего уйдет и поищет более удобный с этой точки зрения ресурс. Мой блог тоже до сегодняшнего дня не отличался хорошими показателями по скорости загрузки, поэтому я решил исправить ситуацию и провести оптимизацию MySql базы данных. С алгоритмом оптимизациия и хочу с вами поделиться. Честно говоря, я пришел в ужас, когда вплотную решил заняться оптимизацией базы данных MySl и увидел ее размер, использовав для этого панель phpMyAdmin:На самом деле, объем базы данных такого небольшого ресурса, как мой блог, превышающий 30 МБ, катастрофа. Поэтому я решил немедленно уделить процессу оптимизации часть драгоценного времени. Далее представлю вам, уважаемые читатели, подробную инструкцию, что необходимо сделать, чтобы исправить ситуацию. Прежде всего, необходимо войти на страницу phpMyAdmin. Я уже писал, что сделать это можно с панели управления вашим хостингом.Поищите, там обязательно должна быть ссылка «phpMyAdmin» или что-то в этом роде. Там на левой стороне выбираете базу данных Mysql своего блога, кликнув по ней, в результате чего получите список таблиц этой базы. Теперь внимание! Прежде чем, приступить непосредственно к оптимизации, необходимо сделать резервную копию (бэкап) базы данных, используя соответствующую опцию программы phpMyadmin. Это необходимо для того, чтобы в случае ваших ошибочных действий ее можно было без проблем восстановить.Действия по оптимизации Mysql базы данных

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

wp_commentswp_linkswp_optionswp_postmetawp_postswp_termswp_term_relationshipswp_term_taxonomywp_usermetawp_users

Но при дальнейшем развитии ресурса, например, при установке различных плагинов, образуются новые таблицы. Очень часто случается так, что приходится то или иное расширение (плагин) удалить, например, для замены его соответствующим кодом (опять же для ускорения сайта). В этом случае таблицы, соответствующие удаленному расширению, остаются и лежат мертвым грузом.Например, у меня был плагин WP-Monalisa, который генерирует смайлики для отражения в комментариях. Но ввиду лишней нагрузки на сервер, создаваемой этим расширением, я его удалил, тем более при сильном желании смайлы можно использовать, вводя код в поле для комментариев. Теперь показываю, как я удалил оставшиеся от него таблицы. Для этого галочкой надо отметить соответсвующую строчку и нажать на значок удаления:Все, лишние таблицы удалены. Теперь приступаем к следующему шагу. Сначала необходимо внести определенные изменения в файл wp-config.php. Обычно он располагается в папке httpdocs или public_html. Для его редактирования лучше всего использовать замечательный редактор notepad++, который позволяет загрузить файлы с хостинга при помощи ftp-соединения и отредактировать. Конечно, можно редактировать прямо через админ панель хостинга, но с недавних пор я пользуюсь именно notepad++. Итак, открываем notepad++ и загружаем wp_config на компьютер. Вставляем в файл две следующих строки:

define('WP_POST_REVISIONS', FALSE);define('EMPTY_TRASH_DAYS', 0);

Очень внимательно отнеситесь к операции редактирования файла, чтобы избежать неприятных минут в дальнейшем. Вставьте две вышеупомянутые строчки сюда:Первая строчка отключает ревизии постов. Что это такое? Допустим, вы написали статью, затем отредактировали ее и не единожды, все эти варианты (ревизии) будут храниться в базе данных, что создает лишнюю нагрузку, поэтому нужно отключить их образование.Вторая строка лимитирует время хранения файлов в корзине. Здесь можно поставить любые значения вплоть до 0. Но имейте ввиду, что если отметите 0, то это значит, что полностью отключите корзину и все файлы будут удаляться безвозвратно, так что будьте здесь осторожны. Я поставил значение 1, как видите из скриншота. После изменения сохраните файл там же в редакторе notepad++ и нажмите «upload» для загрузки уже отредактированного файла wp_config обратно на хостинг.Теперь удалим все ранее скопившиеся ревизии. Для этого в админ панели phpMyadmin выберите базу данных и перейдите во вкладку «SQL», где введите следующий запрос:

DELETE FROM wp_posts WHERE post_type = "revision"

Это будет выглядеть следующим образом:После ввода запроса нажмите кнопку «ОК» в нижнем правом углу. Почти сразу получаете результат, вывод которого занимает доли секунды:Как видите, удалено более 2000 строк. Далее таблицу wp_post необходимо оптимизировать, вновь используя вкладку «SQL» и введя следующий запрос:

OPTIMIZE TABLE wp_posts;DELETE FROM wp_comments WHERE comment_approved = 'spam';

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

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Теперь вообще отключим запись пингбеков в базу данных Mysql c помощью очередного запроса:

UPDATE wp_posts p SET p.ping_status = 'closed'

Вот и вся работа по оптимизации MySql базы данных. Однако теперь необходимо оценить результат проделанной работы, вот он:Если вы сравните этот скриншот с тем, который я приводил в начале поста, то увидите, что объем базы данных уменьшился в 10 раз! Конечно, до такого доводить нельзя и действия по очистке базы желательно производить хотя бы раз в месяц. Надеюсь, вы получили стоящую информацию, прочитав статью, если желаете и впредь получать подобные материалы, не забывайте подписываться на обновления блога. На сегодня позвольте откланяться, новые статьи не заставят себя долго ждать.

Сохрани, чтобы не потерять

zetwin 2013-05-02 2013-05-02 zetwin

variable.pp.ua

Оптимизация базы данных MYSQL с использованием индексации PHP Lang

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

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

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

(d) Никаких дополнительных ресурсов не поможет, если ваш сервер базы данных не настроен должным образом (вы используете кеш запросов? Вы разрешаете MySQL использовать достаточное количество памяти для хранения таблиц в памяти? и т. д.).

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

Что касается вопросов с индексами, индекс не только желателен, но и необходим для «ускорения» вещей. Насколько я понимаю (в терминах «непрофессионала»), функция индекса заключается в ускорении поиска и восстановления данных внутри таблицы.

Причины использования индексов:

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

Некоторые «правила большого пальца», которые я использую, чтобы определить, какие поля нужно индексировать:

Каждый первичный ключ индексируется ( очевидный : первичный ключ должен быть уникальным, а не нулевым)
Каждый иностранный ключ должен быть проиндексирован (чтобы сделать отношения с первичными – внешними ключами эффективными)
Каждое числовое или поле даты, в которое мне нужно выполнить поиск, нужно индексировать. Тем не менее, я стараюсь избегать double (или любых других чисел с числовыми числами с плавающей запятой) для индексирования, поскольку они обычно используются для хранения значений, не предназначенных для поиска.
Каждое поле char или varchar на котором мне нужно выполнять поиск, должно быть проиндексировано. Старайтесь избегать индексов в text полях, поскольку они могут содержать в себе очень большие значения.
Избегайте индексирования двоичных ( blob ) полей … это не имеет смысла
Не поддавайтесь соблазну индексировать все. Не спешите решать, какие поля должны быть проиндексированы и какие поля не должны индексироваться.

ruphp.com

Оптимизация базы данных Wordpress. Очистка БД MySQL

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

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

Что необходимо сделать, чтобы очистить базу данных MySQL WordPress?

Зайти в Вашу панель управления хостингом
Далее зайти в phpmyadmin
Далее выбираем Вашу базу данных WordPress (на которой работает Ваш сайт)
На всякий случай сделайте резервную копию БД
Возвращаемся назад и видим список таблиц, которые вошли в базу данных WordPress. Плагины очень часто создают свои таблицы в БД, а после удаления плагинов – не очищают и не удаляют таблицы. Вам необходимо найти эти таблицы (таблицы плагинов, которые не используются уже, они, скорее всего, будут содержать его имя).
После того, как нашли таблицы, Вы отмечаете при помощи галочек ненужные и нажимаете на кнопку Удалить, затем подтверждаем это.
Заодно можно оптимизировать и оставшиеся, другие таблицы. Для этого выделяем их и нажимаем на кнопку Оптимизировать.
Все. Проверяйте работоспособность своего сайта, должно немного быстрее работать.

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

Ну что, будем и дальше рассматривать статьи по оптимизации базы данных WordPress, удачи Вам и до встречи в новых статьях.

Также советуем почитать на PressDev.RU

pressdev.ru

оптимизация базы данных PHP Lang

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

Разбиение в MySQL

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

Например, скажем, у вас есть таблица сотрудников, и у вас есть обычные столбцы SSN, FNAME, LNAME. В дополнение к этим столбцам мы скажем, что у вас есть еще 10 столбцов в таблице.

Теперь у вас есть этот запрос:

SELECT FNAME, LNAME FROM EMPLOYEES WHERE SSN = 'blah';

Игнорируя тот факт, что SSN, вероятно, может быть основным ключом здесь и может иметь уникальный индекс, вы, вероятно, увидите преимущество в производительности, создав еще один составной индекс, содержащий столбцы (SSN, FNAME, LNAME). Причина, по которой это выгодно, заключается в том, что база данных может удовлетворить этот запрос, просто глядя на составной индекс, поскольку он содержит все значения, необходимые в отсортированном и компактном пространстве. (т. е. меньше ввода-вывода). Несмотря на то, что индекс SSN является лучшим методом доступа к выполнению полного сканирования таблицы, база данных все еще должна считывать блоки данных для индекса (ввода-вывода), находить значения (-ы), которые будут содержать указатели на записи необходимых для удовлетворения запроса, тогда вам нужно будет прочитать разные блоки данных (прочитайте: более случайный ввод-вывод), чтобы получить фактические значения для fname и lname.

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

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

Индексы MySQL – сколько их достаточно?
Когда следует использовать составной индекс?
Оптимизация запросов MySQL (в частности, раздел «Выбор индексов»)

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

Кроме того, никогда не спрашивайте «как улучшить вообще». Нет никакой «общей» оптимизации. Оптимизация всегда является результатом профилирования и исследования вашего конкретного случая.

В вашем примере «WHERE x = y», если y является именем столбца, также создайте индекс с y.

Ключ с индексом – это № результата из вашего запроса select, который должен составлять около 3% ~ 5%, сравнивая всю таблицу, и он будет быстрее.

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

Если вы выполняете заказы или сложные запросы, вам может понадобиться использовать индексы с несколькими столбцами. Например, если вы ищете, где x.name = 'y' OR x.phone = 'z', возможно, стоит поместить индекс по имени, телефону. Упрощенный пример, но если вам нужно это сделать, вам все равно нужно исследовать его дальше 🙂

ruphp.com

optimization - PHP и MySQL: оптимизация базы данных

Разделение в MySQL

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

Например, скажем, у вас есть таблица сотрудников, и у вас есть обычные столбцы SSN, FNAME, LNAME. В дополнение к этим столбцам, мы скажем, что у вас есть еще 10 столбцов в таблице.

Теперь у вас есть этот запрос:

SELECT FNAME, LNAME FROM EMPLOYEES WHERE SSN = 'blah';

Игнорируя тот факт, что SSN, вероятно, может быть основным ключом здесь и, возможно, уже имеет уникальный индекс, вы, вероятно, увидите преимущество в производительности за счет создания другого составного индекса, содержащего столбцы (SSN, FNAME, LNAME). Причина, по которой это выгодно, заключается в том, что база данных может удовлетворить этот запрос, просто глядя на составной индекс, поскольку он содержит все значения, необходимые в отсортированном и компактном пространстве. (т.е. меньше ввода-вывода). Несмотря на то, что индекс SSN является лучшим методом доступа к выполнению полного сканирования таблицы, база данных все еще должна считывать блоки данных для индекса (ввода-вывода), находить значения (-ы), которые будут содержать указатели на записи необходимых для удовлетворения запроса, тогда вам нужно будет прочитать разные блоки данных (прочитайте: более случайный ввод-вывод), чтобы получить фактические значения для fname и lname.

Здесь вы можете найти несколько других ссылок:

qaru.site

Как заработать на WMmail.ru - Оптимизируем базу данных MySQL

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

Давайте рассмотрим из чего состоит база данных движка WordPress. А выглядит она следующим образом:

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

Поэтому, я рекомендую абсолютно для всех, регулярно создавать бэкапы MySQL. Для этого существует очень практичный плагин под названием WordPress Database Backup. Устанавливаете, активируете и в панели управления в разделе «Инструменты» появится новая вкладка: «Резервное копирование». Все настроики на русском языке, так что разберется каждый. Обязательно укажите E-mail адрес на который будут отправлятьсябэкапы базы данных, а также интервал времени (каждый день или раз в неделю).

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

Так, с этим разобрались. Продолжаем изучать MySQL:

wp_commentmeta — таблица, отвечающая за хранение информации о процессе комментирования (одобрен, удален, спам и т.д.)
wp_comments — таблица, которая содержит в себе все комментарии сайта
wp_links — включает в себя ссылки WordPress, установленные по умолчанию, а также ссылки добавленные администратором в панели управления в соответствующей вкладке «Ссылки»
wp_options — очень важная таблица, так как содержит в себе все настройки WordPress и плагинов
wp_postmeta — содержит информацию обо всех файлах (картинки, видео и многое другое)
wp_posts — наверное самая важная таблица, которая состоит из контента сайта (записи, страницы и прочее). Да, именно здесь и хранятся все наши труды и публикации!
wp_terms — включает в себя категории , ссылки и теги сайта
wp_term_relationships — связывает теги, ссылки и категории с соответствующими записями (страницами)
wp_term_taxonomy — содержит записи дочерних рубрик и связывает их с соответствующими публикациями
wp_usermeta — таблица, в которой обирается вся информация о зарегистрированных пользователях (данные, настройки профиля и прочее)
wp_users — хранит все данные пользователей, в том числе и администратора (логины, пароли и прочее)

Это стандартный набор таблиц, формирующийся при «голой» установке WordPress. Все остальные таблицы, создаются во время использования самого движка (установка плагинов и других действий).

Ну а теперь переходим непосредственно к оптимизации базы данных MySQL.

Тут нам также необходимо проделать ряд действий. Я не удивлюсь, что у большинства тех, кто читает данный пост, до сих пор на сайтах не отключено автосохранение ревизий заметок. Если не знаете, что это такое, почитайте об этом статью и отключите данную функцию. Готово? Отлично!

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

Выберите нужную вам базу данных, выделите все ее таблицы и нажмите на вкладку «Экспорт»:

В следующем появившемся окне, внизу, ставите галочку напротив «Сохранить как файл» и нажимаете «Ок». База скачается на ваш компьютер.

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

DELETE FROM wp_posts WHERE post_type = "revision"

и наблюдаем следующую картину:

Удалено столько-то строк. Хорошо, переходим к следующему этапу — оптимизируем таблицу wp_postmeta. А именно удалим ненужную информацию, записывающуюся WordPress во время редактирования публикаций. Это своего рода «мусор», занимающий также дополнительное место в базе данных MySQL. Копируем и вставляем в поле SQL следующий запрос:

DELETE FROM `wp_postmeta` WHERE `meta_key` IN('_edit_lock', '_edit_last','_wp_old_slug')

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

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

Удаляется данная информация путем ввода запроса:

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

Отлично! Теперь, все, что нам осталось сделать — выделить все таблицы и оптимизировать средствами phpMyAdmin:

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

Удачи!

sekretwmmail.ucoz.ru

performance - Оптимальные методы оптимизации базы данных MySQL

"Лучшая практика":

Измерьте производительность, изолируя соответствующую подсистему, а также можете.
Определите основную причину узкого места. Вы связаны с I/O? ЦП связан? Память связана? Ожидание блокировок?
Внесите изменения, чтобы устранить причину, которую вы обнаружили.
Мера снова, чтобы продемонстрировать, что вы исправили узкое место и на сколько.
Перейдите к шагу 2 и повторите по мере необходимости, пока система не будет работать достаточно быстро.

Подпишитесь на RSS-канал в http://www.mysqlperformanceblog.com и прочитайте его исторические статьи. Это очень полезный ресурс для мудрости, связанной с производительностью. Например, вы спросили о InnoDB и MyISAM. Их вывод: InnoDB имеет на 30% более высокую производительность, чем MyISAM в среднем. Хотя есть также несколько сценариев использования, в которых MyISAM выдает InnoDB.

Авторы этого блога также являются соавторами "High Performance MySQL", книги, упомянутой @Andrew Barnett.

Re comment from @ʞɔıu: Как определить, связана ли привязка ввода/вывода от привязки процессора к памяти, зависит от платформы. Операционная система может предлагать такие инструменты, как ps, iostat, vmstat или top. Или вам, возможно, придется обратиться к стороннему инструменту, если ваша ОС не предоставляет его.

В принципе, какой бы ресурс не был привязан к 100% использованию/насыщенности, скорее всего, будет вашим узким местом. Если загрузка вашего процессора низкая, но ваша нагрузка ввода-вывода максимальна для вашего оборудования, тогда вы привязаны к вводу/выводу.

Это только одна точка данных. Средство может также зависеть от других факторов. Например, сложный SQL-запрос может выполнять файловое управление, и это заставляет занятые операции ввода-вывода. Должно ли вы бросать на него больше/быстрее оборудования или вы должны перепроектировать запрос, чтобы избежать файлового управления?

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

Джефф Этвуд просто написал хорошую статью в блоге о поиске узких мест в системе:

qaru.site