Index trong Mysql và cách sử dụng

Một số database là một cấu trúc dữ liệu để cải thiện tốc độ của các hoạt động trong một bảng. Chỉ số có thể được tạo ra bằng cách sử dụng một hoặc nhiều cột, cung cấp cơ sở cho việc tra cứu ngẫu nhiên cả hai nhanh chóng và hiệu quả.

Trong khi tạo index, nó cần được xem xét rằng các cột đó sẽ được sử dụng để thực hiện các truy vấn SQL và tạo ra một hoặc nhiều chỉ số trên các cột đó là gì.

Thực tế, index cũng là loại bảng, mà giữ khóa chính hoặc trường index và một con trỏ đến từng bản ghi vào bảng thực tế.

Người sử dụng không thể nhìn thấy các index, chúng chỉ được sử dụng để tăng tốc độ truy vấn và sẽ được sử dụng bởi cơ sở dữ liệu tìm kiếm để xác định vị trí bản ghi rất nhanh.

Các lệnh INSERT và UPDATE tốn nhiều thời gian hơn trên các bảng có index 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, database cũng phải cần chèn hoặc cập nhật các giá trị index.

Simple Index và Unique Index

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 index_name
ON table_name ( column1, column2,...);

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 users sử dụng email.

CREATE UNIQUE INDEX user_email
ON users (email);

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 user_email
ON users (email DESC)

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 tbl_name ADD PRIMARY KEY (column_list)

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 tbl_name ADD UNIQUE index_name (column_list)

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 tbl_name ADD INDEX index_name (column_list)

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 tbl_name ADD FULLTEXT index_name (column_list)

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 một bảng đang tồn tại.

ALTER TABLE tblUsers ADD INDEX (c);

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 tblUsers DROP INDEX (c);

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 users MODIFY user_id INT NOT NULL;

ALTER TABLE users ADD PRIMARY KEY (user_id);

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

ALTER TABLE table_name 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 table_name

Hãy xem xét ví dụ sau để tìm hiểu và so sánh về tốc độ khi đánh index cho cách trường:

Thông tin về bảng Users:

