MySQL SQL optimization

First, the general steps to optimize the database:

(A) Use the show status command to understand the frequency of the various SQL executions.

(B) Locating SQL statements that perform less efficiently, in two ways:

Post-query location: Slow query log:--log-slow-queries=[file_name],

The query status in the event: Show processlist command to view the current thread, including the lock table condition

(C) Analysis of the execution plan for inefficient SQL through explain or DESC

(D) Identification and adoption of optimization measures

Second, the problem of indexing

(a) The data and indexes of the tables of the MyISAM storage engine are automatically stored separately, independently of one file; The data and indexes of the tables of the InnoDB storage engine are in a table space, but can have multiple files.

(B) The type of storage indexed in MySQL is currently only btree and hash two, specifically related to the table engine. Prefix index is supported.

(C) For multi-column indexes, the condition of the leftmost prefix should be noted, and must be the leftmost column.

(D) A like query, which is a constant and the wildcard is not the first character, can use the index.

(E) Search for large files, with full-text indexing recommended,

(F) If the column name is an index, use column_name is null to use the index.

(G) If the or split condition, if not two are indexed, the index is not used.

(H) If the column type is a string, be sure to enclose the string constant value in quotation marks, otherwise the index will not be used.

Iii. Viewing index usage

If the index is valid, the value of Handler_read_key will be high, representing the number of times a row is read by the indexed value.

Handler_read_rnd_next indicates that the data file reads the number of requests for the next line, and high values mean that the query runs inefficiently.

Iv. two simple and practical optimization methods

(A) periodic analysis of tables and checklists

Five, the optimization of common SQL

(A) Insert data in large quantities,

Speed is increased by controlling the opening and closing of the index.

Controls the order in which imported data is sorted to increase speed.

Increase speed by turning on and off uniqueness checks.

Increase speed by turning autocommit properties on and off.

(B) Elimination of sequencing time consumption through order by null

(C) Optimizing nested queries: Connection queries are more efficient than subqueries: subqueries need to establish temporary tables in memory.

(D) Each condition column of the OR condition must have an index before the index is used.

(E) using SQL hints,

　　

　　　　

　　

MySQL SQL Optimization