MySQL ngoại truyện

Cuối tuần vừa rồi mới vừa clear gần 50% table trong database của Teamcrop, đây là những table của những tính năng không còn sử dụng và đã trải qua thời gian deprecated (chờ xử trảm), thấy có lẽ nên viết một bài về database nhân dịp đầu năm mới cũng như khai blog 2019.

Cũng giống như nhiều đàn anh, đàn chị thời nay, mình đã sớm tiếp xúc với PHP từ 2003 khi xu hướng diễn đàn lớp, diễn đàn trường, diễn đàn vớ vẫn nở rộ. Những đoạn code đầu tiên là những mod diễn đàn viết bằng PHP, cũng như những câu truy vấn database MySQL đầu tiên cũng xuất hiện từ thời điểm này. Bài viết mang tính chất cổ súy MySQL nói riêng (RDBMS nói chung) nên nếu ai không biết nó là gì thì nên tìm hiểu hoặc làm với nó ít ít rồi hẳn đọc tiếp và đối tượng bài viết có thể là DB Administrator hoặc Developer có nhiệm vụ làm việc với MySQL, hầu hết chúng ta – web developer cũng là những người maintain database schema.

Làm việc với MySQL hơn 15 năm (và hiện nay vẫn chỉ tin và dùng MySQL), nên ít nhiều có chút kinh nghiệm khi sử dụng cũng như hình thành những quy trình, đường lối cho cá nhân và team khi tham gia xây dựng dự án. Mỗi khi có Web developer mới tham gia vào công ty thì những kiến thức này lại được truyền đạt, tuy nhiên đó giờ vẫn chưa có một tài liệu cụ thể nên bài blog này cũng là một phần phục vụ việc training này.

Tại sao lại gọi là ngoại truyện?

Hầu hết những gì mình chia sẻ ở đây có thể các bạn sẽ rất ít thấy đề cập ở các sách hay giáo trình về MySQL. Thậm chí có những thứ có thể được coi là đi ngược với những gì bạn biết về MySQL.

Để dễ chia sẻ thông tin, mình sẽ tách thành 3 giai đoạn khi các bạn làm việc với MySQL, đó là trước khi có dữ liệu (data), trong khi có dữ liệu và khi không còn dữ liệu. Ở mỗi giai đoạn sẽ có những kinh nghiệm khác nhau và phù hợp với ngữ cảnh để mọi người tiện theo dõi.

Trước khi có dữ liệu

Theo quan sát và kinh nghiệm, rất nhiều bạn khá hời hợt và xem thường giai đoạn này, tức giai đoạn phân tích và thiết kế database, hay nói 1 cách dân dã là tạo bảng (CREATE TABLE..). Rất nhiều vấn đề phát sinh bởi việc thiết kế bảng hời hợt, thậm chí là thiết kế sai, kéo theo technical debt sẽ ngày càng lớn và thậm chí là phải bỏ cả tính năng / bảng đang sử dụng để refactoring lại thành tính năng mới, tạo table mới và migrate dữ liệu từ bảng cũ sang bảng mới.

Ngoài việc thiết kế bảng đúng, duy trì tính dễ hiểu (understandable), dễ bảo trì (maintainable) và đồng nhất (consistent) cũng là một số yếu tố luôn được cân nhắc khi thiết kế một table. Bên dưới là một số “guideline” mà ai muốn tham gia vào team của Tuấn sẽ được training và bắt buộc phải theo:

Về database:

Trong một Database, tất cả tên bảng phải có tiền tố giống nhau (prefix). Ví dụ: crp_, abc_…
Để tăng khả năng chuyển đổi DBMS và tương thích với các hệ SQL, không sử dụng các khái niệm “phức tạp” của database như Store procedure, View, Constraint
Không sử dụng khái niệm ràng buộc khóa chính, khóa ngoại cài đặt trong DBMS. Chỉ sử dụng khái niệm này trong quá trình trao đổi thông tin giữa các developer và thiết kế bảng.
Áp dụng kiến trúc Microservices để chia nhỏ database theo từng domain/context riêng để giúp giảm tải chung cho toàn Database. Ví dụ: Mỗi service sẽ cần 1 kiến trúc / server DB riêng để vận hành riêng biệt.
Những phiên bản MySQL khác nhau sẽ có một số config khác nhau kéo theo những lỗi không ngờ tới khi nâng cấp phiên bản.
Không sử dụng docker / VM cho MySQL Server.

Về bảng (table):

