Tìm hiểu MySQL CREATE INDEX Statement bằng các ví dụ thực tế – mysql tạo chỉ mục ví dụ

Tóm tắt : trong này hướng dẫn, bạn sẽ tìm hiểu về chỉ mục và cách sử dụng câu lệnh MySQL CREATE INDEX để thêm chỉ mục vào bảng.

Tương tự danh bạ điện thoại

Giả sử bạn có một danh bạ điện thoại chứa tất cả tên và số điện thoại của những người trong một thành phố. Giả sử bạn muốn tìm số điện thoại của Bob Cat. Biết rằng các tên được sắp xếp theo thứ tự bảng chữ cái, trước tiên bạn tìm trang có họ là Cat , sau đó tìm Bob và số điện thoại của anh ấy.

< p> Bây giờ, nếu các tên trong danh bạ điện thoại không được sắp xếp theo thứ tự bảng chữ cái, bạn cần phải xem qua tất cả các trang, đọc từng tên trên đó cho đến khi bạn tìm thấy Bob Cat . Đây được gọi là tìm kiếm tuần tự. Bạn xem qua tất cả các mục nhập cho đến khi bạn tìm thấy người có số điện thoại bạn đang tìm.

Liên hệ danh bạ với bảng cơ sở dữ liệu, nếu bạn có bảng danh bạ và bạn phải tìm số điện thoại của Bob Cat , bạn sẽ thực hiện truy vấn sau:

 
  
 CHỌN 

    số điện thoại
 TỪ 
    danh bạ
 Ở ĐÂU 
    first_name = 
 'Bob' 
 
 VÀ 
    last_name = 
 'Cat' 
;  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 

Nó khá dễ dàng. Mặc dù truy vấn nhanh nhưng cơ sở dữ liệu phải quét tất cả các hàng của bảng cho đến khi tìm thấy hàng đó. Nếu bảng có hàng triệu hàng, không có chỉ mục, thì việc truy xuất dữ liệu sẽ mất rất nhiều thời gian để trả về kết quả.

Giới thiệu về chỉ mục

Chỉ mục là một cấu trúc dữ liệu như vậy như B-Tree, cải thiện tốc độ truy xuất dữ liệu trên bảng với chi phí ghi và lưu trữ bổ sung để duy trì nó.

Trình tối ưu hóa truy vấn có thể sử dụng các chỉ mục để nhanh chóng xác định vị trí dữ liệu mà không cần phải quét mọi hàng trong một bảng cho một truy vấn nhất định.

Khi bạn tạo bảng bằng khóa chính hoặc khóa duy nhất , MySQL sẽ tự động tạo một chỉ mục đặc biệt có tên PRIMARY . Chỉ mục này được gọi là chỉ mục nhóm .

Chỉ mục PRIMARY đặc biệt vì bản thân chỉ mục được lưu trữ cùng với dữ liệu trong cùng một bảng. Chỉ mục được phân nhóm thực thi thứ tự của các hàng trong bảng.

Các chỉ mục khác ngoài chỉ mục PRIMARY được gọi là chỉ mục phụ hoặc chỉ mục không được phân nhóm.

Câu lệnh MySQL CREATE INDEX

Thông thường, bạn tạo chỉ mục cho một bảng tại thời điểm tạo. Ví dụ: câu lệnh sau tạo một bảng mới với chỉ mục bao gồm hai cột c2 và c3.

 
  
 TẠO 
 
 BẢNG 
 t (
   c1 
 INT 
 PRIMARY 
 KEY 
,
   c2 
 INT 
 
 NOT 
 
 KHÔNG ĐỦ 
,
   c3 
 INT 
 
 NOT 
 
 KHÔNG ĐẦY ĐỦ 
,
   c4 
 VARCHAR 
 (
 10 
),
   
 CHỈ SỐ 
 (c2, c3)
);  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 

Tới thêm chỉ mục cho một cột hoặc một tập hợp các cột, bạn sử dụng câu lệnh CREATE INDEX như sau:

 
  
 TẠO 
 
 CHỈ SỐ 
 index_name 
 BẬT 
 table_name (column_list)  
 
 Code language: 
 
 SQL (Structured Query Language) < / p> 
 (
 
 sql 
 
) 
  
 Để tạo chỉ mục cho một cột hoặc danh sách các cột, bạn chỉ định tên chỉ mục, bảng chứa chỉ mục và danh sách cột. 
 Ví dụ: để thêm chỉ mục mới cho cột c4, bạn sử dụng câu lệnh sau: 
 
 
  
 TẠO 
 
 CHỈ SỐ 
 idx_c4 
 BẬT 
 t ( c4);  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 
 Theo mặc định, MySQL tạo chỉ mục B-Tree nếu bạn không chỉ định loại chỉ mục. Phần sau hiển thị loại chỉ mục được phép dựa trên công cụ lưu trữ của bảng: 
 Storage EngineAllowed Index TypesInnoDBBTREEMyISAMBTREEMEMORY / HEAPHASH, BTREE 
 Lưu ý rằng câu lệnh  CREATE INDEX  ở trên là phiên bản đơn giản hóa của câu lệnh  CREATE INDEX  do MySQL giới thiệu. Chúng tôi sẽ đề cập đến các tùy chọn khác trong các hướng dẫn tiếp theo. 
 MySQL  CREATE INDEX  example 
 Câu lệnh sau tìm nhân viên có chức danh là  Đại diện bán hàng < / code>: 

 
  
 CHỌN 

    Số nhân viên,
    họ,
    họ
 TỪ 
    người lao động
 Ở ĐÂU 
    jobTitle = 
 'Đại diện bán hàng' 
;  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 
 Đây là kết quả: 
  
 Chúng tôi có 17 hàng cho biết 17 nhân viên có chức danh là Đại diện bán hàng. 
 Để xem cách MySQL thực hiện nội bộ truy vấn này, bạn thêm mệnh đề  EXPLAIN  ở đầu  Câu lệnh SELECT  như sau: 
  
 Như bạn có thể xem, MySQL đã phải quét toàn bộ bảng bao gồm 23 hàng để tìm nhân viên có chức danh công việc  Đại diện bán hàng . 
 Bây giờ, hãy tạo chỉ mục cho  cột jobTitle  bằng cách sử dụng câu lệnh  CREATE INDEX : 
 
  
 TẠO 
 
 CHỈ SỐ 
 jobTitle 
 BẬT 
 nhân viên (jobTitle) ;  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 
 Và thực thi lại câu lệnh trên: 
 
  
 GIẢI THÍCH 
 
 CHỌN 
    Số nhân viên,
    họ,
    họ
 TỪ 
    người lao động
 Ở ĐÂU 
    jobTitle = 
 'Đại diện bán hàng' 
;  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 
 Đầu ra là: 
  
 Như bạn có thể thấy, MySQL chỉ cần định vị 17 hàng từ chỉ mục  jobTitle  như được chỉ ra trong cột khóa mà không cần quét toàn bộ bảng. 
 Để hiển thị các chỉ mục của bảng, bạn sử dụng   SHOW INDEXES  , ví dụ: 
 
  
 HIỂN THỊ 
 
 CHỈ SỐ 
 
 TỪ 
 nhân viên;  
 
 Ngôn ngữ mã: 
 
 SQL (Ngôn ngữ truy vấn có cấu trúc) 
 
 (
 
 sql 
 
) 
 
 Đây là kết quả: 
  
 Trong hướng dẫn này , bạn đã tìm hiểu về chỉ mục MySQL và cách thêm chỉ mục cho một cột trong bảng. 
 Hướng dẫn này có hữu ích không?