Nghiên cứu về kiến trúc và cách tối ưu MySQL

Tổng quan

MySQL là một hệ thống quản trị cơ sở dữ liệu quan hệ mã nguồn mở được phát triển dựa trên ngôn ngữ C và C++, được tạo ra bởi MySQL AB với các nhà sáng lập David Axmark, Allan Larsson và Michael Widenius. Phiên bản mới nhất hiện tại là phiên bản MySQL 9.0.
MySQL là một sản phẩm mã nguồn mở theo các điều khoản của GNU (General Public License).
MySQL là một hệ quản trị cơ sở dữ liệu quan hệ được sử dụng phổ biến và rộng rãi nhất hiện nay vì tính open source và các chức năng đầy đủ và hiệu suất cao của nó.

Kiến trúc

• Sơ đồ kiến trúc của MySQL
  
  Nguồn

Client Layer

• Là lớp kiến trúc trên cùng của kiến trúc hệ thống MySQL.
• Các service quan trọng của lớp này là: Connection Handling, Authentication và Security.
  • Connection Handling: Nơi để quản lý các connection từ client. Khi Client kết nối đến server nó sẽ có riêng một luồng (thread) cho kết nối của nó. Server sẽ cache threads nên sẽ không cần phải tạo mới mỗi khi có kết nối mới.
  • Authentication: Khi client kết nối thì sẽ cần phải xác thực thông tin tài khoản (authentication) để có thể truy cập vào được MySQL. Thông tin cần được xác thực thông thường sẽ là username và password.
  • Security: cung cấp các chức năng bảo mật cho database, kiểm tra xem client sau khi xác thực có quyền để truy cập dữ liệu hay không.

Server Layer

• Là lớp kiến trúc tiếp theo của MySQL, chịu trách nhiệm xử lý câu lệnh query và cung cấp nhiều chức năng tiện ích khác.
• Các thành phần quan trọng:
  • Parser: Khi câu query được client yêu cầu thì server sẽ phân tích để tạo thành 1 cây truy vấn và viết lại câu query , xác định xem câu query đó sẽ được thực thi như thế nào, lựa chọn loại index như thế nào.
  • Optimizer: nơi câu lệnh sẽ được tối ưu để thực thi. Ở phần này thì sẽ không quan tâm đến storage engine sử dụng là gì. Tuy nhiên các storage engine sẽ có khả năng ảnh hưởng đến quy trình tối ưu câu truy vấn riêng.
  • Query cache: Là nơi lưu trữ kết quả truy vấn với câu truy vấn. Nếu lần sau có 1 truy vấn giống hệt thì hệ thống sẽ lấy kết quả ở đây ra và bỏ qua Parser và Optimizer. Ở các version mới (8.0 trở lên) thì query cache đã hoàn toàn bị loại bỏ vì vấn đề bị nghẽn cổ chai của nó (bottleneck).
  • Services & utilities: Cung cấp nhiều dịch vụ hỗ trợ:
    • Backup & Restore
    • Security: cung cấp hệ thống user và permission
    • Replication: Là quá trình cho phép một data từ máy chủ có thể sao chép và đồng bộ qua nhiều máy chủ (con) khác
    • Cluster
    • Partitioning: Phân chia một bảng ra làm nhiều phần nhỏ theo một logic nhất định
    • Workbench: là một công cụ trực quan để thao tác và sử dụng MySQL

Storage Layer

• Là lớp kiến trúc phụ trách quản lý cách lưu trữ dữ liệu của database
• Theo mặc định thì MySQL sử dụng storage engine mặc định là InnoDB. Chúng ta sẽ nghiên cứu sâu hơn vào InnoDB vào một bài viết khác.
• MySQL hỗ trợ nhiều loại storage engine khác nhau như:
  • InnoDB (Mặc định)
  • MyISAM
  • MEMORY
  • CSV
  • ARCHIVE
  • BLACKHOLE
  • MERGE
  • FEDERATED

Cách tối ưu hiệu suất MySQL

Tối ưu SQL

Câu lệnh xem chiến lược thực thi

• Chỉ dự đoán không thực thi

explain cau_lenh

• Chạy thực thi và trả về kết quả

explain analyze cau_lenh

Kĩ thuật đánh index

