Types of MySQL Indexes

To understand the type of MySQL index, you have to first see how SQL Server is indexed.

SQL Server divides the indexes into:

Clustered index, nonclustered index

As you can see, the order of the clustered index and the data content are the same, and the nonclustered index order is independent of the data content.

Online to see a good chestnut:

We bought the dictionary, there are two kinds of directories:

1. Directories queried by Alphabet

2. Directories by radical Search

We will find that

Querying the dictionary in the first way, the alphabetical order in which the table of contents appears, coincides with the order in which the dictionary content appears. (Clustered index)

Querying the dictionary in the second way, the sequence of strokes displayed by the directory is completely inconsistent with the contents of the dictionary. (Nonclustered index)

However, this example has an inappropriate place ~!

Because whether we are searching by alphabet or radical, the result is the number of pages.

However, this is inconsistent with the actual situation of both indexes,

After the clustered index query, "Do not need to page back to find", the destination value is directly found .

After a nonclustered index query, you may need to do a clustered index query (two queries) again.

Composition:

Clustered index: Key Value (leaf node) is the real data in the table.

Nonclustered indexes: Key Value (leaf node), for Logical Pointers (another index page).

Find on the way:

Clustered index: The data is coherent, that is, when scoping is found, only the start and end are needed.

Nonclustered indexes: The data is scattered, and in the case of scoping, it needs to be checked by a pointer one by one.

Summary of Usage scenarios:

　　

It's a very important point. :

Most nonclustered indexes want to fetch data and need to pass the clustered index

Nonclustered indexes It is a pointer to the data, so it needs to be taken out of the data where it is actually stored. How to take it? The clustered index is the only thing that depends.

　　　　Give a chestnut to help understand:

You're sick, you have a headache, go to the doctor.

1. The doctor prescribed a prescription for you to take to the pharmacy.

2. The pharmacy prescription you with the list you are holding. (Two queries)

The " recipe " here is the pointer, and what we need is medicine, not " prescription ." So take it as a clustered index and go to the pharmacy to find it.

Say a little bit more:

A table has only one order, and all only one clustered index is allowed.

If a table does not have a clustered index, it is called a heap. Data rows in such a table do not have a specific order, and all new rows are added at the end of the table.  

Benefits of Clustered Indexes:
1) Data rows are stored in the primary key order, reading a small number of disk pages can be the adjacent primary key data read out.
2) Both the index and the data are stored in a B tree, and the data is read faster from the index
3) Use a query that overrides the index to use the primary key value that is contained in the leaf node.

Disadvantages of Clustered indexes:
1) The cost of updating the clustered index column is expensive because you want to force each updated data row to be moved to a new location
2) It is a good way to insert a new row in the primary key order, otherwise the performance overhead of updating the primary key or inserting to the random insert is significant.

On the misunderstanding of index use

1, the primary key is the clustered index

　　　　　　is an extreme mistake, a waste of a clustered index.

　　This is a common mistake, but everyone ignores this because of the "goodwill" of MySQL.

Two common MySQL engines for everyone:

1.InnoDB automatically binds the clustered index to the primary key

2.MyIsam all non-clustered indexes

So, the clutered keyword doesn't work in MySQL at all, but it's useful in SQL Server and Ooracle!

Why is that a waste of resources?

Because the clustered index corresponds to the order of the data, the greatest benefit of using a clustered index is the ability to quickly narrow the query based on query requirements and avoid full table scans.

And if we only set meaningless IDs as clustered indexes, we can hardly use the clustered index in conditional queries.

Here is a comparison chart of the online excerpt:

　　

2, as long as the index can significantly improve the query speed

No, only the right index can improve efficiency.

For a very simple chestnut, this index will be inefficient if there is a large amount of duplicate data in the indexed field.

3. The more fields the clustered index joins, the better.　

No, we usually only add the usual columns in the clustered index and the number is 1

4. is the clustered index necessarily better than nonclustered index performance? 　

No, it is generally said that the more conditions ( overriding the index ) are better than the clustered index

5. What does a clustered index and a nonclustered index describe in the database?

The index is described in the form of a btree tree,

the leaf node of the clustered index is the final data node, the leaf section of a nonclustered index is still an index node, but it has a pointer to the final data.

6. When the primary key is the table that creates the clustered index on the data insert why is it slower than creating a nonclustered index table on the primary key?

1. Clustered index because the index leaf node is the data page, if you want to check the uniqueness of the primary key, you need to traverse all the data nodes

2. Nonclustered index because the index already contains the primary key value, so look for the primary key uniqueness, only need to traverse all index pages on the row (the index storage space is less than the actual data),

7. It is faster to use an aggregate index than an order by using a generic primary key, especially in the case of small data volumes

Here, it is 3/10 faster to use the aggregate index than the general primary key for order by.

In fact, if the amount of data is very small, it is much faster to use the clustered index as the rank sequence than the non-clustered index, and if the data volume is large, such as more than 100,000, the speed difference between the two is not obvious.

The above mentioned "overwrite index" even better than clustered index, why?

Overwrite Index

An important feature of a nonclustered index.

Above, we say you're going to have to take the "prescription" and find another pharmacy. This cup is called " two queries "

However, if your prescription is written directly to the number of pharmacies, then you can take them yourself.

How to write clearly? This will use the overwrite index.

We say,

If an index contains (or overrides) the value of all fields (columns) that need to be queried, we call it " Overwrite index ".

Key (last_name, first_name, birthday) is the overwrite index for select Last_name,first_name from people.

It can be said that the "overlay index" is the full-match form of the combined index in MySQL.

The pros and cons of an overlay index are obvious:

Excellent:

1. Index entries are usually smaller than records, so MySQL accesses less data

2. Indexes are stored in order of value, less I/O is required relative to random access records

3, most of the engine can better cache index, such as MyISAM only cache index

4. Overlay index is especially useful for innodb tables, eliminating two queries

Lack:

Harsh query conditions, due to the combination of fields query, maintenance is quite troublesome

Attention

1, the overwrite index also does not apply to any index type, the index must store the value of the column

2. Hash and Full-text indexes do not store values, so MySQL can only use B-tree

3, and different storage engine implementation of the coverage index is different

4. Not all storage engines support them

5, if you want to use the overlay index, be sure to note that the select list value to remove the required column, not a SELECT *, because if all the fields are indexed will cause the index file is too large, the query performance is degraded, can not be used in order to take advantage of the overwrite index

Types of MySQL Indexes