Indeks programu SQL Server a statystyki

Jakie są różnice pomiędzy CREATE INDEXa CREATE STATISTICSi kiedy należy użyć każdego?

Indeksy przechowują rzeczywiste dane (strony danych lub strony indeksów w zależności od typu indeksu, o którym mówimy), a statystyki przechowują dystrybucję danych. Dlatego CREATE INDEXDDL będzie tworzyć indeks (klastrowany, nieklastrowany itp.) I CREATE STATISTICSjest DDL do tworzenia statystyk dotyczących kolumn w tabeli.

Polecam przeczytać o tych aspektach danych relacyjnych. Poniżej znajduje się kilka artykułów dla początkujących, wprowadzających. Są to bardzo szerokie tematy, dlatego informacje na ich temat mogą być bardzo szerokie i bardzo głębokie. Przeczytaj ogólną ideę ich poniżej i zadawaj bardziej szczegółowe pytania, gdy się pojawią.

Odwołanie BOL do organizacji tabel i indeksów
Odniesienie BOL do struktury indeksów klastrowych
Odniesienie BOL do struktur indeksów nieklastrowanych
SQL Server Central na temat wprowadzenia do indeksów
Odniesienie BOL do statystyk

Oto działający przykład, aby zobaczyć te dwie części w akcji (skomentowane w celu wyjaśnienia):

use testdb;
go

create table MyTable1
(
    id int identity(1, 1) not null,
    my_int_col int not null
);
go

insert into MyTable1(my_int_col)
values(1);
go 100

-- this statement will create a clustered index
-- on MyTable1.  The index key is the id field
-- but due to the nature of a clustered index
-- it will contain all of the table data
create clustered index MyTable1_CI
on MyTable1(id);
go


-- by default, SQL Server will create a statistics
-- on this index.  Here is proof.  We see a stat created
-- with the name of the index, and the consisting stat 
-- column of the index key column
select
    s.name as stats_name,
    c.name as column_name
from sys.stats s
inner join sys.stats_columns sc
on s.object_id = sc.object_id
and s.stats_id = sc.stats_id
inner join sys.columns c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
where s.object_id = object_id('MyTable1');


-- here is a standalone statistics on a single column
create statistics MyTable1_MyIntCol
on MyTable1(my_int_col);
go

-- now look at the statistics that exist on the table.
-- we have the additional statistics that's not necessarily
-- corresponding to an index
select
    s.name as stats_name,
    c.name as column_name
from sys.stats s
inner join sys.stats_columns sc
on s.object_id = sc.object_id
and s.stats_id = sc.stats_id
inner join sys.columns c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
where s.object_id = object_id('MyTable1');


-- what is a stat look like?  run DBCC SHOW_STATISTICS
-- to get a better idea of what is stored
dbcc show_statistics('MyTable1', 'MyTable1_CI');
go

Oto jak może wyglądać próbna próbka statystyk:

Zauważ, że statystyki zawierają ograniczenie dystrybucji danych. Pomagają SQL Server w ustaleniu optymalnego planu. Dobrym tego przykładem jest wyobraź sobie, że żyjesz ciężkim przedmiotem. Gdybyś wiedział, ile waży ta waga, ponieważ była na niej oznaczona waga, ustaliłbyś najlepszy sposób na podnoszenie i przy pomocy jakich mięśni. Tak działa SQL Server ze statystykami.

-- create a nonclustered index
-- with the key column as my_int_col
create index IX_MyTable1_MyIntCol
on MyTable1(my_int_col);
go

-- let's look at this index
select
    object_name(object_id) as object_name,
    name as index_name,
    index_id,
    type_desc,
    is_unique,
    fill_factor
from sys.indexes
where name = 'IX_MyTable1_MyIntCol';

-- now let's see some physical aspects
-- of this particular index
-- (I retrieved index_id from the above query)
select *
from sys.dm_db_index_physical_stats
(
    db_id('TestDB'),
    object_id('MyTable1'),
    4,
    null,
    'detailed'
);

Z powyższego przykładu wynika, że ​​indeks faktycznie zawiera dane (w zależności od typu indeksu strony liści będą różne).

Ten post pokazał tylko bardzo bardzo krótki przegląd tych dwóch dużych aspektów SQL Server. Oba mogą zawierać rozdziały i książki. Przeczytaj niektóre z odniesień, a wtedy będziesz lepiej rozumieć.