SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 24.01.2012 23:38:59

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Выбор индекса - hash или btree

Доброго времени суток!

Поделитесь пожалуйста соображениями по сабжу.

Есть две таблицы, допустим, таблица талонов (request, ключевое поле id) и таблица, содержащая историю событий, происходящих с талонами (request_event), request_event содержит в себе поле request_id, которое связано вторичным ключом с полем id таблицы request.

По ходу работы часто составляются запросы, в которых эти две таблицы джоинятся on request.id = request_event.request_id.
Индекс по умолчанию - BTREE.
А в секретных материалах сказано, что индексы типа hash работают побыстрее, но по они не используются, если приходится выбирать по >= или <=, впрочем в данном случае (join табличек) это и не требуется.

Суть вопроса - какой тип индекса для такой связки лучше создать и почему? Если в более общем виде - в каких случаях какой индекс лучше выбирать?

В этом (Паша, если ты это читаешь, то сори за некроманию wink ) сказано, что регулировать тип создания индекса нельзя (кроме memory-табличек), но на mysql 5.1 на табличке типа myisam вполне создался hash index и explain на него (где выборка шла по прямому равенству) показал, что индекс создаётся.

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


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#2 25.01.2012 00:11:55

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Выбор индекса - hash или btree

Теоретически HASH-индексы занимают меньше места и должны быстрее работать (при выборке с точным равенством).

Какая конкретно реализация в MyISAM в 5.1 нужно проверить: самые простые проверки - что он другого размера, чем BTREE на такой же таблице; тест производительности на большой таблице.

Нужно не забывать, что для сортировки они тоже не работают.

Неактивен

 

#3 25.01.2012 00:16:26

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Выбор индекса - hash или btree

В документации не описана возможность HASH индексов для MyISAM таблиц: http://dev.mysql.com/doc/refman/5.5/en/ … index.html

Следует проверить индекс более внимательно - например, работает ли он для нечеткого равенства.

Неактивен

 

#4 25.01.2012 14:16:57

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Выбор индекса - hash или btree

Более того, я подозреваю, что никто не пробовал использовать HASH в MySQL
на больших объемах данных. Он действительно быстрее: в случае хорошего выбора
хэширующей функции он дает O(1) (а в случае плохого выбора — O(n)). Дерево
же всегда дает O(ln(n)), но вся эта теория хорошо работает тогда, когда все стра-
ницы индекса одинаково быстро доступны, что в реальных условиях, очевидно, не
так: некоторые в ОЗУ, а некоторые на диске. В результате я совсем не уверен, что
disk-based HASH будет сильно быстрее дерева.

Неактивен

 

#5 25.01.2012 14:19:37

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Выбор индекса - hash или btree

Ну то есть к тому идёт, что стоит оставить hash-индексы для memory-таблиц, а для myisam использовать "честные" btree?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#6 25.01.2012 14:35:04

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Выбор индекса - hash или btree

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

Неактивен

 

#7 25.01.2012 14:36:11

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Выбор индекса - hash или btree

Ок, проведу тесты, приложу сюда.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#8 27.01.2012 02:34:11

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Выбор индекса - hash или btree

deadka, Очень интересно. Потестируй и расскажи !

Неактивен

 

#9 05.02.2012 02:41:59

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Выбор индекса - hash или btree

Да, складывается ощущение, что в таблицах типа MyISAM формально hash-индексы поддерживаются, но начинка у них - btree.

Создал две таблицы:


 CREATE TABLE `t_5238_btree` (
  `t` int(11) NOT NULL AUTO_INCREMENT,
  `d` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`t`),
  KEY `ind_d` (`d`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=7776001 DEFAULT CHARSET=latin1;

CREATE TABLE `t_5238_hash` (
  `t` int(11) NOT NULL AUTO_INCREMENT,
  `d` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`t`),
  KEY `ind_d` (`d`) USING HASH
) ENGINE=MyISAM AUTO_INCREMENT=7776001 DEFAULT CHARSET=latin1;
 


Вставил внутрь одинаковые данные.

Размер MYI-шных файликов совпадает до байта, diff не дал никаких различий.

Что касается планов выполнения запросов, то тоже все к тому, что btree:


mysql> explain select * from t_5238_btree where d>=10 and d<=20;
+----+-------------+--------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | t_5238_btree | range | ind_d         | ind_d | 9       | NULL | 8560 | Using where |
+----+-------------+--------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t_5238_hash where d>=10 and d<=20;
+----+-------------+-------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | t_5238_hash | range | ind_d         | ind_d | 9       | NULL | 8560 | Using where |
+----+-------------+-------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)


Хотя в случае нормального hash-индекса в запросах с нечетким равенством индекс не будет использоваться вовсе.

Прямо хоть в bugs.mysql.com писать - зачем вводят дельфинарий в заблуждение...

Отредактированно deadka (05.02.2012 02:47:34)


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#10 05.02.2012 02:52:54

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Выбор индекса - hash или btree

В innodb тоже создается HASH-индекс? Наверное в bugs написать стоит, так как это либо бага либо бага документации (если они хотят такое поведение для совместимости, то его нужно описать).

Неактивен

 

#11 05.02.2012 23:22:39

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Выбор индекса - hash или btree

В innodb тоже создаются фальшивые HASH-индексы. Результаты explain-а те же самые.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson