Чайникам 

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

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

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

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

// этот запрос MySQL закэшировать не сможет $res = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // сделать можно иначе $today = date("Y-m-d"); $res = mysql_query("SELECT username FROM user WHERE signup_date >= "$today"");

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

Просмотрите как выполняется ваш запрос с помощью синтаксиса EXPLAIN

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

Когда вам нужна одна запись, выставляйте LIMIT 1

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

// запрос города с кодом Shymkent из базы $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent""); if (mysql_num_rows($res) > 0){ } // добавляем LIMIT 1 для оптимизации запроса $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent" LIMIT 1"); if (mysql_num_rows($res) > 0){ }

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

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

// тут сработает индекс city LIKE ‘shym%’ // тут же индекс задействован не будет city LIKE ‘%shymkent%’

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

Индексируйте поля по которым объединяются таблицы

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

Найдите альтернативу вместо ORDER BY RAND()

Использование рандомной сортировки действительно является весьма удобным, и об этом такого же мнения многие начинающие программисты. Однако тут есть подводные камни, и очень весомые, используя подобный метод выборки в своих запросах, вы оставляете узкое место в производительности. Здесь же рекомендуется прибегнуть к дополнительному коду вместо использования ORDER BY RAND(), в качестве альтернативы, чтобы избавиться от слабого места в производительности, которое напомнит о себе при увеличении объема данных.

Используйте выборку конкретных полей, вместо SELECT *

Не ленитесь указывать конкретные нужные поля в запросе при выборке, вместо использования «*» — выборка всех полей, дело в том, что чем больше данных считывается из таблицы, тем медленнее становиться ваш запрос.

Добавляйте поле ID для всех таблиц

Каждая таблица в хорошем её исполнении должна иметь поле id типа INT, которое является первичным ключом (PRIMARY_KEY), и AUTO_INCREMENT. Кроме того, для поля нужно указать параметр UNSIGNED, который означает то, что значение всегда будет положительным.
В MySQL есть внутренние операции, которые могут использовать первичный ключ, это играет роль для сложных конфигураций баз данных, таких как кластеры, распараллеливание, и т.д.
Кроме того, если есть несколько таблиц, и необходимо выполнить объединенный запрос, то тут ID таблиц окажется весьма кстати.

ENUM как альтернатива VARCHAR

Давайте представим, вы хотите добавить поле в таблице, которое должно содержать определенный набор значений. Традиционно многие программисты выставляют тип VARCHAR для полей. Однако есть и другой тип поля, который гораздо быстрей и компактнее. Значения в данном типе хранятся так же, как и TINYINT, но отображаются как в строковом типе.

Используйте значение NOT NULL вместо NULL

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

Пользуйтесь Prepared Statements

$res = "UPDATE hosts SET ip = INET_ATON("{$_SERVER["REMOTE_ADDR"]}") WHERE id = $host_id";

Используйте статичные таблицы

Статичная таблица это обычная таблица в базе, за исключеним того, что каждое поле в таблице имеет фиксированный размер. Если в таблице есть колонки, не фиксированной длины, к примеру, это могут быть: VARCHAR, TEXT, BLOB, она перестает быть статичной, и будет обрабатываться MySQL немного иначе. Статичные таблицы, или их можно ещё назвать таблицами фиксированного размера работают быстрее не статичных. Записи из таких таблицах будут просматриваться быстрее, при необходимости выбора нужной строки MySQL быстро вычислит её позицию. Если поле имеет не фиксированный размер, то в этом случае поиск производиться по индексу. Есть и другие плюсы использования статических таблиц, дело в том, что эти таблицы проще кэшируются, а так же восстанавливаются после падения базы данных.

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

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

Разделяйте объемные запросы INSERT и DELETE

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

