Τι είναι τα Columnstore Indexes και πότε τα χρησιμοποιούμε στον SQL Server
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Σε αυτό το άρθρο θα αναλύσουμε την διαφορά ενός πίνακα που είναι φυσικά αποθηκευμένος μέσω 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
- Τα 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;
Φτιάχνουμε έναν δεύτερο πίνακα αντιγραφή του ίδιου. Επειδή έχει ένα πεδίο που το μέγεθός του ξεπερνάει το ένα 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 πίνακα κάνει μόνο 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;
Επειδή όμως τα 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;
Αν τρέξουμε aggregate function στον Rowstore πίνακα θα δούμε ότι θα κάνει 5 δευτερόλεπτα:
SELECT score,count(id) as Count FROM Comments group by score;
Αν όμως το τρέξουμε στον Columnstore πίνακα θα δούμε ότι θα κάνει μόνο 10 χιλιοστά του δευτερολέπτου:
SELECT score,count(id) as Count FROM Comments_Columnstore group by score;
Αν κάνουμε 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 πίνακα όμως τα 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;
Αντίστοιχα αν κάνουμε delete στον Rowstore πίνακα θα κάνει 5 δευτερόλεπτα:
delete from comments where Text = 'Test Comment';
Ενώ στον Columnstore πίνακα το delete θα κάνει σχεδόν 10 δευτερόλεπτα (δηλαδή τον διπλάσιο χρόνο):
delete from comments_columnstore where Text = 'Test Comment';
Πότε να χρησιμοποιήσουμε έναν 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.
