Mysql Performance Optimization

Mysql Performance Optimization 1. optimize table

2. analyze table

3. try to declare the column as NOT NULL

4. Mysql only uses the prefix index to avoid creating an index on an existing index prefix.For example, do not create an index (a) on index (a, B)

5. index loading Key Cache

Load index into cache table1;

6. intervene in index usage to improve performance:

A. for Mysql Reference, Mysql is recommended to use an index.

Select * from t_user use index (index_city) where name like 'a % 'and city like' B % ';

B. force Mysql to use an index

Select * from t_user force index (index_city) where name like 'a % 'and city like' B % ';

C. let mysql ignore the use of an index

Select * from t_user ignore index (index_city) where name like 'a % 'and city like' B % ';

7. use temporary tables to improve query performance

Select SQL _BUFFER_RESULT * from t_user; // place the large result set in a temporary table to release the table-level lock.

Select SQL _BIG_RESULT city, count (*) from t_user group by city; // used for grouping and DISTINCT keywords to notify mysql to put the result set into a temporary table when necessary, or even sort it in a temporary table.

8. force table join order

Select a. age, B. score from a straight_join B where...; // force a to join B

9. query cache improves performance

Select SQL _CACHE * from t_user;

Select SQL _NO_CACHE * from t_user; // The query result is not cached, the query result is not searched in the buffer, and it is executed directly.

10. adjust the execution priority

SelectHigh_priority* From t_user; // priority

INSERT DELAYEDThe client submits data to MySQL, and MySQL returns OK to the client. This is not to insert data into the table, but to store the data in the memory and wait for the queue. When mysql is free, insert it again.

The advantage is that the client does not need to wait too long to increase the insert speed. The disadvantage is that you cannot return an auto-incrementing ID, and if the system crashes, MySQL will lose the data before it can be inserted.

Insert delayed into t_user values (...);//