Shulou(Shulou.com)06/01 Report--

This article mainly shows you the "sample analysis of the index in the MYSQL database", which is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of the index in the MYSQL database".

MYSQL INDEX there is nothing to say, is not to build an index, a statement to build an index, the establishment of the index can improve efficiency, related statements can use this index.

Is that really the case?

Here we have a table employee, which stores the employee's name and date of birth. Here we have the first hypothesis.

1 as long as the index is established, it will go to the index TRUE OF FALSE

The following figure shows the structure of the table.

We are now going to look up the names of people who were born before 1960, and here we set up an index about the date of birth in advance.

From the following figure, although the execution analyzer has issued an index, it is not used

This is why, we have a total of 300000 data, but we have to query the number of people greater than 1960, into the calculation of 110000 people who meet the conditions of the query, that is to say, in the query, nearly half of our data are eligible, natural MYSQL 5.7based on COST MYSQL database interpretation engine, will determine, take an index, in the callback data, than I directly want the full table scan of the refreshing.

Let's change the condition. We are looking for an employee born between January and February 1960. Sure enough, due to the reduction in the amount of data, we clearly see the index we established in the KEY item, and give a total of 1970 rows that meet the criteria.

From here we get a concept, the establishment of the index is also based on the ratio of the amount of data you search to the total amount of data, if the ratio is large, it will not go to the index, take the full table scan.

Therefore, it is wrong to say that when an index is established, the query will take the index.

We continue to assume that as long as the index is established and our data meets the condition of a small amount of data, the index will go. Is this TRUE or FALSE?

Let's continue with an experiment to build a federated index, because we have two queries, one is the number of people first_name is born in Georgi after 1960, and based on our previous experience, we set up a federated index to create a federated index of First_name and birth_date to satisfy such a query.

Create index idx_employee_birth_date_first_nameon employees (birth_date,first_name)

Explain select first_name,gender fromemployees where first_name = 'Georgi' and birth_date >' 1960-01-01'

What is the result? unexpectedly, there is no index. Is there too much data that meets this condition? after we have queried, there are only 106 people who meet this condition.

So how exactly are we going to build an index that MYSQL can use in this query? let's try to transposition the query conditions.

Explain select first_name from employees where birth_date > '1960-01-01' and first_name = 'Georgi'

Amazing things happen, the same query statement, just need to change the order in which the conditions are written, and then go to the index. Is this the end of the matter? Of course not. MYSQL has a surprise for us.

The order of the conditions of the query statement can be adjusted, so can the order of indexing be adjusted?

Create index idx_employee_first_name_birth_date on employees (first_name,birth_date)

We are doing a query to see what the results are, and the result is that our new index is actually used as the best index by the query. Why on earth?

First of all, programmers who have used SQL SERVER and ORACLE, or DBA, want to abandon the idea that the order of the indexes and query conditions we build should be basically the same.

After years of experience here, I have summed up this sentence.

Equal to the front, the scope is after, the query index should be the same, if there is a third party, like to put there, the condition is missing to see that, lack equals, it is finished.

If you suddenly see this sentence, you don't quite understand what it means. Let's do a demonstration below, and we'll remember it.

What is the third party? let's see the demonstration.

Explain select first_name from employees where first_name = 'Georgi' andlast_name =' facello' and birth_date > '1960-01-01'

You can clearly see that a condition that is not in the index is added to the middle query condition, but the current optimal index is still used in the query. Is it possible to put this condition somewhere else?

Explain select first_name from employees where last_name = 'facello' andfirst_name =' Georgi' and birth_date > '1960-01-01'

The answer is also yes. In the last sentence, if the condition is missing, if you look at that, if you lack it, it will be over.

Let's verify it again.

Explain select first_name from employees where hire_date > '1986-01-01' andlast_name = 'facello' and first_name =' Georgi'

Sure enough, if we have removed the scope query of the time field, the query can continue to follow the index we built, and the speed is not slow. So whether we remove the condition of the previous query FIRST_NAME, and whether the index can continue to be used.

Explain select first_name from employees where hire_date > '1986-01-01' andlast_name = 'facello' and birth_date >' 1960-01-01'

We can see very clearly that the index has been invalidated and has gone through a full table scan.

--

In fact, the relationship between MYSQL index and query is more complex, and that's all for today. Compared to other databases ORACLE, SQL SERVER's requirements are much more complex, so there are three things you'd better not do when using MYSQL databases.

1 the traditional way of table design, here not only refers to the TYPE of tables, including logic, how to use fewer tables, reduce the relationship between multiple tables, etc., to make the program do more things, not the database.

2 indexing and SELECT, DML statements must be repeated, that is to say, even if the statements are generated by the program, they have to be derived and carefully indexed according to the conditional order of these statements, so most of the SQL statements of MYSQL are handwritten rather than generated by the program architecture, taking into account this factor, otherwise it is likely to be a failed start.

3 index establishment, should be careful, multiple single indexes, to deal with composite query results may not be the same as you think, INDEX MERGE optimizer switch in some queries MERGE performance is not smart, MERGE function (5.7 has a great improvement), it is likely to unexpected secondary performance damage to the query.

Therefore, the gold content of a MYSQL DBA is much more complex than that of other databases.

The above is all the contents of the article "sample Analysis of Indexes in MYSQL Database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.