Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server

Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server

Σε αυτό το άρθρο θα αναλύσουμε την διαφορά ενός πίνακα που είναι φυσικά αποθηκευμένος μέσω Clustered Rowstore Index με το να είναι μέσω Clustured Columnstore Index. Τι ακριβώς είναι τα Columnstore Indexes, πότε πρέπει να χρησιμοποιούνται και ποια είναι τα υπέρ και τα κατά τους.

Τι είναι τα Columnstore Indexes

Τα Columnstore indexes είναι μία τεχνολογία που επιτρέπει να αποθηκεύονται τα δεδομένα ενός πίνακα ανά στήλη εν αντιθέσει με τα “κλασικά” Rowstore indexes που αποθηκεύονται ανά γραμμή των εγγραφών του πίνακα. Λόγο του ότι η κάθε στήλη έχει ίδιου τύπου δεδομένα, προσφέρει έως x10 φορές μεγαλύτερη συμπίεση και x10 φορές γρηγορότερη ανάγνωση των δεδομένων.

Πώς λειτουργούν τα Columnstore Indexes

Κατά την δημιουργία του Columnstore Index οι εγγραφές χωρίζονται σε ομάδες των 1.048.576 εγγραφών (102.400 το ελάχιστο) που ονομάζονται Row Groups, έπειτα η κάθε στήλη που έχει το Row Group χωρίζετε και συμπιέζετε σε ομάδες που ονομάζονται Column Segments. Από την στιγμή που το ελάχιστο για ένα Row Group είναι οι 102.400 εγγραφές όταν υπάρχουν εγγραφές που περισσεύουν, οι παραπάνω εγγραφές πάνε σε έναν ειδικό χώρο που ονομάζετε Deltastore. Στον χώρο του Deltastore τα δεδομένα εξακολουθούν να είναι Row based και να μην έχουν την συμπίεση του Column Store.

Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
docs.microsoft.com

Ποια είναι τα πλεονεκτήματα των Columnstore Indexes

  • Τα Columnstore Indexes όπως αναφέραμε και στην αρχή λόγο του ότι χωρίζουν τα δεδομένα σε όμαδες στηλών, παρέχουν υψηλή συμπίεση των δεδομενων που οδηγεί στο να απαιτήται λίγότερος αποθηκευτικός χώρος.
  • Η υψηλή συμπίεση σε συνδιασμό με το ότι τα queries σε Columnstore Indexes τρέχουν σε batch mode (optimized για πολλαπλές εγγραφές) αντί για row mode παρέχουν γρηγορότερη ανάγνωση των δεδομένων.
  • Τα queries που χρειάζονται συγκεκριμένες μόνο στήλες απο τους πίνακες απαιτούν μειωμένο I/O σε σχέση με το Rowstore.
  • Τα analytical queries που κάνουν χρήση aggregate functions όπως SUM, AVG και GROUPING εκτελούνται σε ελάχιστους χρόνους.

Ποια είναι τα μειονεκτήματα των Columnstore Indexes

  • Η ενημέρωση των πίνάκων σε OLTP workloads με DML όπως insert,update και delete καθυστερεί κατά πολύ σε σχέση με το Rowstore.
  • Πρέπει η κάθε στήλη να χωράει σε ένα και μόνο page π.χ ένα πεδίο που είναι varchar(max) δεν υποστηρίζεται και αν θέλουμε να το κάνουμε χρήση θα πρέπει να κρατήσουμε μόνο τους πρώτους 4000 χαρακτήρες (LEFT(column,4000)).
  • Η αναζήτηση συγκεκριμένων εγγραφών συνήθως απαιτεί περισσότερο χρόνο.

Το παράδειγμα

Έχουμε έναν πίνακα με ένα κλασικό Clustered Rowstore Index στο στο ID. Πριν κάνουμε το οτιδήποτε ενεργοποιούμε τα στατιστικά για χρόνους και I/O:

set statistics time,io on;
go

SELECT * FROM Comments;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
01

Φτιάχνουμε έναν δεύτερο πίνακα αντιγραφή του ίδιου. Επειδή έχει ένα πεδίο που το μέγεθός του ξεπερνάει το ένα page (8k) που πρέπει να έχει για να δημιουργηθεί το Columnstore Index. Για να χωράει σε ένα page κρατάμε μόνο τους πρώτους 4000 χαρακτήρες:

*Το order είναι για να φτιαχτεί ο πίνακας με ανακατεμένες τις εγγραφές προς το ID και την ημερομηνία. Το γιατί θα το δούμε παρακάτω.

Μετά δημιουργούμε το Clustured Columnstore Index:

--drop table comments_columnstore
select id,CreationDate,PostId,left(Text,4000) as Text,score,userid
into Comments_Columnstore
from Comments order by score desc; 
GO

create clustered columnstore index idx_comments_clmstore on [dbo].[Comments_Columnstore]; 
GO

