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

This article introduces the relevant knowledge of "what are the knowledge points of SQL optimization". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

In today's Internet era, there are no more than two difficult problems to be solved, one is information security, and the other is data storage. Information security is based on data storage. When a company is founded to develop into a team of hundreds or even thousands of people, the company's business volume is on the rise, and there will be more and more customers and users. The previously designed table structure may seem unreasonable, and the relationship between the table and the table does not have a stable division of business functions, which shows that the spare fields of the related tables are becoming more and more insufficient or even new fields. in the worst case, there will be data redundancy between different business tables. Thus some design problems are exposed, which is one of the optimization points of SQL: the rationality of database table structure design. In recent years, big data has become more and more popular, and big data is also one of the means to solve the problem of data storage, whose purpose is to collect valuable information from massive data and store it in the database. Because the traditional database with a large amount of data cannot store so much information, it is necessary to analyze valuable information before deciding whether to persist it or not.

Optimization point

Prerequisite knowledge

Learn to use explain keywords to check the performance of SQL statements. Explain seems to support select, update, and delete statement analysis from MYSQL5.6.3. Previously, only select statements were supported. Now we generally use 5.7, so there is no need to worry too much. I won't go into detail here on how to interpret the performance information from explain output.

One of the optimizations-from the perspective of database design

The business connection between the table and the table should be clear: there is actually a business connection between the tables, such as: class (primary key:class_id, all class information tables), student (primary key:student_num, all student information tables), student_class (primary key:stu_class_id, all students' class information tables) there are three tables, if you need a teacher's information table for which class teacher So the correct way at this time is to create a new teacher and teacher_class table instead of inserting the teacher's information directly into the student table and using a field to identify whether it is a teacher or a student. If you see this, you may think, "I'm sure I'll do it the right way," but this is just an example. In fact, in the actual project development process, the table and table structure are often not so simple. At this time, you will make a mistake and use field identification. However, it can not be said that field identification cannot be used, which depends on whether the business corresponding to the two kinds of information identified by the field has an intersection.

Use numeric values as much as possible in table fields: because numeric fields perform better than string types when applied at the bottom of MySQL; you need to understand the underlying mechanism of MySQL for specific reasons for better performance, but keep that in mind.

If you define an order_id varchar (32), when there is an order_id=20180910242360 of a record, the order_id actually takes up 14 bytes but the property length of this field is 32, so there are 18 bytes that are useless but take up memory space.

Establish a reasonable index: index is to use a certain data structure to find the corresponding information, so as to reduce the time complexity and improve the search efficiency. The premise of establishing an index should also be clear, considering whether it is necessary to build an index or not. after all, the index needs to take up storage space, and sometimes the sacrificed space can not get back to time.

The second part of optimization-from the aspect of SQL statement optimization

1. Try to write out the fields you want to output; do not use statements like select * from where xxxxx;. I use * instead of * in this test, but remember to replace fields as much as possible in a production environment.

two。 Rational use of connected table query; not only the join of the table requires a large amount of memory consumption, on the other hand, if the design of the table is not very reasonable, it will lead to invalid index and lead to very bad results.

3. Pay attention to indexing when querying: if you create a name_index index in the name column, and you use a fuzzy query like name Like'%xxxx' or name Like'%xxxx%', you may not walk the index at this time; you should name Like'xxxx%'. The following is a practical example:

Index:

-- create a general index alter tablecust_infoadd index cust_third_acct_index (cust_third_acct) for cust_third_acct

A: query information through SQL: select * from sp_tunnel_user where cust_third_acct like'0200%'; the following is some of the information that meets the query criteria

B: analyze the query performance of Like'%xxxx%': select * from sp_tunnel_user where cust_third_acct like'00%'; can know through the Explain performance analysis command that the index is not executed under this query condition, and type=all indicates that the statement is executed with a full table scan. Although we have an index in the cust_third_acct field, possible_keys=null states that the condition in the form of like'00%' is that the index cust_third_acct_index must not be used. For parsing of other fields, please refer to the article "interpretation of Explain commands optimized by MySQL", which is not analyzed too much.

C: analyze the query performance of Like'xxxx%': select * from sp_tunnel_user where cust_third_acct like'0200%'; compares the possible_keys=cust_third_acct_index of this query with the b query statement, which indicates that the cust_third_acct_index index may be used in this statement, but key=null indicates that the cust_third_acct_index index is not used in the actual execution. We just said that this kind of conditional query may be indexed but not necessarily happened. this is related to MySQL's storage engine, but we try to query it in this way when we use it.

4. Use the index to follow the best left prefix feature, and place the commonly used attributes on the left when building a federated index. For example, we need to create a joint index cust_id_type in the cust_id and cust_tp of a table, and setting cust_id (not the only one) is more common, so we put cust_id on the left.

Establish a federated index:

-- create a joint index alter tablecust_infoadd index cust_id_type (cust_id,cust_tp) for cust_id and cust_tp

5. When using compliant indexes, it should be noted that the use of federated indexes needs to be continuous from left to right before the index will take effect, that is to say, federated indexes must be used continuously but not all. For example, we have created a cust_id_type index in the above 4. If we only use cust_id in the where condition, we will also walk the index. If only cust_tp is used in the where condition, then this statement will not walk the index. Here is an example:

A:select * from sp_tunnel_user where cust_id='8888888888' and cust_tp='04'; the federated index is used when the query condition uses the fields cust_id and cust_tp and cust_id is in front of it; you can see through key=cust_id_type that the index is used in the actual execution.

B:select * from sp_tunnel_user where cust_id='8888888888'; when only one field of cust_id is used in the query condition, the federated index is also used; through key=cust_id_type, you can see that the index is used in the actual execution, which is the left prefix principle.

C:select * from sp_tunnel_user where cust_tp='04'; when the query condition uses only one field of cust_tp, but not the index; you can see through key=null that the index is not used in the actual execution, which is also the principle of left prefix.

The third part of optimization-read-write separation and sub-database and sub-table

When the amount of data reaches a certain amount, it is no longer the optimization at the database level that limits the storage performance of the database; at this time, read-write separation and sub-database and sub-table are often used together with cache. at this time, the optimization at the database level is only the foundation. Read-write separation is suitable for smaller amounts of data; sub-tables are suitable for medium-sized data; and sub-libraries and sub-tables are generally combined, which is suitable for the storage of large amounts of data, which is also one of the methods for large Internet companies to solve data storage. As for how to separate reading and writing, how to divide the table, and how to divide the library, there will be related knowledge sharing in the following articles.

This is the end of the content of "what are the knowledge points optimized by SQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.