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. Các chỉ mục có thể được tạo bởi sử dụng một hoặc nhiều cột, cung cấp cơ sở cho việc tăng nhanh quá trình tìm kiếm ngẫu nhiên và sắp xếp thứ tự truy cập tới các bản ghi một cách hiệu quả.

Trong khi tạo chỉ mục, bạn nên cân nhắc xem cột nào sẽ được sử dụng để tạo các truy vấn SQL và tạo một hoặc nhiều chỉ mục trên các cột đó.

Thực tế, các chỉ mục cũng là kiểu của bảng, mà giữ trường Primary Key hoặc trường index và một con trỏ tới mỗi bản ghi bên trong bảng thực sự.

Người sử dụng không thể thấy các chỉ mục này, chúng chỉ được sử dụng để tăng tốc các truy vấn và sẽ được sử dụng bởi Database Search Engine để định vị các bản ghi một cách cực kỳ nhanh.

Các lệnh INSERT và UPDATE tốn nhiều thời gian hơn trên các bảng có chỉ mục trong khi các lệnh SELECT trở nên nhanh hơn trên các bảng này. Lý do là vì, trong khi chèn và cập nhật, cơ sở dữ liệu cũng phải cần chèn hoặc cập nhật các giá trị chỉ mục.

Quảng cáo

Simple Index và Unique Index trong MySQL

Bạn có thể tạo một Unique Index trên một bảng. Một Unique Index nghĩa là hai hàng không thể có cùng giá trị chỉ mục. Dưới đây là cú pháp để tạo một chỉ mục trên một bảng.

CREATE UNIQUE INDEX ten_chi_muc ON ten_bang ( cot1, cot2,...);

Bạn có thể sử dụng một hoặc nhiều cột để tạo một chỉ mục. Ví dụ, chúng ta có thể tạo chỉ mục trên cột ten trong bảng sinhvienk60 như sau:

CREATE UNIQUE INDEX tenchimuc ON sinhvienk60 (ten)

Bạn có thể tạo một Simple Index trên một bảng. Đơn giản, bạn chỉ cần bỏ qua từ khóa UNIQUE từ truy vấn để tạo Simple Index. Simple Index cho phép tồn tại bản sao các giá trị trong một bảng.

Nếu bạn muốn lập chỉ mục các giá trị trong một cột theo thứ tự giảm dần, bạn có thể thêm từ DESC sau tên cột.

CREATE UNIQUE INDEX tenchimuc ON sinhvienk60 (ten DESC)

Quảng cáo

Lệnh ALTER để thêm và xóa INDEX trong MySQL

Có 4 kiểu lệnh để thêm các chỉ mục cho một bảng:

• ALTER TABLE ten_bang ADD PRIMARY KEY (danh_sach_cot): Lệnh này thêm một PRIMARY KEY, nghĩa là các giá trị được lập chỉ mục phải là duy nhất và không thể là NULL.
• ALTER TABLE ten_bang ADD UNIQUE ten_chi_muc (danh_sach_cot): Lệnh này tạo một chỉ mục cho các giá trị để giá trị đó phải là duy nhất (với giá trị NULL là ngoại lệ, chúng có thể xuất hiện nhiều lần).
• ALTER TABLE ten_bang ADD INDEX ten_chi_muc (danh_sach_cot): Lệnh này thêm một chỉ mục thông thường, trong đó bất kỳ giá trị nào có thể xuất hiện nhiều hơn một lần.
• ALTER TABLE ten_bang ADD FULLTEXT ten_chi_muc (danh_sach_cot): Lệnh này tạo một chỉ mục FULLTEXT đặc biệt, được sử dụng cho mục đích tìm kiếm văn bản.

Ví dụ sau để thêm chỉ mục cho cột ho trong bảng sinhvienk59 đang tồn tại.

ALTER TABLE sinhvienk59 ADD INDEX (ho);

Bạn có thể xóa bất kỳ INDEX nào bởi sử dụng mệnh đề DROP cùng với lệnh ALTER. Bạn xét ví dụ sau để xóa chỉ mục đã được tạo ở trên.

ALTER TABLE sinhvienk59 DROP INDEX (ho);

Lệnh ALTER để thêm và xóa PRIMARY KEY

Bạn cũng có thể thêm Primary Key theo cách tương tự. Nhưng đảm bảo rằng Primary Key làm việc trên các cột mà là NOT NULL.

Ví dụ sau để thêm Primary Key vào một bảng đang tồn tại. Đầu tiên, tạo cột là NOT NULL và sau đó thêm cho nó một Primary Key.

ALTER TABLE sinhvienk59 MODIFY diemthi INT NOT NULL; ALTER TABLE sinhvienk59 ADD PRIMARY KEY (diemthi);

Bạn có thể sử dụng lệnh ALTER để xóa một Primary Key như sau:

ALTER TABLE sinhvienk59 DROP PRIMARY KEY;

Để xóa một chỉ mục mà không là một PRIMARY KEY, bạn phải xác định tên chỉ mục.

Hiển thị thông tin chỉ mục trong MySQL

