Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to set the Hash index in MySQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to set the Hash index in MySQL".

In addition to the B-Tree index, MySQL provides the following indexes:

Hash index

Only supported by Memory engine, the scene is simple.

R-Tree index

A special index type of MyISAM, mainly used for geospatial data types

Full-text

A special index of MyISAM, mainly used for full-text indexing. InnoDB supports full-text indexing since MySQL 5.6,

Index / storage engine MyISAMInnoDBMemoryB-Tree index support HASH index does not support R-Tree index support Full-text index support

The most commonly used indexes are B-tree index and Hash index, and only Memory and NDB engines support Hash indexes. Hash index is suitable for key-value query, and query through Hash index is faster than B-tree index. However, the Hash index does not support range lookups such as =, and so on. Memory uses hash indexes only if it is "="

MySQL only supports functional indexing at 8.0. Before that, you can only index the first part of the column, such as the title title field, and you can only take the first 10 characters of title. This feature greatly reduces the size of the index file, but the prefix index also has shortcomings, which is invalid in order by and group by operations.

Create index idx_title on film (title (10)); 1 characteristics

There is only an array, use a hash function to convert the key to a certain memory location, and then put the value in that location of the array. Hash conflicts are naturally possible when using hash, and MySQL adopts zipper method to solve them.

The Hash index is implemented based on the Hash table, and the hash index can be used only if the query criteria exactly match the columns in the Hash index. For all columns in the Hash index, the storage engine calculates that hashcode is stored in one hashcode,Hash index for each row.

For example, a table that maintains ID card number and name, looks up the corresponding name according to ID card number, and its hash index is as follows:

For example, if we want to check the ID_card_n4 corresponding to username:

Calculate A from ID_card_n4 through hash function

Traverse in order to find User4

The four ID_card_ n values are not necessarily incremented, so even if you add a new User, it is fast, just append it later. Of course, the disadvantage is also obvious, it is not orderly, so the interval query speed of hash index is very slow. For example, if you want to find all users whose ID numbers are in the [ID_card_X, ID_card_Y] range, you have to scan the table.

2 defects of Hash index

You have to look it up twice.

Partial index lookup and range lookup are not supported

There may be hash conflicts in hash codes. If the hash algorithm is not well designed and there are too many collisions, the performance will become worse.

The index holds hash values, so only support

And IN.

Cannot be sorted by manipulating the index, because the storage is calculated by hash, but the calculated hash value is not necessarily equal to the stored one, so it cannot be sorted.

Full table scanning cannot be avoided, just because non-unique value hash indexes are supported in the memory table, that is, different index keys may have the same hash value.

Because the hash table is a data structure that accesses the memory storage location directly according to the keyword, so using the hash index of its principle, all the data files need to be added to memory, which consumes a lot of memory.

If all queries are equivalent queries, then hash is really fast, but in fact range lookups have more data.

Intelligent processing keys are worthy of full value matching.

The query Hash function determines the size of the index key

To enable InnoDB or MyISAM to support hash indexes, you can do so through pseudo-hash indexes, which are called adaptive hash indexes.

You can add a field to store the hash value, index the hash value, create a trigger when inserting and updating, and automatically add the calculated hash to the table.

The hash table structure is suitable for scenarios where there are only equivalent queries, such as Memcached.

3 case application

If there is a very large table, for example, when users log in, they need to retrieve users through email, and if you index directly in the email column, in addition to index interval matching, string matching should also be carried out. It is better to have a short email. If it is long, the query costs a lot. At this point, if the hash index is established in email and the query is queried by int, the performance is much faster than the string alignment query.

Hash algorithm

To build a hash index, we must first choose the hash algorithm, the CRC32 algorithm mentioned by "high-performance MySQL".

INSERT UPDATE SELECT operation

Add fields with hash values to the table:

ALTER TABLE `User` ADD COLUMN email_hash int unsigned NOT NULL DEFAULT 0

The next step is to update the email_hash field automatically during UPDATE and INSERT, using triggers:

DELIMITER | CREATE TRIGGER user_hash_insert BEFORE INSERT ON `User` FOR EACH ROW BEGINSET NEW.email_hash=crc32 (NEW.email); END; | CREATE TRIGGER user_hash_update BEFORE UPDATE ON `User` FOR EACH ROW BEGINSET NEW.email_hash=crc32 (NEW.email); END; | DELIMITER

So the SELECT request becomes:

SELECT `email`, `email_ hash` FROM `User` WHERE email_hash = CRC32 ("xxoo@gmail.com") AND `email` = "xxoo@gmail.com"

+-+ +

| | email | email_hash |

+-+ +

| | xxoo@gmail.com | 2765311122 | |

+-+ +

AND email = "xxoo@gmail.com" is to prevent inaccurate data during hash collisions.

Thank you for reading, the above is the content of "how to set Hash index in MySQL". After the study of this article, I believe you have a deeper understanding of how to set Hash index in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.