Mysql SQL Optimization & execution plan

Reprint: Https://www.cnblogs.com/wardensky/p/4569133.html SQL Optimization Guidelines

DisableSELECT * UseSelectCOUNT (*) counts the number of rows as little as possible to avoid a full table scan, if possible, index the filtered column to avoidThe WHERE clause makes a fieldNull judgment try to avoidThe WHERE clause uses! = or <> tries to avoidThe WHERE clause uses or joins as much as possible to avoid expression evaluation of fields try to avoid using the field as much as possible avoid the use of the  prefix column of the composite index to filter the connection as little as possible, if possible, make the index as few joins as possible Join instead of subqueries try to avoid using in, no in or having with the WHERE clause,   using exists, not exists instead of trying to avoid fuzzy matching at both ends Like%***% try to use UNION ALL instead of union as early as possible to filter to avoid type conversions try to optimize high concurrency SQL for BULK Insert precedence , rather than low frequency large SQL as much as possible for each  SQL for explain as far as possible from the global               

SQL Execution Plan

On the MySQL command line, execute the explain command to see the SQL execution plan, as shown in:

Id

Id: Contains a set of numbers that indicate the order in which a SELECT clause or action table is executed in a query

id相同，可以认为是一组，执行顺序由上至下如果是子查询，id的序号会递增id值越大优先级越高，越先被执行

Select_type

Select_type: Represents the type of each SELECT clause in a query (simple, complex)

SIMPLE：查询中不包含子查询或者UNIONPRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARYSUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERYDERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。若UNION包含在  FROM子句的子查询中，外层SELECT将被标记为DERIVEDUNION：若第二个SELECT出现在UNION之后，则被标记为UNIONUNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT

Table

Table referenced by the output row

Type

Type: Indicates how MySQL finds the desired row in the table, also known as the "access type"

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行index：Full Index Scan，index与ALL区别为index类型只遍历索引树range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下， 使用systemNULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

Possible_keys

Indicates which index MySQL can use to find rows in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query.

Key

Displays the index that MySQL actually uses in the query, and displays NULL if no index is used. If an overwrite index is used in the query, the index appears only in the key list.

Key_len

Represents the number of bytes used in the index, which can be used to calculate the length of the index to use in the query. The value displayed is the maximum possible length of the indexed field, not the actual length, that is, Key_len is calculated from the table definition and not retrieved through the table.

Row

Indicates that MySQL estimates the number of rows to be read to find the required records, based on table statistics and index selection.

Extra

Contains additional information that is not appropriate for display in other columns but is important

Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index）【注：MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件  包含所有满足查询需要的数据的索引称为 覆盖索引】Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询Using filesort： MySQL中无法利用索引完成的排序操作称为“文件排序”

Mysql SQL Optimization & execution Plan