Tối ưu hóa MySQL cho Hệ thống Nghiệp vụ

Tốc độ hoạt động của một hệ thống phụ thuộc vào nhiều yếu tố, bao gồm giao diện người dùng (frontend), logic xử lý (backend), cơ sở dữ liệu (database), các thành phần trung gian (middleware), máy chủ (server), và mạng lưới (network). Trong bài viết này, chúng ta sẽ tập trung vào khía cạnh cơ sở dữ liệu, một yếu tố thường xuyên được quan tâm.

Tương tự như việc tối ưu hóa hệ thống, tối ưu hóa cơ sở dữ liệu cũng bao gồm nhiều khía cạnh, bao gồm hiệu suất thực thi của các câu lệnh SQL và tình trạng hoạt động của chính cơ sở dữ liệu. Với sự đa dạng của các hệ quản trị cơ sở dữ liệu, chúng ta sẽ lấy MySQL làm ví dụ.

Các Hướng Tối ưu hóa Hiệu suất MySQL

  1. Tối ưu hóa Cấu hình Kết nối: Các hoạt động như quản lý kết nối (connection pool) hoặc chuyển đổi cơ sở dữ liệu trong quá trình nghiệp vụ có thể tốn kém.

    • Cấu hình máy chủ
    • Tối ưu hóa phía máy khách

  2. Tối ưu hóa Kiến trúc:

    • Thiết kế bộ nhớ đệm (Caching)
    • Phân tách nghiệp vụ offline và thực thi lệch giờ
    • Phân tách cơ sở dữ liệu theo chiều dọc (Vertical Sharding)
    • Tách biệt đọc và ghi (Read/Write Splitting)
    • Phân tách cơ sở dữ liệu theo chiều ngang (Horizontal Sharding/Partitioning)

  3. Phân tích và Tối ưu hóa Câu lệnh SQL:

    • Truy vấn chậm (Slow Query)
    • Xem các luồng đang chạy
    • Xem trạng thái hoạt động của máy chủ
    • Xem trạng thái hoạt động của Storage Engine
    • Kế hoạch thực thi (Explain Plan)
    • Tối ưu hóa SQL và Chỉ mục (Index)

  4. Storage Engine và Cấu trúc Bảng:

    • Lựa chọn Storage Engine phù hợp
    • Tối ưu hóa các cột (fields)

Tối ưu hóa Cấu hình Kết nối

Vấn đề thường gặp về kết nối: error 1040: Too many connections

Sử dụng các lệnh sau để kiểm tra các chỉ số như số lượng kết nối tối đa, số lượng kết nối được sử dụng nhiều nhất, danh sách các tiến trình SQL, và thời gian chờ của máy khách:

show variables like 'max_connections';
show status like 'max_used_connections';
show processlist;
show variables like 'wait_timeout';

Để giải quyết vấn đề thiếu kết nối, chúng ta có thể thực hiện các biện pháp sau:

Tối ưu hóa Cấu hình Máy chủ

  1. Tăng số lượng kết nối khả dụng bằng cách sửa đổi max_connections. Giá trị mặc định là 151, có thể điều chỉnh lên khoảng 1000 tùy thuộc vào hiệu năng của máy chủ cơ sở dữ liệu. Đồng thời, hệ thống nghiệp vụ nên chú ý kiểm soát số lượng kết nối.

Tối ưu hóa Phía Máy khách

  1. Phía máy khách có thể tối ưu bằng cách giảm thiểu số lần thiết lập kết nối đến máy chủ. Giải pháp phổ biến hiện nay là sử dụng connection pool để tái sử dụng kết nối. Tuy nhiên, connection pool không nên quá lớn. Việc tăng kích thước connection pool một cách mù quáng có thể làm giảm hiệu suất hệ thống. Mỗi kết nối cần một luồng riêng để xử lý, và khi số lượng kết nối tăng lên, số lượng luồng cũng tăng theo. Nếu số lượng luồng vượt quá số lõi CPU, CPU sẽ phải thực hiện chuyển đổi ngữ cảnh (context switching) bằng cách phân bổ thời gian. Việc chuyển đổi ngữ cảnh thường xuyên sẽ gây ra chi phí hiệu năng đáng kể. Hikari khuyến nghị kích thước connection pool là: số lõi CPU * 2 + 1.
  2. Kiểm tra xem có các câu lệnh SQL chậm nào đang chiếm giữ kết nối mà không giải phóng kịp thời hay không, từ đó tối ưu hóa các câu lệnh chậm này.
  3. Kiểm tra xem có các kết nối sleep không hoạt động nhưng chưa được giải phóng hay không. Thời gian chờ mặc định của máy khách là 28800 giây (8 giờ), có thể giảm giá trị này xuống một chút để tránh tạo ra quá nhiều kết nối không hoạt động.

Tối ưu hóa Kiến trúc

Thiết kế Bộ nhớ đệm (Caching)

Trong các hệ thống ngày càng phức tạp, chúng ta thường gặp phải các truy vấn chậm. Một số là do khối lượng dữ liệu lớn, nhiều bảng liên kết, hoặc logic tính toán phức tạp, dẫn đến việc chiếm dụng kết nối trong thời gian dài. Tuy nhiên, đối với một số nghiệp vụ không yêu cầu tính thời gian thực của dữ liệu, như các báo cáo hàng ngày, hàng tuần, chúng ta có khoảng thời gian cho phép. Chúng ta có thể thực hiện tính toán, thống kê vào những thời điểm hệ thống ít bận rộn hơn, sau đó lưu trữ dữ liệu vào hệ thống bộ nhớ đệm. Khi gửi báo cáo thực tế, dữ liệu có thể được lấy trực tiếp từ bộ nhớ đệm đã xử lý, giúp giảm tải cho cơ sở dữ liệu và cải thiện hiệu suất truy vấn.

Khi xem xét việc bổ sung bộ nhớ đệm, có các khuyến nghị sau:

  1. Cần xem xét thời hạn hiệu lực của bộ nhớ đệm, chính sách vô hiệu hóa, v.v., để kiểm soát việc sử dụng bộ nhớ đệm.
  2. Tránh hiện tượng cache penetration (truy cập vào dữ liệu không có trong cache): Chủ yếu áp dụng cho dữ liệu không được đưa vào bộ nhớ đệm. Đặc biệt là khi truy vấn trả về null, thông thường nên tạm thời lưu trữ một chỉ báo null (ví dụ: cache null trong 5 giây). Ngoài ra, có thể cân nhắc sử dụng bitmap hoặc Bloom filter để kiểm tra sự tồn tại của dữ liệu.
  3. Tránh hiện tượng cache avalanche (tất cả dữ liệu trong cache hết hạn cùng lúc): Chủ yếu lo ngại khi một lượng lớn dữ liệu hết hạn đồng thời. Thông thường, nên đặt thời gian hết hạn khác nhau cho một nhóm dữ liệu này (tạo sự chênh lệch theo giây).

Phân tách Nghiệp vụ Offline và Thực thi Lệch giờ

Trong một số trường hợp, không phải tất cả các sự kiện đều cần xử lý ngay lập tức. Nên tránh gửi quá nhiều yêu cầu đến cơ sở dữ liệu có thể gây sập hệ thống. Cần xem xét việc truy cập lệch với nghiệp vụ thời gian thực:

  1. Có thể sử dụng hàng đợi tin nhắn (message queue) để lưu trữ tin nhắn trước, sau đó xử lý yêu cầu từ hàng đợi tin nhắn một cách có trật tự tùy thuộc vào tải của máy chủ.
  2. Phân tách các tác vụ offline, ví dụ như tính toán thống kê, và lên lịch thực thi vào nửa đêm.

Phân tách Cơ sở dữ liệu theo Chiều dọc (Vertical Sharding)

- Phân tách một cơ sở dữ liệu đơn lẻ chứa các bảng "liên quan đến đơn hàng", "liên quan đến dự án", "liên quan đến rủi ro", "liên quan đến người dùng", v.v., thành các cụm cơ sở dữ liệu riêng biệt như "cơ sở dữ liệu đơn hàng", "cơ sở dữ liệu rủi ro", v.v.
- Phân tách một bảng duy nhất thành nhiều bảng dựa trên các cột. Ví dụ, chia các cột lạnh như Description, Attachments của trường ID, Name, Description, Attachments trong bảng kế hoạch nhiệm vụ thành một bảng riêng.

Việc tách biệt đọc và ghi giải quyết tốt vấn đề áp lực đọc. Mỗi khi áp lực đọc tăng lên, chúng ta có thể mở rộng theo chiều ngang bằng cách thêm các bản sao (slave replicas). Tuy nhiên, áp lực ghi với sự bùng nổ của nghiệp vụ không được giải quyết hiệu quả, ví dụ như việc người dùng đặt hàng ngày càng chậm. Qua giám sát cơ sở dữ liệu MySQL, chúng ta thấy rằng các thao tác ghi cơ sở dữ liệu ngày càng chậm, một thao tác insert thông thường thậm chí có thể mất hơn 1 giây.

Mặt khác, nghiệp vụ ngày càng phức tạp, nhiều hệ thống ứng dụng sử dụng chung một cơ sở dữ liệu. Nếu một chức năng nhỏ, không cốt lõi gặp độ trễ, nó thường ảnh hưởng đến các chức năng cốt lõi khác trên cơ sở dữ liệu chính. Lúc này, cơ sở dữ liệu chính trở thành nút thắt cổ chai về hiệu suất. Chúng ta nhận ra rằng cần phải nâng cấp kiến trúc một lần nữa, chia tách cơ sở dữ liệu chính, một mặt để nâng cao hiệu suất, mặt khác để giảm thiểu ảnh hưởng lẫn nhau giữa các hệ thống, nhằm nâng cao độ ổn định của hệ thống. Lần này, chúng ta đã chia tách hệ thống theo nghiệp vụ. Như minh họa bên dưới, cơ sở dữ liệu ban đầu khổng lồ được chia thành các cơ sở dữ liệu nghiệp vụ khác nhau theo nghiệp vụ. Mỗi hệ thống chỉ truy cập vào cơ sở dữ liệu nghiệp vụ tương ứng, cố gắng tránh hoặc giảm thiểu truy cập đa cơ sở dữ liệu.

Vertical Sharding

Trong quá trình phân tách cơ sở dữ liệu theo chiều dọc, chúng ta cũng gặp nhiều thách thức. Thách thức lớn nhất là: không được phép JOIN đa cơ sở dữ liệu, đồng thời cần tái cấu trúc mã nguồn hiện có. Khi còn là một cơ sở dữ liệu đơn lẻ, có thể dễ dàng sử dụng JOIN để liên kết các bảng khi truy vấn. Sau khi phân tách, các cơ sở dữ liệu đã phân tách nằm trên các instance khác nhau, do đó không thể sử dụng JOIN đa cơ sở dữ liệu.

Ví dụ, để truy vấn tất cả đơn hàng của một nhà cung cấp bằng tên nhà cung cấp, trước khi phân tách theo chiều dọc, có thể sử dụng JOIN giữa bảng nhà cung cấp và bảng đơn hàng, hoặc sử dụng trực tiếp truy vấn con như sau:

select * from tb_order where supplier_id in (select id from supplier where name=’Tên Nhà Cung Cấp’)；

Sau khi phân tách, cần tái cấu trúc mã nguồn, trước tiên là truy vấn id nhà cung cấp bằng tên nhà cung cấp, sau đó là truy vấn bảng đơn hàng bằng id nhà cung cấp, như sau:

select id from supplier where name=’Tên Nhà Cung Cấp’
select * from tb_order where supplier_id in (supplier_ids )

Bài học kinh nghiệm trong quá trình phân tách cơ sở dữ liệu theo chiều dọc đã giúp chúng tôi đưa ra các thực tiễn tốt nhất cho SQL, trong đó một trong những điều là cấm hoặc hạn chế sử dụng JOIN trong chương trình, thay vào đó nên tập hợp dữ liệu trong chương trình, làm cho câu lệnh SQL đơn giản hơn. Một mặt là để chuẩn bị cho việc phân tách nghiệp vụ theo chiều dọc trong tương lai, mặt khác cũng tránh được vấn đề hiệu suất kém của JOIN trong MySQL.

Sau khi phân tách theo chiều dọc, mỗi ứng dụng chỉ truy cập vào cơ sở dữ liệu tương ứng. Một mặt, cơ sở dữ liệu đơn điểm được chia thành nhiều cơ sở dữ liệu, phân tán áp lực ghi của cơ sở dữ liệu chính. Mặt khác, các cơ sở dữ liệu đã phân tách độc lập, đạt được sự cô lập nghiệp vụ, không còn ảnh hưởng lẫn nhau.

Tách biệt Đọc và Ghi (Read/Write Splitting) (Liên quan đến Đồng bộ Master-Slave)

Hiện tại, hầu hết các hệ thống của chúng ta đều theo kiến trúc microservices. Kiến trúc đơn thể ban đầu khó có thể đáp ứng các tình huống có lượng truy cập cao. Về cơ sở dữ liệu, chúng ta áp dụng giải pháp cụm - tách biệt đọc ghi để nâng cao hiệu quả truy vấn. Trong cụm, một máy master chịu trách nhiệm ghi dữ liệu, và nhiều node slave chịu trách nhiệm truy vấn dữ liệu.

Công nghệ đồng bộ Master-Slave cho phép chúng ta đạt được sự nhất quán tương đối của dữ liệu giữa master và các node slave (thỉnh thoảng có độ trễ giữa master và slave). Các thao tác ghi của master được ghi vào binlog, và các node slave đọc tệp binlog của master thông qua luồng I/O để hoàn thành việc đồng bộ thao tác ghi của cơ sở dữ liệu chính.

Ở đây đã đề cập đến đồng bộ Master-Slave. Đối với MySQL, có nhiều chế độ đồng bộ, bao gồm:

  1. Đồng bộ bất đồng bộ (Asynchronous replication): Thường được sử dụng trong các tình huống giữa các trung tâm dữ liệu. Có thể cần giám sát và kiểm tra tình trạng đồng bộ binlog để đảm bảo tính nhất quán cuối cùng của dữ liệu trên máy slave.
  2. Đồng bộ toàn phần (Fully synchronous replication): Thường được sử dụng trong các trung tâm dữ liệu trong cùng một thành phố với một master và một slave.
  3. Đồng bộ bán phần (Semisynchronous replication): Thường được sử dụng trong các trung tâm dữ liệu trong cùng một thành phố với một master và nhiều slave.

Ví dụ, bảng ghi nhận giao dịch thanh toán của Tencent Pay sử dụng chế độ đồng bộ bán phần giữa các trung tâm dữ liệu trong cùng một thành phố (cùng set) + đồng bộ bất đồng bộ giữa các địa điểm khác nhau và kiểm tra binlog, liên quan đến logic kiểm tra sức khỏe phức tạp.

Phân tách Cơ sở dữ liệu theo Chiều ngang (Horizontal Sharding/Partitioning)

Ví dụ: Bảng đơn hàng, theo một quy tắc nhất định (ví dụ: theo thời gian tạo, thời gian hoàn thành, v.v.), lưu trữ vào nhiều cơ sở dữ liệu và bảng khác nhau.

Tách biệt đọc ghi, thông qua việc mở rộng chiều ngang các bản sao, đã giải quyết áp lực đọc. Phân tách theo chiều dọc, bằng cách tách nghiệp vụ theo chiều dọc, đã lưu trữ áp lực ghi. Tuy nhiên, hệ thống vẫn tồn tại những rủi ro sau:

  • Khối lượng dữ liệu trong một bảng ngày càng lớn. Ví dụ, bảng đơn hàng, số lượng bản ghi trong một bảng nhanh chóng vượt quá một trăm triệu, vượt quá giới hạn của MySQL, ảnh hưởng đến hiệu suất đọc ghi.
  • Áp lực ghi của cơ sở dữ liệu nghiệp vụ cốt lõi ngày càng lớn, không thể tiếp tục phân tách theo chiều dọc. Lúc này, kiến trúc hệ thống, cơ sở dữ liệu chính của MySQL không có khả năng mở rộng theo chiều ngang.

Lúc này, chúng ta cần tiếp tục phân tách ngang cơ sở dữ liệu MySQL.

Vấn đề đầu tiên trong phân tách cơ sở dữ liệu theo chiều ngang là, theo logic nào để phân tách. Một giải pháp là phân tách theo thành phố, tất cả dữ liệu của một thành phố nằm trong một cơ sở dữ liệu; một giải pháp khác là phân tách dữ liệu đều nhau theo ID đơn hàng. Ưu điểm của việc phân tách theo thành phố là mức độ tập trung dữ liệu tương đối cao, việc thực hiện truy vấn tổng hợp tương đối đơn giản và dễ triển khai. Nhược điểm là sự phân bố dữ liệu không đều, dữ liệu của một số thành phố cực kỳ lớn, tạo ra các điểm nóng, và các điểm nóng này trong tương lai có thể buộc phải phân tách lại. Việc phân tách theo ID đơn hàng thì ngược lại, ưu điểm là dữ liệu phân bố đều, không có tình trạng một cơ sở dữ liệu có quá nhiều hoặc quá ít dữ liệu. Nhược điểm là dữ liệu quá phân tán, bất lợi cho việc truy vấn tổng hợp. Ví dụ, sau khi phân tách theo ID đơn hàng, đơn hàng của một nhà cung cấp có thể phân tán trên nhiều cơ sở dữ liệu, và việc truy vấn tất cả đơn hàng của một nhà cung cấp có thể cần truy vấn nhiều cơ sở dữ liệu. Đối với tình huống này, một giải pháp là tạo bảng dự phòng cho dữ liệu cần truy vấn tổng hợp, bảng dự phòng này không bị phân tách, đồng thời giảm thiểu truy vấn tổng hợp trong quá trình phát triển nghiệp vụ.

Sau khi cân nhắc đi cân nhắc lại, chúng tôi cuối cùng đã quyết định phân tách theo ID đơn hàng.

Từ góc độ kiến trúc, hệ thống được chia thành ba lớp:

  • Lớp ứng dụng: Các hệ thống ứng dụng nghiệp vụ khác nhau.
  • Lớp truy cập dữ liệu: Giao diện truy cập dữ liệu thống nhất, che giấu các chi tiết kỹ thuật như phân tách đọc ghi, phân tách bảng, bộ nhớ đệm cho lớp ứng dụng phía trên.
  • Lớp dữ liệu: Phân mảnh dữ liệu DB, và có thể thêm các phân mảnh shard một cách động.

Điểm mấu chốt kỹ thuật của phân tách cơ sở dữ liệu theo chiều ngang nằm ở thiết kế của lớp truy cập dữ liệu. Lớp truy cập dữ liệu chủ yếu bao gồm ba phần:

  • Bộ nhớ đệm phân tán (Distributed Cache)
  • Middleware cơ sở dữ liệu (Database Middleware)
  • Middleware đồng bộ dữ liệu (Data Heterogeneity Middleware)

Middleware cơ sở dữ liệu cần bao gồm các chức năng quan trọng sau:

  • Bộ tạo ID (ID Generator): Tạo khóa chính cho mỗi bảng.
  • Bộ định tuyến dữ liệu (Data Source Router): Định tuyến mỗi thao tác DB đến các nguồn dữ liệu phân mảnh khác nhau.

Bộ tạo ID (ID Generator)

Bộ tạo ID là cốt lõi của toàn bộ việc phân tách cơ sở dữ liệu theo chiều ngang. Nó quyết định cách phân tách dữ liệu và cách truy vấn, lưu trữ, truy xuất dữ liệu. ID cần duy nhất toàn cục trên các cơ sở dữ liệu, nếu không sẽ gây xung đột ở tầng nghiệp vụ. Ngoài ra, ID phải là số và tăng dần, chủ yếu là để đảm bảo hiệu suất của MySQL (nếu là chuỗi UUID ngẫu nhiên, hiệu suất sẽ rất kém trong trường hợp truy cập đồng thời cao và khối lượng dữ liệu lớn). Đồng thời, bộ tạo ID phải rất ổn định, vì bất kỳ lỗi nào cũng sẽ ảnh hưởng đến tất cả các thao tác cơ sở dữ liệu.

Ở đây có thể tham khảo thuật toán Snowflake, cụ thể như sau:

  • Tổng chiều dài nhị phân của ID là 64 bit.
  • 36 bit đầu tiên sử dụng dấu thời gian, để đảm bảo ID tăng dần.
  • 13 bit ở giữa là mã định danh phân tách cơ sở dữ liệu, dùng để nhận dạng bản ghi tương ứng với ID này nằm ở cơ sở dữ liệu nào.
  • 15 bit cuối cùng là chuỗi tự tăng, để đảm bảo ID không bị trùng lặp trong trường hợp đồng thời trong cùng một giây. Mỗi thư viện phân tách có một bảng chuỗi tự tăng. Khi tạo chuỗi tự tăng, lấy giá trị chuỗi tự tăng hiện tại từ bảng chuỗi tự tăng và cộng thêm 1, làm 15 bit cuối cùng của ID hiện tại.
  • Vào giây tiếp theo, chuỗi tự tăng 15 bit cuối cùng sẽ bắt đầu lại từ 1.

Phân tích và Tối ưu hóa Câu lệnh SQL

Trong quá trình tối ưu hóa hệ thống hàng ngày, hiệu suất thực thi của các câu lệnh SQL cũng thường là một phần quan trọng. Chúng ta thường xuyên tối ưu hóa các truy vấn chậm trong hệ thống. Trước khi tối ưu hóa, chúng ta cần biết truy vấn chậm nằm ở đâu, nguyên nhân là gì, sau đó mới có thể tiến hành tối ưu hóa một cách có mục tiêu.

Trạng thái Truy vấn Chậm

Vì việc bật ghi log truy vấn chậm có chi phí hiệu năng, nên theo mặc định, MySQL tắt chức năng ghi log truy vấn chậm. Chúng ta có thể sử dụng một số lệnh SQL để hiểu rõ hơn một số trạng thái:

  • show_query_log: Kiểm tra xem log truy vấn chậm hiện tại có được bật hay không.
  • show_query_log_file: Chỉ định vị trí lưu trữ log truy vấn chậm.
  • log_query_time: Giới hạn thời gian để xác định truy vấn chậm. Giá trị mặc định thường là 10 giây, chúng ta thường đặt là 1 giây.
  • show full processlist: Xem tất cả các luồng đang chạy.
  • show status: Xem trạng thái hoạt động của máy chủ.
  • Max_used_connections: Số lượng kết nối đồng thời tối đa đã được sử dụng kể từ khi máy chủ khởi động.
  • explain: Kế hoạch thực thi (có thể xem chiến lược thực thi cơ bản, ước tính chi phí, và liệu chỉ mục có được sử dụng hay không).

Tối ưu hóa SQL

Thông qua các lệnh trên, chúng ta có thể xem những vấn đề nào đang tồn tại. Sau khi phát hiện vấn đề, chúng ta có thể tiến hành tối ưu hóa thêm. Một số cách tối ưu hóa phổ biến:

  • Tạo chỉ mục phù hợp: Không phải lúc nào việc tạo chỉ mục cũng được sử dụng. Cần phân tích và xử lý theo tình huống thực tế.
    • Việc chỉ mục và kiểu dữ liệu của giá trị lọc không khớp sẽ dẫn đến việc chỉ mục bị vô hiệu hóa.
    • Nguyên tắc khớp trái nhất (leftmost prefix) phổ biến của chỉ mục MySQL, cũng thường là lý do chỉ mục không được khớp do thứ tự thiết lập chỉ mục không nhất quán.
    • Chỉ mục không phải càng nhiều càng tốt. Quá nhiều chỉ mục cũng sẽ làm giảm hiệu suất. Cố gắng để các điều kiện lọc WHERE "càng nhiều càng tốt" được đánh chỉ mục.
  • Sử dụng truy vấn kết hợp (JOIN) thay cho truy vấn con.
  • Sử dụng bảng nhỏ để điều khiển bảng lớn.
  • Tránh các câu lệnh LIMIT với độ dịch chuyển lớn.
  • Tránh WHERE In với số lượng lớn.
  • Tránh truy vấn phạm vi, cố gắng sử dụng khóa chính của bảng hoặc cột chỉ mục cấp một hoặc cấp hai.
  • Tránh quá nhiều bảng kết hợp, có thể tách thành nhiều câu lệnh SQL, sau đó xử lý hợp nhất và phân tách dữ liệu trong bộ nhớ.
  • Cố gắng tránh sử dụng "Select *". Hầu hết các trường hợp, chúng ta không cần truy vấn tất cả các trường.
  • Các trường dạng "Có/Không" có thể chọn tinyint thay vì int hoặc thậm chí bigint.
  • Đối với chuỗi có độ dài cố định, nên chọn char thay vì varchar (varchar cần thêm không gian để ghi lại độ dài của trường).
  • Đối với các trường không được phép NULL, nên đặt là NOT NULL và cung cấp giá trị mặc định.
  • Cố gắng không lưu trữ các tệp lớn, chỉ cần lưu trữ địa chỉ của tệp.
  • Phân tách các trường lớn, dữ liệu lạnh và dữ liệu nóng để tránh Select * làm giảm hiệu suất truy vấn.
  • Khi kết quả đã biết chỉ có một bản ghi, hãy sử dụng LIMIT 1. Ví dụ, phương thức first() của framework PHP Laravel thay vì phương thức get().
  • Câu lệnh SQL không phải càng dài càng "ngầu". Ngược lại, trong hầu hết các trường hợp, vì hiệu suất tốt hơn, chúng ta sẽ chia nhỏ thành nhiều câu lệnh truy vấn nhỏ nhất có thể khi thuận tiện (trước đây băng thông mạng và tốc độ có thể chậm, nên thường nghe nói nên giảm thiểu tương tác với cơ sở dữ liệu, dẫn đến một số câu lệnh SQL phức tạp).
  • Các trường trong ORDER BY nên được đánh chỉ mục.
  • MySQL 5.7 đã hỗ trợ kiểu dữ liệu JSON và cung cấp nhiều hàm, tuy nhiên, khá tốn kém. Chúng ta có thể tạo một cột ảo cho trường JSON của bảng và tạo chỉ mục, liên kết với cột JSON trong bảng trên để nâng cao hiệu quả truy vấn.
    -- Đối với các cảnh quan có phân trang trong các hệ thống web phổ biến, sẽ có một số truy vấn chậm (tránh câu lệnh LIMIT với độ dịch chuyển lớn)
    select * from own_project limit 99999999,100 -- Bộ phân trang hoặc câu lệnh phân trang phổ biến
    select * from own_project where id>=99999999 limit 100 -- Lọc ID trước, vì sử dụng chỉ mục khóa chính, nên hiệu quả cao hơn câu lệnh SQL đầu tiên.

    -- Tối ưu hóa loại JSON (tạo cột ảo, và xây dựng chỉ mục)
    ALTER TABLE project_progress ADD COLUMN milestone_type_base bigint GENERATED ALWAYS AS (json_unquote(json_extract(`extra_properties`, '$.milestone_type_base'))) VIRTUAL;
    ALTER TABLE project_progress ADD INDEX idx_milestone_type_base(milestone_type_base);

Storage Engine và Cấu trúc Bảng

  1. Tối ưu hóa kiểu dữ liệu trong cấu trúc bảng, sử dụng kiểu dữ liệu nhỏ nhất phù hợp để giảm lãng phí không gian bộ nhớ.
  2. Storage Engine mặc định của MySQL mà chúng ta thường sử dụng là InnoDB, ngoài ra còn có một số Storage Engine có thể thích ứng với các tình huống tương ứng:
    • InnoDB: Hỗ trợ giao dịch, khóa ở cấp độ hàng (row-level lock), có thể hỗ trợ lượng truy cập đồng thời cao, hỗ trợ ràng buộc khóa ngoại.
    • MyISAM: Chủ yếu được sử dụng trong các tình huống nghiệp vụ có nhiều thao tác truy vấn, không hỗ trợ giao dịch.
    • Memory: Chủ yếu được sử dụng trong các tình huống bảng tạm thời. Dữ liệu được lưu trữ trong bộ nhớ để nâng cao tốc độ truy cập, nhưng dữ liệu sẽ bị mất khi máy chủ gặp sự cố.

Thẻ: mysql Tối ưu hóa hiệu suất sql kiến trúc cơ sở dữ liệu phân tách cơ sở dữ liệu

Đăng vào ngày 14 tháng 11 lúc 20:00

Thẻ Phổ Biến

©2026 Thành phố Cuồng loạn