Запрос таблицы из 10 миллионов строк - оптимизация производительности запроса MySQL на таблице ⇐ MySql

Форум по Mysql
Ответить Пред. темаСлед. тема
Anonymous
 Запрос таблицы из 10 миллионов строк - оптимизация производительности запроса MySQL на таблице

Сообщение Anonymous »

В настоящее время у меня есть таблица транзакций со следующей схемой: < /p>

Код: Выделить всё

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
< /code>
Это было создано с использованием Prisma.  < /p>
Я пытаюсь выполнить этот запрос: < /p>
  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 = ''
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;
< /code>
Текущий Объяснение: < /p>
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY   NULL    ref   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

Текущие индексы: < / p > < b r / > < d i v c l a s s = " S - t a b l e - c o n t a i n e r " > < t a b l e c l a s s = " s - t a b l e " > < b r / > < t h e a d > < b r / > < t r > < b r / > < t h > t a b l e < / t h > < b r / > < t h > N o n _ u n i q u e < / t h > < b r / > < t h > k e y _ n a m e < / t h > < b r / > < t h > s e q _ i n _ i n d e x < / t h > < b r / > < t h > c o l u m n _ n a m e < / t h > < b r / > < T h > c o l l a t i o n < / t h > < b r / > < t h > c a r d i n a l i t y < / t h > < b r / > < t h > s u b _ p a r t < / t h > < b r / > < t h > у п а к о в а н < / t h > < b r / > < t h > n u l l < / t h > < b r / > < t h > i n d e x _ t y p e < / t h > < b r / > < t h > К о м м е н т а р и й < / t h > < b r / > < t h > i n d e x _ c o m m e n t < / t h > < b r / > < t h > v i s i b l e < / t h > < B r / > < T h > в ы р а ж е н и е < / t h > < b r / > < / t r > < b r / > < / t h e a d > < b r / > < t b o d y > < b r / > < t r > < b r / > < t d > т р а н з а к ц и я < / t d > < b r / > < t d > 0 < / t d > < b r / > < t d > п е р в и ч н ы й < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n I d < / t d > < b r / > < t d > a < / t d > < b r / > < t d > 7 7 2 1 1 0 7 < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > n u l l < / t d > < t d > < / t d > < b r / > < t d > b t r e e < / t d > < b r / > < t d > < / t d > < b r / > < t d > < / t d > < b r / > < t d > Д а < / t d > < b r / > < t d > n u l l < / t d > < b r / > < / t r > < b r / > < t r > < b r / > < t d > т р а н з а к ц и я < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n _ c u s t o m e r i d _ i d x < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > c u s t o m e r i d < / t d > < b r / > < t d > < / T d > < b r / > < t d > 5 1 8 2 9 < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > < / / t d > < b r / > < t d > b t r e e < / t d > < b r / > < t d > < / t d > < b r / > < t d > < / t d > < b r / > < t d > д а < / t d > < b r / > < T d > n u l l < / t d > < b r / > < / t r > < b r / > < t r > < b r / > < t d > т р а н з а к ц и я < / t d > < b r / > < t d > 1 < / t d > < t d > t r a n s a c t i o n _ c a r d r e f e r e n c e i d _ i d x < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > c a r d r e f e r e n c e I d < / t d > < b r / > < t d > < / t d > < b r / > < T d > 9 2 0 8 0 < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > < / t d > < b r / > < t d > B t r e e < / t d > < b r / > < t d > < / t d > < b r / > < t d > < / t d > < b r / > < t d > Д а < / t d > < b r / > < t d > n u l l < / t d > < b r / > < / t r > < b r / > < t r > < b r / > < t d > т р а н з а к ц и я < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n _ t r a n s a c t i o n t y p e _ i d x < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n t y p e < / t d > < b r / > < t d > a < / t d > < b r / > < t d > 6 6 6 0 < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > < / t d > < b r / > < t d > b t r e e < / t d > < b r / > < T d > < / t d > < b r / > < t d > < / t d > < b r / > < t d > д а < / t d > < b r / > < t d > n u l l < / t d > < b r / > < / t r > < b r / > < t r > < b r / > < t d > т р а н з а к ц и я < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n _ t r a n s a c t i o n d a t e _ i d x < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > T r a n s a c t i o n D a t e < / t d > < b r / > < t d > a < / t d > < b r / > < t d > 5 8 8 5 2 2 6 < / t d > < b r / > < t d > N u l l < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > < / t d > < b r / > < t d > b t r e e < / t d > < b r / > < t d > < / t d > < b r / > < t d > < / t d > < b r / > < t d > д а < / t d > < b r / > < t d > n u l l < / t d > < b r / > < / t r > < b r / > < t r > < b r / > < t d > т р а н з а к ц и я < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n _ m e r c h a n t n a m e _ i d x < / t d > < b r / > < t d > 1 < / t d > < t d > M e r c h a n t N a m e < / t d > < b r / > < t d > a < / t d > < b r / > < t d > 1 1 3 0 6 9 < / t d > < b r / > < t d > n u l l < / t d > < b r / > < T d > n u l l < / t d > < b r / > < t d > д а < / t d > < b r / > < t d > b t r e e < / t d > < b r / > < t d > < / t d > < b r / > < t d > < / t d > < b r / > < t d > Д а < / t d > < b r / > < t d > n u l l < / t d > < b r / > < / t r > < b r / > < t r > < b r / > < t d > Т р а н з а к ц и я < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > t r a n s a c t i o n _ a m o u n t i n c e n t s _ i d x < / t d > < b r / > < t d > 1 < / t d > < b r / > < t d > К о л и ч е с т в о < / t d > < b r / > < t d > a < / t d > < b r / > < t d > 1 7 5 2 0 0 < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > n u l l < / t d > < b r / > < t d > < / t d > < b r / > < t d > b t r e e < / t d > < b r / > < t d > < / t d > < b r / > < t d > < / t d > < b r / > < T d > д а < / t d > < b r / > < t d > null < /td>
< /tr>

