ORDER BY DATE, оптимизация сортировки по дате в SQL Server / Microsoft SQL Server / Sql.ru

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Microsoft SQL Server

ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98

Здравствуйте!

Есть вопрос касательно индексов в таблице. Представим пример: есть некая таблица содержащая, скажем, каталог книжек. У каждой книжки есть поле с датой её издания. Привожу упрощенный вариант таблицы.

create table Books(
  bookId int,
  authorName varchar(128),
  category varchar(128),
  creationDate date
);

Основная задача - выводить книжки по определённому условию, сортируя(order) их по дате создания. Например:

select * from Books where category = 'Классическая литература' order by creationDate;

Вопрос: как оптимально создать индекс(ы) для этой таблицы, что максимально ускорить сортировку по дате?

Вариант А: просто создать некластиризированный индекс для поля creationDate:

create nonclustered index creationDate on Books.creationDate;

Вариант Б: создать некластиризированный индекс для поля creationDate(см. выше) и изначально сделать bookId primary key.

Вариант В: ... ваш вариант!

Я просто не совсем понимаю разницу между кластиризированным и некластиризированным индексом. Знаю только, что кластиризированный индекс и primary key не могут быть НЕ уникальными, поэтому создать кластиризированный индекс для creationDate не получится. Да и вообще, нужен ли там кластиризированный индекс, или подойдёт и некластиризированный? (типа, пофигу с точки зрения быстродействия).

И еще - если уж я создам индекс для creationDate, то нужно ли создавать primary key для bookId? - или это ни к чему?

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

Спасибо.

20 апр 13, 21:16 [14208555] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925

автор
Вариант А: Вариант Б: Вариант В:

Филиал "Кто хочет стать миллионером"?

автор
Основная задача - выводить книжки по определённому условию, сортируя(order) их по дате создания.

Условие то где?! Вывод всей таблицы с сортировкой по одному полю не самое удачное место для обсуждения вариантов оптимизации.

автор
просто не совсем понимаю разницу между кластиризированным и некластиризированным индексом.

Ну так попробуйте понять: https://www.sql.ru/articles/mssql/03013101indexes.shtml

автор
И еще - если уж я создам индекс для creationDate, то нужно ли создавать primary key для bookId? - или это ни к чему?

Вы путаете теплое с мягким. Прильните к основам реляционной теории в части Entity (и прочее) integrity.

автор

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

На заборе и не такое напишут, а по факту дрова...

20 апр 13, 21:41 [14208646] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98


Условие то где?! Вывод всей таблицы с сортировкой по одному полю не самое удачное место для обсуждения вариантов оптимизации.

Так вот же:

select * from Books where category = 'Классическая литература' order by creationDate;


Вы путаете теплое с мягким. Прильните к основам реляционной теории в части Entity (и прочее) integrity.

Я знаком с реляционной теорией, но плаваю в индексах. Логически, primary key мне не нужен вообще, но, если он, в связке с индексом для creationDate даст что-то полезное(чего я не знаю) - то я бы его создал. Раз нет - значит нет.


Ну так попробуйте понять: https://www.sql.ru/articles/mssql/03013101indexes.shtml

Спасибо - читну.

20 апр 13, 21:47 [14208670] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925

roman_lenko
Я знаком с реляционной теорией

Сомневаюсь, судя по приведенной денормализованной таблице. Let's Google "Нормальные формы".

20 апр 13, 21:55 [14208694] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
roman_lenko Member Откуда: Киев Сообщений: 98	Блин, да это примерная таблица, которую я только что выдумал. Я же не буду тут описывать десять таблиц с foreign key и идентификаторами; мне нужно просто и доходчиво описать вам таблицу и получить ответ на вопрос: какой индекс создать для creationDate, чтобы обеспечить оптимальную скорость сортировки по этому полю? - т.е., достаточно ли просто создать некластиризированный индекс(т.е., данные уже хранятся в отсортированном виде - если я правильно понимани), или есть какой-то более продвинутый способ.
20 апр 13, 22:49 [14208818] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
invm Member Откуда: Москва Сообщений: 9646	roman_lenko, У вас пока что полное отсутствие понимания для чего нужны индексы в РСУБД. Вот вам для начала: Индекс (базы данных) Index Basics Ну и непонимание для чего нужен primary key таки свидетельствует, что в реляционной теории вы тоже плаваете...
20 апр 13, 23:13 [14208891] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

alexeyvg
Member

Откуда: Moscow
Сообщений: 31783

roman_lenko

Основная задача - выводить книжки по определённому условию, сортируя(order) их по дате создания. Например:

select * from Books where category = 'Классическая литература' order by creationDate;

Вопрос: как оптимально создать индекс(ы) для этой таблицы, что максимально ускорить сортировку по дате?

Конкретно для этого запроса идеальным будет кластерный индекс на category, creationDate

Т.е. данные в самой таблице будут сгруппированы по category , потом для каждой category по creationDate. Ну и выборка данных соответственно получится идеально быстрая.

21 апр 13, 12:20 [14209477] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98

alexeyvg

Большое спасибо - это именно то, что я и хотел услышать!

Кстати, за литературу всем тоже спасибо - инфа про устройство индексов с SQL.RU оказалась весьма полезной.

22 апр 13, 14:02 [14212760] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
roman_lenko Member Откуда: Киев Сообщений: 98	Подскажите, пожалуйста, как будет вести себя SQL-Server имея следующую таблицу и запрос: create table City( cityId int not null identity(1,1) primary key, cityName not null varchar(64) ); create unique nonclustered index cityName on City(cityName); select cityId from City where cityName = 'киев, украина'; Я хочу добиться, чтобы все имена городов в таблице хранились сортированными по индексу cityName, соотв., при выполнении указанного мною запроса SQL-Server НЕ БУДЕТ сканировать всю таблицу для поиска имени города, а выполнит "оптимизированный" запрос по логике: "у нас определён уникальный индекс cityName, соотв., я(SQL-Server) должен найти первое попавшееся значение cityName, используя сортировку определённую индексом". Это верно? Спасибо.
22 апр 13, 14:29 [14212894] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
ROLpogo Member Откуда: Реутов Сообщений: 219	roman_lenko, Если нет кластерного индекса, то добавьте cityId в include, иначе, если оптимизатор решит использовать ваш некластерный вместо сканирования таблицы, то придется лезть в кучу (RID Lookup) за cityId. create unique nonclustered index cityName on City(cityName) include(cityId);
22 апр 13, 14:41 [14212962] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98

ROLpogo

roman_lenko,
Если нет кластерного индекса, то добавьте cityId в include, иначе, если оптимизатор решит использовать ваш некластерный вместо сканирования таблицы, то придется лезть в кучу (RID Lookup) за cityId.

create unique nonclustered index cityName on City(cityName) include(cityId);

Бр..бр.. Сложно - давайте по порядку :)

Я бы с удовольствием создал clustered index для cityName, но у меня уже есть PRIMARY KEY для cityId, поэтому более кластерных индексов я создавать не могу. PRIMARY KEY мне нужен, потому, что другие таблицы ссылаются на таблицу City как FOREIGN KEY REFERENCES City(cityId).

автор
добавьте cityId в include, иначе, если оптимизатор решит использовать ваш некластерный вместо сканирования таблицы

22 апр 13, 14:48 [14213010] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615

roman_lenko

Подскажите, пожалуйста, как будет вести себя SQL-Server имея следующую таблицу и запрос:

create table City(
  cityId int not null identity(1,1) primary key,
  cityName not null varchar(64)
);

create unique nonclustered index cityName on City(cityName);

select cityId from City where cityName = 'киев, украина';

Я хочу добиться, чтобы все имена городов в таблице хранились сортированными по индексу cityName, соотв., при выполнении указанного мною запроса SQL-Server НЕ БУДЕТ сканировать всю таблицу для поиска имени города, а выполнит "оптимизированный" запрос по логике: "у нас определён уникальный индекс cityName, соотв., я(SQL-Server) должен найти первое попавшееся значение cityName, используя сортировку определённую индексом". Это верно?

