Mysql index learning tutorial, mysql index tutorial

Mysql index learning tutorial, mysql index tutorial

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

Under what circumstances can I use B-tree indexes?

1. Full value matching index

For example:

OrderID = "123"

2. Match the leftmost prefix index Query

For example, create a joint index on the userid and date fields.
If userId is input as the condition, the index can be used for this userid. If date is input as the condition, the index cannot be used.

3. query matching column prefix

For example, order_sn like '200' can use the index.

4. query matching range values

CreateTime> '2017-01-09 'and createTime <'2017-01-10'

5. Exact match of the left first and range match another column

For example:

UserId = 1 and createTime> '2017-9-18'

6. only access to the index query is called overwriting the index, and the index includes the data of the query column.

Limits on BTREE Indexes

1. If you do not start searching based on the leftmost column of the index, you cannot use the index.

For example, create a joint index:

The orderId and createTime fields create a joint index. If only the createTIme condition is input and there is no orderid condition, this index cannot be used.

2. When using an index, you cannot skip the index column.

Three columns:

Date, name, and phone number are columns and indexes. If only the date and phone number are input during query, the date can only be used as the index for filtering.

3. The not in and <> operations cannot use indexes.

4. If a query contains a range query for a column, indexes cannot be used for all columns on the right of the query.

Hash index features

The hash index is implemented based on the hash table. The hash index can be used only when the query condition exactly matches all columns in the hash index. It can only be an equivalent query.

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

Restrictions:

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

2. the hash index cannot be used for sorting.

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

Hash conflict:

Hash indexes cannot use poorly selective fields. Instead, they must be used to create hash indexes on columns with high selectivity, for example.

For example, do not create hash indexes on gender fields.

Why index?

1. indexing greatly reduces the amount of data that the storage engine needs to scan. The index is smaller than the data size.

2. indexes can help us sort data to avoid using temporary tables. The index is ordered.

3. The index can change random I/0 to sequential IO.

Is there more indexes, the better?

1. indexing will increase the cost of write operations

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

Index creation policy

1. expressions or functions cannot be used in index columns.

For example, select * from product where to_days (out_date)-to_days (current_date) <= 30, and out_date is the index column.

Changed:

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

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

3. prefix and index column selectivity.

Create index idx_NAME on table (account );

4. Joint Index

How to select the order of index columns.

1. columns that are often indexed.

2. Columns with high selectivity are given priority.

3. Create an index for a small column.