Khi đặt tên bảng, tên cột thì không sử dụng chữ HOA hoặc ký tự đặc biệt. Cho phép a-z, 0-9 và underscore (gạch dưới “_”).
Tên bảng luôn là tiếng anh và sử dụng từ số ít. Ví dụ: crp_product, crp_news
Nếu tên bảng là tổ hợp nhiều từ, thì mỗi từ cần cách nhau gạch dưới (underscore). Ví dụ: abc_product_category, abc_order_detail
Trừ các cột khóa ngoại, các cột dữ liệu riêng của một bảng luôn có tiền tố giống nhau, và tổng hợp từ chữ cái đầu tiên của tên bảng (không bao gồm tiền tố database). Ví dụ: bảng abc_product_category thì các cột của bảng này phải có tiền tố là “pc_”, như “pc_id”, “pc_name”..
Tên cột ngoài tiền tố bảng thì là các từ tiếng anh, số ít và không có khoảng cách, các từ phải viết liền nhau. Ví dụ: p_countview, p_datecreated..
Khóa chính của một cột trong bảng luôn là tiền tố của bảng + “id”. Ví dụ: “pc_id”, “p_id”, “n_id”..
Khóa ngoại của một bảng sẽ được nằm đầu tiên trong danh sách cột của bảng này, trước cả cột khóa chính. Tên cột khóa ngoại phải giữ nguyên tên cột mà nó làm khóa chính trong bảng của nó. Ví dụ: u_id là User ID và là khóa chính trong bảng “abc_user”, thì khi làm khóa ngoại trong bảng “abc_product” thì nó vẫn là cột “u_id”.
Collation của database / table / text column là “utf8_general_ci”
Table luôn có engine mặc định là MyISAM (tối ưu cho SELECT dữ liệu)
Có thể tận dụng engine MEMORY để tối ưu truy vấn vì toàn bộ dữ liệu của table loại MEMORY sẽ nằm trong RAM thay vì ổ cứng.

Về kiểu dữ liệu của cột trong bảng:

Kiểu dữ liệu của khóa ngoại và khóa chính phải trùng khớp nhau (data type & data length)
Không sử dụng kiểu dữ liệu DATETIME trừ trường hợp lưu ngày sinh.
Không sử dụng kiểu dữ liệu ENUM trong trường hợp cần lưu theo logic này, chỉ cần lưu INT (11) hoặc SMALLINT(3) và trong Model của code sẽ khai báo constant.
Tất cả cột thời gian đều lưu Timestamp và kiểu dữ liệu INT(10).
Các cột dữ liệu thời gian luôn là những cột nằm cuối cùng của bảng. Hầu hết các bảng luôn có cột: datecreated (ngày tạo) và datemodified (ngày cập nhật).
Trường IP Address luôn là BIGINT (20), và sử dụng hàm ip2long() và long2ip() của PHP để encode/decode.
Giá trị tiền nên là DECIMAL (18), tốt nhất là DECIMAL (18,4)
Cẩn thận khi thiết kế dữ liệu cho việc lưu ký tự emoji.

Việc tuân thủ các guideline này sẽ giúp rất nhiều trong việc debug cũng như đoán được nguồn gốc, xuất xứ của 1 cột khi làm việc, và tạo sự nhất quán trong toàn team để dễ trao đổi, debug. Ví dụ bên dưới là thiết kế cho table user, product.

