This is the third day of my participation in the August More text Challenge. For details, see: August More Text Challenge

preface

Late last month the interview out waves that are hammer and come back to review, pressed to bubble to the red book, MySQL high-performance again take out to see it again, just read it again before, this major is a selective reading, mainly concentrated in the fifth and sixth chapter simply did not register with (much), this article is to summarize the today, The fifth chapter in MySQL High Performance – High performance index

What is an index

First step by step, let’s first understand what is the index, in fact, the index in essence, a little similar to the table of contents in our books, it can be convenient for us to find the corresponding content of the book, can quickly locate the location of the content.

There are two common index types in MySQL, one is Btree index and the other is hash index, but it should be noted that InnoDB is the default storage engine for the new version of MySQL, and the use of hash index in InnoDB is adaptive. This means that the hash index is not created manually, but the storage engine decides when to use it.

Preliminary study of Btree index

A Btree index can speed up data access because it allows you to search through the root node of the Btree index instead of using a full table scan.

The appropriate pointer is found by comparing the value of the node page to the value to be looked for, and eventually goes to the node at the next level, where the storage engine either finds the value or does not have the record.

In the Btree index, it is sequential, so when used, it can be used for scoping queries.

For example, in the following example, we create an index on pro_id

CREATE INDEX pro_index ON snapProduct(pro_id);
Copy the code

Pro_id can be indexed when we do an exact lookup or a range lookup later

EXPLAIN SELECT * FROM snapProduct WHERE pro_id > 2;
Copy the code

In order to use the Btree index in a query, one of the following conditions must be met

  1. All values match

Full value matching means that in the process of query, directly through the = ‘XXX’ method to query, for example, the index column is name, query mode

SELECT name, age FROM snapProduct WHERE name = 'test';
Copy the code

This is a query that can be indexed

  1. Matching the left prefix

When creating an index, you can create a joint index in addition to a common single-column index. To use the joint index, the query condition must meet the left-most prefix, that is, the index to be created is

CREATE INDEX pro_type_index ON snapProduct(pro_id,type);
Copy the code

In the process of querying, it must be

SELECT name FROM snapProduct WHERE pro_id = 10 AND name = 'test';
Copy the code
  1. Matching column prefixes

In the process of index matching, such as matching names beginning with J in the name column of the User table, this is called matching the column prefix.

  1. Matching range value

Matching range values, as the name implies, are matched in a range during the query, so indexes can also be used, for example

SELECT score FROM `imc_classvalue` WHERE user_id > 10 AND user_id < 100;
Copy the code

Note, however, that when the first column query uses a range query, the following filter statements do not use indexes.

For example,

SELECT score FROM `imc_classvalue` WHERE user_id > 10 AND user_id < 100 AND level_score = 9.1;
Copy the code

Such a query is not available for level_SCORE with the index on that column.

  1. Exactly matches one column, range matches another column

In the same way as the previous one, if the exact match and the range query are reversed, the indexes on both columns can be used again.

  1. Queries that access only the index

This kind of query is often said to overwrite the index, that is, the content of the column is overwritten by the index, that is, in the process of the query, only need to access the value of the index, you can read the content of the column to be queried, then this can also be used to query the index.

The hash index

There is another type of index in Mysql, hash index, which is created adaptively by Mysql’s Innodb storage engine. It is not created artificially by Mysql’s Innodb storage engine. It is created when the storage engine thinks you need to use hash index. This is something that we usually don’t need to deal with, so we’re not going to talk about it too much.

Advantages of indexing

In fact, an index is a bit like a table of contents in a book, which enables us to quickly locate the content we are looking for. To sum up, there are three advantages of an index:

1. Indexes greatly reduce the number of rows the server needs to scan.

2. Indexes help the server avoid sorting and temporary tables.

3. So you can change random I/O to sequential I/O.

Is indexing the best solution

In fact most of the time, for a small table data volume is not big, adopt the method of a full table scan, might be better than use index to search speed faster, for large tables, the index will greatly speed up the search efficiency, but when the table into a king, whether to establish or use the index, the performance of consumption is huge, In this case, another solution is needed.

The last

That’s it for today. See you next time. Bye