Video học lập trình mỗi ngày

Bài viết được đưa vào: Xây dựng hệ thống bán vé tàu với lượng đồng thời cao vào dịp TẾT

Thực tế nếu như chúng ta là A, bắt đầu nhận một nhiệm vụ mới với một dự án B. Trong đó B sử dụng nhiều công nghệ trong đó và trong đó có sử dụng MySQL để làm dữ liệu tập trung cho ứng dụng.

Giả sử, khi sử dụng prometheus và grafana thì chúng ta thấy hiệu suất truy vấn MySQL cực kỳ chậm, vậy A sẽ bắt đầu lộ trình tối ưu như thế nào? Nếu tôi là A thì sẽ như thế này?

Lập Trình Viên A - Hành trình tối ưu MySQL

Gần đây, qua hệ thống giám sát Prometheus và Grafana, chúng tôi nhận thấy một vấn đề nghiêm trọng: hiệu suất truy vấn MySQL cực kỳ chậm, các biểu đồ thời gian phản hồi đỏ rực, và người dùng bắt đầu có dấu hiệu tắt nhanh ứng dụng khi vừa mới truy cập, đây là một tín hiệu bất thường...

Giám sát MYSQL thông qua prometheus và grafana với GO

Đây là một trong những kinh nghiệm của những dự án thực tế để lại và nếu có cơ hội anh em cũng nên thử một lần. Hầu hết các ứng dụng lớn thì việc tách biệt giữa database và các layer như backend là điều dễ hiểu, không phải cũng có thể truy cập vào db một cách dễ dàng, và không phải ai cũng có thể tối ưu dữ liệu qua một vài năm.

Vì vậy, nếu không giải quyết được vấn đề, thì quy luật sẽ là vấn đề sẽ giải quyết chúng ta...

Dưới đây là cách mà A sẽ giúp chúng ta nhanh chóng khoanh tròn phạm vi hơn so với bắt đầu đi tìm hiểu. Nếu bạn muốn có câu trả lời thì tôi sẽ nói luôn, khỏi mất công Anh Chị lại nói tôi dài dòng.

Tất nhiên đây là kinh nghiệm tôi và bạn biết đây kiến thức không bao giờ là đủ...

4 bước tối ưu MYSQL

Tốt nhất là chúng ta nên tối ưu hóa theo thứ tự sau:

1. Tối ưu hóa các câu lệnh SQL và index;

)Hay xem kỹ lại, đã có index chưa? Nếu có thì câu query đã sử dụng đúng chưa? Nếu chưa thì hãy phân tích bằng EXPLAIN... (Xem bài viết này để thực hành: Sai lầm về Index bạn đã gặp chưa?

2. Tối ưu hóa cấu trúc bảng cơ sở dữ liệu;

Sau khi index đã được áp dụng thành công nhưng dữ liệu vẫn chậm thì xem lại bước 2, dữ liệu lớn thì việc query sẽ chậm lại đó là điều không bàn cãi. Vì vậy phải áp dụng tách NGANG và DỌC (xem bài viết này để thực hành: Khi dữ liệu LỚN ta phải hành động

3. Tối ưu hóa cấu hình hệ thống;

Sau khi tách thành công dữ liệu, cần xem lại cấu hình trong Mysql ví dụ như các thông số innodb_buffer_pool_size...

4. Tối ưu hóa phần cứng;

Đương nhiên, nhà nghèo không thể đi chiếc RR được...

Dưới đây là phân tích kỹ hơn...

À, bạn có muốn biết monitor nào giám sát hiệu suất MySQL không? Nếu chưa hãy Xem bài viết này để thực hành: Giám sát hệ thống dữ liệu qua Promethues + Grafana với JAVA...

Ngoài ra, một backend tối ưu được hệ thống thì quá tốt nhưng nếu tốt hơn thì hãy học nhiều ngôn ngữ backend thì càng tốt, vì trong công ty họ sẽ luân chuyển ví dụ từ JAVA qua làm GO or NestJS... Và ngược lại, đây là ƯU ĐIỂM. Vì vậy nếu có thời gian bạn có thể tham khảo các dự án của các ngôn ngữ sau:

Go Backend (Intern -> Fresher -> Junior)

JAVA Backend (Intern -> Fresher -> Junior -> Senior)

Nodejs Backend (Intern -> Fresher -> Junior -> Senior)

NestJS Backend (Intern -> Fresher -> Junior)

OK, bắt đầu...

Bước 0: Hãy đi nhỏ nhẹ và cười duyên dáng.

Trước khi lao vào bất kỳ thay đổi nào, việc đầu tiên là hiểu rõ tình hình hiện tại, và nắm bắt tâm lý, chứ quát tháo là xong phimg, đương nhiên hãy xem các thông số qua trình giám sát monitor nha anh em...

1. Xác nhận qua Grafana và Prometheus:

   • Các chỉ số chính cần xem: Thời gian phản hồi trung bình/P95/P99 của các truy vấn, số lượng truy vấn chậm (slow queries), % CPU usage của MySQL, Memory Usage, I/O Wait, số lượng kết nối hiện tại.
   • Mục tiêu: Xác định mức độ nghiêm trọng và các khung thời gian cụ thể khi sự cố xảy ra.

2. Kích hoạt và thu thập Slow Query Log của MySQL:

   • Nếu chưa bật, đây là ưu tiên hàng đầu. Trong file cấu hình MySQL (my.cnf hoặc my.ini):

     slow_query_log = ON
     slow_query_log_file = /var/log/mysql/mysql-slow.log
     long_query_time = 1  # Bắt đầu với 1 giây, có thể điều chỉnh
     log_queries_not_using_indexes = ON # Ghi lại cả các query không dùng index
     

   • Mục tiêu: Có được danh sách cụ thể các truy vấn "tội đồ".

3. Kiểm tra cấu hình MySQL hiện tại:

   • Xem xét các tham số quan trọng như innodb_buffer_pool_size, max_connections, các buffer liên quan đến thread.
   • Mục tiêu: Hiểu sơ bộ về cách MySQL đang được thiết lập.

4. Hỏi đội phát triển và vận hành (nếu có):

   • Có thay đổi lớn nào gần đây không (code deploy, thay đổi cấu hình, lượng người dùng tăng đột biến)?
   • Các tính năng nào đang bị ảnh hưởng nhiều nhất?
   • Ở đây cần tỉ mỉ không manh động chửi rủa nha anh em. Bình tĩnh vì chúng ta mới tới thôi... khlalalala

Lộ Trình Tối Ưu Hóa MySQL Thực Chiến Của A

Với những thông tin ban đầu ta điều tra, lúc này A bắt đầu hành trình tối ưu theo một lộ trình có hệ thống, NHỚ là ưu tiên những thay đổi có tác động lớn và chi phí thấp trước.

1. Hãy xem xét về INDEX

Đây là nơi mà A tin rằng sẽ tìm thấy những điều kỳ diệu, vì đánh index chưa chắc là đã sử dụng đúng cách...

• Phân tích Slow Query Log bằng pt-query-digest hoặc mysqldumpslow :

  • Công cụ pt-query-digest từ Percona Toolkit cực kỳ mạnh mẽ. Nó sẽ nhóm các truy vấn tương tự, sắp xếp theo tổng thời gian thực thi, số lần chạy, và nhiều tiêu chí khác.

    pt-query-digest /var/log/mysql/mysql-slow.log > slow_queries_report.txt
    

  • A tập trung vào:

    • Các truy vấn chiếm nhiều thời gian thực thi nhất.
    • Các truy vấn được chạy thường xuyên nhất nhưng vẫn chậm.
    • Các truy vấn quét một lượng lớn hàng (Rows_examined).

• Sử dụng EXPLAIN cho từng câu query mà truy vấn chậm:

  • )A sẽ chạy EXPLAIN SELECT ... cho từng truy vấn đáng ngờ. (Xem video về bài thực hành: Sử dụng Explain theo phong cách chuyên nghiệp

  • A tìm kiếm các dấu hiệu bất thường trong output của EXPLAIN :

    • type: ALL hoặc type: index: Đây là dấu hiệu xấu, MySQL đang phải quét toàn bộ bảng hoặc toàn bộ index. Mục tiêu là ref, eq_ref, range.
    • key: NULL: Không có chỉ mục nào được sử dụng.
    • Rows_examined quá lớn so với Rows_sent.
    • Extra: Using filesort: MySQL phải thực hiện sắp xếp ngoài bộ nhớ, rất tốn kém.
    • Extra: Using temporary: MySQL phải tạo bảng tạm, cũng là một dấu hiệu không tốt.

  • Ví dụ, A phát hiện một truy vấn:

    EXPLAIN SELECT o.order_id, c.customer_name, o.order_date
    FROM orders o
    JOIN customers c ON o.customer_id_legacy_varchar = c.customer_id_int -- !! Kiểu dữ liệu khác nhau
    WHERE YEAR(o.order_date) = 2023 AND o.status = 'COMPLETED';
    

    EXPLAIN có thể cho thấy key: NULL trên JOIN và Using filesort do hàm YEAR().

• A dựa trên kết quả EXPLAIN :

  • Thiếu chỉ mục:

    • Nếu cột trong WHERE hoặc ON (của JOIN) không có chỉ mục, A sẽ tạo chỉ mục.

    • Ví dụ: Cho truy vấn trên, A sẽ xem xét tạo chỉ mục trên orders(status, order_date) và customers(customer_id_int).

      ALTER TABLE orders ADD INDEX idx_status_order_date (status, order_date);
      ALTER TABLE customers ADD INDEX idx_customer_id_int (customer_id_int);
      

  • Chỉ mục không được sử dụng hiệu quả:

    • Hàm trong WHERE : A sẽ viết lại truy vấn để tránh hàm trên cột có chỉ mục.

      -- Thay vì: YEAR(o.order_date) = 2023
      -- A sẽ dùng:
      o.order_date >= '2023-01-01' AND o.order_date < '2024-01-01'
      

    • LIKE '%value%' : Nếu có thể, A sẽ cố gắng thay đổi logic để sử dụng LIKE 'value%' hoặc tìm kiếm Full-Text.
    • Kiểu dữ liệu không khớp trong JOIN : Đây là một lỗi phổ biến. A sẽ làm việc với đội phát triển để đồng bộ kiểu dữ liệu của o.customer_id_legacy_varchar và c.customer_id_int hoặc thực hiện ép kiểu ở phía ứng dụng trước khi truyền vào query, nếu không thể thay đổi schema ngay. Tuy nhiên, cách tốt nhất vẫn là đồng bộ schema.
  • Tối ưu hóa SELECT * : A sẽ rà soát các truy vấn dùng SELECT * và thay thế bằng việc liệt kê cụ thể các cột cần thiết, đặc biệt nếu có thể tạo Covering Index. (Xem video về bài thực hành: Vì sao SELECT * là gánh nặng thiệt hại kinh tế)
  • Phân trang với OFFSET lớn: Nếu dự án B có phân trang với số trang lớn, A sẽ tìm hiểu các kỹ thuật như "Seek Method" (Keyset Pagination) để thay thế.(Xem video về bài thực hành: Tối ưu phân trang với 10.000.000 dữ liệu từ 7s còn 1s)

• Rà soát và loại bỏ chỉ mục thừa/không sử dụng:

  • Chỗ này hơi run tay... Xoá nhầm là Stress nặng luôn... *

2. DỮ LIỆU LỚN - NÊN TÁI CẤU TRÚC BẢNG - NGANG vs DỌC

Tiếp đến sau khi index được tối ưu nhưng vẫn chậm thì chúng ta hãy xem A thiết kế lại các table có dữ liệu LỚN... Or sử dụng sai mục đích.

• Kiểu dữ liệu có tối ưu?

  • A kiểm tra xem có cột nào đang dùng INT trong khi TINYINT hoặc SMALLINT là đủ không. Ví dụ, cột status chỉ có vài giá trị thì dùng TINYINT hoặc ENUM sẽ tốt hơn.
  • Cột VARCHAR có độ dài quá lớn so với dữ liệu thực tế không?
  • Có sử dụng TEXT/BLOB một cách không cần thiết không?

• Chuẩn hóa và Phi chuẩn hóa:

  • A đánh giá xem các bảng có được chuẩn hóa ở mức hợp lý không.
  • Tuy nhiên, nếu một số truy vấn đọc thường xuyên phải JOIN quá nhiều bảng và gây chậm, A có thể cân nhắc việc phi chuẩn hóa có chủ đích cho một số trường hợp cụ thể, chấp nhận một chút dư thừa dữ liệu để tăng tốc độ đọc (ví dụ: lưu thêm customer_name vào bảng orders nếu nó được hiển thị thường xuyên cùng đơn hàng). Hoặc A sẽ chuyển qua một cấu trúc khác như ES nếu nhà có điều kiện.

• Partitioning cho bảng "khổng lồ":

  • Nếu dự án B có những bảng log, bảng sự kiện phát triển cực nhanh, A sẽ nghiên cứu khả năng phân vùng bảng (ví dụ, theo tháng hoặc quý). Điều này giúp các truy vấn chỉ quét trên một phần dữ liệu nhỏ hơn và việc quản lý (như xóa dữ liệu cũ) cũng dễ dàng hơn.

• Storage Engine:

  • A đảm bảo rằng tất cả các bảng quan trọng đều đang sử dụng InnoDB. Nếu còn bảng MyISAM nào đó mà cần giao dịch hoặc hiệu năng ghi tốt, A sẽ lên kế hoạch chuyển đổi.
  • Ở đây anh em nhớ có câu thần chú rằng: Đọc nhiều ghi ít là MyISAM...

3. Cấu Hình MySQL - Tối Ưu Môi Trường Hoạt Động

Với SQL và schema đã được cải thiện, A bắt đầu xem xét các "nút vặn" trong file cấu hình my.cnf.

• innodb_buffer_pool_size :

  • A kiểm tra giá trị hiện tại. Nếu server có nhiều RAM trống mà buffer pool quá nhỏ, đây là một điểm cần tăng ngay. A sẽ đặt mục tiêu khoảng 70-80% RAM vật lý của server (nếu server chỉ dành cho MySQL).
  • Ví dụ: Nếu server có 32GB RAM, A có thể đặt innodb_buffer_pool_size = 24G.

• innodb_log_file_size :

  • Kích thước log file phù hợp giúp cân bằng giữa hiệu năng ghi và thời gian phục hồi. A sẽ xem xét workload ghi của dự án B để điều chỉnh.

• innodb_flush_log_at_trx_commit :

  • A sẽ thảo luận với đội về mức độ chấp nhận rủi ro mất dữ liệu. Nếu hệ thống yêu cầu tính toàn vẹn cao nhất, giữ nguyên giá trị 1. Nếu có thể chấp nhận một chút rủi ro để đổi lấy hiệu năng ghi tốt hơn, có thể xem xét 2.

• Các buffer khác:

  • A sẽ cẩn trọng với các buffer cho mỗi thread (sort_buffer_size, join_buffer_size...). Tăng quá lớn có thể gây cạn kiệt bộ nhớ khi có nhiều kết nối. A sẽ theo dõi các biến status như Sort_merge_passes để xem có cần tăng sort_buffer_size không.

• Công cụ hỗ trợ:

  • A sử dụng MySQLTuner-perl hoặc pt-variable-advisor để có thêm gợi ý, nhưng không áp dụng một cách máy móc mà luôn hiểu rõ ý nghĩa của từng thay đổi.

Quan trọng: Mỗi khi A thay đổi cấu hình, A đều thực hiện trên môi trường staging trước (nếu có), khởi động lại MySQL và theo dõi chặt chẽ các chỉ số trên Grafana để đảm bảo thay đổi mang lại hiệu quả tích cực và không gây ra vấn đề mới.

4. Bước Cuối Cùng (Nếu NHÀ GIÀU): Nâng Cấp Phần Cứng

Nếu sau tất cả các nỗ lực tối ưu hóa phần mềm mà hiệu năng vẫn chưa đáp ứng được yêu cầu tải của dự án B, A sẽ đề xuất nâng cấp phần cứng.

• Thêm RAM: Ưu tiên hàng đầu, để tăng innodb_buffer_pool_size.
• Chuyển sang SSD/NVMe: Nếu hệ thống đang bị nghẽn I/O (I/O Wait cao trên Grafana).
• Nâng cấp CPU: Nếu CPU thường xuyên ở mức cao dù I/O không phải là vấn đề.

Giám Sát Liên Tục và Tinh Chỉnh

Tất nhiên hành trình tối ưu của A không dừng lại sau khi các vấn đề ban đầu được giải quyết. Mà còn nhiều điều A phải thận trọng và quan sát, ví dụ hãy cố gắng tạo thói quen như này anh em:

• Grafana và Prometheus vẫn là bạn đồng hành: A thiết lập các cảnh báo (alerts) cho các ngưỡng hiệu năng quan trọng.
• Định kỳ phân tích Slow Query Log: Ngay cả khi hệ thống ổn định, A vẫn kiểm tra log định kỳ để phát hiện sớm các truy vấn mới có khả năng gây chậm.

Đây là câu chuyện của A trong việc chinh phục thử thách tối ưu MySQL cho dự án B. Mỗi dự án sẽ có những đặc thù riêng, nhưng hy vọng lộ trình này và những kinh nghiệm này sẽ cung cấp cho bạn một khung sườn hữu ích để bắt đầu hành trình tối ưu của riêng mình.

Xin chào anh em!! Cảm ơn thằng ĐỆ...