MySQL Index small note

MySQL indexes fall into the following categories: Fulltext, Hash,btree,rtree.

Fulltext: Full-Text Search index

Mainly to solve the problem of low query efficiency such as ' ad% ', which can only be used on MyISAM and InnoDB engines.

Hash: Hashed Index

The difference between a hash index and a B index

1:hash indexes can only be used for the equivalent of the value of the query. This is because the hash index is based on the hash algorithm

2:hash indexes cannot be used to avoid sorting operations on data.

3:hash indexes cannot be queried with partial index keys.

4:hash indexes cannot avoid scanning the table. Because multiple rows correspond to the same hash value, after locating the hash value

Compare the corresponding tables

5: When the hash conflict is relatively long, the efficiency will decrease. It could be lower than the B index.

BTREE: Tree Index

Tree-based index, which starts querying from the root node

Some considerations when using the index

1. Indexes do not include columns with null values

If there is a null value in a column, then a column with a null value will be invalidated when the composite index is created

2. Using a short index

If you create an index on a column of character type, it is best to specify a length. Compare varchar (20) If the data in the column

In the first 10 or 20 bits to determine uniqueness, there is no need to index the entire column. Short indexes can improve query efficiency and save storage

Space and I/O operations

3. Index column sorting

Only one index is used in a query statement, and if an index is used in the where, the index will not be used in order.

So try to avoid unnecessary sorting

4.like statements

Try to avoid using the like statement.%ad% or _ad in a like statement, queries with% and _ headers will invalidate the index

sdf% This does not invalidate the index

5. Do not perform calculations on columns

6. Do not use NOT and <>

Direct indexing improves query efficiency, but if you create too many indexes on a table, you can reduce the efficiency of the update

MySQL Index small note