The main methods of SQL optimization, 19 species is the most effective SQL optimization techniques
The Great Wall, SEO specialist? On May 28, 2020 13:14:18? Submit
In this paper, we're here to discuss the project in commonly used MySQL optimization method, a total of 19, specific as follows:
1, the EXPLAIN
Do MySQL optimization, we have to use the EXPLAIN the SQL execution plan,
Here is a simple example, labeled (1, 2, 3, 4, 5) we need to focus on the data:
Write SQL remember the 19 optimization principle, efficiency at least 3 times
The type column, connection type, a good SQL statements to at least level range, put an end to all levels,
The key columns, the use of the index name, if there is no selection index, value is NULL, can adopt mandatory index way,
Key_len column, index length,
Rows column, scan line number, the value is a forecast,
Extra columns, detailed instructions, pay attention to, not too friendly common values, as follows: Using filesort, Using temporary,
2, the SQL statement contains the value should not be IN too much
MySQL to do IN the corresponding optimization, all is the constant IN stored IN an array, and the array is sorted, but if the value is more, consumption was the bigger of the two, for example: select id from t where num IN (1, 2, 3) for continuous value, it can be used between don't use IN; Or use the connection to replace,
3, SELECT statement must specify the field name
SELECT * increased a lot of unnecessary cost (CPU, IO, memory, network bandwidth); Increase the possibility of using cover index; When the table structure changed, before also need to be updated, so request directly behind the select connected to the field name,
4, when only need a data using limit 1
This is in order to EXPLAIN the type column to const type
5, if there is no use index sort field, less as far as possible sort
6, if the constraints in the other fields no index, use less as far as possible the or
Or on both sides of the field, if there is a not index field, and other conditions are not indexed field, will cause the index of the query does not walk, a lot of time using a union all or a union (when necessary) to replace "or" will get better effect,
7, try to substitute union all union
Union and union all differences are mainly the former results need to be set and uniqueness of the reentry after filtering operation, this will involve sorting, add a lot of CPU operations, increase resource consumption and delay, of course, the premise of union all is two result sets no duplicate data,
8, do not use the ORDER BY the RAND ()
Select the id from ` dynamic ` order by 1000 rand () limit;
The above SQL statement can be optimized for:
Select id from ` dynamic ` t1 join (select rand () * (select Max (id) from ` dynamic `) as nid) t2 on t1. The id & gt; T2. Nidlimit 1000;
9, distinguish and exists in the not and not the exists in
Select * from table A where id in (select id from table B)
The above SQL statement is equivalent to
Select * from table A where the exists (select * from table B where table B.i table Anderson, d=d)
Distinguish and exists mainly in the drive the change of the order (which is the key to the performance change), if exists, then the outside layer table as the driver table, be accessed first, if it is in, then execute the subquery first, so is suitable for in table within looks great and small; The EXISTS is suitable for small and large table appearance,
About the not and not in the exists, it is recommended to use the not exists, is not only the efficiency problem, not logic problems in May, how to write an effective alternative not exists the SQL statements?
The original SQL statement:
The select colname... From A table where Anderson d not in (select b.i d from table B)
Efficient SQL statements:
The select colname... From A table Left the join table B on the where Anderson, d=b.i d where b.i d is null
Remove the result set of the below said, the data in the table A table is not B:
Write SQL remember the 19 optimization principle, efficiency at least 3 times
10, reasonable use of paging way in order to improve the efficiency of the paging
Select id, name from product limit 866613, 20
When using the above SQL statement to do paging, may someone will find that with the increment of table data, direct use of limit paging query will be more and more slow,
Optimization method is as follows: to take the maximum number of lines of the previous page id, and then according to the maximum id to limit the starting point of the next page, such as column, the previous biggest id is 866612, the SQL can use the following method:
Select id, name from the product where id> Limit of 20 866612
11, segmented query
In some users choose page, may be some of the user to select the time range is too big, slow queries, the main reason is to scan lines too much, this time can pass program, segments query, iterate over, will merge the results to display,
The diagram below the SQL statement, scan millions of more than the number of rows can be used when the segmented query:
Write SQL remember the 19 optimization principle, efficiency at least 3 times
12, to avoid in the where clause to null field value judgment
For null judgment will lead to a full table scan engine abandon the use of index,
13, do not recommend using % prefix fuzzy query
For example LIKE "% name" or the LIKE "% name %", the query will lead to failure and a full table scan, index but can be used LIKE "name %,"
So how to query % name %?
Add secret field as shown in the figure below, although the index, but in the explain the result does not use:
Write SQL remember the 19 optimization principle, efficiency at least 3 times
So how to solve this problem, the answer: use a full-text index,
In our query often use the select id, fnum, FDST from dynamic_201606 where user_name like '% zhangsan %'; , such a statement, general index is unable to meet the demands of a query, fortunately in MySQL, full-text index to help us,
Create a full-text index of SQL syntax is:
The ALTER TABLE ` dynamic_201606 ` ADD FULLTEXT INDEX ` idx_user_name ` (` user_name `);
Using full-text index of SQL statement is:
Select id, fnum, FDST from dynamic_201606 where match (user_name) against (" zhangsan "in Boolean mode);
Note: before the need to create a full-text index, please contact DBA determine whether created, at the same time, it is important to note the query spelled with ordinary index difference,
14, to avoid in the where clause expression of field operation
For example:
The select user_id, user_project from user_base where age * 2=36;
Of field line arithmetic, which can cause the engine to give up using the index, to:
The select user_id, user_project from user_base where age=36/2;
15, avoid implicit type conversion
Column in the where clause fields do not match the type and the incoming parameter types occur when type conversion, suggested to determine the types of parameters in the where,
16, the most left prefix for joint index, to obey the laws of
For the column index contains fields id, name, school, and can directly use id field, can also be id, name such order, but the name; School can't use the index, so in creating a joint index, index field must pay attention to order, the commonly used query field on the front,
17, if necessary, you can use the force index to enforce query go some index
Sometimes MySQL optimizer take it thinks appropriate indexes to retrieve the SQL statement, but may it adopted by the index is not what we want, then forceindex can be used to force the optimizer to use our set index,
18, pay attention to the scope of the query
For joint index, if there is a range query, such as between & gt; ,
19, about the JOIN optimization
Write SQL remember the 19 optimization principle, efficiency at least 3 times
LEFT the JOIN A table as the driver table, INNER JOIN MySQL will automatically find out the data less function driver table table, RIGHT the JOIN table B as the driver table,
Note:
1) there is no full in MySQL join, can use the following ways to solve:
Select * from A left join B on B.n ame=A.n amewhere B.n ame is nullunion allselect * from B;
2) try to use the inner join, avoid left join:
Participate in the joint of the query table for at least two tables, generally there are size, if the connection is inner join, in the absence of other filter conditions MySQL will automatically choose the little table as the driver table, but left the join on the choice of the driver table follow is left drive right principle, which left the join on the left side of the table is called the driver table,
3) reasonable use index:
The driver table index field as restrictions on field,
4) use a small table to drive the big table:
Write SQL remember the 19 optimization principle, efficiency at least 3 times
From schematic diagram can intuitive see if can reduce the driver table, decrease The Times of nested loops in the cyclic number in order to reduce the total number of IO and CPU computing,
5) use opportunely STRAIGHT_JOIN:
Inner join is chosen by the MySQL driver table, but some special cases need to choose another watch as the driver table, such as the group by, such as the order by (Using filesort), (Using temporary), STRAIGHT_JOIN to force join order, in STRAIGHT_JOIN the name of the table on the left is the driver table, is on the right is the driver table, in the use of STRAIGHT_JOIN is a prerequisite for the query is the internal connection, also is the inner join, other links do not recommend Using STRAIGHT_JOIN, otherwise may cause the query result is not accurate,
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull