MySQL HASH and BTREE index

Разница между HASH и BTREE индесами в MySQL

Hash:
- представляет собой результат функции по ключу
- не видит растояния до ближайших элементов
- не может использоваться для операций диапазонов > и <
- сравнивание ключа целиком
- линейный при индексировании
- О(1) при вытаскивании значения

BTREE:
- индекс индексов
- сбалансированное дерево в листьях которого гаранируется одинаковое количество предков
- поиск по диапазонам, =, >, >=, <, <=, or BETWEEN operators
- для поиска можно использовать префикс ключа
- может быть использован для сравнения LIKE, если аргумент LIKE является постоянной строкой, которая не начинается с шаблонного символа

Например, следующие SELECT, операторы используют индексы:
SELECT * FROM имя_таблицы WHERE key_col LIKE 'Patrick%';
SELECT * FROM имя_таблицы WHERE key_col LIKE 'Pat%_ck%';
- В первом заявлении, только строки с "Patrick" <= key_col < 'Patricl' считаются
- Во втором заявлении, только строки с "Pat" <= key_col < 'Pau' считаются.

Следующие SELECT не используют индексы:
SELECT * FROM имя_таблицы WHERE key_col LIKE '% Patrick%';
SELECT * FROM имя_таблицы WHERE key_col LIKE other_col;
- В первом заявлении LIKE значение начинается с символа подстановки.
- Во втором заявлении LIKE значение не является постоянной величиной.


InnoDb и MyISAM:
- первичный индекс (PRIMARY KEY) только BTREE

MEMORY
- первичный индекс (PRIMARY KEY) по умолчанию HASH, можно выбрать BTREE

NDB
- можно использовать как BTREE, так и HASH


MySQL 5.7

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}