Добро пожаловать в форум, 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
Конкретно для этого запроса идеальным будет кластерный индекс на category, creationDate

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


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


Кстати, за литературу всем тоже спасибо - инфа про устройство индексов с 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, иначе, если оптимизатор решит использовать ваш некластерный вместо сканирования таблицы


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

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


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

Если вы мне порекомендуете другой вариант создания структуры таблицы, то опишите, пожалуйста.
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 Ответить