Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: ←Ctrl назад 1 [2] 3 вперед Ctrl→ все |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
Гавриленко Сергей Алексеевич, Вы наверное очень удивитесь, но если ... CREATE INDEX IX_testindex ON testindex(a,b) GO set statistics IO ON go SELECT * FROM testindex WHERE a = 5 and b = 'X' GO set statistics IO off GO DROP INDEX IX_testindex ON testindex GO CREATE INDEX IX_testindex ON testindex(b,a) GO set statistics IO ON go SELECT * FROM testindex WHERE a = 5 and b = 'X' GO set statistics IO off GO DROP INDEX IX_testindex ON testindex GO -------------------------------------------------------------------------------------------- Table 'testindex'. 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. Table 'testindex'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Разница в наших показаниях заключается в том что при использовании индекса №1 сервер использует статистики индекса №2. В вашем случае для построения статистики по полю "b" был сделан FULLSCAN и значение "Х" попало в статистики. Получается что-б ваш пример работал, вы должны иметь индексы по ВСЕМ полям, либо делать апдейт статистик с опцией FULLSCAN. Но даже в вашем случае если вы запустите запрос БЕЗ указания индекса, то сервер выберет индекс №2. |
12 окт 18, 04:32 [21702095] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
Mind, Вообще-то планы разные, так как индекс по полю А "не видит" реального распределения значений по полю "Б" если по полю "Б" нет полной статистики. |
12 окт 18, 04:38 [21702096] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
У меня получился такой-же результат как и у вас, когда я запустил тест в tempdb. Обнаружилась интересная закономерность. В tempdb статистика по полю "b" включает значение "X", а в пользовательской базе нет. В этом и есть разница в IO. Если надо, могу дать скриншоты статистик, хотя я уже и давал скриншоты разных планов. |
||
12 окт 18, 04:51 [21702103] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
Статистика по (a,b) не может содержать распределение по b, ибо гистограмма хранится только для первого столбца. В любой БД. Соответственно, статистика по (b,a) будет содержать такое распределение. Опять же в любой БД.
Репро давайте, подтверждающее ваши слова. |
||||
12 окт 18, 10:10 [21702226] Ответить | Цитировать Сообщить модератору |
SIMPLicity_ Member Откуда: (((@))) Сообщений: 8821 |
like ? ================== PS Как вариант like 'АБэЦэ...%' (в идеале должен быть seek вместо scan).... |
||||
12 окт 18, 14:35 [21702571] Ответить | Цитировать Сообщить модератору |
Mind Member Откуда: Лучший город на Земле Сообщений: 2322 |
Если в ваш пример добавить селективности по полю A DROP TABLE testindex GO CREATE TABLE testindex (a INT, b CHAR(1)) GO ;WITH xx AS (SELECT * FROM (VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b) ) INSERT INTO testindex(a,b) SELECT x1.* FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7; GO INSERT INTO testindex(a,b) VALUES (1,'A') INSERT INTO testindex(a,b) VALUES (2,'A') INSERT INTO testindex(a,b) VALUES (3,'A') INSERT INTO testindex(a,b) VALUES (4,'A') INSERT INTO testindex(a,b) VALUES (5,'X') GO то сервер все равно продолжит выбирать индекс (b,a)!
Можно построить оба индекса одновременно, чтобы исключить
|--Index Seek(OBJECT:([TEST].[dbo].[testindex].[IX_testindex_2]), SEEK:([TEST].[dbo].[testindex].[b]='X' AND [TEST].[dbo].[testindex].[a]=(5)) ORDERED FORWARD) |
|||||||||||
13 окт 18, 00:48 [21703115] Ответить | Цитировать Сообщить модератору |
SIMPLicity_ Member Откуда: (((@))) Сообщений: 8821 |
чуть меняем условие : set statistics profile on; go SELECT a, b FROM testindex WHERE a =5 and b ! = 'X' go set statistics profile off; go Получаем картину: StmtText: |--Index Seek(OBJECT:([tempdb].[dbo].[testindex].[IX_testindex_1]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] < 'X' OR [tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] > 'X') ORDERED FORWARD) при Argument: OBJECT:([tempdb].[dbo].[testindex].[IX_testindex_1]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] < 'X' OR [tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] > 'X') ORDERED FORWARD Выбор в пользу другого индекса при НЕочевидном (для меня, по крайней мере) преобразовании... ![]() |
|||||||||||||
14 окт 18, 14:10 [21703619] Ответить | Цитировать Сообщить модератору |
SIMPLicity_ Member Откуда: (((@))) Сообщений: 8821 |
PS Вообще, вы куда-то слишком в дебри углубились... PPS Мне так кажется.... |
14 окт 18, 14:18 [21703624] Ответить | Цитировать Сообщить модератору |
Mr. X Member Откуда: Сообщений: 37 |
SIMPLicity_, Да какие уж там дебри, все крутится (в основном) вокруг статистики. Из которой тут упоминали только селективность (selectivity), хотя в статистике присутствуют плотность (density), мощьность (cardinality) и гистограмма (histogram). Которые SQL так же использует для оценок. |
14 окт 18, 18:13 [21703675] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
Во первых: есть 2 статистики по обоим полям и когда скуль строит план, то он смотрит на обе. (закрыли этот вопрос?) Во вторых: при запуске одного и того-же кода в разных базах происходит различная обработка алгоритма выборки сэмла. В одной базе последняя страница входит в сэмпл, а в другой нет, от этого различные статистики и планы. (какого рода репо вам предоставить? Потестируйте в разных базах и посмотрите на сэмплы и статистики.) В третьих: При двух индексах a-b и b-a скуль почему-то выбирает второй индекс. Сможете ответить на вопрос "почему?"? |
||||||
15 окт 18, 06:02 [21703818] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
Да, вы правы. В случае выбора между двумя этими индексами скуль не видит разницы так как планы у них с одинаковой стоимостью. |
||
15 окт 18, 07:33 [21703831] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
Но вы продолжаете фантазировать... Особо порадовала зависимость IO запроса от статистики...
Попробуйте поменять порядок создания индексов. Ну и напоследок вопрос - каким образом для индексов (a, b) и (b, a), при предикате a = ... and b = ..., селективность столбцов индекса будет влиять на операцию поиска в нем? |
||||||
15 окт 18, 13:43 [21704160] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
Вы мне не верите, но сами проверить не желаете. Вот вам скриншот. К сообщению приложен файл. Размер - 16Kb |
||||
15 окт 18, 17:34 [21704398] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
invm, Разница в IO обусловлена тем что статистики не захватили FULLSAMPLE. Прочитано только 7% всех записей и значения с последней страницы остались неучтёнными. DECLARE @SQL VARCHAR(1000); SELECT @SQL = 'DBCC SHOW_STATISTICS (''dbo.testindex'',''' + name + ''');' FROM sys.stats as s INNER JOIN sys.stats_columns AS c ON c.object_id = s.object_id AND s.stats_id = c.stats_id WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 1 EXEC (@SQL) SELECT @SQL = 'DBCC SHOW_STATISTICS (''dbo.testindex'',''' + name + ''');' FROM sys.stats as s INNER JOIN sys.stats_columns AS c ON c.object_id = s.object_id AND s.stats_id = c.stats_id WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 2 EXEC (@SQL) К сообщению приложен файл. Размер - 13Kb |
15 окт 18, 17:39 [21704401] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
invm, Как только мы перестроим статистики с полным сканированием то получим результат, который нам продемонстрировал Сергей Гавриленко. DECLARE @SQL VARCHAR(1000); SELECT @SQL = 'UPDATE STATISTICS dbo.testindex ' + name + ' WITH FULLSCAN;' FROM sys.stats as s INNER JOIN sys.stats_columns AS c ON c.object_id = s.object_id AND s.stats_id = c.stats_id WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 1 EXEC (@SQL) SELECT @SQL = 'UPDATE STATISTICS dbo.testindex ' + name + ' WITH FULLSCAN;' FROM sys.stats as s INNER JOIN sys.stats_columns AS c ON c.object_id = s.object_id AND s.stats_id = c.stats_id WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 2 EXEC (@SQL) К сообщению приложен файл. Размер - 16Kb |
15 окт 18, 17:44 [21704404] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37155 |
Откуда появляется четвертое логическое чтение -- не понятно. (Не факт, что оно есть на самом деле и не врет statistics io.) Опять же, если выключить автосоздание статистик на базе, то чтений будет 4 в обоих вариантах, а если индекс прибить руками, то 3 в обоих вариантах. Однако при глубине индекса в три уровня, четвертое чтение не может быть из дерева индекса -- в индексе просто читать больше нечего. Короче, тут не в структуре индекса дело, а в каких-то сайд-эффектах от статистики. |
15 окт 18, 17:47 [21704406] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
Видимо вы так не удосожились хотя бы его результаты посмотреть.
|
||||
15 окт 18, 17:57 [21704408] Ответить | Цитировать Сообщить модератору |
Mind Member Откуда: Лучший город на Земле Сообщений: 2322 |
|
||
15 окт 18, 21:12 [21704535] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
|
|
15 окт 18, 21:23 [21704544] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
В предыдущем примере накосячено.
|
|
15 окт 18, 22:35 [21704591] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
invm, Ну и зачем оно 2 раза читает нижнюю страничку индекса? |
15 окт 18, 23:25 [21704611] Ответить | Цитировать Сообщить модератору |
SandalTree Member Откуда: Перехлёсток восьми батог Сообщений: 28146 |
Mind, Вот нашёл более приближённый пример того что индекс с большей селективностью лучше работает: CREATE TABLE dbo.testindex2(ID INT IDENTITY (0,5), i INT, a CHAR(1)) GO CREATE INDEX ix_testindex1 ON dbo.testindex2(id,i) GO CREATE INDEX ix_testindex2 ON dbo.testindex2(i,id) GO DECLARE @l INT = 2010; DECLARE @m INT = @l; WHILE @m > 0 BEGIN INSERT INTO dbo.testindex2(i,a) SELECT TOP (@m) @l-@m, 'a' FROM sys.messages SET @m -= 5; END GO SET STATISTICS IO ON GO SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex1) WHERE i BETWEEN 0 AND 1000 AND id BETWEEN 0 AND 1000 OPTION (RECOMPILE) GO SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex2) WHERE i BETWEEN 0 AND 1000 AND id BETWEEN 0 AND 1000 OPTION (RECOMPILE) GO SET STATISTICS IO OFF
Распределение данных надумано и не реалистично, но это просто пример селективности. В реальной жизни скорее всего могут быть более серьёзные случаи. |
||
16 окт 18, 03:36 [21704670] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37155 |
SandalTree, Ясен пень, что здесь будут разные seek-предикаты, в отличие от запроса с равенством. |
16 окт 18, 10:18 [21704813] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
А зачем - это вам объяснять. Это же ваша теория "О влиянии неучтенных значений в статистике на количество чтений во время выполнения запроса при идентичных планах выполнения".
Не хотите переформулировать ваше "общее правило"? |
||||
16 окт 18, 11:11 [21704897] Ответить | Цитировать Сообщить модератору |
msLex Member Откуда: Сообщений: 8730 |
Ваше различие в чтениях вызвано не селективностью индекса, а количеством записей, попадающим в диапазон between по одному и по второму полю вот вам чуть измененных скрипт, в котором я просто "размазал" значение поля i, домножив его на 100, но не менял селективность ни одного из полей, и получил противоположный вашему результат if object_id('dbo.testindex2') is not null drop table dbo.testindex2 CREATE TABLE dbo.testindex2(ID INT IDENTITY (0,5), i INT, a CHAR(1)) GO DECLARE @l INT = 2010; DECLARE @m INT = @l; WHILE @m > 0 BEGIN INSERT INTO dbo.testindex2(i,a) SELECT TOP (@m) @l-@m * 100, 'a' FROM sys.messages SET @m -= 5; END GO CREATE INDEX ix_testindex1 ON dbo.testindex2(id,i) GO CREATE INDEX ix_testindex2 ON dbo.testindex2(i,id) GO SET STATISTICS IO ON GO SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex1) WHERE i BETWEEN 1 AND 100000 AND id BETWEEN 1 AND 100000 OPTION (RECOMPILE) GO SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex2) WHERE i BETWEEN 1 AND 100000 AND id BETWEEN 1 AND 100000 OPTION (RECOMPILE) GO SET STATISTICS IO OFF
|
||||||
16 окт 18, 13:58 [21705221] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: ←Ctrl назад 1 [2] 3 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |