Mysql hash index optimization and mysqlhash Index

Mysql hash index optimization and mysqlhash Index

Create a table

Create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Msg 'varchar (20) not null default '',
'Crcmsg 'int (15) not null default '0 ',
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 3 default charset = utf8

// Insert data

Insert into t1 (msg) values ('www .baidu.com '), ('www .sina.com ');

Add indexes to the msg and crcmsg fields respectively.

Alter table t1 add index msg (5 ));

Update t1 set crcmsg = crc32 (msg );

Alter table t1 add index crcmsg (crcmsg );

Start testing

Last data table structure

Increase the speed of database queries based on the length of key_len.

I hope you will enjoy your work and have a good time in your own small test.

 

MySQL database optimization (7): How does MySQL use indexes?

The index is used to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If the field has an index, MySQL can quickly determine the location where the data file is located to search for records without searching all the data. If the table contains 1000 records, this is at least 100 times faster than reading data sequentially. Note: If you need to access almost all 1000 records, sequential reading will be faster, because this will minimize the number of disk searches.
Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored as B trees. Only Spatial fields are stored in the R tree. MEMORY (HEAP) tables support hash indexes.
By default, strings are automatically compressed with spaces in the prefix and suffix.
Generally, indexes can be used in the following situations. The unique feature of hash indexes (for MEMORY tables) will be discussed later.
Find the record that matches the WHERE clause as soon as possible.
Exclude records based on conditions. If multiple indexes are available, MySQL usually selects the index with the least records.
Query records from other tables during table join queries.
You want to find its MIN () or MAX () value on the specified index field key_col. The optimizer will check the index
Before the key_col field, check whether the WHERE key_part _ # = constant clause is used in other indexes. In this case,
MySQL performs an index search separately for MIN () or MAX () expressions and replaces them with constants. When all expressions are replaced with constants, the query returns immediately. As follows:
Select min (key_part2), MAX (key_part2) FROM tbl_name WHERE key_part1 = 10;
Sort or group a table. When grouping or sorting an available leftmost prefix index (such as ORDER
BY key_part1, key_part2 ). If all the indexes are sorted by DESC, the indexes are sorted in reverse order.
In some cases, the query can be optimized so that results can be directly obtained without computing data. When the query uses a numeric field in the table and the field is the leftmost part of the index, the results may be obtained quickly from the index tree:
SELECTkey_part3FROMtbl_nameWHEREkey_part1 = 1
Assume that the SELECT statement is as follows:
If there is a multi-field index on col1 and col2, the corresponding records can be obtained directly.

What is the difference between the mysql btree index and the HASH index?

It seems that the HASH index can only be used in the MEMORY/HEAP type table. This format has never been used.