Създаване на физически модел на база данни: инженеринг на производителността. Създаване на физически модел на база данни: дизайн на производителността на Ms sql сървър, автоматично разделяне на таблици
Когато работим върху големи таблици, постоянно срещаме проблеми с производителността при поддържане и актуализиране на данни. Едно от най-продуктивните и удобни решения за възникващи проблеми е разделянето.
Най-общо казано, разделянето е разделянето на таблица или индекс на блокове. В зависимост от настройката за разделяне може да има блокове различни размери, могат да се съхраняват в различни файлови групи и файлове.
Разделянето има както предимства, така и недостатъци.
Ползите са добре описани на уебсайта Microsoft, ще дам откъс:
« Разделянето на големи таблици или индекси може да осигури следните ползи за управление и производителност:
- Това позволява подгрупи от данни да бъдат премествани и достъпни бързо и ефективно, като същевременно се запазва целостта на набора от данни. Например операция като зареждане на данни от OLTP към OLAP система, завършва за секунди, а не за минути и часове, както при неразделените данни.
- Операциите по поддръжката могат да бъдат завършени по-бързо с една или повече секции. Операциите са по-ефективни, защото се изпълняват само върху подмножества от данни, а не върху цялата таблица. Например, можете да компресирате данни в един или повече дялове или да изградите отново един или повече индексни дялове.
- Можете да подобрите производителността на заявките въз основа на заявки, които се изпълняват често във вашата хардуерна конфигурация. Например, оптимизаторът на заявки може да изпълнява по-бързо equijoin заявки на две или повече разделени таблици, ако таблиците имат еднакви колони на дялове, тъй като самите дялове могат да бъдат обединени.
Когато сортирате данни за I/O операции в SQL Server, първо сортирате данните по дял. SQL Server има достъп само до един диск наведнъж, което може да намали производителността. За да се ускори сортирането на данни, се препоръчва файловете с данни да се разпределят в секции в няколко твърди дисковечрез създаване на RAID. По този начин, въпреки че данните са сортирани по дял, SQL Server ще може едновременно да осъществява достъп до всички твърди дискове във всеки дял.
Можете също така да подобрите производителността, като използвате ключалки на ниво дял, а не на цялата таблица. Това може да намали броя на конфликтите при заключване за таблица».
Недостатъците включват трудността при администриране и поддържане на работата на разделени таблици.
Няма да се спираме на прилагането на разделянето, тъй като този проблем е много добре описан на уебсайта на Microsoft.
Вместо това ще се опитаме да покажем начин за оптимизиране на работата на разделените таблици или по-скоро ще покажем оптималния (според нас) начин за актуализиране на данни за всеки период от време.
Голямото предимство на преградената маса е физическото разделяне на тези секции. Това свойство ни позволява да разменяме секции помежду си или с всяка друга таблица.
За типична актуализация на данни с помощта на плъзгащ се прозорец (например ежемесечно) ще трябва да преминем през следните стъпки:
1. Намерете необходимите редове в голяма таблица;
2. Изтрийте намерените редове от таблицата и индекса;
3. Вмъкнете нови редове в таблицата, актуализирайте индекса.
Когато съхранявате милиарди редове в таблица, тези операции ще отнемат доста време за дълго време, можем да се ограничим практически до едно действие: просто заменете желаната секция с предварително подготвена таблица (или секция). В този случай не е необходимо да изтриваме или вмъкваме редове, а също така трябва да актуализираме индекса на цялата голяма таблица.
Нека да преминем от думи към действия и да покажем как да приложим това.
1. Първо, настройте разделена таблица, както е описано в статията, спомената по-горе.
2. Създайте таблиците, необходими за обмена.
За да актуализираме данните, имаме нужда от мини-копие на целевата таблица. Това е мини-копие, защото ще съхранява данни, които трябва да бъдат добавени към целевата таблица, т.е. данни само за 1 месец. Ще ви е необходима и трета празна таблица, за да реализирате обмен на данни. Защо е необходимо - ще обясня по-късно.
На мини-копието и таблицата за обмен се налагат строги условия:
- Преди употреба Оператор SWITCHИ двете таблици трябва да съществуват. Преди да може да се извърши операция по превключване, както таблицата, от която дялът се премества (таблицата източник), така и таблицата, която получава дяла (таблицата целева), трябва да съществуват в базата данни.
- Дестинационният раздел трябва да съществува и трябва да е празен. Ако таблица се добави като дял към съществуваща разделена таблица или дял се премести от една разделена таблица в друга, целевият дял трябва да съществува и да е празен.
- Неразделената вторична таблица трябва да съществува и трябва да е празна. Ако дялът е предназначен да образува единична неразделена таблица, тогава таблицата, която получава новия дял, трябва да съществува и да бъде празна неразделена таблица.
- Секциите трябва да са от една и съща колона. Ако даден дял се превключи от една разделена таблица към друга, тогава и двете таблици трябва да бъдат разделени в една и съща колона.
- Таблиците източник и местоназначение трябва да са в една и съща файлова група. Таблиците източник и целева таблица в оператор ALTER TABLE...SWITCH трябва да се съхраняват в една и съща файлова група, както и техните колони с големи стойности. Всички съответни индекси, индексни дялове или изгледи на индексирани дялове също трябва да се съхраняват в същата файлова група. Въпреки това, тя може да е различна от файловата група за съответните таблици или други подходящи индекси.
Нека обясня ограниченията, използвайки нашия пример:
1. Мини-копието на таблицата трябва да бъде разделено на същата колона като целевата. Ако миникопието е неразделена таблица, то трябва да се съхранява в същата файлова група като дяла, който се заменя.
2. Таблицата за обмен трябва да е празна и също трябва да бъде разделена на една и съща колона или трябва да се съхранява в същата файлова група.
3. Ние осъществяваме обмена.
Сега имаме следното:
Таблица с данни за всички времена (наричана по-долу Таблица_A)
Таблица с данни за 1 месец (по-нататък Таблица_B)
Празна таблица (по-нататък Table_C)
На първо място, трябва да разберем в кой раздел съхраняваме данните.
Можете да разберете, като попитате:
ИЗБЕРЕТЕ
брой(*) като
, $PARTITION.(dt) като
, rank() над (подреждане по $PARTITION.(dt))
ОТ dbo. (без заключване)
групиране по $PARTITION.(dt)
В тази заявка получаваме секции, които съдържат редове с информация. Няма нужда да броим количеството - направихме това, за да проверим необходимостта от обмен на данни. Използваме Rank, за да можем да влезем в цикъл и да актуализираме няколко секции в една процедура.
Веднага след като разберем в кои секции се съхраняват нашите данни, те могат да бъдат разменени. Да приемем, че данните се съхраняват в раздел 1.
След това трябва да извършите следните операции:
Разменете дялове от целевата таблица с таблицата за обмен.
АЛТЕР ТАБЛИЦА. ПРЕВКЛЮЧВАНЕ НА ДЯЛ 1 КЪМ . ДЯЛ 1
Сега имаме следното:
В целевата таблица в секцията, от която се нуждаем, не са останали данни, т.е. разделът е празен
Размяна на дялове от целевата таблица и миникопиране
АЛТЕР ТАБЛИЦА. ПРЕВКЛЮЧВАНЕ НА ДЯЛ 1 КЪМ . ДЯЛ 1
Сега имаме следното:
Месечните данни се появиха в целевата таблица, но мини-копието вече е празно
Изчистете или изтрийте таблицата за обмен.
Ако имате клъстериран индекс на вашата таблица, това също не е проблем. Трябва да се създаде на всичките 3 таблици, разделени на една и съща колона. При смяна на раздели, индексът ще се актуализира автоматично без повторно изграждане.
Добър вечер/ден/утро, скъпи хора! Продължаваме да развиваме и разширяваме блога за моя любим rdbms с отворен код Postgresql. По чудо се случи така, че темата на днешната тема никога досега не е била повдигана тук. Трябва да кажа, че разделянето в postgresql е много добре описано в документацията, но това наистина ли ще ме спре?).
Въведение
Като цяло, разделянето обикновено се разбира не като някаква технология, а по-скоро като подход към проектирането на бази данни, появил се много преди СУБД да започнат да поддържат т.нар. преградени маси. Идеята е много проста - разделете масата на няколко по-малки части. Има два подвида - хоризонтално и вертикално сечение.Хоризонтално преграждане
Части от таблица съдържат различни редове. Да кажем, че имаме журнална таблица за някакво абстрактно приложение - LOGS. Можем да го разделим на части - една за регистрационни файлове за януари 2009 г., друга за февруари 2009 г. и т.н.Вертикално преграждане
Части от таблица съдържат различни колони. Намирането на приложение за вертикално разделяне (когато е действително оправдано) е малко по-трудно, отколкото за хоризонтално разделяне. Като сферичен кон предлагам да разгледаме тази опция: таблицата NEWS има колони ID, КРАТЪК ТЕКСТ, ДЪЛЪГ ТЕКСТ и нека полето ДЪЛЪГ ТЕКСТ да се използва много по-рядко от първите две. В този случай има смисъл да разделите таблицата NEWS по колони (създайте две таблици съответно за SHORTTEXT и LONGTEXT, свързани с първични ключове + създайте изглед NEWS, съдържащ и двете колони). Така, когато се нуждаем само от описание на новината, СУБД не трябва да чете целия текст на новината от диска.Поддръжка за разделяне в съвременните СУБД
Повечето съвременни СУБД поддържат разделяне на таблици под една или друга форма.- Оракул- поддържа разделяне, започвайки от версия 8. Работата със секции, от една страна, е много проста (не е нужно изобщо да мислите за тях, работите като с обикновена таблица*), а от друга страна, всичко е много гъвкаво. Секциите могат да бъдат разделени на „подраздели“, изтрити, разделени, прехвърлени. Поддържа се различни вариантииндексиране на разделена таблица (глобален индекс, разделен индекс). Връзка към дълго описание.
- Microsoft SQL сървър- поддръжката за разделяне се появи наскоро (през 2005 г.). Първото впечатление от употребата е "Е, най-накрая!! :)", второто е "Работи, всичко изглежда наред." Документация на msdn
- MySQL- поддържа от версия 5.1. Много добро описание на Хабре
- И така нататък…
Разделяне в Postgresql
Разделянето на таблици в postgresql е малко по-различно в изпълнението от другите бази данни. Основата за разделянето е наследяването на таблицата (нещо уникално за postgresql). Тоест, трябва да имаме главна таблица, а нейните секции ще бъдат таблици наследници. Ще разгледаме разделянето, като използваме пример за задача, близка до реалността.Постановка на проблема
Базата данни се използва за събиране и анализ на данни за посетителите на сайта/сайтовете. Обемите от данни са достатъчно големи, за да се мисли за разделяне. В повечето случаи анализът използва данни от последния ден.1. Създайте основната таблица:
CREATE TABLE analytics.events
user_id UUID NOT NULL,
event_type_id SMALLINT NOT NULL,
event_time TIMESTAMP DEFAULT now() NOT NULL,
url VARCHAR (1024) NOT NULL,
референт VARCHAR (1024),
ip INET NOT NULL
);
2. Ще разделим по дни, като използваме полето event_time. Ще създадем нов раздел за всеки ден. Ще именуваме секциите според правилото: analytics.events_DDMMYYYY. Ето например секцията за 1 януари 2010 г.
СЪЗДАВАНЕ НА ТАБЛИЦА analytics.events_01012010
event_id BIGINT ПО ПОДРАЗБИРАНЕ nextval("analytics.seq_events" ) ПЪРВИЧЕН КЛЮЧ,
ПРОВЕРКА (event_time >= TIMESTAMP "2010-01-01 00:00:00" И event_time< TIMESTAMP "2010-01-02 00:00:00" )
) НАСЛЕДЯВА(analytics.events);* Този изходен код беше маркиран с инструмента за открояване на изходния код.
Когато създаваме раздел, ние изрично задаваме полето event_id (PRIMARY KEY не се наследява) и създаваме CHECK CONSTRAINT на полето event_time, за да не вмъкваме ненужни неща.
3. Създайте индекс на полето event_time. Когато разделяме таблицата, очакваме, че повечето заявки към таблицата със събития ще използват условие в полето event_time, така че индекс в това поле ще ни помогне много.
CREATE INDEX events_01012010_event_time_idx НА analytics.events_01012010 ИЗПОЛЗВАНЕ на btree(event_time);* Този изходен код беше маркиран с инструмента за открояване на изходния код.
4. Искаме да гарантираме, че когато бъдат вмъкнати в главната таблица, данните завършват в секцията, предназначена за тях. За целта правим следния трик – създаваме тригер, който ще контролира потоците от данни.
СЪЗДАВАНЕ ИЛИ ЗАМЕНЯНЕ НА ФУНКЦИЯ analytics.events_insert_trigger()
ВРЪЩА ТРИГЕР КАТО $$
НАЧАЛО
АКО (НОВО .event_time >= TIMESTAMP "2010-01-01 00:00:00" И
НОВО .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (НОВО .*);
ДРУГО
ПОВИШЕТЕ ИЗКЛЮЧЕНИЕ „Дата % е извън диапазона. Коригирайте analytics.events_insert_trigger“, НОВО .event_time;
КРАЙ АКО ;
RETURN NULL;
КРАЙ ;
$$
ЕЗИК plpgsql;* Този изходен код беше маркиран с инструмента за открояване на изходния код.
CREATE TRIGGER events_before_insert
ПРЕДИ ВМЪКНЕТЕ В analytics.events
ЗА ВСЕКИ РЕД ИЗПЪЛНЕТЕ ПРОЦЕДУРА analytics.events_insert_trigger();* Този изходен код беше маркиран с инструмента за открояване на изходния код.
5. Всичко е готово, вече имаме разделена таблица, наречена analytics.events. Можем да започнем яростно да анализираме нейните данни. Между другото, създадохме ограничения CHECK не само за да защитим секциите от неправилни данни. Postgresql може да ги използва, когато създава план за заявка (обаче, с жив индекс на event_time, печалбата ще бъде минимална), просто използвайте директивата constraint_exclusion:
SET ограничение_изключване = включено;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* Този изходен код беше маркиран с инструмента за открояване на изходния код.
Край на първата част
И така, какво имаме? Да вървим точка по точка:1. Таблицата със събития, разделена на секции, анализът на наличните данни за последния ден става по-лесен и бърз.
2. Ужасът от осъзнаването, че всичко това трябва да бъде подкрепено по някакъв начин, секциите трябва да бъдат създадени навреме, без да забравяте да промените съответно спусъка.
Ще ви кажа как лесно и безгрижно да работите с разделени маси във втората част.
UPD1: Разделянето е заменено с разделяне
UPD2:
Въз основа на коментар на един от читателите, който, за съжаление, няма акаунт в Хабре:
Има няколко проблема, свързани с наследяването, които трябва да се вземат предвид при проектирането. Секциите не наследяват първичния ключ и външни ключовекъм техните колони. Тоест, когато създавате раздел, трябва изрично да създадете PRIMARY KEY и FOREIGN KEY за колоните на раздела. Бих искал сам да отбележа, че създаването на FOREIGN KEY върху колоните на разделена таблица не е най-добрият начин. В повечето случаи разделената таблица е "фактическа таблица" и сама по себе си се отнася до "измерение" на таблицата.
Добър вечер/ден/утро, скъпи хора! Продължаваме да развиваме и разширяваме блога за моя любим rdbms с отворен код Postgresql. По чудо се случи така, че темата на днешната тема никога досега не е била повдигана тук. Трябва да кажа, че разделянето в postgresql е много добре описано в документацията, но това наистина ли ще ме спре?).
Въведение
Като цяло, разделянето обикновено се разбира не като някаква технология, а по-скоро като подход към проектирането на бази данни, появил се много преди СУБД да започнат да поддържат т.нар. преградени маси. Идеята е много проста - разделете масата на няколко по-малки части. Има два подвида - хоризонтално и вертикално сечение.Хоризонтално преграждане
Части от таблица съдържат различни редове. Да кажем, че имаме журнална таблица за някакво абстрактно приложение - LOGS. Можем да го разделим на части - една за регистрационни файлове за януари 2009 г., друга за февруари 2009 г. и т.н.Вертикално преграждане
Части от таблица съдържат различни колони. Намирането на приложение за вертикално разделяне (когато е действително оправдано) е малко по-трудно, отколкото за хоризонтално разделяне. Като сферичен кон предлагам да разгледаме тази опция: таблицата NEWS има колони ID, КРАТЪК ТЕКСТ, ДЪЛЪГ ТЕКСТ и нека полето ДЪЛЪГ ТЕКСТ да се използва много по-рядко от първите две. В този случай има смисъл да разделите таблицата NEWS по колони (създайте две таблици съответно за SHORTTEXT и LONGTEXT, свързани с първични ключове + създайте изглед NEWS, съдържащ и двете колони). Така, когато се нуждаем само от описание на новината, СУБД не трябва да чете целия текст на новината от диска.Поддръжка за разделяне в съвременните СУБД
Повечето съвременни СУБД поддържат разделяне на таблици под една или друга форма.- Оракул- поддържа разделяне, започвайки от версия 8. Работата със секции, от една страна, е много проста (не е нужно изобщо да мислите за тях, работите като с обикновена таблица*), а от друга страна, всичко е много гъвкаво. Секциите могат да бъдат разделени на „подраздели“, изтрити, разделени, прехвърлени. Поддържат се различни опции за индексиране на разделена таблица (глобален индекс, разделен индекс). Връзка към дълго описание.
- Microsoft SQL сървър- поддръжката за разделяне се появи наскоро (през 2005 г.). Първото впечатление от употребата е "Е, най-накрая!! :)", второто е "Работи, всичко изглежда наред." Документация на msdn
- MySQL- поддържа от версия 5.1.
- И така нататък…
Разделяне в Postgresql
Разделянето на таблици в postgresql е малко по-различно в изпълнението от другите бази данни. Основата за разделянето е наследяването на таблицата (нещо уникално за postgresql). Тоест, трябва да имаме главна таблица, а нейните секции ще бъдат таблици наследници. Ще разгледаме разделянето, като използваме пример за задача, близка до реалността.Постановка на проблема
Базата данни се използва за събиране и анализ на данни за посетителите на сайта/сайтовете. Обемите от данни са достатъчно големи, за да се мисли за разделяне. В повечето случаи анализът използва данни от последния ден.1. Създайте основната таблица:
CREATE TABLE analytics.events
user_id UUID NOT NULL,
event_type_id SMALLINT NOT NULL,
event_time TIMESTAMP DEFAULT now() NOT NULL,
url VARCHAR (1024) NOT NULL,
референт VARCHAR (1024),
ip INET NOT NULL
);
2. Ще разделим по дни, като използваме полето event_time. Ще създадем нов раздел за всеки ден. Ще именуваме секциите според правилото: analytics.events_DDMMYYYY. Ето например секцията за 1 януари 2010 г.
СЪЗДАВАНЕ НА ТАБЛИЦА analytics.events_01012010
event_id BIGINT ПО ПОДРАЗБИРАНЕ nextval("analytics.seq_events" ) ПЪРВИЧЕН КЛЮЧ,
ПРОВЕРКА (event_time >= TIMESTAMP "2010-01-01 00:00:00" И event_time< TIMESTAMP "2010-01-02 00:00:00" )
) НАСЛЕДЯВА(analytics.events);* Този изходен код беше маркиран с инструмента за открояване на изходния код.
Когато създаваме раздел, ние изрично задаваме полето event_id (PRIMARY KEY не се наследява) и създаваме CHECK CONSTRAINT на полето event_time, за да не вмъкваме ненужни неща.
3. Създайте индекс на полето event_time. Когато разделяме таблицата, очакваме, че повечето заявки към таблицата със събития ще използват условие в полето event_time, така че индекс в това поле ще ни помогне много.
CREATE INDEX events_01012010_event_time_idx НА analytics.events_01012010 ИЗПОЛЗВАНЕ на btree(event_time);* Този изходен код беше маркиран с инструмента за открояване на изходния код.
4. Искаме да гарантираме, че когато бъдат вмъкнати в главната таблица, данните завършват в секцията, предназначена за тях. За целта правим следния трик – създаваме тригер, който ще контролира потоците от данни.
СЪЗДАВАНЕ ИЛИ ЗАМЕНЯНЕ НА ФУНКЦИЯ analytics.events_insert_trigger()
ВРЪЩА ТРИГЕР КАТО $$
НАЧАЛО
АКО (НОВО .event_time >= TIMESTAMP "2010-01-01 00:00:00" И
НОВО .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (НОВО .*);
ДРУГО
ПОВИШЕТЕ ИЗКЛЮЧЕНИЕ „Дата % е извън диапазона. Коригирайте analytics.events_insert_trigger“, НОВО .event_time;
КРАЙ АКО ;
RETURN NULL;
КРАЙ ;
$$
ЕЗИК plpgsql;* Този изходен код беше маркиран с инструмента за открояване на изходния код.
CREATE TRIGGER events_before_insert
ПРЕДИ ВМЪКНЕТЕ В analytics.events
ЗА ВСЕКИ РЕД ИЗПЪЛНЕТЕ ПРОЦЕДУРА analytics.events_insert_trigger();* Този изходен код беше маркиран с инструмента за открояване на изходния код.
5. Всичко е готово, вече имаме разделена таблица, наречена analytics.events. Можем да започнем яростно да анализираме нейните данни. Между другото, създадохме ограничения CHECK не само за да защитим секциите от неправилни данни. Postgresql може да ги използва, когато създава план за заявка (обаче, с жив индекс на event_time, печалбата ще бъде минимална), просто използвайте директивата constraint_exclusion:
SET ограничение_изключване = включено;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* Този изходен код беше маркиран с инструмента за открояване на изходния код.
Край на първата част
И така, какво имаме? Да вървим точка по точка:1. Таблицата със събития, разделена на секции, анализът на наличните данни за последния ден става по-лесен и бърз.
2. Ужасът от осъзнаването, че всичко това трябва да бъде подкрепено по някакъв начин, секциите трябва да бъдат създадени навреме, без да забравяте да промените съответно спусъка.
Ще ви кажа как лесно и безгрижно да работите с разделени маси във втората част.
UPD1: Разделянето е заменено с разделяне
UPD2:
Въз основа на коментар на един от читателите, който, за съжаление, няма акаунт в Хабре:
Има няколко проблема, свързани с наследяването, които трябва да се вземат предвид при проектирането. Дяловете не наследяват първичния ключ и външните ключове на своите колони. Тоест, когато създавате раздел, трябва изрично да създадете PRIMARY KEY и FOREIGN KEY за колоните на раздела. Бих искал сам да отбележа, че създаването на FOREIGN KEY върху колоните на разделена таблица не е най-добрият начин. В повечето случаи разделената таблица е "фактическа таблица" и сама по себе си се отнася до "измерение" на таблицата.