Query Optimization in MySQL

Tổng quan về tối ưu hóa

Hiệu suất của cơ sở dữ liệu phụ thuộc vào nhiều yếu tố ở cấp cơ sở dữ liệu: bảng, truy vấn, và các thiết lập cấu hình. Các cấu trúc phần mềm dẫn đến các hoạt động CPU và I/O ở cấp phần cứng, mà bạn phải giảm thiểu và thực hiện chúng sao cho càng hiệu quả càng tốt. Ban đầu cần học các luật cao cấp và hướng dẫn ở phía phần mềm, và đo lường hiệu suất bằng cách sử dụng đồng hồ để đo thời gian thực hiện. Sau đó, để làm được tốt hơn công việc tối ưu cần tìm hiểu thêm về những gì xảy ra trong nội bộ, và bắt đầu đo những thứ như chu kỳ CPU và hoạt động I/O. Người sử dụng phổ thông muốn có được hiệu suất cơ sở dữ liệu tốt nhất dựa trên các cấu hình phần mềm và phần cứng hiện có của họ. Người dùng cao cấp tìm kiếm các cơ hội để cải thiện phần mềm MySQL của họ, hoặc phát triển các engine lưu trữ và các thiết bị phần cứng của mình.

• Tối ưu hóa ở cấp cơ sở dữ liệu: Yếu tố quan trọng nhất trong việc đưa ra một ứng dụng cơ sở dữ liệu có tốc độ nhanh là thiết kế cơ bản của nó.
• Tối ưu hóa ở cấp phần cứng: Bất kỳ ứng dụng cơ sở dữ liệu nào cuối cùng cũng chạm tới giới hạn phần cứng khi các cơ sở dữ liệu ngày càng trở nên cồng kềnh hơn. Một quản trị viên cơ sở dữ liệu phải đánh giá được rằng liệu có thể điều chỉnh các ứng dụng hoặc phải cấu hình lại máy chủ để tránh những tắc nghẽn, hay là bắt buộc phải bổ sung thêm tài nguyên phần cứng hơn.

• Các bảng có được cấu trúc đúng cách? Các cột có kiểu dữ liệu đúng hay không? Mỗi bảng có các cột thích hợp cho các loại công việc? Ví dụ, ứng dụng thực hiện cập nhật thường xuyên thường có nhiều bảng với vài cột, trong khi các ứng dụng phân tích một lượng lớn dữ liệu thường có vài bảng với nhiều cột.
• Bạn đang sử dụng các engine lưu trữ thích hợp cho mỗi bảng, và lợi dụng những điểm mạnh và tính năng của từng engine lưu trữ mà bạn sử dụng không? Việc lựa chọn một engine lưu trữ giao dịch như InnoDB hoặc không giao dịch như MyISAM có thể rất quan trọng đối với hiệu suất và khả năng mở rộng. Lưu ý: Trong MySQL 5.5 và cao hơn, InnoDB là engine lưu trữ mặc định cho các bảng mới. Trong thực tế, các tính năng hiệu suất cao của InnoDB có nghĩa là bảng InnoDB thường tốt hơn các bảng MyISAM đơn giản, nhất là đối với một cơ sở dữ liệu bận rộn.
• Mỗi bảng đã sử dụng một định dạng hàng thích hợp? Lựa chọn này cũng phụ thuộc vào các engine lưu trữ sử dụng cho bảng. Bảng nén sử dụng không gian đĩa ít hơn -> cần ít hoạt động I/O để đọc và ghi dữ liệu. Nén là có sẵn trong engine InnoDB, và chỉ có ở bảng chỉ đọc trong engine MyISAM.
• Liệu các ứng dụng sử dụng một chiến lược khóa thích hợp? Ví dụ, bằng cách cho phép truy cập được chia sẻ khi có thể để các hoạt động cơ sở dữ liệu có thể chạy đồng thời.
• Có phải tất cả các vùng nhớ được sử dụng cho bộ nhớ đệm có kích thước hợp lý? Đủ lớn để chứa dữ liệu thường xuyên truy cập, nhưng không quá lớn để tránh quá tải bộ nhớ vật lý và là nguyên nhân phân trang.

Hệ thống tắc nghẽn thường phát sinh từ các nguồn sau:

• Disk seeks: phải mất một thời gian để ổ đĩa để tìm thấy một mảnh của dữ liệu. Với ổ đĩa hiện đại, thời gian trung bình cho việc này thường thấp hơn 10ms, vì vậy trong lý thuyết có thể thực hiện khoảng 100 tìm kiếm trong một giây. Cách để tối ưu hóa thời gian tìm kiếm là phân phối dữ liệu lên nhiều đĩa.
• Đọc và ghi: với đĩa hiện đại, một đĩa cung cấp thông lượng ít nhất là 10-20MB/s. Do đó dễ dàng hơn để tối ưu hóa tìm kiếm bởi vì bạn có thể đọc song song từ nhiều đĩa một lúc.
• Chu kỳ CPU: khi dữ liệu trong bộ nhớ chính, chúng ta phải xử lý nó để có được kết quả cần thiết. Có các bảng nhỏ so với số lượng bộ nhớ là yếu tố hạn chế phổ biến nhất. Nhưng với các bảng nhỏ, tốc độ thường không phải là vấn đề.
• Băng thông bộ nhớ: khi CPU cần nhiều hơn dữ liệu có thể phù hợp trong bộ nhớ cache của CPU, băng thông của bộ nhớ chính trở thành một nút cổ chai. Đây là một nút cổ chai không phổ biến đối với hầu hết các hệ thống, nhưng phải nhận thức được vấn đề này.

Sau đây chúng ta sẽ đi vào 2 vấn đề chính là Tối ưu hóa SQL Statement và Tối ưu hóa cấu trúc cơ sở dữ liệu.

Các xem xét chính để tối ưu hóa các truy vấn là:

1. Thiết lập indexes trên cột được sử dụng trong mệnh đề WHERE. Để tránh lãng phí không gian đĩa, xây dựng một tập hợp indexes nhỏ mà tăng tốc độ nhiều truy vấn liên quan được sử dụng trong ứng dụng của bạn.
2. Giảm thiểu số bảng được quét trong truy vấn của bạn, đặc biệt là các bảng lớn.
3. Giữ cập nhật bảng thống kê, từ đó bộ tối ưu hóa có các thông tin cần thiết để xây dựng một kế hoạch thực hiện hiệu quả.
4. Tìm hiểu các kỹ thuật điều chỉnh, kỹ thuật lập chỉ mục, và các thông số cấu hình cụ thể cho các engine lưu trữ cho mỗi bảng. Cả InnoDB và MyISAM có các bộ hướng dẫn cho phép đạt hiệu suất cao trong các truy vấn.
5. Cô lập và điều chỉnh bất kỳ phần nào của các truy vấn, chẳng hạn như một lời gọi hàm, nếu nó tốn quá nhiều thời gian.
6. Tránh chuyển các truy vấn sang cách làm cho nó khó hiểu.
7. Nếu một vấn đề hiệu suất không dễ dàng giải quyết bằng một trong những hướng dẫn cơ bản, điều tra các chi tiết bên trong của các truy vấn cụ thể bằng cách đọc các kế hoạch Giải thích và điều chỉnh indexes của bạn, các mệnh đề WHERE, các mệnh đề JOIN.
8. Điều chỉnh kích thước và tính chất của các vùng nhớ mà MySQL sử dụng cho bộ nhớ đệm. Với việc sử dụng có hiệu quả các bộ nhớ đệm, các truy vấn lặp đi lặp lại chạy nhanh hơn bởi vì các kết quả được lấy từ bộ nhớ ở lần thứ hai trở đi.
9. Ngay cả đối với một truy vấn mà chạy nhanh sử dụng các khu vực bộ nhớ cache, bạn vẫn có thể tối ưu hóa hơn nữa để chúng yêu cầu bộ nhớ cache ít hơn, làm cho ứng dụng của bạn có thể mở rộng hơn. Khả năng mở rộng có nghĩa là ứng dụng của bạn có thể xử lý nhiều người dùng đồng thời, với yêu cầu lớn hơn.

MySQL Tối ưu hoá mệnh đề WHERE như thế nào?

Một số ví dụ về 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 đây chỉ sử dụng các mục từ một chỉ mục phụ, nếu các cột được đánh chỉ mục 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 đây sử dụng các dữ liệu chỉ mục để lấy ra bản ghi trong thứ tự đã 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, ...;

Bạn có thể bị lôi cuốn để viết lại các truy vấn của bạn để làm các phép tính nhanh hơn, trong khi hy sinh khả năng đọc. Vì MySQL không tự động tối ưu hóa tương tự, bạn có thể tránh việc này, và viết các truy vấn ở một hình thức dễ hiểu và dễ bảo trì hơn.

Một số tối ưu hóa được thực hiện bởi MySQL 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)
Thay thế hằng số:
(a < b AND b = c) AND a = 5 -> b > 5 AND b = c AND a = 5
Loại bỏ điều kiện hằng số:
(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ố được sử dụng bởi các chỉ mục được đánh giá chỉ một lần. COUNT(*) trên một bảng duy nhất mà không có mệnh đề WHERE được lấy trực tiếp từ các bảng thông tin đối với MyISAM và các bảng ở bộ nhớ trong. Điều này cũng được thực hiện cho bất kỳ biểu thức NOT NULL khi được sử dụng với chỉ một bảng. Phát hiện sớm các biểu thức hằng số không hợp lệ. MySQL nhanh chóng phát hiện một số câu lệnh SELECT là không thể thực hiện và không trả về bản ghi nào.

HAVING được sáp nhập với WHERE nếu bạn không sử dụng GROUP BY hoặc chức năng tổng hợp. Đối với mỗi bảng trong mệnh đề JOIN, một mệnh đề WHERE đơn giản được xây dựng để có được một đánh giá nhanh mệnh đề WHERE cho bảng và cũng để bỏ qua các bản ghi càng sớm càng tốt. Tất cả các bảng không đổi được đọc đầu tiên trước bất kỳ các bảng khác trong truy vấn. Một bảng không đổi là bảng có bất kỳ những tính chất sau đây:

• Một bảng trống hoặc một bảng với một hàng.
• Một bảng được sử dụng với một mệnh đề WHERE ở một PRIMARY KEY hoặc chỉ mục UNIQUE, nơi mà tất cả các phần chỉ mục được so sánh với biểu thức hằng số và được định nghĩa là NOT NULL.

Sự kết hợp join tốt nhất cho việc nối các bảng tìm được bằng cách thử tất cả các khả năng. Nếu tất cả các cột trong mệnh đề ORDER BY và GROUP BY đến từ cùng một bảng, bảng đó được lựa chọn đầu tiên khi join. Nếu có một mệnh đề ORDER BY và GROUP BY khác, hoặc nếu ORDER BY hoặc GROUP BY chứa các cột từ bảng khác với bảng đầu tiên trong hàng đợi join, một bảng tạm thời được tạo ra. Nếu bạn sử dụng tùy chọn SQL_SMALL_RESULT, MySQL sử dụng một bảng tạm thời trong bộ nhớ.

Mỗi bảng đánh chỉ mục được truy vấn, và chỉ mục tốt nhất được sử dụng trừ khi bộ tối ưu tin rằng nó là hiệu quả hơn để sử dụng một bảng quét. Bộ tối ưu lúc này là phức tạp hơn và ước tính dựa trên các yếu tố khác như kích thước bảng, số bản ghi, và kích thước blog I/O. MySQL đôi khi có thể tạo ra kết quả truy vấn sử dụng dữ liệu từ chỉ mục, mà không tham khảo dữ liệu của bảng. Nếu tất cả các cột được sử dụng từ các chỉ mục là kiểu số, chỉ có dữ liệu chỉ mục được sử dụng để giải quyết các truy vấn. Trước khi mỗi hàng được đưa ra kết quả truy vấn, những bản ghi không phù hợp với các mệnh đề HAVING được bỏ qua.

Tối ưu hóa truy vấn LIMIT

MySQL đôi khi tối ưu hóa một truy vấn mà có một mệnh đề LIMIT và không có mệnh đề HAVING. Nếu bạn chỉ lấy ra một vài bản ghi với LIMIT, MySQL sử dụng các chỉ mục trong một số trường hợp trong khi bình thường nó ưu tiên quét toàn bộ bảng. Nếu bạn sử dụng LIMIT ROW_COUNT với ORDER BY, MySQL kết thúc việc sắp xếp ngay sau khi nó đã tìm thấy ROW_COUNT hàng đầu tiên của kết quả đã được sắp xếp, chứ không phải là sắp xếp toàn bộ kết quả. Nếu việc sắp xếp được thực hiện bằng cách sử dụng một chỉ mục, nó sẽ được thực hiện rất nhanh. Khi kết hợp LIMIT ROW_COUNT với DISTINCT, MySQL dừng lại ngay khi nó tìm thấy ROW_COUNT bản ghi unique. Trong một số trường hợp, GROUP BY có thể được giải quyết bằng cách đọc khóa sắp xếp (hoặc thực hiện sắp xếp trên các khóa) và sau đó tính toán tóm tắt cho đến khi giá trị khóa thay đổi. Trong trường hợp này, LIMIT ROW_COUNT không tính toán bất kỳ giá trị GROUP BY không cần thiết. Ngay sau khi MySQL đã gửi số lượng bản ghi yêu cầu cho client, nó hủy bỏ các truy vấn, trừ khi bạn đang sử dụng SQL_CALC_FOUND_ROWS.

Tối ưu hóa Kích thước dữ liệu

Bạn có thể có được hiệu suất tốt hơn cho một bảng và giảm thiểu không gian lưu trữ bằng cách sử dụng các kỹ thuật được liệt kê ở đây:

• Các cột: sử dụng hiệu quả nhất (nhỏ nhất) các loại dữ liệu có thể (MEDIUMINT thường là lựa chọn tốt hơn so với INT vì nó sử dụng ít hơn 25% không gian). Khai báo cột là NOT NULL nếu có thể. Bạn cũng tiết kiệm được một chút không gian lưu trữ, một bit cho mỗi cột. Nếu bạn thực sự cần các giá trị NULL trong bảng của bạn thì hãy sử dụng chúng. Chỉ cần tránh các thiết lập mặc định cho phép giá trị NULL trong mỗi cột.

• Format Row: bảng InnoDB sử dụng một định dạng lưu trữ nhỏ gọn. Trong các phiên bản của MySQL nhỏ hơn 5.0.3, các hàng của InnoDB có chứa một số thông tin không cần thiết, chẳng hạn như số lượng các cột và độ dài của mỗi cột, ngay cả đối với các cột kích thước cố định. Theo mặc định, các bảng được tạo ra trong định dạng nhỏ gọn. Sự hiện diện của các định dạng hàng nhỏ gọn giảm không gian lưu trữ hàng khoảng 20% và chi phí sử dụng CPU gia tăng cho một số hoạt động. Để giảm thiểu không gian hơn nữa bằng cách lưu trữ dữ liệu bảng ở dạng nén, chỉ rõ ROW_FORMAT=COMPRESSED khi tạo bảng InnoDB (bảng nén InnoDB có thể được đọc và ghi, trong khi bảng nén MyISAM là chỉ đọc).

• Chỉ mục: primary index của một bảng nên càng ngắn càng tốt. Đối với các bảng InnoDB, các cột khóa chính được lặp lại ở mỗi chỉ mục thứ cấp, do đó, một khóa chính ngắn giúp tiết kiệm không gian đáng kể nếu bạn có nhiều chỉ mục trung. Chỉ tạo các chỉ mục mà bạn cần để cải thiện hiệu suất truy vấn. Chỉ mục là tốt đối với hoạt động lấy kết quả về, nhưng làm chậm hoạt động chèn và cập nhật. Nếu bạn truy cập vào một bảng chủ yếu bằng cách tìm kiếm trên sự kết hợp của các cột, tạo ra một chỉ mục tổng hợp duy nhất trên chúng tốt hơn là một chỉ mục riêng biệt cho mỗi cột. Nếu nó rất có khả năng rằng một cột long string có một tiền tố unique trên số ký tự đầu tiên, nó là tốt hơn đánh chỉ mục chỉ tiền tố này.

• Joins: trong một số trường hợp, nó có thể có lợi để phân chia thành hai bảng được quét rất thường xuyên. Khai báo cột với các thông tin giống hệt nhau trong các bảng khác nhau với các kiểu dữ liệu giống hệt nhau, để tăng tốc độ joins dựa trên các cột tương ứng. Giữ tên cột đơn giản, do đó bạn có thể sử dụng cùng tên trên các bảng khác nhau và đơn giản hóa truy vấn join.

Tối ưu hóa cho dữ liệu số

Đối với các ID unique hoặc giá trị khác có thể được biểu diễn như là một trong hai kiểu chuỗi hoặc số, các cột số được ưa thích hơn cột chuỗi. Kể từ khi giá trị số lớn có thể được lưu trữ trong số byte ít hơn các chuỗi tương ứng, nó là nhanh hơn và mất ít bộ nhớ để truyền và so sánh chúng. Nếu bạn đang sử dụng dữ liệu số, nó là nhanh hơn trong nhiều trường hợp để truy cập thông tin từ một cơ sở dữ liệu (sử dụng kết nối trực tiếp) hơn để truy cập vào một tập tin văn bản.

Tối ưu hóa cho loại ký tự và string

Khi so sánh các giá trị từ các cột khác nhau, khai báo những cột với các tập ký tự tương tự và đối chiếu bất cứ nơi nào có thể, để tránh chuyển đổi chuỗi trong khi chạy các truy vấn. Đối với cột giá trị kích thước thấp hơn 8KB, sử dụng hệ nhị phân VARCHAR thay vì BLOB. Mệnh đề GROUP BY và ORDER BY có thể tạo ra các bảng tạm thời, và các bảng tạm thời có thể sử dụng các engine lưu trữ bộ nhớ nếu bảng gốc không chứa bất kỳ cột BLOB.

Tối ưu hóa cho các loại BLOB

Khi lưu trữ một blob có chứa dữ liệu văn bản lớn, việc đầu tiên là nén nó. Đối với một bảng có nhiều cột, để giảm yêu cầu bộ nhớ cho các truy vấn mà không sử dụng các cột BLOB, xem xét việc chia tách các cột BLOB vào một bảng riêng biệt và tham chiếu nó với một truy vấn join khi cần thiết. Khi các yêu cầu về hiệu suất để lấy và hiển thị một giá trị BLOB có thể rất khác với các loại dữ liệu khác, bạn có thể đặt bảng BLOB riêng biệt trên một thiết bị lưu trữ khác hoặc thậm chí một cơ sở dữ liệu riêng biệt.

Tối ưu hóa cho nhiều table

Có một vài nhược điểm của việc tạo ra nhiều table trong cơ sở dữ liệu. Nếu bạn có nhiều bảng MyISAM trong thư mục cơ sở dữ liệu tương tự, hoạt động open, close, và create thực hiện một cách chậm chạm. Nếu bạn thực thi câu lệnh SELECT trên nhiều bảng khác nhau, có một ít chi phí khi bảng cache đã đầy, bởi vì khi đó với mỗi bảng đã được mở ra, bảng khác phải được đóng lại. Bạn có thể giảm chi phí bằng cách tăng số lượng các mục cho phép trong bảng cache.

MySQL sử dụng bảng tạm thời nội bộ như thế nào? Những bảng có thể được giữ trong bộ nhớ và xử lý bởi các engine lưu trữ bộ nhớ trong, hoặc được lưu trữ trên đĩa và xử lý bởi các engine lưu trữ MyISAM. Các máy chủ có thể tạo ra một bảng tạm thời ban đầu là một bảng trong bộ nhớ, sau đó chuyển đổi nó vào một bảng trên đĩa nếu nó trở nên quá lớn. Khi máy chủ tạo ra một bảng tạm thời nội bộ (hoặc trong bộ nhớ hoặc trên đĩa), nó tăng biến trạng thái Created_tmp_tables. Nếu các máy chủ tạo ra các bảng trên đĩa (hoặc ngay từ ban đầu hoặc bằng cách chuyển đổi một bảng trong bộ nhớ) nó sẽ tăng giá trị biến trạng thái Created_tmp_disk_tables.

Đến đây tôi đã giới thiệu các bạn vấn đề tối ưu hóa câu lệnh SQL và tối ưu cấu trúc database. Phần tiếp theo tôi sẽ giới thiệu về kế hoạch thực thi truy vấn, đo lường hiệu suất và chi tiết các tối ưu hóa MySQL.

Tham khảo: Chap 12+13+14 of Ramakrishnan http://dev.mysql.com/doc/refman/5.5/en/optimization.html