Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
Доброго времени суток, уважаемые коллеги!
При работе с MS SQL 2005 (9.00.3042) возник вопрос, связанный с оптимизацией структуры БД.
1. Подскажите, правилен ли подход, при котором в базе имеется большое количество таблиц сравнительно небольшого объема (до полумиллиона записей-они постепенно наполняются), их, скажем, три-четыре тысячи и их число медленно увеличивается. Или более правильно перегруппировать данные в базе так, чтобы их хранить в малом количестве таблиц, пусть пятьдесят-сто, но размерности которых будут гораздо больше (до 10-15 миллионов записей, будут наполняться гораздо быстрее)?
2. Если для обоих описанных в п.1 вариантов рассматривать построение индекса (простого, не кластерного) для каждой таблицы по одному полю, по которому нужно и искать, и, вдобавок, сортировать (order by), то что можете посоветовать в таком случае (имеется ввиду факт наличия большого числа индексов в связи с большим числом таблиц и пр.), в таблицы достаточно интенсивно идут вставки новых данных?
24 сен 09, 12:31    [7702391]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
странно, вообщем..но откуда известна логическая модель данніх (ваша) натянутая на БД ? Обічно дизайн делают исходя и логической модели данніх + реляционая модель БД...а никак не меряют количеством таблиц и записей в них,ИМХО
-------------------------------------
Jedem Das Seine
24 сен 09, 12:35    [7702420]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104760
Несовсем понятно, у вас большое количество таблиц с одинаковой структурой ? Иначе как вы преобразуете их меньшее число таблиц ?
24 сен 09, 12:37    [7702426]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
то Alexander Mamontov
у вас таким образом "секционирование" реализовано что ли?

--------------------------------------------------------------
Дьявол кроется в деталях.
24 сен 09, 12:41    [7702456]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
Глори,
мысли читаете)
примерно так и есть, много таблиц с одинаковой структурой, однотипные запросы вставок и выборок
Дедушка,
что-то наподобие
Maxx,
Вы правы, но при проектировании на слишком большой объем данных не рассчитывалось, вот теперь возник вопрос, насколько корректна такая архитектура?
24 сен 09, 12:50    [7702518]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Да, было подобное.
Например фирма работате с пятью Городами.
На каждый город условно 10 таблиц.
Вот и выбор стоит - либо иметь 50 таблиц, либо 10 + поле - признак города.
Мы полши по следующему пути: Сначала было 10 таблиц. Потом, когда размеры таблиц стали большими - разделили на 50.
Вообще, чем меньше сущностей - тем лучше.
24 сен 09, 13:00    [7702608]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
Александр,
схема та же, только количество таких таблиц может быть на порядок больше
24 сен 09, 13:08    [7702657]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alexander Mamontov
Глори,
мысли читаете)
примерно так и есть, много таблиц с одинаковой структурой, однотипные запросы вставок и выборок
Дедушка,
что-то наподобие

Вы уж определитесь
толи вы данные секционировали
толи доступ к данным разделяли
24 сен 09, 13:29    [7702826]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
Глори,
определяюсь-по поводу доступа проблем нет, ставится вопрос о правильности такой организации данных, о том, насколько трудоемко в обслуживании. Поясню, допустим при зачистке ненужных данных либо будет производиться массовое удаление из нескольких таблиц (несколько сотен тысяч записей-delete), либо будут уже удаляться некоторые таблицы полностью, для первого случая, где их большое количество (drop table).
---
Спасибо за внимание, любую дополнительную информацию спрашивайте
24 сен 09, 13:39    [7702899]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
VladimirMA
Member

Откуда: Новосибирск
Сообщений: 74
есть вопрос

как часто происходят обновления-удаления данных
ну или срок жизни данных
24 сен 09, 13:42    [7702928]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alexander Mamontov
Глори,
определяюсь-по поводу доступа проблем нет, ставится вопрос о правильности такой организации данных, о том, насколько трудоемко в обслуживании.

Вопрос былт вообще то - какую изначально задачу вы решали, создавая такое число однотипных таблиц ?
Была ли это задача секционирования данных, для оптимизации запросов ?
Или это была задача разделения доступа к данным для однотипных систем ?
24 сен 09, 13:45    [7702956]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
Глори,
да, именно задача разделения, секционирования данных с целью оптимизации запросов и уменьшения размеров таблиц
VladimirMA
срок жизни 2-3 месяца, затем зачистка ненужных старых данных, обновление данных не происходит, происходит интенсивное добавление (может до трехсот записей в минуту доходить)
24 сен 09, 13:53    [7703020]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
VladimirMA
Member

Откуда: Новосибирск
Сообщений: 74
а как реализованы запросы в приложении
я к тому что 2-3 тысячи таблиц = 2-3 тысячи разных запросов (если они не параметризованы)
и насколько запросы разные т.е. вопрос об индексе возможно есть смысл использовать кластеризованный

и я понял что с данными так - идет массовая вставка данных не редактируемых в последствии а через 2-3 месяца удаление
24 сен 09, 14:09    [7703138]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
VladimirMA,
запросы все однотипные, редактирование если и производится, то редко и не массово, а единичными записями. Кластерный индекс боюсь создавать, вставки слишком часто, как мне кажется или я не прав? Есть числовое поле numeric, целое на 12 разрядов, выборки идут по нему и по нему сортируется "весь фетч", по нему бы хотелось индексировать
24 сен 09, 20:47    [7705708]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
VladimirMA
Member

Откуда: Новосибирск
Сообщений: 74
Не очень понятно кто и как формирует запросы на получение данных
от этого много зависит т.е. а как данные то пользуют

но если первично вставка тогда кластеризованный пожалуй не подойдет
я за большую таблицу как по теории проектирования
конечно проблемы с индексом такого размера будут очевидно
но можно попытаться вынести его на другую файл группу на каой нить диск отдельный
не забыть про дефрагментацию
гетать данные конечно будет не быстро в отсутствии кластеризованного и присутствии order by
мне кажется
25 сен 09, 11:01    [7707025]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
iljy
Member

Откуда:
Сообщений: 8711
Alexander Mamontov
VladimirMA,
запросы все однотипные, редактирование если и производится, то редко и не массово, а единичными записями. Кластерный индекс боюсь создавать, вставки слишком часто, как мне кажется или я не прав? Есть числовое поле numeric, целое на 12 разрядов, выборки идут по нему и по нему сортируется "весь фетч", по нему бы хотелось индексировать


опять обсуждаем сферическую базу в вакууме... Ну сколько можно?
Что за числовое поле? Естесственный ключ? Или идентити? Можно считать его возрастающим? Хотя бы в пределах одного города, или по какой сущности вы таблицы делаете?
Вставки слишком часто - насколько часто? Есть у этих вставок признак, по которому их можно упорядочить? Дата например, номер проводки и т.п.? Насколько велики записи в таблицах?

10-15 миллионов записей - это не сверхогромные объемы, и по-моему городить псевдосекционирование через отдельные таблицы в данном случае незачем. А если уж городить - так проще N баз одинаковой структуры сделать, поддерживать будет легче. Но по-моему лучше сделать одну, секционировать по городу, индексы тоже от него постройте (я так понял у вас все операции в пределах одного города идут?)
25 сен 09, 11:42    [7707308]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
iljy,
>>опять обсуждаем сферическую базу в вакууме... Ну сколько можно?
постараюсь все пояснить
>>Что за числовое поле? Естесственный ключ? Или идентити? Можно считать его возрастающим?
обычное поле bigint или numeric (целый), не обязательно, но в большинстве случаев возрастающее, order by делается для устранения этого "не обязательно"
>>Хотя бы в пределах одного города, или по какой сущности вы таблицы делаете?
да, а для больших городов даже есть несколько таблиц
>>Вставки слишком часто - насколько часто?
в минуту может быть 40-50, а может и 300-400 (днем, в "прайм-тайм", в редких случаях 500-600)
>>Есть у этих вставок признак, по которому их можно упорядочить? Дата например, номер >>проводки и т.п.? Насколько велики записи в таблицах?
можно по дате или по коду от клиентского приложения, которое произвело вставку.
Записи не велики, это 4 поля float, 7 поля int, 1 bigint или numeric(12, 0), 2 numeric(15, 0) и одна
строка на 15 символов nchar(15)
>>10-15 миллионов записей - это не сверхогромные объемы, и по-моему городить >>псевдосекционирование через отдельные таблицы в данном случае незачем.
возможно, здесь Вы и правы, но боюсь, что 15 миллионов еще не предел
>>А если уж городить - так проще N баз одинаковой структуры сделать, поддерживать будет >>легче. Но по-моему лучше сделать одну, секционировать по городу, индексы тоже от него >>постройте
тоже вариант, но много баз-это и много бекапов, через одну в этом плане просто-ночной бекап и он один
>>я так понял у вас все операции в пределах одного города идут?
Совершенно верно, и все идут для одной из таких таблиц

Vladimir
>>Не очень понятно кто и как формирует запросы на получение данных
>>от этого много зависит т.е. а как данные то пользуют
Клиентское приложение цепляется через ADO и забирает данные, селект делает по полю numeric (или bigint), по которому и хотел индексировать

>>но если первично вставка тогда кластеризованный пожалуй не подойдет
>>я за большую таблицу как по теории проектирования
>>конечно проблемы с индексом такого размера будут очевидно
>>но можно попытаться вынести его на другую файл группу на каой нить диск отдельный
>>не забыть про дефрагментацию
>>гетать данные конечно будет не быстро в отсутствии кластеризованного и присутствии order by
>>мне кажется
подскажите, как индекс отдельно можно хранить, в этой части не силен
25 сен 09, 21:31    [7710601]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
iljy
Member

Откуда:
Сообщений: 8711
Alexander Mamontov

>>Что за числовое поле? Естесственный ключ? Или идентити? Можно считать его возрастающим?
обычное поле bigint или numeric (целый), не обязательно, но в большинстве случаев возрастающее, order by делается для устранения этого "не обязательно"

Возрастающее - имелось ввиду при вставке. Если оно у вас в большинстве случаев возрастает - так это прямой кандидат на кластерный индекс.
Alexander Mamontov

>>Хотя бы в пределах одного города, или по какой сущности вы таблицы делаете?
да, а для больших городов даже есть несколько таблиц

в смысле информация по одному городу разбивается на несколько таблиц? И каждая часть обрабатывается независимо? Сделайте дополнительный числовой параметр - номер секции, и таблицу-справочник, какая секция в какой город проецируется. Можно проецировать и несколько секций в один город естествено.
Alexander Mamontov

>>Вставки слишком часто - насколько часто?
в минуту может быть 40-50, а может и 300-400 (днем, в "прайм-тайм", в редких случаях 500-600)
>>Есть у этих вставок признак, по которому их можно упорядочить? Дата например, номер >>проводки и т.п.? Насколько велики записи в таблицах?
можно по дате или по коду от клиентского приложения, которое произвело вставку.
Записи не велики, это 4 поля float, 7 поля int, 1 bigint или numeric(12, 0), 2 numeric(15, 0) и одна
строка на 15 символов nchar(15)
>>10-15 миллионов записей - это не сверхогромные объемы, и по-моему городить >>псевдосекционирование через отдельные таблицы в данном случае незачем.
возможно, здесь Вы и правы, но боюсь, что 15 миллионов еще не предел

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

Alexander Mamontov

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

Если у вас работа с этими данными ведется одинаково и так оно и пребудет во веки вечные - делайте секции и не мучайтесь. Разные таблицы-базы могут иметь смысл когда данные по разному могут обрабатываться, но допускать такого ни в коем случае нельзя, потому что поддержка 2-3 похожих наборов таблиц становиться напряжной, а 10 превращается в кошмар.
Alexander Mamontov

Vladimir
>>Не очень понятно кто и как формирует запросы на получение данных
>>от этого много зависит т.е. а как данные то пользуют
Клиентское приложение цепляется через ADO и забирает данные, селект делает по полю numeric (или bigint), по которому и хотел индексировать

>>но если первично вставка тогда кластеризованный пожалуй не подойдет
>>я за большую таблицу как по теории проектирования
>>конечно проблемы с индексом такого размера будут очевидно
>>но можно попытаться вынести его на другую файл группу на каой нить диск отдельный
>>не забыть про дефрагментацию
>>гетать данные конечно будет не быстро в отсутствии кластеризованного и присутствии order by
>>мне кажется
подскажите, как индекс отдельно можно хранить, в этой части не силен

Для индекса задается файлгруппа. При секционировании можно задать разные файлгруппы для каждой секции.
25 сен 09, 22:18    [7710667]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Тоже вопрос про секционирование:
Что лучще:
1. Сделать одну таблицу с 60-ю секциями (каждая секция это 1 месяц, таблица расчитана на 5 лет). Приблизительный размер таблицы 20 миллиардов записей.
2. Сделать 5 секционных таблиц(на каждый год) разбитых по месяцам. Но в этом случае будет сложно делать запросы.

В таблицу каждые 5 сек поступают данные от 600 объектов.
Таким образом, получаеться у нас за день 10.368.000 записей. За месяц 311.040.000, а за год 3.732.480.000
26 сен 09, 10:17    [7711124]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36799
LexMinsk
Тоже вопрос про секционирование:
Что лучще:
1. Сделать одну таблицу с 60-ю секциями (каждая секция это 1 месяц, таблица расчитана на 5 лет). Приблизительный размер таблицы 20 миллиардов записей.
2. Сделать 5 секционных таблиц(на каждый год) разбитых по месяцам. Но в этом случае будет сложно делать запросы.

В таблицу каждые 5 сек поступают данные от 600 объектов.
Таким образом, получаеться у нас за день 10.368.000 записей. За месяц 311.040.000, а за год 3.732.480.000
Лучше для чего?
26 сен 09, 19:53    [7711846]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Лучше для работы с ними. Я с этой таблицы потом планирую брать данные для отчетов.
26 сен 09, 20:30    [7711895]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
iljy
>>Возрастающее - имелось ввиду при вставке. Если оно у вас в большинстве случаев >>возрастает - так это прямой кандидат на кластерный индекс
Насколько я знаю, кластерный индекс тяжелее перестраивается, а вставок, как я указывал ранее, довольно много, не будет сильно тормозить? В моем случае очень желательно, чтобы вставка была довольно быстрой. Покритикуйте, если не прав. Все остальное прочел, спасибо Вам за советы
26 сен 09, 20:57    [7711944]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
iljy
Member

Откуда:
Сообщений: 8711
Alexander Mamontov
iljy
>>Возрастающее - имелось ввиду при вставке. Если оно у вас в большинстве случаев >>возрастает - так это прямой кандидат на кластерный индекс
Насколько я знаю, кластерный индекс тяжелее перестраивается, а вставок, как я указывал ранее, довольно много, не будет сильно тормозить? В моем случае очень желательно, чтобы вставка была довольно быстрой. Покритикуйте, если не прав. Все остальное прочел, спасибо Вам за советы


если у вас кластерный индекс по возрастающему значению - откуда возьмуться перестроения? Он будет только расширяться вправо. При этом можно поставить отличный от 100 fillfactor, на случай вашего "не обязательно". То же самое справедливо для каждой отдельной секции.
27 сен 09, 01:03    [7712294]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация структуры базы данных  [new]
Alexander Mamontov
Member

Откуда: НСО
Сообщений: 85
Прошу прошения, что снова поднимаю тему... Обнаружилось два сервера, где примерно так организовано секционирование. Если для таким образом организованного секционирования создать по индексу на каждую таблицу-"секцию", то будет большое количество индексов, могут ли быть проблемы с их обслуживанием, обновлением статистики и пр., на сколько SQL Server позволит автоматизировать процесс обслуживания?
30 сен 09, 10:46    [7723383]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить