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

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

Например, у меня есть 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 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. Это хорошо или плохо. Прямо сейчас я НЕ использую составной индекс для геолата и геолинга. Должна ли я быть?

9 Solutions collect form web for “Когда следует использовать составной индекс?”

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

 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 (который использует первую часть индекса composit) используют индексы. В этом случае для запроса 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 .

Возможно, было неправильное представление о том, что делает составной индекс. Многие считают, что составной индекс может использоваться для оптимизации поискового запроса, where предложение 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-деревьев с квадратичным расщеплением .

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

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

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

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

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

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

Для создания таблиц друзей (т. user_id, friend_id Для социальной сети) нам нужны 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 еще не существует.

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

ВКЛЮЧИТЬ СОРТИРОВАНИЕ НА ФИЛЬТРОВАННЫЙ ИДЕНТИФИКАТОР

Очень часто сортировать набор записей по времени сообщения (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 поэтому я буду использовать составные клавиши. Для моей текущей схемы базы данных у меня столько же составных клавиш, сколько одиночных ключей. Не обращайте внимания на использование составного ключа!

Композитные индексы полезны для

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Я с @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 ??? 

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

Давайте будем гением компьютера.