MySQL Database advanced version--mysql project optimization

19 MySQL Optimization Scenarios commonly used in the project

statement: The following optimization scenarios are based on "mysql-index-btree type"

First,EXPLAIN

do MySQL optimization, we need to use EXPLAIN to view the SQL execution plan.

Here 's a simple example, labeling (1,2,3,4,5) We want to focus on the data:

1. The Type column, and the connection types. A good SQL statement must reach at least the range level. Prevent all levels from appearing
2. The name of the index to use for the key column. If no index is selected, the value is null. You can take the forced indexing method
3. Key_len column, index length
4. Rows column, number of lines scanned. The value is a pre-estimate
5. Extra column, detailed description. Note Common, less-friendly values are: Using Filesort, using Temporar

in the SQL statement ,the in contains values should not be too many

MySQL is optimized for in, and the constants in the in are all stored in an array, and the array is well-sequenced. However, if the number is more, the resulting consumption is also relatively large. Again for example: the Select ID from the Where Num in (three-to-three) values, can be used between do not use in, or use the connection to replace.

Third,SELECT statement must indicate the field name

SELECT * Adds a lot of unnecessary consumption (CPU, IO, memory, network bandwidth), increases the likelihood of using an overlay index, and the previous break needs to be updated when the table structure changes. Therefore, it is required to connect the field name directly behind the select.

Iv. When only one piece of data is needed, use limit 1

this is to make The type column in explain reaches the const type

If the sort field is not used in the index, try to sort it as little as possible Vi. If the other fields in the constraint are not indexed, try to use less or

In a field on or both sides, if there is one that is not an indexed field and the other condition is not an indexed field, it causes the query to not go through the index. Many times the union all or union (when necessary) is used instead of "or" to get better results.

Seven, try to use UNION ALL instead of union

The difference between Union and union all is that the former needs to merge the result set and then perform a unique filtering operation, which involves sorting, adding a lot of CPU operations, and increasing resource consumption and latency. of course,the precondition of union all is that there is no duplicate data for two result sets.

viii. do not use ORDER by RAND ()

Select ID from ' Dynamic ' ORDER by rand () limit 1000;

the above SQL statement can be optimized to

Select ID from ' dynamic ' T1 join (select rand () * (select MAX (id) from ' dynamic ') as Nid) T2 on t1.id > T2.nid limit 1 000;

Nine, distinguish in and exists, not in and not exists

SELECT * from table A where ID in (SELECT ID from table B)

The above SQL statement is equivalent to

SELECT * from table A where exists (SELECT * from table B where table b.id= table a.ID)

distinguishing in and exists is mainly caused by the change of the driving sequence (which is the key to the performance change), if it is exists, then the other layer table is the driver table, first is accessed, if it is in, then the subquery is executed first. So in the case of large appearance and small inner table, exists is suitable for small appearance and large inner table. for not-in and not-exists, it is not recommended to use not exists, which is not just an efficiency issue, but a logic problem. How to write an SQL statement that replaces not exists efficiently?

original SQL statement

Select ColName ... from table A where a.id not in (select b.ID from B table)

Efficient SQL statements

Select ColName ... from a table left join B table on where a.id = b.id where b.id is null

The result set that is taken out, as indicated, Table A is not in the data in table B

10, use reasonable paging method to improve the efficiency of paging

Select Id,name from Product limit 866613, 20

When using the above SQL statement for paging, one might find that the direct use of the limit paging query will be slower as the amount of table data increases.

the optimization method is as follows: You can take the ID of the maximum number of rows on the previous page, and then limit the starting point of the next page based on this maximum ID. For example, in this column, the maximum ID for the previous page is 866612. SQL can be written in the following notation:

Select Id,name from product where id> 866612 limit 20

11. Segmented Query

In some user selection pages, some users may choose too large a time range, resulting in slow queries. The main reason is that there are too many rows to scan. This time can be through the program, segmented query, loop traversal, the results are combined processing to show.

as in this SQL statement, you can use a segmented query when the number of rows scanned is more than millions

12. Avoid null values for fields in the WHERE clause

A null judgment causes the engine to discard the full table scan using the index.

13, do not recommend using the% prefix fuzzy query

For example Like "%name" or "%name%", this query causes the index to fail and perform a full table scan. But you can use like "name%".

then how to query %name%?

as shown, although an index is added to the secret field, the explain result is not used

So how to solve this problem, the answer: using full-text indexing

in our query, we often use the select Id,fnum,fdst from dynamic201606 where username like '%zhangsan% '; Such a statement, the normal index is unable to meet the query requirements. Fortunately in MySQL, there is a full-text index to help us.

the SQL syntax for creating a full-text index is:

ALTER TABLE ' dynamic_201606 ' ADD fulltext INDEX ' idx_user_name ' (' user_name ');

SQL statements that use full-text indexing are:

Select Id,fnum,fdst from dynamic_201606 where match (user_name) against (' Zhangsan ' in Boolean mode);

Note: Before you need to create a full-text index, please contact The DBA determines whether it can be created. It is also important to note that the difference between a query statement and a normal index

14. Avoid expression operations on fields in the WHERE clause

Like what

Select User_id,user_project from User_base where age*2=36;

The arithmetic operation is done on the field, which causes the engine to discard the index and the proposed change to

Select User_id,user_project from User_base where AGE=36/2;

15. Avoid implicit type conversions

The type conversion that occurs when the type of the column field appears in the WHERE clause and when the passed parameter type is inconsistent, it is recommended to first determine the type of the parameter in the where

16, for the Federated Index, to abide by the leftmost prefix rule

For example , the index contains field Id,name,school, can be used directly with the ID field, or id,name in such order, but Name;school cannot use this index. Therefore, when creating a federated index, be sure to pay attention to the Index field order, the common query field is placed in the front

17, if necessary, you can use Force index to enforce the query to go to an index

There are times The MySQL optimizer takes the index it deems appropriate to retrieve the SQL statement, but perhaps the index it is using is not what we want. At this point, Force index can be used to compel the optimizer to use the index we have set.

18. Note Scope Query statement

for Federated indexes, if there are scope queries, such as between,>,<, the subsequent index fields are invalidated.

19. About Join optimization

Left JOIN a table for driver table

INNER JOIN MySQL will automatically find the table with less data to drive the table

Right JOIN b table for driver table

Note: There is no full join in MySQL and can be resolved in the following ways

SELECT * from A LEFT join B on b.name = a.name where b.name are null Union Allselect * FROM B;

use inner join as much as possible to avoid left join

A table that participates in a joint query is at least 2 tables, and there are generally size points. If the connection is inner join, MySQL will automatically select the small table as the driver table without any other filtering conditions, but the left join follows the principle of the right side of the drive table, that is, the table named Driver table on the left side of the remaining join.

Rational use of indexes

The indexed field of the drive table as the on Limit field.

Using small tables to drive large tables

from the schematic can be seen intuitively, if you can reduce the driver table, reduce the number of loops in the nesting cycle, to reduce The total amount of IO and the number of CPU operations.

skillfully used Straight_join

Inner JOIN is selected by MySQL driver table, but some special cases need to select another table as the driver table, such as the group by, the order by and so on "using filesort", "using temporary". Straightjoin to force the connection order, the table name on the left side of Straightjoin is the driver table, and the right side is the driver table. A precondition for using straightjoin is that the query is an inner join, or inner join. Other links do not recommend the use of Straightjoin, which may result in inaccurate query results.

This approach can sometimes be reduced by 3 times times.

MySQL Database advanced version--mysql project optimization