VietNetwork.Vn

1. Giới thiệu.

Các bạn có thể triển khai cơ sở dữ liệu MySQL nhanh, an toàn và đáng tin cậy trên Alibaba Cloud. Alibaba có một mạng lưới công nghệ dựa trên công nghệ điện toán đám mây tiên tiến và hiệu suất vượt trội và thanh toán linh hoạt của họ đã cho phép đám mây không biên giới cho hơn một triệu khách hàng.

Alibaba Cloud đã tiếp tục cho thấy sự đóng góp to lớn cho các cộng đồng mã nguồn mở và đã trao quyền cho các nhà phát triển trên toàn thế giới. Alibaba Cloud là người chiến thắng Giải thưởng cộng tác viên MySQL uy tín 2018 và cũng là nhà tài trợ bạch kim của nền tảng MariaDB.

Trong hướng dẫn này, mìn sẽ đưa bạn qua các bước tối ưu hóa các truy vấn và cơ sở dữ liệu SQL trên phiên bản dịch vụ tính toán trên đám mây (ECS) của Alibaba. Điều này sẽ đảm bảo tính ổn định, khả năng mở rộng, độ tin cậy và tốc độ của các ứng dụng và trang web đang chạy trên Alibaba Cloud của bạn.

2. Lập chỉ mục tất cả các cột được sử dụng trong 'where', 'order by' và 'group by'

Ngoài việc đảm bảo các record nhận dạng duy nhất, một chỉ mục cho phép máy chủ MySQL tìm nạp kết quả nhanh hơn từ cơ sở dữ liệu. Một chỉ mục cũng rất hữu ích khi sắp xếp các record.

• Các chỉ mục MySQL có thể chiếm nhiều dung lượng hơn và giảm hiệu suất khi chèn, xóa và cập nhật. Tuy nhiên, nếu bảng của bạn có nhiều hơn 10 hàng, chúng có thể giảm đáng kể thời gian thực hiện truy vấn.

• Luôn luôn kiểm tra các truy vấn MySQL với lượng dữ liệu mẫu trong trường hợp xấu nhất để có được một bức tranh rõ ràng hơn về cách truy vấn sẽ hoạt động trong môi trường sản phẩm thực tế.

Hãy xem xét trường hợp bạn đang chạy truy vấn SQL sau từ cơ sở dữ liệu có 500 hàng không có chỉ mục:

mysql> select customer_id, customer_name from customers where customer_id='140385';
Truy vấn trên sẽ buộc máy chủ MySQL tiến hành quét toàn bộ bảng (bắt đầu kết thúc) để truy xuất bản ghi mà chúng ta đang tìm kiếm.

May mắn thay, MySQL có một câu lệnh 'EXPLAIN' đặc biệt mà bạn có thể sử dụng cùng với việc chọn, xóa, chèn, thay thế và cập nhật các câu lệnh để phân tích các truy vấn của mình.

Khi bạn nối thêm truy vấn trước câu lệnh SQL, MySQL sẽ hiển thị thông tin từ chương trình tối ưu hóa về kế hoạch thực hiện dự định.

Nếu chúng ta chạy SQL ở trên một lần nữa với câu lệnh EXPLAIN, chúng ta sẽ có một bức tranh đầy đủ về những gì MySQL sẽ làm để thực hiện truy vấn:


Như bạn có thể thấy, chương trình tối ưu hóa đã hiển thị thông tin rất quan trọng có thể giúp chúng ta tinh chỉnh bảng cơ sở dữ liệu của chúng ta. Đầu tiên, rõ ràng rằng MySQL sẽ tiến hành quét toàn bộ bảng vì cột khóa là 'NULL'. Thứ hai, máy chủ MySQL đã chỉ rõ rằng nó sẽ tiến hành quét toàn bộ 500 hàng trong cơ sở dữ liệu của chúng tôi.

Để tối ưu hóa truy vấn trên, chúng ta chỉ cần thêm một chỉ mục vào trường ' customer_id ' bằng cú pháp dưới đây:

mysql> Create index customer_id ON customers (customer_Id);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0
Nếu chúng ta chạy câu lệnh EXPLAIN một lần nữa, chúng ta sẽ nhận được kết quả dưới đây:


Từ đầu ra giải thích ở trên, rõ ràng máy chủ MySQL sẽ sử dụng chỉ mục của chúng ta (customer_Id) để tìm kiếm bảng. Bạn có thể thấy rõ số lượng hàng cần quét sẽ là 1. Mặc dù tôi chạy truy vấn trên trong một bảng có 500 bản ghi, các chỉ mục có thể rất hữu ích khi bạn truy vấn một tập dữ liệu lớn (ví dụ: bảng có 1 triệu hàng).

3. Tối ưu hóa các câu lệnh với mệnh đề Union.

Đôi khi, bạn có thể muốn chạy truy vấn bằng toán tử so sánh 'or' trên các trường hoặc cột khác nhau trong một bảng cụ thể. Khi từ khóa 'or' được sử dụng quá nhiều trong mệnh đề where, nó có thể khiến chương trình tối ưu hóa MySQL chọn không chính xác một lần quét toàn bộ bảng để lấy bản ghi.


Mệnh đề Union có thể làm cho truy vấn chạy nhanh hơn, đặc biệt nếu bạn có một chỉ mục có thể tối ưu hóa một mặt của truy vấn và một chỉ mục khác để tối ưu hóa phía bên kia.

Ví dụ, hãy xem xét trường hợp bạn đang chạy truy vấn bên dưới với ' First_name ' và ' last_name ' được lập chỉ mục:

mysql> select * from students where first_name like  'Ade%'  or last_name like 'Ade%' ;
Truy vấn trên có thể chạy chậm hơn rất nhiều so với truy vấn bên dưới sử dụng toán tử Union kết hợp 2 kết quả của các truy vấn nhanh riêng biệt để tận dụng các chỉ mục.

mysql> select  from students where first_name like  'Ade%'  union all select  from students where last_name like  'Ade%' ;
4. Tránh các biểu thức với các ký tự đại diện hàng đầu.


MySQL không thể sử dụng các chỉ mục khi có một ký tự đại diện hàng đầu trong một truy vấn. Nếu chúng ta lấy ví dụ ở trên trên bảng students, một tìm kiếm như thế này sẽ khiến MySQL thực hiện quét toàn bộ bảng ngay cả khi bạn đã lập chỉ mục trường ' First_name ' trên bảng students.

mysql> select * from students where first_name like  '%Ade'  ;
Chúng ta có thể chứng minh điều này bằng cách sử dụng từ khóa explain:


Như bạn có thể thấy ở trên, MySQL sẽ quét tất cả 500 hàng trong bảng students của chúng ta và thực hiện sẽ khiến truy vấn trở nên cực kỳ chậm.

5. Tận dụng lợi thế của Full-Text Searches MySQL.

Nếu bạn gặp phải tình huống cần tìm kiếm dữ liệu bằng ký tự đại diện và bạn không muốn cơ sở dữ liệu của mình hoạt động kém, bạn nên cân nhắc sử dụng tìm Full-Text Searches MySQL vì nó nhanh hơn nhiều so với truy vấn sử dụng ký tự đại diện.


Hơn nữa, FTS cũng có thể mang lại kết quả tốt hơn và phù hợp hơn khi bạn đang tìm kiếm một cơ sở dữ liệu khổng lồ.

Để thêm chỉ mục tìm kiếm toàn văn vào bảng mẫu của students, chúng ta có thể sử dụng lệnh MySQL bên dưới:

mysql>Alter table students ADD FULLTEXT (first_name, last_name);

mysql>Select * from students where match(first_name, last_name) AGAINST ('Ade');
Trong ví dụ trên, chúng tôi đã chỉ định các cột mà chúng ta muốn khớp (First_name và last_name) theo từ khóa tìm kiếm của chúng ta ('Ade').

Nếu chúng ta truy vấn chương trình tối ưu hóa về kế hoạch thực hiện của truy vấn trên, chúng tôi sẽ nhận được các kết quả sau:


Rõ ràng là chỉ một hàng duy nhất sẽ được quét ngay cả khi cơ sở dữ liệu của students chúng ta có 500 hàng và điều này sẽ tăng tốc cơ sở dữ liệu.

6. Tối ưu hóa lược đồ cơ sở dữ liệu của bạn Database Schema.

Ngay cả khi bạn tối ưu hóa các truy vấn MySQL của mình và không đưa ra được cấu trúc cơ sở dữ liệu tốt, hiệu suất cơ sở dữ liệu của bạn vẫn có thể bị dừng khi dữ liệu của bạn tăng lên.

7. Bình thường hóa bảng.

Đầu tiên, bình thường hóa tất cả các bảng cơ sở dữ liệu ngay cả khi nó sẽ liên quan đến một số sự đánh đổi. Chẳng hạn, nếu bạn đang tạo hai bảng để giữ dữ liệu khách hàng và đơn hàng, bạn nên tham chiếu khách hàng trên bảng đơn hàng bằng cách sử dụng id khách hàng thay vì lặp lại tên của khách hàng trên bảng đơn hàng. Cái sau sẽ khiến cơ sở dữ liệu của bạn phình to.

Hình ảnh dưới đây đề cập đến một lược đồ cơ sở dữ liệu được thiết kế để thực hiện mà không có bất kỳ sự dư thừa dữ liệu nào. Trong chuẩn hóa cơ sở dữ liệu MySQL, bạn chỉ nên trình bày một thực tế một lần trong toàn bộ cơ sở dữ liệu. Đừng lặp lại tên khách hàng trong mỗi bảng; thay vào đó chỉ sử dụng customer_Idđể tham khảo trong các bảng khác.


Ngoài ra, luôn luôn sử dụng cùng một loại dữ liệu để lưu trữ các giá trị tương tự ngay cả khi chúng ở trên các bảng khác nhau, ví dụ, lược đồ ở trên sử dụng loại dữ liệu ' INT ' để lưu trữ ' customer_id ' cả trong bảng khách hàng và đơn hàng.

8. Sử dụng các kiểu dữ liệu tối ưu.

MySQL hỗ trợ các loại dữ liệu khác nhau bao gồm số nguyên, float, double, date, date_time, Varchar và văn bản, trong số các loại khác. Khi thiết kế bảng của bạn, bạn nên biết rằng "ngắn hơn luôn luôn tốt hơn".

Trong các trường hợp, nếu bạn đang thiết kế bảng người dùng hệ thống sẽ chứa ít hơn 100 người dùng, bạn nên sử dụng loại dữ liệu ' TINYINT ' cho trường ' user_id ' vì nó sẽ chứa tất cả các giá trị của bạn từ -128 đến 128.

Ngoài ra, nếu một trường mong đợi một giá trị ngày (ví dụ: sales_order_date), sử dụng kiểu dữ liệu date_time sẽ rất lý tưởng vì bạn không phải chạy các hàm phức tạp để chuyển đổi trường thành ngày khi lấy bản ghi bằng SQL.

Sử dụng các giá trị số nguyên nếu bạn kỳ vọng tất cả các giá trị là số (ví dụ: trong trường student_id hoặc trường Payment_id). Hãy nhớ rằng, khi nói đến tính toán, MySQL có thể làm tốt hơn với các giá trị nguyên so với các kiểu dữ liệu văn bản như Varchar.

9. Tránh các giá trị Null.

Null là sự vắng mặt của bất kỳ giá trị nào trong một cột. Bạn nên tránh loại giá trị này bất cứ khi nào có thể vì chúng có thể gây hại cho kết quả cơ sở dữ liệu của bạn. Chẳng hạn, nếu bạn muốn lấy tổng của tất cả các đơn hàng trong cơ sở dữ liệu nhưng một bản ghi đơn hàng cụ thể có số tiền không có giá trị, kết quả dự kiến có thể hoạt động sai trừ khi bạn sử dụng câu lệnh ' ifnull ' của MySQL để trả về giá trị thay thế nếu bản ghi là null.

Trong một số trường hợp, bạn có thể cần xác định giá trị mặc định cho trường nếu các bản ghi không phải bao gồm giá trị bắt buộc cho cột / trường cụ thể đó.

10. Tránh quá nhiều cột.

Các bảng rộng có thể cực kỳ tốn kém và cần nhiều thời gian CPU hơn để xử lý. Nếu có thể, đừng vượt quá một trăm trừ khi logic kinh doanh của bạn đặc biệt yêu cầu điều này.

Thay vì tạo một bảng rộng, hãy xem xét tách nó thành các cấu trúc logic. Ví dụ: nếu bạn đang tạo bảng khách hàng nhưng bạn nhận ra khách hàng có thể có nhiều địa chỉ, tốt hơn là tạo một bảng riêng để giữ địa chỉ khách hàng tham chiếu lại bảng khách hàng bằng trường ' customer_id '.

11. Tối ưu hóa Join.

Luôn bao gồm ít bảng hơn trong các statements tham gia của bạn. Một câu lệnh SQL với mẫu được thiết kế kém có nhiều liên kết có thể không hoạt động tốt. Một nguyên tắc nhỏ là có tối đa một Join cho mỗi truy vấn.

12. Bộ nhớ đệm truy vấn MySQL Query Caching.

Nếu trang web hoặc ứng dụng của bạn thực hiện nhiều truy vấn chọn lọc (ví dụ: WordPress), bạn nên tận dụng tính năng Query Caching của MySQL. Điều này sẽ tăng tốc hiệu suất khi các hoạt động đọc được tiến hành.

Công nghệ hoạt động bằng cách Query Caching cùng với tập dữ liệu kết quả. Điều này làm cho truy vấn chạy nhanh hơn vì chúng được tìm nạp từ bộ nhớ nếu chúng được thực thi nhiều lần. Tuy nhiên, nếu ứng dụng của bạn cập nhật bảng thường xuyên, điều này sẽ làm mất hiệu lực mọi truy vấn và kết quả được lưu trong bộ nhớ cache.

Bạn có thể kiểm tra xem máy chủ MySQL của bạn có bật Query Caching hay không bằng cách chạy lệnh bên dưới:


Cấu hình Query Caching MySQL.

Bạn có thể đặt các giá trị bộ đệm truy vấn MySQL bằng cách chỉnh sửa tập tin cấu hình (' /etc/mysql/my.cnf ' hoặc ' /etc/mysql/mysql.conf.d/mysqld.cnf '). Điều này sẽ phụ thuộc vào cài đặt MySQL của bạn. Không đặt giá trị kích thước bộ đệm truy vấn rất lớn bởi vì điều này sẽ làm giảm máy chủ MySQL do quá tải và khóa bộ nhớ cache. Các giá trị trong phạm vi hàng chục megabyte được khuyến nghị.

Để kiểm tra giá trị hiện tại, sử dụng lệnh dưới đây:
Sau đó, để điều chỉnh các giá trị, bao gồm các mục sau trên tập cấu hình MySQL:

query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k
Bạn có thể điều chỉnh các giá trị trên theo nhu cầu máy chủ của bạn.

' query_cache_type = 1 ' sẽ bật bộ đệm MySQL nếu nó bị tắt theo mặc định.

' Query_cache_size ' mặc định là 1 MB và như chúng ta đã nói ở trên, giá trị khoảng 10 MB được khuyến nghị. Ngoài ra, giá trị phải trên 40 KB nếu không máy chủ MySQL sẽ đưa ra cảnh báo, "Query cache failed to set size"

' Query_cache_limit ' mặc định cũng là 1MB. Giá trị này kiểm soát số lượng kết quả truy vấn riêng lẻ có thể được cache.

Trong hướng dẫn này, chúng tôi đã chỉ cho bạn cách tối ưu hóa máy chủ MySQL của bạn được lưu trữ trên đám mây của Alibaba về tốc độ và hiệu suất.

Chúng tôi tin rằng hướng dẫn sẽ cho phép bạn tạo các truy vấn tốt hơn và có cơ sở dữ liệu có cấu trúc tốt, không chỉ đơn giản để duy trì mà còn mang lại sự ổn định hơn cho các ứng dụng phần mềm hoặc trang web của bạn.