(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Lightweight Tables или практические советы при проектировании БД…

Источник: habrahabr
AlanDenton

В данном топике хотелось бы поговорить о повышении производительности при работе с таблицами.

Тема не нова, но становится особенно актуальной, когда в базе наблюдается постоянный рост данных - таблицы становятся большими, а поиск и выборка по ним - медленной.

Как правило, это происходит из-за плохо спроектированной схемы - изначально не рассчитанной на оперирование большими объемами данных.

Чтобы рост данных в таблицах не приводил к падению производительности при работе с ними, рекомендуется взять на вооружение несколько правил при проектировании схемы.

Первое и, наверное, самое важное. Типы данных в таблицах должны иметь минимальную избыточность.

Все данные, которыми оперирует SQL Server, хранятся на, так называемых, страницах, которые имеют фиксированный размер в 8 Кб. При записи и чтении сервер оперирует именно страницами, а не отдельными строками.

Поэтому, чем более компактные типы данных используются в таблице, тем меньше страниц требуется для их хранения. Меньшее число страниц - меньшее количество дисковых операций.

Кроме очевидного снижения нагрузки на дисковую подсистему - в данном случае есть еще одно преимущество - при чтении с диска, любая страница вначале помещается в специальную область памяти ( Buffer Pool ), а потом уже используется по прямому назначению - для считывания или изменения данных.

При использовании компактных типов данных, в Buffer Pool можно поместить больше данных на том же количестве страниц - за счет этого мы не тратим впустую оперативную память и сокращаем количество логических операций.

Теперь рассмотрим небольшой пример - таблицу, в которой хранится информация о рабочих днях каждого сотрудника.

CREATE TABLE dbo.WorkOut1 ( DateOut DATETIME , EmployeeID BIGINT , WorkShiftCD NVARCHAR(10) , WorkHours DECIMAL(24,2) , CONSTRAINT PK_WorkOut1 PRIMARY KEY (DateOut, EmployeeID) )
Правильно ли выбраны типы данных в этой таблице? По-видимому - нет.

Например, очень сомнительно что сотрудников на предприятии насколько много (2^63-1), что для покрытия такой ситуации был выбран тип данных BIGINT .

Уберем избыточность и посмотрим, будет ли запрос из такой таблицы более быстрым?

CREATE TABLE dbo.WorkOut2 ( DateOut SMALLDATETIME , EmployeeID INT , WorkShiftCD VARCHAR(10) , WorkHours DECIMAL(8,2) , CONSTRAINT PK_WorkOut2 PRIMARY KEY (DateOut, EmployeeID) )
На плане выполнения можно увидеть разницу в стоимости, которое зависит от среднего размера строки и ожидаемого количество строк, которое вернет запрос:

Весьма логично, что чем меньший объем данных требуется прочитать - тем быстрее будет выполняться сам запрос:

(3492294 row(s) affected)

SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 33606 ms.

(3492294 row(s) affected)

SQL Server Execution Times:
CPU time = 1420 ms, elapsed time = 29694 ms.

Как Вы видите, использование менее избыточных типов данных зачастую положительно сказывается на производительности запросов и позволяет существенно снизить размер проблемных таблиц.

К слову, узнать размер таблицы можно посредством следующего запроса:

SELECT table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name , data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4)) FROM sys.objects o JOIN ( SELECT p.[object_id] , total_rows = SUM(p.[rows]) , total_pages = SUM(a.total_pages) , usedpages = SUM(a.used_pages) , pages = SUM( CASE WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0 WHEN a.[type] != 1 AND p.index_id < 2 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END ) FROM sys.partitions p JOIN sys.allocation_units a ON p.[partition_id] = a.container_id LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id] GROUP BY p.[object_id] ) do ON o.[object_id] = do.[object_id] WHERE o.[type] = 'U'
Для рассматриваемых таблиц, запрос вернет следующие результаты:

table_name data_size_mb -------------------- ------------------------------- dbo.WorkOut1 167.2578 dbo.WorkOut2 97.1250
Правило второе. Избегайте дублирования и применяйте нормализацию данных.

Собственно, недавно я анализировал базу данных одного бесплатного веб-сервиса для форматирования T-SQL кода. Серверная часть там очень простая и состояла из одной единственной таблицы:

CREATE TABLE dbo.format_history ( session_id BIGINT , format_date DATETIME , format_options XML )
Каждый раз при форматировании сохранялся id текущей сессии, системное время сервера и настройки, с которыми пользователь отформатировал свой SQL код. Затем полученные данные использовались для выявления наиболее популярных стилей форматирования.

С ростом популярности сервиса, количество строк в таблице увеличилось, а обработка профилей форматирования занимала все большее количество времени. Причина заключалась в архитектуре сервиса - при каждой вставке в таблицу сохранялся полный набор настроек.

Настройки имели следующую XML структуру:

<FormatProfile> <FormatOptions> <PropertyValue Name="Select_SelectList_IndentColumnList">true</PropertyValue> <PropertyValue Name="Select_SelectList_SingleLineColumns">false</PropertyValue> <PropertyValue Name="Select_SelectList_StackColumns">true</PropertyValue> <PropertyValue Name="Select_SelectList_StackColumnsMode">1</PropertyValue> <PropertyValue Name="Select_Into_LineBreakBeforeInto">true</PropertyValue> ... <PropertyValue Name="UnionExceptIntersect_LineBreakBeforeUnion">true</PropertyValue> <PropertyValue Name="UnionExceptIntersect_LineBreakAfterUnion">true</PropertyValue> <PropertyValue Name="UnionExceptIntersect_IndentKeyword">true</PropertyValue> <PropertyValue Name="UnionExceptIntersect_IndentSubquery">false</PropertyValue> ... </FormatOptions> </FormatProfile>
Всего 450 опций форматирования - каждая такая строка в таблице занимала примерно 33Кб. А ежедневный прирост данных составлял более 100Мб. С каждым днем база разрасталась, а делать аналитику по ней становилось делать дольше.

Исправить ситуацию оказалось просто - все уникальные профили были вынесены в отдельную таблицу, где для каждого набора опций был получен хеш. Начиная с SQL Server 2008 для этого можно использовать функцию sys.fn_repl_hash_binary .

В результате схема была нормализирована:

CREATE TABLE dbo.format_profile ( format_hash BINARY(16) PRIMARY KEY , format_profile XML NOT NULL ) CREATE TABLE dbo.format_history ( session_id BIGINT , format_date SMALLDATETIME , format_hash BINARY(16) NOT NULL , CONSTRAINT PK_format_history PRIMARY KEY CLUSTERED (session_id, format_date) )
И если запрос на вычитку раньше был таким:

SELECT fh.session_id, fh.format_date, fh.format_options FROM SQLF.dbo.format_history fh
То на получение тех же данных в новой схеме потребовалось сделать JOIN:

SELECT fh.session_id, fh.format_date, fp.format_profile FROM SQLF_v2.dbo.format_history fh JOIN SQLF_v2.dbo.format_profile fp ON fh.format_hash = fp.format_hash
Если сравнить время выполнения запросов, то мы не увидим явного преимущества от изменения схемы.

(3090 row(s) affected)

SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 4698 ms.

(3090 row(s) affected)

SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 4479 ms.

Но цель в данном случае преследовалась другая - ускорить аналитику. И если раньше приходилось писать очень мудреный запрос для получения списка самых популярных профилей форматирования:

;WITH cte AS ( SELECT fh.format_options , hsh = sys.fn_repl_hash_binary(CAST(fh.format_options AS VARBINARY(MAX))) , rn = ROW_NUMBER() OVER (ORDER BY 1/0) FROM SQLF.dbo.format_history fh ) SELECT c2.format_options, c1.cnt FROM ( SELECT TOP (10) hsh, rn = MIN(rn), cnt = COUNT(1) FROM cte GROUP BY hsh ORDER BY cnt DESC ) c1 JOIN cte c2 ON c1.rn = c2.rn ORDER BY c1.cnt DESC
То за счет нормализации данных стало возможным существенно упростить не только сам запрос:

SELECT fp.format_profile , t.cnt FROM ( SELECT TOP (10) fh.format_hash , cnt = COUNT(1) FROM SQLF_v2.dbo.format_history fh GROUP BY fh.format_hash ORDER BY cnt DESC ) t JOIN SQLF_v2.dbo.format_profile fp ON t.format_hash = fp.format_hash
Но и сократить время его выполнения:

(10 row(s) affected)

SQL Server Execution Times:
CPU time = 2684 ms, elapsed time = 2774 ms.

(10 row(s) affected)

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 379 ms.

Приятным дополнением также стало и снижение размера база данных на диске:

database_name row_size_mb ---------------- --------------- SQLF 123.50 SQLF_v2 7.88
Вернуть размер файла данных для базы можно следующим запросом:

SELECT database_name = DB_NAME(database_id) , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WHERE database_id IN (DB_ID('SQLF'), DB_ID('SQLF_v2')) GROUP BY database_id
Надеюсь, на этом примере, мне удалось показать важность нормализации данных и минимизации избыточности в базе.

Третье. Осторожно выбирайте столбцы, входящие в индексы.

Индексы позволяют существенно ускорить выборку из таблицы. Также как и данные из таблиц, индексы хранятся на страницах. Соотвественно. чем меньше страниц требуется для хранения индекса - тем быстрее по нему можно провести поиск.

Очень важно правильно выбрать поля, которые будут входить в кластерный индекс. Поскольку все столбцы кластерного индекса автоматически входят в каждый некластерный - за счет этого может существенно увеличиваться размер базы данных.

Четвертое. Используйте промежуточные и консолидированные таблицы.

Здесь все достаточно просто - зачем каждый раз делать сложный запрос из большой таблицы, если есть возможность сделать простой запрос из маленькой.

Например, в наличии есть запрос по консолидации данных:

SELECT WorkOutID , CE = SUM(CASE WHEN WorkKeyCD = 'CE' THEN Value END) , DE = SUM(CASE WHEN WorkKeyCD = 'DE' THEN Value END) , RE = SUM(CASE WHEN WorkKeyCD = 'RE' THEN Value END) , FD = SUM(CASE WHEN WorkKeyCD = 'FD' THEN Value END) , TR = SUM(CASE WHEN WorkKeyCD = 'TR' THEN Value END) , FF = SUM(CASE WHEN WorkKeyCD = 'FF' THEN Value END) , PF = SUM(CASE WHEN WorkKeyCD = 'PF' THEN Value END) , QW = SUM(CASE WHEN WorkKeyCD = 'QW' THEN Value END) , FH = SUM(CASE WHEN WorkKeyCD = 'FH' THEN Value END) , UH = SUM(CASE WHEN WorkKeyCD = 'UH' THEN Value END) , NU = SUM(CASE WHEN WorkKeyCD = 'NU' THEN Value END) , CS = SUM(CASE WHEN WorkKeyCD = 'CS' THEN Value END) FROM dbo.WorkOutFactor WHERE Value > 0 GROUP BY WorkOutID
Если данные в таблице изменяются не слишком часто, можно создать отдельную таблицу:

SELECT * FROM dbo.WorkOutFactorCache
И не удивительно, что чтение из консолидированной таблицы будет проходить быстрее:

(185916 row(s) affected)

SQL Server Execution Times:
CPU time = 3448 ms, elapsed time = 3116 ms.

(185916 row(s) affected)

SQL Server Execution Times:
CPU time = 1410 ms, elapsed time = 1202 ms.

Пятое. В каждом правиле есть свои исключения.

Я показал пару примеров, когда изменение типов данных на менее избытоные позволяет сократить время выполнения запроса. Но это бывает не всегда.

Например, у типа данных BIT есть одна особенность - SQL Server оптимизирует хранение группы столбцов этакого типа на диске. Например, если в таблице имеется 8 или меньше столбцов типа BIT , они хранятся на странице как 1 байт, если до 16 столбцов типа BIT , они хранятся как 2 байта и т.д.

Хорошая новость - таблица будет занимать существенно меньше места и сократит количество дисковых операций.

Плохая новость - при выборке данных этого типа будет происходить неявное декодирование, которое очень требовательно к ресурсам процессора.

Покажу это на примере. Есть три идентичные таблицы, которые содержат информацию о календарном графике сотрудников (31 + 2 PK столбца). Все они отличаются только типом данных для консолидированных значений (1 - вышел на работу, 0 - отсутствовал):

SELECT * FROM dbo.E_51_INT SELECT * FROM dbo.E_52_TINYINT SELECT * FROM dbo.E_53_BIT
При использовании менее избыточных данных размер таблицы заметно уменьшился (особенно последняя таблица):

table_name data_size_mb -------------------- -------------- dbo.E31_INT 150.2578 dbo.E32_TINYINT 50.4141 dbo.E33_BIT 24.1953
Но существенного выигрыша в скорости выполнения от использования типа BIT мы не получим:

(1000000 row(s) affected) Table 'E31_INT'. Scan count 1, logical reads 19296, physical reads 1, read-ahead reads 19260, ... SQL Server Execution Times: CPU time = 1607 ms, elapsed time = 19962 ms. (1000000 row(s) affected) Table 'E32_TINYINT'. Scan count 1, logical reads 6471, physical reads 1, read-ahead reads 6477, ... SQL Server Execution Times: CPU time = 1029 ms, elapsed time = 16533 ms. (1000000 row(s) affected) Table 'E33_BIT'. Scan count 1, logical reads 3109, physical reads 1, read-ahead reads 3096, ... SQL Server Execution Times: CPU time = 1820 ms, elapsed time = 17121 ms.
Хотя план выполнения будет говорить об обратном:

В результате наблюдений было замечено, что негативный эффект от декодирования не будет проявятся если таблица содержит не более 8 BIT столбцов.

Попутно стоит отметить, что в метаданных SQL Server тип данных BIT используется очень редко - чаще применяют тип BINARY и вручную делают сдвиг для получения того или иного значения.

И последнее о чем нужно упомянуть. Удаляйте ненужные данные.

Собственно, зачем это делать?

При выборке данных, SQL Server поддерживает механизм оптимизации производительности, называемый упреждающим чтением, который пытается предугадать, какие именно страницы данных и индексов понадобятся для выполнения запроса, и помещает эти страницы в буферный кэш, прежде чем в них возникнет реальная необходимость.

Соответственно, если таблица содержит много лишних данных - это может привести к ненужным дисковым операциям.

Кроме того, удаление ненужных данных позволяет сократить количество логических операций при чтении данных из Buffer Pool - поиск и выборка данных будет проходить по меньшему объёму данных.

В заключение, что могу еще добавить - внимательно выбирайте типы данных для столбцов в Ваших таблицах и старайтесь учитывать будущие нагрузки на базу данных.

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft »
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 27.02.2014 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft SQL Server Standard Edition 2017 Sngl OLP 1License NoLevel
Microsoft SQL Server Standard Core 2017 Sngl OLP 2Licenses NoLevel CoreLic Qualified
Microsoft Visual Studio Professional w/MSDN AllLng Software Assurance OLP 1 License No Level Qualified
Microsoft Visual Studio Professional 2017 Sngl OLP 1License NoLevel
Microsoft Office 365 Бизнес премиум. Подписка на 1 рабочее место на 1 год
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
CASE-технологии
Программирование на Microsoft Access
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
3D и виртуальная реальность. Все о Macromedia Flash MX.
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Проблемы с интернетом в Windows 7 (2)
Здравствуйте, подписан на вашу рассылку - весьма полезная вещь. Если не сложно чиркните пару...
 
Помощь по MS Access (330)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Как извлечь рисунки из файла Word (44)
Вообще-то есть еще способ - сделать в Word-е Copy рисунка, открыть Microsoft Photo Editor и там:...
 
Не активен пункт меню Tools->References (3)
В редакторе Microsoft Visual Basic не активен пункт меню Tools->References. С чем это может быть...
 
Отличается ли ДрифтКазино от беттинга? (3)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
 
 



    
rambler's top100 Rambler's Top100