Giới thiệu các Storage Engine trong MySQL

Bài viết áp dụng cho MySQL 5.7

InnoDB

Đây là Storage Engine mặc định trong MySQL 5.7. InnoDB là một Storage Engine transaction-safe (tuân thủ ACID) cho MySQL có các commit, rollback và khả năng khôi phục lỗi để bảo vệ dữ liệu người dùng. Row-level locking của InnoDB và kiểu nonlocking read của Oracle-style làm tăng sự đồng thời và hiệu suất của nhiều người dùng. InnoDB lưu trữ dữ liệu người dùng trong các clustered indexes để giảm I/O cho các truy vấn thông thường dựa trên các primary key. Để duy trì tính toàn vẹn của dữ liệu, InnoDB cũng hỗ trợ các ràng buộc toàn vẹn Foreign Key.

MyISAM

Table-level locking giới hạn hiệu suất read/write dữ liệu, vì vậy nó thường được sử dụng cho các công việc read-only hoặc read-mostly trong các cấu hình Web và lưu trữ dữ liệu.

Memory

Lưu trữ tất cả dữ liệu trong RAM, để truy cập nhanh trong các môi trường đòi hỏi tra cứu nhanh các dữ liệu không quan trọng. Engine này trước đây gọi là HEAP Engine. Storage Engine này đang sử dụng ít dần, do InnoDB với vùng bộ đệm cung cấp một cách mục đích chung và bền để giữ hầu hết hoặc tất cả dữ liệu trong memory, và NDBCLUSTER cung cấp tra cứu giá trị quan trọng nhanh cho các bộ dữ liệu phân tán lớn.

CSV

Các bảng của nó thực sự là các tập tin văn bản với các giá trị được phân cách bởi dấu phẩy. Các bảng CSV cho phép bạn nhập hoặc đổ dữ liệu ở định dạng CSV, để trao đổi dữ liệu với các tập lệnh và ứng dụng đọc và ghi cùng một định dạng. Vì bảng CSV không được lập chỉ mục, bạn thường giữ dữ liệu trong các bảng InnoDB trong quá trình hoạt động bình thường và chỉ sử dụng các bảng CSV trong giai đoạn nhập hoặc xuất.

Archive

Các bảng nhỏ gọn, không biểu hiện này được dùng để lưu trữ và truy xuất số lượng lớn các thông tin kiểm tra lịch sử, lưu trữ, hoặc kiểm tra an toàn.

Blackhole

Công cụ lưu trữ Blackhole chấp nhận nhưng không lưu dữ liệu, tương tự như /dev/null trên Unix. Các truy vấn luôn trả về một tập rỗng. Các bảng này có thể được sử dụng trong các cấu hình nhân bản, nơi các lệnh DML được gửi đến các máy chủ nô lệ, nhưng máy chủ chủ không giữ bản sao dữ liệu của chính nó.

NDB (còn được gọi là NDBCLUSTER)

Công cụ cơ sở dữ liệu được nhóm lại này đặc biệt phù hợp với các ứng dụng đòi hỏi thời gian hoạt động và tính khả dụng cao nhất có thể.

Merge

Cho phép một DBA MySQL hoặc nhà phát triển hợp lý nhóm một loạt các bảng MyISAM giống hệt nhau và tham chiếu chúng như một đối tượng. Tốt cho các môi trường VLDB như lưu trữ dữ liệu.

Federated

Cung cấp khả năng liên kết máy chủ MySQL riêng biệt để tạo ra một cơ sở dữ liệu hợp lý từ nhiều máy chủ vật lý. Rất tốt cho môi trường phân phối hoặc dữ liệu mart.

Bảng tổng hợp tính năng của các Storage Engine

• [1] InnoDB hỗ trợ cho việc Geospatial indexing từ MySQL 5.7.5 trở lên.
• [2] InnoDB sử dụng Hash index nội bộ cho tính năng Hash Index của nó.
• [3] InnoDB hỗ trợ FULLTEXT từ MySQL 5.6.4 và sau đó.
• [4] Các bảng InnoDB đã nén được yêu cầu định dạng tệp InnoDB Barracuda.
• [5] Các bảng MyISAM nén chỉ được hỗ trợ khi sử dụng định dạng dòng nén. Các bảng sử dụng định dạng nén với MyISAM chỉ được đọc (readonly).
• [6] Thực hiện trên server (thông qua chức năng mã hóa). Mã hóa dữ liệu có sẵn từ MySQL 5.7 trở lên.
• [7] Thực hiện trên server thay vì Storage Engine.
• [8] Hỗ trợ cho khóa ngoại (foreign key) có sẵn từ MySQL Cluster NDB 7.3 và sau đó.
• [9] Thực hiện trên server thay vì Storage Engine.

InnoDB vs MyISAM

• InnoDB phục hồi từ một vụ crash hoặc tắt máy bất ngờ bằng cách phát lại các bản ghi log của nó. MyISAM phải quét và sửa chữa đầy đủ hoặc xây dựng lại các chỉ mục hoặc bảng có thể đã được cập nhật nhưng không đầy đủ sang ổ cứng. Kể từ khi phương pháp InnoDB là khoảng thời gian cố định trong khi thời gian MyISAM phát triển với kích thước của các tập tin dữ liệu, InnoDB cung cấp sẵn sàng hơn khi kích thước cơ sở dữ liệu phát triển.
• InnoDB, với innodb_flush_log_at_trx_commit đặt thành 1, ghi nhật ký transactioni sau mỗi transaction, cải thiện đáng kể độ tin cậy. MyISAM phải được chạy trên đầu trang của một hệ thống tập tin journal đầy đủ, chẳng hạn như ext4 gắn kết với data=journal, để cung cấp khả năng phục hồi tương tự chống lại các tập tin dữ liệu hỏng. (Journal có thể được đặt trên một thiết bị SSD để cải thiện hiệu năng MyISAM, tương tự, nhật ký InnoDB có thể được đặt trên một hệ thống tập tin không ghi nhật ký như ext2 chạy trên một SSD để tăng hiệu suất tương tự. )
• InnoDB có thể chạy ở chế độ mà nó có độ tin cậy thấp hơn nhưng trong một số trường hợp hiệu năng cao hơn. Thiết lập innodb_flush_log_at_trx_commit đến 0 chuyển sang chế độ mà transaction không commit với disk trước khi kiểm soát được trả lại cho người gọi. Thay vào đó, disk flushes xảy ra trên một bộ đếm thời gian.
• InnoDB tự động nhóm lại nhiều chèn đồng thời và flushes chúng vào đĩa cùng một lúc MyISAM dựa vào bộ nhớ cache khối hệ thống tập tin cho bộ nhớ đệm đọc cho các hàng dữ liệu và các chỉ mục, trong khi InnoDB thực hiện điều này trong chính công cụ, kết hợp các cache hàng với các cache chỉ mục.
• InnoDB sẽ lưu trữ các hàng trong trật tự primary nếu có, khác thứ tự duy nhất thứ tự then chốt. InnoDB sẽ sử dụng một phím số nguyên duy nhất được tạo ra bên trong và sẽ lưu trữ các hồ sơ theo thứ tự chèn, vì MyISAM nào. Ngoài ra, một trường khoá chính có thể tự động được sử dụng để đạt được hiệu quả tương tự.
• InnoDB cung cấp lưu trữ trang nén LZW có thể cập nhật cho cả dữ liệu và chỉ mục. MyISAM bảng nén không thể được cập nhật.
• Khi hoạt động ở các chế độ tuân thủ ACID đầy đủ, InnoDB phải thực hiện một tuôn ra đĩa ít nhất một lần cho mỗi giao dịch, mặc dù nó sẽ kết hợp flushes cho chèn từ nhiều kết nối. Đối với các ổ cứng hoặc mảng điển hình, điều này sẽ áp đặt giới hạn khoảng 200 giao dịch cập nhật mỗi giây. Đối với các ứng dụng yêu cầu tỷ lệ giao dịch cao hơn, bộ điều khiển đĩa với bộ nhớ đệm ghi và sao lưu pin sẽ được yêu cầu để duy trì tính toàn vẹn của giao dịch. InnoDB cũng cung cấp một số chế độ làm giảm hiệu ứng này, tự nhiên dẫn đến mất bảo đảm toàn vẹn giao dịch, mặc dù vẫn giữ được độ tin cậy cao hơn MyISAM. MyISAM không có chi phí này, nhưng chỉ vì nó không hỗ trợ giao dịch.
• MyISAM sử dụng khóa mức bảng để cập nhật và xóa các hàng hiện có, với một tùy chọn để nối các hàng mới thay vì lấy khóa và chèn chúng vào không gian trống. InnoDB sử dụng khóa cấp hàng. Đối với các ứng dụng cơ sở dữ liệu lớn, nơi nhiều hàng thường được cập nhật, cấp hàng khóa là rất quan trọng bởi vì một khóa bảng duy nhất giảm đáng kể đồng thời trong cơ sở dữ liệu.
• Cả InnoDB và MyISAM đều hỗ trợ tìm kiếm toàn văn, với InnoDB đạt được sự hỗ trợ chỉ mục toàn văn trong MySQL 5.6.4, nhưng kết quả có thể khác biệt đáng kể.

Nguồn tham khảo

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines