Hẳn những tín đồ đã và đang từng nghe về 2 loại index là Clustered Index và Non-clustered index.

Dạo một vòng tìm những khái niệm trên Google, chắc những tín đồ cũng sẽ tìm được cách phân biệt đơn giản và giản dị đó là: Clustered index được tiết ra trên một table với primary key, còn non clustered thì đơn giản và giản dị là cho những key để lại không phải là primary key. Lời đáp này liệu đã đủ sức thuyết phục?

Nội dung bài viết ngày hôm nay mình xin được reviews cách hiểu của tớ về clustered index và non-clustered index.

Clustered index

Vậy clustered index là gì? Liệu nó có phải đơn giản và giản dị là loại index được đánh trên primary key của một table?

Clustered index khái niệm trật tự mà tài liệu được lưu trữ vật lý trong một bảng.

Hiểu một kiểu thường thì, khi tín đồ đánh index cho một trường trong tables, những giá trị của trường này sẽ được tổ chức lưu trữ có cấu trúc (thường thì sẽ tận dụng B-Tree), thành phẩm tìm kiếm trên B-Tree index sẽ trả về row pointer tới record tín đồ đang muốn tìm.

Tuy nhiên, với clustered index, toàn bộ row sẽ tiến hành lưu có cấu trúc ngay trên B-Tree index, tức là sau khoản thời gian tìm kiếm với field được đánh clustered index trên B-Tree thành phẩm trả về đó là record mình muốn tìm.

Có một lưu ý là, toàn bộ tài liệu của một row sẽ tiến hành lưu ngay trên node lá của B Tree, nhưng những node trung gian sẽ chỉ lưu giá trị của cột được đánh index. Mỗi table nên làm có một clustered index, cũng chính vì clustered index lưu toàn bộ tài liệu trong một row và tín đồ không nên lưu những tài liệu này ở nhiều nơi một lúc.

Clustered index trên InnoDB

Cũng chính vì việc thực thi index được đảm nhiệm bởi những storage engines, vì vậy không phải storage engine nào thì cũng tư vấn clustered index. Trong nội dung bài viết này mình sẽ nói về việc thực thi clustered index trong InnoDB, những storage engines khác đôi lúc sẽ được những cách thực thi khác lạ tuy nhiên về nguyên tắc hoạt động và sinh hoạt thì nó vẫn sẽ tương tự nhau.

Trong InnoDB, mặc định cột được đánh primary key sẽ cũng là “index column” cho việc clusters tài liệu. Bởi nguyên nhân này, những tín đồ thường nghe nói “Clustered index được tiết ra trên một table với primary key”.

Tuy nhiên nếu trong một table mà tín đồ không đánh primary key thì phải tận dụng cột nào để build clustered index. Lời đáp là: InnoDB chọn column để “chọn mặt gửi vàng” cho việc clustered index theo trật tự ưu tiên như sau:

• Trước tiên, như đã đề cập ở trên, InnoDB sẽ mặc định chọn Primary Key làm “index column”
• Nếu table không hề có khai báo Primary key, InnoDB sẽ tìm kiếm cột nào vừa lòng tham dự Unique và Not null để thay thế
• Nếu trong table này vẫn không hề có cột nào Unique và Not null, InnoDB sẽ dùng cách sau cuối là tự define một hidden primary key và cluster data trên cái cột này.

Non clustered index

Với cách lưu trữ index thường thì, tài liệu sẽ tiến hành lưu ở một vùng nhớ nào đó và những node lá sau cuối của B Tree sẽ chứa con trỏ tới đúng record muốn tìm. Tuy nhiên với clustered index, tài liệu được tổ chức lưu trữ ngay trên B Tree. Primary key đó là “index column” được chọn để triển khai clusters. Vậy những cột để lại khi được đánh index nó sẽ lưu trữ thế nào?

Trong InnoDB, toàn bộ những index để lại mà không phải là clustered index thì sẽ chứa giá trị của clustered index tương ứng. Tức là, khi tín đồ triển khai tìm kiếm với cột nonclustered index, khối hệ thống sẽ tìm kiếm trên B Tree index của cột đó, thành phẩm trả về là clustered index tương ứng, khối hệ thống sẽ tiếp tục quét B Tree của clustered index và trả về tương đối đầy đủ tài liệu.

Giả sử tín đồ có một table gồm ID, FName, LName. Trong số đó ID là PK, tín đồ đánh index cho trường FName thì InnoDB sẽ build 2 B Tree như sau

Khi triển khai câu lệnh

select * from tables where FName = ?

