Một số chú ý tạo index trong mysql

Không chỉ với Mysql mà với bất kì cơ sở dữ liệu nào có đánh index nó sẽ cải thiện tốc độ tìm kiếm , sắp xếp truy cập record của một bảng.Tuy nhiên, không phải lúc nào đánh index cũng tốt vì thế chúng ta cần xem xét xem khi nào thì cần đánh index Bài viết hôm nay mình sẽ chỉ ra một số chú ý về cách tạo Index cụ thể cho các bạn mới bắt đầu tìm hiểu về Index

Ưu điểm

Đẩy nhanh xử lý lọc ,tìm kiếm record theo điều kiện WHERE Không chỉ giới hạn với câu lệnh SELECT mà còn đẩy nhanh tốc độ tìm kiếm record trong các quá trinh xử lý UPDATE , DELETE( theo WHERE)。

Nhược điểm

Cost của INSERT, UPDATE ,DELETE tăng lên . Hơn nữa, có thể không ảnh hưởng đến tốc độ hoặc tốc độ bị giảm đi bởi index

• UPDATE Khi thay đổi, update column có đánh index thì index cũng được update theo dẫn đến tốc độ bị châm Lượng data update và số lượng index sẽ ảnh hưởng đến tốc độ Index mà không update data thì sẽ không bị thay đổi（do đó không ảnh hưởng đến giảm tốc độ bởi index )

• DELETE Vì phải tiến hành update toàn bộ index của table theo record delete nên dẫn đến tốc độ bị chậm Thời gian cần để delete tỉ lệ thuận với số lượng index

• INSERT Khi insert record thì đồng thời việc insert index cũng tăng lên dẫn đến tốc độ cũng bị chậm đi

• Trong trường hợp search record với số lượng ít , lượng data trong table nhiều

• Hay được sử dụng như điều kiện của mệnh đề ORDER BY , diều kiện kết quả , điều kiện của WHERE Tuy nhiên, sẽ không cần đánh index nếu tất cả các scan là table target

• Vì data có nhiều dữ liệu NULL nên sẽ search ngoài giá trị NULL Do Index không gồm giá trị NULL nên Index sẽ hiệu quả khi search ngoài giá trị NULL

• Trong trường hợp search lượng lớn record từ table hay kích thước của table bé
• Không có logic điều kiện where
• Giá trị của cột hay phải insert , update, delete Nếu insert dòng mới vào table , thì giá trị index cũng tự động đưa vào . Do đó tốc độ search data cũng nhanh hơn nhưng khi phát sinh maintain index thông qua xử lý insert , update, delete thì tốc độ sẽ giảm xuống
• Sử dung như điều kiện của mệnh đề WHERE nhưng cột được tham chiếu như một phần của biếu thức

Trong trường hợp thông thường

ALTER TABLE landing_pages ADD INDEX index_name(user_id)

Chúng ta cũng có thể đánh index một phần của giá trị field.VD bên dưới là đánh index cho 4 byte đầu tiên của last_name

ALTER TABLE phone_book ADD INDEX (last_name(4))

Theo như ví dụ trên thì dung lượng cần thiết của data đánh index sẽ giảm, hiệu quả đánh index sẽ tăng lên .Tuy nhiên, sẽ xảy ra trường hợp giá trị Index giống nhau ở nhiều record

Trong trường hợp multi cột index

Chúng ta có thể đánh index cho nhiều cột như sau

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

Trong trường hợp đánh multi như thế này có câu hỏi được đặt ra là việc tạo 2 index có vấn đề gì không? Với MySQL khi thực hiện 1 request thì chỉ sử dụng 1 index tương ứng với 1 table . Theo đó , trong trường hợp đánh Index riêng cho từng first_name và last_name thì MySQL sẽ chọn một trong hai index đó MySQL sẽ phán đoán dựa trên kinh nghiêm để xác định ra Index nào ứng với dòng ít hơn Nếu sử dụng multi column Index , Index cho nhiều column đươcj sử dụng , tốc độ query cũng tăng lên ( cũng có trường hợp không tăng)

Unique index

ALTER TABLE phone_book ADD UNIQUE(phone_number)

Khi mà đánh Unique Index , không chỉ là tìm ra record map với query , mà giá trị chỉ định của cột tương ứng chỉ xuất hiện một lần( trừ trường hợp NULL) , Nói cách khác là khi insert hoặc update record sẽ cần check tất cả các giá trị , đảm bảo không tồn tại giá trị của một cột ở 2 record giống nhau Unique Index hoạt động theo cách này như một điều kiện ràng buộc trong MySQL

