Hash indexes of MySQL Indexes

The following describes how to create a MySQL hash index. The idea is very simple. Create a pseudo hash index on the Standard B-Tree index. It is not the same as a real hash index,

The following describes how to create a MySQL hash index. The idea is very simple. Create a pseudo hash index on the Standard B-Tree index. It is not the same as a real hash index,

The Hash Index is based on the Hash table. It is only useful for exact search of each column in the Index. For each row, the storage engine calculates the Hash Code to be indexed. It is a small value and may be different from the Hash Code of other rows. It stores the hash code in the index and saves a pointer to each row in the hash table.

In mysql, only the memory storage engine supports explicit hash indexes. If multiple values have the same hash code, the index stores row pointers in the same record of the hash table as a linked list.

There are still many details about the hash index. Because myISAM and innodb do not support it, I will not explain it here.

The following describes how to create a MySQL hash index.

The idea is very simple. Create a pseudo-Hash index on the Standard B-Tree index. It is not the same as the real hash index, because it still uses the B-Tree index for search. However, it will use the hash value of the key for search, rather than the key itself. All you need to do is to manually define the hash function in the where clause.

Example: URL search.

URLs usually lead to larger B-Tree indexes because they are very long. Generally, the URL table is searched as follows.

Mysql> select id from url where url = 'HTTP: // www.linuxidc.com ';

However, if you remove the index from the url column and add an indexed url_src column to the table, you can query it as follows:

Mysql> select id from url where url = 'HTTP: // www.linuxidc.com 'and url_src = CRC32 ('HTTP: // www.linuxidc.com ');

The mysql query optimizer notices that the url_src column has very small and highly selective indexes, and uses the values in it for index search. Even if there are several columns with the same url_src value, it is easy to make a precise comparison to determine the required rows. The alternative is to regard the complete URL index as a string, which is much slower.

One disadvantage of this method is to maintain the hash value. You can perform manual maintenance. In MySQL or later versions, you can use triggers for maintenance.

1. Create a table:

Next, create a trigger. We will temporarily update the command separator so that the semicolon can be used in the trigger:

The rest is to verify that the trigger automatically maintains the hash value.

If this method is used, SHA1 () and MD5 () should not be used. They return a long string, which wastes a lot of storage space and slows down the comparison. They are strongly-encrypted functions and are designed to avoid task conflicts. This is not our goal. A simple hash function can ensure acceptable conflict rates while providing good performance. Of course, if the table has many rows and CRC32 () Conflicts, you must implement your own 64-bit hash function. Make sure that your function returns an integer instead of a string.

Mysql> select conv (right (md5 ('HTTP: // www.linuxidc.com/'), 16), 16, 10) as hash64;