Khi nào tôi nên TẠO INDEX trong MySQL?

Chỉ mục là một cấu trúc dữ liệu cho phép chúng ta thêm các chỉ mục trong bảng hiện có. Nó cho phép bạn cải thiện việc truy xuất bản ghi nhanh hơn trên bảng cơ sở dữ liệu. Nó tạo một mục nhập cho từng giá trị của các cột được lập chỉ mục. Chúng tôi sử dụng nó để nhanh chóng tìm thấy bản ghi mà không cần tìm kiếm từng hàng trong bảng cơ sở dữ liệu bất cứ khi nào bảng được truy cập. Chúng ta có thể tạo một chỉ mục bằng cách sử dụng một hoặc nhiều cột của bảng để truy cập hiệu quả vào các bản ghi

Khi một bảng được tạo bằng khóa chính hoặc khóa duy nhất, nó sẽ tự động tạo một chỉ mục đặc biệt có tên là CHÍNH. Chúng tôi gọi chỉ mục này là chỉ mục nhóm. Tất cả các chỉ mục khác với chỉ mục CHÍNH được gọi là chỉ mục không nhóm hoặc chỉ mục phụ

Cần lập chỉ mục trong MySQL

Giả sử chúng ta có một sổ liên lạc chứa tên và số điện thoại di động của người dùng. Trong sổ liên lạc này, chúng tôi muốn tìm số điện thoại di động của Martin Williamson. Nếu sổ liên lạc ở dạng không có thứ tự nghĩa là tên của sổ liên lạc không được sắp xếp theo thứ tự abc, chúng ta cần phải xem qua tất cả các trang và đọc từng tên cho đến khi không tìm thấy tên mong muốn mà chúng ta đang tìm kiếm. Loại tên tìm kiếm này được gọi là tìm kiếm tuần tự

Để tìm tên và liên hệ của người dùng từ bảng danh bạ, thông thường, chúng ta thường thực hiện truy vấn sau

Truy vấn này rất đơn giản và dễ dàng. Mặc dù tìm thấy số điện thoại và tên của người dùng nhanh chóng, nhưng cơ sở dữ liệu sẽ tìm kiếm toàn bộ các hàng của bảng cho đến khi không tìm thấy các hàng mà bạn muốn. Giả sử bảng danh bạ chứa hàng triệu hàng, khi đó nếu không có chỉ mục thì việc truy xuất dữ liệu mất rất nhiều thời gian mới tìm được kết quả. Trong trường hợp đó, lập chỉ mục cơ sở dữ liệu đóng một vai trò quan trọng trong việc trả về kết quả mong muốn và cải thiện hiệu suất tổng thể của truy vấn

Câu lệnh TẠO INDEX của MySQL

Nói chung, chúng tôi tạo một chỉ mục tại thời điểm tạo bảng trong cơ sở dữ liệu. Câu lệnh sau đây tạo một bảng có chỉ mục chứa hai cột col2 và col3

Nếu chúng ta muốn thêm chỉ mục vào bảng, chúng ta sẽ sử dụng câu lệnh CREATE INDEX như sau

Trong câu lệnh này, index_name là tên của chỉ mục, table_name là tên của bảng chứa chỉ mục và column_names là danh sách các cột

Hãy để chúng tôi thêm chỉ mục mới cho cột col4, chúng tôi sử dụng câu lệnh sau

Theo mặc định, MySQL cho phép loại chỉ mục BTREE nếu chúng tôi chưa chỉ định loại chỉ mục. Bảng sau đây hiển thị các loại chỉ mục khác nhau dựa trên công cụ lưu trữ của bảng

Ví dụ

Trong ví dụ này, chúng ta sẽ tạo một bảng student và thực hiện câu lệnh CREATE INDEX trên bảng đó

Tên bảng. sinh viên

Bây giờ, hãy thực hiện câu lệnh sau để trả về kết quả của sinh viên có lớp là nhánh CS

Tuyên bố này sẽ cung cấp cho đầu ra sau

Trong bảng trên, chúng ta có thể thấy bốn hàng biểu thị các sinh viên có lớp là chi nhánh CS

Nếu bạn muốn xem cách MySQL thực hiện truy vấn này bên trong, hãy thực hiện câu lệnh sau

Bạn sẽ nhận được đầu ra dưới đây. Ở đây, MySQL quét toàn bộ bảng có bảy hàng để tìm sinh viên có lớp là nhánh CS

Bây giờ, chúng ta hãy tạo một chỉ mục cho cột lớp bằng cách sử dụng câu lệnh sau

Sau khi thực hiện câu lệnh trên, chỉ mục được tạo thành công. Bây giờ, hãy chạy câu lệnh dưới đây để xem cách MySQL thực hiện truy vấn này bên trong

Câu lệnh trên cho đầu ra, như hình bên dưới

Trong kết quả này, MySQL tìm thấy bốn hàng từ chỉ mục lớp mà không cần quét toàn bộ bảng. Do đó, nó làm tăng tốc độ truy xuất các bản ghi trên bảng cơ sở dữ liệu

Khi quyết định thời điểm và cách tạo chỉ mục trong cơ sở dữ liệu MySQL của bạn, điều quan trọng là phải xem xét cách dữ liệu đang được sử dụng

Giả sử bạn có một cơ sở dữ liệu về nhân viên. Chúng ta sẽ tạo nó như thế này