CREATE TABLE `crp_user` (
  `u_id` int(11) NOT NULL,
  `u_email` varchar(128) NOT NULL,
  `u_password` text NOT NULL,
  `u_ipaddress` bigint(20) NOT NULL,
  `u_status` smallint(3) NOT NULL,
  `u_datecreated` int(10) NOT NULL,
  `u_datemodified` int(10) NOT NULL,
  `u_datelastloggedin` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `crp_product` (
  `u_id` int(11) NOT NULL,
  `p_id` int(11) NOT NULL,
  `p_name` varchar(255) NOT NULL,
  `p_description` text NOT NULL,
  `p_price` decimal(18,4) NOT NULL,
  `p_status` smallint(3) NOT NULL,
  `p_ishot` tinyint(1) NOT NULL,
  `p_datecreated` int(10) NOT NULL,
  `p_datemodified` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Với những tiêu chí trên thì đảm bảo team bạn sẽ dễ dàng làm việc trên các bảng dữ liệu của các dự án.

Trong khi có dữ liệu

Sau quá trình thiết kế bảng hoàn tất và đã được đưa lên hệ thống, tính năng cũng đã được chạy và bạn bắt đầu có dữ liệu, không gì tuyệt hơn là có dữ liệu. Đến giai đoạn này, việc duy trì cho hệ thống đang chạy là ưu tiên hàng đầu, và một số chia sẻ ở giai đoạn này cũng nhằm giúp việc đảm bảo hệ thống DB không bị gián đoạn.

Không thay đổi tên cột trong bất kì tình huống nào.
Không xóa cột trong bất kì tình huống nào.
Chỉ đổi kiểu dữ liệu cột sang cùng loại và sang dung lượng lớn hơn. Ví dụ cho phép đổi từ VARCHAR(50) -> VARCHAR (100), SMALLINT(3) -> INT (11).
Cần áp dụng replicate database (Master / Slave) dữ liệu để giảm tải. Để tránh rắc rối không cần thiết, chỉ nên sử dụng 1 Master và có thể cho phép nhiều Slave.
Trong bất kì tình huống nào liên quan đến vấn đề đồng bộ giữa Master & Slave và liên quan đến lỗi đồng bộ binlog, tốt nhất là disable replicate, chỉ sử dụng Master, tìm ra nguyên nhân cụ thể để khắc phục và phần lớn vấn đề liên quan đến networking, ổ cứng mạng, tắt bất tử.
Lưu ý “Delay Gap” (thời gian bất đồng bộ dữ liệu) giữa Master và Slave(s).
Đồng bộ dữ liệu vào thời gian ít truy cập nhất
Backup dữ liệu định kì và upload lên một hệ thống / server khác hệ thống đang chạy. Bên mình kết hợp S3cmd để đẩy file backup sql lên Amazon S3 để backup mỗi ngày.
Tuyệt đối lưu ý đến max connection mặc định của MySQL là 150 để tránh lỗi MySQL connection.
Nếu cài đặt max connection lớn thì lưu ý việc MySQL server sẽ block IP do cơ chế security.
Hạn chế đến mức tối đa việc sử dụng JOIN TABLE để tăng khả năng refactoring, migration cũng như giảm thiểu vấn đề về performance / SQL execution. Chỉ JOIN khi đây là hoạt động bất khả kháng và không còn phương án nào tối ưu hơn cho việc truy vấn (tìm kiếm).
Không sử dụng JOIN cho các entity không liên quan đến nhau hoặc cùng 1 domain/service. VD: không join user & comment, product & comment..Có thể JOIN order & order detail.
Luôn có Cache layer ở tầng Application (Redis, memcached) để hạn chế truy vấn vào Database cũng như hỗ trợ việc hạn chế JOIN.

Khi dữ liệu không cần thiết

Hầu hết dự án nào cũng có những tính năng “không còn sử dụng” và có nhu cầu xóa bỏ luôn dữ liệu thì bạn cũng cần có những quy định cho việc này. Vì sao phải xóa dữ liệu? Theo thời gian, giữ liệu sẽ ngày một phình to, nếu vẫn cố giữ những dữ liệu không còn sử dụng thì sẽ kéo theo thời gian backup / migration / recovery lớn hơn, dẫn đến nhiều rủi ro tiềm ẩn không cần thiết, do đó, tốt nhất là xóa những gì không dùng. Hoặc nếu muốn giữ lại thì cũng nên tách data này sang 1 nơi mà không ảnh hưởng đến performance / rủi ro chung của toàn hệ thống.

Bên dưới là một số bước mà bên mình dùng để xóa dữ liệu, các bạn có thể tham khảo và áp dụng.

Bởi vì không có cài đặt ràng buộc tự động (Constraint) nên tầng Application cần có các tính năng (cronjob) tự động để xóa các dòng dữ liệu không cần thiết hoặc update các cột dữ liệu bị lỗi.
Đảm bảo các bảng cần xóa không được tham chiếu bởi bất kì tính năng nào. Nếu có tham chiếu thì cần đảm bảo tính năng này hiện đã không còn sử dụng.
Kiểm tra ngày cuối cùng mà bảng này có dữ liệu mới, hay modified date của bảng.
Không có bảng ngay mà chỉ đổi tên bảng và thêm tiền tố. Ví dụ: thêm OLD_, DEPRECATED_
Chờ tối thiểu 1 tháng -> 3 tháng và chỉ xóa các bảng có tiền tố định trước là sẽ xóa.
Hạn chế tối đa việc gõ tên bảng, nên copy tên bảng ra trước rồi copy vào CMD.
Khi xóa bảng thì không nên dùng MySQL Client / GUI để thao tác mà thông qua MySQL CLI và thực thi SQL mà thôi.
Theo dõi hệ thống support / CS tối thiểu 1 ngày để phát sinh những support ticket liên quan đến mất dữ liệu, lỗi tính năng do khách hàng report.

Trên đây là tất cả những quy trình, kinh nghiệm mà team mình áp dụng khi làm việc với MySQL. Hy vọng mọi người cũng có những quy trình để giúp bảo vệ dữ liệu tốt hơn và cải thiện quá trình lưu trữ và làm việc nhóm liên quan đến cấu trúc dữ liệu.

Xem thêm:

Google Analytics API cho PHP
Lab 2: Lập Trình Hướng Đối Tượng Trong PHP
Lab 3: Tạo Cơ Sở Dữ Liệu (#LTHĐT)