Chỉ mục (INDEX) vào SQL là bảng tra cứu đặc biệt mà hình thức tìm kiếm cửa hàng dữ liệu có thể sử dụng nhằm tăng nhanh thời hạn và năng suất truy xuất dữ liệu.

Bạn đang xem: Cách đánh index trong sql

Hiểu đơn giản, một chỉ mục là một trong con trỏ chỉ tới từng giá bán trị xuất hiện thêm trong bảng/cột được tiến công chỉ mục. Chỉ mục vào Database có ý nghĩa tương tự như những mục trong lộ diện trong Mục lục của một cuốn sách.

INDEX góp tăng tốc những truy vấn SELECT chứa những mệnh đề WHERE hoặc ORDER, cơ mà nó làm lờ lững việc tài liệu nhập vào với các lệnh UPDATE với INSERT. Những chỉ mục rất có thể được sản xuất hoặc xóa mà không ảnh hưởng tới dữ liệu.

Các kiểu index gồm trong SQL:

Single-Column Index
Unique Index
Composite Index
Implicit Index
Để tạo một chỉ mục ta áp dụng lệnh CREATE INDEX, bạn có thể đặt tên mang đến chỉ mục, xác định bảng, các cột mong lập chỉ mục và khẳng định chỉ mục là theo vật dụng tự tăng dần hoặc sút dần.

Lệnh CREATE INDEX

Cú pháp cơ bạn dạng của lệnh CREATE INDEX trong SQL như sau:

CREATE INDEX ten_index ON ten_bang;

Chỉ mục SINGLE-COLUMN

Single-Column Index được tạo nên duy nhất 1 cột trong bảng. Cú pháp cơ bạn dạng như sau:

CREATE INDEX ten_index
ON ten_bang (ten_cot);

Chỉ mục UNIQUE

Unique Index là chỉ mục duy nhất, được sử dụng để tăng hiệu suất và đảm bảo tính toàn diện dữ liệu. Một chỉ mục độc nhất không có thể chấp nhận được chèn bất kỳ giá trị đụng hàng nào được chèn vào bảng. Cú pháp cơ bản như sau.

CREATE unique INDEX ten_index
ON ten_bang (ten_cot);

Chỉ mục COMPOSITE

Composite Index là chỉ mục kết hợp giành riêng cho hai hoặc những cột trong một bảng. Cú pháp cơ phiên bản của nó như sau:

CREATE INDEX ten_index
ON ten_bang (cot1, cot2);Lưu ý:

Việc sản xuất Single-Column Index xuất xắc Composite Index tùy nằm trong vào tần suất bạn thực hiện mệnh đề WHERE của truy vấn vấn bên dưới dạng đk bộ lọc.Nếu chỉ bao gồm một cột được sử dụng, thì lựa chọn cực tốt là Single-column Index. Nếu tất cả hai hoặc những cột được sử dụng liên tục trong mệnh đề WHERE như là những bộ thanh lọc thì dạng chỉ mục Composite Index là lựa chọn về tối ưu hơn.

IMPLICIT INDEX

Implicit Index (Index ngầm định) là chỉ mục nhưng được tạo auto bởi Database Server khi một bảng được tạo. Những Index ngầm định được tạo tự động cho những ràng buộc Primary key và những ràng buộc Unique.

Lệnh DROP INDEX

Khi không cần sử dụng INDEX nữa bạn cũng có thể DROP theo cú pháp sau:

DROP INDEX ten_index;

Bạn nên cẩn trọng trong lúc xóa một chỉ mục, bởi vì khi kia hiệu suất hoàn toàn có thể chậm hơn hoặc ko được cải thiện.

Khi nào nên tránh sử dụng INDEX?

Mặc dù áp dụng INDEX nhằm mục tiêu mục đích để nâng cấp hiệu suất của Database, nhưng đôi khi bạn phải tránh dùng chúng. Dưới đây là một số ngôi trường hợp bạn phải xem xét để quyết định có nên sử dụng INDEX tuyệt không:

Không yêu cầu sử dụng trong các bảng nhỏ, ít bản ghi.Không nên sử dụng Index vào bảng mà các chuyển động UPDATE, INSERT xảy ra liên tục với tần suất lớn.Không đề xuất sử dụng cho những cột mà lại chứa một số trong những lượng phệ giá trị NULL.Không đề xuất dùng Index cho các cột mà thường xuyên bị sửa đổi.
Người dịch: Hoàng Văn Cương, thiết kế viên Spring Boot, cachseo.com Việt nam
Bài viết gốc bởi tiếng Anhởdataschool.com/sql-optimization/how-indexing-works/

