Đăng bởi:
Duy NguyễnĐăng ngày:
Jan 30, 2021Đăng ở:
Công cụ, tiện ích, thủ thuậtKhô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
Đẩ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)。
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
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
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)
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
ALTER TABLE table_name DROP INDEX index_name;
Khi so sánh giá trị của field với số chỉ định VD: WHERE name = "hogehoge"
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[=、>、>=、<、<=]
Khi cố định giá trị đầu tiên của chuỗi theo LIKE
MIN(), MAX()
Có thể ORDER BY, GROUP BY dựa theo prefix của chuỗi
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)
Khi áp dụng mệnh đề BETWEEN or IN
Khi bắt đầu LIKE bằng wild card
Khi Mysql được xác định rằng việc đọc toàn bộ DB sẽ nhanh hơn
Thông thường index sẽ không sử dụng ORDER BY
Khi field ORDER BY và WHERE khác nhau nhưng chỉ đánh index trên một filed
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 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
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 JOINref
.... 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ậmALL
: Đâ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ậmpossible_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ếmKey
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 đó.Sử dụng kết hợp Using filesort và Using temporary
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 đó.
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.
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
Nguồn:
http://kiyotakakubo.hatenablog.com/entry/20101117/1289952549
https://qiita.com/ysks-y/items/bba52e4a7eba82a715be
https://viblo.asia/p/mot-so-chu-y-tao-index-trong-mysql-djeZ1BYglWz
Bình luận
Để lại bình luận
Email và số điện thoại sẽ không được công khai. Những trường bắt buộc được đánh dấu *