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

This article mainly introduces the principles of SQL optimization what the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe you will have something to gain after reading this SQL optimization principles, let's take a look at it.

Performance optimization principles and classification

Performance optimization can be generally divided into:

Active optimization and passive optimization

The so-called active optimization refers to a kind of behavior that is carried out spontaneously without external force, for example, when the service has no obvious stutter, downtime or abnormal hardware indicators, the behavior of self-optimization can be called active optimization.

On the other hand, passive optimization is just the opposite of active optimization, which refers to the behavior of optimizing only when server stutters, service exceptions or physical indicators are found.

Performance optimization principle

Both active and passive optimization should conform to the following principles of performance optimization:

Optimization can not change the logic of service operation, but should ensure the correctness of the service; the process and results of optimization should ensure the security of the service; to ensure the stability of the service, we should not sacrifice the stability of the program for the pursuit of performance. For example, the persistence function cannot be turned off in order to improve the running speed of Redis, because the stored data will be lost after the Redis server is restarted or powered off.

The above principles seem to be nonsense, but they give us an inspiration, that is, our means of performance optimization should be: prevention of performance problems + passive optimization.

In other words, we should focus on preventing performance problems and avoid performance problems as much as possible during the development phase, while under normal circumstances, we should try to avoid active optimization to prevent unknown risks (except for KPI, or idle), especially for production environments, and finally consider passive optimization.

PS: when there is a slow decline in performance or a slow increase in hardware metrics, such as 50% memory occupancy today, 70% tomorrow, and 90% the day after tomorrow, and there is no sign of recovery, we should find and deal with such problems in advance (this case is also a kind of passive optimization).

Passive performance optimization of MySQL

So this article will focus on the knowledge of MySQL passive performance optimization, according to the knowledge of passive performance optimization, you can get some ways to prevent performance problems, so as to avoid MySQL performance problems.

In this paper, we will start with the problem, and then consider the causes of the problem and the corresponding optimization scheme. In actual development, we usually encounter the following three problems:

A single SQL runs slowly; part of the SQL runs slowly; the whole SQL runs slowly.

Question 1: analysis of the slow running of a single SQL

There are two common reasons why a single SQL runs slowly:

The index is not created or used properly; the amount of data in the table is too large. Solution 1: create and use indexes correctly

Index is a main means to help MySQL improve query efficiency, so in general, we encounter a single SQL performance problems, are usually caused by not creating or for the correct use of the index, so in the case of a single SQL running slowly, the first thing you need to do is to check whether the index of this table is created normally.

If the index of the table has been created, the next step is to check whether the SQL statement triggers the index query properly. If the following occurs, MySQL will not be able to use the index properly:

If you use the! = or operator in the where clause, the query reference will abandon the index and perform a full table scan; you cannot use a leading fuzzy query, that is,'% XX' or'% XX%', cannot make use of the order of the index because of the leading ambiguity, so you must look for it one by one to see if the condition is met, which will lead to a full index scan or a full table scan. If there is an or in the condition, even if there is a conditional index in it, the index will not be used normally. If you want to use or and want the index to take effect, you can only index every column in the or condition; perform expression operations on the field in the where clause.

Therefore, you should try to avoid the above situation. In addition to using the index normally, we can also use the following techniques to optimize the query speed of the index:

Try to use the primary key query instead of other indexes, because the primary key query will not trigger the query back to the table; the query statement is as simple as possible, and the large statement is separated from the small statement to reduce the locking time; try to use numeric fields, and if the field contains only numerical information, try not to be designed as a character; use exists instead of in query; avoid using is null and is not null on the index column.

Back table query: after the ordinary index query to the primary key index, the process of going back to the primary key index tree search is called back table query.

Solution 2: data split

When the amount of data in the table is too large, the query of SQL will be slow. You can consider splitting the table to reduce the amount of data in each table, so as to improve the query efficiency.

1. Vertical split

It refers to splitting a table with more columns into multiple tables. For example, some fields in the user table are often accessed, put these fields in one table, and other less commonly used fields in another table, and use transactions to ensure data consistency between the two tables when inserting data. Principles of vertical split:

Put the less commonly used fields in a separate table; split out large fields such as text,blob and put them in the schedule; and often combine the query columns in a table. two。 Horizontal split

It refers to splitting the rows of a data table, which slows down when the number of rows of the table exceeds 2 million, and the data of one table can be split into multiple tables to store. In general, we use modularization to split the table, for example, a user table users with 400W, in order to improve its query efficiency, we divide it into four tables users1,users2,users3,users4, and then use the user ID modularization method, while query, update and delete are also operated by modularization.

