Lập chỉ mục là một phần quan trọng của tối ưu hóa cơ sở dữ liệu. Lập chỉ mục có thể làm tăng đáng kể tốc độ truy vấn. Xem cách các chỉ mục hoạt động và tìm hiểu cách xây dựng chỉ mục với SQL.

Bạn đang xem : sử dụng lập chỉ mục trong sql

Lập chỉ mục

Sửa đổi lần cuối: ngày 09 tháng 8 năm 2021

Lập chỉ mục là gì?

Lập chỉ mục giúp các cột truy vấn nhanh hơn bằng cách tạo con trỏ đến nơi dữ liệu được lưu trữ trong cơ sở dữ liệu.

Hãy tưởng tượng bạn muốn tìm một phần thông tin nằm trong cơ sở dữ liệu lớn. Để lấy thông tin này ra khỏi cơ sở dữ liệu, máy tính sẽ xem qua từng hàng cho đến khi tìm thấy nó. Nếu dữ liệu bạn đang tìm kiếm đã đến cuối, thì truy vấn này sẽ mất nhiều thời gian để chạy.

Hình dung để tìm mục nhập cuối cùng:

Nếu bảng được sắp xếp theo thứ tự bảng chữ cái, việc tìm kiếm tên có thể diễn ra nhanh hơn rất nhiều vì chúng tôi có thể bỏ qua việc tìm kiếm dữ liệu trong các hàng nhất định. Nếu chúng tôi muốn tìm kiếm “Zack” và chúng tôi biết dữ liệu theo thứ tự bảng chữ cái, chúng tôi có thể nhảy xuống nửa chừng dữ liệu để xem liệu Zack đến trước hay sau hàng đó. Sau đó, chúng tôi có thể phân nửa số hàng còn lại và thực hiện cùng một phép so sánh.

Quá trình so sánh này mất 3 lần để tìm ra câu trả lời đúng thay vì 8 lần trong dữ liệu chưa lập chỉ mục.

Các chỉ mục cho phép chúng tôi tạo danh sách được sắp xếp mà không cần phải tạo tất cả các bảng được sắp xếp mới, điều này sẽ chiếm nhiều dung lượng lưu trữ.

Chỉ mục chính xác là gì?

Chỉ mục là một cấu trúc chứa trường mà chỉ mục đang sắp xếp và một con trỏ từ mỗi bản ghi đến bản ghi tương ứng của chúng trong bảng gốc nơi dữ liệu thực sự được lưu trữ. Chỉ mục được sử dụng trong những thứ như danh sách liên hệ, nơi dữ liệu có thể được lưu trữ thực tế theo thứ tự bạn thêm thông tin liên hệ của mọi người nhưng sẽ dễ dàng tìm thấy mọi người hơn khi được liệt kê theo thứ tự bảng chữ cái.

Hãy xem chỉ mục từ ví dụ trước và xem cách nó ánh xạ trở lại bảng Bạn bè ban đầu:

Ở đây, chúng ta có thể thấy rằng bảng có dữ liệu được lưu trữ được sắp xếp theo thứ tự tăng dần dựa trên thứ tự dữ liệu được thêm vào. Và Chỉ mục có các tên được lưu trữ theo thứ tự bảng chữ cái.

Các loại lập chỉ mục

Có hai loại chỉ mục cơ sở dữ liệu:

  1. Được phân nhóm
  2. Không theo nhóm

Cả chỉ mục được phân nhóm và không phân nhóm đều được lưu trữ và tìm kiếm dưới dạng cây B, một cấu trúc dữ liệu tương tự như cây nhị phân . B-tree là “cấu trúc dữ liệu dạng cây tự cân bằng nhằm duy trì dữ liệu được sắp xếp và cho phép tìm kiếm, truy cập tuần tự, chèn và xóa theo thời gian logarit.” Về cơ bản, nó tạo ra một cấu trúc giống như cây giúp sắp xếp dữ liệu để tìm kiếm nhanh chóng.

Đây là cây B của chỉ mục chúng tôi đã tạo. Mục nhập nhỏ nhất của chúng tôi là mục nhập ngoài cùng bên trái và mục nhập lớn nhất của chúng tôi là mục nhập ngoài cùng bên phải. Tất cả các truy vấn sẽ bắt đầu ở nút trên cùng và hoạt động theo cách của chúng xuống dưới cây, nếu mục nhập đích nhỏ hơn nút hiện tại thì đường dẫn bên trái được theo sau, nếu lớn hơn đường dẫn bên phải sẽ theo sau. Trong trường hợp của chúng tôi, nó đối đầu với Matt, sau đó là Todd và sau đó là Zack.

Để tăng hiệu quả, nhiều cây B sẽ giới hạn số ký tự bạn có thể nhập vào một mục nhập. Cây B sẽ tự thực hiện việc này và không yêu cầu dữ liệu cột bị hạn chế. Trong ví dụ trên, cây B bên dưới giới hạn mục nhập là 4 ký tự.

Xem Thêm  Hướng dẫn cơ bản về lệnh gọi lại JavaScript - giải thích hàm gọi lại javascript

Chỉ mục được nhóm

Chỉ mục được phân nhóm là chỉ mục duy nhất trên mỗi bảng sử dụng khóa chính để tổ chức dữ liệu trong bảng. Chỉ mục được phân cụm đảm bảo rằng khóa chính được lưu trữ theo thứ tự tăng dần, cũng là thứ tự mà bảng lưu giữ trong bộ nhớ.

  • Các chỉ mục được phân nhóm không cần phải được khai báo rõ ràng.
  • Được tạo khi bảng được tạo.
  • Sử dụng khóa chính được sắp xếp theo thứ tự tăng dần.

Tạo chỉ mục theo cụm

Chỉ mục theo nhóm sẽ được tạo tự động khi khóa chính được xác định:

  

TẠO

BẢNG

bạn bè

(

id

INT

CHÍNH

KEY

,

tên

VARCHAR

,

thành phố

VARCHAR

);

Sau khi điền, bảng đó sẽ trông giống như sau:

Bảng đã tạo, “bạn bè”, sẽ có một chỉ mục nhóm được tạo tự động, được tổ chức xung quanh “id” của Khóa chính được gọi là “friends_pkey”:

Khi tìm kiếm bảng theo “id”, thứ tự tăng dần của cột cho phép thực hiện các tìm kiếm tối ưu. Vì các số được sắp xếp theo thứ tự, việc tìm kiếm có thể điều hướng cây B cho phép tìm kiếm diễn ra theo thời gian logarit.

Tuy nhiên, để tìm kiếm “tên” hoặc “thành phố” trong bảng, chúng tôi sẽ phải xem xét mọi mục nhập vì các cột này không có chỉ mục. Đây là lúc các chỉ mục không phân cụm trở nên rất hữu ích.

Chỉ mục không phân cụm

Chỉ mục không phân cụm là các tham chiếu được sắp xếp cho một trường cụ thể, từ bảng chính, giữ con trỏ quay lại các mục gốc của bảng. Ví dụ đầu tiên mà chúng tôi đưa ra là một ví dụ về bảng không phân cụm:

Chúng được sử dụng để tăng tốc độ truy vấn trên bảng bằng cách tạo các cột dễ tìm kiếm hơn. Các nhà phân tích / nhà phát triển dữ liệu có thể tạo chỉ mục không theo nhóm sau khi tạo và điền bảng.

Lưu ý: Các chỉ mục không phân cụm không phải là bảng mới. Các chỉ mục không phân cụm giữ trường mà chúng chịu trách nhiệm sắp xếp và một con trỏ từ mỗi mục đó quay trở lại mục nhập đầy đủ trong bảng.

Bạn có thể coi những thứ này giống như các chỉ mục trong một cuốn sách. Chỉ mục chỉ đến vị trí trong sách nơi bạn có thể tìm thấy dữ liệu bạn đang tìm kiếm.