Διαβάζοντας τις εγγραφές του Rowstore πίνακα για ένα μήνα, βλέπουμε ότι κάνει 7 δευτερόλεπτα:

SELECT id,CreationDate,PostId,Text,score
	FROM Comments
where
1=1
and CreationDate between '2010-01-01' and '2010-02-01'
order by CreationDate;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
02

Αντίστοιχα στον Columnstore πίνακα κάνει μόνο 5 δευτερόλεπτα. Επίσης μπορούμε να δούμε στα μηνύματα ότι αυτή τη φορά κάνει Segment reads:

SELECT id,CreationDate,PostId,Text,score
	FROM Comments_Columnstore
where
1=1
and CreationDate between '2010-01-01' and '2010-02-01'
order by CreationDate;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
03

Επειδή όμως τα Row Groups χωρίζονται αναλόγως πως είναι οι εγγραφές στον πίνακα και εμείς τις περάσαμε ανακατεμένες, αν θέλουμε να τις ταξινομήσουμε ανά ημερομηνία θα πρέπει να φτιάξουμε Clustered Rowstore Index πάνω στον πίνακα σβήνοντας το Columnstore και έπειτα να ξαναφτιάξουμε το Columnstore σβήνοντας το Rowstore.

Αυτό το κάνουμε καθώς στα Columnstore Indexes δεν μπορούμε ακόμα να ορίσουμε με ποίο πεδίο να γίνουν order τα Segment Row Groups.

Αν το κάνουμε αυτό και ξανατρέξουμε το query θα δούμε ότι από τα 5 έπεσε στο 1.5 δευτερόλεπτο:

create clustered index idx_comments_clmstore on [dbo].[Comments_Columnstore](CreationDate) with (drop_Existing = on);
go

create clustered columnstore index idx_comments_clmstore on [dbo].[Comments_Columnstore]  with (drop_Existing = on);
go

SELECT id,CreationDate,PostId,Text,score
	FROM Comments_Columnstore
where
1=1
and CreationDate between '2010-01-01' and '2010-02-01'
order by CreationDate;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
04

Αν τρέξουμε aggregate function στον Rowstore πίνακα θα δούμε ότι θα κάνει 5 δευτερόλεπτα:

SELECT score,count(id) as Count
	FROM Comments
group by score;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
05

Αν όμως το τρέξουμε στον Columnstore πίνακα θα δούμε ότι θα κάνει μόνο 10 χιλιοστά του δευτερολέπτου:

SELECT score,count(id) as Count
	FROM Comments_Columnstore
group by score;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
06

Αν κάνουμε 100.000 inserts στον Rowstore πίνακα θα κάνει 41 δευτερόλεπτα:

declare @i int
set @i = 1

WHILE @i < 100000
BEGIN
insert into comments(CreationDate,PostId,Score,Text,Userid)
values ('2010-09-06 08:09:52.330',35314,3,'Test Comment',3);
set @i=@i+1;
END;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
07

Στον Columnstore πίνακα όμως τα 100.000 inserts θα αργήσουν και θα κάνουν 54 δευτερόλεπτα.

declare @i int
set @i = 1

WHILE @i < 100000
BEGIN
insert into comments_columnstore(CreationDate,PostId,Score,Text,Userid)
values ('2010-09-06 08:09:52.330',35314,3,'Test Comment',3);
set @i=@i+1;
END;
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
08

Αντίστοιχα αν κάνουμε delete στον Rowstore πίνακα θα κάνει 5 δευτερόλεπτα:

delete from comments where Text = 'Test Comment';
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
09

Ενώ στον Columnstore πίνακα το delete θα κάνει σχεδόν 10 δευτερόλεπτα (δηλαδή τον διπλάσιο χρόνο):

delete from comments_columnstore where Text = 'Test Comment';
Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
10

Πότε να χρησιμοποιήσουμε έναν Columnstore Index

Τα Columnstore Indexes έχουν δημιουργηθεί κυρίως για χρήση σε Data warehouses (OLAP). Φτιάχνονται στα Fact tables και στους πολύ μεγάλους Dimensional tables. Η χρήση τους βελτιώνει δραματικά την απόδοση καθώς απαιτήται μεγάλος όγκος δεδομένων.

Επίσης με τη δημιουργία Nonclustered Columnstore Index σε Rowstore πίνακες μπορούμε να αυξήσουμε την απόδοση analytical queries που κάνουν χρήση aggregate functions όπως SUM, AVG και GROUPING εκτελούνται σε ελάχιστους χρόνους.

Στον ιστότοπο columnscore.com θα βρείτε ένα ωραίο quiz με ερωτήσεις του Brent Ozar για το πότε κάνουμε χρήση ή όχι έναν Columnstore Index.

Πηγές:

Μοιράσου το

Αφήστε μία απάντηση