Understanding InnoDB clustered indexes

Original source: http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/


Hiểu nhóm chỉ số InnoDB

Viết bởi ovais.tariq  | Thể loại: Indexing, Innodb, MySQL  | lượt xem: 25,619

 

Một số người hầu như không biết, nhưng có một sự khác biệt giữa việc các chỉ số hoạt động trong MyISAM và làm thế nào chúng hoạt động trong InnoDB, cụ thể là khi nói từ các góc độ nâng cao hiệu suất. Kể từ bây giờ, InnoDB bắt đầu được sử dụng rộng rãi, điều quan trọng là chúng ta hiểu cách lập chỉ mục hoạt động trong InnoDB. Vì vậy, đó chính là lý do cho bài viết này!

 

Điều đầu tiên và quan trọng nhất để biết là InnoDB sử dụng nhóm chỉ số để lưu trữ dữ liệu trong bảng. Bây giờ các nhóm chỉ số có nghĩa là gì?

 

Phân cụm chỉ số

Một nhóm chỉ số xác định trật tự vật lý của dữ liệu trong một bảng thống kê. Khi nghĩ về một nhóm chỉ số hãy nghĩ về một thư mục điện thoại, nơi dữ liệu được sắp xếp bởi tính chất vật lý tên cuối. Bởi vì các chỉ số nhóm quyết định thứ tự lưu trữ vật lý của dữ liệu trong bảng, một bảng chỉ có thể có một nhóm chỉ số duy nhất. Nhưng, một nhóm chỉ số có thể bao gồm nhiều cột (một chỉ số tổng hợp), trong cùng một cách như một thư mục điện thoại được tổ chức bởi cả hai tên đầu tiên và tên cuối.

 

Chỉ số phân cụm đối với InnoDB

InnoDB lưu trữ các chỉ số cũng như B + tree cấu trúc dữ liệu phân nhánh, và cùng là trường hợp với các chỉ số nhóm. Nhưng sự khác biệt là trong trường hợp của chùm chỉ số InnoDB thực ra lưu giữ chỉ số và các dòng với nhau trong cùng một cấu trúc. Khi một bảng có một nhóm chỉ số , hàng của nó đang thực sự được lưu trữ trong các trang lá của chỉ số này. Như vậy bảng InnoDB cũng có thể được gọi là bảng nhóm chỉ số có tổ chức.

 

Bây giờ hãy xem xét cách InnoDB quyết định chỉ số sử dụng như là các chỉ số nhóm!

 

Làm thế nào InnoDB chọn một nhóm chỉ số?

Với InnoDB, thường là PRIMARY KEY được đồng nghĩa với nhóm chỉ số, nhưng phải làm sao  nếu một PRIMARY KEY không tồn tại hoặc thậm chí không có một chỉ số duy nhất được định nghĩa trên bàn. Sau đó, sau đây là cách InnoDB quyết định những gì cần sử dụng làm các nhóm chỉ số:

 

  • Nếu có một PRIMARY KEY được xác định trên bảng, InnoDB sử dụng nó như là các cụm chỉ số .
  • Nếu không có PRIMARY KEY được xác định trên bảng, InnoDB sử dụng các chỉ số UNIQUE đầu tiên mà tất cả các cột chính có NOT NULL cũng như các chỉ số nhóm.
  • Nếu không có PRIMARY KEY hoặc không phù hợp chỉ số UNIQUE hiện tại, InnoDB nội bộ tạo ra một PRIMARY KEY ẩn và sau đó sử dụng khóa bí mật này cũng như các chỉ số nhóm. PRIMARY KEY ẩn này là một trường 6-byte làm tăng đều đều theo các dòng mới được chèn vào.

 

Do vậy, lời khuyên của tôi là luôn luôn xác định một PRIMARY KEY cho mỗi bảng mà bạn tạo ra. Nếu không có khóa hợp lý có thể được tạo ra, thêm một cột tự động tăng mới, và sử dụng nó như là PRIMARY KEY.

 

Bạn có biết rằng Chỉ số thứ cấp (Secondary Index) có liên quan đến Primary Key?

Trong InnoDB, mỗi INDEX SECONDARY chứa các cột PRIMARY KEY (số nhiều) cùng với các cột (số nhiều) của các chỉ số phụ, một cách tự động. Điều này do cách lưu trữ dữ liệu InnoDB, nhớ những gì tôi chỉ nói với bạn khi nói về cách dữ liệu được lưu trữ, một nút lá không lưu giữ bất kỳ con trỏ đến vị trí địa lý của hàng, nhưng trong thực tế lưu trữ dữ liệu của hàng. Vì vậy, nói cách khác PRIMARY KEY thực ra được cho con trỏ tới dữ liệu hàng.

 

