Shulou(Shulou.com)05/31 Report--

This article is to share with you about the 19 MySQL skills that have at least tripled the efficiency. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Without saying much, let's take a look at it.

Let's talk about the MySQL optimization methods commonly used in the project, a total of 19, specifically as follows:

1 、 EXPLAIN

To do MySQL optimization, we need to make good use of EXPLAIN to view the SQL execution plan.

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

Type column, connection type. A good SQL statement should at least reach the range level. Put an end to the all level.

Key column, the index name used. If no index is selected, the value is NULL. Mandatory indexing can be adopted.

Key_len column, index length.

Rows column, number of rows scanned. The value is an estimate.

Extra column, detailed description. Note that the common unfriendly values are as follows: Using filesort,Using temporary.

2. IN should not contain too many values in the SQL statement

MySQL optimizes IN accordingly, that is, all the constants in IN are stored in an array that is sorted. However, if the number is higher, the consumption is also relatively large. Another example: select id from t where num in (1 between 2) for consecutive values, don't use in if you can, or use a connection instead.

3. The SELECT statement must specify the field name.

SELECT* adds a lot of unnecessary consumption (CPU, IO, memory, network bandwidth); increases the possibility of using overwritten indexes; and pre-breaks also need to be updated when the table structure changes. Therefore, it is required to put the field name directly after the select.

4. Use limit 1 when only one piece of data is needed

This is to make the type column in EXPLAIN reach the const type

5. if the index is not used in the sort field, sort as little as possible

6. If other fields in the restriction do not have an index, use or as little as possible.

If one of the fields on both sides of the or is not an index field, and the other condition is not an index field, it will cause the query not to move the index. In many cases, using union all or union (when necessary) instead of "or" will get better results.

7. Replace union with union all as much as possible

The main difference between union and union all is that the former needs to merge the result sets and then carry out unique filtering operation, which will involve sorting, increasing a large number of CPU operations, and increasing resource consumption and delay. Of course, the prerequisite for union all is that there is no duplicate data in both result sets.

8. 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.nidlimit 1000

9. Distinguish between in and exists, not in and not exists

Select * from table A where id in (select id from form B)

The above SQL statement is equivalent to

Select * from table A where exists (select * from form B where table B.id = table A.id)

The difference between in and exists is mainly caused by the change of the driver order (which is the key to the performance change). If it is exists, then the outer layer table is the driver table and is accessed first, and if it is IN, then the subquery is executed first. Therefore, IN is suitable for situations where the appearance is large and the inner table is small; EXISTS is suitable for situations where the appearance is small and the inner table is large.

With regard to not in and not exists, the recommended use of not exists is not just a matter of efficiency, but there may be logic problems with not in. How to write a SQL statement instead of not exists efficiently?

The original SQL statement:

Select colname... From A form where a.id not in (select b.id from B form)

Efficient SQL statements:

Select colname... From A table Left join B table on where a.id = b.id where b.id is null

The extracted result set is shown in the following figure. Table An is not the data in Table B.

10. Use reasonable paging methods to improve the efficiency of paging.

Select id,name from product limit 866613, 20

When using the above SQL statement for paging, someone may find that as the amount of data in the table increases, the direct use of limit paging queries will become slower and slower.

The method of optimization is as follows: you can take the id of the number of rows of the previous page, and then limit the starting point of the next page according to the id of this page. In this column, the id of the previous page * * is 866612. SQL can be written as follows:

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

11. Segmented query

In some user selection pages, the time range selected by some users may be too large, resulting in slow query. The main reason is that there are too many scan lines. At this time, you can use the program, segment query, loop traversal, merge the results to display.

As shown in the following SQL statement, you can use a segmented query when the number of rows scanned is more than *:

12. Avoid judging fields with null values in the where clause

The judgment of null causes the engine to abandon the use of indexes and perform a full table scan.

13. Fuzzy query with% prefix is not recommended

For example, LIKE "% name" or LIKE "% name%", this kind of query can cause the index to fail and perform a full table scan. But you can use LIKE "name%".

Then how to query% name%?

As shown in the following figure, although the index is added to the secret field, it is not used in the explain result:

So how to solve this problem? the answer: use full-text index.

Select id,fnum,fdst from dynamic_201606 where user_name like'% zhangsan%'; is often used in our queries. With such a statement, the ordinary index can not 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`)

The 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, contact DBA to determine whether it can be created. At the same time, we should pay attention to the difference between the writing of query statements and ordinary indexes.

Avoid expression operations on fields in the where clause

For example:

Select user_id,user_project from user_base where age*2=36

Perform an arithmetic operation on the field in the, which will cause the engine to abandon the use of the index. It is recommended to change it to:

Select user_id,user_project from user_base where age=36/2

15. Avoid implicit type conversion

For the type conversion that occurs when the type of the column field is inconsistent with the type of the parameter passed in the where clause, it is recommended to determine the parameter type in where first.

16. For federated indexes, follow the leftmost prefix rule

For columns, the index contains fields id, name, and school. You can use the id field directly, or in the order of id and name, but name;school cannot use this index. Therefore, when creating a federated index, we must pay attention to the order of the index fields, with the commonly used query fields at the front.

17. If necessary, you can use force index to force the query to walk an index

Sometimes the MySQL optimizer takes the index it sees fit to retrieve the SQL statement, but maybe the index it uses is not what we want. At this point, forceindex can be used to force the optimizer to use our index.

18. pay attention to the range query statement

For federated indexes, if there are scope queries, such as between, >,

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.