Когда следует использовать составной индекс?

  • Когда я должен использовать составной индекс в базе данных?
  • Что такое разветвление производительности, используя составной индекс)?
  • Почему я должен использовать составной индекс?

Например, у меня есть таблица homes:

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  PRIMARY KEY  (`home_id`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
) ENGINE=InnoDB  ;

Имеет ли смысл использовать составной индекс для geolat и geolng, например:

Я заменяю:

  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),

с:

KEY `geolat_geolng` (`geolat`, `geolng`)

Если да:

  • Почему?
  • Что такое разветвление производительности с помощью составного индекса)?

UPDATE:

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

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

ОБНОВЛЕНИЕ 2:

Со следующей схемой базы данных:

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `primary_photo_group_id` int(10) unsigned NOT NULL default '0',
  `customer_id` bigint(20) unsigned NOT NULL,
  `account_type_id` int(11) NOT NULL,
  `address` varchar(128) collate utf8_unicode_ci NOT NULL,
  `city` varchar(64) collate utf8_unicode_ci NOT NULL,
  `state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `zip` mediumint(8) unsigned NOT NULL,
  `price` mediumint(8) unsigned NOT NULL,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `num_of_beds` tinyint(3) unsigned NOT NULL,
  `num_of_baths` decimal(3,1) unsigned NOT NULL,
  `num_of_floors` tinyint(3) unsigned NOT NULL,
  `description` text collate utf8_unicode_ci,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  `display_status` tinyint(1) NOT NULL,
  `date_listed` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `contact_email` varchar(100) collate utf8_unicode_ci NOT NULL,
  `contact_phone_number` varchar(15) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`home_id`),
  KEY `customer_id` (`customer_id`),
  KEY `city` (`city`),
  KEY `num_of_beds` (`num_of_beds`),
  KEY `num_of_baths` (`num_of_baths`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
  KEY `account_type_id` (`account_type_id`),
  KEY `display_status` (`display_status`),
  KEY `sqft` (`sqft`),
  KEY `price` (`price`),
  KEY `primary_photo_group_id` (`primary_photo_group_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

Используя следующий SQL:

EXPLAIN SELECT  homes.home_id,
                    address,
                    city,
                    state,
                    zip,
                    price,
                    sqft,
                    year_built,
                    account_type_id,
                    num_of_beds,
                    num_of_baths,
                    geolat,
                    geolng,
                    photo_id,
                    photo_url_dir
            FROM homes
            LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id
                AND homes.primary_photo_group_id = home_photos.home_photo_group_id
                AND home_photos.home_photo_type_id = 2
            WHERE homes.display_status = true
            AND homes.geolat BETWEEN -100 AND 100
            AND homes.geolng BETWEEN -100 AND 100

EXPLAIN возвращает:

id  select_type  table        type  possible_keys                                    key                  key_len  ref     rows  Extra
----------------------------------------------------------------------------------------------------------
1   SIMPLE       homes        ref   geolat,geolng,display_status                     display_status       1        const   2     Using where
1  SIMPLE        home_photos  ref   home_id,home_photo_type_id,home_photo_group_id   home_photo_group_id  4        homes.primary_photo_group_id   4

Я не совсем понимаю, как читать команду EXPLAIN. Это хорошо или плохо. Прямо сейчас я НЕ использую составной индекс для геолата и геолинга. Должен ли я быть?

Ответы

Ответ 1

Вам следует использовать составной индекс, когда вы используете запросы, которые его извлекают. Составной индекс, который выглядит следующим образом:

index( column_A, column_B, column_C )

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

index( column_A, column_B, column_C )
index( column_A, column_B )
index( column_A )

Но это не будет (по крайней мере, не напрямую, может быть, может частично помочь, если нет лучших индексов) помощь для запросов, требующих

index( column_A, column_C )

Обратите внимание, что отсутствует column_B.

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

Ответ 2

Представьте, что у вас есть следующие три запроса:

Запрос I:

SELECT * FROM homes WHERE `geolat`=42.9 AND `geolng`=36.4

Запрос II:

SELECT * FROM homes WHERE `geolat`=42.9

Запрос III:

SELECT * FROM homes WHERE `geolng`=36.4

Если у вас отдельный индекс на столбец, все три запроса используют индексы. В MySQL, если у вас есть составной индекс (geolat, geolng), только запрос я и запрос II (который использует первую часть индекса компоновки) используют индексы. В этом случае запрос III требует полного поиска в таблице.

В разделе "Несколько столбцов индексов" в разделе руководства, четко объясняется, как работают несколько столбчатых индексов, поэтому я не хочу вводить вручную,

На странице Справочное руководство по MySQL:

Многоколоночный индекс может быть рассмотренный сортированный массив, содержащий значения , созданные конкатенирование значений индексированные столбцы.

Если вы используете отдельный индекс для столбцов geolat и geolng, у вас есть два разных индекса в вашей таблице, которые вы можете искать независимо.

INDEX geolat
-----------
VALUE RRN
36.4  1
36.4  8
36.6  2
37.8  3
37.8  12
41.4  4

INDEX geolng
-----------
VALUE RRN
26.1  1
26.1  8
29.6  2
29.6  3
30.1  12
34.7  4

Если вы используете составной индекс, у вас есть только один индекс для обоих столбцов:

INDEX (geolat, geolng)
-----------
VALUE      RRN
36.4,26.1  1
36.4,26.1  8
36.6,29.6  2
37.8,29.6  3
37.8,30.1  12
41.4,34.7  4

RRN - относительный номер записи (для упрощения вы можете указать ID). Первые два индекса генерируются отдельно, а третий индекс является составным. Как вы можете видеть, вы можете искать на основе геолинга на композитном, поскольку он индексируется геолатом, однако его можно искать по геолату или геолоту и геолокации (так как geolng - это индекс второго уровня).

Кроме того, ознакомьтесь с Как MySQL использует индексы.

Ответ 3

Может быть неправильное представление о том, что делает составной индекс. Многие считают, что составной индекс можно использовать для оптимизации поискового запроса, если предложение where охватывает индексированные столбцы в вашем случае geolat и geolng. Позвольте углубиться глубже:

Я считаю, что ваши данные о координатах домов будут случайными десятичными знаками как таковыми:

home_id  geolat  geolng
   1    20.1243  50.4521
   2    22.6456  51.1564
   3    13.5464  45.4562
   4    55.5642 166.5756
   5    24.2624  27.4564
   6    62.1564  24.2542
...

Так как значения geolat и geolng вряд ли повторяются. Составной индекс на geolat и geolng будет выглядеть примерно так:

index_id  geolat  geolng
   1     20.1243  50.4521
   2     20.1244  61.1564
   3     20.1251  55.4562
   4     20.1293  66.5756
   5     20.1302  57.4564
   6     20.1311  54.2542
...

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

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

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

Ответ 4

Композитные индексы очень мощные, поскольку они:

  • Обеспечение целостности структуры
  • Включить сортировку по FILTERED id

ЦЕЛОСТНОСТЬ КОНСТРУКЦИИ

Композитные индексы - это не просто еще один тип индекса; они могут обеспечить НЕОБХОДИМУЮ структуру для таблицы путем обеспечения целостности в качестве основного ключа.

Mysql Innodb поддерживает кластеризацию, а следующий пример иллюстрирует, почему необходим составной индекс.

Для создания таблиц друзей (т.е. для социальной сети) нам нужны 2 столбца: user_id, friend_id.

Строка таблицы

user_id (medium_int)
friend_id (medium_int)

Primary Key -> (user_id, friend_id)

В силу того, что первичный ключ (PK) уникален, и, создав составную PK, Innodb автоматически проверяет, нет ли дубликатов в user_id, friend_id при добавлении новой записи. Это ожидаемое поведение, поскольку ни один пользователь не должен иметь более 1 записи (связь связи) с friend_id = 2, например.

Без составной PK мы можем создать эту схему с помощью суррогатного ключа:

user_friend_id
user_id
friend_id

Primary Key -> (user_friend_id)

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

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

ВКЛЮЧИТЬ СОРТИРОВАНИЕ НА ФИЛЬТРИРОВАННУЮ ID

Очень часто сортировать набор записей по времени публикации (timestamp или datetime). Обычно это означает проводку по заданному идентификатору. Вот пример

Таблица User_Wall_Posts (подумайте, если настенные сообщения Facebook)

user_id (medium_int)
timestamp (timestamp)
author_id (medium_int)
comment_post (text)

Primary Key -> (user_id, timestamp, author_id)

Мы хотим запросить и найти все сообщения для user_id = 10 и отсортировать сообщения комментариев timestamp (date).

SQL QUERY

SELECT * FROM User_Wall_Posts WHERE user_id = 10 ORDER BY timestamp DES

Составной PK позволяет Mysql фильтровать и сортировать результаты с использованием индекса; Mysql не будет использовать временный файл или файл для получения результатов. Без составного ключа это было бы невозможно и вызовет очень неэффективный запрос.

Таким образом, составные клавиши очень мощные и подходят больше, чем простая проблема "Я хочу искать column_a, column_b, поэтому я буду использовать составные клавиши. Для моей текущей схемы базы данных у меня столько же составных клавиш, сколько одиночных Не забудьте использовать составной ключ!

Ответ 5

Составные индексы полезны для

  • 0 или более "=", плюс
  • не более одного предложения диапазона.

Составной индекс не может обрабатывать два диапазона. Я обсуждаю это далее в своей индексной кулинарной книге.

Найти ближайший. Если вопрос действительно о оптимизации

WHERE geolat BETWEEN ??? AND ???
  AND geolng BETWEEN ??? AND ???

то ни один индекс не может справиться с обоими измерениями.

Вместо этого нужно "думать из коробки". Если одно измерение реализовано с помощью разбиения на разделы, а другое реализовано путем тщательного выбора PRIMARY KEY, можно получить значительно лучшую эффективность для очень больших таблиц поиска lat/lng. Мой блог latlng рассказывает о том, как реализовать "найти ближайший" на земном шаре. Он включает в себя код.

PARTITIONs являются полосами широтных диапазонов. PRIMARY KEY намеренно начинается с долготы, так что полезные строки, вероятно, будут в одном блоке. Stored Routine организует грязный код для выполнения order by... limit... и для увеличения "квадрата" вокруг цели, пока у вас не будет достаточно кафе (или что-то еще). Он также заботится о вычислениях большого круга и обработке данных и полюсов.

Ответ 6

Нет черно-белых, один размер подходит для всех.

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

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

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

UPDATE (в ответ на редактирование размещенного вопроса): Если вы выберете * из таблицы, можно использовать составной индекс, это может быть не так. Вам нужно будет запустить EXPLAIN PLAN.

Ответ 7

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

В некоторых базах данных встроены пространственные индексы. Быстрый поиск в Google показывает, что MySQL 5 имеет их (которые смотрят на ваш SQL, я предполагаю, что вы используете MySQL).

Ответ 8

Компонентный индекс может быть полезен, когда вы хотите оптимизировать предложение group by (проверьте эту статью http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html). Обратите внимание:

Наиболее важными предпосылками для использования индексов для GROUP BY являются что все столбцы GROUP BY ссылаются на атрибуты из одного индекса, и что индекс сохраняет свои ключи в порядке (например, это Индекс BTREE, а не индекс HASH)

Ответ 9

Я с @Mitch, полностью зависит от ваших запросов. К счастью, вы можете создавать и удалять индексы в любое время, и вы можете добавить ключевое слово EXPLAIN к своим запросам, чтобы увидеть, использует ли анализатор запросов индексы.

Если вы будете искать точную лат/длинную пару, этот индекс, скорее всего, будет иметь смысл. Но вы, вероятно, собираетесь искать дома на определенном расстоянии от определенного места, поэтому ваши запросы будут выглядеть примерно так (см. source):

select *, sqrt(  pow(h2.geolat - h1.geolat,  2) 
               + pow(h2.geolng - h1.geolng, 2) ) as distance
from homes h1, homes h2
where h1.home_id = 12345 and h2.home_id != h1.home_id
order by distance

и индекс, скорее всего, не будет полезен вообще. Для геопространственных запросов вам нужно что-то вроде this.

Обновление: с помощью этого запроса:

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

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