MySQL Index Learning Tutorial

In MySQL, indexes can be divided into two types: hash index and B-tree index.

In what cases can B-tree index be used?

1.Full value match index

For example:

orderID="123

2.Matching the leftmost prefix index query

For example: create a composite index on the userid and date fields.
Then if userId is entered as a condition, this userid can be used in the index, and if date is directly entered as a condition, then the index cannot be used.

3.Matching prefix query of the column

For example: order_sn like '134%' so the index can be used.

4.Matching range value query

createTime>'2015-01-09' and createTime<'2015-01-10

5.Exact match of the leftmost column and range match of the other column

For example:

userId=1 and createTime>'2016-9-18

6.Only queries that access the index are called cover indexes, and the index includes the data of the query columns.

Limitations of BTREE index

1.If the search is not started from the leftmost column of the index, then the index cannot be used.

For example, create a composite index:

orderId, createTime fields create a composite index, if only input createTime condition, without orderId condition, then this index cannot be used.

2.When using an index, you cannot skip the columns of the index.

Three columns:

Date, name, phone composed of columns and indexes, if only input date and phone during the query, then only date can be used as index for filtering.

3.NOT IN and <> operations cannot use the index.

4.If there is a range query for some column in the query, then all columns to the right of it cannot use the index.

Characteristics of hash index

Hash index is implemented based on hash table, and hash index can only be used when the query condition matches all columns in the hash index exactly. It can only be used for equality queries.

For all columns in the hash index, the storage engine will calculate a hash code for each row, and the hash index stores the hash code.

Limitation:

1.Must be read twice, first read hash to find the corresponding row, then read the corresponding row data.

2.Hash index cannot be used for sorting.

3.Only supports exact search, does not support partial index search, nor range search.

Hash conflict:

Hash indexes cannot be used for fields with poor selectivity, and should be used to create hash indexes on columns with strong selectivity, such as

For example: Do not create a hash index on the gender field.

Why use indexes?

1Indexes greatly reduce the amount of data that the storage engine needs to scan. Indexes are smaller than data size.

2Indexes can help us sort to avoid using temporary tables. Indexes are ordered.

3Indexes can turn random I/0 becomes sequential IO

Is it better to have more indexes?

1Indexes will increase the cost of write operations.

2Too many indexes will increase the query optimizer and selection time.

Index creation strategy

1Expressions or functions cannot be used on index columns.

For example: select * from product where to_days(out_date) –to_days(current_date)<=30, out_date is an index column.

Change to:

select* from product where out_date<date_add(current_date,interval 30 day)

2Index size cannot exceed a certain value.
inodb index column size is 200 characters in length.

3Prefix and selectivity of index columns.

create index idx_NAME on table (account);

4Composite index

How to choose the order of index columns.

1Columns that are frequently indexed.

2Prioritize columns with high selectivity.

3Create an index for small columns.

Statement: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume relevant legal liabilities. If you find any suspected copyright content, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email for reporting, and provide relevant evidence. Once verified, this site will immediately delete the infringing content.)