Điều này làm cho chúng ta kết luận về một kết quả thú vị ..

 

Một chỉ số thứ cấp secondary index yêu cầu lookups! Lần đầu thì chỉ số thứ cấp sẽ tự lookup tra cứu chính nó, nhưng sau đó sẽ look up cho một khóa chính primary key

 

Ưu điểm của phân nhóm

Phân nhóm cung cấp bởi InnoDB có những lợi ích hiệu suất rất đáng kể, một số trong đó có đề cập dưới đây:

 

  • Bởi vì dữ liệu do lưu trữ cơ bản theo PRIMARY KEY, time kiếm lookups dữ liệu bởi PRIMARY KEY là rất nhanh. Ví dụ, cách nhanh nhất để tìm một nhân viên đặc biệt sử dụng cột Employeed_ID duy nhất là để tạo ra một PRIMARY KEY trên cột Employeed_ID.
  • Với việc phân nhóm tìm kiếm cho các phạm vi có thể cực kỳ hiệu quả. Giả sử một ứng dụng thường xuyên tìm kiếm các hồ sơ giữa một loạt các ngày, một nhóm chỉ số có thể nhanh chóng xác định các hàng chứa ngày đầu, và sau đó lấy tất cả các hàng liền kề trong bảng cho đến ngày cuối cùng là được. Do đó cải thiện hiệu suất của các truy vấn hàng loạt.
  • Một tác động tích cực của việc phân nhóm là về việc thực hiện phân loại dữ liệu. Giả sử có một cột được sử dụng thường xuyên để sắp xếp các dữ liệu lấy từ một bảng, nó có thể là lợi thế cho cụm bảng trên cột đó để tiết kiệm chi phí của một phân loại mỗi lần cột được truy vấn.
  • Cũng bởi vì chỉ số được phân nhóm cả bộ chỉ số và các dữ liệu với nhau trong một B-Tree, nên lấy các hàng từ một nhóm chỉ số bình thường được nhanh hơn so với một tra cứu so sánh trong một chỉ số không được phân nhóm.

 

Chỉ số thư cấp (Secondary index) có thể hoạt động cũng như các chỉ số chung, khi dữ liệu được yêu cầu bao gồm các cột khóa chính, vì thực tế là chỉ số thứ cấp sẽ tự động bao gồm các cột khóa chính.

 

Nhược điểm của phân nhóm

Sau đây là một trong những nhược điểm của phân nhóm:

 

  • Nếu một nhóm chỉ số lượng lớn được xác định, bất kỳ chỉ số thứ cấp được xác định trên cùng một bảng sẽ được lớn hơn đáng kể vì các chỉ số thứ cấp chứa phím clustering – phím phân nhóm.
  • Bởi vì cách đến cách dữ liệu được lưu trữ, chỉ số thứ cấp yêu cầu hai time kiếm lookups.
  • Chỉ số nhóm có thể tốn kém cho các cột phải trải qua những thay đổi thường xuyên bởi vì nó buộc InnoDB phải di chuyển mỗi hàng được cập nhật đến một vị trí mới.
  • Insertions- Chèn có thể được làm chậm, nếu dữ liệu không được chèn vào trong theo thứ tự PRIMARY KEY, vì thế chúng ta có thể kết luận rằng tốc độ chèn phụ thuộc rất nhiều vào thứ tự chèn. Chèn hàng để khóa chính primary key là cách nhanh nhất để tải dữ liệu vào một bảng InnoDB.

 

UPDATE- CẬP NHẬT (CẢM ƠN SUNNY):

Sau đây là một điều mà chúng ta nên biết về chỉ số thứ cấp:

 

Các bản ghi trong InnoDB thứ cấp không bao giờ được cập nhật tại chỗ. Vì vậy, điều đó có nghĩa là một UPDATE của một cột chỉ số thứ cấp có nghĩa là xóa các bản ghi cũ và chèn một hình mới.

 

Mặc dù, tôi đã chỉ ra một số nhược điểm, nhưng thực tế là những nhược điểm không được đặt năng bởi số lượng lớn các lợi ích mà đi kèm với việc nhóm trong InnoDB. Nếu bạn học tập và hiểu những khía cạnh mà tôi đã đề cập trong bài viết này và áp dụng chúng cho phù hợp, bạn sẽ thấy những cải tiến hiệu suất tuyệt vời. Sau cùng, việc phân nhóm clustering có một bước quan trọng trong việc mang lại MySQL gần gũi hơn với MSSQL và Oracle.


 

Sơ đồ trang

 

 

Advertisements