Kiến trúc MySQL (3 lớp)
Lớp Clients:
Xử lý kết nối
Xác thực
Bảo mật
Lớp SQL:
Phân tích cú pháp truy vấn
Phân tích và tối ưu hoá
Thực thi truy vấn
Các hàm tích hợp sẵn:
Lớp Storage Engines:
InnoDB
MyISAM
So sánh InnoDB và MyISAM trong MySQL:
1. Khả năng hỗ trợ giao dịch (Transactions)
InnoDB: Hỗ trợ transaction với các đặc tính ACID (Atomicity, Consistency, Isolation, Durability). Điều này đảm bảo tính nhất quán dữ liệu và bảo vệ dữ liệu trong trường hợp có lỗi hoặc sự cố hệ thống.
MyISAM: Không hỗ trợ transaction. Dữ liệu có thể bị hỏng nếu xảy ra lỗi trong quá trình cập nhật.
2. Khóa (Locking)
InnoDB: Hỗ trợ khóa cấp hàng (row-level locking), cho phép nhiều giao dịch cập nhật cùng lúc mà không gây xung đột, nâng cao hiệu suất trong môi trường có nhiều truy vấn đồng thời.
MyISAM: Sử dụng khóa cấp bảng (table-level locking). Khi một hàng bị khóa để cập nhật, toàn bộ bảng bị khóa, dẫn đến hiệu suất thấp hơn trong trường hợp có nhiều truy vấn đồng thời.
3. Khôi phục dữ liệu sau sự cố (Crash Recovery)
InnoDB: Có khả năng khôi phục dữ liệu sau sự cố nhờ vào tính năng ghi nhật ký (redo logs) và các cơ chế khôi phục tự động.
MyISAM: Khả năng khôi phục dữ liệu kém hơn. Sau một sự cố, bảng MyISAM có thể cần phải kiểm tra và sửa chữa thủ công.
4. Hỗ trợ khóa ngoại (Foreign Key Constraints)
InnoDB: Hỗ trợ khóa ngoại, giúp duy trì tính toàn vẹn của dữ liệu giữa các bảng liên quan.
MyISAM: Không hỗ trợ khóa ngoại.
5. Hiệu Suất Truy Vấn Đọc
InnoDB: Hiệu suất đọc tốt, nhưng không nhanh bằng MyISAM trong một số trường hợp cụ thể vì cần phải quản lý thêm các tính năng như transaction và khóa cấp hàng (row-level locking).
MyISAM: Hiệu suất đọc cao hơn cho các truy vấn chỉ đọc do không phải quản lý các tính năng liên quan đến transaction.
6. Kích thước tập tin chỉ mục (Index File Size)
InnoDB: Index có thể lớn hơn vì lưu trữ thông tin về giao dịch và khóa.
MyISAM: Index nhỏ gọn hơn, giúp tăng tốc độ truy vấn tìm kiếm.
7. Tìm kiếm toàn văn bản (Full-Text Search)
InnoDB: Bắt đầu từ MySQL 5.6, InnoDB đã hỗ trợ tìm kiếm toàn văn bản, nhưng vẫn không mạnh mẽ như MyISAM.
MyISAM: Hỗ trợ tìm kiếm toàn văn bản từ lâu và hiệu suất tìm kiếm toàn văn bản thường tốt hơn InnoDB.
8. Kích thước bảng tối đa
InnoDB: Hỗ trợ kích thước bảng lên tới 64TB.
MyISAM: Hỗ trợ kích thước bảng lên tới 256TB, phụ thuộc vào kích thước tập tin và hệ thống tệp.
9. Backup và khôi phục dữ liệu
InnoDB: Hỗ trợ sao lưu và khôi phục dữ liệu nóng (hot backup) nhờ tính năng transaction và nhật ký redo.
MyISAM: Chỉ hỗ trợ sao lưu và khôi phục dữ liệu khi hệ thống không có hoạt động ghi (cold backup).
Có nên sử dụng ràng buộc (Constraint) ở tầng cơ sở dữ liệu ?
Ràng buộc (constraints) trong cơ sở dữ liệu là các quy tắc được áp dụng trên các bảng để đảm bảo tính toàn vẹn và nhất quán của dữ liệu. Các ràng buộc này được quản lý trực tiếp trên MySQL. Một số ràng buộc phổ biến như PRIMARY KEY, FOREIGN KEY, UNIQUE, ... Trong phần này, chúng ta sẽ bàn về việc có nên sử dụng ràng buộc liên quan đến FOREIGN KEY hay không.
Đối với cá nhân mình, bạn không nhất thiết phải cài đặt ràng buộc FOREIGN KEY. Một số hạn chế của nó bên cạnh lợi ích như sau:
Giảm hiệu suất cập nhật:
Ràng buộc FOREIGN KEY có thể làm giảm hiệu suất ghi chép dữ liệu, đặc biệt khi có nhiều thao tác ghi hoặc cập nhật dữ liệu vì cơ sở dữ liệu phải thực hiện các kiểm tra bổ sung để duy trì tính toàn vẹn tham chiếu.
Phức tạp trong quản lý:
Việc thêm, sửa đổi hoặc loại bỏ ràng buộc FOREIGN KEY có thể phức tạp, đặc biệt khi cơ sở dữ liệu lớn hoặc có nhiều mối quan hệ tham chiếu phức tạp.
Giới hạn tính linh hoạt:
Ràng buộc FOREIGN KEY có thể làm giảm tính linh hoạt của cơ sở dữ liệu, đặc biệt khi có các yêu cầu thay đổi cấu trúc dữ liệu hoặc nghiệp vụ thường xuyên. Xu hướng hiện tại cũng chấp nhận việc dư thừa dữ liệu nhiều hơn, nên việc áp dụng ràng buộc khoá ngoại chặt chẽ cũng không thực sự cần thiết.
Khó khăn trong phát triển và kiểm tra:
Trong quá trình phát triển, ràng buộc FOREIGN KEY có thể làm cho việc nhập dữ liệu thử nghiệm trở nên khó khăn hơn, vì bạn phải đảm bảo rằng tất cả các mối quan hệ tham chiếu đều hợp lệ.
Với các vấn đề trên, theo ý kiến cá nhân của mình bạn không cần triển khai ràng buộc FOREIGN KEY trong thực tế (Phần này mọi người để lại ý kiến cá nhận của mình để cùng trao đổi).
Sử dụng transaction để đảm bảo tính toàn vẹn dữ liệu
Transaction là một thuật ngữ đã quá quen thuộc khi bạn làm việc với cơ sở dữ liệu. Một transaction là một nhóm các câu lệnh SQL được xử lý atomic (nguyên tử), như một đơn vị công việc duy nhất. Trong quá trình xử lý tập các câu lệnh SQL trong một transaction, nếu có bất kỳ câu lệnh nào không thể thực hiện được vì lý do nào đó (như hệ thống gặp sự cố), thì sẽ không có câu lệnh SQL nào được thực thi trong tập các câu lệnh trên. Điều này còn được mô tả bằng thuật ngữ “tất cả hoặc không gì cả” (Hoặc là tất cả các câu lệnh thành công, hoặc sẽ không có câu lệnh nào được thực thi).
Thiết kế và quản lý schema
Một số nguyên tắc chọn loại dữ liệu để tối ưu:
Nhỏ hơn thì thường là tốt hơn: Sử dụng kiểu dữ liệu nhỏ nhất có thể: Các kiểu dữ liệu nhỏ hơn thường nhanh hơn và tiết kiệm tài nguyên hơn. Ví dụ: Nếu bạn chỉ cần lưu trữ các giá trị từ 0 đến 255, hãy sử dụng TINYINT thay vì INT. Điều này không chỉ tiết kiệm không gian lưu trữ mà còn tăng hiệu suất truy vấn.
Dữ liệu càng đơn giản càng tốt:
Các kiểu dữ liệu đơn giản, ví dụ dạng INT thường tốn ít CPU hơn để tính toán và xử lý. Trong hầu hết các trường hợp, việc so sánh các số nguyên thường sẽ nhanh hơn so với so sánh các ký tự vì các bộ ký tự và các quy tắc sắp xếp (collations) làm cho việc so sánh ký tự trở nên phức tạp.
Ví dụ về việc sử dụng các kiểu dữ liệu đơn giản: Sử dụng các kiểu dữ liệu ngày và giờ có sẵn của MySQL thay vì chuỗi (string): Lưu trữ ngày và giờ dưới dạng các kiểu dữ liệu có sẵn như DATE, TIME, DATETIME, và TIMESTAMP thay vì chuỗi sẽ tiết kiệm bộ nhớ và tăng hiệu suất truy vấn.
Sử dụng số nguyên thay vì chuỗi (string) cho các giá trị danh mục: Nếu bạn có các danh mục hoặc các giá trị trạng thái, hãy sử dụng các kiểu dữ liệu số nguyên hoặc ENUM thay vì chuỗi.
Lợi ích của việc sử dụng kiểu dữ liệu đơn giản:
● Hiệu suất cao hơn: Các kiểu dữ liệu đơn giản sẽ tốn ít CPU hơn để xử lý các phép toán, giúp cải thiện hiệu suất tổng thể của hệ thống.
● Tiết kiệm không gian lưu trữ: Các kiểu dữ liệu nhỏ hơn chiếm ít không gian lưu trữ hơn, giúp giảm kích thước bảng và tăng tốc độ truy vấn.
● Tăng tính nhất quán: Sử dụng các kiểu dữ liệu chuẩn như DATE, TIME, INT, và ENUM giúp tăng tính nhất quán trong cơ sở dữ liệu, giảm thiểu lỗi và dễ dàng quản lý hơn.
Tránh NULL nếu có thể :
Trong thực tế, mình gặp rất nhiều các schema bao gồm các cột có thể NULL (Nullable) ngay cả khi những cột đó sẽ không bao giờ được để trống giá trị. Thông thường, tốt nhất là chỉ định các cột là NOT NULL trừ khi bạn thực sự có dự định lưu trữ NULL trong chúng.
Tại sao nên tránh sử dụng cột có thể NULL?
● Tối ưu hóa truy vấn khó khăn hơn: MySQL gặp khó khăn hơn trong việc tối ưu hóa các truy vấn liên quan đến các cột NULL-able vì chúng làm cho việc Index và so sánh giá trị trở nên phức tạp hơn.
● Tiêu tốn không gian lưu trữ: Cột có thể NULL sử dụng nhiều không gian lưu trữ hơn và yêu cầu xử lý đặc biệt bên trong MySQL.
● So sánh giá trị phức tạp hơn: Các so sánh giá trị với cột có thể NULL phức tạp hơn vì MySQL phải xử lý các giá trị NULL một cách đặc biệt.
Ví dụ: Trong một bảng người dùng, cột username và password nên được đặt là NOT NULL vì mỗi người dùng luôn cần có tên đăng nhập và mật khẩu.
Lưu trữ hình ảnh trong MySQL
Trước đây, không phải là hiếm khi một số ứng dụng chấp nhận lưu trữ hình ảnh dưới dạng dữ liệu BLOB trong cơ sở dữ liệu MySQL (hoặc lưu dưới dạng Base64 trong các cột dạng TEXT). Phương pháp này ban đầu khá thuận tiện; tuy nhiên, khi kích thước dữ liệu tăng lên, các hoạt động như thay đổi schema trở nên chậm hơn do kích thước của dữ liệu BLOB là rất lớn.
Nếu có thể, đừng lưu trữ dữ liệu như hình ảnh trong cơ sở dữ liệu. Thay vào đó, ghi chúng vào một service lưu trữ riêng biệt và sử dụng MySQL để theo dõi vị trí hoặc tên tệp của hình ảnh.
Sử dụng ENUM thay cho kiểu chuỗi
Đôi khi bạn có thể sử dụng cột ENUM thay vì các kiểu chuỗi thông thường. Một cột ENUM có thể lưu trữ một tập hợp các giá trị chuỗi xác định trước. MySQL lưu trữ chúng rất nhỏ gọn, được nén thành 1 hoặc 2 byte tùy thuộc vào số lượng giá trị trong danh sách. Nó lưu trữ mỗi giá trị nội bộ dưới dạng một số nguyên đại diện cho vị trí của nó trong danh sách được định nghĩa.
Giả sử bạn có một bảng lưu trữ trạng thái của đơn hàng:
ENUM('pending', 'shipped', 'delivered', 'canceled') NOT NULL )
Trong ví dụ này, status là một cột ENUM có thể chứa một trong các giá trị: 'pending', 'shipped', 'delivered', 'canceled'. MySQL sẽ lưu trữ các giá trị này rất nhỏ gọn, từ đó truy vấn sẽ có hiệu suất tốt hơn.
Lợi ích của việc sử dụng ENUM
Tiết kiệm không gian lưu trữ: Vì MySQL lưu trữ các giá trị ENUM dưới dạng số nguyên nhỏ gọn, nó tiết kiệm không gian lưu trữ so với việc lưu trữ chuỗi ký tự thông thường.
Tăng tốc độ so sánh: So sánh các số nguyên nhanh hơn so với so sánh chuỗi ký tự (do MySQL lưu trữ giá trị ENUM dưới dạng các số nguyên).
Ràng buộc tính hợp lệ: Sử dụng ENUM giúp ràng buộc các giá trị hợp lệ cho cột, tránh việc nhập sai giá trị.
Sử dụng timestamp, datetime, unixpox
DATETIME: Sử dụng khi cần lưu trữ một phạm vi rộng các giá trị ngày và giờ, không phụ thuộc vào múi giờ.
TIMESTAMP: Sử dụng khi cần tiết kiệm không gian lưu trữ và giá trị thời gian phụ thuộc vào múi giờ.
Unix Epoch: Tránh các vấn đề xử lý thời gian của MySQL bằng cách lưu trữ ngày và giờ dưới dạng Unix epoch, phần xử lý dữ liệu hiển thị sẽ được đẩy sang ứng dụng.
Lựa chọn kiểu dữ liệu tốt cho cột định danh (ID)
Trong cơ sở dữ liệu, định danh là cách bạn tham chiếu đến một hàng và thường là yếu tố làm cho nó trở nên duy nhất (unique). Ví dụ, nếu bạn có một bảng về người dùng, bạn có thể muốn gán cho mỗi người dùng một ID số hoặc một tên đăng nhập duy nhất. Trường này thông thường có thể là một phần hoặc toàn bộ của khoá chính (PRIMARY KEY).
Tầm quan trọng của kiểu dữ liệu cho cột định danh
Chọn một kiểu dữ liệu tốt cho cột định danh là rất quan trọng. Bạn sẽ thường xuyên sử dụng cột định danh này trong các phép toán như JOIN, hoặc dùng nó để tìm dữ liệu trong các bảng khác. Bạn cũng có khả năng sử dụng chúng trong các bảng khác như khóa ngoại, vì vậy khi bạn chọn kiểu dữ liệu cho một cột định danh, bạn có thể đang chọn kiểu dữ liệu cho các bảng liên quan nữa.
Các yếu tố cần xem xét khi chọn kiểu dữ liệu cho cột định danh
Kiểu lưu trữ và hiệu suất so sánh: Khi chọn kiểu dữ liệu cho cột định danh, bạn không những phải cân nhắc cách MySQL lưu trữ, mà còn là cách MySQL xử lý tính toán và so sánh trên các cột này. Ví dụ, MySQL lưu trữ các kiểu ENUM và SET dưới dạng số nguyên nhưng sẽ chuyển đổi chúng thành chuỗi khi thực hiện các phép so sánh.
Khi bạn xem xét kiểu dữ liệu định danh, hãy chắc chắn rằng bạn sử dụng cùng một kiểu trong tất cả các bảng liên quan (khoá chính - khoá ngoại). Các kiểu dữ liệu cần phải khớp chính xác, bao gồm cả các thuộc tính như UNSIGNED. Ví dụ, nếu bạn sử dụng UNSIGNED INT cho cột định danh trong một bảng, hãy sử dụng cùng kiểu đó trong các bảng khác mà trường này làm khóa ngoại. Trộn lẫn các kiểu dữ liệu khác nhau có thể gây ra các vấn đề về hiệu suất, đặc biệt trong tình huống xảy ra chuyển đổi kiểu ngầm trong các phép so sánh có thể tạo ra các lỗi khó phát hiện. Ngoài ra, phép JOIN trên các cột khác kiểu dữ liệu cũng sẽ không đạt hiệu suất tốt nhất.
Chọn kích thước nhỏ nhất: Chọn kích thước nhỏ nhất có thể chứa phạm vi giá trị yêu cầu và sẵn sàng cho sự phát triển trong tương lai nếu cần thiết. Ví dụ, nếu bạn có một cột province_id lưu trữ ID các thành phố ở Việt Nam, bạn không cần hàng ngàn hay hàng triệu giá trị, vì vậy bạn không nên sử dụng kiểu dữ liệu INT. Kiểu dữ liệu TINYINT là vừa đủ cho tình huống này và cả trong tương lai.
Các lời khuyên khi chọn kiểu dữ liệu cho cột định danh
Sử dụng kiểu số nguyên khi có thể: Ví dụ, sử dụng INT hoặc BIGINT cho các cột định danh.
Sử dụng kiểu dữ liệu nhỏ gọn: Ví dụ, sử dụng TINYINT cho các giá trị có phạm vi nhỏ (ví dụ định danh các bảng ít dữ liệu).
Đồng nhất các thuộc tính: Ví dụ, đảm bảo rằng tất cả các cột id trong các bảng liên quan đều có cùng thuộc tính, ví dụ như UNSIGNED.
Bài viết trên là tổng hợp những gì mình tìm hiểu và học được trong quá trình làm việc, có cóp nhặt ở nhiều nơi (Còn update thêm phần 2, phần 3) . Cũng có nhiều ý kiến và quan điểm khác nên mọi xây dựng, góp ý thảo luận thoải mái nhé ^^ Mong bài viết có ích với mọi người
