Test the hash index and principles of MySQL

Test the hash index and principles of MySQL

1. hash index: (hash index) is implemented based on the hash table. Only queries that precisely match the index column can achieve the effect. For each row of data, the storage engine calculates a hash code for all index columns. The hash code is a small integer, the hash codes calculated by rows with different key values are also different.

2. Only Memory storage engine explicitly supports hash indexes, but the principle can be used in pseudo hash indexes.
The table structure is as follows:

Create table test_hash (
Fname varchar (100) not null default '',
Lname varchar (100) not null default '',
Index using hash (fname)
) Engine = memory
Insert into test_hash values ('zhang', 'san'), ('Tao', 'shihan'), ('lil', 'si ');

　　

3. Suppose there is such a hash function f (), which returns the following hash code integer
F ('Tao') = 2323
F ('zhang ') = 7437
F ('lil') = 8784

4. A hash table stores mappings. The slot numbers are sequential, and the value data rows are not
Slot Value (Value)
2323 points to 2nd rows of data
7437 points to 1st rows of data
8784 points to 3rd rows of data

5. select lname from test_hash where fname = 'Tao' \ G;
Mysql first calculates the hash value of 'Tao', f ('Tao') = 2323, then searches for the corresponding row in the hash index table, and finds
2nd rows of data, directly query 2nd rows of data, and determine that the fname is tao to ensure correctness

6. Hash conflicts: different hash codes are worth the same. For example, f ('Tao') = 2323 f ('wang') = 2323. In this case, a hash conflict occurs.
When a hash conflict occurs, the same data is stored in the linked list, and the consistent data is found in the traversal table.

7. features:
1) hash indexes only contain hash codes and pointers, and do not store data field values.
2) hash index data is not stored in order, so it cannot be used for sorting.
3) because the hash code to be calculated through the query value, the hash index does not support partial matching, does not support range searches, and only supports equivalent comparison queries.
4) when there are many hash conflicts, the efficiency will be reduced.

On the InnoDB Storage engine, you can implement pseudo-Hash indexes based on the above principles, and use the default B-Tree indexes.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151364.htm