logo

Thông báo

Icon
Error

Chia sẻ
Tùy chọn
Xem
Xem bài viết cuối
Offline admin  
#1 Đã gửi : 16/03/2015 lúc 06:32:33(UTC)
admin

Danh hiệu: Administration

Chức danh:

Nhóm: Administrators
Gia nhập: 23-07-2013(UTC)
Bài viết: 6,117
Man
Viet Nam
Đến từ: Vietnam

Cảm ơn: 10 lần
Được cảm ơn: 2 lần trong 2 bài viết

Sử dụng index trong MySql

download

Index trong MySql là gì ?

Khi phát triển ứng dụng website, sau khi một thời gian bạn có thể nhận thấy website của mình đang chạy chậm đi, có thể là do đường truyền nhưng còn 1 nguyên nhân khác, đó là máy chủ server tính toán quá nhiều dẫn đến kết quả đưa ra bị chậm . Đây là điều thường thấy ở những website về Diễn Đàn (Forum), Tin Tức (Portal) và Thương mại điện tử (Ecommerce).

Khi số lượng thành viên, số lượng bài viết tăng lên, đồng nghĩa với việc Database khi truy vấn (query) 1 yêu cầu phải duyệt qua tất cả các dữ liệu hiện có để tìm ra dữ liệu thích hợp. Cũng giống như 1 quyển sách. Nếu sách là mỏng, bạn dễ dàng tìm ra thông tin mình cần. Nhưng khi sách dầy lên, thời gian tìm kiếm của bạn sẽ tăng đáng kể.

Việc Database quá tải còn dẫn đến nhiều thiệt hại khác, các hàng đợi (Queuie) dài ra, file logs lớn lên chiếm hết không gian đĩa và user khi kết nối sẽ bị từ chối. Có thể dẫn đến  câu báo lỗi “Too many connections” không phải là hiếm gặp trong các website trên Internet. Những lỗi trên thông thường bắt nguồn từ khâu định nghĩa Database (define) hay không sử dụng Indexes. Khắc phục những thiếu sót trên, Database của bạn sẽ “nhẹ nhàng” và nhanh chóng đáng kể. Hãy xem xét ví dụ sau:

Screenshot from 2015-01-21 13:07:18

 

Như hình trên là một ví dụ . Bảng được khởi tao là bảng doan_vien .

Khi muốn tìm thông tin của 1 doan_vien theo mã số đoàn viên chẳng hạn (ví dụ: DOANVIEN_00935 ), ta query như  sau :

Screenshot from 2015-01-21 13:14:56

 

MySQL biết rằng phải tìm ở table doan_vien 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 (vd trong bảng này có 10000 bản ghi) để tìm thông tin về doan_vien có mã số DOANVIEN_00935 .

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 ma_doan_vien (mã số đoàn viên), MySQL sẽ dễ dàng tìm ra được mã số 1 cách nhanh chóng. Trở lại ví dụ 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, khi thường xuyên phải truy vấn ở những cơ sở dữ liệu nhiều bản ghi.

Trước khi sử dụng index, ta hãy xem câu lệnh truy vấn trên chạy như thế nào . Hãy sử dụng lệnh EXPLAIN

Thêm EXPLAIN vào đầu câu 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.

Screenshot from 2015-01-21 13:26:40

Ở đây ta có :

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

- key : vIndex 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ư vậy ta có thể thấy để tìm được doan_vien này MySql phải tìm qua 9611 gần như là duyệt toàn bộ CSDL . Nếu database của bạn có 1.000.000 bản ghi thì thật là rất mất thời gian . Giảm hiệu xuất hoạt động của hệ thống .

Bây giờ chúng ta sẽ đánh chỉ mục (index) cho trường ma_doan_vien sau đó query lại :

Screenshot from 2015-01-21 13:31:49

Như bạn thấy ở đây sau khi được đánh index thì MySql chỉ cần tìm qua 1 rows là đã có được kết quả . Tốc độ truy vấn đã được tăng lên rất nhiều so với trước đây .

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ó thể ! 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, ten và ho_ten_dem  là dạng varchar có maximum 255 chars, nếu chúng ta tạo index của ten và ho_ten_dem, thì có thể  chúng ta tạo ra mỗi record đến 500 chars . Quá dài , có thể tiết kiệm bằng cách sau

Screenshot from 2015-01-21 13:47:11

Bây giờ thì bạn tiết kiệm được rất nhiều mà vẫn đảm bảo được tốc độ rồi đó (Không nên tạo index trong trường hơp này quá ngắn vì có thể ảnh hưởng đến tốc độ tìm kiếm ).

Khi sử dụng index trong MySql thì cần chú ý những gì ?

- Những lệnh DELETE và UPDATE để lại rất nhiều những khoảng trống (gaps) vô nghĩa cho table (Đặc biệt là khi bạn dùng kiểu varchar hay text/blob). Điều đó có nghĩa rằng MySQL cũng phải đọc và phân tích những thứ vô nghĩa đó khi query. Điều này được khắc phục khi bạn chạy

Screenshot from 2015-01-21 13:53:07

- Trong một câu lệnh SQL, một điều kiện được tìm kiếm ở mệnh đề WHERE được gọi là sargable(Search Argument-Able) nếu index có thể được sử dụng khi thực hiện câu lệnh (giả sử cột tương ứng có index).

Screenshot from 2015-01-21 14:00:50

như câu lệnh trên thì điều kiện Where này có thể sử dụng index của cột ma_doan_vien có sẵn.

Vì index giúp tăng hiệu năng của câu lệnh lên rất nhiều, việc viết code để sao cho các điều kiện tìm kiếm trở thành sargable là một mục tiêu rất quan trọng. Một nguyên tắc rất cơ bản trong SQL Server mà bạn có thể áp dụng trong rất nhiều trường hợp, đó là cột cần tìm phải đứng một mình ở một phía của biểu thức tìm kiếm, nói cách khác là không có hàm số hay phép tính toán nào áp dụng trên cột đó.

Để xem xét rõ hơn hay xem câu lệnh dưới đây :

Screenshot from 2015-01-21 14:04:48

Câu lệnh 1 dẫn đến thao tác Clustered Index Scan, tức là quét cả cây clustered index, đồng nghĩa với quét bảng (vì clustered index chính là bảng). Do vậy mà chi phí của nó tăng vọt. Sở dĩ index đã không được sử dụng vì khi bạn áp dụng một phép tính toán trên cột, hệ thống phải thực hiện tính toán đó trên từng node trên cây index trước khi có thể lấy kết quả để so sánh với giá trị cần tìm. Vì thế nó phải duyệt tuần tự qua từng node thay vì tìm theo kiểu nhị phân (index seek, như với câu lệnh 2).  

Các bạn khi làm việc nên chú ý yếu tố này .

Khi nào nên đánh index trên 1 cột nào đó ?

- Khi trong cột muốn đánh index có số giá trị duy nhất nhiều thì nên sử dụng , nhưng nếu số giá trị duy nhất  trong column đó quá thấp thì bạn không nên dùng index đối với trường hợp này. Ví dụ trong cột này có quá nhiều giá trị trùng nhau . Số giá trị khác nhau quá ít thì index sẽ kém hiệu quả . 

Tài liệu tham khảo :

https://thanhnp.wordpress.com/2012/05/26/mysql-co-nen-su-index-khi-nao-thi-nen-su-dung-index/

http://aptech.vn/kien-thuc-tin-hoc/su-dung-index-trong-sql-server-mot-cach-hieu-qua-2.html

Ai đang xem chủ đề này?
OceanSpiders 2.0
Chủ đề tương tự
Mẹo hay tận dụng bia không sử dụng (Mẹo vặt gia đình)
Bởi Ellry 27-02-2021 lúc 08:05:38(UTC)
Những sản phẩm mẹ bầu hạn chế sử dụng (Mang thai - Bà bầu)
Bởi Ellry 19-01-2021 lúc 10:02:50(UTC)
Sử dụng điều hòa nóng đúng cách (Kiến thức tiêu dùng)
Bởi Ellry 19-12-2020 lúc 10:40:00(UTC)
Lưu ý sử dụng kính áp tròng khi đi du lịch (Kiến thức tiêu dùng)
Bởi Ellry 12-12-2020 lúc 11:50:00(UTC)
Hạn sử dụng của kính áp tròng sau khi mở niêm phong (Kiến thức tiêu dùng)
Bởi Ellry 07-12-2020 lúc 10:59:42(UTC)
Mẹo hay khi sử dụng phấn rôm (Các mẹo vặt hay)
Bởi Ellry 02-12-2020 lúc 10:55:57(UTC)
Mẹo hay khi sử dụng smartphone (Thế giới số)
Bởi Ellry 21-11-2020 lúc 03:37:13(UTC)
Những điều cần lưu ý khi sử dụng thẻ tín dụng (Quản lý tài chính cá nhân)
Bởi Ellry 18-11-2020 lúc 10:24:35(UTC)
Vì sao không nên đóng nắp máy giặt ngay sau khi sử dụng? (Kiến thức tiêu dùng)
Bởi Ellry 04-11-2020 lúc 11:04:36(UTC)
Sử dụng dầu tẩy trang thế nào? (Trang điểm)
Bởi Ellry 29-10-2020 lúc 10:04:05(UTC)
Cách đọc hạn sử dụng trên bao bì thực phẩm (Kiến thức tiêu dùng)
Bởi Ellry 28-10-2020 lúc 10:40:22(UTC)
Dấu hiệu nên dừng sử dụng tủ lạnh (Kiến thức tiêu dùng)
Bởi Ellry 24-10-2020 lúc 09:33:22(UTC)
Bảo quản chanh đang sử dụng (Mẹo vặt gia đình)
Bởi Ellry 13-10-2020 lúc 11:13:37(UTC)
Những loại băng vệ sinh không nên sử dụng (Sức khỏe phụ khoa)
Bởi Ellry 25-09-2020 lúc 10:56:26(UTC)
Nguyên tắc sử dụng máy giặt tiết kiệm điện nước (Kiến thức tiêu dùng)
Bởi Ellry 24-09-2020 lúc 09:37:17(UTC)
Di chuyển  
Bạn không thể tạo chủ đề mới trong diễn đàn này.
Bạn không thể trả lời chủ đề trong diễn đàn này.
Bạn không thể xóa bài của bạn trong diễn đàn này.
Bạn không thể sửa bài của bạn trong diễn đàn này.
Bạn không thể tạo bình chọn trong diễn đàn này.
Bạn không thể bỏ phiếu bình chọn trong diễn đàn này.

| Cung cấp bởi YAF.NET 2.2.4.14 | YAF.NET © 2003-2022, Yet Another Forum.NET
Thời gian xử lý trang này hết 0.744 giây.