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

This article will explain in detail how to optimize SQL for you. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Why optimize?

The throughput bottleneck of the system often appears in the access speed of the database, that is, with the running of the application, there will be more and more data in the database, the processing time will be slower accordingly, and the data is stored on disk. Read and write speed cannot be compared with memory

How to optimize

1. Design of database: design of database tables and fields, storage engine

2. Make good use of the functions provided by MySQL itself, such as the tuning of index and sentence writing.

3. MySQL cluster, sub-database and sub-table, read-write separation

The network has a lot of experience about the optimization of SQL statements, so this paper tries to build a tree on the optimization of Dao layer and database design, and gives two simple examples.

Example 1:ERP query optimization

Status quo analysis:

1. Missing associated index

2. Due to the limited performance of Mysql itself, the association support for multiple tables is not good. At present, the performance is mainly focused on the list query, which associates many tables.

Coping methods:

1 add the necessary index: view the execution record through explain, and add the index according to the execution plan

2 first count the primary key of the primary table of the business data to obtain a smaller result set, and then use the result set to associate the query

1) display the primary key of the business data according to the primary table and condition query

2) use the primary key as the query condition, and then associate other associated tables to query the required business fields.

3) when querying the main table, for the query conditions that need to be associated with other tables, it is necessary to set this condition before setting the table association.

For example, the following table TT_A TT_B TT_C TT_D assumes that the SQL before optimization is such a SELECT A.ID,. B.NAME,. C.AGE,.... D.SEX. From TT_A ALEFT JOIN TT_B B ON A.ID = B.ITEM_IDLEFT JOIN TT_C C ON B.ID = C.ITEM_IDLEFT JOIN TT_D D ON C.ID = D.ITEM_IDWHERE 1=1AND A.XX =? AND A.VV =?. So the optimized SQL is the first step SELECT A.IDFROM TT_A AWHERE 1=1AND A.XX =? AND A.VV =? Step 2 SELECT A.ID,.... B.NAME,. C.AGE,.... D.SEX. From (SELECT A. IDJ. FROM TT_A WHERE ID IN ALEFT JOIN TT_B B ON A.ID = B.ITEM_IDLEFT JOIN TT_C C ON B.ID = C.ITEM_IDLEFT JOIN TT_D D ON C.ID = D.ITEM_IDWHERE 1=1AND A.XX =? AND A.VV =?

Summary:

This optimization applies to list queries, because the conditions of a list query are generally linked to the main table, so take advantage of this to establish a key field index, and at the same time greatly reduce the amount of data in the main table through the limitations of query conditions. This makes it much faster to associate other tables.

Example 2: article search optimization

Suppose you want to do a post bar article search function, the simplest and most direct storage structure is to use a relational database to create such a relational database table TT_ARTICLES that stores articles:

So, if the current search keyword is "target", we can use string matching to match the CONTENT column:

Select * from ARTICLES where CONTENT like'% Target%'

This makes it easy to implement the search function. However, there is an obvious problem with this approach, that is, using% for string matching is very inefficient, so such a query needs to traverse the entire table (full table scan). The timing of a few or dozens of articles is not a problem, but if there are hundreds of thousands or millions of articles, this approach is not feasible at all. Apart from the fact that a single relational database table cannot hold such a large amount of data, it can hold it. To scan it, the time cost here is unimaginable.

Therefore, we are about to introduce the technology of "inverted index". In the scenario described earlier, we can split the concept into two parts: well, the ARTICLES table above still exists, but now we need to add a key table KEYWORDS, and the KEYWORD column needs to be indexed, so the record for this keyword can be found quickly:

Of course, we also need a relational table to combine the KEYWORDS table with the ARTICLES table, with KEYWORD_ID and ARTICLE_ID as the federated primary key

You see, this is actually a many-to-many relationship, that is, the same keyword can appear in multiple articles, while an article can contain many different keywords. In this way, we can first find the corresponding KEYWORD_ID from the KEYWARDS table according to the indexed keywords, then find the ARTICLE_ID in the above relational table according to it, and then find the corresponding article in the ARTICLES table according to it.

Summary:

This looks like three lookups, but because you go through the index each time, you don't have to scan the whole table, the speed is not slow when the amount of data is small, and when you use SQL implementation, this process can be put into a SQL statement. When the amount of data is small, the above method is good enough. This solves the performance problems caused by full table scans and string% matching queries.

Summary:

In a technical interview, if you can cite practical examples, or directly say that the questions and gains of your development process will add a lot to the interview, the answer should be more logical, not a little bit east or west. It's easy to get dizzy. For example, if you ask how to optimize SQL without immediately indexing the answer, you can answer like this:

Hello, interviewer, first of all, our project DB data volume encountered a bottleneck, resulting in very slow list query, poor user experience, in order to solve this problem, there are many methods, such as the most basic database table design, basic SQL optimization, MYSQL clustering, read-write separation, sub-database sub-table, adding cache layer in architecture, etc., their advantages and disadvantages. Synthesize these, and then combine the characteristics of our project, and finally who we choose in the technology selection.

If you answer the questions in such an orderly and well-founded manner and say so many knowledge points beyond the questions, the interviewer will feel that you are not only a person who can write code, but that your logic is clear and you have your own understanding and thinking about technology selection.

On how to optimize SQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.