Спасибо.

автор
чтобы все имена городов в таблице хранились сортированными по индексу cityName

записи в таблице хранятся в порядке определенным кластерным индексом cityId

автор

соотв., при выполнении указанного мною запроса SQL-Server НЕ БУДЕТ сканировать всю таблицу для поиска имени города, а выполнит "оптимизированный" запрос по логике: [i]"у нас определён уникальный индекс cityName, соотв., я(SQL-Server) должен найти первое попавшееся значение cityName, используя сортировку определённую индексом"

если надо найти первое попавшееся значение по cityName
то

select top 1 cityId from City where cityName = 'киев, украина' order by cityId;

22 апр 13, 14:49 [14213019] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
roman_lenko Member Откуда: Киев Сообщений: 98	Т.е., чтобы вы правильно поняли, cityId вообще никакой роли в сортировке играть НЕ должен. cityId - это просто уникальный идентификатор города - и все.
22 апр 13, 14:50 [14213021] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98


записи в таблице хранятся в порядке определенным кластерным индексом cityId

А как же тогда определить таблицу, чтобы данные хранились отсортированными по cityName?? - убрать PRIMARY KEY?

Короче, я запутался..

22 апр 13, 14:51 [14213026] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
ROLpogo Member Откуда: Реутов Сообщений: 219	Извиняюсь, не заметил primary key. Тогда у вас все в порядке. Оптимизатор с большой вероятностью выберет поиск по некластерному, где и найдет cityId.
22 апр 13, 14:53 [14213033] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615

автор
добавьте cityId в include, иначе, если оптимизатор решит использовать ваш некластерный вместо сканирования таблицы

Если я правильно понимаю, то мне это и нужно. Т.е., скажем, я бы хотел, чтобы города в таблице хранились, примерно, как:

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

т.е., типа, когда SQL-Server ищет идентификатор по имени города "киев, украина", чтобы он сразу переходил к области данных, которая начинается на букву "к", а не сканировал все целиком(по крайней мере, я так себе представляю работу некластерного индекса).

Если вы мне порекомендуете другой вариант создания структуры таблицы, то опишите, пожалуйста.[/quot]

у вас нет ограничения на уникальность названий городов - поэтому запрос select cityId from City where cityName = 'киев, украина'; не гарантирует что будет выбран только один город. И, еще, включать кластерный ключ cityId в индекс по cityName не имеет никакого смысла, он и так там уже есть.

22 апр 13, 14:54 [14213042] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98


у вас нет ограничения на уникальность названий городов

Так есть же - UNIQUE:

create UNIQUE nonclustered index cityName on City(cityName);

22 апр 13, 14:57 [14213054] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
Cammomile Member Откуда: Сообщений: 1214	Роман, вам глаз не режет, что у вас название страны в поле "Название города" храниться? Это дурная практика, на самом деле.
22 апр 13, 15:28 [14213278] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]
Cammomile Member Откуда: Сообщений: 1214	*тся, блин
22 апр 13, 15:29 [14213282] Ответить \| Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

roman_lenko
Member

Откуда: Киев
Сообщений: 98

Cammomile
Роман, вам глаз не режет, что у вас название страны в поле "Название города" храниться? Это дурная практика, на самом деле.

Я знаю об этом. Логично создать поле countryId foreign key references Country(id), но конкретно для текущего случая мне нужно именно так.

22 апр 13, 15:33 [14213312] Ответить | Цитировать Сообщить модератору

Re: ORDER BY DATE, оптимизация сортировки по дате в SQL Server [new]

Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615

roman_lenko


у вас нет ограничения на уникальность названий городов

Так есть же - UNIQUE:

create UNIQUE nonclustered index cityName on City(cityName);

тогда

select cityId from City where cityName = 'киев, украина'

будет работать так, как запланировано

22 апр 13, 16:03 [14213543] Ответить | Цитировать Сообщить модератору

Все форумы / Microsoft SQL Server

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)