Nguyên tắc cơ bản

• Select trường tối thiểu cần dùng
• Index
• Khoá chính (Tự động được index )
• Khoá ngoài
• Điều kiện where, order by , group by ..
• Chọn kiểu dữ liệu phù hợp: “shorter is always be.er”
• Kiểu int nhanh hơn string
• dùng Imestamp nhanh hơn dùng dateIme *Tránh null value (sử dụng default value thay vì null)
• Hạn chế query mà điều kiện group by hoặc order by nhiều cột trên các bảng khác nhau. Thay đổi thiết kế nếu cần, chấp nhận dư thừa dữ liệu
• Sử dụng join thay vì dùng subquery (trong một số trường hợp join có kết hợp dis_nct , group by thì chưa chắc nhanh hơn subquery)
• Sư dụng union tận dụng index trên các cột riêng biệt
• Sử dụng explain

Ví dụ

• films: (id, name) – primary key id • actors :(id, first_name, last_name) – primary key id, index first_name, last_name • film_actors: (id, film_id, actor_id) – primary key: id, index film_id, actor_id (không đánh khoá ngoài mà đánh index) • Lấy danh sách diễn viên theo thứ tự tham gia nhiều bộ phim ? Cách làm thông thường nhưng thường câu truy vấn chạy rất chậm do có két hợp order by number_film

Thiết kế thêm cột number_film ngay trong bảng actors (chập nhận dư thừa dữ liệu) đánh index cột number_film

Tìm danh sách bộ phim mà diễn viên có id là 1 tham gia?

Trong một số trường hợp sql opLmize sẽ hiểu như bên dưới, tuỳ vào tương quan số lượng bản ghi trong các bảng.

Sự dung join như bên dưới tốc độ vẫn nhanh nhất lý do mysql opLmize cho join

Sử dụng union

thay vì sử dụng or

Đôi khi không phải là 100% sư dụng union sẽ tốt hơn

Sử dụng explain

EXPLAIN Query

ID

Là số thứ tự cho mỗi câu SELECT trong truy vấn của bạn (trường hợp bạn sử dụng các truy vấn lồng nhau - nested subqueries).

select_type

• SIMPLE-Là một câu truy vấn đơn không có subqueries hay unions nào.Truy vấn là một câu SELECT cơ bản, không có bất cứ truy vấn con (subqueries) hay câu lệnh hợp (UNION) nào. • PRIMARY-Query là câu SELECT ngoài cùng của một phép JOIN • DERIVED-Query nằm bên trong một FROM • SUBQUERY-Query đầu tiên nằm trong subquery, không phụ thuộc vào query nào khác.Query này sẽ được execute đúng lần đầu tiên, sau đó kết quả sẽ được cache lại. • DEPENDENT SUBQUERY-Querymàphụthuộcvàoquerynằmngoàinó • UNCACHEABLE SUBQUERY-Querykhôngthểcachelạiđược • UNION-Query là câu SELECT thứ hai của lệnh UNION • DEPENDENT UNION-Khi mà trong subquery có union,và subquery đó thuộc loại DEPENDENT SUBQUERY • UNCACHEABLE UNION-Khi mà trong uncacheable subquery có chứa union-UNIONRESULT- Query là kết quả của lệnh UNION.

table

Nó chỉ là tên bảng liên quan đến câu truy vấn.

type

• system - Bảng có 0 hoặc 1 dòng • const - Bảng chỉ có duy nhất 1 dòng đã được đánh chỉ mục mà khớp với điều kiện tìm kiếm. Đây là loại join nhanh nhất, bởi bảng chỉ cần đọc một lần duy nhất và giá trị của cột được xem như là hằng số khi join với các bảng khác. • eq_ref - Giống như const nhưng trường được sử dụng không đứng riêng mà nằm trong câu lệnh JOIN. Đây là loại join tốt thứ hai chỉ sau const. • ref - Khi trường được tìm kiếm có được đánh index , tuy nhiên trường đó không phải là UNIQUE. Kiểu join này thường xảy ra với các cột được so sánh với toán tử = hoặc <=> • fulltext - Phép join các bảng sử dụng FULLTEXT index • ref_or_null - Gần giống như ref nhưng chứa cả các dòng với cột mang giá trị null • index_merge - Phép join sử dụng một danh sách các index để đưa ra tập kết quả. Ở cột KEY sẽ liệt kê các key được sử dụng • unique_subquery - Truy vấn con với lệnh IN sẽ trả về duy nhất một kết quả và sử dụng primary key • index_subquery - Gần giống như unique_subquery nhưng trả về nhiều hơn một dòng • range - Một index được sử dụng để Dm các hàng phù hợp trong một khoảng xác định khi khóa được so sánh với hằng số thông qua các toán tử BETWEEN, IN, >, >=,… • index - Toàn bộ cây index được duyệt để tìm ra row thỏa mãn điều kiện, do đó sẽ rất chậm • all - Toàn bộ bảng được quét để tìm ra các hàng phù hợp cho join. Kiểu join này được coi là tệ nhất và thường cho thấy việc thiếu các index trên các bảng

possible_keys

List tất cả các key bởi MySql để tìm ra các dòng trong bảng. Các key này có thể có hoặc không được sử dụng trong thực tế

key

Key được chính thức MySql sử dụng để làm index để tìm kiếm. Cột này có thể chứa khóa không được liệt kê ở cột possible_keys

key_len

Hiển thị độ dài của index trình tối ưu hóa truy ván chọn để sử dụng. Ví dụ, key_len = 2 tức là cần bộ nhớ để lưu 2 ký tự

ref

Hiển thị các cột hoặc các hằng số được so sánh với index được nêu ra ở cột key. Trong trường hợp query là JOIN thì đây chính là giá trị của key ở bảng tương ứng mà được join cùng với bảng chính

rows

Nó thể hiện số dòng mà mysql “dự định” sẽ fetch ra từ bảng trong query đó. Đây là một chỉ số rất quan trọng, nhất là khi bạn dùng JOIN hoặc truy vấn con

Có một điểm chú ý là khi query thuộc type là “DERIVED” tức là nó sẽ là một subquery nằm trong FROM statement, thì khi đó nếu không execute query thì mysql sẽ không thể nào “đoán” được số dòng cần lấy ra. Do vậy khi đó EXPLAIN sẽ khá là tốn thời gian nếu subquery đó nặng

extra

Đây cũng là một thông số rất quan trọng. Các giá trị kiểu như Using Temporary, Using filesort,… của cột này có thể cho thấy một truy vấn không thực sự tốt.

Chỉ cần nhìn qua extra thì bạn sẽ đoán được phần nào chuyện gì sẽ xảy ra đằng sau một query nào đó

Tóm lại nguyên tắc cơ bản

• Select_type, type ưu tiên giảm dần: tránh DEPENDENT SUBQUERY
• Key: phải có ko có phải xem lại
• Rows: càng ít càng tốt
• Extra: tránh
  • Using Temporary
  • Usingfilesort

#Ví dụ

Sau khi explain trường extra sẽ thấy temporary, filesort -> chạy rất chậm. do phải thực hiện hết kết quả count vào bảng tạm sau đó thực hiện sort mà không có index