Will optimize, will you really optimize it? In fact, you may really lack an understanding of the "database article"

In fact, before writing this blog, I also feel that I will point optimization, at least know not to use the "*" number ah, to the regular query column to create an index AH what, in fact, are not as simple as everyone thought, in fact, there are many things behind them, it is worth our understanding and learning.

Share with you a discussion of a question, which query is fast for subqueries and connection queries ? The most important purpose is to help you in the future development on the road do not make my mistake, see is to earn, haha.

My answer is to connect the query. Because this is my experience in the practice of the previous days, feeling quite deep, in to our company's homepage executed a statistical SQL statement, when I was a subquery written SQL statements, The first execution of the 21.783SEC, the second implementation of the 5.178sec, at that time feel very awkward, a site of the first page load to go through 3-4 seconds to brush out statistics, too outrageous, so I tried to use the connection query to transform a bit, sure enough, the query speed to go up, then feel a kind of conquered something like, really, Super sense of accomplishment, before really is write code, did not pay attention to this aspect too much, this time drilled a really good.

Directly, to show you the execution time of query and connection query, (however, the performance of a SQL statement can not be measured by the query time, previously seen in a blog post, pretty Feel)

First, sub-query statistics

Performed a 5.198sec

Second, the connection query statistics

performed a 0.010sec.

(Why connection query than sub-query block, this problem I have been summing up, compare their query principle is how to scan the table structure, the same has not been able to summarize a comfortable answer, such as a good summary of the late to share, but also hope that master guidance, hope that the expert message to help, is trying to understand ... ）

Well, after sharing a little bit with you, let's get down to the chase and talk about simple optimizations and how they're going to improve performance.

1, the query process of MySQL: When we send a SQL statement through the MySQL client, what exactly does the MySQL server do, and what process has gone through, I still borrow the diagram to explain it

Simply give us a little explanation.

MySQL database is also the client/server communication protocol mode, at any moment, is either the client sends a request to the server, or the service side to the client to respond to the query results, these two actions can not occur simultaneously. Once one end begins to send a message, the other end receives the entire message to respond to it, so we cannot send a request message into small chunks and have no way to control traffic.

When the client sends the SQL statement to the server, the client first makes the query statement into a separate data packets to the server, and if the query statement is long it needs to set the parameters, if it is too large, the server may refuse to accept more data and throw an exception.

In the same way, the server responds to the client when it gets the data from the storage engine, and the server responds with a lot of data that cannot be made into a single packet, which can be made into multiple packets. However, when the server responds to a client request, the client must fully receive the entire return result, rather than simply taking the previous few results and then having the server stop sending. Therefore, in the actual development, it is very good to keep the query simple and return only the necessary data, to reduce the size and quantity of packets between the communication is a great habit, which is also one of the reasons for avoiding the use of select * and adding limit restriction in the query.

2, the query as far as possible to use limit to do a limited query, the reason above has been analyzed, the server may respond to a large number of data packets to the client, we usually only get the first few data, the following data if necessary, you can use paging query, edge with search.

3, there is also a misunderstanding is that when we design the table structure, as far as possible to set the data type of the field to the minimum, enough on the line, do not indecisive, take the int type, we are accustomed to the int type of the field set length, in fact, you set not set are no egg use, The int type is a 16-bit storage space, then its storage scope has been determined, so int (1) and int (20) are the same for storage and calculation, let's change it, I just learned.

4, we are querying the database, may use a number of scope conditions to make restrictions, such as the search for a time period of a certain age group of employees, when it is necessary to accept that MySQL can not use both the index of the field, it will only select one of the fields of the index to do the query.

5, it is recommended that we often delete some of the list of infrequently used indexes, periodically remove some long unused indexes is a very good habit. (Here is to ask, do not think the index is the best tool to query, if you sometimes query a very small table, it is recommended not to index, the direct full table scan effect is better).

【

The optimization of the learning journey has just begun, the latter will bring you more experience, very much hope that you can see and give advice.

Reference blog:

http://www.cnblogs.com/zishengY/p/6892345.html,http://blog.csdn.net/luckarecs/article/details/7165472

】

Will optimize, will you really optimize it? In fact, you may really lack an understanding of the "database article"