MySQL optimization index

Objective

A few days to take a time to "high-performance MySQL" looked at the index is very important, and then summed up to consolidate knowledge. This article uses the InnoDB storage engine as an index. Because this article does not say that the benefits of using the index, so do not write the results of the QPS and other tests please forgive us. My MySQL version is 8.0.11.

Directory

(i) Optimization of index use

① stand-alone columns

② Overlay Index

③ Index Matching

(ii) Index creation optimization

① prefix index and index selectivity

② Selecting the appropriate index order

③ do not create redundant and duplicate indexes

Index usage Optimization

We sometimes make the index useless by creating the appropriate index but using it incorrectly, so I summarize the index usage in the book. Before I do this, let me introduce the meaning of the field type and extra in the explain generation result, first of all, the result of the type that is often present.

(1) A maximum of one row in a const table is used for matching of primary key and unique index

(2) All full table scan

(3) ref uses index and matches the leftmost match

(4) Index: ❶

A. When the query is indexed, that is, all data can be obtained from the index tree (using index in extra);

B. A full-table scan of data rows from the index in index order (without Using index);

C. If the using index in the extra is present with the using where, the index is used to find the value of the key;

D. In the case of a single occurrence, a read index is used instead of a read row, but not used to find

Next we explain the results of extra:

(1) Using index uses the overwrite index.

(2) Some conditions in the using where Condition statement use an index, and other conditions need to be filtered in the table.

(3) The using Inex condition all conditions in the condition statement are in the index, but the required data is not in the index.

(4) The using Where;using index condition and the required data are in the indexes.

Stand-alone columns

Stand-alone column at a glance you think it's about creating an index on a single column but it's actually not. A "stand-alone column" means that an indexed column cannot be part of an expression or a function's parameter ❷. The previous sentence of this sentence is in the book: If you use a separate column, MySQL will not use the index. This is a bit ambiguous, "do not use the index" in the end is to include the index full scan or do not include the index full scan, if included is not consistent with the experimental results, if not included, then there is no problem. Don't say much nonsense or use the results to prove it. First of all, my database table structure is this way, as shown in:

　　

I created an index of two separate columns to test the expression and function as shown:

　　

Test Sql:explain Select age from user where Age = 2;

　　

From the test results we can see that type is ref (using Btree index), Extra is using index (overwrite index is used)

If we change the SQL statement to: Explain select age from user where age+1=2, the interpretation results are as follows:

　　

You can see that the query statement is indexed, but all the data of the index is scanned. Let's test it. If a function is used in a conditional statement, my SQL language is: EXPLAIN SELECT ID from user where to_days (birthday) >= 50000000; The test results are as follows:

　　

OK, and the result is index. As for the words in the book is right or wrong I am not clear, but you can test it yourself.

Overwrite Index

If you compare using an index to a wuling Wang Kwong, then you use an overlay index to open a Lamborghini (Lamborghini performance is up to you). Overriding an index simply speaking, the field and condition statements you want to query are in an index. Then again the process of proving, I create a new index as shown in:

　　

Then I use this SQL statement EXPLAIN SELECT first_name,age from the user where first_name= ' and age >0, in this SQL statement I query two different indexes of the column query results are as follows;

　　

In this SQL statement I used two indexes idx_fk_name and idx_fk_age, and the query's columns and query conditions were in both indexes, and the result of the test was the using where (the data needed to back up the table query). Next we use this SQL statement EXPLAIN SELECT last_name from user where first_name = ' Zhang ', using the result as shown:

Index Matching

If we write an SQL statement that conforms to the index matching principle, then we can scan all the data without indexing, and the result is that our query becomes more efficient. So what is the index matching principle? Let me summarize it briefly.

Full value Matching

A full-value match is a match between the query criteria and all columns in the index. As I created above the Idx_fx_name index. SELECT * from user where first_name= ' zhang ' and last_name = ' three ' This SQL statement is a full value match. Note that if written last_name= ' three ' and first_name= ' are also full value matches

Left-most matching

I divide the matching leftmost prefix and match column prefixes in the book into the leftmost match, because I think it all matches from the far left, as if it were on the Internet.

The leftmost prefix is the conditional query statement that you write for an index that fits in the same way that it matches from one of the left (the order of the measured conditional statements does not affect the principle of the leftmost match), and then take my Idx_fx_name index to give an example. such as SELECT * from user where last_name = ' three ' and select * from user where first_name = ' Zhang ' These two SQL statements query the index in a different way, the former is to scan all the index data, the second only Scanned part of the data for the index. The test results are as follows:

　　

　　

Match Range value

You can use scopes to query on the basis of matching the leftmost match.

Exact Match plus range matching

On the basis of matching the leftmost match, the last query condition can be queried by memory range.

　　

Index creation optimization prefix index and index selectivity

Let's first talk about the selectivity of the index. The selectivity of an index is the ratio of non-repeating index values (also known as cardinality, cardinality) to the total number of records (#T) of the data table, ranging from 1/#T到1之间 ❸. The common understanding of this sentence is that you choose to index (of course only one field, all or part of a field) the lower the repetition rate in this field column in the table, the better, because it can filter more rows of data. A prefix index is a prefix that can take a field as an index the prefix so and the index selectivity put together to solve when we choose a particularly long field as the index is a waste of space first and then the query when the speed will certainly be slower.

So how do we calculate the high and low index selectivity? The method is to use the keyword distinct and count to calculate the selectivity of the index. As I calculate the selectivity of first_name can be calculated as follows:

Select count (DISTINCT first_name)/count (1) as A1 from user;

If I want to calculate the first three characters of First_Name as an index, the calculation selectivity can be written like this:

Select COUNT (DISTINCT Left (first_name,3))/count (1) as A1 from user;

By constantly modifying the size of the included prefixes we can find an index of high selectivity.

Select the appropriate index sequence

In fact, choose the appropriate index sequence I think according to the actual situation to do analysis. But in general we all put the high selectivity in front, the other is to select the index sequence based on other factors such as sorting, grouping, and range conditions in the WHERE clause

Do not create redundant and duplicate indexes

Here are two questions to put in front of us what is the index of redundancy? What is a repeating index?

Duplicate index: An index with the same column is a repeating index. such as (A, B) and (B,A) are duplicate indexes.

Redundant indexes: A subset of an index is a redundant index. such as (A,b,c) and (A, b) (B,C) are redundant indexes.

As a general rule, it is best to not create duplicate indexes and redundant indexes, but in special cases we can create redundant indexes.

Summarize

The above is my study of "high-performance MySQL" book Summary. If you have any questions please feedback to me after all to communicate with each other to promote learning.

Learn from books or blogs

❶http://blog.51cto.com/lijianjun/1881208

❷ "High Performance MySQL" chapter fifth section III

❸ "High Performance mSQL" chapter fifth section II