MySql - Optimizing SELECT Statements

Truy vấn trong các mệnh đề SELECT làm nhiệm vụ thực hiện tất cả các hoạt động tìm kiếm, tra cứu trong cơ sở dữ liệu. Việc tối ưu các câu lệnh này luôn là ưu tiên hàng đầu.

Tốc độ của SELECT

Các việc cần lưu ý để tối ưu truy vấn:

• Để làm truy vấn SELECT...WHERE nhanh hơn, đầu tiên là kiểm tra xem bạn có thể thêm index không. Kiểm tra và thêm index trên column được sử dụng trong mệnh đề WHERE. Để tránh lãng phí không gian bộ nhớ, hãy xây dựng một tập hợp nhỏ các index sao cho tăng tốc được nhiều query liên quan nhất được sử dụng trong ứng dụng của bạn.
• Index đặc biệt quan trọng đối với các truy vấn có tham chiếu với các table khác nhau, có sử dụng JOINS và FOREIGN KEYS. Bạn có thể sử dụng mệnh đề EXPLAIN để xác định các index được sử dụng cho một SELECT.
• Cô lập và tối ưu từng phần của truy vấn nặng thành các function. Tùy thuộc vào cách truy vấn được cấu trúc như thế nào, function có thể được gọi một lần cho mỗi row trong tập kết qủa, hoặc thậm chí mỗi row trong table.
• Giảm thiểu việc quét toàn bộ table trong truy vấn của bạn, đặc biệt là các table lớn.
• Giữ table thống kê được cập nhật bằng cách sử dụng mệnh đề ANALYZE TABLE định kỳ, để tối ưu các thông tin cần thiết để xây dựng kế hoạch thực thi hiệu qủa.
• Tìm hiểu các kỹ thuật tối ưu, đánh index và thông số cấu hình cụ thể cho storage engine cho mỗi table. Cả InnoDB và MyISAM đều có hướng dẫn cho phép duy trì hiệu suất cao trong các truy vấn.
• Bạn có thể tối ưu các phiên transactions truy vấn đơn cho InnoDB table
• Tránh chuyển các truy vấn theo cách làm nó khó hiểu.
• Nếu vấn đề hiệu năng không thể giải quyết bằng những hướng dẫn cơ bản, bạn cần điều tra chi tiết bên trong các truy vấn bằng cách đọc EXPLAIN và điều chỉnh lại index, WHERE, JOINS...
• Ngay cả đối với một truy vấn chạy nhanh sử dụng các vùng nhớ cache, bạn vẫn có thể tối ưu hơn nữa để sử dụng ít bộ nhớ cache hơn, làm ứng dụng của bạn mở rộng hơn (tức là ứng dụng của bạn có thể sử lý đồng thời nhiều người dùng hơn, nhiều yêu cầu hơn,...).
• Xử lý các vấn đề khi tốc độ truy vấn có thể ảnh hưởng bởi các session khác truy cập vào table cùng một lúc.

Làm thế nào để tối ưu mệnh đề WHERE

Phần này đưa ra cách tối ưu có thể xử lý với mệnh đề WHERE. Các ví dụ sử dụng SELECT, nhưng cách tối ưu hóa này có thể được áp dụng tương tự cho mệnh đề DELETE và UPDATE.

Khi bạn viết các câu truy vấn để làm các phép tính nhanh hơn, thì tức là bạn đang hy sinh khả năng đọc. Bởi vì MySQL không tối ưu hóa tự động, bạn có thể tránh khỏi việc này và đưa ra các truy vấn dễ hiểu hơn. VD như sau:

• Loại bỏ các dấu ngoặc đơn không cần thiết:

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

• Sử dụng các hằng số:

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

• Loại bỏ các điều kiện:

   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

• Biều thức hằng số sử dụng index chỉ được đánh gía một lần.

• COUNT(*) trên một table duy nhất mà không có sử dụng WHERE để lấy trực tiếp từ các thông tin cho table MyISAM và MEMORY. Điều này cũng được thực hiện đối với bất kỳ biểu thức NOT NULL nào khi sử dụng với chỉ một table.

• Phát hiện sớm các biểu thức hằng số không hợp lệ. MySQL phát hiện rất nhanh những câu SELECT không thể và trả về no rows.

• HAVING được kết hợp với WHERE nếu bạn không sử dụng GROUP BY hoặc các function tổng hợp như: COUNT(), MIN(),...

• Đối với mỗi table được JOIN, WHERE đơn giản được xây dựng để có được ước lượng WHERE nhanh cho table và bỏ qua rows càng sớm càng tốt.

• Tất cả các table constant được đọc đầu tiên trong truy vấn. Table constant có các đặc điểm sau:

  • Một table trống hoặc table chỉ có một row
  • Một table sử dụng mệnh đề WHERE với PRIMARY KEY hoặc UNIQUE index, nơi tất cả các index được so sánh với những biểu thức constant và được định nghĩa là NOT NULL. Tất cả các table sau được coi là constant tables:

  SELECT * FROM t WHERE primary_key=1;
  SELECT * FROM t1,t2
    WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

• Sự kết hợp join tốt cho việc join các table được tìm bằng cách thử tất cả các khả năng. Nếu tất cả column đặt trong mệnh đề ORDER BY và GROUP BY từ cùng một table, thì đó là table phù hợp nhất để join.
• Nếu mệnh đề ORDER BY và GROUP BY khác nhay, hoặc nếu ORDER BY hoặc GROUP BYchứa tất cả các column từ bảng khác với bảng đầu tiên trong lệnhjoin`, một bảng tạm sẽ được tạo ra.
• Nếu bạn sử dụng tùy chọn SQL_SMALL_RESULT, MySQL sử dụng một table tạm trong bộ nhớ.
• Mỗi table index được truy vấn, index tốt nhất được sử dụng trừ khi việc tối ưu tin tưởng rằng nó là hiệu qủa hơn để sử dụng table để scan. Tại một thời điểm, một scan được sử dụng dựa vào việc các chỉ số tốt nhất kéo dài hơn 30% của table, nhưng không có một tỷ lệ cố định để quyết định việc lựa chọn sử dụng một index hay một scan. Tối ưu bây giờ phức tạp hơn và dựa vào ước lượng trên các yêu tố khác như kích thước table, số row, và kích thước block I/O.
• Trong một số trường hợp, MySQL có thể đọc nhiều rows từ index mà không cần qua data file. Nếu tất cả các cột được sử dụng từ index là số, chỉ các cây index được sử dụng để giải quyết truy vấn.
• Trước mỗi row được output, những row không phù hợp với mệnh đề HAVING được bỏ qua.

Ví dụ một số truy vấn rất nhanh:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

MySQL giải quyết các truy vấn sau sử dụng cây index, giả định rằng các column được index là số:

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

Các truy vấn sau sử dụng đánh index để lấy các row trong thứ tự được sắp xếp không phân tách loại sắp xếp:

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;