Delete Index

ALTER TABLE table_name DROP INDEX index_name;

Khi nên sử dụng

1. Khi so sánh giá trị của field với số chỉ định VD: WHERE name = "hogehoge"

2. Khi JOIN toàn bộ giá trị của filed VD:WHERE a.name = b.name

3. Khi cần giới hạn phạm vi giá trị của field bằng các toán tử so sánh[=、>、>=、<、<=]

4. Khi cố định giá trị đầu tiên của chuỗi theo LIKE

5. MIN(), MAX()

6. Có thể ORDER BY, GROUP BY dựa theo prefix của chuỗi

7. Trường hợp nếu tất cả các field của của WHERE có một phần index (không tham chiếu toàn bộ DB)

8. Khi áp dụng mệnh đề BETWEEN or IN

Khi không nên sử dụng

1. Khi bắt đầu LIKE bằng wild card

2. Khi Mysql được xác định rằng việc đọc toàn bộ DB sẽ nhanh hơn

3. Thông thường index sẽ không sử dụng ORDER BY

4. Khi field ORDER BY và WHERE khác nhau nhưng chỉ đánh index trên một filed

5. Trong trường hợp data dưới 1000 record, mặc dù đánh index nhưng có thể không được sử dụng

EXPLAIN

EXPLAIN là công cụ giúp tìm hiểu và đánh giá query MYSQL chạy bên trong như thế nào nhằm tối ưu query Cách sử dụng EXPLAIN : gắn EXPLAIN trước câu lệnh SELECT Đề đánh giá và thấy rõ perpormance của query nên thực hiện EXPLAIN trên lượng data lớn có thể thực hiện trên môi trường thật hoặc staging

Check type

Biến này cho chúng ta một thông tin rất quan trọng, đó la "cách" access vào table sử dụng trong query đó. Tuỳ vào cách access chúng ta sẽ có những cách access "nhanh", và cách "access" chậm. Biến này là biến chính để giúp chúng ta tuning index cho database.

• const ... Khi mà table được access sử dụng PRIMARY KEY, hoặc sử dụng index một field nào mà các giá trị của field đó là UNIQUE. Khi mà type là const chúng ta sẽ có tốc độ access vào table "nhanh nhất" !
• eq_ref ... giống như const nhưng field được sử dụng không đứng riêng mà nằm trong câu lệnh JOIN
• ref .... Khi mà field được tìm kiếm có được đánh index , tuy nhiên field đó không phải là UNIQUE, và field đó được sử dụng trong phép so sánh Where =
• range .... Khi mà field được tìm kiếm có dán index, và được sử dụng trong phép tìm theo range Where In hoặc là Where > hay Where < ..
• index .... khi có dán index cơ mà để tìm ra kết quả thì mysql bắt buộc phải scan toàn bộ field, do đó mà sẽ rất chậm
• ALL : Đây là khi mà không những field không đánh index, mà lại còn phải scan toàn bộ field, đây chính là nơi mà bạn bắt buộc phải tuning , không thì sẽ rất chậm

Khác

1. Các item check là 「possible_keys」「key」「Extra」

• possible_keys List tất cả các key mà optimizer của mysql có thể sử dụng được index của chúng để tìm kiếm
• Key Key được chính thức optimizer sử dụng để làm index để tìm kiếm.
• Extra Đây cũng là một thông số rất quan trọng để tuning mysql query. Biến này thể hiện optimizer sẽ thực hiện như thế nào để thực thi query đó. Chỉ cần nhìn qua extra thì bạn sẽ hiểu được tổng quan nhưng gì xảy ra đằng sau một query nào đó.

1. Sử dụng kết hợp Using filesort và Using temporary

2. Using filesort Khi trong query của chúng ta có order by chẳng hạn thì thông tin lấy về sẽ cần phải sort. Using filesort nói lên điều đó.

3. Using temporary Khi mà trong query chúng ta phải sort kết quả của JOIN , hay là trong query có sử dụng distinct thì MySQL sẽ phải tạo "bảng tạm" để thực hiện việc này.

4. Trong trường hợp system tiến hành xử lý real time cần cải thiện xử lý nếu hiển thị cả hai Using filesort và Using temporary

Source

http://kiyotakakubo.hatenablog.com/entry/20101117/1289952549 https://qiita.com/ysks-y/items/bba52e4a7eba82a715be