SQL-Ex blog
При построении индексов для ваших запросов порядок столбцов в ключе индекса имеет значение. SQL Server наиболее эффективно использует индекс, если данные в этом индексе сохраняются в том же порядке, какой требуется для вашего запроса при выполнении соединения (join), фильтрации (where), группировки (group by) или сортировки (order by).
Однако если ваш запрос требует нескольких ключевых столбцов из-за множества предикатов (например, WHERE Color = ‘Red’ AND Size = ‘Medium’), какой порядок столбцов следует задать при определении ключевых столбцов в индексе?
Кардинальное число
В SQL Server кардинальное число (cardinality) означает число различных элементов в столбце. Оставив в стороне все другие соображения, скажем, что при определении ключевых столбцов для вашего индекса, столбец с наибольшим кардинальным числом, или с наибольшим числом различных значений, должен идти первым.
Чтобы понять почему так, давайте вернемся к нашему примеру с цветом и размером. Если у нас есть таблица с данными, представляющими цвета и размеры различных птиц, это может выглядеть примерно так:
Если подсчитать число различных значений в каждом из столбцов Color и Size, то обнаружим, что имеется 20 различных цветов, но только 5 различных размеров:
SELECT
COUNT(DISTINCT Color) AS DistinctColors,
COUNT(DISTINCT Size) AS DistinctSizes
FROM
dbo.Birds
(Для простоты в этом примере данные в таблице распределены совершенно равномерно по всем 20 цветам и 5 размерам — т.е. каждый цвет представлен по одному каждым из пяти размеров, что в сумме дает 100 строк.)
Если мы поставим Size первым столбцом в индексном ключе, SQL Server мог бы сразу сократить число строк, среди которых он должен выполнить поиск, чтобы удовлетворить нашему предикату (WHERE Color = ‘Red’ and Size = ‘Medium’), до 20 строк — после чего мы можем отсеять все строки, размер которых не равен Medium:
Однако если вместо этого мы сделаем Color первым столбцом, то можем сразу отбросить 95% строк в нашем наборе данных — оставив только 5 строк со значением ‘Red‘, по одной строке на каждый из 5 размеров (помним, что данные распределены равномерно):
В большинстве сценариев размещение столбца с наивысшим кардинальным числом первым позволит SQL Server отфильтровать наибольшую часть данных, как неподходящих, сфокусировав внимание на меньшем подмножестве строк, которые еще требуется сравнить.
Хотя существуют обстоятельства, когда вы можете нарушить это общее правило, например, когда вы пытаетесь оптимизировать использование индекса под множество запросов; иногда может иметь смысл не помещать столбцы в порядке, определяемом максимальностью значений кардинального числа, если это приводит к тому, что большее количество запросов смогут использовать единственный индекс.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Что такое Cardinality и зачем оно нужно?
Услышал, что в таблице слетело кардиналити, но так и не понял, что это такое и за что оно отвечает. Можно простыми словами объяснить, для чего это надо и какое влияние имеет для таблиц mysql, и как его починить, если оно слетело?
- Вопрос задан более трёх лет назад
- 13651 просмотр
Комментировать
Решения вопроса 1
Игорь Воротнёв @HeadOnFire
PHP, Laravel & WordPress Evangelist
- gender — высокоповторяющиеся данные, низкая кардинальность
- city — данные повторяются, но уже не так часто — нормальная кардинальность
- phone — данные уникальны, высокая кардинальность
Как кардинальность может слететь — впервые слышу. Это характеристика данных по их уникальности, а не индекс какой-нибудь. Она не слетает.
Впрочем, иногда под кардинальностью (скорее ошибочно) подразумевают кардинальные отношения между таблицами (1:1, 1:n, n:n). В данном случае тогда речь может идти о слетевших связях (foreign keys слетевшие в результате некорректного импорта с переопределением autoincrements).
Ответ написан более трёх лет назад
Нравится 4 2 комментария
Twitt @Twitt Автор вопроса
Спасибо. Нет, речь шла конкретно про кардиналити, последний абзац — не оно.
Если в таблице 100 записей, то у phone должно быть кардиналити 100? или оно считается иначе как-то?
Игорь Воротнёв @HeadOnFire
Twitt, да, все верно. Вот пример по одной из таблиц первой попавшейся БД:
Кардинальность колонки meta_id равна количеству записей в этой таблице. Но для характеризации степени уникальности данных в целом обычно используется темринология low / normal / high cardinality, этого достаточно чтобы понимать ситуацию. Конкретные цифры смотрите по конкретному столбцу конкретной таблицы и действуете исходя из этих данных (например, думаете как лучше нормализовать схему).
Реляционная модель данных: теоретические основы
Реляционная модель данных: кем, когда и для чего создана
Реляционная модель данных — созданная Эдгаром Коддом логическая модель данных, описывающая:
- структуры данных в виде (изменяющихся во времени) наборов отношений;
- теоретико-множественные операции над данными: объединение, пересечение разность и декартово произведение;
- специальные реляционные операции: селекция, проекция, соединение и деление;
- специальные правила, обеспечивающие целостность данных.
Эдгар Франк «Тед» Кодд — (23 августа 1923 —18 апреля 2003) — британский учёный, работы которого заложили основы теории реляционных баз данных. Работая в компании IBM, он создал реляционную модель данных. В 1970 издал работу «A Relational Model of Data for Large Shared Data Banks», которая считается первой работой по реляционной модели данных.
Реляционная модель данных — это способ рассмотрения данных, то есть предписание для способа представления данных (посредством таблиц) и для способа работы с таким представлением (посредством операторов). Она связана с тремя аспектами данных: структурой (объекты), целостностью и обработкой данных (операторы).
В 2002 журнал Forbes поместил реляционную модель данных в список важнейших инноваций последних 85 лет.
Цели создания реляционной модели данных:
- обеспечение более высокой степени независимости от данных;
- создание прочного фундамента для решения семантических вопросов и проблем непротиворечивости и избыточности данных;
- засширение языков управления данными за счёт включения операций над множествами.
Структура данных в реляционной модели данных
Реляционная модель данных предусматривает структуру данных, обязательными объектами которой являются:
Отношение — это плоская (двумерная) таблица, состоящая из столбцов и строк:
| ID | Фамилия | Имя | Должность | г.р. |
| 1 | Петров | Игорь | Директор | 1968 |
| 2 | Иванов | Олег | Юрист | 1973 |
| 3 | Ким | Елена | Бухгалтер | 1980 |
| 4 | Сенин | Илья | Менеджер | 1981 |
| 5 | Васин | Сергей | Менеджер | 1978 |
Атрибут — это поименованный столбец отношения.
Домен — это набор допустимых значений для одного или нескольких атрибутов.
Кортеж — это строка отношения.
Степень определяется количеством атрибутов, которое оно содержит
Кардинальность — это количество кортежей, которое содержит отношение.
Первичный ключ — это уникальный идентификатор для таблицы.
Соответствие между формальными терминами реляционной модели данных и неформальными:
- отношение (формальный термин) — таблица (неформальный термин);
- атрибут — столбец;
- кортеж — строка или запись;
- степень — количество столбцов;
- кардинальное число — количество строк;
- первичный ключ — уникальный идентификатор;
- домен — общая совокупность допустимых значений.
Отношения и их реализация в реляционной модели данных
Отношение R на множестве доменов D 1 , D 2 , …, D n — это подмножество декартова произведения этих доменов:
Пример 1. Определены домены: D 1 — множество фамилий преподавателей, D 2 — множество аудиторий, D 3 — множество учебных групп, D 4 — множество учебных дисциплин. Записать отношения: 1) закрепление преподавателей за учебными курсами; 2) расписание занятий в группах.
1) закрепление преподавателей за учебными курсами:
Это отношение определяет множество преподавателей, ведущих множество учебных дисциплин.
2) расписание занятий в группах:
Это отношение определяет множество аудиторий, в которых проводятся занятия по множеству учебных дисциплин для множества учебных групп.
Свойства отношений:
- уникальное имя отношения;
- уникальное имя атрибута;
- нет одинаковых кортежей;
- кортежи не упорядочены сверху вниз;
- атрибуты не упорядочены слева направо;
- все значения атрибутов атомарные (нормализованное отношение).
Таким образом, реляционная база данных — это набор нормализованных отношений. Для того, чтобы перейти к видам отношений, введём понятие переменной отношения. Переменная отношения — это именованный объект, значение которого может изменяться с течением времени. Переменная отношения в разное время — это различные таблицы базы данных, у которых разные строки, но одинаковые столбцы.
Виды отношений:
- именованное отношение;
- базовое отношение;
- производное отношение;
- выражаемое отношение;
- представление (view);
- снимки (snapshot);
- результат запроса;
- промежуточный результат.
Именованное отношение — это переменная отношения, определённая в СУБД (системе управления базами данных) посредством оператора CREATE (CREATE TABLE, CREATE BASE RELATION, CREATE VIEW, CREATE SNAPSHOT).
Базовое отношение — это именованное отношение, которое не является производным. Существование базового отношения не зависит от существования других отношений.
Производное отношение — это отношение, которое определено через другие именованные отношения. Производное отношение зависит от существования других — базовых — отношений.
Выражаемое отношение — это отношение, которое можно получить из набора именованных отношений посредством некоторого реляционного выражения. Каждое именованное отношение является выражаемым отношений, но не наоборот. Примеры выражаемых отношений — базовые отношения, представления, снимки, промежуточные и окончательные результаты. Множество всех выражаемых отношений — это множество всех базовых и всех производных отношений.
Представление — это именованное производное отношение. Представлены в базе данных в виде определения. Представление не хранится в физической памяти системы управления базой данных (СУБД), а формируется с использованием других именованных отношений.
Снимки (snapshot) — это то же, что и представление, но с физическим сохранением и с периодическим обновлением.
Результат запроса — это неименованное производное отношение. СУБД не обеспечивает постоянного существования результатов запросов. Для сохранения результата запроса его можно присвоить какому-либо именованному отношению.
Промежуточный результат — это неименованное производное отношение, являющееся результатом подзапроса, вложенного в бОльшее выражение.
Ключи отношения в реляционной модели данных
Ключи отношения могут быть следующми:
- суперключ;
- потенциальный ключ;
- первичный ключ;
- внешний ключ;
- суррогатный ключ.
Ключ отношения — это подсхема исходной схемы отношения, состоящая из одного или нескольких атрибутов, для которых декларируется условие уникальности значений в кортежах отношений. При объявлении схемы базового отношения могут быть заданы объявления нескольких ключей.
Ключ отношения может быть простым или составным. Простой ключ – это ключ, состоящий из одного и не более атрибута. Составной ключ -ключ, состоящий из двух и более атрибутов.
Суперключ — это атрибут или множество атрибутов, которое единственным образом идентифицирует кортеж данного отношения. Он может включать дополнительные атрибуты. Суперключ не обладает свойством неизбыточности.
Потенциальный ключ — это подмножество атрибутов отношения, удовлетворяющее требованиям уникальности и неизбыточности. Он обладает следующими свойствами. Уникальность: в таблице нет двух разных строк с одинаковыми значениями в нашем потенциальном ключе. Неизбыточность: нельзя убрать один из столбцов из ключа, так, чтобы он не потерял уникальности. В отношении может быть больше одного потенциального ключа.
Первичный ключ (primary key, PK) — это один из потенциальных ключей отношения, выбранный в качестве основного ключа. Допустимо объявление одного и только одного первичного ключа. Атрибуты первичного ключа не могут принимать значения Null.
Внешний ключ (foreign key, FK) — это ключ, объявленный в базовом отношении, который при этом ссылается на первичный того же самого или какого-то другого базового отношения.
Суррогатный ключ — это служебный атрибут, добавленный к уже имеющимся информационным атрибутам отношения. Предназначение суррогатного ключа — служить первичным ключом отношения. Значение этого атрибута генерируется искусственно.
Пример 2. Есть база данных сети аптек. В ней есть таблица «Аптеки», в которую занесены все аптеки сети, и есть таблица «Препараты». Кроме того, есть таблица «Наличие», в которую заносятся данные о наличии препаратов в каждой аптеке. В таблице наличие есть поля: «Аптека» (в ней — идентификаторы аптек), «Препарат» (в ней — идентификаторы препаратов), «Количество». Возникает проблема: в случае поступления в аптеку некоторого количества препарата можно не заметить, что в той же аптеке тот же препарат уже содержится в некотором количестве и сделать новую записись в таблице, в которой аптека и препарат будут повторяться. Как на уровне ключей избежать этой проблемы?
Решение. Можно объявить первичным ключём таблицы «Наличие» составной ключ, состоящий из идентификатора аптеки и идентификатора препарата. Тогда в таблице невозможно повторение в разных записях сочетания аптеки и прапарата. Первичный ключ может быть не только простым, но и составным.
Целостность данных в реляционной модели данных
Понятия реляционной целостности:
- определитель NULL;
- целостность сущностей;
- ссылочная целостность;
- корпоративные ограничения целостности.
Определитель NULL. Значение Null обозначает тот факт, что значение не определено. Null не принадлежит никакому типу данных и может присутствовать среди значений любого атрибута, определенного на любом типе данных. Двуместная «арифметическая» операция с Null даёт Null. Операция сравнения с Null даёт UNKNOWN.
Целостность сущностей. Требование целостности сущности означает, что первичный ключ должен полностью идентифицировать каждую сущность, а поэтому в составе любого значения первичного ключа не допускается наличие неопределенных значений. Значение атрибута должно быть атомарным.
Ссылочная целостность. Требование целостности по ссылкам состоит в том, что для каждого значения внешнего ключа, появляющегося в кортеже значения-отношения ссылающейся переменной отношения, либо в значении-отношении переменной отношения, на которую указывает ссылка, должен найтись кортеж с таким же значением первичного ключа, либо значение внешнего ключа должно быть полностью неопределенным. Существуют правила удаления кортежа из отношения, на которое ведет ссылка.
Ссылочная целостность: удаление кортежа. Существует три подхода удаления кортежа из отношения, на которое ведет ссылка.
- Ограничение удаления–Delete: Restrict.
- Каскадное удаление–Delete: Cascade.
- Установка значения NULL, перевод значения внешнего ключа в неопределённое состояние – Delete: Set NULL.
Ограничение удаления. Запрещается производить удаление кортежа, для которого существуют ссылки. Сначала нужно либо удалить ссылающиеся кортежи, либо соответствующим образом изменить значения их внешнего ключа.
Каскадное удаление. При удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи.
Установка значения NULL. При удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится полностью неопределенным.
Пример 3. Есть база данных портала новостей. В ней есть таблица «Рубрики» (политика, экономика, спорт и т.д), есть таблица «Автора» (фамилии и имена авторов). Есть таблица «Тексты», в которой в каждой записи о тексте новости есть поля «Рубрика» (с идентификаторами рубрик из соответствующей таблицы) и «Автор» (с идентификаторами рубрик из соответствующей таблицы). Какими способами можно добиться, чтобы при удалении рубрики и автора была соблюдена ссылочная целостоность данных?
Решение. Первый способ: установить запрет на удаление рубрики или автора из соответствующих таблиц, в случае, если в таблицы «Тексты» есть ссылки на эту рубрику или на этого автора. Второй способ: задать автоматическое удаление из таблицы «Тексты» записей, в которой фигурируют эта рубрика или этот автор. Третий способ: в случае удаления рубрики или автора из соответствующих таблиц в ссылающихся кортежах таблицы «Тексты» значения идентификатора этой рубрики или этого автора становятся неопределёнными (NULL).
Как это делается на уровне языка запросов SQL — в материале SQL ALTER TABLE — изменение таблицы базы данных.
Корпоративные ограничения целостности — это дополнительные правила поддержки целостности данных, определяемые пользователями или администраторами базы данных.
Кардинальное число и положение
Одним из терминов, который иногда можно встретить в отношении наборов, включая таблицы- является кардинальное число (cardinality, «мощность множества»). Оно описывает количество строк в наборе, который может быть таблицей или выходным набором. Реже можно встретить кардинальное число строки или кардинальное число значения ключа, что означает позицию строки в упорядоченном выходном наборе.
Термин, используемый для номера столбца в наборе, — положение (degree). Кроме того, вы можете встретить фразу типа положение столбца, означающую позицию столбца в порядке столбцов в наборе слева направо.
Читайте также
Положение и выравнивание заголовка
Положение и выравнивание заголовка Первым в данном разделе рассмотрим свойство caption-side. Оно определяет положение поля заголовка относительно поля таблицы. Вот список его значений, указывающих, что поле заголовка располагается:• top – над полем таблицы;• bottom – ниже поля
Число загрузок с одного хоста
Число загрузок с одного хоста Над временной диаграммой (кроме блокирования картинок) нам также стоит задуматься о том, что картинки после скрипта загружаются только по две. Это происходит из-за ограничений на число файлов, которые могут быть загружены параллельно. В IE <=
Положение указателя текущей позиции
Положение указателя текущей позиции feofУказатель конца файла.Синтаксис:int feof(int $f)Возвращает true, если достигнут конец файла (то есть если указатель файла установлен за концом файла).Пример:$f=fopen(«myfile.txt»,»r»);while(!feof($f))< $str=fgets($f); // Обрабатываем очередную строку
4.24 Максимальное число пересылаемых элементов
4.24 Максимальное число пересылаемых элементов Каждая из рассмотренных нами технологий имеет различные максимальные размеры для своих кадров. После исключения заголовка кадра, заключительной части, а также заголовков LLC и SNAP (если они присутствуют), полученный результат
19. Пиковое положение, или Статистика пасьянсов
19. Пиковое положение, или Статистика пасьянсов У каждого программиста рано или поздно наступает момент, когда работа не идет. Без каких-либо видимых причин программа прямо-таки сопротивляется всем вашим усилиям написать ее. Каждая новая попытка тут же оборачивается
Пример 4-4. Целое число или строка?
Пример 4-4. Целое число или строка? #!/bin/bash# int-or-string.sh: Целое число или строка?a=2334 # Целое число.let «a += 1″echo «a = $a » # a = 2335echo # Все еще целое число.b=$ # замена «23» на «BB». # Происходит трансформация числа в строку.echo «b = $b» # b = BB35declare -i b
4.6.8 Незаданное Число Параметров
4.6.8 Незаданное Число Параметров Для некоторых функций невозможно задать число и тип всех параметров, которые можно ожидать в вызове. Такую функцию описывают завершая список описаний параметров многоточием (. ), что означает «и может быть, еще какие-то неописанные
Переменное число параметров
Переменное число параметров Для указания того, что подпрограмма должна иметь переменное число параметров, используется ключевое слово params, за которым следует описание динамического массива. Например: function Sum(params a: array of integer): integer; begin Result := 0; for i: integer := 0 to a.Length do
Делим на число пи
Делим на число пи Автор: Владислав БирюковКонечно, безоговорочно полагаться на приведенное на соседней странице ранжирование компаний не стоит – оно может служить лишь неким ориентиром. Слишком много в подобных расчетах условностей: какие показатели учитываются, с
4.3. Экспозиция и число диафрагмы
4.3. Экспозиция и число диафрагмы Читая этот раздел, вы заметите, что понятия выдержки и диафрагмы употребляются, как правило, в паре. Объясняется это просто: выдержка и диафрагма определяют значение ключевого понятия фотографии – экспозиции. Определение Экспозицией
Команды подменю Window → Arrange (Окно → Положение)
Команды подменю Window ? Arrange (Окно ? Положение) Данное подменю содержит 10 команд, знание которых облегчает работу в Photoshop.• Cascade (Каскад). Размещает окна открытых изображений друг за другом. Это классический, привычный многим пользователям режим работы.• Tile (Разделить). Другой
13-Я КОМНАТА: Число человеческое
13-Я КОМНАТА: Число человеческое Автор: Сергей Вильянов «Здесь мудрость. Кто имеет разум, тот сочтет число зверя, ибо это число человеческое; число его — шестьсот шестьдесят шесть». Откровение святого Иоанна Богослова гл. 13. ст. 18 Как известно, люди обожают искусственные
ТЕМА НОМЕРА: Осадное положение
ТЕМА НОМЕРА: Осадное положение Авторы: Андрей Васильков, Бойцев, ОлегЕсли бы участие человека в войнах можно было бы исключить, идеалы хиппи были бы воплощены в жизнь еще много столетий назад. Однако, несмотря на все ухищрения технического прогресса, победу в войне за свою