MySQL Study Notes 8-Performance Optimization

Show status like 'value'; query the performance of the MySQL database.

Value parameter Connections: Number of Connections to the MySQL server Uptime: MySQL Server launch time Slow_queries: Number of slow queries Com_lelect: Number of query operations Com_insert: Number of insert operations Com_delete: the number of delete operations to query the connection times of the MySQL server: show status like 'connections'; optimize the query analysis statement to query the explain select statement; describe select statement; impact of indexes on query speed analysis query optimization: Using indexes in EXPLAIN database query statements not only automatically optimizes query efficiency, but also reduces server overhead. Optimize index query using the LIKE keyword in index Query

If the first character in the matched string is "%", the index will not be used. If the position of the matched string is not the first, the index will be used normally. Use multi-column indexes in query statements

Create index index_student_info ON studentinfo (name, sex );

The index must be the first field in the application for normal use. Use the OR keyword in the query statement

The two fields to be queried must be the same as the index. If one of the search criteria is not indexed, the index will not be applied to the query. Optimize the database structure to split tables with many fields into multiple tables, add Intermediate tables, optimize insert record speed, disable INDEX

Statement for disabling indexes: alter table name disable keys;

Re-ENABLE the index statement: alter table name enable keys; Disable unique check

SET UNIQUE_CHECKS = 0;

Re-enable the uniqueness check statement: SET NINQUE_CHECKS = 1; optimize the INSERT statement

When inserting a large amount of DATA, we recommend that you use an INSERT statement to INSERT multiple records. If you can use the load data infile statement, try to use the load dadta infile statement, because the load data infile statement imports faster than the INSERT statement.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT },...),(...),...

[On duplicate key update col_name = expr,...] analysis table

Basic Syntax: analyze table name 1 [TABLE name 2...]; Checklist

Basic Syntax: check table name 1 [, TABLE name 2] [option]

Check whether errors exist in tables of the InnoDB and MyISAM types. In addition, this statement can also check whether errors exist in views. Optimization table

Use the optimize table statement in MySQL to OPTIMIZE the TABLE. Basic Syntax: optimize tables table Name 1 [TABLE name 2...];

The optimize table statement can be used to eliminate disk fragments caused by deletion and update, thus reducing the waste of space. This statement also adds a read-only lock to the table during execution. Check whether the cache is enabled

Show variables like '% quer_cache %'; use high-speed cache

SELECT SQL _CACHE * FROM table name;

Once the table changes, the cache for querying this table will become invalid and will be deleted from the cache. This prevents the query from returning invalid data from the old table. In addition, you can apply the SQL _NO_CACHE keyword without using the cache lookup function. Optimize multi-Table query optimization table design when designing a data table, you should prioritize the use of specific field length, and then consider using variable length fields. Use the optmize table command to Process Tables frequently operated by users.