VIEW
STORED PROCEDURE
INDEX
FULL TEXT SEARCH
TRIGGER
TIPS
CÁC CHỦ ĐỀ
BÀI MỚI NHẤT
MỚI CẬP NHẬT

Cách đánh Index trong MySQL: Index là gì và cách tạo thế nào?

Trong bài này bạn sẽ được học cách sư dụng lệnh CREATE INDEX để tạo Index trong MySQL, đây là một kỹ thuật giúp bạn tối ưu truy vấn trong CSDL MySQL.

test php

banquyen png
Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.

Thực ra khóa chính Primary Key cũng là một loại Index có tên gọi là Primary Key Index. Đây là kiểu index đặc biệt nên cho tốc độ truy vấn cực nhanh. Và để hiểu rõ hơn thì ta sẽ đi vào một tình huống cụ thể.

1. MySQL Index là gì?

cach danh index trong mysql png

Index là dữ liệu có cấu trúc như B-Tree giúp cải thiện tốc độ tìm kiếm trên một bảng, làm giảm chi phí thực hiện truy vấn. Việc tối ưu hóa chỉ mục sẽ giúp xác định được vị trí của dữ liệu cần tìm thay vì phải dò theo thứ tự hàng triệu record trong bảng.

Khi bạn tạo một khóa chính thì đồng nghĩa với việc bạn đã tạo một chỉ mục duy nhất, các hàng không được trùng dữ liệu với nhau, tên gọi của nó là PRIMARY, thuộc thể loai clustered index.

Bài viết này được đăng tại [free tuts .net]

Những chỉ mục khác khong phải là PRIMARY thì ta gọi là chỉ mục phụ secondary index hoặc non-clustered indexes.

2. Đặt vấn đề về Index trong MySQL

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 người dân trong thành phố. Bây giờ bạn muốn tìm số điện thoại của Bob Cat. Vì các tên được sắp xếp theo thứ tự abc nên trước tiên bạn tìm trang có tên cuối cùng là Cat, sau đó bạn tìm Bob và số điện thoại của anh ấy.

Nếu tên trong danh bạ điện thoại không được sắp xếp theo thứ tự abc, bạn sẽ cần phải đi qua tất cả các trang, đọc mọi tên trên đó cho đến khi bạn tìm thấy Bob Cat. Điều này được gọi là [tìm kiếm tuần tự]. Bạn đi qua tất cả các hàng cho đến khi bạn tìm thấy người có số điện thoại mà bạn đang tìm kiếm.

Liên hệ danh bạ điện thoại với bảng cơ sở dữ liệu, nếu bạn có bảng phonebooks 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:

SELECT
    phone_number
FROM 
    phonebooks
WHERE 
    first_name = 'Bob' AND 
    last_name = 'Cat';

Truy vấn thực hiện khá nhanh trong trường hợp table có một vài ngàn dữ liệu. Nhưng nếu bảng có hàng triệu record và không được đánh dấu chỉ mục thì truy vấn này sẽ mất khá nhiều thời gian xử lý.

3. Lệnh CREATE INDEX trong MySQL

Thông thường ta sẽ tạo chỉ mục ngay lúc tạo bảng bằng lệnh Create Table. Ví dụ dưới đây mình đã tạo một bảng và thiết lập chỉ mục cho nó luôn.

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);

Ngoài ra, để thêm một chỉ mục vào bảng có sẵn thì ta sử dụng lệnh CREATE INDEX, cú pháp như sau:

CREATE INDEX index_name ON table_name (column_list)

Để tạo chỉ mục cho một cột hoặc nhiều cột thì tốt nhất ta đặt cho nó một cái tên, phía sau là danh sách các cột.

CREATE INDEX idx_c4 ON t(c4);

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. Sau đây cho thấy loại chỉ mục cho phép dựa trên kiểu lưu trữ của bảng:

Storage Engine Allowed Index Types
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH, 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 được giới thiệu bởi MySQL. Chúng ta sẽ học cách sử dụng nó nhiều hơn ở các ví dụ tiếp theo.

4. Dùng CREATE INDEX tạo Index trong MySQL

Câu lệnh SQL sau đây sẽ tìm các nhân viên có chức danh là Sales Rep:

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

Kết quả như sau:

MySQL Index example png

Kết quả trả về 10 nhân viên đang làm việc với chức danh là Sales Rep. Và để xem chi tiết câu truy vấn này thì ta thêm lệnh EXPLAIN ngay đầu lệnh Select thì sẽ thu được kết quả như sau:

MySQL Index EXPLAIN statement png

Như bạn thấy, câu truy vấn đã duyệt qua tổng cộng là 23 rows để trả về 10 kết quả như trên.

Bây giờ mình sẽ tạo một chỉ mục cho cột jobTitle như sau:

CREATE INDEX jobTitle ON employees(jobTitle);

Và chạy lại câu truy vấn thêm một lần nữa:

EXPLAIN SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

Kêt quả thu được như sau:

MySQL Index effect png

Thật tuyệt vời, MySQL chỉ duyệt qua 17 rows thay vì 23 rows như ở trên. Điều này chính là nhờ vào kỹ thuật tạo index mà mình đã làm.

Để xem tất cả những index thuộc một table nào đó thì ta sử dụng lệnh SHOW INDEXES.

SHOW INDEXES FROM employees;

Kết quả như hình dưới đây là một ví dụ khá chi tiết, nó hiển thị thông tin và kiểu index rõ ràng.

MySQL Create Index Example png

Như vậy là chúng ta đã học xong cách tạo Index trong MySQL. Đây là một bài khá quan trọng để giúp bạn hiểu được các kiểu index ở những bài tiếp theo.

Cùng chuyên mục:

Hàm ENCRYPT trong MySQL

Hàm ENCRYPT trong MySQL

Cách sử dụng ENCRYPT trong MySQL

Hàm MD5 trong MySQL

Hàm MD5 trong MySQL

Cách sử dụng MD5 trong MySQL

Hàm OLD_PASSWORD trong MySQL

Hàm OLD_PASSWORD trong MySQL

Cách sử dụng OLD_PASSWORD trong MySQL

Hàm PASSWORD trong MySQL

Hàm PASSWORD trong MySQL

Cách sử dụng PASSWORD trong MySQL

Hàm ASCII trong MySQL

Hàm ASCII trong MySQL

Cách sử dụng ASCII trong MySQL

Hàm CHAR_LENGTH trong MySQL

Hàm CHAR_LENGTH trong MySQL

Cách sử dụng CHAR_LENGTH trong MySQL

Hàm CHARACTER_LENGTH trong MySQL

Hàm CHARACTER_LENGTH trong MySQL

Cách sử dụng CHARACTER_LENGTH trong MySQL

Hàm CONCAT trong MySQL

Hàm CONCAT trong MySQL

Cách sử dụng CONCAT trong MySQL

Hàm CONCAT_WS trong MySQL

Hàm CONCAT_WS trong MySQL

Cách sử dụng CONCAT_WS trong MySQL

Hàm FIELD trong MySQL

Hàm FIELD trong MySQL

Cách sử dụng FIELD trong MySQL

Hàm FIND_IN_SET trong MySQL

Hàm FIND_IN_SET trong MySQL

Cách sử dụng FIND_IN_SET trong MySQL

Hàm FORMAT trong MySQL

Hàm FORMAT trong MySQL

Cách sử dụng FORMAT trong MySQL

Hàm INSERT trong MySQL

Hàm INSERT trong MySQL

Cách sử dụng INSERT trong MySQL

Hàm INSTR trong MySQL

Hàm INSTR trong MySQL

Cách sử dụng INSTR trong MySQL

Hàm LCASE trong MySQL

Hàm LCASE trong MySQL

Cách sử dụng LCASE trong MySQL

Hàm LEFT trong MySQL

Hàm LEFT trong MySQL

Cách sử dụng LEFT trong MySQL

Hàm LENGTH trong MySQL

Hàm LENGTH trong MySQL

Cách sử dụng LENGTH trong MySQL

Hàm LOCATE trong MySQL

Hàm LOCATE trong MySQL

Cách sử dụng LOCATE trong MySQL

Hàm LOWER trong MySQL

Hàm LOWER trong MySQL

Cách sử dụng LOWER trong MySQL

Hàm LPAD trong MySQL

Hàm LPAD trong MySQL

Cách sử dụng LPAD trong MySQL

Top