транзакция < /td>
1 < /td>
transaction_origintransactiondate_idx < /td>
1 < /td>
OriginTransactionDate < /td>
a < /td>
5414580 < /td>
null < /td>
null < /td>
Да
btree


Да
null < /td>
< /tr>

транзакция < /td>
1 < /td>
transaction_serviceid_idx < /td>
1 < /td>
serviceId < /td>
a < /td >
5132 < /td>
null < /td>
null < /td>
Да < /td> btree < /td>
< /td>
< /td>
да < /td>
td> null < /td>
< /tr>

транзакция < /td>
1 < /td>
transaction_fraudparameterid_idx < /td>
1 < /td>
frudparameterid < /td>
a < /td>
1408 < /td>
null < /td>
null < /td>
да < /td>
btree


Да
null
< /tr>

транзакция < /td>
1 < /td>
transaction_rertievalerenceno_idx < /td >
1 < /td>
rertievalreferenceno < /td>
a < /td>
1 < /td> null < /td>
null < /td>
Да < /td>
btree < /td>


да
null


транзакция < /td>
1 < /td>
transaction_merchantcode_idx < /td>
1 < /td>
MerchantCode < /td>
a < /td>
703 < /td>
null
null
да
btree

< /td>
да < /td>
null < /td>
< /tr>

транзакция < /td>
1 < /td>
transaction_retrievalreferenceno_idx < /td>
1 < /td> retievalreferenceno < /td>
a < /td>
1187740 < /td>
null < /td>
null < /td>
да < /td>
btree < /td>
< /td>
< /td>
Да < /td>
null < /td>
< /tr>

Транзакция < /td>
1 < /td>
transaction_transactiondate_transactiontype_idx < /td>
1 < /td>
TransactionDate
a
64646660
null
null
< /td>
btree < /td>
< /td>
< /td>
да < /td>
null < /td>
< /tr>

транзакция < /td>
1 < /td>
transaction_transactionDate_transactiontype_idx < /td>
2 < /td>
TransactionType < /td>
a < /td>
7721107 < /td>
null < /td>
null < /td>

btree


Да
null < /td>
< /tr>

транзакция < /td>
1 < / td>
transaction_transactiontype_messagetype_idx < /td>
1 < /td>
transactiontype < /td>
< /td>
4773 < /td>
null < /td>
null < /td>
< /td>
btree < /td>
< /td>
< /td>
да < /td>
Null < /td>
< /tr>

транзакция < /td>
1 < /td>
Transaction_transactiontype_messagetype_idx < /td>
2 < /td>
messageType < /td>
a < /td>
5875
null
null

btree
< /td>
< /td>
Да < /td>
null < /td>
< /tr>

транзакция < /td>
1 < /td>
idx_transaction_customerid_transactiontype < /td>
1 < /td>
customerId < /td>
a < /td>
62298 < /td>
null < /td>
null < /td>
< /td>
btree < /td>
< /td>
< /td>
да < /td>
null < /td>
< /tr>

транзакция < /td>
1 < /td>
idx_transaction_customerid_transactiontype < /td>
2 < / td>
transactionType < /td>
a < /td>
336677 < /td>
null < /td>
null < /td>
< /td>
btree < /td>
< /td>
< /td>
да < /td>
null < /td>
< /tr>
< /tbody>
< /table> < /div>
Что я хочу: < /p>

[*] Потяните транзакции клиента менее чем за 100 мс из данный диапазон дат. Даже когда я фильтрую за последние 30 дней, я получаю 519 мс. >
< /ul>
Что я сделал Производительность. < /li>
Я создал новое поле, называемое TransactionDateonly с полем даты и индексом. < /li>
< /ol>
Индексы. Я попробовал: < /p>

Покрытый индекс (TransactionDateOnly, CustomerId, TransactionId, TransactionType) < /li>
Индекс TransactionType и TransactionDate < /li >
Index TransactionType и TransactionDateOnly < /li>
Покрытый индекс № 2 (TransactionDateonly, CustomerID, TransactionId, TransactionType, описание (10)) < /li>
< /ul>
И все они дают мне худшую производительность (MySQL выполняет полное сканирование таблицы) < /p>
Другие комментарии: < /p>
  • Я знаю, что типы данных неэффективны, так как многие из них не нужны эти длины варчара (следовательно, заставляя индекс превышать его предел) < /li>
    База данных - это размещено в Planetscale.
Есть идеи?

Подробнее здесь: https://stackoverflow.com/questions/794 ... ce-on-tabl
Вернуться к началу
Реклама
Ответить Пред. темаСлед. тема

Быстрый ответ

Изменение регистра текста: 
Смайлики
:) :( :oops: :roll: :wink: :muza: :clever: :sorry: :angel: :read: *x)
Ещё смайлики…
   
К этому ответу прикреплено по крайней мере одно вложение.

Если вы не хотите добавлять вложения, оставьте поля пустыми.

Максимально разрешённый размер вложения: 15 МБ.

  • Похожие темы
    Ответы
    Просмотры
    Последнее сообщение

Вернуться в «MySql»