Đánh chỉ mục là gì?

Đánh chỉ mục giúp những cột được truy vấn vấn cấp tốc hơn bằng phương pháp tạo ra những hướng dẫn tới địa điểm mà dữ liệu được lưu trữ trong CSDL.Hãy tưởng tượng bạn có nhu cầu tìm kiến một phần thông tin nhỏ trong CSDL. Để rước được nó, máy vi tính sẽ phải đi kiếm từng chiếc một cho tới khi kiếm tìm được. Nếu tài liệu mà bạn đang tìm kiếm ở tận sau cuối thì việc truy vấn đang tốn tương đối nhiều thời gian.Ví dụ như hình dưới:Nếu bảng được sắp xếp tên theo anphabet, chúng ta cũng có thể tìm kiếm cấp tốc hơn những vì rất có thể bỏ qua một vài dòng nhất định. Để kiếm tìm “Zack” với dữ liệu đã bố trí theo anphabet bạn có thể bắt đầu từ trọng điểm và xét coi tên đó ở nửa trên tốt nửa dưới. Kế tiếp lại bước đầu tìm tìm từ ở giữa của nửa đó và cứ liên tục như vậy cho đến khi tìm thấy.

Xem thêm: Cách test đá rơi tủ lạnh hitachi, cách test xả đá cưỡng bức tủ lạnh hitachi

Việc search kiếm đã chỉ mất 3 lần so sánh để tìm được đáp áp đúng chuẩn thay vì 8 sinh sống dữ liệu chưa được đánh chỉ mục.Các chỉ mục cho phép chúng ta tạo những danh sách được thu xếp mà không nhất thiết phải tạo một bảng với tài liệu được sắp xếp mới. Điều này giúp ta tiết kiệm rất nhiều bộ nhớ.

Vậy thực sự tấn công chỉ mục là gì?

Một chỉ mục là một kết cấu mà trong số đó chứa đựng trường nhưng mà chỉ mục đang chuẩn bị xắp và liên kết từ nó cho tới dữ liệu tương ứng ở bảng tài liệu gốc – vị trí mà tài liệu thực sự được lưu trữ. Có thể hiểu chỉ mục y hệt như một cuốn sách thần kỳ, nó lưu lại trữ thông tin theo thứ tự ta ghi vào và gồm hai chế độ đọc. Chế độ nguyên bạn dạng nơi tài liệu như đúng chú ta ghi (bảng tài liệu gốc) và cơ chế sắp xếp tạo điều kiện cho ta tìm kiếm thông tin nhanh rộng (cấu trúc chỉ mục).Để dễ hình dung, bọn họ dùng ví dụ nghỉ ngơi trên và xem phương pháp mà chỉ mục liên kết tới bảng dữ liệu gốc ra sao:Chúng ta thấy rằng, bảng Friends được sắp xếp theo id tăng dầnmỗi lúc ta thêm dữ liệu mới. Còn bảng Index được lưu trữ với việc thu xếp tên theo anphabet.

Các giao diện đánh chỉ mục

Có 2 vẻ bên ngoài đánh chỉ mục mang đến CSDL:Clustered – Đánh theo cụm
Non-clustered – Đánh ko phân cụm
Cả hai kiểu dáng trên đều lưu trữ và search kiếm theo kiểu
B-trees, một kết cấu dữ liệu tương tự với cây nhị phân (binary tree). Từng B-tree là “ cấu trúc dữ liệu cây tự cân đối nơi đảm bảo an toàn việc dữ trữ dữ liệu đã thu xếp và có thể chấp nhận được tìm kiếm, truy vấn tuần tự, thêm với xóa cùng với với thời hạn theo log(n)”. Về cơ bản, nó tạo ra một cây – giống kiểu kết cấu mà dữ liệu được bố trí để tìm kiếm kiếm nhanh.Trên đó là một B-tree của chỉ mục. Đầu vào nhỏ tuổi nhất là sinh sống điểm ko kể cùng bên trái, lớn số 1 là quanh đó cùng mặt phải. Tất cả truy vấn bao gồm thể ban đầu ở node trên thuộc và đi dần xuống, nếu node mục tiêu bé dại hơn node hiện tại thì sẽ di chuyển sang bên trái, trường hợp node phương châm lớn hơn thì di chuyển sang mặt phải. Ở ví dụ trên nó sẽ dịch chuyển như sau: Matt => Todd => Zack.Để tăng tích hiệu quả, không hề ít B-tree sẽ giới hạn số thành phần bạn nhập vào trong 1 mục. B-tree sẽ tự thao tác đó với không yêu mong giới hạn dữ liệu cột. Ở ví dụ trên, B-tree giới hạn số mục ngơi nghỉ 4.