CREATE TABLE `users` ( `user_id` int(10) unsigned NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(60) COLLATE utf8_unicode_ci NOT NULL, `firstname` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `lastname` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `birthday` date NOT NULL, `gender` tinyint(1) NOT NULL, `goal` int(11) unsigned, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Và để tìm thông tin điểm của Hoàng Văn Huy (mã số 123123), bạn sẽ query như sau:

SELECT goal FROM users WHERE user_id = '123123';

MySQL biết rằng phải tìm ở table users nhưng nó sẽ không biết bắt đầu từ đâu. Thậm chí nó cũng không biết trước rằng có bao nhiêu kết quả . Do đó nó sẽ duyệt qua tất cả danh sách (ví dụ hơn 200.000 người) để tìm thông tin về Hoàng Văn Huy.

Index là 1 file riêng biệt được lưu trữ ở máy chủ và chỉ chứa những Fields mà bạn muốn nó chứa. Nếu bạn tạo 1 Index cho Field user_id (mã số người dùng), MySQL sẽ dễ dàng tìm ra được mã số 1 cách nhanh chóng. Ví dụ như quyển sách, khi cần tìm 1 thông tin, ta thường lật ngay tới phần “Mục Lục” và tìm từ đó để tăng tốc độ tìm. Và việc tạo ra Index này sẽ làm bạn thấy Database của bạn chạy nhanh 1 cách khác thường.

Nhưng trước khi sửa lại cấu trúc của table ở trên, tôi sẽ hướng dẫn bạn 1 chút về cách theo dõi kết quả “Tăng tốc MySQL” mà bạn đang làm. Hãy sử dụng lệnh EXPLAIN

Cú pháp:

EXPLAIN Query;

Bằng lệnh này bạn sẽ nhận ra được với câu Query của bạn, điều gì đang xảy ra và kiểu kết hợp (Join) nào đang diễn ra bên trong.

EXPLAIN SELECT user_id, email, firstname, goal FROM users WHERE user_id= '123123';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 17123| Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

• table : Table nào đang liên quan đến output data
• type : Đây là thông tin quan trọng, nó cho chúng ta biết kiểu query nào nó đang sử dụng. Mức độ từ tốt nhất đến chậm nhất như sau: system, const, eq_ref, ref, range, index, all

ALTER TABLE users ADD INDEX idx_userid(user_id);

EXPLAIN SELECT user_id, email, firstname, goal FROM users WHERE user_id= '123123';

+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | users | ref | idx_userid | idx_userid | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+

• possible_keys : Đưa ra những Index có thể sử dụng để query

• key : và Index nào đang được sử dụng

• key_len : Chiều dài của từng mục trong Index

• ref : Cột nào đang sử dụng

• rows : Số hàng (rows) mà MySQL dự đoán phải tìm

• extra : Thông tin phụ, thật tệ nếu tại cột này là “using temporary” hay “using filesort”

@@, nhìn lại câu query của chúng ta mới thật khủng khiếp. Không có Possible_keys nào được sử dụng, MySQL phải duyệt qua 17123 bản ghi mới tìm ra cái ta cần (Hãy tưởng tượng 1 Forum sẽ có đến hơn 500.000 bản ghi).

Bây giờ chúng ta sẽ thêm Index vào và query lại

ALTER TABLE users ADD INDEX idx_userid(user_id);

EXPLAIN SELECT user_id, email, firstname, goal FROM users WHERE user_id= '123123';

+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | users | const| idx_userid | idx_userid | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+

Tốt hơn nhiều rồi, kiểu TYPE = Const có nghĩa rằng MYSQL hiểu ra chỉ có 1 hàng đúng với ý ta, và thể hiện qua cột Rows = 1, kiểu key= PRIMARY được sử dụng và chiều dài key_len là 10.Chỉ tìm 1 hàng tất nhiên rằng tốt hơn nhiều so với tìm 17123 hàng

Vậy câu hỏi đặt ra là, nếu tôi muốn thêm Index cho những cột mà có thể có nhiều hơn 1 kết quả khi query thì sao?

Bạn có thể Add index theo cặp như bạn add index cho cặp firstname và lastname.

ALTER TABLE users ADD INDEX idx_full_name(firstname, lastname);

+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | users | ref | idx_userid | idx_userid | 4 | const | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+

Tuy nhiên, nếu chỉ cần firstname

EXPLAIN SELECT goal FROM users WHERE firstname=’Name’;

+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | users | ref | idx_userid | idx_userid | 17123 |where used| 1 | |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+

thì MySQL sẽ tìm hết vì không hề có Index cho firstname mà chỉ có Index cho (firstname, lastname).

Khi nào thì cần Add Index ? Bất cứ khi nào bạn thay đổi Table bạn đều cần Add Index lại, giống như khi bạn thay đổi nội dung quyển sách, bạn cần phải làm lại mục lục. Vậy hãy cân nhắc, nếu Database của bạn sử dụng INSERT hay UPDATE nhiều hơn là SELECT thì Index chỉ làm chậm thêm mà thôi.

Có thể nhanh hơn nữa không? Câu trả lời là có! Bạn không cần phải làm Index cho cả field mà chỉ cần 1 phần. Giống như chi tiết mục lục của sách mà quá dài cũng làm bạn khá vất vả, do đó họ chỉ trích dẫn 1 tựa đề. Quay lại với table của chúng ta, lastname và firstname chỉ maximum là 50 chars, nếu chúng ta index nó, chúng ta tạo ra mỗi record đến 100 chars . Có thể tiết kiệm bằng cách sau

ALTER TABLE users ADD INDEX(lastname(25),firstname(25));

Bây giờ thì bạn tiết kiệm được đến 50% mà vẫn đảm bảo được tốc độ rồi đó (trừ phi bạn làm Index quá ngắn). Có thể bạn nói đĩa cứng server tôi “vô tư” nhưng hãy nhớ rằng “Nhỏ hơn là nhanh hơn”.