Other optimization schemes for the table: use the data type that can save the smallest data; use simple data types, int is easier to process in MySQL than varchar type; try to use tinyint, smallint, mediumint as integer types instead of int;, use not null to define fields as much as possible, because null takes up 4 bytes of space; use text types as little as possible, and consider sub-tables when you have to; try to use timestamp instead of datetime There should not be too many fields in a single table, and it is recommended that there are no more than 20 fields. Question 2: analysis of the slow running of some SQL

Some of the SQL runs slowly, so the first thing we need to do is locate the SQL, and then see if the SQL is created and used correctly. In other words, we first use the slow query tool to locate the specific SQL, and then use the solution of problem 1 to deal with the slow SQL.

Solution: slow query analysis

MySQL has the function of slow log, which can be used to record statements whose response time exceeds the threshold in MySQL. Specifically, SQL whose running time exceeds the value of long_query_time will be recorded in the slow log. The default value for long_query_time is 10, which means to run statements above 10s. By default, MySQL database does not start slow query log, so we need to set this parameter manually. If it is not necessary for tuning, it is generally not recommended to enable this parameter, because enabling slow query log will have a certain performance impact on MySQL server. Slow logging supports writing log records to files as well as database tables. Use mysql > show variables like'% slow_query_log%'; to query whether the slow log is enabled. The execution effect is as follows: if the value of slow_query_log is OFF, the slow log is not enabled.

Open slow query log

To enable slow log, you can use the following MySQL command:

Mysql > set global slow_query_log=1

However, this setting method only takes effect on the current database. If MySQL restarts, it will also become invalid. If you want to take effect permanently, you must modify the configuration file my.cnf of MySQL, as shown below:

Slow_query_log = 1 slow_query_log_file=/tmp/mysql_slow.log

When you open the slow query log, all slow query SQL will be recorded in the slow_query_log_file parameter configuration file, the default is / tmp/mysql_slow.log file, at this time we can open the log query to all slow SQL to optimize one by one.

Question 3: analysis of the slow running of the whole SQL

When the whole SQL runs slowly, it means that the carrying capacity of the database has reached its peak, so we need to use some database expansion means to alleviate the MySQL server.

Solution: read-write separation

Generally speaking, the database is "read more and write less". In other words, most of the pressure on the database is caused by a large number of operations of reading data. We can adopt the scheme of database cluster and use a library as the master library to write data; other libraries are slave libraries, which are responsible for reading data. This relieves the pressure of accessing the database.

There are two common read-write separation schemes for MySQL:

1. Application layer solution

You can route the data source through the application layer to achieve read-write separation. For example, using SpringMVC + MyBatis, you can give the SQL route to Spring and control the data source displayed by code through AOP or Annotation. Advantages: the routing strategy is scalable and controllable. Cons: you need to add coupling control code to Spring.

two。 Middleware solution

Through MySQL middleware to do master-slave clusters, such as: Mysql Proxy, Amoeba, Atlas and other middleware can meet the requirements. Advantages: decoupling from the application layer. Disadvantages: add a risk point for service maintenance, performance and stability to be tested, need to support code to enforce master and slave and transactions.

Extended knowledge: SQL sentence Analysis

In MySQL, we can use the explain command to analyze the execution of SQL, such as:

Explain select * from t where id=5

As shown in the following figure:

Where:

Id-Select identifiers, the larger the id, the higher the priority, the first to be executed; select_type-indicates the type of query; table-the table that outputs the result set; partitions-matching partitions; type-indicates the join type of the table; possible_keys-indicates the possible index to be used when querying; key-represents the actual index used; key_len-the length of the index field; comparison of ref- columns with indexes Rows-approximate estimated number of rows; filtered-percentage of rows filtered by table criteria; Extra-description and description of execution.

The most important of these is the type field. The type value types are as follows:

All-scan full table data; index-traversal index; range-index range lookup; index_subquery-use ref;unique_subquery in subquery-use eq_ref;ref_or_null in subquery-optimized ref;fulltext for indexing null-use full-text index; ref-use non-unique index to find data; eq_ref-use primary key or unique index association in join query Const-by placing a primary key after where as a conditional query, the MySQL optimizer can optimize the query into a constant, how and when, depending on the optimizer, which is a little more efficient than eq_ref. This is the end of the article on "what are the principles of SQL optimization?" Thank you for reading! I believe you all have a certain understanding of "what are the principles of SQL optimization". If you want to learn more, you are 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.