partitioning và index trong mysql

Partitioning và index trong mysql

1.partitioning:
+ Khái niệm:
– Các phần của bảng được lưu như các bảng riêng biệt tại các vị trí khác nhau
– Cho phép phân phối các phần của bảng trên hệ thống file tuân theo các luật được thiết lập.
– Luật phân chia dữ liệu được chọn bởi người dùng được gọi là hàm phân mảnh (partitioning function)
+ Ưu điểm:
– Phân mảnh giúp lưu dữ liệu trong một bảng lớn hơn trên một đĩa hoặc phân vùng hệ thống file
– Dữ liệu không còn hữu ích có thể thường xuyên loại bỏ dễ dàng bằng cách xóa phân vùng
– Một số truy truy vấn có thể tối ưu nếu dữ liệu thỏa mãn mệnh đề WHERE được xác định lưu trữ trong một hoặc nhiều phân vùng
– Do các phân vùng có thể sửa đổi sau khi bảng được tạo, có thể tổ chức lại dữ liệu để nâng cao các truy vấn thường xuyên
+ Các kiểu phân mảnh:
– Khoảng (RANGE): gán các dòng vào các phân mảnh dựa vào giá trị của cột nằm trong một khoảng
– Danh sách (List): tương tự như khoảng, nhưng danh sách là một tập các giá trị rời rạc
– Băm (HASH): dựa trên giá trị trả lại bởi biểu thức định nghĩa bởi người dùng (tạo ra giá trị nguyên, không âm)
– Khóa (KEY): tương tự phân mảnh bởi băm, ngoại trừ hàm băm được cung cấp bởi MySQL server, đảm bảo kết quả trả lại là số nguyên bất kỳ kiểu dữ liệu của cột. Bất kỳ các cột được sử dụng như là khóa phân mảnh cần phải là một phần hoặc toàn bộ khóa chính của bảng

+ Ví dụ:
VD1:

VD2:

VD3:

2.Index:
+ Khái niệm:
Index là một tập tin riêng biệt được lưu trữ tại máy chủ, nó chỉ lập chỉ mục cho các trường dữ liệu mà bạn muốn. Giả sử khi bạn lập chỉ mục cho trường user_id thì MySQL sẽ dễ dàng tìm ra được email của userId thứ 12599 dựa trên một số thuật toán tìm kiếm nhị phân, … Trở lại về ví dụ quyển sách, khi chúng ta tìm kiếm thường tìm đến phần “mục lục” để tìm kiếm nhanh tới vị trí mong muốn thì trong MySQL cũng như vậy.

+ Ví dụ và cách dùng:
+ 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.

Giải thích:

– 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

– 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”

Wow, 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 86792 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 86792 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?

Vẫn add index như bình thường,giả sử bạn cần tìm những người có họ là Nguyễn Văn, tên là A

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

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

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, user_surname và user_firstname chỉ maximum là 40 chars, nếu chúng ta index nó, chúng ta tạo ra mỗi record đến 80 chars . Có thể tiết kiệm bằng cách sau

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”.

Điều kì diệu OPTIMIZE VÀ ANALYZE

“Ma thuật” của MySQL là biết cách chọn khoá (key) nào để query(nếu có). Quá trình này gọi là “query optimizer”, nó sẽ “liếc” qua những Index đang có để quyết định sẽ dùng Index nào để tìm. Hãy tưởng tượng bạn đang tìm 1 dĩa CD của “Maria Carrey” có tên là “I Love You”, có nghĩa là có 2 Indexes ở đây, 1 cho tên tác giả và 1 cho tên CD. Bạn nhận thấy rằng danh mục có 20.000 tên tác giả và 400.000 tên Album. Một cách đơn giản,bạn sẽ tìm theo tên tác giả. Khi có được, bạn lại thấy rằng “Maria Carrey” có 50 CDs và CD “I Love You” bắt đầu bằng chữ I. Đơn giản và dễ dàng tìm ra cái mình muốn phải không ? MySQL cũng vậy nhưng …bạn phải chỉ cho nó bằng cách:

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

Do đó 2 câu lệnh trên bạn nên chạy 1 cách thường xuyên để bảo đảm tối ưu hoá Database của mình.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">