Когда я должен использовать составной индекс?
- Когда я должен использовать составной индекс в базе данных?
- Какое увеличение производительности с использованием составного индекса)?
- Почему я должен использовать использование составного индекса?
Например, у меня есть 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`)
Если так:
- Зачем?
- Что такое увеличение производительности с помощью составного индекса)?
ОБНОВИТЬ:
Поскольку многие утверждают, что это полностью зависит от запросов, которые я выполняю, ниже приведен наиболее распространенный запрос:
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. Это выглядит хорошо или плохо. Прямо сейчас я НЕ использую составной индекс для geolat и geolng. Должна ли я быть?
9 ответов
Вы должны использовать составной индекс, когда вы используете запросы, которые извлекают из него выгоду. Составной индекс, который выглядит так:
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 отсутствует.
В вашем исходном примере составной индекс для двух измерений будет в основном полезен для запросов, которые запрашивают оба измерения или самое левое измерение, но не самое правое измерение само по себе. Если вы всегда запрашиваете два измерения, составной индекс - это путь, который не имеет значения, какой из них является первым (наиболее вероятно).
Представьте, что у вас есть следующие три запроса:
Запрос 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), только запрос I и запрос 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 на составном, поскольку он индексируется с помощью geolat, однако можно выполнять поиск по geolat или "geolat AND geolng" (поскольку geolng является индексом второго уровня).
Также ознакомьтесь с разделом " Как MySQL использует индексы ".
Там может быть неправильное представление о том, что делает составной индекс. Многие считают, что составной индекс можно использовать для оптимизации поискового запроса, если 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 колонка.
Как упомянул Will, MySQL обеспечивает поддержку пространственного расширения. Пространственная точка хранится в одном столбце вместо двух отдельных latlng колонны. Пространственный индекс может быть применен к такому столбцу. Тем не менее, эффективность может быть переоценена на основе моего личного опыта. Возможно, что пространственный индекс не решает двумерную проблему, а просто ускоряет поиск с использованием R-деревьев с квадратичным разбиением.
Компромисс состоит в том, что пространственная точка потребляет гораздо больше памяти, поскольку она использует восьмибайтовые числа двойной точности для хранения координат. Поправь меня, если я ошибаюсь.
Составные индексы полезны для
- 0 или более предложений "=", плюс
- не более одного предложения диапазона.
Составной индекс не может обрабатывать два диапазона. Я обсуждаю это далее в моей книге указателей.
Найти ближайший - Если вопрос действительно об оптимизации
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???
тогда ни один индекс не может действительно обрабатывать оба измерения.
Вместо этого нужно "думать из коробки". Если одно измерение реализовано с помощью разбиения, а другое реализуется путем тщательного выбора PRIMARY KEYможно получить значительно лучшую эффективность для очень больших таблиц поиска по широте и долготе. В моем последнем блоге подробно рассказывается о том, как реализовать "найди ближайшего" на земном шаре. Включает в себя код.
PARTITIONs являются полосами широтных диапазонов. PRIMARY KEY намеренно начинается с долготы, так что полезные строки, вероятно, находятся в одном и том же блоке. Хранимая процедура организует грязный код для выполнения order by... limit... и для выращивания "квадрата" вокруг цели, пока у вас не будет достаточно кофеен (или чего-то еще). Это также заботится о вычислениях большого круга и обработке линии даты и полюсов.
Составные индексы очень мощные, так как они:
- Обеспечить целостность структуры
- Включить сортировку по фильтрованному идентификатору
СОХРАНИТЬ СТРУКТУРУ ЦЕЛОСТНОСТИ
Составные индексы - это не просто другой тип индекса; они могут предоставить НЕОБХОДИМУЮ структуру для таблицы путем обеспечения целостности в качестве первичного ключа.
Innodb Mysql поддерживает кластеризацию, и в следующем примере показано, почему может потребоваться составной индекс.
Для создания таблиц друзей (т.е. для социальной сети) нам понадобятся 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 не существует
Таким образом, составной индекс может обеспечить целостность структуры.
ВКЛЮЧИТЬ СОРТИРОВКУ НА ФИЛЬТР
Очень часто сортировать набор записей по времени публикации (отметка времени или дата / время). Обычно это означает размещение по заданному идентификатору. Вот пример
Таблица 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 (Дата).
SQL QUERY
SELECT * FROM User_Wall_Posts WHERE user_id = 10 ORDER BY timestamp DES
Составной PK позволяет Mysql фильтровать и сортировать результаты, используя индекс; Mysql не нужно будет использовать временный файл или сортировку файлов для получения результатов. Без составного ключа это было бы невозможно и привело бы к очень неэффективному запросу.
Таким образом, составные ключи очень мощные и подходят больше, чем простая проблема "Я хочу найти column_a, column_b поэтому я буду использовать составные ключи. Для моей текущей схемы базы данных у меня столько же составных ключей, сколько и отдельных ключей. Не забывайте об использовании составного ключа!
Там нет черного и белого, один размер подходит для всех ответов.
Вам следует использовать составной индекс, когда рабочая нагрузка для вашего запроса выиграет от него.
Вам нужно профилировать нагрузку на ваш запрос, чтобы определить это.
Составной индекс вступает в игру, когда запросы могут быть полностью удовлетворены из этого индекса.
ОБНОВЛЕНИЕ (в ответ на изменение опубликованного вопроса): если вы выбираете * из таблицы, может использоваться составной индекс, а может и нет. Вам нужно будет запустить EXPLAIN PLAN, чтобы быть уверенным.
Составной индекс может быть полезен, когда вы хотите оптимизировать group by пункт (проверьте эту статью http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html). Пожалуйста, обратите внимание:
Наиболее важными предварительными условиями использования индексов для GROUP BY являются то, что все столбцы GROUP BY ссылаются на атрибуты из одного и того же индекса, и что индекс хранит свои ключи по порядку (например, это индекс BTREE, а не индекс HASH)
Для пространственного поиска вам необходим алгоритм R-Tree, который позволяет очень быстро искать географические области. Именно то, что вам нужно для этой работы.
В некоторые базы данных встроены пространственные индексы. Быстрый поиск в Google показывает, что в MySQL 5 они есть (и, глядя на ваш SQL, я предполагаю, что вы используете MySQL).
Я с @Mitch, полностью зависит от ваших запросов. К счастью, вы можете создавать и удалять индексы в любое время, и вы можете добавить ключевое слово EXPLAIN к вашим запросам, чтобы увидеть, использует ли анализатор запросов индексы.
Если вы будете искать точную пару широта / лонг, этот индекс, скорее всего, будет иметь смысл. Но вы, вероятно, будете искать дома на определенном расстоянии от определенного места, поэтому ваши запросы будут выглядеть примерно так (см. Источник):
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
и индекс, скорее всего, не поможет вообще. Для геопространственных запросов вам нужно что-то вроде этого.
Обновление: с этим запросом:
SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???
Анализатор запросов может использовать индекс только для geolat, индекс только для geolng или, возможно, оба индекса. Я не думаю, что он будет использовать составной индекс. Но легко опробовать каждую из этих перестановок на реальном наборе данных, а затем (а) посмотреть, что EXPLAIN говорит вам, и (б) измерить время, которое действительно занимает запрос.