Các chỉ mục không phân cụm trỏ đến địa chỉ bộ nhớ thay vì tự lưu trữ dữ liệu. Điều này khiến chúng truy vấn chậm hơn so với các chỉ mục được phân nhóm nhưng thường nhanh hơn nhiều so với cột không được lập chỉ mục.

Bạn có thể tạo nhiều chỉ mục không phân cụm. Kể từ năm 2008, bạn có thể có tới 999 chỉ mục không phân cụm trong SQL Server và không có giới hạn trong PostgreSQL.

Tạo cơ sở dữ liệu không phân cụm (PostgreSQL)

Để tạo chỉ mục để sắp xếp tên bạn bè của chúng ta theo thứ tự bảng chữ cái:

  

TẠO

CHỈ SỐ

friends_name_asc

BẬT

bạn bè

(

tên

ASC

);

Điều này sẽ tạo một chỉ mục có tên là “friends_name_asc”, cho biết rằng chỉ mục này đang lưu trữ tên từ “bạn bè” được lưu trữ theo thứ tự bảng chữ cái tăng dần đặt hàng.

Lưu ý rằng cột “thành phố” không có trong chỉ mục này. Đó là bởi vì các chỉ mục không lưu trữ tất cả thông tin từ bảng gốc. Cột “id” sẽ là một con trỏ quay lại bảng ban đầu. Logic con trỏ sẽ giống như sau:

Tạo chỉ mục

Trong PostgreSQL, lệnh “\ d” được sử dụng để liệt kê các chi tiết trên bảng, bao gồm tên bảng, các cột trong bảng và các kiểu dữ liệu, chỉ mục và ràng buộc của chúng.

Chi tiết về bảng bạn bè của chúng ta bây giờ trông như thế này:

Truy vấn cung cấp thông tin chi tiết về bảng bạn bè : \ d bạn bè;

Nhìn vào hình ảnh trên, “friends_name_asc” hiện là một chỉ mục liên quan của bảng “friends”. Điều đó có nghĩa là kế hoạch truy vấn , kế hoạch mà SQL tạo ra khi xác định cách tốt nhất để thực hiện truy vấn, sẽ bắt đầu sử dụng chỉ mục khi các truy vấn đang được thực hiện. Lưu ý rằng “friends_pkey” được liệt kê dưới dạng chỉ mục mặc dù chúng tôi chưa bao giờ khai báo đó là chỉ mục. Đó là chỉ mục theo nhóm đã được tham chiếu trước đó trong bài viết được tạo tự động dựa trên khóa chính.

Chúng tôi cũng có thể thấy có chỉ mục “friends_city_desc”. Chỉ mục đó được tạo tương tự như chỉ mục tên:

  

TẠO

CHỈ SỐ

friends_city_desc

BẬT

bạn bè

(

thành phố

MÔ TẢ

);

Chỉ mục mới này sẽ được sử dụng để sắp xếp các thành phố và sẽ được lưu trữ theo thứ tự bảng chữ cái ngược lại vì từ khóa “DESC” đã được chuyển, viết tắt của “giảm dần”. Điều này cung cấp một cách để cơ sở dữ liệu của chúng tôi truy vấn tên thành phố một cách nhanh chóng.

Tìm kiếm chỉ mục

Sau khi các chỉ mục không phân cụm của bạn được tạo, bạn có thể bắt đầu truy vấn với chúng. Các chỉ mục sử dụng một phương pháp tìm kiếm tối ưu được gọi là tìm kiếm nhị phân. Tìm kiếm nhị phân hoạt động bằng cách liên tục cắt đôi dữ liệu và kiểm tra xem mục nhập bạn đang tìm kiếm có xuất hiện trước hoặc sau mục nhập ở giữa phần dữ liệu hiện tại hay không. Điều này hoạt động tốt với cây B vì chúng được thiết kế để bắt đầu ở lối vào giữa; để tìm kiếm các mục nhập trong cây, bạn biết các mục nhập ở đường dẫn bên trái sẽ nhỏ hơn hoặc trước mục nhập hiện tại và các mục nhập ở bên phải sẽ lớn hơn hoặc sau mục nhập hiện tại. Trong một bảng như sau:

