Как да направите бързи броячи в MySQL - XP Injection

От време на време ще пиша интересни технически бележки, никога не се знае, че разработчиците също четат нашия блог. 🙂 И така, днес ще се опитаме да разрешим един много прост проблем - да организираме броячи в MySQL. За какво е? Има много примери за използване: трябва да съхранявате броя на посещенията за всяка страница от сайта, когато обработвате голямо количество данни, трябва да изчислите честотата на поява на елементи, трябва да контролирате броя на заявките за всеки потребител в системата ... Като цяло, задачата често се случва.

Използваме „ненадеждно“ хранилище

Ако точността на резултата не е особено важна за вас и загубата на част от данните не е критичен проблем, тогава моята препоръка към вас е да съхранявате данните извън базата данни. Има много опции за организиране на брояча, някои са малко по-бързи, други са малко по-бавни. Ето само няколко от тях:

Съхранявайте всички броячи в паметта и периодично изчиствайте стойностите за проверка на диска в инкрементални файлове с клеймо за време. Този метод ви позволява да загубите само част от данните от последното записване.
Използвайте Redis с неговата способност за противодействие. Работи много бързо и доста надеждно. Но ако все още не използвате Redis, тогава добавянето му само за броячи е доста противоречиво. И вторият проблем е транзакционен - броячът трябва да се актуализира след успешно завършване на транзакцията (в идеалния случай в рамките на транзакцията), в противен случай понякога, когато системата се срине или спре, броячът може да излезе от синхрон с реалността.
Използвайте ZooKeeper с неговите разпределени броячи. Той предлага повече функционалност, отколкото ви трябва, но в някои случаи работи много добре. Отново не препоръчвам въвеждането на ZooKeeper само за броячи. И при голямо натоварване можестане тясно място, защото работи в една нишка.

Всички тези опции няма да постигнат перфектна точност и да избегнат потенциална загуба на данни или двойно отчитане. Но за много задачи това е повече от достатъчно.

Глупаво решение на челото

Все още се нуждаете от точни броячи, свързани с бизнес транзакция, и сте решили да ги внедрите на ниво база данни. От този момент до края на тази статия всички примери ще бъдат заMySQL. Решението се подсказва, защото вече имате база данни и защо да не я използвате и за тази цел. Първото решение, което идва на ум, е да създадете отделна таблица:

Е, за да актуализирате брояча, трябва да извикате банален код:

Това решение има няколко „изненади“. Първият е много прост и разбираем за всеки, който знае как работят базите данни. В случай на актуализиране на брояча от няколко нишки, всички те ще бъдат блокирани и изпълнени по ред на приоритет. Следователно представянето няма да бъде напълно радостно. Но има още една интересна "изненада" - наличието на блокирания в банален код. Те могат да се появят в няколко случая наведнъж:

Вие актуализирате няколко брояча наведнъж в една транзакция. Няколко такива транзакции могат да се извършват паралелно. Ако не контролирате реда на resource_id, тогава поради спецификата на улавяне на ключалки по индекс (в примера това е PK, но във всеки случай, разбира се, трябва да го имате, за да може търсенето на брояча по resource_id да работи бързо) ще получите блокиране.
Друг сценарий на блокиране ще се появи, ако извършите действия със самата таблица с ресурси в същата транзакция за друг запис (например дете или родител) и вместо обикновен индекс използвате FK на таблицата с ресурси.

Като цяло опциятарешенията са толкова и определено са подходящи само за леки натоварвания.

Само вложки, нищо освен вложки

И тук идва оптимизацията. Спомняте си, че вмъкванията практически не се блокират взаимно, за разлика от актуализациите на данни. Следователно решавате да премахнете PK от resource_id и да добавите нови данни, вместо да актуализирате брояча. За да изчислите общата стойност на брояча, просто ще използвате следната заявка:

За известно време всичко дори ще работи доста бързо, но с течение на времето производителността бавно ще се влоши и общото количество „боклук“ исторически данни ще расте през цялото време. И започваш да мислиш повече...

Изчислете междинни суми

За да се отървете от "боклук" исторически данни, има много прости техники от същия тип. Необходимо е от време на време да се обобщават данни и да се изтриват или „виртуално изтриват“ старите. Нека да разгледаме малко по-подробно. Изпълнявате заданието в отделна нишка или на ниво база данни, или на ниво приложение. Тази задача преминава през таблицата с броячи, изчислява сумата за всеки ресурс и вместо набор от записи оставя точно един със стойността на сумата. Как да го реализираме технически:

