Блог

Если ваше приложение работает с базой данных медленно, то  самое время узнать причины и задуматься над оптимизацией базы данных (БД). 


Оптимизация скорости и БД

 
Без оптимизации не обойтись в следующих ситуациях:


•    если приложение заточено под высокие нагрузки


•    если база данных изобилует сложными запросами к ней



Сложные запросы к БД



 

Индексация


В любой таблице, имеющей первичный ключ, есть кластеризованный индекс по столбцу первичного ключа. Чтобы повысить производительность таблицы можно попробовать создать дополнительные некластеризованные индексы.


С другой стороны, в индексации страницы кроется ряд опасностей:


•    индексация таблицы замедляет операции добавления, изменения  и удаления


•    индекс занимает много места на диске


•    соблазн распыления индекса на таблицы с малым количеством записей


Что делать? 


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


Пример (индекс на столбец FirmFlags.firmId):


SELECT        Firm.id, Firm.name, FirmFlags.isLoading
FROM            FirmFlags INNER JOIN
                         Firm ON FirmFlags.firmId = Firm.id
WHERE Firm.id=63



2. Для сортировок. Здесь мы рекомендуем индекс на столбцы, по которым часто производится сортировка. Например, если в таблице есть столбец определённой датой, по которой по умолчанию производится сортировка по убыванию, то на такой столбец требуется создать индекс с направлением сортировки descending.


Пример (индекс на столбец dateOfStart с направлением сортировки DESC):


SELECT        id, name, dateOfStart, dateOfFinish
FROM            Actions
ORDER BY dateOfStart DESC


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


Пример (составной индекс на столбцы dateOfStart и dateOfFinish с направлениями сортировки DESC):


SELECT        id, name, dateOfStart, dateOfFinish
FROM            Actions
ORDER BY dateOfStart DESC, dateOfFinish DESC



3. Фильтрация. Мы рекомендуем создавать индекс на столбцы, по которым часто производится фильтрация данных. Например, если в таблице есть столбец с определённым числом, по которому из интерфейса часто вызывается фильтрация (например, больше определенного количества), то на такой столбец опять же требуется создать индекс.


Пример (индекс на столбец numberOfRealCoupons):


SELECT        id, name, dateOfStart, dateOfFinish, numberOfRealCoupons
FROM            Actions
WHERE        (numberOfRealCoupons > 0)


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


Пример (составной индекс на столбцы numberOfRealCoupons и numberOfVirtualCoupons):


SELECT        id, name, dateOfStart, dateOfFinish, numberOfRealCoupons, numberOfVirtualCoupons
FROM            Actions
WHERE        (numberOfRealCoupons > 0)



4. Фрагментация индексов. Грамотно построенные индексы потенциально в нсеколько раз могут повысить скорость выполнения запросов. Одно но – они могут стать своеобразной бомбой замедленного действия: по истечении определённого промежутка времени они фрагментируются и намертво «вешают» таблицу при выборке.


В SQL Server Management Studio (SSMS) просмотреть уровень фрагментации индекса возможно, щелкнув по индексу правой кнопкой мыши и нажав «Перестроить» («Rebuild») или «Реорганизовать» («Reorganize»).
 


Перестройка индексов


Мы также советуем реорганизовывать индекс, если уровень фрагментации лежит в диапазоне от 5 до 30%. Если же уровень фрагментации выше 30%, то дело за перестройкой. Для автоматизации процесса обновления индексов рекомендуем  использовать задания (job) SQL Server Agent.


Запомнить: размер индекса в MS SQL Server не может превышать более 900 байт. Если вам потребуется создать индекс на столбец типа varchar, его необходимо ограничить длиной в 900 символов; если же требуется создать индекс на столбец типа nvarchar, его необходимо ограничить длиной в 450 символов. Если это требование из разряда невыполнимых, смотрим в сторону полнотекстового индекса.


Дополнительные способы оптимизации базы данных


Грязное чтение


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


У нас сложилась практика, что везде, где выбранные из БД данные не изменяются, мы используем грязное чтение:


1. На уровне SQL-запроса грязное чтение задается ключевым словом nolock:


SELECT        id, name, dateOfStart, dateOfFinish, numberOfRealCoupons, numberOfVirtualCoupons
FROM            Actions WITH (nolock)
WHERE        (numberOfRealCoupons > 0)


2. При использовании ORM после открытия контекста требуется выполнить команду:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


Пример функции, которую мы вызываем после открытия контекста БД (Entity Framework):
    /// <summary>
    /// установка хинта nolock для запросов
    /// </summary>
    /// <param name="db">контекст БД</param>
    public static void SetNoLockOn(this ObjectContext db) {
      db.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
    }


Кэшируемые поля


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


Варианты запросов


var goods =
          db.Watchlists.Where(
            w =>
            w.userId == userId &&
            (!w.Good.isDeleted && !w.Good.isAuction &&
             (w.Good.Firm.FirmApproved == null || w.Good.Firm.FirmApproved.isApproved) && w.Good.cost > 0)).OrderByDescending(w => w.changeDate).Select(
                 g =>
                 new {
                   id = g.Good.id,
                   name = g.Good.name,
                   cost = g.Good.cost,
                   costWithDiscount = g.Good.costWithDiscount,
                   discount = g.Good.FirmBestOffersSimples.Any() ? g.Good.FirmBestOffersSimples.FirstOrDefault().discount : 0,
                   mainImageUrl = g.Good.GoodImages.Any(b => b.isMain) ? g.Good.GoodImages.FirstOrDefault(b => b.isMain).url : null,
                   addingDate = g.Good.addingDate,
                   reviews = g.Good.Reviews.Where(p => p.isDeleted == false).Select(b => b.rate),
                   description = g.Good.description,
                   shortDescription = g.Good.shortDescription,
                   positionValueId = g.GoodHeadingForGood_PossibleValues.Any() ? (int?)g.GoodHeadingForGood_PossibleValues.FirstOrDefault().id : null,
                   oldCost = g.oldCost,
                   newCost = g.newCost,
                   changeDate = g.changeDate,
                   goodPositionValueId1 = g.GoodHeadingForGood_PossibleValues.Any() ? (int?)g.GoodHeadingForGood_PossibleValues.FirstOrDefault().id : null,
                   goodPositionValueId2 = (g.GoodHeadingForGood_PossibleValues.Any() && g.GoodHeadingForGood_PossibleValues.Count > 1) ? (int?)g.GoodHeadingForGood_PossibleValues.OrderBy(p => p.id).Skip(1).FirstOrDefault().id : null,
                   firmId = g.Good.firmId,
                   firmName = g.Good.Firm.name,
                   firmRating = g.Good.Firm.rating ?? 0,
                   subdomain = g.Good.Firm.FirmSubdomain != null ? g.Good.Firm.FirmSubdomain.name : null,
                   isInWishList = db.Wishlists.Any(a => a.goodId == g.Good.id && a.userId == userId && (g.GoodHeadingForGood_PossibleValues.Any() == false || (a.GoodHeadingForGood_PossibleValues.Any() && a.GoodHeadingForGood_PossibleValues.FirstOrDefault().id == g.GoodHeadingForGood_PossibleValues.FirstOrDefault().id))),
                   hasPos = g.GoodHeadingForGood_PossibleValues.Any(),
                   goodPositionName = g.GoodHeadingForGood_PossibleValues.Any() ? db.GoodHeadingForGoods.FirstOrDefault(a => a.id == g.GoodHeadingForGood_PossibleValues.FirstOrDefault().goodHeadingId).name : String.Empty,
                   goodPositionValue = g.GoodHeadingForGood_PossibleValues.Any() ? g.GoodHeadingForGood_PossibleValues.FirstOrDefault().possibleValue : String.Empty
                 }).ToArray();

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


Пример:


SELECT        Firm.id, Firm.name
FROM            Firm INNER JOIN
                         Good ON Firm.id = Good.firmId
WHERE        (Good.saleCount > 0)
GROUP BY Firm.id, Firm.name
Заменить на
SELECT        id, name
FROM            Firm
WHERE        EXISTS
                             (SELECT        TOP (1) *
                               FROM            Good
                               WHERE        (firmId = Firm.id) AND (saleCount > 0))
GROUP BY id, name

 

Entity Framework. No tracking


Entity Framework при сохранении данных по команде SaveChanges проверяет загруженные в память объекты на наличие изменений. Но что, если мы заведомо знаем, что изменений не будет? В таком случае можно указать EF, что не требуется отслеживать изменения объектов в памяти.


    /// <summary>
    /// Выборка только для чтения без отслеживания объекта
    /// </summary>
    /// <typeparam name="T">таблица</typeparam>
    /// <param name="table">таблица</param>
    public static void SetNoTracking<T>(this ObjectSet<T> table) where T : class {
      table.MergeOption = MergeOption.NoTracking;
    }


Автор: Юрий Титов, программист отдела высоконагруженных систем


Возврат к списку