Lời nói đầu

MySQL là một hệ quản trị cơ sở dữ liệu quan hệ phổ biến nhất thế giới, nhưng vẫn chưa được tối ưu hoá - nhiều người bỏ mặc nó theo giá trị mặc định mà không mở rộng nghiên cứu thêm nữa. Trong bài này, chúng tôi sẽ giới thiệu cho các bạn một số mẹo tối ưu hoá MySQL mà chúng ta đã đề cập trước đó, và kết hợp chúng với những điều mới mẻ xuất hiện từ đó.

Nội dung

I: Tối ưu hóa Configuration

Điều đầu tiên – và bị bỏ qua nhiều nhất – đó là việc nâng cấp hiệu suất mà sử dụng MySQL thông qua tùy chỉnh configuration. Với version 5.7 (phiên bản hiện tại) có mặc định tốt hơn nhiều so với các phiên bản trước đó nhưng vẫn có thể thực hiện các tùy chỉnh cái tiến một cách đơn giản

Giả sử bạn đang sử dụng một Linux-based host hoặc một Vagrant box tốt như Homestead Improved thì configuration file của bạn sẽ nằm trong /etc/mysql/my.cnf.

Bạn cũng có thể tạo ra các file cài đặt phụ vào bằng cách import các file đó vào file config chính my.cnf

Nếu tệp my.cnf không có nhiều nội dung, thì tệp /etc/mysql/mysql.conf.d/mysqld.cnf có thể tồn tại.

1: Editing Configuration

Nếu bạn đã làm việc nhiều với các dòng lệnh Mysql thì hãy cứ thoải mái khi sử dụng . Còn nếu bạn chưa bao giờ tiếp xúc với nó thì đây là lúc thích hợp.

Nếu bạn đang edit trực tiếp trên một Vagrant box, bạn có thể copy file vào main filesystem bằng cách copy file đó vào shared folder với lênh

cp /etc/mysql/my.cnf /  home /vagrant /Code

và edit nó bằng một text editor thông thường, sau đó copy nó trở lại vị trí khi hoàn thành. Nếu không có thể sử dụng một text editor đơn giản như vim bằng cách chạy sudo vim /etc/mysql/my.cnf.

Lưu ý: sửa đổi path ở trên để khớp với vị trí thực của config file - có thể là nó nằm ở /etc/mysql/mysql.conf.d/mysqld.cnf

2 : Manual Tweaks (Chỉnh sửa thủ công)

Các chính sửa thủ công sau đây nên được làm trong box. Theo những phương pháp này, hãy thêm vào config file dưới phần[mysqld] đoạn code sau:

innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size – buffer pool là vùng lưu trữ data và indexes trong bộ nhớ. Nó được sử dụng để lưu trữ dữ liệu thường xuyên truy cập trong bộ nhớ, và khi bạn đang chạy một máy chủ chuyên dụng hoặc máy ảo mà DB thường là bottleneck, và đây sẽ là phần chiếm nhiều ram nhất trong các ứng dụng mà bạn đang sử dụng. Do đó, chúng tôi cung cấp cho nó 50-70% của tất cả các RAM. Có một bộ tài liệu hướng dẫn định dạng butter pool có sẵn trong MySQL docs.
• The log file size cũng được giải thích ở đây nhưng tóm lại nó là việc bao nhiêu dữ liệu được lưu trữ trong một log trước khi xóa nó. Lưu ý rằng một log trong trường hợp này không phải là log một lỗi hoặc log một action của bạn àm log này nó cho thấy thời điểm checkpoint bởi vì với MySQL, writes xảy ra trong background nhưng vẫn ảnh hưởng đến performance. Big log files hoạt động tốt hơn vì có ít checkpoint mới và nhỏ hơn được tạo ra, nhưng thời gian phục hồi lâu hơn trong trường hợp bị lỗi (nhiều thứ cần phải được ghi lại vào DB).
• innodb_flush_log_at_trx_commit được giải thích ở đây và cho biết điều gì xảy ra với log file. Với giá trị là 1 chúng ta có thiết lập an toàn nhất, bởi vì log được flushing sang disk sau mỗi lần transaction. Với giá trị 0 hoặc 2 nó ít ACID hơn, nhưng nhiều performant hơn. Sự khác biệt trong trường hợp này là không đủ lớn để vượt qua các lợi ích ổn định của việc thiết lập của 1.
• innodb_flush_method - để hoàn thành công việc với flushing, điều này thiết lập O_DIRECT để tránh double-buffering. Việc này nên được thực hiện, trừ khi I/O system có hoạt động rất thấp. Trên hầu hết các hosted servers như DigitalOcean droplets bạn sẽ có SSDs, do đó I/O sẽ có hiệu suất cao.

3: Variable Inspector

Để cài đặt trên Ubuntu, bạn có thể chạy các lệnh như sau :

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

Với các hệ thống khác, xem tại hướng dẫn tại đây

Sau đó, chạy toolkit với:

pt-variable-advisor h=localhost,u=homestead,p=secret

Bạn sẽ thấy output không khác với dưới đây:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

Không gì trong số này là quan trọng, chúng không cần phải fix. Chỉ có một thứ chúng ta có thể thêm là binary logging cho replication và snapshot.

Note: kích thước binlog sẽ mặc định là 1G trong các phiên bản mới hơn và sẽ không được ghi lại bởi PT.

max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'

• Thiết lập max_binlog_size xác định các binary log sẽ lớn như thế nào. Đây là những log ghi lại transactions và queries của bạn và tạo ra checkpoints. Nếu một transaction lớn hơn max, thì một log có thể lớn hơn max khi lưu vào disk - nếu không, MySQL sẽ mặc định giữ chúng ở mức giới hạn đó.
• Tùy chọn bog_bin cho phép binary logging hoàn toàn. Không có nó thì không có snapshotting hoặc replication. Lưu ý rằng điều này có thể rất khó khăn trên không gian disk. Server ID là một tùy chọn cần thiết khi kích hoạt binary logging, do đó các log biết chúng đến từ server nào (để replication) và binlog-format chỉ là cách ghi log.

Như bạn thấy, MySQL bản mới có mặc định làm mọi thứ gần như được tạo sẵn. Tất nhiên, mỗi app là khác nhau và cũng cần có custom tweaks bổ sung thích hợp.

4: MySQL Tuner

Tuner sẽ theo dõi cơ sở dữ liệu trong khoảng thời gian dài (chạy nó mỗi tuần một lần hoặc lâu hơn trên một app trực tiếp) và đề xuất những thay đổi dựa trên những gì được thấy trong logs.

Cài đặt đơn giản bằng cách tải xuống:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

Chạy nó bằng ./mysqltuner.pl sẽ yêu cầu bạn nhập tên người dùng và mật khẩu quản trị viên cho cơ sở dữ liệu và đưa ra thông tin từ việc quét nhanh. Ví dụ :

[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/11.2M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 96.65% (19146 hits/ 19809 total)
[!!] InnoDB Write Log efficiency: 83.88% (640 hits/ 763 total)
[OK] InnoDB log waits: 0.00% (0 waits / 123 writes)

Một lần nữa, điều quan trọng cần lưu ý là công cụ này nên được chạy một lần mỗi tuần hoặc nhiều hơn khi server đã được chạy. Khi một config value bị thay đổi và server khởi động lại, nên chạy một tuần từ thời điểm đó. Bạn nên thiết lập một cronjob để làm điều này và gửi cho bạn kết quả theo định kỳ.

Hãy đảm bảo là bạn khởi động lại mysql server sau mỗi lần thay đổi configuration: sudo service mysql restart

II, Indexes

Tiếp theo, chúng ta hãy tập trung vào Indexes - điểm khó chính với nhiều quản trị viên . Đặc biệt là những người ngay lập tức nhảy vào ORM và chưa bao giờ thực sự tiếp xúc với SQL thô.

Lưu ý: terms keys và indexes có thể được sử dụng hoán đổi cho nhau.

Bạn có thể so sánh MySQL indexes với index trong một cuốn sách để thấy bạn dễ dàng tìm thấy trang chính xác có chứa chủ đề bạn đang tìm kiếm. Nếu không có bất kỳ index nào, bạn phải xem toàn bộ cuốn sách để tìm kiếm các trang có chứa chủ đề.

Như bạn có thể tưởng tượng, tìm kiếm bằng index sẽ nhanh hơn là phải đi qua từng trang. Do đó, thêm các index vào database nói chung làm tăng tốc select queries của bạn. Tuy nhiên, index cũng phải được tạo ra và lưu trữ. Vì vậy, khi tạo mới hay cập nhật thì quiries sẽ chậm hơn và nó sẽ tốn một chút không gian disk. Nói chung, bạn sẽ không nhận thấy sự khác biệt với updating và inserting nếu bạn đã lập index table của bạn chính xác và do vậy bạn nên thêm các index vào đúng vị trí.

Table mà chỉ chứa một vài record dữ liệu không thực sự thể hiện được lợi ích từ việc index. Bạn có thể tưởng tượng rằng việc tìm kiếm thông qua 5 record không chậm hơn nhiều với việc đầu tiên là đến index, nhận được số trang và sau đó mở trang cụ thể đó.

Vậy làm cách nào để chúng ta tìm ra các index nào cần thêm, và loại index nào tồn tại?

1 : Unique / Primary Indexes

Primary index là các index chính của data, đây là cách mặc định để xác định chúng. Đối với tài khoản người dùng, có thể là ID người dùng hoặc tên người dùng, thậm chí là một email chính. Primary index là duy nhất. Unique index là các index không thể lặp lại trong một bộ dữ liệu. Ví dụ: nếu người dùng đã chọn một username cụ thể, không ai khác có thể lấy nó. Thêm một index "unique" vào cột username sẽ giải quyết vấn đề này. MySQL sẽ báo cáo nếu có ai đó cố gắng update hoặc tạo mới một user có username đã tồn tại.

ALTER TABLE `users` 
ADD UNIQUE INDEX `username` (`username`);

Primary keys/indexes thường được xác định khi tạo bảng, và unique index được xác định sau fact bằng cách thay đổi bảng.

Cả primary keys và unique keys đều có thể được thực hiện trên một cột hoặc nhiều cột cùng một lúc. Ví dụ: nếu bạn muốn đảm bảo chỉ có một username trên một quốc gia có thể được xác định, bạn tạo một unique index trên cả hai cột đó, như sau:

ALTER TABLE `users`
ADD UNIQUE INDEX `usercountry` (`username`, `country`),

2: Regular Indexes

Các regular index dễ dàng tra cứu. Chúng rất hữu ích khi bạn cần tìm dữ liệu theo cột cụ thể hoặc kết hợp các cột một cách nhanh chóng, nhưng dữ liệu đó không cần phải là duy nhất.

ALTER TABLE `users`
ADD INDEX `usercountry` (`username`, `country`),

Index cũng giúp phân loại và phân nhóm tốc độ.

3: Fulltext Indexes

FULLTEXT index được sử dụng cho tìm kiếm full-text. Chỉ có các công cụ lưu trữ InnoDB và MyISAM hỗ trợ các FULLTEXT index và chỉ sử dụng được với các cột CHAR, VARCHAR, và TEXT.

Các index này rất hữu ích cho tất cả các tìm kiếm văn bản bạn có thể cần phải làm. Tìm các từ bên trong các phần của văn bản là đặc trưng của FULLTEXT. Sử dụng chúng trên các record dữ liệu lưu bài viết, nhận xét, mô tả, đánh giá, v.v ...

4 :Descending Indexes

Không phải là một loại index đặc biệt, mà là một sự thay đổi. Từ phiên bản 8+, MySQL hỗ trợ Descending index, có nghĩa là nó có thể lưu trữ các index trong descending order. Điều này có thể có ích khi bạn có bảng lớn mà thường xuyên cần dữ liệu ở cuối cùng được thêm vào đầu tiên, hoặc ưu tiên bắt đầu theo cách đó. Sắp xếp theo descending order là sẽ có hiệu xuất cao hơn. Điều này hơn nữa còn làm tăng tốc độ công việc.

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)

5 : Helper Tools: Explain

Khi xem xét việc tối ưu query, công cụ EXPLAIN sẽ là vô giá. Thêm vào đầu một query đơn giản với EXPLAIN sẽ xử lý nó một cách sâu sắc, phân tích các index đang sử dụng, và hiển thị cho bạn tỷ lệ truy cập và bỏ lỡ. Bạn sẽ nhận thấy có bao nhiêu record mà nó phải xử lý để có được kết quả bạn đang tìm kiếm.

EXPLAIN SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Bạn có thể hiểu 1 cách rõ ràng vào chi tiết hơn cách sử dụng EXPLAIN tại (đây)[https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/].

Nguồn

https://www.sitepoint.com/optimize-mysql-indexes-slow-queries-configuration