TẠO BẢNG nhân viên [
ID INT,
tên VARCHAR[60],
lương thập phân[10,
date hired[date]
]

Vì vậy, bạn sẽ nhận thấy rằng bảng này khá đơn giản và không thực sự chứa tất cả thông tin bạn cần để thực sự quản lý nhân viên, nhưng nó chỉ nhằm mục đích minh họa và bạn luôn có thể bổ sung thêm sau hoặc thậm chí tạo một bảng khác

Bây giờ chúng ta sẽ lướt nhanh qua những

ID về cơ bản chỉ là một số [INT] có thể chứa một số rất lớn. Nếu đây là thế giới thực, tôi có thể sẽ làm cho nó không có chữ ký, vì bạn sẽ không bao giờ có ID nhân viên âm – nhưng dù sao đi nữa, bạn sẽ không bao giờ đạt được số lượng nhân viên cần thiết để đạt được số điền vào INT

Ngay cả unsigned int sẽ giữ các giá trị lên tới 2.147.483.647. Vì vậy, nếu bạn có 2 tỷ nhân viên, có lẽ bạn sẽ không còn là nhà phát triển nữa ;-]

Bạn có thể muốn xem xét việc đặt trường ở mức tăng tự động và khóa chính, mức tăng tự động tùy thuộc vào cách dữ liệu sẽ được nhập vào cơ sở dữ liệu này

Tên là một varchar[60] đơn giản sẽ bao gồm hầu hết tên của mọi người

Lương là một số thập phân có tổng cộng 10 chữ số, hai chữ số ở bên phải dấu thập phân. Điều này sẽ xử lý mức lương lên tới 99.999.999. 99 – một lần nữa, bạn không có khả năng đạt đến giới hạn này

Ngày được thuê sẽ là một ngày ở định dạng này 2010-05-06. YYYY-MM-DD

Vì vậy, khi xem xét bảng đơn giản này, bạn sẽ cần chỉ mục ở đâu?
Nếu chúng tôi chỉ định ID làm khóa chính, thì chúng tôi không cần chỉ mục ở đó.

Chỉ mục được sử dụng tốt nhất trên các cột thường được sử dụng trong mệnh đề where và trong bất kỳ kiểu sắp xếp nào, chẳng hạn như "sắp xếp theo"

Bạn cũng nên chú ý xem thông tin này có thay đổi thường xuyên hay không, vì nó sẽ làm chậm quá trình cập nhật và chèn của bạn. Vì bạn sẽ không thường xuyên thêm nhân viên nên bạn không phải lo lắng về việc chèn

Giả sử rằng bạn sẽ tra cứu nhân viên bằng giao diện web php và người dùng cuối sẽ nhập tên nhân viên để tìm họ, vì việc ghi nhớ ID nhân viên sẽ rất phức tạp

Có vẻ như tình huống này sẽ tốt hơn nếu sử dụng một chỉ mục

A – Bạn sẽ không cập nhật tên của nhân viên thường xuyên, vì vậy bạn không phải lo lắng về hiệu suất đạt được ở đó

B – Bạn SẼ sử dụng nhân viên trong các mệnh đề như thế này

chọn * từ nhân viên nơi tên ='thợ rèn';

C – Bạn SẼ tạo báo cáo, có thể sẽ theo thứ tự bảng chữ cái, như thế này

select * from employees order by name asc;

Vì vậy, trong ví dụ đơn giản này, thật dễ dàng để thấy khi nào thì việc sử dụng các chỉ mục là quan trọng

Vì vậy, bạn có thể làm điều đó như thế này

create index name_index on employees [name];

Bạn có thể đang làm việc trên một cơ sở dữ liệu phức tạp hơn, vì vậy bạn nên nhớ một vài quy tắc đơn giản

- Các chỉ mục làm chậm quá trình chèn và cập nhật, vì vậy bạn muốn sử dụng chúng cẩn thận trên các cột THƯỜNG XUYÊN cập nhật

- Các chỉ số tăng tốc nơi mệnh đề và sắp xếp theo

Hãy nhớ suy nghĩ về CÁCH dữ liệu của bạn sẽ được sử dụng khi xây dựng các bảng của bạn

Có một vài điều khác cần nhớ. Nếu bàn của bạn rất nhỏ, tôi. e. , chỉ có một vài nhân viên, sử dụng một chỉ mục còn tệ hơn là bỏ nó đi và chỉ để nó quét bảng. Các chỉ mục thực sự chỉ có ích với các bảng có nhiều hàng

Vì vậy, nếu Cửa hàng thú cưng của Joe đang sử dụng cơ sở dữ liệu này, họ có thể bỏ chỉ mục ra khỏi cột "tên"

Nếu Microsoft đang sử dụng cơ sở dữ liệu này [hah. ] họ có thể muốn ném và lập chỉ mục trong đó

Một điều khác cần nhớ, đó là một nhược điểm trong tình huống cơ sở dữ liệu nhân viên của chúng tôi, đó là nếu cột có độ dài thay đổi, thì các chỉ mục [cũng như hầu hết MySQL] hoạt động kém hiệu quả hơn nhiều

Như bạn có thể thấy, có rất nhiều điều cần xem xét với các chỉ mục, ngay cả với một bảng rất đơn giản như thế này

Tôi khuyên bạn nên xem lệnh giải thích trong MySQL, mà tôi sẽ viết về nó trong tương lai

Khi nào bạn nên tạo cơ sở dữ liệu chỉ mục?

Khi nào chúng ta nên sử dụng lập chỉ mục?