'select * from ALargeTable' thực thi rất chậm trong MySQL

Aug 25 2020

Tôi có một bảng 'giá':

CREATE TABLE `prices` (
    `dt` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `buy` double NOT NULL,
    `sell` double NOT NULL,
    PRIMARY KEY (`dt`),
    UNIQUE KEY `idx_dt_buy_sell` (`dt`,`buy`,`sell`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

với khoảng 22 triệu hàng.

một truy vấn đơn giản

select * from prices;

mất khoảng 1 phút để thực hiện.

MySQL làm được gì trong 1 phút? Nó xây dựng những cấu trúc dữ liệu nào? Có cách nào để tối ưu hóa điều này không?

Ví dụ một truy vấn

select * from prices limit 10;

thực thi ngay lập tức trong 0,00 giây.

Tôi đã chơi một chút với mức cô lập giao dịch, với các lệnh như

set SESSION transaction isolation level Read committed;
SELECT @@transaction_ISOLATION;

nhưng không thành công.

Phiên bản MySQL là 5.7.30

Trả lời

2 hsibboni Aug 25 2020 at 04:44

Điều gì đang xảy ra? Máy chủ MYSQL đang đọc dữ liệu từ đĩa của bạn và tải nó vào bộ nhớ (nếu nó chưa có trong bộ nhớ) và gửi dữ liệu đến máy khách MYSQL, người đang lưu trữ nó trong bộ nhớ, trước khi nhắc nó cho người dùng (bạn).

Nó xây dựng những cấu trúc dữ liệu nào? Tôi không biết, nhưng tôi không chắc liệu nó có thực sự quan trọng hay không.

Có cách nào để tối ưu hóa điều này không? Có, hãy đọc ít dữ liệu hơn hoặc kiểm tra cấu hình và phần cứng của bạn, (và nếu bạn gặp sự cố đồng thời, bạn có thể cần phải thay đổi công cụ).

đọc ít dữ liệu hơn: như bạn đã thấy, với một limit 10truy vấn chạy nhanh, có thể bạn không cần trả về 22 triệu cho mỗi truy vấn và bạn có thể thêm một WHEREmệnh đề.
kiểm tra phần cứng của bạn: dữ liệu có trên đĩa, đảm bảo bạn có đĩa nhanh (như SSD), cũng đảm bảo bạn có đủ bộ nhớ trên máy chủ
kiểm tra cấu hình của bạn: có những câu trả lời tốt hơn trên internet sẽ giải thích cách điều chỉnh cấu hình của bạn, nói một cách ngắn gọn (và không chính xác) bạn muốn tất cả dữ liệu của mình có thể được lưu trữ trong bộ nhớ, nếu bạn chỉ đọc dữ liệu, tôi sẽ khuyên khi xem câu trả lời này: https://dba.stackexchange.com/a/136409/119372: tăng của bạn key_buffer_sizeđể phù hợp với kích thước chỉ mục của bạn nếu nó chưa được thực hiện và lần lượt thử các đề xuất khác để xem liệu có hoặc tất cả có ảnh hưởng đến hiệu suất của bạn không
về đồng thời: dự đoán của tôi là bảng này chỉ được đọc, nếu bạn đang chèn / cập nhật dữ liệu trong khi truy vấn nó, MyIsam thực hiện khóa bảng, vì vậy bạn có thể thử InnoDB để tránh khóa bảng này

Một lưu ý nhỏ, như @Barmar đã chỉ ra rằng bạn đang kiểm tra máy chủ và máy khách cùng một lúc và bạn không cho biết liệu cả hai có đang chạy trên cùng một máy chủ hay không. Rất có thể là bạn, vì vậy bộ nhớ có thể được sử dụng bởi máy chủ MYSQL và ứng dụng khách MYSQL của bạn.