Информация об изменениях

Сообщение Гигантская тормозная база, секционирование и ускорение от 18.01.2023 2:03

Изменено 18.01.2023 2:19 Слава

Старый текст
Новый текст
Разница

Гигантская тормозная база, секционирование и ускорение

Есть база MS SQL на полтора терабайта, а может и больше, я не видел сам прод.

В ней имеется 9 миллионов клиентов, у которых имеются сотни тысяч транзакций (в бизнес-смысле). Это данные о проезде автомобилей через ворота, условно говоря.

Всякие гриды в тормозном веб-интерфейсе тормозят ещё сильнее во время выборки. А выборка в типовой хранимке происходит так: заполняем временную таблицу уймой запросов по всем данным заданного клиента, затем фильтруем временную таблицу по значениям фильтра из грида, по всем 50 колонкам, по всему миллиону строк, вот в таком духе:

                AND (
                    (@ipv_biTransactionID IS NULL AND @ipv_vcTransactionOperator IS NULL)
                    OR (@ipv_vcTransactionOperator = 's%' AND Y.CustTrxnID LIKE (CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTransactionOperator = 'e%' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX))))
                    OR (@ipv_vcTransactionOperator = '=' AND Y.CustTrxnID = @ipv_biTransactionID)
                    OR (@ipv_vcTransactionOperator = '!=' AND Y.CustTrxnID != @ipv_biTransactionID)
                    OR (@ipv_vcTransactionOperator = 'IN' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTransactionOperator = 'NOT IN' AND Y.CustTrxnID NOT LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                )            
                AND (
                    (@ipv_iTrxnStatus IS NULL AND @ipv_vcTrxnStatusOperator IS NULL)
                    OR (@ipv_vcTrxnStatusOperator = 's%' AND Y.TrxnStatusID LIKE (CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'e%' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX))))
                    OR (@ipv_vcTrxnStatusOperator = '=' AND Y.TrxnStatusID = @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = '!=' AND Y.TrxnStatusID != @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = 'IN' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'NOT IN' AND Y.TrxnStatusID NOT LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 's%' AND Y.DisputedTollStatus LIKE (CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'e%' AND Y.DisputedTollStatus LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX))))
                    OR (@ipv_vcTrxnStatusOperator = '=' AND Y.DisputedTollStatus = @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = '!=' AND Y.DisputedTollStatus != @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = 'IN' AND Y.DisputedTollStatus LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'NOT IN' AND Y.DisputedTollStatus NOT LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                )        
            WHERE x.RowNumber BETWEEN @v_iStartIndex AND @v_iEndIndex
            ORDER BY x.RowNumber;

Транзакции могут быть переданы между клиентами, но чаще всего они происходят в рамках одного клиента. Исторические данные никак от текущих не отделены, в базе есть и 2005 год.

Как бы эту базу распилить, чтобы не тормозила? Если у большей части самых гигантских таблиц есть accountId, то логично было бы применить секционирование по этому id. Но 9 миллионов секций не поддерживаются MS SQL, это слишком много.

Секционирование можно сделать например по 2 последним цифрам в id клиента. Хорошая ли это идея? Что вообще с такой базой можно сделать?

Гигантская тормозная база, секционирование и ускорение

                AND (
                    (@ipv_biTransactionID IS NULL AND @ipv_vcTransactionOperator IS NULL)
                    OR (@ipv_vcTransactionOperator = 's%' AND Y.CustTrxnID LIKE (CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTransactionOperator = 'e%' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX))))
                    OR (@ipv_vcTransactionOperator = '=' AND Y.CustTrxnID = @ipv_biTransactionID)
                    OR (@ipv_vcTransactionOperator = '!=' AND Y.CustTrxnID != @ipv_biTransactionID)
                    OR (@ipv_vcTransactionOperator = 'IN' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTransactionOperator = 'NOT IN' AND Y.CustTrxnID NOT LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                )            
                AND (
                    (@ipv_iTrxnStatus IS NULL AND @ipv_vcTrxnStatusOperator IS NULL)
                    OR (@ipv_vcTrxnStatusOperator = 's%' AND Y.TrxnStatusID LIKE (CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'e%' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX))))
                    OR (@ipv_vcTrxnStatusOperator = '=' AND Y.TrxnStatusID = @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = '!=' AND Y.TrxnStatusID != @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = 'IN' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'NOT IN' AND Y.TrxnStatusID NOT LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                )        
            WHERE x.RowNumber BETWEEN @v_iStartIndex AND @v_iEndIndex
            ORDER BY x.RowNumber;