Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Правильные инексы  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Добрый день, коллеги!

Есть весьма актуальный вопрос. Как правильно строить индексы. Например, есть у нас таблица из 10 столбцов. Первый, как водится, уникальный. Поиск осуществляю по 1,2,3,4,5,6,7,8,9,10. Как было бы правильно выстроить индексы для подобных запросов? сколько индексов создали бы вы?
9 июн 14, 15:52    [16143528]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Индексы строятся для запросов. О которых мало чего конкретного сказано.
9 июн 14, 16:25    [16143823]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Гавриленко Сергей Алексеевич
Индексы строятся для запросов. О которых мало чего конкретного сказано.


Куда уж конкретней. Выборка ведется по трем-четырем полям из 10. 3-4 поля могут быть разными. Суть в том, стоит ли ограничиться только кластерным индексом или создать кластерный и один который будет покрывать другие поля. Или несколько помимо кластерного...
9 июн 14, 16:33    [16143882]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
MedBrat
Гавриленко Сергей Алексеевич
Индексы строятся для запросов. О которых мало чего конкретного сказано.


Куда уж конкретней. Выборка ведется по трем-четырем полям из 10. 3-4 поля могут быть разными. Суть в том, стоит ли ограничиться только кластерным индексом или создать кластерный и один который будет покрывать другие поля. Или несколько помимо кластерного...
Запросы на выборку начинаются со слова select. А не "какая-то выборка как-то ведется по каким-то трем-четырем полям". Вот и сделайте для нее индекс по каким-то полям.
9 июн 14, 16:34    [16143890]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
MedBrat
Добрый день, коллеги!

Есть весьма актуальный вопрос. Как правильно строить индексы. Например, есть у нас таблица из 10 столбцов. Первый, как водится, уникальный. Поиск осуществляю по 1,2,3,4,5,6,7,8,9,10. Как было бы правильно выстроить индексы для подобных запросов? сколько индексов создали бы вы?


То, что вы пишете, похоже, следует назвать поисковой системой.

Как искать -- никто вам не скажет. Мой вам совет, не ищите по исходной таблице.

Создайте индексирующие структуры, ориентированные на быстрые сканы и быструю фильтрацию. Я имею ввиду отдельные таблицы со своими индексами. Запрос формируйте динамически, надо фильтровать по полю 1 подключайте фильтр по полю 1 и т.п.

В общем, если хотите делать правильно не делайте поиск по исходно таблице. Потом будете оптимизировать и переоптимизировать и всё равно будут запросы, которые будут виснуть.
9 июн 14, 16:40    [16143945]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Индексы строятся конкретные для конкретных запросов. Индекс на все случаи жизни не существует.
9 июн 14, 16:55    [16144086]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
a_voronin
Мой вам совет, не ищите по исходной таблице.

Создайте индексирующие структуры, ориентированные на быстрые сканы и быструю фильтрацию. Я имею ввиду отдельные таблицы со своими индексами. Запрос формируйте динамически, надо фильтровать по полю 1 подключайте фильтр по полю 1 и т.п.
О боже. Что это было?

MedBrat, не обращайте на этот бред никакого внимания.
9 июн 14, 22:39    [16145515]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
SSn888
Member

Откуда:
Сообщений: 340
Написание модели идеального сферического коня в вакууме

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


У меня тоже актуальный вопрос
Как правильно поливать домашние растения?
Вот у меня стоят на подоконнике десяток штук, одни какие-то колючие, другие - ничего - вроде даже приятные и мяконькие.
Вот как мне их все поливать?
9 июн 14, 23:10    [16145670]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
SSn888
Написание модели идеального сферического коня в вакууме

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


У меня тоже актуальный вопрос
Как правильно поливать домашние растения?
Вот у меня стоят на подоконнике десяток штук, одни какие-то колючие, другие - ничего - вроде даже приятные и мяконькие.
Вот как мне их все поливать?


Согласен, может быть информация слишком общая. Залез в мануал и стал читать. Пишут, что строго экспериментальным путем и желательно с малым количеством полей, чтоб понизить стоимость обслуживания и повысить скорострельность.
10 июн 14, 10:39    [16147096]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1741
MedBrat
Добрый день, коллеги!

Есть весьма актуальный вопрос. Как правильно строить индексы. Например, есть у нас таблица из 10 столбцов. Первый, как водится, уникальный. Поиск осуществляю по 1,2,3,4,5,6,7,8,9,10. Как было бы правильно выстроить индексы для подобных запросов? сколько индексов создали бы вы?


Ищем и читаем информацию про sys.db_missing_index_group_stats, sys.dm_db_missing_index_group_stats
На форуме есть пример кода, работающего с этими группами, и позволяющего понять, какие индексы стоит создать, а какие стоит удалить.

Другой вариант - выделяем код запроса, и нажимаем на панели SQL Editor кнопку Display Estimated Execution Plan.
Если для ускорения запроса стоит создать индекс, то информацию об этом Вы получите над планом выполнения запроса.
После создания рекомендованного индекса советую операцию повторить, так как индексов может потребоваться создать несколько.
10 июн 14, 15:25    [16149543]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
zrb
Member

Откуда:
Сообщений: 3
DaniilSeryi,

может нужно развернуть OLAP для таких целей?
10 июн 14, 15:39    [16149677]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
MedBrat,

Я бы подумал об нормализации таблицы... При 10 полях можно сделать или 10 таблиц (Key, Value) или 1 таблицу... (Key, Type, Value) и эти таблицы уже индексировать... Да будут дополнительные JOIN, но есть очень большая вероятность что это будет быстрее нежели 100500 индексов и точно меньше места занимать и на диске и в памяти, при запросах.
В любом случае перед выбором какая структура лучше - проведите тестирования.
10 июн 14, 15:56    [16149837]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Алексей Куренков, +1 к троллингу.

Молодец, возьми с полки пирожок.
10 июн 14, 16:25    [16150008]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Mnior
Алексей Куренков, +1 к троллингу.

Молодец, возьми с полки пирожок.


Спасибо за совет, уважаемый молдавский друг. Я не совсем понимаю сарказма?
10 июн 14, 17:37    [16150631]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Mnior
a_voronin
Мой вам совет, не ищите по исходной таблице.

Создайте индексирующие структуры, ориентированные на быстрые сканы и быструю фильтрацию. Я имею ввиду отдельные таблицы со своими индексами. Запрос формируйте динамически, надо фильтровать по полю 1 подключайте фильтр по полю 1 и т.п.
О боже. Что это было?

MedBrat, не обращайте на этот бред никакого внимания.


Вот интересный момент, я не один раз писал успешные быстрые поисковики по такому принципу, и всегда меня обсирали, что так не делают, что это бред.
10 июн 14, 18:29    [16150876]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Алексей Куренков
Да будут дополнительные JOIN, но есть очень большая вероятность что это будет быстрее нежели 100500 индексов и точно меньше места занимать и на диске и в памяти, при запросах.
Это будет точно всегда медленее или так же, это очевидно. Не может быть ни одного случая, сочетания данных и условий выборки, когда такая схема быстрее для выборки данных.

Места действительно может занимать и меньше, зависит от запросов, и, соответственно, от требуемых индексов. Обновление тоже соответственно может быть медленее, если индексов много.
10 июн 14, 19:45    [16151134]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
a_voronin
я не один раз писал успешные быстрые поисковики по такому принципу, и всегда меня обсирали, что так не делают, что это бред.
Не надо вот этой гуманитарной напыщенности. Нормальный логик найдёт несколько очевидных когнитивных искажения в вашей постановке вопроса.

Более критически относитесь к вещам, тогда и научитесь замечать больше, и соответственно понимать других и почему к вам "так относятся".

Пресекая встречный вопрос. Выше уже есть ответ почему это фигово.
10 июн 14, 21:16    [16151480]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
a_voronin
Mnior
пропущено...
О боже. Что это было?

MedBrat, не обращайте на этот бред никакого внимания.


Вот интересный момент, я не один раз писал успешные быстрые поисковики по такому принципу, и всегда меня обсирали, что так не делают, что это бред.
Вы можете привести хотя бы один конструктивный аргумент в пользу этого подхода? А еще лучше результаты тестов. Тогда можно будет что-то обсуждать, а пока что да, звучит больше как бред.
11 июн 14, 09:58    [16152609]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Glory
Member

Откуда:
Сообщений: 104760
Мда. У MedBrat-а проблема из предыдущей темы - как уменьшить размер базы и индексов.
А ему советуют - создай еще больше таблиц и индексов.

Типа
Сначала купи козу. Потом продай козу.
11 июн 14, 10:02    [16152626]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Гавриленко Сергей Алексеевич, Minor