So sánh phương pháp này với truy vấn của bảng không được lập chỉ mục ở đầu bài viết, chúng tôi có thể giảm tổng số tìm kiếm từ tám xuống ba. Sử dụng phương pháp này, tìm kiếm 1.000.000 mục nhập có thể giảm xuống chỉ còn 20 lần nhảy trong tìm kiếm nhị phân.

Khi nào sử dụng Chỉ mục

Chỉ mục có nghĩa là để tăng tốc hiệu suất của cơ sở dữ liệu, vì vậy hãy sử dụng lập chỉ mục bất cứ khi nào nó cải thiện đáng kể hiệu suất của cơ sở dữ liệu của bạn. Khi cơ sở dữ liệu của bạn ngày càng lớn hơn, bạn càng có nhiều khả năng nhận thấy lợi ích từ việc lập chỉ mục.

Khi nào không sử dụng Index

Khi dữ liệu được ghi vào cơ sở dữ liệu, bảng gốc (chỉ mục nhóm) được cập nhật trước tiên và sau đó tất cả các chỉ mục ngoài bảng đó sẽ được cập nhật. Mỗi khi ghi vào cơ sở dữ liệu, các chỉ mục sẽ không thể sử dụng được cho đến khi chúng được cập nhật. Nếu cơ sở dữ liệu liên tục nhận ghi thì các chỉ mục sẽ không bao giờ có thể sử dụng được. Đây là lý do tại sao các chỉ mục thường được áp dụng cho cơ sở dữ liệu trong kho dữ liệu nhận dữ liệu mới được cập nhật theo lịch trình (giờ thấp điểm) chứ không phải cơ sở dữ liệu sản xuất có thể luôn nhận các bản ghi mới.

LƯU Ý: phiên bản Postgres mới nhất (hiện đang ở phiên bản beta ) sẽ cho phép bạn truy vấn cơ sở dữ liệu trong khi các chỉ mục đang được cập nhật.

Kiểm tra hiệu suất của Chỉ mục

Để kiểm tra xem các chỉ mục có bắt đầu giảm thời gian truy vấn hay không, bạn có thể chạy một tập hợp các truy vấn trên cơ sở dữ liệu của mình, ghi lại thời gian cần các truy vấn đó để hoàn thành, sau đó bắt đầu tạo chỉ mục và chạy lại các thử nghiệm của bạn.

Để thực hiện việc này, hãy thử sử dụng mệnh đề GIẢI THÍCH PHÂN TÍCH trong PostgreSQL.:

  

GIẢI THÍCH

PHÂN TÍCH

CHỌN

*

TỪ

bạn bè

WHERE

tên

=

'Blake'

;

Kết quả nào trên cơ sở dữ liệu nhỏ của tôi:

Kết quả này sẽ cho bạn biết phương pháp tìm kiếm nào từ kế hoạch truy vấn đã được chọn và việc lập kế hoạch và thực hiện truy vấn mất bao lâu.

Mỗi lần chỉ tạo một chỉ mục vì không phải tất cả các chỉ mục sẽ làm giảm thời gian truy vấn.

  • Lập kế hoạch truy vấn của PostgreSQL khá hiệu quả, vì vậy việc thêm chỉ mục mới có thể không ảnh hưởng đến tốc độ thực hiện các truy vấn.
  • Thêm chỉ mục sẽ luôn có nghĩa là lưu trữ nhiều dữ liệu hơn
  • Việc thêm chỉ mục sẽ làm tăng thời gian cơ sở dữ liệu của bạn cập nhật đầy đủ sau một thao tác ghi.

Nếu việc thêm một chỉ mục không làm giảm thời gian truy vấn, bạn chỉ cần xóa nó khỏi cơ sở dữ liệu.

Để xóa một chỉ mục, hãy sử dụng lệnh DROP INDEX:

  

DROP

INDEX

friends_name_asc

;

Đường viền của cơ sở dữ liệu bây giờ trông giống như sau:

Trong đó hiển thị việc xóa thành công chỉ mục để tìm kiếm tên.

Tóm tắt

  • Lập chỉ mục có thể giảm đáng kể thời gian truy vấn
  • Mỗi bảng có khóa chính đều có một chỉ mục được nhóm
  • Mỗi bảng có thể có nhiều chỉ mục không phân cụm để hỗ trợ cho việc truy vấn
  • Các chỉ mục không phân cụm giữ các con trỏ quay lại bảng chính
  • Không phải mọi cơ sở dữ liệu đều được hưởng lợi từ việc lập chỉ mục
  • Không phải mọi chỉ mục sẽ tăng tốc độ truy vấn cho cơ sở dữ liệu

Tài liệu tham khảo:

https://www.geeksforgeeks.org/indexing-in-databases-set-1/
https://www.c-sharpcorner.com/blogs/differences- between-clustered-index-and-nonclustered-index1
https://en.wikipedia.org/wiki/B-tree
https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm
https: // www. cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/#

Được viết bởi:

Blake Barnhill

Xét bởi:

Matt David
,

Matthew Layne


Xem thêm những thông tin liên quan đến chủ đề sử dụng lập chỉ mục trong sql

SQL Server-S: Chỉ mục (Index)

  • Tác giả: V1Study.com
  • Ngày đăng: 2021-11-11
  • Đánh giá: 4 ⭐ ( 7530 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: SQL Server: Chỉ mục (Index):
    – Clustered Index
    – Nonclustered Index
    – Unique Index
    – Xem thông tin Index
    – Xóa Index

Chỉ mục (Index) trong SQL

  • Tác giả: vietjack.com
  • Đánh giá: 4 ⭐ ( 8332 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Chỉ mục (Index) trong SQL – Học SQL cơ bản nâng cao. Học SQL trực tuyến theo các bước từ Khái niệm về database, Cú pháp SQL, Truy vấn SELECT, Truy vấn INSERT, Lệnh DELETE, Truy vấn UPDATE, Ràng buộc (Constraint), Truy vấn DROP, Lệnh TRUNCATE, Từ khóa DISTINCT, Mệnh đề ORDER BY, Mệnh đề GROUP BY, Mệnh đề WHERE, Sử dụng Join, Sử dụng View, Sử dụng Sequence, Transaction, Hàm xử lý, Bảng tạm, Toán tử.

Index – Chỉ mục trong SQL – Deft Blog

  • Tác giả: shareprogramming.net
  • Đánh giá: 3 ⭐ ( 7421 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Chỉ mục (INDEX) trong SQL là bảng tra cứu đặc biệt mà công cụ tìm kiếm cơ sở dữ liệu có thể sử dụng để tăng nhanh thời gian và hiệu suất

Sử Dụng Index Sql Là Gì – Chỉ Mục (Index) Trong Sql

  • Tác giả: sentory.vn
  • Đánh giá: 3 ⭐ ( 3445 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Index (chỉ mục) trong SQL Server là các cấu trúc dữ liệu đặc biệt được liên kết với các bảng hoặc view giúp tăng tốc truy vấn. SQL Server cung cấp hai loại

Chỉ mục (index) trong mysql

  • Tác giả: viblo.asia
  • Đánh giá: 4 ⭐ ( 3198 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: 1. Khái niệm về chỉ mục (index) trong mysql

Lập chỉ mục cho hiệu suất cao trong SQL

  • Tác giả: itzone.com.vn
  • Đánh giá: 4 ⭐ ( 3809 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: The ITZone platform Vietnam is the community for anyone interested in news, training seminars, presentations etc in the IT industry

Chỉ mục (INDEX) trong MySQL

  • Tác giả: hoclaptrinh.vn
  • Đánh giá: 3 ⭐ ( 9649 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Một cơ sở dữ liệu với chỉ mục là một cấu trúc dữ liệu mà cải thiện tốc độ của các hoạt động trong một bảng.

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

By ads_php