Ôn lại MySQL Performance Tuning và một vài chú ý

Bài này với mục đích ôn lại cơ bản về MySQL và một vài chú ý để tránh những trường hợp đáng tiếc và tận dụng sức mạnh vốn có khi dùng MySQL.

Note: bài này sẽ tìm kiếm trên MySQL phiên bản mới nhất là 8.0

MySQL là một trong 4 DBMS phổ biến nhất hiện nay bao gồm Oracle, MySQL và PostgreSQL

Trái tim của Database

Trong MySQL có nhiều loại Database Engine, nơi sẽ quy định cấu trúc, thuật toán lưu trữ dữ liệu, tìm kiếm, indexing, relationship, transaction... Các loại Database Engine phổ biến như: InnoDB, MyISAM, MEMORY, CSV, ARCHIVE

InnoDB

Với nhiều sự cái tiến lẫn nhiều ưu điểm của InnoDB, storage engine này đã được setup là default storage engine của MySQL và trong tương lai có thể thay thế hẳn luôn MyISAM.

Ưu điểm nổi bật:

Hỗ trợ transactions (commit, rollback, crash-recovery)
Row level locking thây vì Table locking như MyISAM
Hỗ trợ tính toàn vẹn dữ liệu với Foreign key
InnoDB sẽ sắp xếp lại dữ liệu để tối ưu việc queries dựa trên primary keys.
Kể từ version 5.6, InnoDB cũng đã hỗ trợ fulltext indexes.

MyISAM

MyISAM là một storage engine cũ với cấu trúc đơn giản và được đánh giá là nhanh trong trường hợp đọc nhưng lại có nhược điểm là Table Locking nên việc INSERT, UPDATE, DELETE sẽ chậm hay không hỗ trợ Foreign key, transactions

MEMORY Storage Engine

MEMORY Storage Engine hay trước đây gọi là HEAP, loại engine này tạo ra với mục đích là chuyển nội dung của table này lên memory để tận dụng tốc độ.

Tìm hiểu thêm ưu điểm của InnoDB và best practices khi dùng

Việc xác định primary key cho mỗi table nên dựa trên những cột nào thường xuyên queries nhất, bởi InnoDB nó sẽ index primary key.
Để tối ưu khi sử dụng JOIN, nên define foreign keys cho những JOIN columns.
Để tối ưu tốc độ ghi, chúng ta có thể turn off autocommit. Bởi mặc định sau mỗi câu query lệnh commit sẽ được thực hiện.

Optimizing

Database performance phụ thuộc vào nhiều yếu tố như là storage engine, queries, configuration setting, hardware,... Thông thường việc optimizing database sẽ chú trọng vào:

Optimizing Database Level
Optimizing Hardware Level
Balancing

Nhưng để optimizing tốt thì phía trước cả một rừng kiến thức sâu thẳm, ở đây mình chỉ múa ở mức cơ bản thôi nhé.

Optimizing Database Level Overview

Về cơ bản, để Optimizing Database Level chúng ta cần lượt qua 1 vòng các câu hỏi như:

Việc thiết kế cấu trúc tables có đúng chưa? cụ thể columns đã đúng kiểu dữ liệu chưa, hay cách tổ chức số lượng columns có phù hợp theo từng chức năng không?
Đã tối ưu việc indexing với queries chưa?
Đã chọn đúng storage engine chưa?
Application có sử dụng việc locking phù hợp chưa?
Memory để caching đã phù hợp chưa?

Optimizing Hardware Level Overview

Bạn đã optimizing rất nhiều thứ về software nhưng lại bị bottlenecks ở hardware và tất nhiên kết quả sẽ là vô nghĩa. Điểm qua một vài nguyên nhân cơ bản dẫn đến việc bottlenecks ở tầng hardware:

Disk seeks, Disk seeks có nghĩa là việc tìm kiếm một piece of data trên ổ đĩa. Với một ổ đĩa mới hiện nay, thời gian trung bình của một lần tìm kiếm thường nhỏ hơn 10ms, vì vậy theo lý thuyết nó sẽ tốn khoản 1 giây cho 100 seeks. Vậy để tối ưu disk seek chỉ có một cách là distribute data ra nhiều ổ đĩa.
Disk reading and writing, cũng gần giống như disk seeks, tốc độ ghi của một ổ đĩa là 1 con số giới hạn, để tối ưu việc reading và writing chỉ có một cách là distribute data ra nhiều ổ đĩa.
CPU cycles cũng là một vấn đề cần lưu tâm. Ví dụ ứng dụng có một phép tính trên dữ liệu của một table mà kích thước dữ liệu lớn hơn bộ nhớ thì CPU cycles cũng là tác nhận gây ra bottlenecks.