Hướng dẫn mysql optimize query large table - mysql tối ưu hóa truy vấn bảng lớn
Tôi đã cố gắng để có được một số cải tiến tốc độ trên một truy vấn được chọn nhất định. Tình huống như sau: Có một (trong mắt tôi) bàn giao nhau lớn. Nó hiện có khoảng 20 triệu hàng, nhưng tôi hy vọng điều này sẽ phát triển rất nhiều. Dựa trên bảng giao nhau này, tôi cần tạo một bảng khác. Để làm như vậy, tôi cần thực hiện truy vấn sau: Show
Bây giờ điều này hiện đang dẫn đến khoảng một triệu hàng. Tôi đã có các chỉ mục trên cả trường1 trong 2 bảng, nhưng vẫn mất 18 phút để hoàn thành .. Tôi đã nghĩ về việc tách bảng, nhưng sau đó tôi cần tìm cách phân chia dữ liệu và vì nó Chỉ cần một bàn giao nhau không có gì xuất hiện trong tâm trí về cách làm điều này. Bất kỳ ý tưởng về cách này có thể được tối ưu hóa? Thanks. Theo yêu cầu Đây là câu lệnh tạo:
Và đầu ra giải thích:
Đây không phải là một số lượng lớn các hàng. Điều đầu tiên cần làm là điều tra khóa. Vì vậy, bạn có thể chạy truy vấn trong một thiết bị đầu cuối, và sau đó trong một thiết bị đầu cuối khác, thực hiện một danh sách quy trình đầy đủ và xem những gì bạn nhận được. Nếu nhiều truy vấn đồng thời đang chờ khóa trên cùng một bảng, đó là một vấn đề. Nếu loại bảng là myisam, hãy ghi lại các lần đọc, vì vậy một truy vấn dài trên bảng Myisam có thể khóa tất cả các kết nối khác và làm cho máy chủ web của bạn hết các quy trình. Trong mọi trường hợp, nếu bạn định giá dữ liệu, nên chuyển bảng sang Innodb. Giả sử không có vấn đề về khóa, điều tiếp theo cần xem xét là kích thước thực tế của bảng. Bạn có thể sử dụng phpmyadmin hoặc chỉ truy vấn nó. Tôi đang nghi ngờ về cột "mô tả" đó. Nếu nó nhỏ, được. Nhưng nếu bạn đặt trung bình 10 kb của văn bản vào đó, bảng hàng 7m của bạn sẽ sử dụng 70GB, điều đó có nghĩa là ngay cả khi số lượng hàng nhỏ, thì chắc chắn đó không phải là thứ bạn muốn truy cập mà không suy nghĩ cẩn thận về việc lập chỉ mục. Bạn có thể làm:
Để có được một ý tưởng về những gì trong đó, quá. Nếu, như tôi nghi ngờ, cột Mô tả sử dụng một lượng không gian khổng lồ, thì đó sẽ là một vấn đề. Nếu bạn chỉ muốn tìm kiếm trên các cột nhỏ (Tiêu đề, Vendorid, năm) thì bạn cần giữ dữ liệu đó trong bộ đệm để nó nhanh. Tuy nhiên, nếu mỗi hàng có một cột mô tả lớn, điều đó sẽ lấp đầy cách bộ nhớ cache của bạn nhanh hơn bạn muốn. Điều đó cũng làm tăng lượng IO cần thiết để tìm nạp các hàng và lọc chúng, làm cho các truy vấn của bạn chậm. Vì vậy, chiến lược phụ thuộc vào việc bạn sẽ sử dụng tìm kiếm toàn văn trên mô tả hay không. Nếu bạn làm như vậy, thì tìm kiếm toàn văn MySQL rất có thể sẽ không phụ thuộc vào nhiệm vụ. Thay vào đó, sử dụng một cái gì đó như Sphinx hoặc Xapian. Nếu bạn không tìm kiếm trên mô tả, thì có khả năng bạn có thể sử dụng chỉ mục FullText MySQL trên cột Tiêu đề. Tuy nhiên, tôi không nghĩ MySQL có thể thực hiện chỉ mục FullText bao gồm và bao gồm cột năm trong đó, điều đó có nghĩa là chúng tôi đã trở lại vấn đề trước: Chỉ mục FullText sẽ tối ưu hóa các tìm kiếm trên tiêu đề, nhưng nếu có bất kỳ bộ lọc nào được thực hiện theo năm , sau đó nó sẽ phải nhấn bảng và làm tắc nghẽn bộ đệm với các mô tả của tất cả các hàng bạn không muốn. Vì vậy, nếu bạn không tìm kiếm trên mô tả, có một bản sửa lỗi đơn giản: Đặt cột Mô tả vào một bảng riêng biệt, được liên kết với một khóa ngoại. Điều này làm cho bảng (ID, Tiêu đề, Năm, Vendorid) nhỏ hơn nhiều và có thể phù hợp với bộ đệm, do đó sẽ ít gặp vấn đề hơn nếu chỉ mục FullText không thể cung cấp phạm vi bảo hiểm của các cột/Chuẩn. 13.7.3.4 & NBSP; Tối ưu hóa câu lệnh bảng
Sử dụng
Tuyên bố này yêu cầu các đặc quyền 5 và 3 cho bảng. 5 và 6. OPTIMIZE TABLE cũng được hỗ trợ cho các cột động của các bảng 2 trong bộ nhớ. Nó không hoạt động cho các cột có chiều rộng cố định của các bảng trong bộ nhớ, cũng không hoạt động cho các bảng dữ liệu đĩa. Hiệu suất của 3 trên các bảng cụm NDB có thể được điều chỉnh bằng cách sử dụng 4, điều khiển thời gian chờ đợi giữa các lô xử lý của hàng bằng OPTIMIZE TABLE . Để biết thêm thông tin, xem Phần & NBSP; 23.2.7.11, Các vấn đề cụm NDB trước đó được giải quyết trong cụm NDB 8.0.Đối với các bảng cụm NDB, 6 có thể bị gián đoạn bởi (ví dụ) tiêu diệt luồng SQL thực hiện hoạt động 3. Theo mặc định, 6 cho các công cụ lưu trữ khác bằng cách khởi động MySQLD với tùy chọn 0. Trong trường hợp này, OPTIMIZE TABLE chỉ được ánh xạ tới 2.not work for tables created using any other storage engine and returns a result indicating this lack of support. You can make 6 work for
other storage engines by starting mysqld with the 0 option. In this case, OPTIMIZE TABLE is just
mapped to 2. Tuyên bố này không hoạt động với quan điểm. Theo mặc định, máy chủ ghi các câu lệnh 6 vào nhật ký nhị phân để chúng sao chép thành bản sao. Để ngăn chặn ghi nhật ký, chỉ định từ khóa 5 tùy chọn hoặc bí danh của nó 6.
Tối ưu hóa đầu ra bảngChi tiết Innodb
2, 3, 4, 5 hoặc 6mysqld process, OPTIMIZE TABLE generates a "cannot change ownership of the file" error unless
mysqld is started by the OPTIMIZE TABLE 2 user. Chi tiết InnodbChi tiết myisam
Những ý kiến khác
8Tên bảng
Chi tiết myisamNhững ý kiến khác
Làm thế nào tôi có thể tăng tốc các truy vấn bàn lớn của mình?Công việc nổi bật trong lập trình.. Thay vì cập nhật, sử dụng trường hợp. .... Giảm quan điểm lồng nhau để giảm độ trễ. .... Dữ liệu trước khi tổ chức. .... Sử dụng bảng temp. .... Tránh sử dụng mã tái sử dụng. .... Tránh tìm kiếm tiêu cực. .... Tránh con trỏ. .... Chỉ sử dụng số lượng cột chính xác bạn cần .. MySQL có thể xử lý 100 triệu hồ sơ không?MySQL có thể xử lý 100 triệu hồ sơ không?Vâng, nó có thể xử lý hàng tỷ hồ sơ.Nếu bạn lập chỉ mục đúng các bảng, chúng phù hợp với bộ nhớ và các truy vấn của bạn được viết đúng thì nó không phải là một vấn đề.Yeah, it can handle billions of records. If you properly index tables, they fit in memory and your queries are written properly then it shouldn't be an issue.
Kích thước bảng có ảnh hưởng đến tốc độ truy vấn không?Kích thước bảng: Nếu truy vấn của bạn đạt được một hoặc nhiều bảng với hàng triệu hàng trở lên, nó có thể ảnh hưởng đến hiệu suất.Tham gia: Nếu truy vấn của bạn tham gia hai bảng theo cách tăng đáng kể số lượng hàng của tập kết quả, truy vấn của bạn có thể sẽ chậm.If your query hits one or more tables with millions of rows or more, it could affect performance. Joins: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow.
Tối ưu hóa bảng khóa bảng có?Tối ưu hóa bảng được thực hiện trực tuyến cho các bảng InnoDB thường xuyên và được phân vùng.Mặt khác, MySQL khóa bảng trong bảng tối ưu hóa thời gian đang chạy.MySQL locks the table during the time OPTIMIZE TABLE is running. |