• Giúp tối ưu hiệu năng hơn khi tìm kiếm.
• Khi đánh index nhiều cột thì cột đầu tiên được đánh index rất quan trọng ➔ nó quyết định xem hiệu năng của câu truy vấn dựa trên index như thế nào. VD index(ten, ngay_sinh) thì khi tìm where theo tên thì có thể dùng được index này còn theo ngày sinh thì không và ngược lại nếu đổi thứ tự
• Câu lệnh tạo index:

create index idx_namsinh on customers(nam_sinh);

• Câu lệnh chuyển chế độ index sang invisible ➔ hệ thống không thể thấy được và sẽ không sử dụng index này

alter table customers alter index idx_namsinh invisible;

Partition

• Dung lượng 2 GB (hoặc 10 triệu bản ghi) trở lên thì nên tạo Partition
• Khi có partition thì lúc query sẽ tối ưu và nhanh hơn so với khi không có partition
• Lưu ý: partition cần dựa trên cột phù hợp sẽ có trong câu lệnh điều kiến where

Tối ưu tham số Database

Buffer Cache Hit

• Tỉ lệ hệ thống sử dụng Buffer Cache để tải dữ liệu thay vì phải xuống hệ thống vật lý (Innodb_buffer_pool_size)

• Tỉ lệ < 90% là cần tối ưu

• Dựa trên 2 thông số:

  • Innodb_buffer_pool_read_requests: Tổng số yêu cầu gửi tới Buffer Pool. Câu lệnh kiểm tra: show global status like '%Innodb_buffer_pool_read_requests%
  • Innodb_buffer_pool_reads: Tổng số yêu cầu phải xuống disk để lấy (không tìm thấy trong Buffer pool tại memory). Câu lệnh: show global status like '%Innodb_buffer_pool_reads%

• Công thức tính:

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

Table Cache Hit

• Trong MySQL có thể cache trước các bảng để lấy nhanh trong memory
• Dựa trên 2 thông số:
  • Open_tables: Tổng số table được open trong cache. Câu lệnh: show global status like 'Open_tables
  • Opened_tables: Tổng số table được open. Câu lệnh: show global status like 'Opened_tables
• Công thức: Open_tables/Opened_tables
• Tỉ lệ này < 80% thì cần tối ưu
• Hướng tối ưu: tối ưu tham số: Table_open_cache

Table Definition Cache Hit

• Khi truy vấn thì MySQL sẽ cần có các thông tin của bảng như là bảng gì, số cột như thế nào, sô hàng như thế nào. Thông tin này gọi là Table Definition
• Dựa trên 2 thông số:
  • Open_table_definitions: Tổng số table definition có trong cache. Câu lệnh: show global status like 'Open_table_definitions
  • Opened_tables: Tổng số table definition được yêu cầu. Câu lệnh: show global status like 'Opened_table_definitions
• Công thức: Open_table_definitions/Opened_table_definitions
• Tỉ lệ này < 80% thì cần tối ưu
• Hướng tối ưu: tối ưu tham số: Table__definition_cache

Thông số sử dụng Temporary Table trên Memory

• Các câu lệnh Order, Group By,... cần sử dụng một bảng tạm (temporary table)
• Dựa trên 2 thông số:
  • Created_tmp_disk_tables: Tổng số bảng temp được tạo ở trên Disk. Câu lệnh: show global status like '%Created_tmp_disk_tables%
  • Created_tmp_tables: Tổng số bảng temp được tạo. Câu lệnh: show global status like '%Created_tmp_tables%
• Công thức: (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables
• Tỉ lệ này dưới 80% là cần tối ưu
• Hướng tối ưu: Tối ưu chiến lược thực thi của câu lệnh SQL

Tổng kết

Trên đây là những kiến thức tổng quan về MySQL. MySQL là một hệ thống quản trị cơ sở dữ liệu quan hệ có chi phí thấp với nhiều tính năng và hiệu suất cao phù hợp áp dụng cho nhiều dự án và đang được sử dụng rất rộng rãi. MySQL còn rất nhiều kiến thức và tính năng hay và mình sẽ viết trong một bài viết khác ở tương lai.
Mình xin gửi lời cảm ơn đến anh Trần Quốc Huy vì đã có nhiều chia sẻ hay về kiến thức về database và MySQL. Mọi người có thể xem những chia sẻ rất hay và bổ ích của anh ở kênh này
Cảm ơn mọi người đã đọc bài viết này và hẹn gặp lại mọi người trong các bài viết khác.

Tài liệu tham khảo

• MySQL Documentation
• MySQL Course - Trần Quốc Huy
• Wikipedia - MySQL