While (1){ mysql_query("DELETE FROM logs WHERE log_date <= "2015-07-20" LIMIT 1000"); if (mysql_affected_rows() == 0){ // записи удалены успешно break; } usleep(50000); // делаем небольшую паузу }

Стремитесь использовать поля небольшого размера

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

Выбирайте тип таблиц под свои задачи

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

  • Разместил Николай Коротков
  • Дата: 8 декабря 2012 в 14:04

Для чего все это нужно? На что влияет? Как воплотить в реальность? На все эти вопросы я постараюсь дать четкий ответ в этом посте!

А теперь небольшая предыстория. В общем, недавно получил письмо на свой e-mail адрес, следующего содержания:

В течение последних 3 дней средний уровень нагрузки, создаваемый Вашим аккаунтом ******* , составил 119% от допустимого уровня Вашего тарифного плана. Мы рекомендуем Вам перейти на тарифы VPS. Обращаем Ваше внимание, что в случае регулярного превышения лимитов, мы оставляем за собой право заблокировать Ваш аккаунт согласно пункту Договора...

Оба на, приплыли — подумал я в тот момент! Согласитесь, не очень приятно получать такие письма. А так как с подобного рода проблемой я столкнулся впервые, представляете, в каком я был недоумении? Моему возмущению не было предела! Какой нафиг VPS? Я можно сказать только обжился на одном тарифе, а мне тут предлагают перейти на виртуальный хостинг, который в три раза дороже. Ну нет уж ребята, — думаю я, — еще рановато.

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

В ответ получаю следующее:

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

Ну спасибо за разъяснения, — думаю про себя. Пойду изучать проблему. Набрав в интернете запрос «как снизить нагрузку на хостинг» понял, что я не один такой, а на самом деле проблема довольно актуальная. И рано или поздно коснется многих. Ознакомившись более детально с проблемой, понял, что у меня есть два выхода из данной ситуации:

  1. Обратиться за помощью к профессионалам (фрилансерам), заплатив им определенную сумму денег, что всегда успеется.
  2. Постараться устранить проблему самому.

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

Разница, на лицо! Сейчас я вам покажу и расскажу, что я для этого сделал:

— оптимизировал базу данных mysql, что существенно отразилось на снижении нагрузки на хостинг и ускорении wordpress;
— избавился от порядка 8 ненужных плагинов.
— ускорил wordpress, отредактировав несколько файлов темы своего блога.

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

В общем, материал, который вы почерпнете из этих трех постов, будет ну просто обалденным. Не пропустите, !

Оптимизация базы данных

Прежде чем вы начнете производить различные действия с базой данных, обязательно делайте резервную копию . Чтобы в случае возникновения проблем можно было все быстренько восстановить. База данных содержит всю историю вашего ресурса, в ней хранятся все записи, присутствующие на вашем блоге! А вообще, советую вам взять за правило сохранять базу данных каждый день! Это у вас займет буквально 1 минуту, но зато вы будете всегда спать спокойно. Сами понимаете может случится всякое.

1. Делаем резервную копию базы данных

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

В итоге вы должны оказаться вот на такой странице, phpMyAdmin:

Заходите в базу данных, кликнув по ее названию. Перед вами откроется таблица базы данных (кликните для увеличения):

Нажимаете «Экспорт» и «ОК». Сохраняете на своем ПК. Все, база данных сохранена, теперь можем приступать к ее оптимизации. Обратите внимание, если на вашем хостинге присутствует поле «Сохранить как файл» не забудьте напротив него поставить галочку! А также запомните, сколько весит в данный момент ваша база данных, а потом посмотрите сколько она будет весить после оптимизации.

У меня она весила до оптимизации 26 Mb — это УЖАС, а что сейчас? А сейчас она весит всего 2 Mb! Представляете, сколько всякого ненужного хлама она содержала в себе? Представляете, какую нагрузку она создавала на сервере? После оптимизации базы данных, мой блог стал летать, как реактивный самолет! В общем, после того как вы проделаете все ниже описанные действия, вы почувствуете существенную разницу!

2. Отключаем ревизии постов и устанавливаем минимальный срок хранения удаленных файлов в корзине

Что такое ревизия постов? Когда вы пишите пост в блог, wordpress автоматически, через определенный промежуток времени, сохраняет резервную копию каждого поста в базе данных, в общем, делает авто сохранение. А теперь представьте когда вы напишите 50 постов на блоге? Сколько копий постов у вас будет сохранено? Это ЖЕСТЬ! Пока вы пишите пост, у вас уже как минимум проходит 10 авто сохранений!

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

Вот основная часть последствий, но далеко не всех: снижение , частые отказы, ухудшение , понижение позиций в выдаче поисковиков... А дальше, автор в подает в отчаяние от не оправданных ожиданий. Желание вести блог со временем пропадает и все! КРАХ!

Все это я к чему говорю? За базой данных постоянно нужно следить и содержать ее в надлежащем состоянии. Поймите, база данных — это как сердце блога. При постоянной нагрузке на сердце не нужным хламом, со временем оно не выдержит и ОСТАНОВИТСЯ! Я думаю, вы меня поняли? Поэтому хватит ужастиков и переходим к оптимизации базы данных.

Итак, открываем файл wp-config.php, он находится в корне вашего блога, т.е. ваш хостинг/httpdocs или public_html (в зависимости от хостинга)/wp-config.php. И вставляем в него две строчки:

1 2 define ("WP_POST_REVISIONS" , false) ; define ("EMPTY_TRASH_DAYS" , 1 ) ;

Строка №1 отключает ревизию постов, строка №2 означает, сколько дней будут храниться удаленные файлы в вашей корзине. Как видите, я поставил «1», можно конечно поставить и «0», но если вдруг по неосторожности у вас дрогнет рука и вы нажмете на ссылку «удалить», все — КАПЕЦ!

А после просиживания за компом 5-8 часов, поверьте мне, это возможно! Так что я предпочитаю оставить циферку «1». Конечно, после удаления файла лучше сразу же почистить корзину вручную, но если даже вы забудете это сделать, спустя сутки файл из корзины автоматически удалится! Вот как это выглядит у меня:

3. Удаляем ревизии постов

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

Переходим снова в базу данных MySQL, как описано в первом пункте. Заходим во вкладку SQL, вставляем в поле скопированную строчку и нажимаем «ОК»:

База данных спросит:

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

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

4. Оптимизируем записи в wp-post

Папка wp-post содержит все записи блога. Точно так же как и в предыдущем пункте, копируем строку:

OPTIMIZE TABLE wp_posts;

И вставляем в поле SQL запроса. Нажимаем «ОК», смотрим:

Все, запрос выполнен!

5. Чистим wp-postmeta

Что именно будем чистить? Папка wp-postmeta содержит в себе:

— время последнего редактирования какого-либо из постов. Значения никакого не имеет, а нагрузку на сервер, какую никакую, а дает;
— содержание предыдущего (человека понятного урла). Если вы когда-нибудь меняли постоянную ссылку в любом посте. То при смене ее, она не удаляется, а оседает в папке wp-postmeta и нагружает вашу базу.

Делаем все тоже самое, копируем вот этот код:

Вставляем его в поле запроса SQL, и жмем «ОК». Смотрим на результат:

6. Удаляем спам-комментарии

Делается аналогично, копируем код:

Вставляем в поле SQL запроса, жмем «ОК», смотрим результат:

Как вы видите «0». После выполнения этого запроса, вы забудете про спам комментарии!

7. Удаляем пингбеки

Пингбеки — это уведомления о том, что на ваш пост или страницу кто-то ссылается. Нам это не нужно, лишняя нагрузка! Удаляем!

8. Отключаем пингбеки

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

UPDATE wp_posts p SET p. ping_status = "closed"

Ну как вам такая чистка? Понравилась? А теперь посмотрите, сколько стала весить ваша база данных после ее оптимизации? Заметно уменьшился размер? А я вам говорил! Посмотрите, как стал грузиться ваш блог! Он должен летать! Но и это еще не все на сегодня. Сейчас мы рассмотрим еще один последний пункт, который также существенно улучшит оптимизацию.

9. Устанавливаем плагин Optimize DB

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

Все, ваша база данных оптимизирована дополнительно при помощи плагина:

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

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

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

И на последок порция приколов:

Ну как вам статья? Я уверен, что вы останетесь довольны после ее прочтения и проделанных рекомендаций со своим ресурсом! Жду ваших комментариев!

Понравилась статья? Поделись с друзьями!

Каждому комментатору книга в подарок!

Книга включает в себя подробное описание самых эффективных методов продвижения вашего ресурса!


    60 комментариев

  1. Александр 8 декабря 2012 15:18

    А я знаю почему у Тебя нагрузка так выросла. Просто я тут у Тебя прижился, и постоянно что то изучаю. А что делать если инфа здесь классная. А если серьезно, то все вышеперечисленные советы рекомендую сделать всем блоговодам в первую очередь. Я это давно сделал, поэтому сплю спокойно. И еще, плагин Optimize DB, это вообще обязательный атрибут любого Блога. Спасибо Коля, как всегда, все полезно и актуально. А вот следующий пост вообще жду с нетерпением. Так что давай, пиши

  2. 9 декабря 2012 16:19

    Я в базе данных ковыряться побаиваюсь, но после установки и чистки плагином WP-Cleanup она у меня уменьшилась с почти 50 до 7Mb. Блог действительно стал грузиться намного быстрее.

  3. 9 декабря 2012 20:39

    Строго говоря, спрашивает при операциях с базой данных не сама БД (СУБД вообще все действия одинаковы, ничего не спрашивает), а клиент, phpMySql.

    Касательно пингбэков, «Из прошлого пункта мы выяснили, что пингбеки не несут никакой пользы для нашего ресурса, а только его засоряют.» — строго говоря, ничего не выяснили.

    Вы просто сказали, не аргументируя, что они не нужны, вот и всё. На самом деле, польза от них вполне может быть, просто употреблять этот инструмент нужно по назначению. Например, ключевое слово «семантическая сеть» вам говорит что-нибудь?

  4. 10 декабря 2012 08:36
  5. Юрий 16 декабря 2012 23:49

    Привет, дружище!

    Твой пост и в самом деле классный. В Интернете столько много бредни написано, что информацию приходится искать по крупицам. А здесь я зашел, и на тебе, все доходчиво и понятно. У меня как раз началась проблема с нагрузкой на сервер. Еще советую установить плагин WP Super Cache. Только его нужно грамотно настроить. Классный плагин! Может у тебя в остальных постах о нем что-то и сказано, но я еще не читал. Спешу перейти ко второй части оптимизации. Удачи тебе и твоему блогу

  6. 25 декабря 2012 11:40
  7. 28 января 2013 11:24

    Добрый день! Очень интересно, а как быть мне с блогом на Blogger? Все плагины для WP не годятся для Блогспот, нужно искать методы оптимизации самостоятельно в инете.

    С уважением, Вадим.

  8. Антон 2 апреля 2013 20:34

    Спасибо, пост действительно добротный. У меня, кстати, после проделывания пункта №3 — «Удалено 4145 строк. (Запрос занял 7.0269 сек.)»

  9. 14 июля 2013 19:04

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

  10. 14 июля 2013 19:06

    Вдогонку: а еще очень похоже на ваш текст вот тут dayafternight.ru/wordpress/baza-dannih-mysql-optimizacia

  11. 12 сентября 2013 12:57

    Спасибо Николай, нужная вещь.

    Все доступно и понятно написано.

    А статья про коды уже вышла?

  12. 12 сентября 2013 13:05

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

    Подскажите откуда она могла появиться?

    в последнее время только код яндекс-метрики вставляла.

    Наталья Гегер

    Не обращайте на это внимание... На большинстве современных серверов она есть! Связано это с выходом MySQL версии 5.0 и выше...

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

  13. 27 октября 2013 01:06

    ох, почистил базу по вашему методу + от себя ручками, результат на лицо. Раньше база весила 20мб, сейчас 5мб

  14. 29 октября 2013 23:34

    Спасибо огромное за статью. Сегодня тоже получил втык от хостера. В результате действий, база из 25Мб стала 5,2. Есть 2 вопроса, эти все манипуляции надо делать периодически? И второй вопрос, установил плагин, нажимаю оптимизировать, в результате напротив каждой строки пишется,

    note: Table does not support optimize, doing recreate + analyze instead

    Не похоже, что всё хорошо?!

    Пожалуйста! Да, я делаю все эти манипуляции, примерно один раз в месяц. А вот насчет плагина пока не могу ничего сказать, видимо вы что-то сделали не правильно. Попробуйте поискать информацию в интернет по этому поводу. Но есть и приятные события. Вы оставили на моем блоге 2100-й комментарий и за это вам полагается приз в размере 100 рублей:

    Присылайте номер своего wmr-кошелька и я перечислю вам деньги.

  15. 30 октября 2013 13:27

    Спасибо, приз получен. Как я оказался на Вашем сайте?! Вчера очередной раз сайт перестал работать, а на экране писалось «Ошибка соединения с базой данных». Написал хостеру, там подтвердили что большая нагрузка на MySQL и что-то с этим делайте, а пока перевели на тариф выше. Сразу же начал искать, что же делать и нашёл Вашу статью, которая уменьшила базу в 5 раз. Плагин который сначала не хотел работать, всё таки заработал, но основная проблема, убрать лишние запросы, так и не была решена. У меня уже стоит плагин WP Super Cache, но он кеширует страницы, а не запросы к БД. И вот я до четырёх часов утра искал плагин, который мне сможет помочь с запросами и нашёл. WP File Cache кеширует запросы, количество запросов и МБ памяти, уменьшается в разы. На страницах где до этого было 40 запросов и 35МБ, теперь запросов 9 и 12МБ. Единственное, скорость загрузки вроде чуток увеличилось, но незначительно, учитывая что скорость загрузки страниц у меня, в среднем 0,15-0,5 секунды. Может кому то данная информация будет интересна.

  16. 7 декабря 2013 15:41

    выше указанные действия могут повлиять на работу плагина nrelate-flyout ?

Управление индексами, то есть как они создаются и поддерживаются — может значительно повлиять на производительность sql запросов.

Очень часто можно применить следующие оптимизации:

  • удалить неиспользуемые индексы
  • определить неиспользуемые вообще и неэффективные индексы
  • улучшить индексы
  • избегать вообще sql запросов!
  • упрощать sql запросы
  • и магия варианты кеширования

Объединение DDL запросов

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

Удаление дублирующихся индексов

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

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

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

Утилита pt-duplicate-key-checker из perkona-toolkit — это простой и быстрый способ проверить свою структуру базы на наличие лишних индексов.

Удаление неиспользуемых индексов

Кроме индексов, которые не используются никогда, поскольку являются дублями, могут быть недублирующиеся индексы, которые просто никогда не используются. Такие индексы влияют также, как и дублирующиеся индексы. В стандартном mysql нет никаких способов определить какие индексы неиспользуются, однако в некоторых версиях есть подобная возможность, например при использовании Google MySQL patch.

В этом патче была введена фишка: SHOW INDEX_STATISTICS.

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

Оптимизация индексных полей.

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

Типы данных

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

BIGINT vs INT

Когда PRIMARY ключ определён как BIGINT AUTO INCREMENT — как правило нет никаких причин использовать именно его. Тип данных INT UNSIGNED AUTO_INCREMENT может хранить максимум числа до 4294967295. Если у вас реально будет больше записей чем это число, вам скорее всего понадобится другая архитектура.

От такого изменения с BIGINT на INT UNSIGNED каждая строка таблицы начинает занимать в 2 раза меньше места на диске, кроме того с 8 байт до 4 снижается размер, занимаемый PRIMARY ключом.

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

DATETIME vs TIMESTAMP

Тут все просто: timestamp — 4 байта, datetime — 8 байт.

По возможности надо использовать, потому что:

  • дополнительная проверка целостности данных
  • такое поле будет использовать всего 1 байт для хранения 255 уникальных значений
  • такие поля удобнее читать:)

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

NULL vs NOT NULL

Если вы не уверены, что колонка может содержать неопределенное значение (NULL), лучше определять ее как NOT NULL. Индекс на такой колонке будет меньше по размеру и будет легче обрабатываться.

Автоматичесие конвертации типов

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

Для целочисленных полей, убедитесь что SIGNED и UNSIGNED совпадают, для переменных типов полей, лишней работой может быть конвертация кодировки при джоине, поэтому их тоже обязательно проверять. Частая проблема это автоконвертация между кодировками latin1 и utf8.

Типы колонок

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

IP адрес

IPv4 адрес может храниться в поле INT UNSIGNED, которое займет всего 4 байта. Часто встречается ситуация, когда ip адрес хранят в поле VARCHAR(15), которое занимает 12 байт. Одно это изменение может сократить размер на 2/3. Функции INET_ATON() и INET_NTOA служат для конвертации между строкой с ip адресом и числовым значением.

Для IPv6 адресов, которые все сильнее наступают, важно хранить их 128битное цифровое значение в полях BINARY(16) и не использовать VARCHAR для человекочитаемого формата.

Хранение md5 полей как CHAR(32) является повсеместной практикой. Если вы используете поле VARCHAR(32) вы еще дополнительно добавляете лишний оверхед длины строки для каждого значения. Однако md5 строка — это шестнадцатиричное значение — и его можно хранить эффективнее используя функции UNHEX() и HEX(). В этом случае данные можно хранить в полях BINARY(16). Такое простое действие снизит размер поля с 32 байт до 16 байт. Подобный принцип можно применять к любым шестнадцатиричным значениям.

Основано на книге Рональда Брэдфорда.

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

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

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

Мы надеемся, что вы используете Linux или что-то вроде Vagrant -box (как наш Homestead Improved), а, соответственно, ваш конфигурационный файл будет находиться в /etc/mysql/my.cnf . Вполне возможно, что ваша установка на самом деле будет подгружать дополнительный файл конфигурации в этот. Так что посмотрите, если файл my.cnf содержит немного, то посмотрите в /etc/mysql/mysql.conf.d/mysqld.cnf .

Ручной тюнинг

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

Innodb_buffer_pool_size = 1G # (здесь поменяйте примерно 50%-70% от общего объема оперативы) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # можно поменять на 2 или 0 innodb_flush_method = O_DIRECT

  • innodb_buffer_pool_size . Пул буферизации (buffer pool) является эдаким "складом" для кэширования данных и индексов в памяти. Он используется, чтобы хранить часто используемые данные в памяти. И когда вы используете выделенный или виртуальный сервер, на котором зачастую именно БД является узким местом, то есть смысл отдать ей бОльшую часть оперативы. Следовательно, мы даем ей 50-70% всей RAM. В документации MySQL есть руководство по настройке этого пула .
  • innodb_log_file_size . Настройка размера лог-файла хорошо описана , но в двух словах это количество хранимых данных в логах, прежде чем его почистят. Обратите внимание, что лог в этом случае - это не записи об ошибках, а некий дельта-слепок изменений, которые ещё не были сброшены на диск в основные файлы innodb. MySQL пишет в фоновом режиме, но это все же влияет на производительность в момент записи. Большой лог-файл означает более высокую производительность из-за малого количества создаваемых новых и небольших контрольных точек, но при этом более длительное время восстановления в случае краша (больше данных должно быть переписано в БД).
  • innodb_flush_log_at_trx_commit описан и показывает, что происходит с файлом логов. Значение 1 - самое безопасное, т. к. лог сбрасывается на диск после каждой транзакции. При значениях 0 и 2 - меньше гарантируется ACID , но больше производительность. Разница не является достаточно большой, чтобы перевесить преимущества стабильности при 1.
  • innodb_flush_method . В довершение всего того, что касается сброса данных, эту настройку нужно установить в O_DIRECT - чтобы избежать двойной буферизации. Советую всегда это делать, пока система ввода-вывода остаётся очень медленной. Хотя на большинстве хостингах, типа DigitalOcean, вы будете иметь SSD-диски, поэтому система ввода-вывода будет более производительна.

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

Variable Inspector

Установка variable inspector на Ubuntu:

Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-toolkit

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

Затем запустите toolkit:

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

Вы увидите такой результат:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

Прим. переводчика:
На моей локальной машине, кроме этого, выдал ещё вот такой ворнинг:

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.

О том, что параметр innodb_flush_method нужно установить в O_DIRECT и почему говорилось выше. И если вы придерживались последовательности тюнинга как в статье, то вы не увидите это предупреждение.

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

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

Max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "ROW"

  • max_binlog_size . Определяет насколько большими будут бинарные логи. В них записываются ваши транзакции и запросы и делаются контрольные точки. Если транзакция превышает максимум, то лог может превышать свой размер при сохранении на диск; в противном случае MySQL будет поддерживать его в рамках этого лимита.
  • log_bin . Эта опция включает запись бинарных логов в целом. Без неё невозможны снапшоты или репликации. Обратите внимание, что это может очень сказаться на дисковом пространстве. server-id - это необходимая опция при включении бинарного лога, поэтому логи "знают" с какого сервера они пришли (для репликации), а binlog-format - это просто способ, которым они записываются.

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

MySQL Tuner

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

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

Pt-duplicate-key-checker h=localhost,u=homestead,p=secret # ######################################################################## # homestead.wp_posts # ######################################################################## # Key type_status_date ends with a prefix of the clustered index # Key definitions: # KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), # PRIMARY KEY (`ID`), # Column types: # `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default "post" # `post_status` varchar(20) collate utf8mb4_unicode_520_ci not null default "publish" # `post_date` datetime not null default "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

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

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

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

Pt-index-usage /var/log/mysql/mysql-slow.log

Подробную информацию об использовании этой утилиты см. .

Узкие места

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

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

Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1

Строки выше должны быть добавлены в конфигурацию mysql. БД будет отслеживать запросы, которые выполнялись больше чем 1 секунду, и те, которые не используют индексы.

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

Pt-query-digest /var/log/mysql/mysql-slow.log # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz # Current date: Thu Feb 13 22:39:29 2014 # Hostname: * # Files: mysql-slow.log # Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________ # Time range: 2014-02-13 22:23:52 to 22:23:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 3ms 267us 406us 343us 403us 39us 348us # Lock time 827us 88us 125us 103us 119us 12us 98us # Rows sent 36 1 15 4.50 14.52 4.18 3.89 # Rows examine 87 4 30 10.88 28.75 7.37 7.70 # Query size 2.15k 153 296 245.11 284.79 48.90 258.32 # ==== ================== ============= ===== ====== ===== =============== # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article # 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECT portfolio_item # 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item # 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category # 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category # 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article # ==== ================== ============= ===== ====== ===== =============== # Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______ # Scores: V/M = 0.00 # Time range: all events occurred at 2014-02-13 22:23:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 37 3 # Exec time 40 1ms 352us 406us 375us 403us 22us 366us # Lock time 42 351us 103us 125us 117us 119us 9us 119us # Rows sent 25 9 1 4 3 3.89 1.37 3.89 # Rows examine 24 21 5 8 7 7.70 1.29 7.70 # Query size 47 1.02k 261 262 261.25 258.32 0 258.32 # String: # Hosts localhost # Users * # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE "blog_article"\G # SHOW CREATE TABLE `blog_article`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

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

Mysqldumpslow /var/log/mysql/mysql-slow.log

С дополнительными параметрами можно отфильтровать данные, чтобы экспортировать только нужное. Например, топ-10 запросов, отсортированных по среднему времени выполнения:

Mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log

Заключение

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

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

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

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

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

// запрос не будет кэширован $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()" ); // а так будет! $today = date("Y-m-d" ); $r = mysql_query("SELECT username FROM user WHERE signup_date >= "$today"" );

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

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

// создаем a prepared statement if ($stmt = $mysqli ->prepare("SELECT username FROM user WHERE state=?" )) { // привязываем значения $stmt ->bind_param("s" , $state ); // выполняем $stmt ->execute(); // привязываем результат $stmt ->bind_result($username ); // получаем данные $stmt ->fetch(); printf("%s is from %s\n" , $username , $state ); $stmt ->close(); }

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

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

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

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

14. Храните IP в UNSIGNED INT

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

$r = "UPDATE users SET ip = INET_ATON("{$_SERVER["REMOTE_ADDR"]}") WHERE user_id = $user_id" ;

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

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

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

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

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

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

while (1 ) { mysql_query("DELETE FROM logs WHERE log_date <= "2009-10-01" LIMIT 10000" ); if (mysql_affected_rows() == 0 ) { // удалили break ; } // небольшая пауза usleep(50000 ); }

18. Маленькие столбцы быстрее

Для базы данных работа с жестким диском, возможно, является самым слабым местом. Маленькие и компактные записи обычно лучше с точки зрения производительности, т.к. уменьшают работу с диском.
В документации к MySQL есть список требований к хранилищам данных для всех типов данных.
Если ваша таблица будет хранить мало строк, то не имеет смысла делать основной ключ типом INT, возможно лучше будет сделать его MEDIUMINT, SMALLINT или даже TINYINT. Если вам не нужно хранить время, используйте DATE вместо DATETIME.
Однако будьте осторожны, что бы не вышло как с Slashdot .

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

20. Используте ORM

21. Будьте осторожны с постоянными соединениями

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



Есть вопросы?

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Отправить