SQL Server-ის ინდექსები და execution plan-ები, განმარტებით

SQL Server-ის ნელი query-ების უმეტესობა სამიდან ერთ-ერთი მიზეზით არის ნელი: არ არსებობს ინდექსი, რომელზეც query-ს seek შეუძლია, ინდექსი არსებობს, მაგრამ query ისეა დაწერილი, რომ მისი გამოყენება შეუძლებელია, ან ინდექსი row-ებს სწრაფად პოულობს, შემდეგ კი ყოველ სვეტს ცხრილიდან თითო row-ით იღებს. execution plan რამდენიმე წამში გეტყვის, რომელია, თუ იცი, სად უნდა შეხედო. ეს გზამკვლევი განიხილავს, რა არის სინამდვილეში SQL Server-ის ინდექსები, რით განსხვავდება clustered და nonclustered ინდექსები, რისთვის არის included სვეტები და როგორ წაიკითხო plan მარჯვნიდან მარცხნივ, სანამ ძვირი ოპერატორი დამალვას შეწყვეტს.

მაგალითები SQL Server 2022-ს იყენებს და აქ ყველაფერი Express ედიციასაც ეხება - ინდექსის ყველა ტიპი, filtered და columnstore ინდექსების ჩათვლით, ყველა ედიციაში ხელმისაწვდომია SQL Server 2016 Service Pack 1-დან. რაც Express-ს აკლია, ეს online rebuild-ის ოფციებია, რაც 10 GB-ით შეზღუდულ ბაზაზე იმაზე ნაკლებად მნიშვნელოვანია, ვიდრე ხალხს ჰგონია.

როგორ ინახავს SQL Server ცხრილს#

SQL Server-ის ინდექსი B-tree-ა: root გვერდი, რამდენიმე შუალედური დონე და leaf გვერდები ქვემოთ, თითოეული გვერდი 8 KB. გასაღების ძებნა root-იდან ქვემოთ მიდის და თითო დონეზე ერთ გვერდს ეხება. ათმილიონ row-იან ცხრილს ჩვეულებრივ სამ-ოთხ დონიანი ინდექსი აქვს, ამიტომ ერთი row-ის პოვნა სამი-ოთხი გვერდის წაკითხვა ჯდება და არა მთელი ცხრილისა.

ცხრილის ორი სახე არსებობს:

• Clustered ცხრილს აქვს clustered ინდექსი, და მისი leaf დონე თავად ცხრილია. row-ები ინდექსის შიგნით clustered გასაღების მიმდევრობით ინახება. ცხრილზე მხოლოდ ერთი clustered ინდექსი შეიძლება იყოს, რადგან row-ები ფიზიკურად მხოლოდ ერთი გზით შეიძლება დალაგდეს.
• Heap-ს clustered ინდექსი არ აქვს. row-ები იქ დევს, სადაც ადგილი იყო, და თითოეულს ფიზიკური row-ის იდენტიფიკატორით (ფაილი, გვერდი, slot) მიმართავენ.

PRIMARY KEY-ის გამოცხადება ნაგულისხმევად მასზე clustered ინდექსს ქმნის, თუ ცხრილს უკვე არ აქვს clustered ინდექსი ან თუ PRIMARY KEY NONCLUSTERED-ს არ დაწერ. სწორედ ამ ნაგულისხმევის გამოა აპლიკაციის ბაზაში თითქმის ყველა ცხრილი clustered int ან bigint IDENTITY სვეტზე, და ცხრილების უმეტესობისთვის ეს კარგი არჩევანია: გასაღები ვიწროა, უნიკალურია, არასოდეს იცვლება და ყოველთვის იზრდება, ამიტომ ახალი row-ები ბოლო გვერდზე ხვდება და შუაში გვერდებს არ ყოფს.

Heap-ები გონივრული არჩევანია staging ცხრილებისთვის, რომლებსაც ტვირთავ და შემდეგ ერთხელ კითხულობ. ყველაფრისთვის, რაც განახლდება, მათ ძველი პრობლემა აქვთ: როცა განახლებული row თავის გვერდზე აღარ ეტევა, SQL Server მას გადაიტანს და უკან forwarding pointer-ს ტოვებს, კითხვა კი შემდეგ ამ pointer-ებს მიჰყვება. აპლიკაციის ცხრილებს clustered ინდექსი მიეცი.

Nonclustered ინდექსები და included სვეტები#

nonclustered ინდექსი ცალკე B-tree-ა, რომელიც თავის გასაღების სვეტებს და row-ზე უკან მიმთითებელს ინახავს. clustered ცხრილზე ეს მიმთითებელი clustered გასაღებია; heap-ზე - row-ის იდენტიფიკატორი. ცხრილს შეიძლება ჰქონდეს 999-მდე nonclustered ინდექსი, რაც დაახლოებით 990-ით მეტია, ვიდრე ნებისმიერ ცხრილს უნდა ჰქონდეს.

CREATE TABLE dbo.Orders (    OrderId     int IDENTITY(1,1) NOT NULL PRIMARY KEY,   -- clustered by default    CustomerId  int           NOT NULL,    Status      tinyint       NOT NULL,    CreatedAt   datetime2(0)  NOT NULL,    Total       decimal(12,2) NOT NULL,    Notes       nvarchar(max) NULL);CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_CreatedAt    ON dbo.Orders (CustomerId, CreatedAt)    INCLUDE (Status, Total);

გასაღების სვეტები, CustomerId და CreatedAt, დალაგებული და საძიებოა. INCLUDE სვეტები მხოლოდ leaf დონეზე ინახება: მათზე ძებნა ან დალაგება შეუძლებელია, მაგრამ query-ს, რომელსაც ისინი სჭირდება, შეუძლია ისინი პირდაპირ ინდექსიდან წაიკითხოს და ცხრილში არ დაბრუნდეს. ინდექსს, რომელიც query-ის მიერ გამოყენებულ ყველა სვეტს შეიცავს, covering ეწოდება, და ეს SQL Server-ის tuning-ში ყველაზე სასარგებლო ფორმაა.

გასაღებში სვეტების მიმდევრობა მნიშვნელოვანია და წესი მარტივია: ჯერ ტოლობა, შემდეგ დიაპაზონი ან დალაგება. ეს ინდექსი ყველა ამას ემსახურება:

-- Seek on CustomerId, rows already in CreatedAt order: no sort neededSELECT OrderId, CreatedAt, TotalFROM dbo.OrdersWHERE CustomerId = 42ORDER BY CreatedAt DESC;-- Seek on CustomerId, then a range on CreatedAtSELECT OrderId, Status, TotalFROM dbo.OrdersWHERE CustomerId = 42  AND CreatedAt >= '2026-09-01';

ის არ ეხმარება ცალკე WHERE CreatedAt >= '2026-09-01'-ს, რადგან ინდექსი ჯერ მომხმარებლით არის დალაგებული, სექტემბერში კი ყველა მომხმარებელს აქვს row-ები. ამ query-ს სჭირდება ინდექსი, რომელიც CreatedAt-ით იწყება.

რამდენიმე ზღვარი, რომლის ცოდნაც ღირს: ინდექსის გასაღები შეიძლება იყოს მაქსიმუმ 900 ბაიტი clustered ინდექსისთვის და 1,700 ბაიტი nonclustered-ისთვის, და მაქსიმუმ 32 გასაღების სვეტი. nvarchar(max) და varbinary(max) სვეტები გასაღების სვეტები საერთოდ ვერ იქნება, თუმცა შეიძლება included იყოს - რაც ჩვეულებრივ ცუდი იდეაა, რადგან დიდ მნიშვნელობებს ინდექსში აკოპირებს.

Filtered და columnstore ინდექსები#

filtered ინდექსს აქვს WHERE პირობა და მხოლოდ შესაბამის row-ებს აინდექსებს. ეს სწორი ინსტრუმენტია, როცა query-ები დიდი ცხრილის პატარა, მკაფიოდ განსაზღვრულ ნაწილს ითხოვენ:

CREATE NONCLUSTERED INDEX IX_Orders_Pending    ON dbo.Orders (CreatedAt)    INCLUDE (CustomerId, Total)    WHERE Status = 0;

თუ შეკვეთების 2 პროცენტი მოლოდინშია, ეს ინდექსი სრული ინდექსის ზომის 2 პროცენტია და მისი შენარჩუნება ბევრად იაფია. ხაფანგი: optimiser-ი მას მხოლოდ მაშინ იყენებს, როცა შეუძლია დაამტკიცოს, რომ query-ის პრედიკატი ფილტრის შიგნით ხვდება. query WHERE Status = @status-ით და პარამეტრით ჩვეულებრივ არ დაემთხვევა, რადგან plan ნებისმიერ მნიშვნელობაზე უნდა მუშაობდეს. filtered ინდექსები შეეფერება query-ებს ლიტერალური მნიშვნელობებით ან OPTION (RECOMPILE)-ით. ისინი ასევე სტანდარტული გზაა nullable სვეტზე უნიკალურობის დასაცავად, თან რამდენიმე NULL-ის დაშვებით: უნიკალური ინდექსი WHERE Email IS NOT NULL.

columnstore ინდექსი მონაცემებს სვეტების მიხედვით ინახავს და არა row-ების, შეკუმშულად, და მათ batch-ებად ამუშავებს. ის შესანიშნავია მილიონობით row-ის აგრეგაციისთვის - რეპორტინგი, dashboard-ები, ანალიტიკა მოვლენების ცხრილებზე - და ცუდია ცალკეული row-ების მოსაძებნად. Express-ზე ის მუშაობს, მაგრამ columnstore-ის მეხსიერება ინსტანციაზე 352 MB-ით არის შეზღუდული, ამიტომ ის ზომიერ ანალიტიკურ ცხრილებს შეეფერება და Express-ს data warehouse-ად ვერ აქცევს.

Execution plan-ის მიღება#

execution plan არის ოპერატორების ხე, რომელიც SQL Server-მა query-ის გასაშვებად აირჩია. არსებობს ორი, რომელსაც შეგიძლია შეხედო:

• Estimated plan - რის გაკეთებასაც optimiser-ი აპირებს, query-ის გაშვების გარეშე. SSMS-ში Ctrl+L.
• Actual plan - იგივე plan, პლუს ის, რაც რეალურად მოხდა: row-ების ფაქტობრივი რაოდენობა, გაშვებები თითო ოპერატორზე, memory grant-ები, გაფრთხილებები. SSMS-ში ჩართე "Include Actual Execution Plan" Ctrl+M-ით, შემდეგ გაუშვი query.

ყოველთვის actual plan ამჯობინე, როცა query-ის გაშვება შეგიძლია. ნებისმიერ plan-ში ყველაზე მნიშვნელოვანი რიცხვი არის სხვაობა შეფასებულ და ფაქტობრივ row-ებს შორის, estimated plan-ს კი ეს არ აქვს.

plan-ს I/O სტატისტიკა დაუწყვილე, რომელიც გეუბნება, რამდენი სამუშაო შესრულდა, ისეთ ერთეულში, რომელიც query-ის ვერსიებს შორის შეგიძლია შეადარო:

SET STATISTICS IO, TIME ON;SELECT OrderId, CreatedAt, TotalFROM dbo.OrdersWHERE CustomerId = 42ORDER BY CreatedAt DESC;

Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, ... SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 1 ms.

Logical reads არის მეხსიერებიდან წაკითხული 8 KB-იანი გვერდები. სწორედ ეს რიცხვი უნდა შეამცირო: გასწორება, რომელიც query-ს 48,000 logical read-იდან 12-მდე ჩამოიყვანს, იმუშავა, რაც არ უნდა აჩვენოს elapsed time-მა გამთბარ cache-ზე. SSMS-ის გარეთ SET STATISTICS XML ON actual plan-ს XML-ად აბრუნებს, რომლის შენახვა და მოგვიანებით გახსნა ნებისმიერ ინსტრუმენტს შეუძლია.

