Câu hỏi Tối ưu hóa truy vấn cho bảng với hàng trăm triệu hàng


điều này giống như một câu hỏi "làm bài tập về nhà cho tôi" nhưng tôi thực sự bị kẹt ở đây cố gắng làm cho truy vấn này chạy nhanh chóng với một bảng có nhiều hàng. Đây là một SQLFiddle cho thấy lược đồ (nhiều hơn hoặc ít hơn).

Tôi đã chơi với các chỉ mục, cố gắng để có được một cái gì đó sẽ hiển thị tất cả các cột cần thiết nhưng không có nhiều thành công. Đây là create:

CREATE TABLE `AuditEvent` (
    `auditEventId` bigint(20) NOT NULL AUTO_INCREMENT,
    `eventTime` datetime NOT NULL,
    `target1Id` int(11) DEFAULT NULL,
    `target1Name` varchar(100) DEFAULT NULL,
    `target2Id` int(11) DEFAULT NULL,
    `target2Name` varchar(100) DEFAULT NULL,
    `clientId` int(11) NOT NULL DEFAULT '1',
    `type` int(11) not null,
    PRIMARY KEY (`auditEventId`),
    KEY `Transactions` (`clientId`,`eventTime`,`target1Id`,`type`),
    KEY `TransactionsJoin` (`auditEventId`, `clientId`,`eventTime`,`target1Id`,`type`)
)

Và (phiên bản) select:

select ae.target1Id, ae.type, count(*)
from AuditEvent ae
where ae.clientId=4
    and (ae.eventTime between '2011-09-01 03:00:00' and '2012-09-30 23:57:00')
group by ae.target1Id, ae.type;

Tôi kết thúc với một 'Sử dụng tạm thời' và 'Sử dụng filesort' là tốt. Tôi đã thử bỏ count(*) và sử dụng select distinct thay vào đó, điều này không gây ra 'Sử dụng filesort'. Điều này có lẽ sẽ ổn nếu có một cách để join trở lại để có được số lượng.

Ban đầu, quyết định được thực hiện để theo dõi target1Name và target2Name của các mục tiêu khi chúng tồn tại khi bản ghi kiểm toán được tạo ra. Tôi cũng cần những cái tên đó (cái mới nhất sẽ làm).

Hiện tại truy vấn (ở trên, thiếu cột target1Name và target2Name) chạy trong khoảng 5 giây trên ~ 24 triệu bản ghi. Mục tiêu của chúng tôi là trong hàng trăm triệu và chúng tôi muốn truy vấn tiếp tục thực hiện theo các dòng đó (hy vọng giữ nó dưới 1-2 phút, nhưng chúng tôi muốn có nó tốt hơn nhiều), nhưng nỗi sợ của tôi là một lần chúng tôi nhấn số lượng dữ liệu lớn hơn mà nó sẽ không (làm việc để mô phỏng các hàng bổ sung đang được tiến hành).

Tôi không chắc chắn về chiến lược tốt nhất để có được các trường bổ sung. Nếu tôi thêm các cột thẳng vào select Tôi mất 'Sử dụng chỉ mục' trên truy vấn. Tôi đã thử join trở lại bảng, giữ 'Sử dụng chỉ mục' nhưng mất khoảng 20 giây.

Tôi đã cố gắng thay đổi cột eventTime thành một int chứ không phải là một datetime nhưng điều đó dường như không ảnh hưởng đến việc sử dụng chỉ mục hoặc thời gian.


8
2017-10-23 13:22


gốc


Thời gian truy vấn hiện tại của bạn là gì và bạn hiểu gì dưới "nhanh"? - feeela
Rất tiếc, đã thêm các chi tiết đó - Nick Spacek
Bạn có các chỉ mục trên clientId và eventTime không? Cũng xác minh rằng nếu bạn có những người mà bạn đang sử dụng chỉ số eventTime và không thực hiện quét toàn bộ bảng. - Natan Cox
Làm thế nào đến không ai đề cập đến công cụ lưu trữ được sử dụng và phần cứng đằng sau tất cả mọi thứ? Chỉ có rất nhiều người có thể làm bằng cách thiết lập mọi chỉ số bên phải, phần còn lại là phần cứng. Và không sử dụng InnoDB với buffer_pool lớn có nghĩa là rất nhiều đĩa IO và trên một ổ đĩa cơ học với ~ 400 IOPS - tất nhiên hiệu suất trên một hàng nhiều triệu sẽ là khủng khiếp. - N.B.
@NickSpacek - đây là một số đọc thú vị, nhưng chủ yếu là những gì bạn muốn làm là tăng biến được gọi là innodb_buffer_pool - Tôi thường có khoảng 90% RAM có sẵn. Một điều nữa là bạn nên có hệ thống con đĩa nhanh có khả năng hơn 500 IOPS (SSD tiêu diệt các ổ đĩa cơ học ở đó, dao động từ 40k IOPS trở lên). - N.B.


Các câu trả lời:


Như bạn có thể hiểu, vấn đề ở đây là điều kiện phạm vi ae.eventTime between '2011-09-01 03:00:00' and '2012-09-30 23:57:00' mà (như thường lệ) phá vỡ hiệu quả sử dụng Transactions chỉ mục (đó là chỉ mục thực sự chỉ được sử dụng cho clientId phương trình và phần đầu của điều kiện phạm vi và chỉ mục không được sử dụng để nhóm).

Thông thường, giải pháp là thay thế điều kiện phạm vi bằng kiểm tra bình đẳng (trong trường hợp của bạn, hãy giới thiệu period cột, nhóm eventTime vào các giai đoạn và thay thế BETWEEN mệnh đề với một period IN (1,2,3,4,5)). Nhưng điều này có thể trở thành một chi phí cho bảng của bạn.

Một giải pháp khác mà bạn có thể thử là thêm một chỉ mục khác (có thể thay thế Transactions nếu nó không được sử dụng nữa): (clientId, target1Id, type, eventTime)và sử dụng truy vấn sau:

SELECT
  ae.target1Id,
  ae.type,
  COUNT(
    NULLIF(ae.eventTime BETWEEN '2011-09-01 03:00:00' 
                            AND '2012-09-30 23:57:00', 0)
  ) as cnt,
FROM AuditEvent ae
WHERE ae.clientId=4
GROUP BY ae.target1Id, ae.type;

Bằng cách đó, bạn sẽ a) di chuyển điều kiện phạm vi đến cuối, b) cho phép sử dụng chỉ mục cho nhóm, c) tạo chỉ mục bao gồm chỉ mục cho truy vấn (đó là truy vấn không cần thao tác đĩa IO)

UPD1: Tôi xin lỗi, yesteday tôi đã không đọc kỹ bài đăng của bạn và không nhận thấy rằng vấn đề của bạn là truy xuất target1Name và target2Name. Trước hết, tôi không chắc chắn rằng bạn hiểu chính xác ý nghĩa của Using index. Sự thiếu vắng của Using index không có nghĩa là không có chỉ mục nào được sử dụng cho truy vấn, Using indexcó nghĩa là chính chỉ mục chứa đủ dữ liệu để thực thi truy vấn phụ (đó là chỉ mục được bao gồm). Vì target1Name và target2Name không được bao gồm trong bất kỳ chỉ mục nào, truy vấn phụ tìm nạp chúng mà không có Using index.

Nếu bạn đặt câu hỏi chỉ là cách thêm hai trường đó vào truy vấn của bạn (mà bạn xem xét đủ nhanh), thì hãy thử làm như sau:

SELECT a1.target1Id, a1.type, cnt, target1Name, target2Name
FROM (
  select ae.target1Id, ae.type, count(*) as cnt, MAX(auditEventId) as max_id
  from AuditEvent ae
  where ae.clientId=4
      and (ae.eventTime between '2011-09-01 03:00:00' and '2012-09-30 23:57:00')
  group by ae.target1Id, ae.type) as a1
JOIN AuditEvent a2 ON a1.max_id = a2.auditEventId
;

4
2017-10-23 19:22



Cả hai đều là câu trả lời hợp lệ thực sự; Tôi đang tìm cách tăng hiệu suất truy vấn / nhận lời khuyên về các cách khác nhau để cấu trúc nó, và ngoài ra tôi đã tự hỏi cách tốt nhất để truy xuất các cột không được lập chỉ mục. Cả hai đề xuất của bạn đã cải thiện hiệu suất so với các truy vấn tôi đã thử! - Nick Spacek
@nickSpacek, ok, tôi rất vui vì nó đã giúp =) - newtover