Mysql Index Learning Tutorial _mysql

In MySQL, indexes can be divided into two types of hash index and Btree index.

Under what circumstances can I use a B-tree index?

1. Full Value Matching index

Like what:

Orderid= "123"

2. Matching the leftmost prefix index query

For example, create a federated index on the UserID and Date fields.
So if you enter UserID as a condition, then this userid can be used to the index, and if you enter date directly as a condition, you will not be able to use the index.

3. Matching column prefix query

For example: Order_sn like ' 134% ' can be used to index.

4. Matching Range value Query

Createtime> ' 2015-01-09 ' and createtime< ' 2015-01-10 '

5. Match the left front and the range match the other column exactly

Like what:

Userid=1 and createtime> ' 2016-9-18 '

6. A query that accesses only the index, called the overlay Index, and the index includes the data for the query column.

Restrictions on Btree indexes

1. Indexes cannot be used if you are not looking at the leftmost column in the index.

For example, to create a federated index:

OrderId, the Createtime field creates a federated index, and if you simply enter a createtime condition and do not have a orderId condition, you will not be able to use this index.

2. Columns that cannot be skipped when using an index.

Three columns:

Date, name, phone composition column and index, if only the date and the phone when the query, then only use the date as the index to filter.

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

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

The characteristics of hash index

The hash index is based on a hash table, and the hash index is used only if the query condition exactly matches all the columns in the hash index. can only be equivalent query.

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

Limit:

1. Must read two times, read the hash first to find the corresponding row, and then read the corresponding row data.

2.hash indexes cannot be used for sorting.

3. Only accurate lookups are supported, partial index lookups are not supported, and range lookup is not supported.

Hash conflict:

A hash index cannot be used for poorly-chosen fields, but rather for creating a hash index on a column such as a strong selection.

For example, do not create a hash index on the gender field.

Why should I use an index?

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

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

3. Index can turn random i/0 into sequential io

is the index more and better?

1. Indexing increases the cost of write operations

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

Policies to establish an index

1. An expression or function cannot be used on an indexed column

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

Change into:

select* from product where Out_date<date_add (Current_date,interval)

2. Index size cannot exceed a certain value.
The INODB index column size is 200 lengths.

3. Prefix and selectivity of indexed columns.

CREATE index idx_name on table (account);

4. Federated Index

How to select the Order of indexed columns.

1. Columns that are often indexed.

2. High selectivity in the column of priority.

3. Create an index on a small column.