Вообще, высоконагруженные проекты могут быть разными. Например, на ваш новостной сайт приходит миллион-другой посетителей в сутки, но это такие хорошие, спокойные посетители, почитали себе статью, в крайнем случае, оставили комментарий, и ушли.
Другое дело, когда вы разрабатываете соцсеть, и по цифрам видите те же самые миллион-другой человек в сутки, но тут уже совсем другая история – они без конца чего-то постят, пишут друг другу, загружают фоточки и т.д.
Если вы (как я однажды) мирно писали себе скромные сайтики, интернет-магазины или туристические порталы с посещаемостью до 5 тысяч человек в сутки и вдруг попали в разработку соцсети или подобного проекта, то это может для вас выглядеть приблизительно так:
В этом случае вам могут помочь некоторые советы и практические приемы.
Что может сделать обычный программист, чтобы ускорить работу приложения?
Прежде всего, попросить денег на новое оборудование, память и т.д.))
А также позаботиться о грамотном проектировании архитектуры базы данных, оптимизации запросов и активном кешировании.
Проектирование архитектуры базы данных
Если вы только начинаете новый проект, то стоит уделить достаточно времени продумыванию архитектуры базы.
Ошибки в проектировании начинаются с неправильной расстановки индексов, либо совершенным их отсутствием, (неэкономным) выбором типов данных под определенное поле и заканчиваются абсолютно нелогичной архитектурой.
Как мы можем улучшить архитектуру базы?
1. Разделение баз
Прежде всего, нужно разделить и, при возможности, разнести по разным серверам базы разного назначения. Например, выделить отдельные базы: внешнего проекта, внутреннего проекта, если есть, служебных очередей, например, под крон-задачи, под логи и т. д.
Зачем это нужно?
Представьте, что происходит, когда у вас все лежит в одном месте. Допустим, вы логгируете все основные действия пользователей (или ошибки, или постоянно записываете новые задачи в очередь для исполнения по крону).
Это постоянные, частые запросы, причем, на апдейты, которые грузят базу и сказываются на производительности всего проекта. Как правило, эти данные базы независимы друг от друга и их легко разнести по разным базам и серверам.
2. Тщательно продумайте структуру таблиц.
Если вы предполагаете, что ваша таблица будет содержать миллионы записей статей, то не нужно все свойства этих статей сгружать в эту таблицу.
Разбейте ее на несколько, хотя бы две, и в первой заведите только заголовок и краткое описание, т.е. то, что будет показываться в каталоге или на главной странице, причем, по возможности, сделайте поля фиксированной длины, а вот полноценные тексты вынесите во вторую таблицу.
Таким образом, вы будете обращаться к тяжелой таблице только в момент непосредственного обращения к тексту статьи, что, как правило, случается гораздо реже, чем вывод их названий.
Тоже самое относится к пользователям. Не нужно хранить все данные профиля в одной таблице, если они показываются только один раз: когда кто-то зашел на профиль пользователя. Вынесите главное и часто используемое отдельно.
Этот принцип называется Вертикальное разделение.
Но надо быть уверенными в том, что не потребуется постоянного связывания двух таблиц, которые вы только что разделили, так как это может привести к ухудшению производительности.
3. Правильно выбирайте тип данных.
Не нужно ставить поле типа INT, если заведомо известно, что вам требуется тип TINYINT.
Вопрос: сколько памяти выделит mysql под тип INT(1) и под INT(20)?
Надеюсь увидеть ответ в комментариях)
Внимательно изучите нюансы работы выбранной СУБД с разными типами данных.
Например, в столбцах DATETIME и TIMESTAMP можно хранить один и тот же тип данных: дату и время с точностью до секунды. Однако тип TIMESTAMP требует вдвое меньше места, позволяет работать с часовыми поясами и обладает специальными средствами автоматического обновления. При этом диапазон допустимых значений для него намного уже, а специальные средства могут стать недостатком.
Или, еще пример. Часто разработчики хранят в базе IP-адрес используя тип данных VARCHAR(15). Это очень неэкономно и достаточно медленно работает при поиске.
Оптимально хранить ip-адреса не как строки (VARCHAR), а как числа.
Для этого существует две функции mysql — INET_ATON и INET_NTOA.
Первая преобразует 4-байтную последовательность ip-адреса в число, вторая преобразует обратно. Таким образом:
- Столбец, в котором будут хранится ip-адреса объявляется как `ip` INT UNSIGNED NOT NULL
- При вставке:
INSERT INTO `ips` SET ip = INET_ATON('213.169.23.35') - При выборке:
SELECT INET_NTOA(ip) FROM `ips` WHERE `ips`.ip > INET_ATON('255.255.0.0')
4. Числовые поля
Иногда встречается практика заводить текстовые поля под статусы или типы, например, “on”, “deleted” и тому подобное.
Замените их на числовые, это заметно ускорит их обработку.
Про индексы, думаю, и так все хорошо знают, поэтому не будем на них задерживаться.
Оптимизация запросов
Столкнувшись с высоконагруженным проектом, приходится забыть свои блестящие навыки нормализации базы данных и заняться денормализацией...
1. Никаких сверхсложных запросов со множеством джойнов, группировками и вложенными выборками!
Это на собеседованиях любят задавать вопросы, что такое полиморфизм и предлагают сделать сложную выборку, но в большом проекте все по-другому - сложные запросы просто его положат.
Для высоконагруженных проектов очень часто (но не всегда) работает правило:
лучше два простых запроса, чем один сложный
Проект в этом случае будет работать быстрее.
Когда-то давно я искренне гордилась своим запросом (скопировала прямо из кода):
SELECT DECODE(FF.NAME_RUS,NULL,FF.NAME_ENG,FF.NAME_RUS) AIRCRAFTNAME,
DECODE(FF.DESCRIPTION,NULL,0,1) AIRCRAFTLINK,
DECODE(JJ.NAME_RUS,NULL,JJ.NAME_ENG,JJ.NAME_RUS) DEPARTUREAIRPORTNAME,
DECODE(LL.NAME_RUS,NULL,LL.NAME_ENG,LL.NAME_RUS) ARRIVALAIRPORTNAME,
BB.OPTIONID,BB.ITEMID,CC.*,AA.*,DD.PRICE AS PRICE,DD.ADULTS,DD.CHILDREN,DD.INFANTS,BB.OPTIONID AS OPTIONCODE,
TO_CHAR(CC.DEPARTUREDATE,'DD.MM.YYYY HH24:MI') DEPARTUREDATE,
TO_CHAR(CC.ARRIVALDATE,'DD.MM.YYYY HH24:MI') ARRIVALDATE,
COUNT(CC.SEGMENTID) OVER(PARTITION BY AA.ITEMID) SEGMENTSCOUNT,
COUNT(DISTINCT CC.AIRLINECODE) OVER(PARTITION BY AA.ITEMID) AIRLINESCOUNT,
(SELECT DECODE(NAME_RUS,NULL,NAME_ENG,NAME_RUS) FROM HT_AIRLINES WHERE CC.AIRLINECODE=IATACODE AND SERVICEREGION<>'Cargo' AND ROWNUM=1) AIRLINENAME
FROM B2C.B2C_AVIA_ITEMS AA
LEFT JOIN B2C.B2C_AVIA_OPTIONS BB ON AA.ITEMID=BB.ITEMID
LEFT JOIN B2C.B2C_AVIA_FLIGHT_SEGMENTS CC ON BB.OPTIONID=CC.OPTIONID
LEFT JOIN B2C.B2C_ITEMS DD ON AA.ITEMID=DD.ITEMID
LEFT JOIN AV_AIRCRAFTS FF ON CC.AIRCRAFTCODE=FF.IATA
LEFT JOIN HT_GEO_CODES HH ON HH.CODE_NAME=CC.DEPARTUREAIRPORTCODE AND HH.CODEID=11
LEFT JOIN HT_GEO JJ ON HH.GEOID=JJ.GEOID
LEFT JOIN HT_GEO_CODES KK ON KK.CODE_NAME=CC.ARRIVALAIRPORTCODE AND KK.CODEID=11
LEFT JOIN HT_GEO LL ON KK.GEOID=LL.GEOID
WHERE " . $str . $forhash . $strtime."
ORDER BY AA.ITEMID,BB.OPTIONID,CC.SEGMENTID
Вы сейчас в таком же ужасе, как и я, когда смотрите на него?Да, по тем реалиям, когда сайт посещали 5000 человек в день, запрос выполнял свою функцию.
В приложении с большой посещаемостью даже запрос вида
select * from A where id in (select category_id from B)может сильно усложнить жизнь.
Вложенный запрос будет вызываться для каждой строки внешнего, а не вычисляться один раз перед выполнением внешнего.
В некоторых запросах поможет замена на join, но не всегда, нужно замерять время работы запроса. Когда речь идет о миллионах записей, то гораздо эффективнее будет разбиение запроса. Конечно, хочется все сделать быстренько и за один заход, мы так привыкли. Но...
Сначала выполните select cetegory_id from B, а потом в цикле пройдитесь по результату и выберите нужные значения из А.
2. В запросах вместо * выбирайте только те столбцы, которые вам реально нужны.
Этим вы снизите нагрузку.
3. Постарайтесь избавиться от агрегирующих функций вроде group by.
Как же можно избавиться от группировок, если, скажем, вам нужно постоянно выводить количество комментариев для каждого пользователя?
Очень просто: в таблице пользователей заведите поля вроде comments_count. Да, вам придется дергать таблицу пользователей при каждом добавлении комментария, но вы избавитесь от тяжелых группировок.
Впрочем, дергать таблицу пользователей каждый раз тоже необязательно, и тут нам на помощь приходит, например, мемкеш, но это уже следующая история.
4. Также желательно не использовать count(*).
В mysql лучше использовать SQL_CALC_FOUND_ROWS. При использовании этой опции можно получить количество всех найденных по запросу записей - это незаменимо при постраничном выводе поисковых результатов, когда используются «тяжелые» запросы.
5. Давайте поговорим про перебор большого количества данных.
Очень часто приходится перебирать большое количество данных. Например, перебрать все фотки или альбомы. Или всех пользователей.
Есть три способа перебора данных, чтобы база сразу не умерла от перегрузки:
-
используя limit;
-
делая перебор по ID;
-
используя курсор.
LIMIT
Старый, добрый limit. Типичное использование limit
$i = 0;
$pack = 1000;
do
{
$albums = $this->getAlbums()
->limit(($pack * $i), $pack)
->sort('id')
);
foreach ($albums as $album)
{
// do smth with album
}
$i++;
}
while ($albums->count());Но лучше отказаться он данного типа выборки.
Почему, спросите вы. Потому что она очень медленная. Чем больше объектов надо перебрать, тем медленнее работает данный перебор.
Between IDs
Этот способ мы использовали, когда уперлись в то, что обновление всех фоток идет неделями.
Использование id >= AND id <
$i = 0;
$pack = 1000;
do
{
$photos = $this->getPhotos()
->param('id>=', $pack * $i)
->param('id<', ($pack * $i) + $pack)
->sort('id')
);
foreach ($photos pages as $ photo)
{
// do smth with photos
}
$i++;
}
while ($photos->count());В данном примере перебираются все фото на сайте. В чем минус такого подхода? В том, что есть возможность не пройти все объекты, которые мы ожидаем.
Например, что если в таблицу photos, было добавлено подряд 5000 фоток? Да ещё и в самом начале? Поэтому можно попробовать использовать курсоры.
Курсоры
Курсоры нужны для того, чтобы избежать переполнение памяти в тех случаях, когда выбирается сразу очень много записей. В этом случае ставится курсор, выбираются данные, если нужно, далее курсор передвигается.
Подробнее о курсорах можно почитать тут: https://dev.mysql.com/doc/refman/8.0/en/cursors.html
или тут: https://postgrespro.ru/docs/postgresql/15/plpgsql-cursors?lang=ru-en
6. Используйте LIMIT 1, если нужно получить уникальную строку.
Иногда, во время использования запроса, вы уже знаете, что ищете только одну строку. Вы можете получить уникальную запись или просто проверить существование любого количества записей, которые удовлетворяют предложению WHERE. В таком случае добавление LIMIT 1 к вашему запросу может улучшить производительность. При таком условии механизм базы данных останавливает сканирование записей как только найдет одну и не будет проходит по всей таблице или индексу.
// Есть ли какой нибудь пользователь из Алабамы?
// Так не нужно делать:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0)
{
// ...
}
// Вот так будет значительно лучше:
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0)
{
// ...
}Точно также разделяйте большие запросы DELETE или INSERT.
Если вам нужно выполнить большой запрос DELETE или INSERT на работающем сайте, то нужно быть осторожным. Большой запрос может заблокировать таблицы и привести к остановке приложения.
Если у вас есть скрипт, который удаляет большое количество записей, тоже используйте предложение LIMIT для разбиения его на маленькие партии, чтобы избежать описанной ситуации.
while (1)
{
mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
if (mysql_affected_rows() == 0)
{
// выполняем удаление
}
// можно сделать небольшую паузу
usleep(50000);
}(Для целостности данных не забываем про транзакции).
Репликация и шардирование
Большинство баз данных имеют встроенные механизмы репликаций. Основная проблема, решаемая при репликации – синхронизация данных между серверами. Возможны различные топологии: один мастер (master) – много слейвов (slave), много мастеров и т.д. Реплицировать можно весь сервер целиком, одну базу, одну таблицу.
Что достигается с помощью репликации?
- Распределение данных: данные можно копировать по разным дата-центрам.
- Распределение нагрузки: запросы можно разносить между разными серверами.Как правило, апдейты базы отдаются на master, а селекты – на slave
- Бекапы: репликация упрощает архивацию.
- Устойчивость: наличие слейвов позволяет уменьшить риск отказов системы.
В средне нагруженном проекте (вроде новостного из примера выше) разумно использовать master/slave и все запросы от пользователей на чтение (их будет большинство) отправлять на slave, а все запросы от редакторов/авторов на запись (новые статьи, модерация и т.д. - таких запросов будет намного меньше, но они и потяжелее) отправлять на master.
В более сложных системах может понадобиться писать данные в базы на разных серверах и потом реплицировать их. Какие проблемы могут возникнуть в этом случае? Допустим, мы проверяем, был ли создан объект, и если да – то возвращаем его, если нет – то вставляем.
$object = $this->findByUser($user);
if (!$object->count())
{
$this->create($user);
}
else
{
return $object;
}Но если запись только что была вставлена и не успела реплицироваться с мастера на слейв, то слейв еще не знает, о том, что она есть. И что происходит?
Слейв возвращает, что объекта нет, происходит попытка вставки, выкидывается некий эксепшен от базы (например, что такая запись уже существует). Решить это можно таким способом:
$object = $this->findByUser($user);
if (!$object->count())
{
try
{
$object = $this->create($user);
}
catch (Some_Database_UniqueException $e)
{
$object = $this->findByUser($user);
if (!$object ->count())
throw new Real_Exception();
}
}В этом случае мы перехватываем исключение и делаем вторую попытку обратиться к слейву. Если опять не получается – только тогда кидаем эксепшен или как-то обрабатываем.
Наконец, если ваша база так огромна, что все предложенные способы уже не помогают, то пришло время шардирования. Что это?
Шардирование (горизонтальное партиционирование) — это принцип проектирования базы данных, при котором логически независимые строки таблицы базы данных хранятся раздельно, заранее сгруппированные в секции, которые, в свою очередь, размещаются на разных, физически и логически независимых серверах базы данных, при этом один физический узел кластера может содержать несколько серверов баз данных.
Так удается распределить нагрузку, оказываемую трафиком. Об этом можно написать отдельно, в рамках этой мы подробнее останавливаться не будем.
Я рада, что вы дочитали статью, давайте попрактикуемся?
Моя реальная задача была.
Условия: таблица с миллионами текстов. Тексты большие, проект новостной. Присылают список ссылок, запрещенных Роспотребнадзором. Список большой, порядка 200 тысяч ссылок. Нужно скриптом пройтись по всем миллионам текстов, поискать эти 200 тысяч ссылок в каждом (регуляркой, что тоже небыстро) и удалить, если найдется что-то. Задача несрочная, но…
Но скрипт все равно работал слишком долго — несколько дней или даже неделю. Да, на порции разбила, все вроде сделала, но мы не могли занимать боевую базу такой долгой работой.
Какие есть варианты решения? Пишите в комментариях.
Еще чаще бывают задачи вроде такой:
Допустим, вы разработчик социальной сети, пользователи которой обожают размещать фотографии, и все свободное время просматривают фотографии друзей.
Приходит ваш заказчик и просит сделать подпись к каждой фотографии, вроде: «Просмотров: за последний день – 44, за последнюю неделю – 190, за последний месяц – 500, за все время – 1080»
Как вы это реализуете?
Допустим, если у вас всего 20000 пользователей, то вы будете подсчитывать количество просмотров каждой фотографии, записывая каждое новое значение в базу данных, а при выводе сделаете группировку.
Но что будет, когда пользователей станет 20 000? А 2 000000? А 20 000 000?
Если задача и вообще эта тема интересны, пишите, я напишу еще статью с ответом на этот вопрос.
Голева Екатерина
@girlsinphp
https://vk.com/girlsinphp
Комментарии (3)
Андрей