Hướng dẫn mysql use index
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.
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
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.
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:
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.
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).
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.
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.
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.
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.
Bạn có thể sử dụng lệnh ALTER để xóa một Primary Key như sau:
Để 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:
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:
Và để tìm thông tin điểm của Hoàng Văn Huy (mã số 123123), bạn sẽ query như sau:
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:
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.
@@, 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
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
Tuy nhiên, nếu chỉ cần firstname
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à
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”. |