Hash index in mysql

Posted on | Edited on | In

Just like the hash function in java
Dispatch field’s row pointer to a slot by field’s hash code.
If some rows have same hash code, then storage pointer to a list.

Prepare

Create a table like this:

1
2
3
4
5
CREATE TABLE animal (
   name   VARCHAR(50)   NOT NULL,
   weight DOUBLE(11, 3) NOT NULL,
   KEY `name` (`name`) USING HASH
) ENGINE=MEMORY;

mysql> select * from animal;
+————+————-+
| name | weight |
+————+————-+
| python | 20.500 |
| bear | 321.720 |
| lion | 221.980 |
| rabbit | 7.123 |
+————+————-+

Try

f represent the hash function:

1
2
3
4
f('python') = 2123
f('bear')   = 6417
f('lion')   = 8771
f('rabbit') = 2359

then:

Slot Hash value
2123 point to row 1
2359 point to row 4
6417 point to row 2
8771 point to row 3
  • Mysql calculate query’s hash value to decide
    which pointer or a pointer list, sql want.
  • Because index it’s self only store a short value,
    so hash code’s length is not determined by data type.

Some limit

  1. Index only have hash code, so can’t avoid table space i/o.
    But if table engine is memory, that’s not affected much.
  2. Hash index can’t be used to sort query collection.
  3. Not support part match, it only rely on hash code.
  4. Only support equal operation like:
    = <> in() not in()