Shulou(Shulou.com)06/01 Report--

This article mainly introduces the steps to optimize the mysql sql and sql implementation plan, the contents of the article are carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the steps to optimize the mysql sql and sql implementation plan.

SQL optimization

Disable SELECT *

Count rows using SELECT COUNT (*)

As few operations as possible

Try to avoid full table scans and, if possible, index the filter column

Try to avoid making NULL judgment on fields in the WHERE clause

Try to avoid using the WHERE clause! = or

Try to avoid using OR connections in WHERE clauses

Avoid expression evaluation of fields as much as possible

Avoid functional operations on fields as much as possible

Try to avoid using prefix columns that are not composite indexes to filter connections

Sort as little as possible, and if you can, index

Try to reduce JOIN.

Replace subqueries with JOIN as much as possible

Try to avoid using IN,NOT IN or HAVING in the WHERE clause and use EXISTS,NOT EXISTS instead

Try to avoid fuzzy matching between two ends of LIKE% * *%

Try to use UNION ALL instead of UNION

Filter as early as possible

Avoid type conversion

Try to batch INSERT

Priority is given to optimizing high concurrency SQL rather than low-frequency large SQL

EXPLAIN each SQL whenever possible

Proceed from the overall situation as much as possible

Sql execution plan

From the mysql command line, execute the explain command to see the sql execution plan

Interpretation of EXPLAIN column

Table

Shows which table the data for this row is about.

Type

ALL, index, range, ref, eq_ref, const, system, NULL

From left to right, performance from the worst to the best

ALL:FULL TABLE Scan, MySQL will traverse the entire table to find matching rows.

INDEX:FULL INDEX Scan,INDEX differs from ALL in that the INDEX type only traverses the index tree.

RANGE: index range scan, where the scan of the index starts at a certain point and returns rows that match the range, which is common in queries such as BETWEEN, etc.

Ref: a non-unique index scan that returns all rows that match a single value. It is common for lookups using the non-unique prefix of a non-unique index, that is, a unique index.

Eq_ref: unique index scan, with only one record in the table matching for each index key. Common in primary key or unique index scans

Const, system: use these types of access when MySQL optimizes some part of the query and converts it to a constant. If the primary key is placed in the WHERE list, MySQL can convert the query to a constant. System is a special case of the const type, using system when the query table has only one row.

NULL:MySQL breaks up statements during optimization and executes without even accessing tables or indexes.

Possible_keys

Displays the indexes that may be applied to this table. If empty, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain.

Key

The index actually used. If NULL, the index is not used. In rare cases, MYSQL chooses indexes that are not sufficiently optimized. In this case, you can use USE INDEX (indexname) in the SELECT statement to force the use of an index or IGNORE INDEX (indexname) to force MYSQL to ignore the index.

Key_len

The length of the index used. Without losing accuracy, the shorter the length, the better.

Ref

Shows which column of the index is used and, if possible, a constant.

Rows

The number of rows that MYSQL thinks must be checked to return the request data.

Extra

USING INDEX: this value indicates that the Covering INDEX is used in the corresponding SELECT operation. [note: MySQL can use the index to return the fields in the SELECT list without having to read the data file again according to the index. The index that contains all the data needed by the query is called the overlay index].

USING WHERE: indicates that the MySQL CVM performs "post-filtering" (Post-filter) after the storage engine is recorded. If the query fails to use the index, the function of USING WHERE is to remind us that MySQL will use the WHERE clause to filter the result set.

USING TEMPORARY: indicates that MySQL needs to use temporary tables to store result sets, which are common in sorting and grouping queries.

USING filesort: the sort operation in MySQL that cannot be done with an index is called "file sorting".

After reading the above steps to optimize the implementation plan of mysql sql and sql, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.