mysql index learning tutorial_MySQL

In mysql, indexes can be divided into two types: hash index and btree index.

Under what circumstances can B-tree index be used?

1.Full value matching index

For example:

orderID="123"

2. Match leftmost prefix index query

For example: create a joint index on userid and date fields.
Then if you enter userId as the condition, then the userid can be used in the index. If you directly enter date as the condition, then the index cannot be used.

3. Match column prefix query

For example: order_sn like ‘134%’ This can use the index.

4. Match range value query

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

5. Exactly match the left front column and range match another column

For example:

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

6. A query that only accesses the index is called a covering index, and the index includes the data of the query column.

Limitations of BTREE index

1. If the search does not start from the leftmost column of the index, the index cannot be used.

For example, create a joint index:

orderId and createTime fields create a joint index. If you only enter the condition of createTIme without the condition of orderid, this index will not be used.

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

Three columns:

Date, name, and phone number form columns and indexes. If you only enter date and phone number when querying, you can only use date as an index for filtering.

3.NOT IN and <> operations cannot use indexes.

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

Features of hash index

Hash index is implemented based on hash table. Hash index can only be used when the query conditions accurately match all columns in the hash index. It can only be an equivalent query.

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

Limitations:

1. It must be read twice, first read the hash to find the corresponding row, and then read the corresponding row data.

2.Hash index cannot be used for sorting.

3. Only precise search is supported, partial index search is not supported, and range search is not supported.

hash conflict:

Hash indexes cannot be used on fields with poor selectivity, but must be used to create hash indexes on columns with strong selectivity.

For example: don’t create a hash index on the gender field.

Why use index?

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

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

3. Index can turn random I/0 into sequential IO

Are the more indexes, the better?

1. Indexes will increase the cost of write operations

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

Strategy for indexing

1. Expressions 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 the index column.

Change to:

select* from product where out_date

2.The index size cannot exceed a certain value.
inodb index column size is 200 length.

3. Selectivity of prefix and index columns.

create index idx_NAME on table (account);

4. Union index

How to choose the order of index columns.

1. Columns that are often indexed.

2. Columns with high selectivity are given priority.

3. Create indexes on small columns.