Plan-ის კითხვა: ოპერატორები, რომლებიც მნიშვნელოვანია#

გრაფიკული plan მარჯვნიდან მარცხნივ და ზემოდან ქვემოთ იკითხება: მონაცემები მარჯვნივ მდგომ ოპერატორებთან იწყება, მარცხნივ მიედინება ისრებით, რომელთა სისქე row-ების რაოდენობას ასახავს, და მთავრდება მარცხნივ SELECT-თან. თითოეული ოპერატორი აჩვენებს ღირებულების პროცენტს, რომელიც შეფასებაა actual plan-შიც კი - სასარგებლოა იმის საპოვნელად, სად შეხედო, მაგრამ მტკიცებულება არ არის.

ყველაზე ხშირად ნახავ შაბლონს, სადაც Index Seek კვებავს Nested Loops join-ს, ქვემოთ კი Key Lookup არის. seek-მა row-ები იპოვა; lookup-ი თითოეულისთვის clustered ინდექსში დაბრუნდა, რომ ისეთი სვეტი აეღო, რომელიც ინდექსს არ ჰქონდა. მიიტანე კურსორი Key Lookup-ზე და tooltip ამ სვეტს "Output List"-ში ჩამოთვლის. დაამატე ის ინდექსის INCLUDE სიაში და lookup-ი გაქრება.

შემდეგ შეფასებები შეამოწმე. მიიტანე კურსორი ძვირი განშტოების ყველაზე მარჯვენა ოპერატორზე და შეადარე "Estimated Number of Rows" და "Actual Number of Rows". შეფასება 1 ფაქტობრივი 200,000-ის წინააღმდეგ ნიშნავს, რომ optimiser-მა plan არასწორი ამოცანისთვის აირჩია - nested loop იქ, სადაც hash join უნდა ყოფილიყო, ზედმეტად პატარა memory grant, რის გამოც sort გადაიღვრება. მიზეზი თითქმის ყოველთვის მოძველებული სტატისტიკაა, პრედიკატი, რომლის შეფასებაც optimiser-ს არ შეუძლია, ან პარამეტრის მნიშვნელობა, რომელიც ძალიან განსხვავდება იმისგან, რისთვისაც plan დაკომპილირდა.

ბოლოს, წაიკითხე ყვითელი გამაფრთხილებელი სამკუთხედები. ხშირია "Type conversion in expression may affect cardinality estimate", tempdb-ში გადაღვრა Sort-ზე ან Hash Match-ზე და დაკარგული ინდექსის მწვანე შეთავაზება plan-ის თავზე.

რატომ უგულებელყოფს query შენს ინდექსს#

ინდექსი მხოლოდ მაშინ არის სასარგებლო, თუ პრედიკატი sargable-ია - ისეა დაწერილი, რომ ძრავს შეუძლია ის seek-ის დიაპაზონად აქციოს. ესენი ასეთი არ არის:

-- A function on the column: every row must be computed firstWHERE YEAR(CreatedAt) = 2026-- Rewrite as a rangeWHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01'-- A leading wildcard: no starting point in the sorted indexWHERE Email LIKE '%@example.com'-- Arithmetic on the columnWHERE Total * 1.2 > 100-- Move it to the other sideWHERE Total > 100 / 1.2

ყველაზე ფაქიზი შემთხვევა იმპლიციტური კონვერტაციაა. თუ Email არის varchar და აპლიკაცია პარამეტრს nvarchar-ად აგზავნის - რასაც დრაივერების უმეტესობა სტრიქონებისთვის ნაგულისხმევად აკეთებს - SQL Server-მა ერთი მხარე უნდა გადააკონვერტიროს. nvarchar-ს უფრო მაღალი პრიორიტეტი აქვს, ამიტომ სვეტი კონვერტირდება და plan სვეტზე აჩვენებს CONVERT_IMPLICIT-ს და scan-ს იქ, სადაც seek-ს ელოდი. Windows collation-ით optimiser-ს ზოგჯერ მაინც შეუძლია seek გამოთვლილ დიაპაზონზე, მაგრამ ძველი SQL_ collation-ებით ის scan-ს აკეთებს. დრაივერში პარამეტრის ტიპი სვეტის ტიპს დაუმთხვიე; SQL Server-ის collation-ები და Unicode ხსნის, რატომ ცვლის collation შედეგს.

მეორე ხშირი მიზეზი ისაა, რომ ინდექსს ზედმეტად ბევრი key lookup დასჭირდებოდა. თუ query ცხრილის დიდ ნაწილს აბრუნებს, seek და შემდეგ თითოეული row-ის lookup scan-ზე ნელია, და optimiser-ი მართალია, როცა scan-ს ირჩევს. გამოსავალი covering ინდექსია და არა index hint.

დაკარგული ინდექსის მინიშნებები და ინდექსების მოვლა#

SQL Server query-ების ოპტიმიზაციისას იწერს ინდექსებს, რომლებიც ისურვებდა, რომ ჰქონოდა. შეთავაზებები საწყისი წერტილია და არა გასაკეთებელი საქმეების სია - ისინი სვეტების მიმდევრობის ნიუანსებს უგულებელყოფენ, ერთმანეთის თითქმის დუბლიკატებს გვთავაზობენ და ჩაწერებზე ინდექსის შენარჩუნების ღირებულებას არასოდეს ითვალისწინებენ:

SELECT TOP (20)    d.statement AS table_name,    d.equality_columns, d.inequality_columns, d.included_columns,    s.user_seeks, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dJOIN sys.dm_db_missing_index_groups AS g ON g.index_handle = d.index_handleJOIN sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handleORDER BY s.user_seeks * s.avg_user_impact DESC;

საპირისპირო კითხვაზე - რომელი ინდექსები არასოდეს გამოიყენება - პასუხი sys.dm_db_index_usage_stats-დან მოდის. ინდექსი ნული seek-ით, scan-ით და lookup-ით, მაგრამ ბოლო restart-ის შემდეგ ათასობით განახლებით, ჩაწერებსა და ადგილს ტყუილად გიხარჯავს. ორივე view სერვერის restart-ისას ნულდება, ამიტომ შეაფასე ისინი ტრაფიკის რეპრეზენტატიული პერიოდის შემდეგ.

ფრაგმენტაციასთან დაკავშირებით, NVMe-ზე პატარა ბაზისთვის გულწრფელი პოზიცია ისაა, რომ ის ბევრად ნაკლებად მნიშვნელოვანია, ვიდრე ადრე. ფრაგმენტაცია მაშინ აზიანებდა, როცა მბრუნავი დისკები ყოველ მიმდევრობის გარეშე წაკითხვაში იხდიდნენ. რაც კვლავ მნიშვნელოვანია, ეს გვერდების სიმჭიდროვეა - მასიური წაშლების შემდეგ ნახევრად ცარიელი გვერდები ნიშნავს მეტ წასაკითხ გვერდს და მეტ გამოყენებულ მეხსიერებას. შეამოწმე ის sys.dm_db_index_physical_stats-ით და ხელახლა ააგე მხოლოდ ის, რაც ძლიერ არის დაზიანებული:

ALTER INDEX IX_Orders_CustomerId_CreatedAt ON dbo.Orders REBUILD;-- or the lighter, always-online optionALTER INDEX IX_Orders_CustomerId_CreatedAt ON dbo.Orders REORGANIZE;

REBUILD WITH (ONLINE = ON) Enterprise-ის ფუნქციაა, ამიტომ Express-ზე rebuild ცხრილს მთელი თავისი ხანგრძლივობით ბლოკავს; რამდენიმე მილიონ row-იან ცხრილზე ეს წამებია, მაგრამ მაინც მშვიდი საათისთვის დაგეგმე. სტატისტიკა ფრაგმენტაციაზე მნიშვნელოვანია: UPDATE STATISTICS dbo.Orders WITH FULLSCAN; დიდი მონაცემების ჩატვირთვის შემდეგ მეტ ცუდ plan-ს ასწორებს, ვიდრე ნებისმიერი rebuild. rebuild გვერდითი ეფექტით ამ ინდექსის სტატისტიკასაც განაახლებს; reorganise - არა.

