Индексы в локальной базе данных

Что такое индексы?

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

Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск — например, сбалансированного дерева, хотя могу представлять собой обычную хеш таблицу.

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

Преимущества и недостатки?

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

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

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

Реализация в WP7

В Windows Phone 7.1 в качестве локальной базы данных используется SQL Compact. Обращение к данных происходит по средствам LINQ to SQL. Чтобы создать индекс нужно применить атрибут [Index] к таблице. Он имеет несколько свойств:

• Name — Устанавливает или возвращает имя индекса

• Columns — Набор столбцов из которых состоит индекс.

• IsUnique — Указывает на то, является ли индекс уникальным.

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

Производительность

Рассмотри пример TODO листа. Пусть у каждой записи есть идентификатор, имя, и поле указывающее завершен ли пункт в листе. Модель будет выглядить следующим образом.

public class ToDoDataContext : DataContext  //  INotifyPropertyChanged, INotifyPropertyChanging не реализовывались
{
        public ToDoDataContext(string connectionString)
            : base(connectionString) { }
        public Table<ToDoItem> Items;
}

 [Table]
 [Index(Columns = "ItemName", IsUnique = true, Name = "IdxItemName")]
 public class ToDoItem
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false)]
        public int ToDoItemId { get; set; }
        [Column]
        public string ItemName { get; set; }
        [Column]
        public bool IsComplete { get; set; }
    }

Напишем пару простых тестов для оценки производительности.

public void TestInsert(ToDoDataContext context, int count)
 {
            for (int i = 0; i < count; i++)
            {
                ToDoItem item = new ToDoItem()
                {
                    IsComplete = true,
                    ItemName = "Name " + i
                };
                context.Items.InsertOnSubmit(item);
                context.SubmitChanges();
            }
  }

  public void TestUpdate(ToDoDataContext context, int count)
  {
            for (int i = 0; i < count; i++)
            {
                string name = "Name " + i;
                var item = context.Items.Single(x => x.ItemName == name);
                item.IsComplete = true;
                context.SubmitChanges();
            }
  }
public void TestSelect(ToDoDataContext context, int count)
{
            for (int i = 0; i < count; i++)
            {
                string name = "Name " + i;
                var item = context.Items.Single(x => x.ItemName == name);
            }
}

Я их прогнал на 100, 500, 1000 записей. Первый раз без индексов, а затем с ними. Для замеров использоваться класс Stopwatch. И вот, что получилось в итоге:

Обращаю внимание, что время указано в миллисекундах.

Таким образом, при использовании индексов время выборки (Select) увеличилось примерно в 2 раза; время, затраченное на вставку и обновление данных, изменилось не очень сильно, в пределах 10%. Так что используя индексы можно малыми усилиями (в данном случае просто применением атрибута) увеличить производительность вашего приложения.

И на последок о приятном. На днях прошла презентация Windows phone 8. Где была заявлена поддержка SQLite. Так что ждем с нетерпением.

Исходный код на github