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

Откуда:
Сообщений: 361
Оптимизация запроса без использования покрывающих индексов.
Есть большие (100 миллионов записей) таблицы, на которые не хочется лепить покрывающие индексы под каждый запрос. Поизучал ресурсы
Напр http://www.sql-server-performance.com/2007/covering-indexes/
Вопрос как стимулировать SQL server 2008 делать более эффективный index intersection?
Пример
Есть таблица – буфер сообщений (сотня миллионов записей)
CREATE TABLE [dbo].[msg_buffer](
	[MsgCounter] [bigint] IDENTITY(1,1) NOT NULL,
	[MsgXMLText] [xml] NULL,
	[MsgVarCharMax] [nvarchar](max) NULL,
	[MsgHeader] [nvarchar](2000) NULL,
	[MsgID] [nvarchar](50) NULL,
	[MsgReceiveTime] [datetime] NULL,
	[MsgDescription] [nvarchar](2000) NULL,
	[MsgSource] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_msg_buffer] PRIMARY KEY CLUSTERED 
(
	[MsgCounter] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Есть один кластерный индекс по ключу (MsgCounter) Остальные индексы по колонкам . Каждый индекс по одной колонке (без покрывающих) вот один из примеров.

****** Object: Index [IND_MsgHeader] Script Date: 02/06/2017 17:12:55 ******/
CREATE NONCLUSTERED INDEX [IND_MsgHeader] ON [dbo].[msg_buffer] 
(
	[MsgHeader] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


Таблицу [_InfoRg18168] использую для соединения, и дополнительной фильтрации записей, как видно из плана с ней проблем нет

Далее делаю запрос

    SELECT MsgCounter, MsgReceiveTime, MsgHeader           
           FROM [MIS_BUSBUFFER].[dbo].[msg_buffer] t
           left join [TEST1].[dbo].[_InfoRg18168] as reg  
           ON reg._Fld18362 = t.msgcounter and reg._Fld628=0 
           where MsgReceiveTime>'2016-10-01'  and MsgReceiveTime<'2016-10-10'                          
           AND reg._Fld18362 is null   



Получаю вот такой план:
По сути SQL Server делает Index intersect, но за счет Index Scan по IND_Msgheader . Если убрать MsgHeader из полей выборки (в условиях его нет) – план упростится и станет эффективным.
Как я понимаю – SQL сервер просто предпочитает брать MsgHeader из индекса, но почему он не берет его из таблицы? В конце концов указатели нужные записи уже есть при применении индексов

К сообщению приложен файл. Размер - 84Kb
6 фев 17, 17:41    [20185856]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

ммм прибейте кластерный индекс(станет только хуже :)) ... иначе откуда по вашему возьмётся поле для вывода если в индексе его нет?
6 фев 17, 17:50    [20185897]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
selis76,

> Как я понимаю – SQL сервер просто предпочитает брать MsgHeader из индекса, но почему он не берет его из таблицы?

он посчитал, что один раз просканить индекс по MsgHeader - это дешевле, чем сделать 100500 лукапов в таблицу.
6 фев 17, 17:54    [20185914]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
daw
selis76,

> Как я понимаю – SQL сервер просто предпочитает брать MsgHeader из индекса, но почему он не берет его из таблицы?

он посчитал, что один раз просканить индекс по MsgHeader - это дешевле, чем сделать 100500 лукапов в таблицу.
selis76, посмотрите, сколько записей возвращает ваш запрос. Если много, то действительно, сервер боится лукапа.
Если мало, то, наверное, статистика неправильная, и сервер зря боится лукапа.
6 фев 17, 17:57    [20185933]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16944
партиционирование?
6 фев 17, 18:02    [20185962]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
Lookup по кластерному индексу он тоже делает , но в зависимости от параметров.
Статистику по данной таблице я не пересобирал - таблица тестовая

К сообщению приложен файл. Размер - 37Kb
6 фев 17, 18:04    [20185967]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
invm
Member

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

У вас фильтрация всегда по MsgReceiveTime?
6 фев 17, 18:05    [20185973]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
invm
selis76,

У вас фильтрация всегда по MsgReceiveTime?

Как правило да
6 фев 17, 18:07    [20185982]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

так а в чём беда сделать индекс покрывающим?
6 фев 17, 18:08    [20185988]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
selis76
Как правило да
Тогда сделайте кластерный неуникальный по MsgReceiveTime.
Таблица слегка распухнет, зато запросам с предикатами вида where MsgReceiveTime>'2016-10-01' and MsgReceiveTime<'2016-10-10' полегчает значительно.
Отдельные индексы по каждому столбцу зачем?
6 фев 17, 18:15    [20186013]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
TaPaK
selis76,

так а в чём беда сделать индекс покрывающим?

Да я могу наплодить покрывающих, но хотелось бы поминимому их сделать - так как в данную таблицу много всего вставляют.
Вот например хочу сделать такой запрос добавив условие по MsgHeader
   SELECT t.MsgCounter, t.MsgReceiveTime , t.MsgHeader , t.MsgID       
           FROM [MIS_BUSBUFFER].[dbo].[msg_buffer] t 
           left join [MIS_TEST1].[dbo].[_InfoRg18168] as reg  
           ON reg._Fld18362 = t.msgcounter and reg._Fld628=0 
           where t.MsgReceiveTime>'2016-10-01'  and t.MsgReceiveTime<'2016-10-10'                          
           AND reg._Fld18362 is null  and    t.MsgHeader like '%.trade.%'    


план будет с IndexScan по IND_MsgHeader

Если расширить диапазон дат, то план поменяется и будет уже Clustered Index Scan по PK_msg_buffer

Вопрос : Если не делать покрывающий индекс по MsgCounter , MsgReceiveTime, MsgHeader - есть компромисcный набор индексов который улучшит план запроса (избавить от Index Scan)

К сообщению приложен файл. Размер - 73Kb
6 фев 17, 18:19    [20186027]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
selis76
Да я могу наплодить покрывающих
Не надо их плодить.
Не хотите делать кластерный по MsgReceiveTime, сделайте обычный с include (все остальные столбцы).
6 фев 17, 18:29    [20186057]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
selis76
Вот например хочу сделать такой запрос добавив условие по MsgHeader
план будет с IndexScan по IND_MsgHeader

Если расширить диапазон дат, то план поменяется и будет уже Clustered Index Scan по PK_msg_buffer

Вопрос : Если не делать покрывающий индекс по MsgCounter , MsgReceiveTime, MsgHeader - есть компромисcный набор индексов который улучшит план запроса (избавить от Index Scan)
Для такого запроса разумный индекс - MsgReceiveTime с инклюдом MsgCounter, MsgHeader , MsgID

А покрывающий индекс по MsgCounter , MsgReceiveTime, MsgHeader будет совершенно бесполезен, по нему можно делать только скан.

Действительно, если у вас все запросы подобные, то делайте обычный индекс на MsgReceiveTime с инклюдом ряда (или всех) колонок.
6 фев 17, 20:22    [20186347]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
selis76
Вопрос : Если не делать покрывающий индекс по MsgCounter , MsgReceiveTime, MsgHeader - есть компромисcный набор индексов который улучшит план запроса (избавить от Index Scan)
Индексы они в первую очередь для поиска по полям, а не для возврата результата. Если у вас в запросе SELECT T.ColumnA ... это вовсе не значит что надо срочно делать индекс ON T(ColumnA), толку будет ноль. В вашем случае сервер использует пересечение индексов лишь только потому что вы его в угол загнали своими xml и nvarchar(max), что он пытается всячески избегать обращения к таблице.

Ну и еще, не уверен что ваш запрос делает именно то что вы хотите:
 SELECT MsgCounter, MsgReceiveTime, MsgHeader           
           FROM [MIS_BUSBUFFER].[dbo].[msg_buffer] t
           left join  [TEST1].[dbo].[_InfoRg18168] as reg  
           ON reg._Fld18362 = t.msgcounter and reg._Fld628=0  
           where MsgReceiveTime>'2016-10-01'  and MsgReceiveTime<'2016-10-10'                          
           AND reg._Fld18362 is null    
7 фев 17, 23:16    [20190784]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
Alex_496
Member [заблокирован]

Откуда: https://www.dvbi.ru
Сообщений: 3869
selis76,

"Есть большие (100 миллионов записей) таблицы..."

Лучше бы исключить слово большие, т.к. разработчики от других вендоров будут смотреть с ухмылкой.
Вот так и складывается мнение, что MS SQL - для ларьков, а не Enterprise Core.
7 фев 17, 23:57    [20190859]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
Mind

Ну и еще, не уверен что ваш запрос делает именно то что вы хотите:

Делает - _Fld628 техническое поле, в моем случае не используется но в покрывающий индекс 1С его ставит
8 фев 17, 10:38    [20191713]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
alexeyvg
Для такого запроса разумный индекс - MsgReceiveTime с инклюдом MsgCounter, MsgHeader , MsgID

А покрывающий индекс по MsgCounter , MsgReceiveTime, MsgHeader будет совершенно бесполезен, по нему можно делать только скан.

Действительно, если у вас все запросы подобные, то делайте обычный индекс на MsgReceiveTime с инклюдом ряда (или всех) колонок.

Да уже прихожу к выводу что через include в моем случае эффективней действовать, про index intersect в ms sql как то мало информации и как им управлять
8 фев 17, 10:42    [20191741]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
selis76
через include в моем случае эффективней действовать, про index intersect в ms sql как то мало информации и как им управлять
Да дело не в "index intersect", а в том, что ваши индексы непригодны для поиска, у них неправильный список полей, просто теоретически по ним нельзя искать.
Их можно только сканить, потом делать пересечения, а потом ещё делать миллионы лукапов к требуемым полям.
Вот сервер и выбирает другие варианты.
8 фев 17, 13:21    [20192608]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
selis76
Mind
Ну и еще, не уверен что ваш запрос делает именно то что вы хотите:

Делает - _Fld628 техническое поле, в моем случае не используется но в покрывающий индекс 1С его ставит
А, понял. Не туда посмотрел. :)
9 фев 17, 23:24    [20198765]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
По той же таблице сделал
SELECT DISTINCT MsgHeader FROM [MIS_BUSBUFFER].[dbo].[msg_buffer] 

есть обычный индекс по MsgHeader (индекс из одного поля) . Судя по плану на картинке делается INDEX Scan .
Соответственно данные начинают выводится сразу , но все выводится долго минуты 4.
У меня была уверенность: если индекс организован в виде B-дерева , то достаточно просканить верхний уровень. Количество различных значений всего 5000 тыс, сама таблица содержит больше 100 млн записей.
Зачем SQL Server сканит весь индекс?

К сообщению приложен файл. Размер - 42Kb
13 фев 17, 13:09    [20207211]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
selis76
если индекс организован в виде B-дерева , то достаточно просканить верхний уровень
Вы плохо представляете устройство b-дерева. На препоследнем перед листовым уровнем не обязательно должны быть все ключи.
13 фев 17, 13:14    [20207242]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
selis76
Member

Откуда:
Сообщений: 361
У меня тогда разрыв понимания с картинками
https://www.sql.ru/articles/mssql/03013101indexes.shtml#4

Сообщение было отредактировано: 13 фев 17, 13:24
13 фев 17, 13:21    [20207268]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
selis76
У меня тогда разрыв понимания с картинками
https://www.sql.ru/articles/mssql/03013101indexes.shtml#4
Какой такой разрыв? По рисунку 5 видно на на редадущем уровне на странице 30 есть 4 ключа из 35. Как вы их все через полчуать собрались только по этой странице?
13 фев 17, 13:26    [20207303]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
Владислав Колосов
Member

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

здесь индекс сканировать выгоднее, чем кучу, т.к. он меньше кучи.
13 фев 17, 13:26    [20207305]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса без использования покрывающих индексов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76
У меня тогда разрыв понимания с картинками
https://www.sql.ru/articles/mssql/03013101indexes.shtml#4

т.е. по верхнему уровню вы хотите считать уникальные значения? нафиг тогда вообще эти деревья
13 фев 17, 13:35    [20207346]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить