Узнать размер колонки
Для того, чтобы узнать размер всей бд, или, например, одной таблицы, у нас есть команда ‘sp_spaceused’. Но если пойти дальше, то возникает вопрос: а как получить статистику по полям? Как узнать, в каком поле (колонке) в таблице содержатся самые тяжёлые данные, а в каком самые легкие? Вопрос не столько теоретический, сколько практический: есть огромная база, которую надо проанализировать (постепенно погружаюсь в Data-Mining).
Отслеживать
задан 29 авг 2016 в 23:18
Raman Sinclair Raman Sinclair
229 2 2 серебряных знака 9 9 бронзовых знаков
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Используйте функцию DATALENGTH()
В следующем примере находится длина столбца ProductName в таблице MyOrderTable.
SELECT DATALENGTH(ProductName) as [SizeInBytes] FROM MyOrderTable Отслеживать
ответ дан 30 авг 2016 в 2:37
4,599 2 2 золотых знака 15 15 серебряных знаков 29 29 бронзовых знаков
Как узнать, в каком поле (колонке) в таблице содержатся самые тяжёлые данные, а в каком самые легкие?
Вас интересует размер в байтах, занимаемый тем или иным столбцом на диске (видимо так, раз вы упомянули sp_spaceused )?
Не уверен, что возможно определить его точно. Точно можно узнать, сколько страниц (блоков по 8Кб, которыми SqlServer хранит данные) занимают данные всей таблицы (или индекса).
Месту на диске, отведенному под хранение конкретного столбца, по-видимому можно дать лишь некоторую оценку (которая, впрочем, не всегда будет адекватной). И с datalength не совсем всё просто. Далее несколько подробнее.
/*1*/ select sum(datalength([Column])) from [Table]; за основу оценки.
Во-первых. Кроме собственно данных столбца всегда есть дополнительная служебная информация, которая может иметь отношение к столбцу, но её объем между столбцами логически может делиться непропорционально их количеству в таблице (например данные заголовка строки). Т.е. оценку размера /*1*/ следует воспринимать как «не менее чем».
Чем меньше в таблице столбцов, и чем короче запись, тем больше издержки на служебные данные, тем, соответственно, дальше оценка /*1*/ от реальности. Так, для таблицы с одним коротким столбцом полный размер данных (с учётом служебной информации) может значительно превосходить «логический» размер данных самого столбца. Сравните, например, для таблицы
create table tiny_nums (value tinyint); insert into tiny_nums (value) select top (1048576) 1 from master.sys.all_columns a cross join master.sys.all_columns b; результат, возвращаемый запросом /*1*/ с тем, что покажет sp_spaceused .
Во-вторых. Значение, возвращаемое datalength не всегда соответствует действительности. В частности, если datalength([Column]) возвращает NULL , то физически это может быть вовсе не ноль.
Дело в том, что типы столбцов делятся на fixed-length (напр. int , char(20) , datetime2(0) , uniqueidentifier , и т.п.) и variable-length (напр. varbinary(64) , nvarchar(30) и т.п.). И если для variable-length оценка /*1*/ приблизительно справедлива, то для fixed-length столбцов резервируется место для хранения значения, даже если само значение NULL .
Т.е. для fixed-length столбцов оценку /*1*/ следует скорректировать, используя вместо NULL (если они возможны) какое-либо непустое значение, соответствующее типу столбца (например 0 для int ):
/*2*/ select sum(datalength(isnull([IntColumn], 0))) from [Table]; Также нужно учитывать, что для столбцов типа bit возвращаемое datalength значение равно 1. Однако если в таблице (или индексе) несколько bit столбцов, то SqlServer объединяет их по 8 в 1 байт.
Также столбцы могут быть sparse , что означает 0 байт на хранение NULL (даже для fixed-length), но плюс 4 дополнительных байта на хранение значения, если оно не NULL :
/*3*/ select sum(datalength([SparseColumn]) + 4) from [TableName]; В-третьих. Если столбец не просто присутствует в таблице, а ещё и участвует в индексах, то он «утяжеляется» кратно количеству индексов, в которых он участвует. Если столбец является ключевым в кластерном индексе, то нужно прибавить оценку кратную количеству всех некластерных индексов (т.к. в leaf-level страницах некластерных индексов содержатся значения ключей кластерного индекса). Так в таблице
create table SomeTable ( PK_ID bigint primary key, FK_1_ID int, FK_2_ID int, . FK_10_ID int, UID uniqueidentifier ); create index IX_1 on SomeTable (FK_1_ID); . create index IX_10 on SomeTable (FK_10_ID); самым «тяжёлым» скорее всего окажется вовсе не UID столбец, а PK_ID , т.к. (помимо участия в кластерном первичном ключе) значения PK_ID будут присутствовать ещё в 10-ти некластерных индексах.
Следует учесть также, что если некластерный индекс является фильтрованным индексом, то соответствующую оценку ( /*1*/ , /*2*/ или /*3*/ ) нужно взять не по всей таблице, а по строкам, соответствующим фильтру такого индекса.
В-четвертых (относится к Enterprise edition). Если применяется сжатие строк или страниц таблицы
alter table [TableName] rebuild partition = all with (data_compression = row); alter index [IX_Name] on [TableName] rebuild with (data_compression = page); то оценки с помощью datalength перестают быть адекватными и фактор «не менее чем» перестаёт работать.
Сравните для таблиц
create table strings (value char(2000)); insert into strings (value) select top (10000) replicate('a', 2000) from master.sys.all_columns a cross join master.sys.all_columns b; create table strings_page (value char(2000)) with (data_compression = page); insert into strings_page with (tablock) (value) select top (10000) replicate('a', 2000) from master.sys.all_columns a cross join master.sys.all_columns b; значения оценки размера столбца с помощью datalength c тем, что покажет sp_spaceused . Для первой таблицы «показания» datalength и sp_spaceused будут близки (т.к. строка таблицы «широкая» и объем служебной информации сказывается мало), а для второй будут расходиться очень сильно.
В-пятых. Всё что было сказано до этого момента справедливо для SqlServer 2008. В более поздних версиях появились COLUMNSTORE индексы, которые, из-за особенностей своего устройства, могут хранить данные в существенно сжатом виде. Для них оценка размера столбца с помощью datalength также может давать неадекватный результат. Если для таблицы
create table strings_col (value char(2000)); insert into strings_col (value) select top (10000) replicate('a', 2000) from master.sys.all_columns a cross join master.sys.all_columns b; create clustered columnstore index ix_clcs on strings_col; сравнить показания sp_spaceused с datalength , то опять можно наблюдать сильное расхождение.
Полагаю, что данный список факторов, которые следует учитывать при оценке места, занимаемого тем или иным столбцом, не исчерпывающий.
Функция LENGTH
Функция LENGTH используется для подсчета количества символов в строках.
Синтаксис
SELECT LENGTH(поле) FROM имя_таблицы WHERE условие
Таблицы для примеров
| id айди |
name имя |
|---|---|
| 1 | Дмитрий |
| 2 | user6 |
| 3 | Владимир |
Пример
В данном примере при выборке из таблицы создается дополнительное поле, которое содержит длину поля name:
SELECT id, name, LENGTH(name) as length FROM users
Результат выполнения кода:
| id айди |
name имя |
length длина строки |
|---|---|---|
| 1 | Дмитрий | 4 |
| 2 | user6 | 4 |
| 3 | Владимир | 4 |
Пример
В данном примере с помощью условия WHERE выбираются только те записи, в которых длина поля name больше или равна 7 :
SELECT *, LENGTH(name) as length FROM users WHERE LENGTH(name)>=7
Результат выполнения кода:
| id айди |
name имя |
length длина строки |
|---|---|---|
| 1 | Дмитрий | 7 |
| 3 | Владимир | 8 |
Пример
Конечно, не обязательно делать поле length, чтобы применить функцию LENGTH в условии:
SELECT * FROM users WHERE LENGTH(name)>=7
Результат выполнения кода:
| id айди |
name имя |
|---|---|
| 1 | Дмитрий |
| 3 | Владимир |
Как узнать длину поля таблицы sql
Финансовая грамотность от А до Я
Данный курс в кратчайшие сроки обучит Вас финансовой грамотности. Эти знания позволят Вам получить, как минимум, серьёзную финансовую стабильность, а, как максимум, финансовую независимость.
В рамках курса даётся большое количество практических заданий, благодаря которым Вы не просто посмотрите курс, а действительно выполните ряд важнейших шагов, о которых в нём говорится.
Помимо самого курса Вас ждёт ещё бесплатный ценный Бонус: «Ранний выход на пенсию». В рамках этого Бонуса Вы узнаете, какой должен быть капитал конкретно в Вашем случае, варианты его получения, а также какие есть стратегии вывода капитала на пенсии и где их можно протестировать.
Уроки и статьи
Подпишитесь на мой канал на YouTube, где я регулярно публикую новые видео.
Подписаться
Подписавшись по E-mail, Вы будете получать уведомления о новых статьях.
Подписаться
Добавляйтесь ко мне в друзья ВКонтакте! Отзывы о сайте и обо мне оставляйте в моей группе.
Мой аккаунт Моя группа
Какая тема Вас интересует больше?
Бесплатный курс
33 шага к финансовой независимости
Данный курс представляет из себя чек-лист в PDF-формате с 33-мя последовательными шагами, пройдя которые Вы станете финансово независимым человеком. К этому чек-листу так же прилагается подробная видеоинструкция.
Чтобы получить Видеокурс,
заполните форму
Бесплатный онлайн-семинар
Как создать профессиональный Интернет-магазин
После семинара:
— Вы будете знать, как создать Интернет-магазин.
— Вы получите бесплатный подарок с подробным описанием каждого шага.
— Вы сможете уже приступить к созданию Интернет-магазина.
Настраиваем запрос в MySQL: максимальная длина поля и данные
Для определения максимального размера строки в столбце применяйте функцию MAX(LENGTH(column_name)) :
Скопировать код
SELECT MAX(LENGTH(column_name)) AS max_length FROM your_table;Данная команда позволяет определить максимальный размер поля в столбце column_name таблицы your_table .
Получение самой большой по размеру записи вместе с её размером
Если вам необходимо не только узнать максимальный размер строки, но и саму строку в таблице, используйте следующий запрос:
Скопировать код
SELECT *, LENGTH(column_name) AS length FROM your_table ORDER BY length DESC LIMIT 1;Таким образом, вы получите возможность изучить самую большую по размеру строку данных вместе с ее размером.
Вложенные запросы на помощь
С более сложными ситуациями или при работе с несколькими таблицами вам могут помочь подзапросы:
Скопировать код
SELECT column_name FROM your_table WHERE LENGTH(column_name) = (SELECT MAX(LENGTH(column_name)) FROM your_table);Важность кодировки символов
Важно отличать байты от символов. При работе с многобайтовыми кодировками используйте CHAR_LENGTH() вместо LENGTH() :
Скопировать код
SELECT CHAR_LENGTH(column_name) FROM your_table;Повышаем точность с помощью оператора HAVING
Оператор HAVING применяется совместно с GROUP BY для точнее отбора данных:
Скопировать код
SELECT customer_id, MAX(CHAR_LENGTH(email)) FROM customers GROUP BY customer_id HAVING CHAR_LENGTH(email) = MAX(CHAR_LENGTH(email));Визуализация
Представьте таблицу как набор игрушечных поездов ( �� ), где каждая строка — это поезд определенной длины:
Скопировать код
| Игрушечные поезда | Длина | | ----------------- | ------ | | �� | 4 см | | ���� | 8 см | | ������ | 12 см |Вашей задачей является найти самый длинный поезд:
Скопировать код
SELECT train, LENGTH(train) AS length FROM playroom ORDER BY length DESC LIMIT 1; // �������� (12 см – это максимальная длина)Теперь вы умеете находить самого длинного из игрушечных поездов. ������
Предостережения, замечания и лучшие практики
Недостатки MAX(LENGTH())
Использование MAX(LENGTH()) на больших базах данных может ухудшить производительность. Возможно сохранение ресурсов, если хранить длину в отдельной колонке.