Bạn sử dụng lệnh SHOW INDEX để liệt kê tất cả chỉ mục được liên kết với một bảng. Kết quả trong định dạng dọc (được xác định bởi \G) thường là có ích hơn với lệnh này, để tránh một dòng liệt kê dài.

Bạn thử ví dụ sau:

SHOW INDEX FROM tenbang\G ........

Đã có app VietJack trên điện thoại, giải bài tập SGK, SBT Soạn văn, Văn mẫu, Thi online, Bài giảng....miễn phí. Tải ngay ứng dụng trên Android và iOS.

Theo dõi chúng tôi miễn phí trên mạng xã hội facebook và youtube:

Follow fanpage của team //www.facebook.com/vietjackteam/ hoặc facebook cá nhân Nguyễn Thanh Tuyền //www.facebook.com/tuyen.vietjack để tiếp tục theo dõi các loạt bài mới nhất về Java,C,C++,Javascript,HTML,Python,Database,Mobile.... mới nhất của chúng tôi.

Bạn đang tạo khóa ngoại nhưng gặp lỗi "missing index for constraint" trong MySQL? Vậy thì hãy tham khảo bài hướng dẫn này nhé, mình sẽ giúp bạn fix lỗi đó.

Nếu dịch ra tiếng Anh thì nó rất rõ nghĩa, đây là lỗi bị thiếu chỉ mục cho ràng buộc khóa ngoại. Theo như quy tắc thì khá ngoại của bảng con phải trỏ đến khóa chính của bảng cha, nhưng bạn đã không tạo khóa chính cho bảng cha.

Vì vậy giải pháp là bạn phải tạo chỉ mục khóa chính trước khi tạo khóa ngoai nhé.

Mình sẽ lấy một ví dụ trên stackoverflow luôn nhé.

Bài viết này được đăng tại [tranvanbinh.vn]

Tạo bảng con med_pharmacy

1

2

3

4

5

6

7

8

9

10

11

12

13

DROP TABLE IF EXISTS med_pharmacy;

CREATE TABLE IF NOT EXISTS med_pharmacy (

DROP`0`DROP`1`DROP`2`NOT `DROP`4 `DROP`5

DROP`7`DROP`1`DROP`2`NOT `DROP`4`TABLE`2

`TABLE`4`TABLE`5`TABLE`6`TABLE`7 `DROP`4`TABLE`2

IF EXISTS med_pharmacy; 1 IF EXISTS med_pharmacy; ``2 `TABLE`7 `DROP`4`TABLE`2

IF EXISTS med_pharmacy; 7 IF EXISTS med_pharmacy; ``2 `TABLE`7 `DROP`4`TABLE`2

`CREATE`3`DROP`1`DROP`2`TABLE`7 `DROP`4`TABLE`2

`TABLE`0`DROP`1`DROP`2`TABLE`7 `DROP`4`TABLE`2

`TABLE`7`TABLE`5`TABLE`6`TABLE`7 `DROP`4`TABLE`2

`IF `4 `IF `5 `IF `6

`IF `5 `IF `9

`NOT`0`TABLE`7 `NOT`2

Tạo bảng cha medication

1

2

3

4

5

6

7

8

9

DROP TABLE `NOT`5

CREATE TABLE `NOT`8

DROP`7`DROP`1`DROP`2`NOT `DROP`4`TABLE`2

EXISTS med_pharmacy ( 7`TABLE`5 EXISTS med_pharmacy ( `9`NOT `DROP`4`TABLE`2

` `4 IF EXISTS med_pharmacy; ``2 `TABLE`7 `DROP`4`TABLE`2

`TABLE`7`TABLE`5`TABLE`6`TABLE`7 `DROP`4`TABLE`2

`DROP`07`TABLE`5`DROP`09`TABLE`7 `DROP`4

`DROP`12`TABLE`7 `NOT`2

Bây giờ thêm khóa ngoại bằng lệnh SQL:

1

2

3

4

5

DROP`15 `TABLE `DROP`17

`DROP`18 `DROP`19 `DROP`20

`DROP`21 `IF `5 `DROP`23

`DROP`24

`DROP`25`DROP`26 `DROP`27 `DROP`28 `DROP`26 `DROP`30 `DROP`28 `DROP`32

Thì xuất hiện lỗi:

1822 - Failed to add the foreign key constaint.

Missing index for constraint 'fk_med_pharmacy_medication1' in the referenced table 'medication'

Nó nói khá rõ ràng, trong bảng medication bạn đã không tạo khóa chính cho med_id.

Vì vậy hãy thêm nó bằng lệnh sau:

1

DROP`15 `TABLE `DROP`35`DROP`18 `IF `4 `IF `5 `DROP`39

Rồi chạy lại lệnh tạo kháo ngoại ở trên nhé.

1

2

3

4

5

DROP`15 `TABLE `DROP`17

`DROP`18 `DROP`19 `DROP`20

`DROP`21 `IF `5 `DROP`23

`DROP`24

`DROP`25`DROP`26 `DROP`27 `DROP`28 `DROP`26 `DROP`30 `DROP`28 `DROP`32

Như vậy là mình đã hướng dẫn xong cách khắc phục lỗi "missing index for constraint" trong MySQL. Chúc bạn thành công!