Tối ưu hóa tốc độ lấy mẫu dữ liệu

6.1 Giới thiệu

Bây giờ chúng ta hãy chuyển từ lý thuyết sang thực hành.

Chúng ta đang sống trong thế giới thực và tất cả các sản phẩm phần mềm cuối cùng đều được tạo ra cho những người đang sống. Và những người sống này rất khó chịu bởi các trang web tải chậm và các chương trình chạy chậm.

Và nếu một truy vấn cơ sở dữ liệu mất hơn một giây, điều này là không thể chấp nhận được . Người dùng đơn giản là sẽ không sử dụng một sản phẩm có các trang/chức năng quá chậm.

Nhưng thông thường, để hiển thị một trang, bạn cần thực hiện vài chục truy vấn tới cơ sở dữ liệu. Và nếu chúng được thực hiện tuần tự, thì bạn không còn giới hạn thứ hai nữa, mà giả sử là 100 mili giây cho mỗi yêu cầu.

Dưới đây là 5 cách hàng đầu giúp các lập trình viên tăng tốc truy vấn cơ sở dữ liệu:

1. Thêm chỉ mục vào các bảng trong cơ sở dữ liệu.
2. Viết lại và tối ưu hóa các truy vấn.
3. Bật (và định cấu hình) bộ nhớ đệm ở phía cơ sở dữ liệu.
4. Kích hoạt bộ nhớ đệm ở phía máy khách.
5. Thực hiện chuẩn hóa cơ sở dữ liệu.

Phần lớn bạn đã quen thuộc với tất cả những điều này, vì vậy những điều sau đây sẽ chỉ là lời khuyên thiết thực.

6.2 Chỉ số

Không có gì bí mật khi làm việc với cơ sở dữ liệu chiếm phần lớn công việc của hầu hết mọi trang web. Và nó đang làm việc với cơ sở dữ liệu thường là nút cổ chai nhất của các ứng dụng web.

Trong bài viết này, tôi muốn đưa ra lời khuyên thiết thực về việc sử dụng MySQL.

Tôi sẽ nói ngay:

• bài viết này được viết về MySQL, mặc dù những điều chung có thể đúng với bất kỳ DBMS nào.
• tất cả những gì viết trong bài là quan điểm cá nhân của tôi, và không phải là sự thật cuối cùng.
• lời khuyên không giả vờ là mới và là kết quả của sự khái quát hóa các tài liệu đã đọc và kinh nghiệm cá nhân.
• Trong khuôn khổ bài viết này, tôi sẽ không đề cập đến vấn đề cấu hình MySQL.

Các sự cố khi sử dụng MySQL có thể được chia thành 3 nhóm sau (xếp theo mức độ quan trọng):

1. Không sử dụng hoặc lạm dụng chỉ mục.
2. Cấu trúc cơ sở dữ liệu sai.
3. Các truy vấn SQL không chính xác \ dưới mức tối ưu.

Chúng ta hãy xem xét kỹ hơn từng nhóm này.

Sử dụng chỉ mục

Không sử dụng hoặc lạm dụng các chỉ mục là điều thường làm chậm các truy vấn nhất. Đối với những người không quen thuộc với cơ chế hoạt động của các chỉ mục hoặc chưa đọc về nó trong sách hướng dẫn, tôi thực sự khuyên bạn nên đọc nó.

Mẹo sử dụng chỉ mục:

• Bạn không cần lập chỉ mục mọi thứ . Khá thường xuyên, không hiểu ý nghĩa, mọi người chỉ cần lập chỉ mục tất cả các trường của bảng. Các chỉ mục tăng tốc độ tìm nạp, nhưng làm chậm quá trình chèn và cập nhật hàng, vì vậy việc lựa chọn từng chỉ mục phải có ý nghĩa.
• Một trong những tham số chính đặc trưng cho chỉ mục là tính chọn lọc, là số lượng các phần tử khác nhau trong chỉ mục. Sẽ vô nghĩa khi lập chỉ mục một trường có hai hoặc ba giá trị có thể. Sẽ có rất ít lợi ích từ một chỉ số như vậy.
• Việc lựa chọn các chỉ mục nên bắt đầu bằng việc phân tích tất cả các truy vấn đối với một bảng nhất định. Rất thường xuyên, sau khi phân tích như vậy, thay vì ba hoặc bốn chỉ số, bạn có thể tạo một chỉ số tổng hợp.
• Khi sử dụng các chỉ mục tổng hợp, thứ tự của các trường trong chỉ mục là rất quan trọng.
• Đừng quên về việc bao gồm các chỉ mục. Nếu tất cả dữ liệu trong một truy vấn có thể được truy xuất từ ​​một chỉ mục, thì MySQL sẽ không truy cập trực tiếp vào bảng. Những yêu cầu như vậy sẽ được thực hiện rất nhanh chóng. Ví dụ: đối với truy vấn SELECT name FROM user WHERE login='test'có chỉ mục (đăng nhập, tên), không cần truy cập vào bảng. Đôi khi, việc thêm một trường bổ sung vào một chỉ mục tổng hợp là điều hợp lý, điều này sẽ làm cho chỉ mục bao phủ và tăng tốc độ truy vấn.
• Đối với chỉ mục hàng, thường chỉ lập chỉ mục một phần của hàng là đủ. Điều này có thể làm giảm đáng kể kích thước chỉ mục.
• Nếu %là lúc bắt đầu, LIKE(SELECT * FROM table WHERE field LIKE '%test')các chỉ mục sẽ không được sử dụng.
• Chỉ mục FULLTEXT chỉ được sử dụng với cú pháp MATCH ... AGAINST .

6.3 Cấu trúc cơ sở dữ liệu

Cơ sở dữ liệu được thiết kế tốt là chìa khóa để làm việc nhanh chóng và hiệu quả với cơ sở dữ liệu. Mặt khác, một cơ sở dữ liệu được thiết kế kém luôn khiến các nhà phát triển đau đầu.

Mẹo thiết kế cơ sở dữ liệu:

1. Sử dụng các loại dữ liệu nhỏ nhất có thể. Kiểu dữ liệu càng lớn, bảng càng lớn thì càng cần nhiều truy cập đĩa để lấy dữ liệu. Sử dụng một thủ tục rất thuận tiện: SELECT * FROM table_name PROCEDURE ANALYSE();để xác định các loại dữ liệu tối thiểu có thể.
2. Quan sát các hình thức bình thường trong giai đoạn thiết kế. Thông thường, các lập trình viên sử dụng đến việc không chuẩn hóa ở giai đoạn này. Tuy nhiên, trong hầu hết các trường hợp, khi bắt đầu dự án, không rõ điều này có thể dẫn đến kết quả như thế nào. Việc không chuẩn hóa một bảng dễ dàng hơn nhiều so với việc phải chịu đựng một bảng không chuẩn hóa dưới mức tối ưu. Và JOINđôi khi nó hoạt động nhanh hơn các bảng không chuẩn hóa không chính xác.
3. Không sử dụng NULLcác cột trừ khi bạn thực sự cần chúng.

6.4 Truy vấn SQL.

Thông thường, có mong muốn viết lại tất cả các truy vấn trong SQL gốc để truy vấn nhanh nhất có thể. Nếu bạn quyết định làm điều này, thì đây là một số lời khuyên:

1. Tránh các yêu cầu trong một vòng lặp. SQL là ngôn ngữ của các tập hợp và việc viết các truy vấn không nên được tiếp cận bằng ngôn ngữ của các hàm mà bằng ngôn ngữ của các tập hợp.
2. Tránh *(dấu hoa thị) trong truy vấn. Vui lòng liệt kê chính xác các lĩnh vực bạn chọn. Điều này sẽ làm giảm lượng dữ liệu được tìm nạp và gửi đi. Ngoài ra, đừng quên bao gồm các chỉ mục. Ngay cả khi bạn chọn tất cả các trường trong bảng, tốt hơn hết bạn nên liệt kê chúng. Đầu tiên , nó cải thiện khả năng đọc mã. Khi sử dụng dấu hoa thị, không thể tìm ra những trường nào trong bảng mà không nhìn vào nó. Thứ hai , hôm nay bảng của bạn có năm cột INT và một tháng sau, một cột TEXT và BLOB khác đã được thêm vào và dấu hoa thị vẫn như cũ.
3. Khi được phân trang, để lấy tổng số bản ghi, hãy sử dụng SQL_CALC_FOUND_ROWSvà SELECT FOUND_ROWS();When used SQL_CALC_FOUND_ROWS MySQL, lưu vào bộ đệm số lượng hàng đã chọn (trước khi áp dụng LIMIT) và khi được sử dụng, SELECT FOUND_ROWS()chỉ trả về giá trị đã lưu trong bộ nhớ cache này mà không phải thực hiện lại truy vấn.
4. Đừng quên rằng INSERTcó một cú pháp cho nhiều lần chèn. Một truy vấn sẽ chạy nhanh hơn nhiều lần so với nhiều truy vấn trong một vòng lặp.
5. Sử dụng LIMITnơi bạn không cần tất cả dữ liệu.
6. Sử dụng INSERT… ON DUPLICATE KEY UPDATE…thay cho và INSERThoặc UPDATEsau lựa chọn, và thường thay cho REPLACE.
7. Đừng quên tính năng tuyệt vời này GROUP_CONCAT. Nó có thể trợ giúp với các truy vấn phức tạp.