Đánh chỉ mục theo nhiều (Clustered Indexes)

Chỉ mục được phân các là chỉ mục độc nhất trên từng bảng sử dụng khóa chính để tổ chức dữ liệu vào bảng. Nó bảo đảm rằng khóa chủ yếu được tàng trữ theo lắp thêm tự tăng dần, đó cũng là sản phẩm tự mà lại bảng giữ trong bộ nhớ.Chỉ mục cụm không tốt nhất thiết phải khai báo rõ ràng.Được tạo ngay trong lúc bảng được tạo nên ra.Sử dụng khóa chủ yếu theo vật dụng tự tăng dần.

Tạo Clustered Indexes

Đánh chỉ mục cụm – clustered index sẽ được tạo auto khi khóa thiết yếu được lựa chọn:CREATE TABLE friends (id INT PRIMARY KEY, name VARCHAR, thành phố VARCHAR);Khi được điền đầy đủ dữ liệu, bảng sẽ như bên dưới:Bảng Friends được tạo ra sẽ có được đánh chỉ mục cụm auto như đang nêu với được tổ chức triển khai xung xung quanh khóa chủ yếu “id” call là “friends_pkey”:Khi kiếm tìm kiếm vào bảng bằng id, việc thu xếp tăng dần sẽ giúp tốiưu kiếm tìm kiếm. Bởi vì được thu xếp nên việc tìm kiếm sẽ điều hướng sang B-tree cùng giúp thời hạn tìm kiếm chỉ với log(n).Tuy nhiên, để tìm kiếm mang lại “name” tốt “city” sống trong bảng, ta cần phải tìm kiếm tuần tự do thoải mái những cột đó chưa xuất hiện chỉ mục. Đó là lý mà lại mà đánh chỉ mục không phân cụm - non-clustered indexes trở phải hữu dụng.

Chỉ mục không phân các (Non-Clustered Indexes)

Chỉ mục không phân cụm là những tham chiếu được bố trí cho một trường cụ thể từ bảng chính và nó chứa những con trỏ chỉ về mục nơi bắt đầu ở bảng chính. Ví dụ ở đầu bài chính là một chỉ mục không phân cụm:Thông thường xuyên nó đang tăng tốc câu hỏi truy vấn bằng cách tạo ra những cột hoàn toàn có thể tìm kiếm dễ dàng dàng. Non-clustered indexes có thể tạo ra vị data analysts/developers sau khoản thời gian một bảng được tạo thành và điền thông tin.Note: Chỉ mục ko phân cụmkhông cần là bảng mới. Nó giữ lại trường dùng làm sắp xếp và một con trỏ, trỏtừ mỗimục trong những số ấy tới mục chứa không thiếu các tường sống bảng gốc.Bạn hoàn toàn có thể tưởng tượng nó hệt như mục lục của một cuốn sách. Những index đã chỉ mang đến vị trí cất nội dung mà các bạn tìm kiếm trong cuốn sách.Non-clustered indexes đang chỉ đến add bộ ghi nhớ của dữ liệu thay vày tự lưu lại trữ. Việc này khiến cho việc truy vấn lờ đờ hơn so với clustered indexes nhưng thông thường sẽ nhanh hơn vấn đề không đánh chỉ mục. Chúng ta có thể tạo ra nhiều non-clustered indexes. Trên 2008, chúng ta cũng có thể tạo 999 non-clustered indexes tronng SQL server và giới hạn max trong Postgres
SQL.

Tạo Non-Clustered Databases(Postgre
SQL)

Để sản xuất một chỉ mục mang lại việc sắp xếp tên theo anphabet:CREATE INDEX friends_name_asc ON friends(name ASC);