19 MySQL optimization methods in database management and 19 MySQL Optimization

19 MySQL optimization methods in database management and 19 MySQL Optimization

After the MySQL database is optimized, it not only reduces database redundancy, but also changes the running speed of the database. The following describes 19 very good MySQL database optimization methods. For more information, see.

Declaration: The following optimization schemes are based on the Mysql-index-BTree type.

1. EXPLAIN

For MySQL optimization, we should make good use of EXPLAIN to view the SQL Execution Plan.

The following is 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 must at least reach the range level. The index name used for key columns of all levels should not appear. If no index is selected, the value is NULL. You can use the forced index method for the key_len column, the index length rows column, and the number of rows scanned. This value is an extra column of the estimated value. Note that common unfriendly values include: Using filesort and Using temporary. 2. The values in SQL statements should not be too large.

MySQL has made corresponding optimizations for the IN, that is, all constants in the in are stored IN an array, and this array is sorted. However, if the number is large, the consumption is also large. For example, select id from t where num in (, 3) for continuous values, use between instead of in, or use a connection to replace.

3. The field name must be specified in the SELECT statement.

SELECT * adds a lot of unnecessary consumption (cpu, io, memory, and network bandwidth). It increases the possibility of overwriting indexes. When the table structure changes, the pre-disconnection also needs to be updated. Therefore, the field name must be directly attached to the select statement.

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

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

5. If the sorting field is not indexed, try to sort it as few as possible.

6. If other fields in the restriction are not indexed, use less or

Or, if one of the fields on both sides is not an index field and other conditions are not an index field, the query will not take the index. In many cases, using the union all or union (when necessary) method instead of "or" will produce better results.

7. Try to replace union all with union

The main difference between union and union all is that the former requires a set of results and then a unique filtering operation. This involves sorting, adding a lot of CPU operations, and increasing resource consumption and latency. Of course, the precondition for union all is that there is no duplicate data in the two result sets.

8. Do not use order by rand ()

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

 

The preceding SQL statement can be optimized

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

 

9. differentiate in and exists, not in and not exists

Select * from Table A where id in (select id from Table B)

 

The preceding SQL statement is equivalent

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

 

Distinguishing in and exists mainly results IN a change in the driver sequence (this is the key to performance changes). If exists is used, the External table is the driver table and is accessed first. If IN is used, execute the subquery first. Therefore, IN is suitable for the case where the external table is large but the internal table is small; EXISTS is suitable for the case where the external table is small but the internal table is large.

For not in and not exists, not exists is recommended, not only the efficiency problem, not in may have logic problems. How to efficiently write an SQL statement that replaces not exists?

Original SQL statement

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

 

Efficient SQL statements

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

 

The result set is shown as follows. Table A is not in table B.

10. Use a reasonable paging Method to Improve paging Efficiency

select id,name from product limit 866613, 20

 

When using the preceding SQL statement for paging, someone may find that as the table data volume increases, the direct use of limit paging queries slows down.

The optimization method is as follows: You can take the id of the maximum number of rows in the previous page, and then limit the start point of the next page based on this maximum id. For example, in this column, the maximum id of the previous page is 866612. The SQL statement can be written as follows:

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

11. segmented Query

In some user selection pages, some user selection may have a large time range, resulting in slow query. The main reason is that the number of scanned rows is too large. In this case, you can use programs and segments for query and loop traversal to merge and display the results.

For example, when the number of scanned rows is more than, you can use segmented query.

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

If null is used, the engine will discard the index and scan the entire table.

13.% prefix fuzzy search is not recommended.

For example, if you LIKE "% name" or LIKE "% name %", this query will cause index failure and scan the entire table. However, you can use LIKE "name % ".

How can I query % name %?

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

So how can we solve this problem? Answer: use full-text index

Select id, fnum, fdst from dynamic_201606 where user_name like '% zhangsan %'; is often used in our queries ';. For such statements, normal indexes cannot meet the query requirements. Fortunately, full-text indexes are available in MySQL 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 that uses full-text index is:

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 the DBA to determine whether the index can be created. At the same time, it should be noted that the query statement writing method is different from the normal index writing method.

14. Avoid expression operations on fields in the where clause

For example

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

 

Arithmetic operations are performed on the field, which causes the engine to discard the index. We recommend that you change it

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

 

15. Avoid implicit type conversion

Type conversion occurs when the column field type is inconsistent with the input parameter type in the where clause. We recommend that you first determine the parameter type in the where clause.

 

16. For composite indexes, follow the leftmost prefix rule.

The column name indicates that the index contains the id, name, and school fields. You can directly use the id field, id, and name fields. However, neither name nor school can use this index. Therefore, when creating a joint index, you must pay attention to the order of index fields. Common query fields are placed at the beginning.

17. force index can be used to forcibly query an index if necessary

Sometimes the MySQL optimizer uses indexes that it deems appropriate to retrieve SQL statements, but the indexes it uses may not be what we want. In this case, we can use force index to force the optimizer to use our index.

18. Pay attention to the range query statement

For a federated index, if conditions such as between, >,< exist, the subsequent index fields are invalid.

19. About JOIN Optimization

Left join Table A is the driving table inner join MySQL will automatically find the table with less data. The drive table right join table B is the driving table.

Note: MySQL does not have full join. You can solve this problem using the following methods:

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

Try to use inner join to avoid left join.

The tables involved in the joint query must have at least two tables, which generally have different sizes. If the connection method is inner join, MySQL automatically selects a small table as the driving table without other filtering conditions, however, left join follows the principle on the right of the driver on the left, that is, the name of the table on the left of left join is the driver table.

Rational use of Indexes

The index field of the drive table is used as the limit field of on.

Use small tables to drive large tables

The schematic diagram intuitively shows that if the driver table can be reduced, the number of loops in the nested loop is reduced to reduce the total IO and the number of CPU operations.

Use STRAIGHT_JOIN

Inner join is a driver table selected by mysql, but in some special cases, another table needs to be selected as the driver table, for example, when "Using filesort" and "Using temporary" are available, such as group by and order. STRAIGHT_JOIN to force the join order. The table name on the left of STRAIGHT_JOIN is the driver table, and the right is the driver table. A prerequisite for using STRAIGHT_JOIN is that the query is an inner join. STRAIGHT_JOIN is not recommended for other links; otherwise, the query results may be inaccurate.

This method may sometimes reduce the time by three times.

Here we only list the above optimization solutions. Of course there are other optimization methods. You can try it out and thank you for your attention ..