Немного о повышении производительности БД: Практические советы



    / фото Ozzy Delaney CC

    Мы в 1cloud много рассказываем о собственном опыте работы над провайдером виртуальной инфраструктуры и тонкостях организации внутренних процессов. Сегодня мы решили немного поговорить об оптимизации БД.

    Многие СУБД способны не только хранить и управлять данными, но и исполнять код на сервере. Примером этого служат хранимые процедуры и триггеры. Однако всего одна операция изменения данных может запустить несколько триггеров и хранимых процедур, которые, в свою очередь, «разбудят» еще парочку. В качестве примера можно привести каскадное удаление в базах данных SQL, когда исключение одной строки в таблице приводит к изменению многих других связанных записей.

    Очевидно, что пользоваться расширенной функциональностью следует осторожно, чтобы не нагружать сервер, ибо все это может сказаться на производительности клиентских приложений, использующих данную БД.

    Взгляните на график ниже. На нем изображены результаты выполнения нагрузочного тестирования приложения, когда число пользователей (синий график), работающих с БД, постепенно увеличивается до 50. Количество запросов (оранжевый), с которыми система может справиться, быстро достигает своего максимума и перестаёт расти, тогда как время ответа (желтый) постепенно увеличивается.



    При работе с крупными базами данных даже малейшее изменение способно оказать серьезное влияние на производительность, причем как в положительную, так и отрицательную сторону. В организациях среднего и большого размера настройкой баз данных занимается администратор, но частенько эти задачи ложатся на плечи разработчиков. Поэтому далее мы дадим несколько практических советов, которые помогут повысить производительность баз данных SQL.

    Используйте индексы

    Индексация – это эффективный способ настройки базы данных, которым часто пренебрегают во время разработки. Индекс ускоряет запросы, предоставляя быстрый доступ к строкам данных в таблице, аналогично тому, как предметный указатель в книге помогает вам быстро найти желаемую информацию.

    Например, если вы создадите индекс по первичному ключу, а затем будете искать строку с данными, используя значения первичного ключа, то SQL-сервер сначала найдет значение индекса, а затем использует его для быстрого нахождения строки с данными. Без индекса будет выполнено полное сканирование всех строк таблицы, а это трата ресурсов.

    Однако стоит обратить внимание, что, если ваши таблицы «бомбардируются» методами INSERT, UPDATE и DELETE, к индексации нужно отнестись осторожно – она может привести к ухудшению производительности, так как после проведения указанных выше операций все индексы должны быть изменены.

    Более того, когда нужно добавить в таблицу большое количество строк (например более миллиона) сразу, администраторы БД часто сбрасывают индексы для ускорения процесса вставки (после вставки индексы создаются заново). Индексация – это обширная и интересная тема, для ознакомления с которой недостаточно столь краткого описания. Больше информации по этой теме вы найдете здесь.

    Не используйте циклы с большим количеством итераций

    Представьте ситуацию, когда на вашу БД последовательно приходит 1000 запросов:

    for (int i = 0; i < 1000; i++)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES...");
        cmd.ExecuteNonQuery();
    }
    

    Такие циклы писать не рекомендуется. Пример выше можно переделать, используя один INSERT или UPDATE с несколькими параметрами:

    INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9)
    UPDATE TableName SET A = CASE B
            WHEN 1 THEN 'NEW VALUE'
            WHEN 2 THEN 'NEW VALUE 2'
            WHEN 3 THEN 'NEW VALUE 3'
        END
    WHERE B in (1,2,3
    )
    

    Убедитесь, что операция WHERE не перезаписывает одинаковые значения. Такая простая оптимизация может ускорить выполнение SQL-запроса, уменьшив количество обновляемых строк с тысяч до сотен. Пример проверки:

    UPDATE TableName
    SET A = @VALUE
    WHERE
          B = 'YOUR CONDITION'
                AND A <> @VALUE – VALIDATION
    

    Избегайте коррелирующих подзапросов

    Коррелирующим подзапросом называют такой подзапрос, который использует значения родительского запроса. Он выполняется построчно, один раз для каждой строки, возвращённой внешним (родительским) запросом, что снижает скорость работы БД. Вот простой пример коррелирующего подзапроса:

    SELECT c.Name, 
           c.City,
           (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
    FROM Customer c
    

    Здесь проблема в том, что внутренний запрос (SELECT CompanyName…) выполняется для каждой строки, которую возвращает внешний запрос (SELECT c.Name…). Чтобы повысить производительность, можно переписать подзапрос через JOIN:

    SELECT c.Name, 
           c.City, 
           co.CompanyName 
    FROM Customer c 
    	LEFT JOIN Company co
    		ON c.CompanyID = co.CompanyID
    

    Старайтесь не использовать SELECT *

    Старайтесь не использовать SELECT *! Вместо этого стоит подключать каждый столбец по отдельности. Звучит просто, но на этом моменте спотыкаются многие разработчики. Представьте таблицу с сотнями столбцов и миллионами строк. Если вашему приложению нужно лишь несколько столбцов, нет смысла запрашивать всю таблицу – это большая трата ресурсов.

    Например, что лучше: SELECT * FROM Employees или SELECT FirstName, City, Country FROM Employees?

    Если вам действительно нужны все столбцы, укажите каждый в явном виде. Это поможет избежать ошибок и дополнительной настройки БД в будущем. Например, если вы используете INSERT… SELECT..., а в исходной таблице появился новый столбец, могут возникнуть ошибки, даже если этот столбец не нужен в конечной таблице:

    INSERT INTO Employees SELECT * FROM OldEmployees
    Msg 213, Level 16, State 1, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.
    

    Во избежание таких ошибок, нужно прописывать каждый столбец:

    INSERT INTO Employees (FirstName, City, Country)
    SELECT Name, CityName, CountryName
    FROM OldEmployees
    

    Однако стоит заметить, что есть ситуации, в которых использование SELECT * допустимо. Примером могут служить временные таблицы.

    Пользуйтесь временными таблицами с умом

    Временные таблицы чаще всего усложняют структуру запроса. Поэтому их лучше не использовать, если есть возможность оформить простой запрос.

    Но если вы пишете хранимую процедуру, выполняющую какие-то действия с данными, которые невозможно оформить в одном запросе, то используйте временные таблицы как «посредников», помогающих получить конечный результат.

    Допустим, вам нужно сделать выборку с условиями из большой таблицы. Чтобы увеличить производительность БД, стоит перевести свои данные во временную таблицу и выполнить JOIN уже с ней. Временная таблица будет меньше исходной, поэтому объединение произойдёт быстрее.

    Не всегда понятно, в чем разница между временными таблицами и подзапросами. Потому приведем пример: представьте таблицу покупателей с миллионами записей, из которой нужно сделать выборку по региону. Один из вариантов реализации – использовать SELECT INTO с последующим объединением во временную таблицу:

    SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
    SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID
    

    Но вместо временных таблиц можно использовать подзапрос:

    SELECT r.RegionName, t.Name FROM Region r 
    JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t 
    ON t.RegionID = r.RegionID
    

    В предыдущем пункте мы обсуждали, что стоит прописывать в подзапросе только нужные нам столбцы, поэтому:

    SELECT r.RegionName, t.Name FROM Region r 
    JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t 
    ON t.RegionID = r.RegionID
    

    Каждый из трех примеров вернет один и тот же результат, но в случае с временными таблицами вы получаете возможность использовать индексацию. Для более полного понимания принципов работы временных таблиц и подзапросов можете почитать тему на Stack Overflow.

    Когда работа с временной таблицей закончена, лучше удалить её и освободить ресурсы tempdb, чем ждать, пока произойдет автоматическое удаление (когда ваше соединение с сервером БД закроется):

    DROP TABLE #temp

    Используйте EXISTS()

    Если необходимо проверить существование записи, лучше использовать оператор EXISTS() вместо COUNT(). Тогда как COUNT() проходит по всей таблице, EXISTS() прекращает работу после нахождения первого совпадения. Этот подход повышает производительность и улучшает читаемость кода:

    IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0
     	PRINT 'YES' 
    

    или

    IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%')
        PRINT 'YES'
    

    Вместо заключения


    Пользователи приложений любят, когда им не нужно подолгу смотреть на значок загрузки, когда все работает четко и быстро. Применение описанных в этом материале приемов позволит вам повысить производительность базы данных, что положительно скажется на пользовательском опыте.

    Хотелось бы подвести небольшой итог и повторить ключевые моменты, описанные в статье:

    1. Используйте индексы, чтобы ускорить проведение поиска и сортировки.
    2. Не используйте циклы с большим количеством итераций для вставки данных – используйте INSERT или UPDATE.
    3. Обходите стороной коррелирующие подзапросы.
    4. Ограничивайте количество параметров оператора SELECT – указывайте только нужные таблицы.
    5. Используйте временные таблицы только как «посредников» для объединения крупных таблиц.
    6. Для проверки на наличие записи пользуйтесь оператором EXISTS(), который заканчивает работу после определения первого совпадения.

    Если вам интересна тема производительности баз данных, то на Stack Exchange есть обсуждение, в котором собрано большое количество полезных ресурсов, – вам стоит обратить на него внимание. Еще можете почитать наш материал о том, как работают с данными крупные мировые компании.

    Свежие материалы из нашего блога на Хабре:

    1cloud.ru
    264,00
    IaaS, VPS, VDS, Частное и публичное облако, SSL
    Поделиться публикацией

    Комментарии 34

      0
      Неплохо, в закладки. спасибо
        0
        Спасибо, что читаете блог.

        Кстати, в комментариях можно было бы обсудить предложения для следующих постов – какие туториалы могли бы быть интересны применительно к теме IaaS, например.
          +1
          Я у вас раньше читал что-то про систему мониторинга нагрузки. Есть ли какие-то новые функции в ней? Можно было бы какие-то реальные кейсы привести, в которых она используется для обнаружения узких мест и быстрого масштабирования.
            +3
            Спасибо, подумаем на этот счет :)
        0
        А как же всё связанное EXPLAIN? Без этого получается однобоко — результат был бы более наглядно.
        А что насчет хаков по принуждению тупого оптимизатора к использованию конкретных индексов?

        Но спасибо, людей носом тыкать в одну ссылку гораздо удобнее, перечислены ключевые вещи.
          0
          Можно в следующих сериях рассмотреть, конечно
            +1
            А это где вы встретили тупой оптимизатор? :)
              0
              в Mysql некоторых версий приходилось дополнительно уговаривать использовать специально созданные индексы просто потому, что это начинало работать быстрее.
              http://dev.mysql.com/doc/refman/5.7/en/index-hints.html
              http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
              конструкции пока в движке еще есть, так что кому-то это нужно,
              но я сейчас уже не работаю с тем проектом, где жить без этого было медленно, так что реальных примеров не смогу дать.
                0
                Иногда бывает удобно принудительно указывать на индекс в случае работы с большими таблицами и когда имеется несколько «частичных» индексов (partial index). Оптимизатор тут может долго гадать какой использовать или просто выбрать самый большой индекс, чтобы уж наверняка.
                  0
                  В email lists PostgreSQL была в своё время одна светлая мысль, которая звучала вкратце — «не считайте себя умнее планирощика запросов».
                  В 99,9% это работает. Возможно и с MySQL тоже.

                  Если немного расширить — «сначала разберитесь, почему планировщик выбрал именно этот индекс или не выбрал вообще никакой и, возможно, поймёте, почему вы были неправы».
                    +2
                    Большая таблица(миллионы строк), в запросе множество условий(и под них попадает большая часть данных), приправленные LIKE и в конце ODER BY date DESC LIMIT 20.
                    Оптимизатор пытается подобрать индекс для отсеивания по части условий(меньшее из больших подмножеств), но потом при исполнении нужно проверять остальные и сортировать результат( в выбранном индексе date может присутствовать, но до него поле, где получился range)
                    Если же ему подсказать использовать индекс date — будет читать строки по нему и в 95% случаев получит желаемые 20 проверив меньше 100, хоть и нужно проверять все условия.
                    Да, причина в несоответствии структуры хранения «эволюционировавшим» условиям выборок, но работать должно сейчас, а отрефакторят быть может в следующей жизни.
                +1
                Со временными таблицами нужно быть осторожным. Во-первых, СУБД может сама решить создать временный индекс и(или) будет использовать знания о сортировке. Во-вторых, в какой-то момент СУБД может решить изменить in-memory таблицу на дисковую. В третьих, размер временной таблицы может неожиданно превысить размер раздела для временных файлов, а установление этого факта может оказаться не тривиальным.
                  +6
                  Извините, конечно, но это рекомендации начального уровня.

                  Если в системе, напротив которой выполняется нагрузочное тестирование, они не были изначально выполнены — то это значит, что перед нами — однопользовательский проект студенческого уровня, а не бакенд системы массового обслуживания.

                    +3
                    Повсеместная проблема состоит в том, что все обычно забывают про базовые моменты :)
                      +1
                      В таком случае, эти «все» — поголовно студенты. Хотя в этом вы правы — в процессе вертикальной разработки систем типичен холивар по поводу naming convention, coding style и иерархии классов, а на продумывание и проектирование хорошего бакенда времени почему-то не остается. Code first, и вперед!
                        0
                        Чаще всего эти проблемы возникают именно у «опытных». При чем, что характерно, студенты просто ошибаются — ну не набил он ещё этих шишек, это простительно. А вот опытные разработчики часто не делают из принципа. Любимые мантры «эта таблица никогда не вырастет», «индекс увеличивает время апдейта» (играет в кепа, но расчитать статистику не способен) и «да зачем нужен индекс на родительской таблице под форейн кеем, я никогда этого не делаю» (детская ошибка с тяжкими последствиями в Оракл).
                          +4
                          Еще часто слышно:
                          1) «Этим все должна заниматься ORM»
                          2) «Надо просто увеличить кэш»
                          3) «Надо просто нарастить мощности»
                          4) «Это недо-БД. Вот postgres|oracle|MSSQL|… с этим сама справится»
                          5) «Некогда этим заниматься»
                            0
                            6) «Этим должен заниматься ДБА, мне про индексы знать ни к чему»
                      –1
                      1) Нужно пояснить, что речь идет про MySQL. Для других СУБД советы могут быть неверными.

                      2)
                      Убедитесь, что операция WHERE не перезаписывает одинаковые значения.

                      Это актуально только если это улучшает использование индексов (например, при использовании двух полей индекса вместо одного.) и тем самым уменьшает количество записей, читаемых из таблицы.
                      Но в случае с неравенством это редко бывает.
                      Если же улучшения использования индексов не происходит, то смысла в этой проверке нет, так как MySQL все равно ее производит самостоятельно и не перезаписывает неизменённые записи.
                        +1
                        Тоже удивился когда прочел пост, звучит как общие рекомендации однако например совет 2 не верен для MS SQL, поскольку практически всегда кореллирующий подзапрос преобразуется к OUTER JOIN.
                        Насчет EXISTS разница с SELECT TOP 1 будет не большая, а сам пример выглядит как хороший вредный совет при использовании LIKE и индексов. Нужно стараться избегать LIKE начинающихся с %.
                        По факту из полезных советов только используйте индексы и объединяйте кучу мелких запросов в один, все остальное микрооптимизации которые врядли дадут буст производительности и являются скорее хорошим стилем.
                          0
                          Не использовать коррелирующие подзапросы — самый полезный совет во всей статье. Они я вам скажу очень не всегда правильно раскладываются оптимизатором.
                          p.s. У меня есть три любимых пункта на тему «как максимально усложнить жизнь себе, оптимизатору и вселенной»
                          1. Используйте коррелированные подзапросы — много, да еще и желательно вложенные в друг друга.
                          2. Используйте инлайн функции, особенно хорошо когда они тоже сами вызывают функции.
                          3. Используйте триггера, тоже сами понимаете лучше вызывающие срабатывание других триггеров и т.д.
                            0
                            Коррелирующие подзапросы часть языка не использовать их только потому, что оптимизатор может ошибиться мне кажется глупым. Запрос как и любая программа должен хорошо читаться, если для выразительности подзапрос выглядит лучше объединения, я буду использовать подзапрос. Когда же мы говорим о производительности, запрос сам по себе не может быть хорошим или плохим, всегда плохая производительность результат неудачного плана, а тот в свою очередь результат множества факторов из которых такой простой рефакторинг как замена subquery на join очень редко являются первопричиной (буду говорить только об оптимизаторе MSSQL поскольку плотно работал только с ним). Возможно для других движков имеют место подобные проблемы, тогда и говорить о подобных советах следует в контексте конкретного движка В общем случае только план запроса может быть необходимым и достаточным условием для оптимизации запроса.
                        0
                        1. Осторожно с индексами. Они должны быть продуманы. Бывает ускоришь один селект, а десять упадут.

                        2. Bulk Insert'ы для загрузки таблицы. Если это еще и процесс например миграции, то желательно снести все индексы перед инсертами.

                        6. На счет count vs exists можно ограничить count по rownumber = 1. Так же exists может быть намного быстрее чем конструкция IN (), но может и не быть.

                        7. Писать такие запросы, которые кэшируются и не требуют парсинга каждый раз, когда выполняются. Как пример preparedstatement

                        В целом надо смотреть на планы.
                          0
                          Еще подготовленные запросы — очень полезная штука.
                          И про лимиты не стоит забывать.

                          А вообще, если требуется серьезно поднять производительность работы БД(без повышения производительности аппаратной части), лучшим будет изучить хотя бы поверхностно, как ведет себя конкретная БД в определенных случаях(в транзакции; при первичной записи; что и в каких случаях попадает в кэш БД; как работает журналирование;… прочее...). Чуточку в настройках покопаться. И разобраться, как все вышеперечисленное между собой связано.

                          Да и в общем особенности реализации конкретной БД.

                          Многие оптимизации сами придут в голову, при понимании работы конкретной БД.

                          //------------------------
                          Ну и на мой взгляд, под производительностью стоит подразумевать не только скорость обработки запросов, но и объем потребляемых ресурсов. Бывает и такое, что ваш не самый частый запрос стал обрабатываться чуток быстрее, но выбил из кэша много полезного и часто используемого.
                            +1
                            Оптимизатор может делать все это за вас, а вы пытаясь переписать код в пустуе потратите время. Оптимизатор зачастую отлично справится с COUNT и построит такой же план как и для EXISTS.
                            IF (SELECT count(*) FROM Users WHERE active = 1) > 0

                            |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
                            |--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
                            |--Constant Scan
                            |--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))

                            IF EXISTS (SELECT 1 FROM Users WHERE active = 1)

                            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))
                            |--Nested Loops(Left Semi Join, DEFINE:([Expr1005] = [PROBE VALUE]))
                            |--Constant Scan
                            |--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))

                            Свободно преобразует подзапрос к джойну:
                            select top 100 CourseRegistrationID, (SELECT Email FROM Users WHERE UserID = cr.UserID) FROM CourseRegistrations cr

                            |--Top(TOP EXPRESSION:((100)))
                            |--Compute Scalar(DEFINE:([Expr1007]=[TestDB].[dbo].[Users].[Email]))
                            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([cr].[UserID], [Expr1008]) WITH UNORDERED PREFETCH)
                            |--Index Scan(OBJECT:([TestDB].[dbo].[CourseRegistrations].[IX_CourseRegistrations_UserID] AS [cr]), ORDERED FORWARD)
                            |--Clustered Index Seek(OBJECT:([TestDB].[dbo].[Users].[PK_Users]), SEEK:([TestDB].[dbo].[Users].[UserID]=[TestDB].[dbo].[CourseRegistrations].[UserID] as [cr].[UserID]) ORDERED FORWARD)

                            Оптимизировать не имея статистики и не глядя в планы исполнения изначально является глупой затеей. Соберите статистику, проанализируйте план запроса и только после этого начинайте оптимизацию.

                            Если условия слишком сильно зависят от параметров, а параметры могут влиять на логику, в таком случае иногда выгоднее иметь не preparedstatement, а динамически сформированный запрос.

                              0
                              --Пример выше можно переделать, используя один INSERT или UPDATE

                              И тут вы быстро упретесь в ограничение на размер запроса в 8 Кб.

                              Лучше всего пихать в xml и передавать как параметер, тогда и 2 Тб можно вставить одним запросом.

                              C#:
                                                  using (SqlCommand cmd = new SqlCommand())
                                                  {
                                                      cmd.Connection = cnn;
                                                      cmd.CommandType = CommandType.Text;
                                                      cmd.CommandText = "INSERT INTO CoreAccount SELECT X.C.value('@name', 'nvarchar(255)') FROM (SELECT @data AS XML_DATA) DATA CROSS APPLY DATA.XML_DATA.nodes('//value') as X(C) ";
                              
                                                      cmd.Parameters.Add("@data", SqlDbType.Xml);
                              
                                                              cmd.Parameters[0].Value = userList;
                              
                                                              cmd.ExecuteNonQuery();
                              }
                                0
                                И тут вы быстро упретесь в ограничение на размер запроса в 8 Кб.
                                Это где такое ограничение?
                                По крайней мере, в самом MySQL такого ограничения нет.

                                Точнее, есть max_allowed_packet, но у него дефолтное значение 1 или 4 мегабайта (зависит от версии MySQL).
                                  0
                                  Вы пишите о ограничениях сервера, а есть еше ограничения библиотек. ODBC, JDBC, ADO,…
                                  Там бывает все печальнее.
                                    0
                                    2 тб так вставлять тоже не стоит. Bulk load наш выбор
                                      0
                                      2 Тб это конечно гротеск, но из реального — пришлось недавно напистаь сервис, что в идеале должен раз в сутки вставлять список из миллиона аккаунтов. никаког bulk разумеется в той архитектуре не предвидится.

                                      Пир средней длине имени 10 символов (тестирпвал twitter) это примерно 30М в конечном файле с UTF-8.
                                –1
                                Пользуйтесь временными таблицами с умом


                                Не знаю как в MySQL, но в MS SQL вместо
                                 create table #tempTable (id int) ... drop table #tempTable
                                

                                лучше использовать table variable

                                declare @temp table(id int)
                                


                                А если ещё использовать тип, то можно быть 100% уверенным в том, что таблица будет in memory.
                                  +1
                                  На самом деле в MSSQL table variable и temp table хранятся в temp db и имеют очень мало отличий (речь не идет о использовании типов). Из главных отличий (в моей практике) это отсутствие статистики для table variable, в большенстве случае вы будете получать estimate в 1 запись, особенно это критично в сторед процедурах. Так же хочу заметить что записи в table variable не являются частью пользовательской транзакции. Наверное самый толковый пост по сравнению этих объектов (там же есть ссылка на Memory-Optimized Table Types) What's the difference between a temp table and table variable in SQL Server?
                                  0
                                  Очень много споров и обсуждений касательно COUNT(1) or COUNT(*)?
                                  Я заметил, что вы используете COUNT(1).
                                  Прокомментируйте пожалуйста, что же всё таки предпочтительнее/быстрее? * or 1 ?)
                                    +1
                                    Это вкусовщина чистой воды, любое NOT NULL выражение равносильно COUNT(*).

                                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                  Самое читаемое