Блокирайте достъпа до определен ресурс за времетраенето на операцията. Проблемът е, че други нишки продължават да вмъкват стойности. И това означава, че без блокиране ще изтриете стари записи, сред които може да има нови. Така рискувате да счупите брояча. Заключването може да се извърши както на ниво база данни, така и в кода. Методът не е много добър, защото ключалките винаги забавят работата.
Вторият начин е да добавите нова колона с часа на добавяне на записа (тип TIMESTAMP). Сега можете безопасно да изчислите сумата за 5 минутиминали и изтрийте записи, които вече не са необходими в същата транзакция. Сумата се добавя като нов запис за текущото време. Всъщност този метод също е изпълнен с допълнителни заключвания при изтриване и блокирания при паралелно вмъкване и изтриване в индекса, който имате на resource_id.
Третият начин е да използвате отделен поток за почистване и да маркирате обобщените записи със специален маркер. В този случай правилната стойност на брояча ще бъде равна на сбора от всички записи, като се започне от последната обобщена стойност. Историческите данни могат да бъдат изтривани един по един или блокове по всяко време без никакъв риск.

Третият метод е може би най-бързият и най-сигурният от ключалките, изброени в плана. Това решение е„виртуално изтриване“ и често се използва за изчисляване на баланса за определени периоди. Обобщените записи могат да бъдат изтрити или да останат в системата за изграждане на времеви графики. Изчисляването на насрещната стойност е повече или по-малко фиксирано във времето и зависи от периода на агрегиране.

И тогава споменът ви идва на ум, че изтриването на данни от таблица обикновено не е много кошерна операция и е най-добре да се избягва. Как да бъдем?

Почистваме бързо след себе си

За да избегнете изтриването на данни, трябва да се разтегнете малко и да запомните за операциятаTRUNCATE TABLE. Изчиства данните много бързо. Но не всичко е толкова просто, ще трябва леко да промените алгоритъма за актуализиране на стойностите на брояча.

За да направим това, вместо само една, имаме нужда от 2 или 3 плочи с еднаква структура наведнъж: resource_counter,resource_counter_shadow, resource_counter_total (тази плоча не е задължителна). Всеки от тях ще поддържа само вложки. Вашето приложение записва всички промени в стойносттаброяч под формата на допълнителни записи в таблицата resource_counter и само в нея. Паралелно работи отделна нишка, която веднъж в определен момент замества таблици:

Заявката е атомарна и по същество разменя таблиците resource_counter и resource_counter_shadow. Оказва се, че никой не работи с таблицата resource_counter_shadow и можете бързо да обобщите данните в нея. Резултатите могат да бъдат добавени към таблицата resource_counter със заявката:

Такава заявка ще работи доста бързо, тъй като таблицата resource_counter_shadow е малка и се контролира от интервала на агрегиране. Можете също така да използвате незадължителната таблица resource_counter_total, която е въведена за оптимизация (за да избегнете преливане на данни от една таблица в друга, ако не се промени). Можете да направите това със следната заявка:

Има още по-красива версия на същата заявка:

И разбира се, след използване на таблицата resource_counter_shadow, тя се изчиства. С изключение на незадължителна таблица, този подход изисква минимален брой заключвания, но увеличава броя на разливите на данни.

Прилагане на мулти-брояч

В даден момент ще се чудите дали всичко е станало твърде сложно и ще се обърнете към първоначалния проблем. Състои се в това, че записът в таблицата на брояча е блокиран по време на актуализацията. Но в същото време формирането на отделни записи за всяка актуализация води до увеличаване на техния брой и необходимост от почистване на данните. Тогава трябва да използвате златната среда. За да направите това, нова колона counter_index се добавя към таблицата на брояча, която е включена в PK:

Нашата цел сега е да разпространим актуализацията в множество записи на таблици, което намалява вероятността от блокиране. отКачеството на разпределение ще зависи от броя на ключалките на един брояч. За проста версия можете да използвате следната заявка:

За да получите стойността на брояча за конкретен ресурс, пак ще трябва да използвате сумата, но за фиксиран брой редове (максимум 10 в нашия пример):

Работи бързо както за актуализиране, така и за получаване на стойност. Можете да балансирате броя на броячите на ресурс.

Както можете да видите, има много решения на такъв прост проблем, ако познавате предметната област и внимателно тествате вашите решения. Надявам се това да спести време на някого. 🙂

Не искате да пропуснете нищо? Абонирайте се за RSS емисията или ни следвайте в Twitter!