SandalTreeпытаюсь высказать своё мнениеНе мнение, а утверждение.
SandalTreeВ этом тексте говорится что индексы нужны только для тех колонок, которые используются в WHERE или JOIN.В этом тексте говорится как определять порядок столбцов в индексе. И вашего утверждения в нем нет. Именно для этого он и был процитирован.
SandalTreeОднако он не говорит что "нужно создавать отдельные индексы для ВСЕХ полей использующихся в WHERE или JOIN"А кто говорит, что нужно именно так?
SandalTreeКстати, вы попытались меня обидеть, но так и не сказали в чём-же я не прав.Да? Предложением поразмышлять?
Касаемого вашего тестаБестолку анализировать запросы с уровнем оптимизации TRIVIAL
Поэтому немного изменим данные и запросы 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
/*
Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17134: ) (Hypervisor)
*/
use tempdb;
go
CREATE TABLE testindex (a INT, b varchar(30), dummy char(100))
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,dummy) SELECT x1.*, 'a'
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,dummy) VALUES (5,'X','a')
GO
CREATE INDEX IX_testindex ON testindex(a,b)
GO
1. 1.
2.
3.
4.
5.
6.
set statistics profile on;
go
set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X' option (recompile); set statistics io off;
go
set statistics profile off;
go
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions11SELECT a, b FROM testindex WHERE a = 5 and b = 'X' option (recompile)110NULLNULLNULLNULL500000,5NULLNULLNULL1,604764NULLNULLSELECT0NULL11 |--Index Seek(OBJECT:([tempdb].[dbo].[testindex].[IX_testindex]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X') ORDERED FORWARD)121Index SeekIndex SeekOBJECT:([tempdb].[dbo].[testindex].[IX_testindex]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X') ORDERED FORWARD[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b]500000,51,0546060,5501575161,604764[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b]NULLPLAN_ROW01 1.
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.
2. 1.
2.
3.
4.
5.
6.
set statistics profile on;
go
set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X' option (recompile, querytraceon 9481/*turn on "old" Cardinality Estimator*/); set statistics io off;
go
set statistics profile off;
go
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions11SELECT a, b FROM testindex WHERE a = 5 and b = 'X' option (recompile, querytraceon 9481/*turn on "old" Cardinality Estimator*/)110NULLNULLNULLNULL1NULLNULLNULL0,0032831NULLNULLSELECT0NULL11 |--Index Seek(OBJECT:([tempdb].[dbo].[testindex].[IX_testindex]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X') ORDERED FORWARD)121Index SeekIndex SeekOBJECT:([tempdb].[dbo].[testindex].[IX_testindex]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X') ORDERED FORWARD[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b]10,0031250,0001581160,0032831[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b]NULLPLAN_ROW01
1.
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.
3. 1.
2.
3.
4.
5.
6.
set statistics profile on;
go
set statistics io on; SELECT a, b, dummy FROM testindex WHERE a = 5 and b = 'X' option (recompile); set statistics io off;
go
set statistics profile off;
go
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions11SELECT a, b, dummy FROM testindex WHERE a = 5 and b = 'X' option (recompile)110NULLNULLNULLNULL500000,5NULLNULLNULL11,99588NULLNULLSELECT0NULL11 |--Table Scan(OBJECT:([tempdb].[dbo].[testindex]), WHERE:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X'))121Table ScanTable ScanOBJECT:([tempdb].[dbo].[testindex]), WHERE:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X')[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b], [tempdb].[dbo].[testindex].[dummy]500000,510,895721,10015811611,99588[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b], [tempdb].[dbo].[testindex].[dummy]NULLPLAN_ROW01
1.
Table 'testindex'. Scan count 1, logical reads 14706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
4. 1.
2.
3.
4.
5.
6.
set statistics profile on;
go
set statistics io on; SELECT a, b, dummy FROM testindex WHERE a = 5 and b = 'X' option (recompile, querytraceon 9481/*turn on "old" Cardinality Estimator*/); set statistics io off;
go
set statistics profile off;
go
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions11SELECT a, b, dummy FROM testindex WHERE a = 5 and b = 'X' option (recompile, querytraceon 9481/*turn on "old" Cardinality Estimator*/)110NULLNULLNULLNULL1NULLNULLNULL0,00657038NULLNULLSELECT0NULL11 |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]) OPTIMIZED)121Nested LoopsInner JoinOUTER REFERENCES:([Bmk1000]) OPTIMIZEDNULL104,18E-061160,00657038[tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b], [tempdb].[dbo].[testindex].[dummy]NULLPLAN_ROW0100 |--Compute Scalar(DEFINE:([Expr1003]=BmkToPage([Bmk1000])))142Compute ScalarCompute ScalarDEFINE:([Expr1003]=BmkToPage([Bmk1000]))[Expr1003]=BmkToPage([Bmk1000])10,0031250,0001581240,0032831[Bmk1000], [tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b], [Expr1003]NULLPLAN_ROW0111 | |--Index Seek(OBJECT:([tempdb].[dbo].[testindex].[IX_testindex]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X') ORDERED FORWARD)154Index SeekIndex SeekOBJECT:([tempdb].[dbo].[testindex].[IX_testindex]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b]='X') ORDERED FORWARD[Bmk1000], [tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b]10,0031250,0001581240,0032831[Bmk1000], [tempdb].[dbo].[testindex].[a], [tempdb].[dbo].[testindex].[b]NULLPLAN_ROW0111 |--RID Lookup(OBJECT:([tempdb].[dbo].[testindex]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)172RID LookupRID LookupOBJECT:([tempdb].[dbo].[testindex]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD[tempdb].[dbo].[testindex].[dummy]10,0031250,00015811070,0032831[tempdb].[dbo].[testindex].[dummy]NULLPLAN_ROW01
1.
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.
|
