sql - Querying a table of 10 Million Rows - Optimizing MySQL Query Performance on table - Stack Overflow

admin管理员组

文章数量:1300185

I currently have a transaction table with the following schema:

CREATE TABLE
    `Transaction` (
        `transactionId` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
            `transactionType` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
            `messageType` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
            `description` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            `customerId` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
            `meta` json NOT NULL,
            `transactionDate` datetime (3) NOT NULL,
            `amountInCents` int NOT NULL,
            `balanceInCents` int NOT NULL,
            `requestedAmountInCents` int NOT NULL,
            `requestedCurrencyCode` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
            `originTransactionDate` datetime (3) DEFAULT NULL,
            `serviceId` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            `retrievalReferenceNo` varchar(191) CHARACTER
        SET
            utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            PRIMARY KEY (`transactionId`),
            KEY `Transaction_customerId_idx` (`customerId`),
            KEY `Transaction_transactionType_idx` (`transactionType`),
            KEY `Transaction_transactionDate_idx` (`transactionDate`),
            KEY `Transaction_amountInCents_idx` (`amountInCents`),
            KEY `Transaction_originTransactionDate_idx` (`originTransactionDate`),
            KEY `Transaction_serviceId_idx` (`serviceId`),
            KEY `Transaction_retrievalReferenceNo_idx` (`retrievalReferenceNo`),
            KEY `Transaction_transactionDate_transactionType_idx` (`transactionDate`, `transactionType`),
            KEY `Transaction_transactionType_messageType_idx` (`transactionType`, `messageType`),
            KEY `idx_Transaction_customerId_transactionType` (`customerId`, `transactionType`)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci

This was created using Prisma.

I'm trying to perform this query:

  WITH RankedTransactions AS (
      SELECT
          t.*,
          ROW_NUMBER() OVER (
              PARTITION BY
                  CASE
                      WHEN t.transactionType = '00' THEN COALESCE(t.retrievalReferenceNo, t.transactionId)
                      ELSE t.transactionId
                  END
              ORDER BY
                  CASE
                      WHEN t.description LIKE 'Pre-Auth%' THEN 2
                      WHEN t.description LIKE '%Fee%' THEN 1
                      ELSE 0
                  END
          ) AS rn
      FROM Transaction t
      LEFT JOIN Bank b ON b.customerId = t.customerId
      WHERE b.userId = '<user-id>'
      AND t.transactionType IN ('00', '01', '09', '20', '21', '28', '40', '46', '47', 'DD', 'DB', 'PD', 'PV', 'Q1','W1', 'Q2', 'YK')
  )
  SELECT *
  FROM RankedTransactions
  WHERE rn = 1
  ORDER BY transactionDate DESC;

Current explain:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived2>  NULL    ref <auto_key0> <auto_key0> 8   const   10  100 Using filesort
2   DERIVED b   NULL    ref PRIMARY,Bank_customerId_key,Bank_userId_idx,Bank_customerId_idx,idx_Bank_userId_customerId  idx_Bank_userId_customerId  766 const   2   100 Using where; Using index; Using temporary; Using filesort
2   DERIVED t   NULL    ref Transaction_customerId_idx,Transaction_transactionType_idx,Transaction_transactionType_messageType_idx,idx_Transaction_customerId_transactionType   idx_Transaction_customerId_transactionType  766 b.customerId    123 42.83   Using index condition

Current indexes:

本文标签： sqlQuerying a table of 10 Million RowsOptimizing MySQL Query Performance on tableStack Overflow