ყოველ ინდექსს ფასი აქვს ყოველ insert-ზე, update-სა და delete-ზე, და ის Express-ზე 10 GB-იან მონაცემთა ზღვარში ითვლება. დატვირთულ ცხრილზე ხუთი კარგად შერჩეული covering ინდექსი თხუთმეტ ერთსვეტიანს სჯობს. SQL Server Query Store და ნელი query-ები აჩვენებს, როგორ იპოვო, რომელი query-ები იმსახურებს ინდექსს საერთოდ, ხოლო ზოგადი მეთოდი სტატიიდან EXPLAIN ANALYZE და ნელი query-ები PostgreSQL-იდან თითქმის უცვლელად გადმოდის.

FAQ#

უნდა ჰქონდეს ყველა foreign key სვეტს ინდექსი?

ჩვეულებრივ, დიახ. SQL Server foreign key-სთვის ავტომატურად არ ქმნის ინდექსს, და მის გარეშე ამ სვეტზე join-ები scan-ს აკეთებს, მშობელი row-ის წაშლა კი შვილობილ ცხრილს scan-ით ამოწმებს მიმართვების მოსაძებნად. გამონაკლისია პაწაწინა lookup ცხრილები და სვეტები, რომლებზეც არავინ არასოდეს აკეთებს join-ს ან ფილტრაციას.

რა განსხვავებაა index seek-სა და index scan-ს შორის?

seek B-tree-ით პირდაპირ მიდის row-ებამდე, რომლებიც გასაღებს ან დიაპაზონს ემთხვევა. scan ინდექსის ან ცხრილის ყოველ გვერდს ერთი ბოლოდან კითხულობს. 50 row-იანი ცხრილის scan ნორმალურია; 5 მილიონ row-იანი ცხრილის scan ათი row-ის დასაბრუნებლად - სწორედ ის არის, რაც უნდა გაასწორო.

იყენებს SQL Server query-ში ცხრილზე ერთზე მეტ ინდექსს?

ზოგჯერ. მას შეუძლია ორი nonclustered ინდექსის გადაკვეთა, მაგრამ იშვიათად ირჩევს ამას, და შედეგი თითქმის ყოველთვის უფრო ნელია, ვიდრე ერთი კომპოზიტური ინდექსი, რომელიც პრედიკატს ფარავს. კომპოზიტური ინდექსები შენი რეალური query-ებისთვის დააპროექტე და გადაკვეთის იმედზე ნუ იქნები.

რატომ არის ჩემი query SSMS-ში სწრაფი და აპლიკაციიდან ნელი?

ჩვეულებრივ, განსხვავებული cache-ში შენახული plan-ის გამო. SSMS და დრაივერების უმეტესობა განსხვავებულ SET ოფციებს იყენებს, განსაკუთრებით ARITHABORT-ს, ამიტომ ისინი plan cache-ში ცალკე ჩანაწერებს იღებენ, თითოეული პარამეტრების განსხვავებული მნიშვნელობებისთვის დაკომპილირებული. შეადარე ორივეს plan-ები და შეამოწმე პარამეტრების ტიპები იმპლიციტურ კონვერტაციაზე.

რამდენი ინდექსია ზედმეტად ბევრი?

რიცხვი არ არსებობს, მაგრამ სიმპტომი ნათელია: insert-ები და update-ები ნელდება, გამოყენების სტატისტიკა კი აჩვენებს ინდექსებს, რომლებიც მუდმივად იწერება და არასოდეს იკითხება. ჩაწერებით დატვირთულ ცხრილზე ხუთი-ექვსი კარგად დაპროექტებული ინდექსი ბევრია. ძირითადად წასაკითხ რეპორტინგის ცხრილზე მეტიც ნორმალურია.