MySQL database index summary

1. B-Tree Index

Example

Create Table 'test'. 'student '(
'Name' varchar (45) null,

'Dob' date null,

'Desc' varchar (45) null,

Index 'B' using btree ('name' ASC, 'dob' ASC, 'desc' ASC ))

Engine = MyISAM

Default Character Set = utf8

Collate = utf8_general_ci;

How btree indexes work

1) match the full name. For example, you can find the line with name = "Jack" and DOB = "1991-09-09" and desc = "good ".

2) match the leftmost prefix. For example, you can search for all persons with name = "Allen ".

3) match the column prefix. For example, you can find the person whose DESC starts with G.

4) range value matching the leftmost Prefix: for example, you can find the person with name like "JAC %"

5) match the exact value of a column and the range value of a column. For example, you can find name = "Jack" and desc like "goo %"

 

The btree index does not work.

1) Search for rows that do not start from the leftmost of the index: for example, search for rows with DOB = "1991-09-09", DESC = "good", or name like "% ack"

2) skip the index column. For example, the Dob is skipped here for name = "Jack" and desc = "good ".

3) the storage engine does not optimize the column on the right of the first range condition: for example, name like "Jack %" and DOB = "1991-09-09" and desc = "good ", DOB and desc won't be optimized here

 

2. Hash Index

The hash Index uses a hash table to store the key after the index is hashed. The value points to the actual data address. It is an efficient index, but it also has its limitations:

1. the hash index is unordered, so it only applies to precision queries such as =, <=>, in (), but does not support range queries such as>, <,.

2. different values of the hash index may have the same key after being hashed. These same keys will be stored in the corresponding address of a key using a linked list. When there is a collision value in the query, the hash index first finds the linked list through the hash value and finds the corresponding data address by matching the values in the linked list. When the collision volume is large, the hash index efficiency will be reduced.

Generally, a hash index is used to index a long field column. It compresses the index key of a long field column into a short hash value. For example:

To create a long URL index, you can use the hash index. Because the URL is too long, directly creating an index will make the index huge. We can create an index column for the URL, in this way, the index length can be compressed.

If the database engine does not support hash indexes, we can simulate hash indexes by ourselves,

For example, a table contains URL columns and url_crc columns, and then creates a btree index for url_crc.

Use the following query when querying

Select * From url_table where url = "www.baidu.com" and url_crc = CRC32 ("www.baidu.com ");

In this way, the hash index can be used and accurate results can be obtained.

If url_crc is directly used for query, a collision may occur, for example

Select * From url_table where url_crc = CRC32 ("www.baidu.com ");

It is possible to find two different URLs because the CRC32 values of these two URLs are the same.

 

3. spatial indexes

4. Full-text index: A special index exclusive to MyISAM. It is used to search for large text segments and search for text keywords. The index type is Fulltext. It is operated using the match and against functions. For example:

Select * from news where match (content) against ("the president ");

Here, we first index the full text of the content column, and then look for rows in the content containing the President keyword