19 MySQL optimization methods in database management

After MySQL database optimization, not only can the redundancy of the database be reduced, but also the running speed of the database can be changed. Here are 19 very good MySQL database optimization methods we have compiled for your reference.

Statement: The following optimization solutions are all based on "MySQL-index-BTree type"

1. EXPLAIN

When optimizing MySQL, we need to make good use of EXPLAIN to view the SQL execution plan.

Here is a simple example, marking (1,2,3,4,5) the data we want to focus on.

type column, connection type. A good SQL statement must at least reach the range level. Avoid using all-level key columns and index names. If no index was selected, the value is NULL. You can use the forced index method key_len column, index length rows column, and scan number of rows. This value is an estimated extra column, detailed description. Note that common unfriendly values ​​include: Using filesort, Using temporary 2. The values ​​contained in IN in the SQL statement should not be too many

MySQL has made corresponding optimizations for IN, that is, all constants in IN are stored in an array, and this array is sorted. However, if the value is large, the consumption will also be relatively large. Another example: select id from t where num in(1,2,3) For consecutive values, use between instead of in; or use join 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 covering indexes; when the table structure changes, the preceding section also needs to be updated. Therefore, it is required to add the field name directly after select.

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

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

5. If the sorting field does not use an index, sort as little as possible

6. If there is no index on other fields in the restriction, try to use or as little as possible

If one of the fields on both sides of the or condition is not an index field and the other conditions are not index fields either, the query will not use the index. Often times, using union all or union (when necessary) instead of "or" will produce better results.

7. Try to use union all instead of union

The main difference between union and union all is that the former needs to merge the result sets before performing unique filtering operations, which involves sorting, adding a large amount of CPU operations, increasing resource consumption and latency. Of course, the prerequisite for union all is that there is no duplicate data in the two result sets.

8. Not using ORDER BY RAND()

select id from `dynamic` order by rand() limit 1000;

The above SQL statement can be optimized as follows:

select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid limit 1000;

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

select * from tableA where id in (select id from tableB)

The above SQL statement is equivalent to

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

The main difference between in and exists is that it changes the driving order (which is the key to performance change). If it is exists, the outer table is the driving table and is accessed first. If it is IN, the subquery is executed first. Therefore, IN is suitable for the situation where the outer table is large and the inner table is small; EXISTS is suitable for the situation where the outer table is small and the inner table is large.

Regarding not in and not exists, it is recommended to use not exists. It is not only an efficiency issue, but not in may have logical problems. How to efficiently write a SQL statement that replaces not exists?

Original SQL statement

select colname … from table A where a.id not in (select b.id from table B)

Efficient SQL statements

select colname … from table A Left join table B on where a.id = b.id where b.id is null

The retrieved result set is shown in the following figure, the data in table A is not in table B

10. Use reasonable paging methods to improve paging efficiency

select id,name from product limit 866613, 20

When using the above SQL statement for paging, some people may find that as the amount of table data increases, directly using limit paging queries will become slower and slower.

The optimization method is as follows: you can get 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 largest ID on the previous page is 866612. sql can be written as follows:

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

11. Segment Query

On some user selection pages, some users may select a time range that is too large, causing slow query. The main reason is too many scan lines. At this time, you can use the program to query in segments, traverse in a loop, and merge the results for display.

As shown in the following SQL statement, segmented query can be used when the number of scanned rows is more than one million.

12. Avoid null value judgment on fields in the where clause

The judgment of null will cause the engine to give up using the index and perform a full table scan.

13. It is not recommended to use % prefix fuzzy query

For example, LIKE "%name" or LIKE "%name%". This query will cause the index to fail and a full table scan will be performed. But you can use LIKE "name%".

So how do you query %name%?

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

So how to solve this problem? The answer is: use full-text index

In our queries, we often use select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; . For such statements, ordinary indexes cannot meet the query requirements. Fortunately, in MySQL, there are full-text indexes 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 statement to use the full-text index is:

select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

Note: Before creating a full-text index, please contact your DBA to confirm whether it can be created. At the same time, it is important to note that the writing of query statements is different from ordinary indexes.

14. Avoid expression operations on fields in the where clause

for example

select user_id,user_project from user_base where age*2=36;

In the example above, arithmetic operations are performed on the fields, 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

Type conversion occurs when the column type in the where clause is inconsistent with the parameter type passed in. It is recommended to first determine the parameter type in where

16. For joint indexes, the leftmost prefix rule must be followed

For example, if the index contains the fields id, name, and school, you can use the id field directly, or in the order of id and name, but name and school cannot use this index. Therefore, when creating a joint index, you must pay attention to the order of the index fields, and put the commonly used query fields at the front.

17. If necessary, you can use force index to force the query to go through a certain index

Sometimes the MySQL optimizer uses the index it thinks is appropriate to retrieve SQL statements, but the index it uses may not be what we want. At this time, we can use force index to force the optimizer to use the index we set.

18. Pay attention to range query statements

For a joint index, if there is a range query, such as between, >, <, etc., the subsequent index fields will become invalid.

19. About JOIN optimization

LEFT JOIN Table A is the driving table INNER JOIN MySQL will automatically find the table with less data as the driving table RIGHT JOIN Table B is the driving table

Note: There is no full join in MySQL. You can solve it in the following way

select * from A left join B on B.name = A.name 
where B.name is null
 union all
select * from B;

Try to use inner join and avoid left join

There are at least two tables involved in a joint query, and they are usually of different sizes. If the connection method is inner join, MySQL will automatically select the small table as the driving table if there are no other filtering conditions. However, left join follows the principle of the left side driving the right side in selecting the driving table, that is, the table name on the left side of the left join is the driving table.

Proper use of indexes

The index field of the driven table is used as the restriction field of on.

Using a small table to drive a large table

It can be seen intuitively from the schematic diagram that if the driver table can be reduced, the number of loops in the nested loop can be reduced to reduce the total IO amount and the number of CPU operations.

Using STRAIGHT_JOIN

Inner join is driven by MySQL, but in some special cases, another table needs to be selected as the driving table, such as group by, order by, "Using filesort", "Using temporary". STRAIGHT_JOIN is used to enforce the join order. The table name on the left side of STRAIGHT_JOIN is the driving table, and the table on the right side is the driven table. A prerequisite for using STRAIGHT_JOIN is that the query is an inner join. It is not recommended to use STRAIGHT_JOIN for other links, otherwise the query results may be inaccurate.

This method can sometimes reduce the time by 3 times.

Only the above optimization solutions are listed here. Of course, there are other optimization methods. You can explore and try them. Thank you for your attention. .