thì InnoDB sẽ triển khai tìm kiếm trên B Tree của FName, sau khoản thời gian tìm được node lá tương ứng thì nó tiếp tục cầm giá trị của node lá này (đó là key của clustered index) để quét trên B Tree của ID (clustered index) và trả về giá trị tương đối đầy đủ của truy vấn.

Lưu ý khi chọn cột đánh clustered index

Việc tận dụng clustered index sẽ hỗ trợ tăng vận tốc truy vấn tài liệu. Cũng chính vì clustered index lưu trữ index và tài liệu ngay trên B Tree. Record sẽ tiến hành trả về ngay sau khoản thời gian triển khai quét B Tree kết thúc thay vì phải tìm kiếm đến row pointer như thông thường, nâng cao I/O-bound workloads.

Tuy nhiên, nếu tận dụng không đúng cách dán, clustered index sẽ làm performance giảm đáng kế:

• Vận tốc insert vào cluster tùy thuộc vào vị trí muốn insert vào. Vì thực chất index là được lưu trữ có trật tự, khi insert 1 record mới sẽ phải tìm kiếm vị trí tương thích để insert vào thay vì insert vào ô nhớ khả dụng tiếp theo như cách thường thì.
• Ngân sách cho việc update cột được đánh clustered index sẽ rất đắt, cũng chính vì InnoDB cũng sẽ phải move toàn bộ row tương tứng đến vị trí mới.
• Table tận dụng clustered index hoàn toàn có thể bị phân chia trang khi record mới được chèn vào, hoặc khi cột được đánh index bị update. Việc chia trang xẩy ra khi một key sau khoản thời gian tìm kiếm đúng vị trí order phải bắt buộc chèn vào vị trí trong page đã full data. Lúc này storage engine phải chia page này thành 2, và table sẽ tận dụng nhiều space trên đĩa hơn.
• Chính ví việc hoàn toàn có thể bị phân trang ở trên, clustered tables sẽ chậm hơn khi triển khai full table scan.
• Non clustered index hoàn toàn có thể sẽ to hơn thông thường vì node lá của chúng lưu trữ giá trị từ clustered index, giá trị này càng lớn (ví dụ kiểu varchar) thì non clustered index sẽ to hơn.

Vậy thì tận dụng clustered index thế nào cho đúng để tránh những hạn chế đã nêu trên?

Lời đáp là tín đồ nên tận dụng field AUTO INCREASEMENT cho column được chọn làm clustered index. Vì sao? Lúc này mọi người hãy cùng thử so sánh việc lựa chọn một field AUTO INCREASEMENT và một field có mức giá trị tình cờ vừa lòng UNIQUE và NOT NULL (ví dụ UUID) làm clustered index và cùng phân tích performance của 2 trường hợp này tương ứng với những hạn chế nêu ở trên.

Clustered index column AUTO INCREASEMENT column Random Column Vận tốc insert Khi giá trị của key được đánh index tự động hóa tăng, new record chỉ việc insert vào vị trí sau cuối. Tìm kiếm ví trí tương thích để chèn key và record vào Ngân sách update Không nên triển khai update cho cột được chọn đánh clustered index để tránh việc này Không nên triển khai update cho cột được chọn đánh clustered index để tránh việc này Hạn chế phân trang Với trường tự động hóa tăng, record mới luôn luôn được chèn vào vị trí sau cuối, sẽ không hề có trường hợp chèn vào trong số những vị trí đã làm được data, nên storage engine không cần triển khai những tác vụ phân trang lãng phí Vì record mới sẽ tiến hành chèn vào tình cờ nên dẫn tới bị phân trang sở hữu nhiều space Giảm kích thước của non clustered index Thường thì trường tự động hóa tăng sẽ được kiểu tài liệu là Number, kích thước nhỏ hơn nhiều so với varchar nếu kiểu tài liệu là varchar càng lớn thì non clustered index sẽ phải tốn nhiều space hơn để lưu những giá trị này

Từ những so sánh này hoàn toàn có thể thấy, khi đánh clustered index nên lựa chọn cột UNIQUE, NOT NULL, AUTO INCREASEMENT để làm được được hiệu suất cao tốt nhất. Thường thì, cột có tính chất như trên đó là cột ID được khai báo là Primary Key và InnoDB sẽ mặc định chọn nó làm clustered index column.

Kỳ vọng qua nội dung bài viết, những tín đồ nắm rõ hơn về thực chất của clustered index thay vì cách hiểu hàn lâm thường thì là “Clustered index được tiết ra trên một table với primary key”.