После ваших ответов я немного протестировал свою версию... получилось то, что, на мой взгляд, все таки предложенное мной решение не самое плохое...

----- подготовим БД и таблицы для тестов
use master
--drop database test1
create database test1
go
use test1
go
create table tbl1
(
	id int primary key,
	f1 int,
	f2 money,
	f3 varchar(50)
)
create table tbl2_id (id int primary key clustered)
create table tbl2_1  (id int primary key clustered, f1 int, unique (f1,id))
create table tbl2_2  (id int primary key clustered, f2 money, unique (f2,id))
create table tbl2_3  (id int primary key clustered, f3 varchar(50), unique (f3,id))
go
create view dbo.tbl2 with schemabinding as
select t0.id, t1.f1, t2.f2, t3.f3
from dbo.tbl2_id t0
join dbo.tbl2_1 t1 on t0.id = t1.id
join dbo.tbl2_2 t2 on t0.id = t2.id
join dbo.tbl2_3 t3 on t0.id = t3.id
go
create trigger trg_ins on tbl2 instead of insert
as
	insert tbl2_id select id from inserted
	insert tbl2_1 select id, f1 from inserted
	insert tbl2_2 select id, f2 from inserted
	insert tbl2_3 select id, f3 from inserted
go
create unique clustered index ix1 on tbl2 (id)
go

with cte as (select * from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(i))
insert tbl2
output inserted.* into tbl1
select 1+c0.i+c1.i*10+c2.i*100+c3.i*1000+c4.i*10000
	,convert(int,convert(binary(16),newid()))
	,convert(money,convert(binary(16),newid()))
	,cast(newid() as varchar(50))
from		cte c0
cross join	cte c1
cross join	cte c2
cross join	cte c3
cross join	cte c4
go


Ну и сами тесты, которые можно дополнить (смотреть здесь статистику ИО и планы запросов):

select top 100 * from tbl1
select top 100 * from tbl2
select top 100 * from tbl2 with(noexpand)

select top 100 * from tbl1 where f1 between 1000000 and 2000000
select top 100 * from tbl2 where f1 between 1000000 and 2000000


Единственное чего я не понимаю - это причину не использования индексированного представления без хинта...

пробовал на select @@version = "Microsoft SQL Server 2014 (CTP2) - 12.0.1524.0 (X64)
Oct 3 2013 19:00:26
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )"

Так же на 2012 девелопер эдишн
11 июн 14, 11:24    [16153213]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Алексей Куренков
Гавриленко Сергей Алексеевич, Minor

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

Итак по-порядку:

1. Для начала вставим хотя бы по миллиону записей. (100К это совсем мало)

insert tbl1...
 SQL Server Execution Times:
CPU time = 21107 ms, elapsed time = 43010 ms.

insert tbl2...
 SQL Server Execution Times:
CPU time = 41776 ms, elapsed time = 80992 ms

2. Место на диске:

TableNameReserved MB
dbo.tbl1143.875

TableNameReserved MB
dbo.tbl263.32
dbo.tbl2_130.141
dbo.tbl2_238.016
dbo.tbl2_3100.891
dbo.tbl2_id12.633
Total tbl2:245.001

Как-то расходится с утверждением:
Алексей Куренков
точно меньше места занимать и на диске и в памяти, при запросах


3. Поисковые запросы.
Вы специально "забыли" создать индексы на tbl1? При том что таблицы tbl2 обвешали индексами с ног до головы?
Сначала создадим индексы, чтобы можно было сравнивать результаты:
create nonclustered index tbl1_f1 on tbl1 (f1)
create nonclustered index tbl1_f2 on tbl1 (f2)
create nonclustered index tbl1_f3 on tbl1 (f3)


Ну и собственно:
select * from tbl1 where f1 between 1000000 and 2000000
select * from tbl2 where f1 between 1000000 and 2000000

 Table 'tbl1'. Scan count 1, logical reads 691, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 64 ms.


Table 'tbl2_3'. Scan count 0, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl2_2'. Scan count 0, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl2_id'. Scan count 0, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl2_1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 172 ms.

А на холодном кэше те же запросы еще интереснее:
Table 'tbl1'. Scan count 1, logical reads 1165, physical reads 1, read-ahead reads 1880, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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


Table 'tbl2_3'. Scan count 0, logical reads 1153, physical reads 1, read-ahead reads 1680, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl2_2'. Scan count 0, logical reads 1115, physical reads 1, read-ahead reads 1328, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl2_id'. Scan count 0, logical reads 1081, physical reads 1, read-ahead reads 1080, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl2_1'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2488 ms.

Можно и поинтереснее тест придумать
select top 100 * from tbl1 where f1 = 1000000 OR f2 = 1000000
select top 100 * from tbl2 where f1 = 1000000 OR f2 = 1000000

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl1'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

Table 'tbl2'. Scan count 5, logical reads 8210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 98 ms.


Ну и напоследок
DELETE FROM dbo.tbl1

DELETE FROM tbl2_id
DELETE FROM tbl2_1
DELETE FROM tbl2_2
DELETE FROM tbl2_3

tbl1:
SQL Server Execution Times:
CPU time = 17940 ms, elapsed time = 22238 ms.

tbl2:
SQL Server Execution Times:
CPU time = 6661 ms, elapsed time = 8140 ms.

SQL Server Execution Times:
CPU time = 6786 ms, elapsed time = 7220 ms.

SQL Server Execution Times:
CPU time = 7441 ms, elapsed time = 9162 ms.

SQL Server Execution Times:
CPU time = 11981 ms, elapsed time = 13219 ms.

Я так и не понял где выигрыш.
12 июн 14, 00:52    [16157695]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Mind,

Я не спорю что десяток джоинов при большом скане дает выигрыш нежели скан хипа или кластерного индекса, и именно на эту тему есть предложение индексированного представления - полный аналог таблицы. Просто, когда встает вопрос коньюктивно составлять предикаты на случайные поля, (ну f1 = @f1 and @f2 = f2 and .....) то идеально когда есть индекс (f1, f2 ... - 8 а то и 10 полей). Когда в условии нет f1, а есть (f4, f5, f6, f7, f8, f9) - то индекс собственно для быстрого поиска должен содержать эти все поля. Комбинаций здесь при 10 полях может быть достаточно много. И в итоге индексов может быть больше чем в таблице (key, value) - кластерный и некластерный - других вариантов нету просто. И в моем понимании эта схема может спасти ситуацию...
Естественно, если вопрос идет о 5-10 запросах, а не случайно скомбинированных условиях, дешевле навесить на широкую таблицу эти 5-10 индексов...
А если такое что то...

declare @param varchar(max) = '@f1 int, @f2 ..... @f10 varchar(200)'
declare @q varchar(max) = 'select * from tbl2 where 1=1'
+case when @f1 is not null then ' and f1 = @f1' else '' end
+case when @f1 is not null then ' and f2 = @f2' else '' end
+case when @f1 is not null then ' and f3 = @f3' else '' end
+case when @f1 is not null then ' and f4 = @f4' else '' end
+case when @f1 is not null then ' and f5 = @f5' else '' end
+case when @f1 is not null then ' and f6 = @f6' else '' end
+case when @f1 is not null then ' and f7 = @f7' else '' end
+case when @f1 is not null then ' and f8 = @f8' else '' end
+case when @f1 is not null then ' and f9 = @f9' else '' end
+case when @f1 is not null then ' and f10 = @f10' else '' end

exec sp_executesql @q, @param, @f1 = @f1, @f2 = @f2 ..... @f10 = @f10

вот про такую ситуацию я и понимал изначально... для такой ситуации все комбинации индексов практически не реально построить и сканирование кластерного индекса (кучи) будет неизбежно...

А при 10 ках таблиц... возможно и будет выигрыш.
12 июн 14, 22:39    [16159818]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Алексей Куренков
А при 10 ках таблиц... возможно и будет выигрыш.
Гадание на кофейной гуще?
Так может быть или нет?! Или стоим до последнего?!

16151134
13 июн 14, 01:11    [16160259]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Mnior,

Нет конечно... я не упираюсь как баран в своей правоте. )))))
Просто предложил один из возможных вариантов... который в каких то случаях частных будет иметь преимущества... Хотя... здесь действительно гадание... ибо не понятно что требует тюнинга... какие запросы/команды.
16 июн 14, 13:47    [16169262]     Ответить | Цитировать Сообщить модератору
 Re: Правильные инексы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Алексей Куренков
... который в каких то случаях частных будет иметь преимущества...
Угу, чуть менее чаще, чем никогда.
Алексей Куренков
Хотя... здесь действительно гадание... ибо не понятно что требует тюнинга... какие запросы/команды.
Совершенно нипанятна, уже теряем нить разговора ... 16151134, да?
17 июн 14, 00:01    [16172770]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить