В первой части мы обсудили философские основы рейтинговой системы и основные принципы её работы. Теперь перейдём к техническим деталям реализации на MySQL.
Почему SQL?
Выбор реализации бэкенда на SQL был сделан по нескольким причинам:
Атомарность операций — SQL-запросы на обновление огромного числа записей атомарны. При пересчёте рейтингов критически важно, чтобы все изменения применялись одновременно или не применялись вообще.
Реализация такой логики на PHP + SQL была бы крайне сложной и подверженной ошибкам.
Если представить, что во время пересчёта рейтингов половина пересчиталась, а половина нет, то это катастрофа.Вся логика в одном месте
Типичный код PHP + SQL — это масса циклов, отдельных запросов, разделение логики — часть в PHP, часть в SQL.
Транзакции частично улучшают дело, но разделение логики по двум мирам — PHP и SQL остаётся. Поэтому всё сделано на SQL.Язык сверхвысокого уровня — SQL позволяет записывать сложные операции с данными высокоуровнево и декларативно, что делает код более читаемым и поддерживаемым.
Оптимизация на низком уровне — высококлассные программисты уже тщательно оптимизировали MySQL для быстрой работы с данными. Сама БД на уровне движка, уже использует индексы, кэширование и параллельные вычисления. Это особенно важно при работе с десятками тысяч записей.
InnoDB и транзакции — Для реализации был выбран движок InnoDB (в то время он ещё не был движком по умолчанию). Это позволило использовать транзакции для обеспечения целостности данных при пересчёте рейтингов.
Почему не PostgreSQL? Проект уже хранил свои данные в MySQL. Если бы возможностей MySQL оказалось недостаточно, то, возможно, я бы стал думать о чём-то другом. Но возможностей хватило.
Структура данных
Таблица оценок
Все оценки хранятся в таблице grade:
CREATE TABLE `grade` (
`o_id` int(10) unsigned NOT NULL DEFAULT '0',
`type` enum('user','rolik','blog','comment') NOT NULL DEFAULT 'comment',
`value` tinyint(4) NOT NULL DEFAULT '0',
`u_id` int(10) unsigned NOT NULL DEFAULT '0',
`grade_user_id` int(10) unsigned NOT NULL DEFAULT '0',
`gdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ip` int(11) NOT NULL DEFAULT '0',
UNIQUE KEY `o_id` (`type`,`o_id`,`grade_user_id`),
KEY `type` (`type`,`grade_user_id`,`gdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Поля:
o_id— идентификатор оцениваемого объекта (ролика, поста, комментария),type— тип объекта,value— значение оценки от -127 до +127 (на фронтенде это +1/-1, но в базе хранится умноженное на 127),u_id— идентификатор владельца контента (для кого эта оценка),grade_user_id— идентификатор пользователя, который поставил оценку,gdate— дата и время оценки (автоматически обновляется при изменении),ip— IP-адрес голосующего (хранится как целое число черезip2long()).
Индексы:
UNIQUE KEY (type, o_id, grade_user_id)— гарантирует, что один пользователь может оценить объект только один раз. Также используется для быстрого поиска оценок конкретного объекта.KEY (type, grade_user_id, gdate)— служит для убыстрения запросов по отслеживанию ежедневных лимитов голосований и поиску накруток (нужно быстро находить все оценки пользователя за период).
Кэшируемые поля в контентных объектах
В таблицах roliki, blogs и comments были добавлены поля с префиксом cache_:
`cache_grade` float DEFAULT NULL,
`cache_people` int(10) unsigned DEFAULT NULL,
`cache_relgrade` float DEFAULT NULL,
Префикс cache_ поддерживает для программиста, что информация в этих полях вторична по сравнению с таблицей grade. Но для убыстрения показов рейтингов и числа проголосовавших удобнее вместе с роликами/постами сразу извлекать информацию о голосованиях из той же таблицы, это позволяет не нагружать базу при каждом запросе страницы.
Во время пересчётов рейтингов (которые происходят раз в 30 минут) значения берутся из кэшированных полей, не участвующих в пересчёте. Это позволяет параллельно работать пересчёту и отображению/изменению рейтингов на фронтэнде.
Также во время голосования мы имеем моментальные изменения рейтингов при голосовании, так как используем кешированные значения. Которые после пересчёта автоматически уточняются.
Когда нам нужно сбросить кешированные значения, то записываем NULL в кешированное поле. И система понимает, что этот рейтинг ещё не посчитан и может запустить пересчёт. Рейтинг может быть равен нулю при равенстве голосов «За» и «Против», поэтому важно отличать случай нулевого рейтинга от случая, когда он ещё не посчитан.
Назначение полей:
-
cache_grade— суммарный рейтинг объекта. Вычисляется как сумма произведений силы голоса каждого оценившего на значение его оценки:SUM(u.cache_votepower * g.value) / 127
Делим на 127 для нормализации, так как в базе оценка представлена знаковым полем типа TINYINT, которое может принимать значения от -127 до +127.
Эта величина, показывающая суммарное значение всех оценок проголосовавших пользователей за конкретную единицу контента.
cache_people— количество уникальных пользователей, оценивших объект.-
cache_relgrade— относительный рейтинг, ключевое поле для ранжирования. Вычисляется по формуле:sigmoid(коэффициент, cache_people) * (cache_grade / сумма_силы_голоса_оценивших)Где:
sigmoid(K, N) = 2/(1+exp(-K*N))-1— сигмоидная функция для учёта достоверности оценки в зависимости от числа голосов. Чем меньше проголосовавших, тем менее достоверна оценка.cache_grade / сумма_силы_голоса— средняя оценка, взвешенная по силе голоса.
Это поле учитывает и объективность оценки (через усреднение оценок разных пользователей), и её достоверность (через сигмоиду). Именно
cache_relgradeиспользуется для сортировки контента на сайте.
Поля пользователей
В таблице users кроме трёх стандартных полей для рейтинга (см. выше) есть дополнительные рейтинговые поля с префиксом cache_:
`cache_rank` float DEFAULT NULL, -- ранг от -1 до 1 (главный результат расчёта)
`cache_votepower` float DEFAULT NULL, -- сила голоса (логарифм от ранга, макс. 5)
`cache_rating` float DEFAULT NULL, -- общий рейтинг (сумма оценок контента)
`cache_grade` float DEFAULT NULL, -- суммарный рейтинг контента пользователя
`cache_people` int(10) unsigned DEFAULT NULL, -- число оценивших пользователя
`cache_relgrade` float DEFAULT NULL, -- относительный рейтинг пользователя
Назначение полей:
cache_rank— главный результат расчёта, ранг пользователя от -1 (абсолютно вредный) до 1 (абсолютно полезный). Это ключевое поле, на основе которого вычисляется сила голоса.cache_votepower— сила голоса пользователя, вычисляется как логарифм от ранга. Максимальное значение ограничено 5, так как один человек не может быть «правее» пяти обычных пользователей. Исключение сделано для хозяина сайта, его сила голоса равна 18. В конце концов он платит за всё.cache_rating— суммарный рейтинг всех единиц контента пользователя, взвешенный по силе голоса оценивших. Это абсолютная величина, показывающая общий «вес» всех оценок. Временная инфляция не учитывается. Важно: это поле используется для проверки при вычислении силы голоса. Если у пользователя ранг равен 0, ноcache_ratingне равен 0, то ему всё равно даётся некая минимальная сила голоса (так как его контент когда-то был признан полезным и положительно оценён). Если же и ранг 0, и рейтинг 0 (или NULL), то сила голоса = 0 (пользователь не имеет оценок контента).cache_grade— суммарный рейтинг контента пользователя. При голосовании моментально изменяется на основании силы голоса голосующего. Но при последующем пересчёте уточняется.cache_people— количество уникальных пользователей, оценивших данного пользователя или его контент.cache_relgrade— относительный рейтинг пользователя, учитывающий достоверность оценки в зависимости от числа проголовавших через сигмоиду.
Обновление полей:
Все эти поля обновляются в процессе пересчёта рейтингов, который запускается каждые 30 минут через cron. Скрипт пересчёта — это простенький PHP-скрипт, который вызывает хранимую процедуру пересчёта. Между пересчётами значения для фронтэнда берутся из кэшированных полей для скорости.
Процесс пересчёта рейтингов
Этап 1: Удаление накруток
В начале каждого цикла вызывается хранимая процедура для удаления явных взаимных голосований:
CALL del_seo_votes(50, 0.995);
Аргументы процедуры:
50(min_vote_count) — минимальное число взаимных голосований между парой пользователей, чтобы считать это подозрительным. Если два пользователя голосуют друг за друга менее 50 раз, это не считается накруткой.0.995(percent_seo) — порог однонаправленности. Если 99.5% или более голосов идут в одну сторону (A→B, но почти нет B→A), это признак накрутки. Процедура находит такие пары и удаляет все их взаимные оценки.
Код процедуры del_seo_votes
CREATE PROCEDURE del_seo_votes (
IN min_vote_count INT,
IN percent_seo FLOAT
)
BEGIN
-- Объявляем переменные для работы с курсором
DECLARE no_more_rows BOOLEAN;
DECLARE t_u_id, t_grade_user_id int unsigned;
-- CURSOR: создаём курсор для выборки подозрительных пар пользователей
-- Курсор позволяет последовательно обрабатывать результаты запроса
-- Выбираем пары пользователей, которые голосуют друг за друга слишком часто
-- и однонаправленно (99.5% голосов в одну сторону)
DECLARE cur CURSOR FOR
SELECT u_id, grade_user_id
FROM grade
WHERE (u_id NOT IN (17, 38)) -- исключаем админов
AND (grade_user_id NOT IN (17, 38))
GROUP BY u_id, grade_user_id
HAVING COUNT(*) >= min_vote_count *
IF(sum(value) >= 0, 1, 1.5) -- для отрицательных оценок порог выше
AND (COUNT(*) * percent_seo <= ABS(sum(value)/127));
-- Обработчик: устанавливаем флаг, когда курсор достигнет конца результата
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
-- Открываем курсор для начала итерации по результатам
OPEN cur;
-- Основной цикл обработки: последовательно получаем каждую пару из курсора
the_loop: LOOP
-- FETCH: получаем следующую строку из курсора в переменные
FETCH cur INTO t_u_id, t_grade_user_id;
-- Если достигли конца результата, выходим из цикла
IF no_more_rows THEN
LEAVE the_loop;
END IF;
-- Удаляем все взаимные оценки между найденной парой пользователей
DELETE FROM grade
WHERE u_id=t_u_id
AND grade_user_id=t_grade_user_id;
END LOOP the_loop;
-- Закрываем курсор и освобождаем ресурсы
CLOSE cur;
END;
Критерии накрутки:
Минимальное число взаимных голосований.
Процент однонаправленности (если 99.5% голосов за одного и того же пользователя — это подозрительно).
Этап 2: Пересоздание функций и хранимых процедур
Поскольку я не был уверен, что мои хранимки и функции не удалит другой программист, то при каждом пересчёте скрипт на всякий случай пересоздавал их:
DROP function if exists sigm;
CREATE FUNCTION sigm (K FLOAT, N INT unsigned)
RETURNS FLOAT DETERMINISTIC
RETURN 2/(1+exp(-1*K*N))-1;
Определение сигмоиды:
Эта функция используется для расчёта достоверности оценки в зависимости от числа голосов. При малом числе голосов значение близко к 0, при большом — стремится к 1. Подробнее в 1-й части статьи.
DETERMINISTIC — ключевое слово означает, что функция всегда возвращает одинаковый результат для одинаковых входных параметров. Это позволяет MySQL кэшировать результаты функции, что значительно ускоряет вычисления при повторных вызовах с теми же параметрами.
Этап 3: Пересчёт рейтингов контента
При каждом пересчёте мы копируем значения из grade в tmp_grade с учётом поправок за время и число голосов. Важно: все расчёты выполняются во временных таблицах, чтобы не блокировать основные таблицы, которые могут использоваться пользователями сайта в это время.
Создание временной таблицы для промежуточных результатов:
CREATE TABLE tmp_grade (
id int(10) unsigned NOT NULL,
grade float,
people int unsigned,
relgrade float,
PRIMARY KEY(id)
);
Эта таблица используется только для промежуточных вычислений и не мешает работе основного сайта.
Для упрощения расчётов нам потребуется ещё таблица
grade_vector
Каждый факт голосования можно представить как направленное ребро графа. Подобное представление поможет нам найти взаимные накрутки, а также накрутки по схеме «треугольник» и многоугольники.
Все факты голосования пользователем 1 за контент пользователя 2 мы пересчитываем в 1 строку в этой таблице с учётом всех видом инфляций, коэффициентов достоверности и других метрик. В итоге у нас появляется граф связей между пользователями, где каждая связь (ребро) имеет направление.
У каждого ребра есть свой вес kweight. Если голосования пользователя 1 в адрес пользователя 2 признаются накруточными, то вес этого ребра обнуляется. Вес сделан бинарно, но в потенциале может быть переделан в тип float, для отслеживания частичных накруток.
Фактически все расчёты рейтинга пользователей происходят в этой таблице, а также обесценивание накруточных голосований в адрес контента конкретных пользователей.
CREATE TABLE grade_vector (
quser int unsigned NOT NULL, -- голосующий
qcache_rank float NOT NULL, -- кэш ранга голосующего
user int unsigned NOT NULL, -- оцениваемый
qcsum float NOT NULL, -- сумма оценок за контент
qrsum float NOT NULL, -- сумма оценок за репутацию
qcont float NOT NULL, -- число оценок контента
qrep float NOT NULL, -- число оценок репутации
high TINYINT UNSIGNED NOT NULL, -- флаг двусторонней связи (1 = есть обратная связь B→A для связи A→B)
kweight TINYINT UNSIGNED NOT NULL, -- вес вектора (0 = удалён)
qk float NOT NULL, -- коэффициент оценок
ak float NOT NULL, -- коэффициент других
rt float NOT NULL, -- суммарный рейтинг
UNIQUE KEY quf (quser, user),
INDEX hk (high, kweight),
INDEX hqu (high, quser, user),
INDEX huq (high, user),
INDEX u (user)
);
Индексы для поиска колец:
(high, kweight)— быстро находить активные двусторонние связи (high=1 означает, что есть обратная связь).(high, quser, user)— оптимизирует JOIN при поиске циклов.(high, user)— быстрый поиск всех голосований за пользователя.
Примечание о флаге high: Название не очень понятное, но флаг означает наличие двусторонней связи. Если есть связь A→B, то high=1 только если существует обратная связь B→A. Это важно для поиска накруточных колец — мы ищем только циклические схемы, где все связи двусторонние.
Заполняем таблицу `grade_vector` одним запросом, с учётом относительной ценности каждого типа контента и временной инфляции
INSERT INTO grade_vector(quser, user, qcsum, qrsum, qcont, qrep, qk, rt, qcache_rank)
SELECT quser, user, qcsum, qrsum, qcont, qrep, IF(qcsum + qrsum = 0, 0, (qcsum + qrsum)/(qcont+qrep)) AS qk,
(qcsum + qrsum)*votepower AS rt,
cache_rank
FROM
(SELECT
grade_user_id AS quser,
u_id AS user,
-- Приведённая сумма относительных оценок за контент
SUM(
value*
IF(type="rolik", 1,
IF(type="blog", '.(getconf('W_BLOG')/getconf('W_ROLIK')).',
IF(type="comment", '.(getconf('W_COMMENT')/getconf('W_ROLIK')).',0)
)
)*(1-0.50929582*ATAN(DATEDIFF(CURDATE(), g.gdate)*0.0004116))
)/127 AS qcsum,
-- Приведённая сумма относительных оценок за репутацию
SUM(
value*
IF(type="user", '.(getconf('W_USER')/getconf('W_ROLIK')).', 0)*
(1-0.50929582*ATAN(DATEDIFF(CURDATE(), g.gdate)*0.0004116))
)/127 AS qrsum,
-- Приведённое число оценок (только контент)
SUM(
IF(type="rolik", 1,
IF(type="blog", '.(getconf('W_BLOG')/getconf('W_ROLIK')).',
IF(type="comment", '.(getconf('W_COMMENT')/getconf('W_ROLIK')).',
IF(type="user", 0, 0)
)
)
)*(1-0.50929582*ATAN(DATEDIFF(CURDATE(), g.gdate)*0.0004116))
) AS qcont,
-- Приведённое число оценок (только репутация)
SUM(
IF(type="user", '.(getconf('W_USER')/getconf('W_ROLIK')).', 0)*
(1-0.50929582*ATAN(DATEDIFF(CURDATE(), g.gdate)*0.0004116))
) AS qrep,
u.cache_rank,
u.cache_votepower AS votepower
FROM grade g
INNER JOIN users u ON u.id=g.grade_user_id AND u.cache_rank > 0
GROUP BY grade_user_id, u_id) t
Этап 4: Поиск накруточных колец
Это самая сложная часть алгоритма. Система ищет циклические схемы взаимных голосований (A→B→C→A и т.д.).
Поиск простых колец (2-6 элементов)
Для колец небольшого размера используется прямой SQL-запрос с множественными JOIN:
UPDATE grade_vector AS t1
STRAIGHT_JOIN grade_vector AS t2 ON t1.user=t2.quser
AND t2.quser <> t1.quser
STRAIGHT_JOIN grade_vector AS t3 ON t2.user=t3.quser
AND t3.quser NOT IN (t1.quser, t2.quser)
-- ... и так далее для t4, t5, t6
SET t1.kweight=0
WHERE t1.kweight = 1
AND t1.quser=t3.user -- замыкание цикла
AND t1.high = 1 AND t2.high = 1 AND t3.high = 1
AND t2.qcache_rank >= t1.qcache_rank -- самый сильный в начале
AND t2.qcache_rank >= t3.qcache_rank;
Запрос находит все кольцевые тройки (A→B→C→A), и обнуляет вес первого звена (того, у кого наименьший ранг). Так как обычно накручивают рейтинг тому, у кого он недостаточен.
Поиск больших колец (7-8 элементов)
Для больших колец используется хранимая процедура grade_ring_search() с алгоритмом «растекания жидкости»:
Берётся первое звено (A→B).
От точки B «растекается жидкость» по всем связанным звеньям.
Если жидкость возвращается к A, значит, найден цикл.
Процесс повторяется для всех звеньев.
Важная оптимизация: Процедура использует временные таблицы в памяти (ENGINE=memory) для максимального ускорения вычислений. Работа с таблицами в памяти — это ключевая оптимизация рейтинговой системы для скоростной работы. Таблицы в памяти работают в десятки раз быстрее обычных таблиц на диске, что критично при обработке десятков тысяч записей. Кроме того, это позволяет не блокировать основные таблицы и не снижать быстродействие сайта во время пересчёта.
Код процедуры grade_ring_search
CREATE PROCEDURE grade_ring_search(
IN minlevel INT UNSIGNED, -- минимальный размер кольца для поиска
IN maxlevel INT UNSIGNED -- максимальный размер кольца для поиска
)
COMMENT "Поиск колец от minlevel до maxlevel в таблице grade_vector. Число найденных колец возвращается во временной таблице tmp_ring"
MODIFIES SQL DATA
BEGIN
-- Объявляем переменные для работы с курсором и алгоритмом
DECLARE i, k, aff, ia, nquser, nuser INT UNSIGNED;
DECLARE done, tmp_done INT DEFAULT FALSE;
-- CURSOR: создаём курсор для выборки всех двусторонних связей
-- Курсор позволяет последовательно обрабатывать каждую связь A→B
-- для поиска циклов, начинающихся с этой связи
DECLARE cur CURSOR FOR
SELECT quser, user
FROM grade_vector
WHERE high=1 AND kweight=1; -- только активные двусторонние связи
-- Обработчик: устанавливаем флаг, когда курсор достигнет конца результата
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- ОПТИМИЗАЦИЯ: Создаём временную таблицу в памяти для подсчёта найденных колец каждого размера
-- ENGINE=memory обеспечивает максимальную скорость работы с данными
-- Таблица в памяти работает в десятки раз быстрее таблиц на диске
DROP TEMPORARY TABLE IF EXISTS tmp_ring;
CREATE TEMPORARY TABLE tmp_ring (
rang INT UNSIGNED NOT NULL default 0 PRIMARY KEY,
count INT UNSIGNED NOT NULL default 0
) engine=memory;
-- Заполняем временную таблицу нулями для каждого размера кольца
SET i=minlevel;
WHILE i <= maxlevel DO
INSERT INTO tmp_ring(rang, count) VALUES(i, 0);
SET i = i + 1;
END WHILE;
-- ОПТИМИЗАЦИЯ: Создаём рабочую таблицу в памяти для алгоритма растекания жидкости
-- ENGINE=memory критически важен для производительности при поиске больших циклов
-- При поиске 8-угольников выполняется множество операций UPDATE, которые в памяти работают мгновенно
DROP table IF EXISTS tmp_gv;
CREATE table tmp_gv (
quser int unsigned NOT NULL, -- голосующий пользователь
user int unsigned NOT NULL, -- оцениваемый пользователь
kweight TINYINT UNSIGNED NOT NULL default 1, -- вес вектора (0 = удалён)
fluid TINYINT UNSIGNED NOT NULL default 0, -- метка растекания жидкости
UNIQUE KEY quf (quser, user, fluid),
INDEX fqh (fluid, quser)
) ENGINE=memory;
-- Копируем все активные двусторонние связи в рабочую таблицу
INSERT INTO tmp_gv(quser, user, kweight)
SELECT quser, user, kweight FROM grade_vector WHERE high=1;
-- Подготавливаем запросы для алгоритма растекания жидкости
-- Обнуление всех меток жидкости перед началом поиска цикла
PREPARE grade_set_zero FROM "UPDATE tmp_gv SET fluid=0 WHERE fluid > 0";
-- Создание начальной "кляксы" жидкости из первой точки (A→B)
PREPARE grade_first_fluid FROM "UPDATE tmp_gv g
INNER JOIN tmp_gv g1 ON g.user=g1.quser
SET g1.fluid=1
WHERE g1.fluid=0 AND g.quser= ? AND g.user= ?";
-- Растекание жидкости: от помеченных точек к следующим связанным точкам
PREPARE grade_normal_fluid FROM "UPDATE tmp_gv g
STRAIGHT_JOIN tmp_gv g1 ON g.user=g1.quser
SET g1.fluid=1
WHERE g.fluid=1 AND g1.fluid=0";
-- Обнуление веса первого звена, если жидкость вернулась к нему (найден цикл)
PREPARE grade_update_first FROM "UPDATE tmp_gv g
SET kweight=0
WHERE fluid=1 AND g.quser=? AND g.user=?";
-- Открываем курсор для начала итерации по всем связям
OPEN cur;
-- Основной цикл: обрабатываем каждую связь из курсора
read_loop: LOOP
-- FETCH: получаем следующую связь (A→B) из курсора
FETCH cur INTO nquser, nuser;
SET @quser = nquser, @user = nuser;
-- Если достигли конца результата, выходим из цикла
IF done THEN
LEAVE read_loop;
END IF;
SET tmp_done = done;
-- Обнуляем все метки жидкости перед поиском цикла для этой связи
EXECUTE grade_set_zero;
-- Создаём начальную "кляксу" жидкости из точки B (куда ведёт связь A→B)
EXECUTE grade_first_fluid USING @quser, @user;
-- Растекаем жидкость на (minlevel-1) шагов вперёд
-- Это нужно, чтобы не проверять слишком короткие пути
SET k=1, i = minlevel - 1;
WHILE k < i DO
-- Растекаем жидкость на один шаг вперёд
EXECUTE grade_normal_fluid;
-- Если жидкость перестала растекаться, значит цикла нет
-- Переходим к следующей связи
IF (SELECT ROW_COUNT()=0) THEN
ITERATE read_loop;
END IF;
SET k=k+1;
END WHILE;
-- Теперь ищем циклы размером от minlevel до maxlevel
WHILE k <= maxlevel DO
-- Растекаем жидкость ещё на один шаг
EXECUTE grade_normal_fluid;
-- Если жидкость перестала растекаться, переходим к следующей связи
IF (SELECT ROW_COUNT()=0) THEN
ITERATE read_loop;
END IF;
-- Проверяем, вернулась ли жидкость к начальной точке A
-- Если да, то найден цикл размером (k+1)
EXECUTE grade_update_first USING @quser, @user;
-- Подсчитываем число найденных циклов
SET ia = (SELECT ROW_COUNT());
IF ia > 0 THEN
SET aff = aff + ia;
-- Сохраняем статистику: сколько найдено колец размером (k+1)
UPDATE tmp_ring SET count=count + ia WHERE rang=k+1;
END IF;
-- Продолжаем поиск, даже если уже нашли цикл
-- Может быть несколько циклов разного размера, начинающихся с одной связи
SET done = FALSE;
SET k=k+1;
END WHILE;
SET done = tmp_done;
END LOOP read_loop;
-- Закрываем курсор и освобождаем ресурсы
CLOSE cur;
-- Освобождаем подготовленные запросы
DEALLOCATE PREPARE grade_set_zero;
DEALLOCATE PREPARE grade_first_fluid;
DEALLOCATE PREPARE grade_normal_fluid;
DEALLOCATE PREPARE grade_update_first;
-- Обновляем веса в основной таблице grade_vector
-- Звенья найденных циклов помечаются как удалённые (kweight=0)
UPDATE grade_vector AS g
INNER JOIN tmp_gv AS t ON g.quser=t.quser AND g.user=t.user
SET g.kweight=t.kweight;
-- Удаляем рабочую таблицу
DROP TABLE tmp_gv;
END;
Результаты поиска:
6-угольники находятся за 1.8 секунды.
7-угольники — за 23 секунды.
8-угольники — за 435 секунд.
Однако на практике оказалось, что через ~3 оценки все пользователи связаны между собой (аналог теории 6 рукопожатий), поэтому имеет смысл отслеживать только взаимные голосования.
Этап 5: Пересчёт ранга пользователей
Главный результат расчёта — это ранг пользователя (cache_rank), который может быть от -1 до 1, где -1 означает абсолютно вредного пользователя, а 1 — абсолютно полезного. Ранг вычисляется на основе оценок его контента другими пользователями:
INSERT INTO tmp_grade(id, grade, relgrade)
SELECT
id,
rt, -- суммарный рейтинг
IF(qcsum + qrsum = 0, 0,
(qcsum + qrsum)/(qcont + qrep)
) * sigm(@ksigm, people) AS rank
FROM (
SELECT
u.id AS id,
SUM(rt) AS rt, -- сумма оценок, умноженных на силу голоса
SUM(qcsum * qcache_rank) AS qcsum, -- взвешенная сумма за контент
SUM(qrsum * qcache_rank) AS qrsum, -- взвешенная сумма за репутацию
SUM(qcont * qcache_rank) AS qcont, -- взвешенное число контента
SUM(qrep * qcache_rank) AS qrep, -- взвешенное число репутации
COUNT(gv.quser) AS people
FROM users u
LEFT OUTER JOIN grade_vector gv
ON u.id=gv.user
AND gv.kweight > 0 -- только не удалённые оценки
AND gv.qcache_rank > 0
GROUP BY u.id
) t;
Здесь используется функция sigm(@ksigm, people), где:
@ksigm— коэффициент из конфига (обычно 0.05).people— число пользователей, оценивших данного пользователя.Формула:
sigm(K, N) = 2/(1+exp(-K*N))-1.
Затем применяются корректировки:
Инфляция от числа контента — пользователи с большим количеством контента получают бонус. Чем больше пользователь нагенерировал контента, тем точнее другие пользователи могут его оценить. Пользователь с 1-й удачной статьёй никогда не получит максимальный рейтинг. Чем-то напоминает методику расчёта индекса Хирша для научных статей.
UPDATE tmp_grade t
INNER JOIN (
SELECT u_id, SUM(
IF(type="rolik", 1,
IF(type="blog", 15/21, -- веса из конфига
IF(type="comment", 1/21, 0)
)
)
) AS csum
FROM grade
GROUP BY u_id, type, o_id
GROUP BY u_id
) AS g ON t.id=g.u_id
SET t.relgrade = sigm(@ksigm, csum) * t.relgrade;
Здесь используется функция sigm(@ksigm, csum), где:
@ksigm— коэффициент из конфига (обычно 0.05).csum— приведённое число единиц контента пользователя.Формула:
sigm(K, N) = 2/(1+exp(-K*N))-1.
Инфляция от времени последнего голосования — снижает ранг неактивных пользователей:
UPDATE tmp_grade t
LEFT OUTER JOIN (
SELECT grade_user_id, MAX(gdate) AS gmax_date
FROM grade
GROUP BY grade_user_id
) g ON t.id=g.grade_user_id
SET t.relgrade = IF(ISNULL(gmax_date), 0.8,
1 - 0.50929582 * ATAN(DATEDIFF(CURDATE(), gmax_date) * 0.0004116)
) * t.relgrade;
Обновление ранга и рейтинга пользователей:
UPDATE users r
INNER JOIN tmp_grade t ON r.id=t.id
SET
r.cache_rating = t.grade,
r.cache_rank = t.relgrade;
Вычисление силы голоса (логарифм от ранга):
UPDATE users SET cache_votepower =
IF(ISNULL(cache_rank) OR
cache_rank < 0.05 OR
(cache_rank=0 AND (cache_rating=0 OR ISNULL(cache_rating))),
0,
log(2.516890229, cache_rank*100+1) + 0.01
);
Логика проверки: Сила голоса = 0, если:
Ранг NULL.
Ранг < 0.05 (минимальный порог).
Ранг = 0 И рейтинг = 0 (или NULL) — это означает, что у пользователя нет оценок контента.
Важная деталь: Если ранг = 0, но cache_rating != 0, то пользователь всё равно получает силу голоса. Это защита от ситуации, когда у пользователя был контент с оценками, но ранг стал 0 (например, из-за временной инфляции). В этом случае пользователь сохраняет право голоса, так как его контент был оценён сообществом.
Основание логарифма подобрано так, чтобы при ранге 1 сила голоса была около 5, а при ранге 0.1 — около 0.5. Максимальная сила голоса ограничена значением 5, так как было принято решение, что один человек не может быть «правее» пяти обычных пользователей. Иначе возникает слишком сильный дисбаланс в системе.
Бонус за социальные сети (опционально): В проекте мы ещё увеличивали силу голоса тем, кто был подписан на разные социальные сети проекта, как более лояльным юзерам.
UPDATE users
INNER JOIN (
SELECT user_id, COUNT(*) c
FROM social_user
WHERE provider IN ('tw', 'fb')
GROUP BY user_id, provider
GROUP BY user_id
) t ON users.id=t.user_id
SET cache_votepower = cache_votepower + 0.4 * t.c;
Этап 6: Восстановление силы админов
В начале каждого цикла сила голоса админов восстанавливается из «божественного источника»:
UPDATE users SET cache_rank=1, cache_votepower=18
WHERE id = 17; -- главный админ
UPDATE users SET cache_rank=1, cache_votepower=4
WHERE id IN (1, 9, 11, ...); -- команда админов
Это гарантирует, что админы всегда остаются источником силы в системе, даже если на них голосуют против.
Голосование в реальном времени через AJAX из фронтэнда
-
Проверки:
Пользователь не забанен.
Сила голоса > 0 (или это админ).
Не превышен дневной лимит голосований.
Не превышен лимит голосований одному пользователю.
Пользователь не голосует за свой контент.
Сохранение оценки:
$val = 127 * $val; // +1/-1 -> +127/-127
INSERT INTO grade SET
o_id=?, type=?, value=?,
u_id=?, grade_user_id=?, ip=?
Обновление оценок онлайн при голосовании (инкрементально, без пересчёта всех взаимосвязей):
$power = $user['cache_votepower']; // или специальная для админов
$diff_grade = $val * $power / 127;
UPDATE roliki SET
cache_grade = IF(ISNULL(cache_grade), 0, cache_grade) + $diff_grade,
cache_people = IF(ISNULL(cache_people), 0, cache_people) + 1
WHERE id = ?;
Это позволяет сразу показывать обновлённую суммарную оценку единицы контента без ожидания следующего пересчёта.
Первичная инициализация рейтинговой системы
При первом запуске системы выполняется скрипт calc_rating_first:
всем, у кого есть в сумме хотя бы 3 ролика и поста ставим силу голоса равную 1,
устанавливаем голоса админам.
Подсчёт числа контента каждого пользователя:
Создаём временную таблицу с пользователями и числом их контента.
CREATE TEMPORARY TABLE tmp_count(
id int unsigned NOT NULL,
count int unsigned NOT NULL
);
INSERT INTO tmp_count
SELECT u.id,
IF(ISNULL(r_s), 0, r_s) + IF(ISNULL(b_s), 0, b_s) AS t_s
FROM users u
LEFT JOIN (
SELECT author, COUNT(r.id) AS r_s
FROM roliki r
GROUP BY author
) r ON u.id=r.author
LEFT JOIN (
SELECT blog_author author, COUNT(b.id) AS b_s
FROM blogs b
GROUP BY author
) b ON u.id=b.author;
Установка начальных значений:
UPDATE users
SET cache_rank=0, cache_votepower=0,
cache_rating=NULL, cache_people=NULL,
cache_relgrade=NULL, cache_grade=NULL;
-- Пользователям с 3+ единицами контента даём начальную силу
UPDATE users u
INNER JOIN tmp_count t ON u.id=t.id
SET cache_votepower=1, cache_rank=0.2
WHERE t.count > 2;
-- Админам — максимальные значения
UPDATE users SET cache_rank=1, cache_votepower=4
WHERE id IN (...);
UPDATE users SET cache_rank=1, cache_votepower=18
WHERE id=17; -- главный админ
После инициализации запускается основной цикл пересчёта.
Важное замечание о стабильности оценок и сходимости результатов пересчёта:
При разработке рейтинговой системы возникла интересная проблема: при регулярных пересчётах рейтингов пользователей стали появляться волны, похожие на ритмы мозговой активности людей, вместо того, чтобы сходиться к одному стабильному значению. Рейтинги циклически менялись, создавая автоколебания в системе.
Чтобы решить эту проблему, пришлось:
Тщательно подбирать константы (коэффициенты сигмоиды, основание логарифма для силы голоса, относительные веса разных типов контента).
Создать инициализационный скрипт
calc_rating_first, который устанавливает начальные значения для всех пользователей.Ввести механизм восстановления силы админов в начале каждого цикла, чтобы система имела «вечный источник справедливости». Иначе сплочённые тусовки могли бы заминусовать админский контент и лишить админов силы голоса.
Без правильной инициализации и подбора констант система могла войти в режим автоколебаний, где рейтинги постоянно менялись без сходимости к стабильным значениям.
Оптимизация производительности
Таблицы в памяти
Ключевая оптимизация для поиска колец накруток — использование таблиц в памяти (ENGINE=memory) для промежуточных вычислений. Это критически важно для скоростной работы системы при обработке десятков тысяч записей.
Преимущества таблиц в памяти:
Скорость: Таблицы в памяти работают в десятки раз быстрее таблиц на диске.
Производительность: Операции UPDATE, INSERT, SELECT выполняются практически мгновенно.
Не блокируют основные таблицы: Пользователи сайта могут продолжать работать без задержек.
Идеально для алгоритмов: Особенно эффективны для сложных алгоритмов поиска циклов, где выполняется множество операций UPDATE.
Использовались в процедуре grade_ring_search() (таблицы tmp_ring и tmp_gv).
Без использования таблиц в памяти поиск 8-угольных колец занял бы не 435 секунд, а значительно больше времени, что сделало бы систему непрактичной. Хотя в конечном итоге оказалось, что имеет смысл отслеживать только взаимные накрутки. Там как в рамках сайта уже через 2-3 голосования (аналог «рукопожатий») все пользователи тотально связаны между собой.
Использование индексов
Не секрет, что в 95% случаев секрет быстрой работы БД — это создание и использование индексов по полям, участвующим в запросах.
Ключевые индексы для производительности:
-
В таблице
grade:(type, o_id, grade_user_id)— уникальность + быстрый поиск оценок объекта.(type, grade_user_id, gdate)— проверка дневных лимитов.
-
В таблице
grade_vector:(high, kweight)— поиск активных связей для колец.(high, quser, user)— оптимизация JOIN при поиске циклов.(high, user)— быстрый поиск всех голосований за пользователя.
-
В контентных таблицах:
(vis, cache_relgrade)— фильтрация и сортировка на главной по рейтингу контента.
Заключение
Реализация рейтинговой системы на SQL позволила:
Обеспечить согласованность массового пересчёта рейтингов контента и пользователй через атомарность SQL-запросов.
Выразить сложную логику декларативно.
Компактно сконцентрировать всю бизнес-логику в SQL-запросах и хранимых процедурах.
Использовать низкоуровневые оптимизации MySQL для работы с данными для обеспечения быстроты пересчёта.
Пересчитывать рейтинги тысяч пользователей и десятков тысяч контента за 1-1.5 минуты в фоновом режиме.
Некоторые части алгоритма (например, поиск многоугольников накрутки) являются избыточными, они были реализованы для исследования границ возможного и понимания структуры связей в сообществе.
Система работает стабильно уже более 10 лет без каких-либо моих вмешательств.
Надеюсь, какие-то идеи из этой статьи смогут вам пригодиться в ваших разработках своих рейтинговых систем.
Успехов!
© 2025 ООО «МТ ФИНАНС»