mysql中explain的使用

explain

explain select * from table

explain用在查询语句之前，会输出一个表，表中的每个字段有着各自的含义：

• id: select的标识符，自动分配且唯一
• select_type: select的查询类型
• table: 查询的表名称
• partitions: 匹配的分区
• type: join类型
• possible_keys: 有可能使用到的key
• key：真实使用的key
• key_len: 使用的key的字节长度
• ref: columns compared to index
• rows: 查询到的数据条数
• extra: 额外的信息

重要字段的说明

select_type-查询的类型

• SIMPLE – the query is a simple SELECT query without any subqueries or UNIONs：没有子查询和UNION语句
• PRIMARY – the SELECT is in the outermost query in a JOIN：最外层的查询
• DERIVED – the SELECT is part of a subquery within a FROM clause：在from中的子查询
• SUBQUERY – the first SELECT in a subquery：子查询中的第一个SELECT
• DEPENDENT SUBQUERY – a subquery which is dependent upon on outer query
• UNCACHEABLE SUBQUERY – a subquery which is not cacheable (there are certain conditions for a query to be cacheable)
• UNION – the SELECT is the second or later statement of a UNION
• DEPENDENT UNION – the second or later SELECT of a UNION is dependent on an outer query
• UNION RESULT – the SELECT is a result of a UNION

type-查询的效率

• system – the table has only zero or one row: 查询结果只有一行或零行，特殊的const
• const – the table has only one matching row which is indexed. This is the fastest type of join because the table only has to be read once and the column’s value can be treated as a constant when joining other tables.：只有一行结果，且使用了索引
• eq_ref – all parts of an index are used by the join and the index is PRIMARY KEY or UNIQUE NOT NULL. This is the next best possible join type.：使用了索引的全部，并且索引是主键或唯一索引
• ref – all of the matching rows of an indexed column are read for each combination of rows from the previous table. This type of join appears for indexed columns compared using = or <=> operators.：所有匹配的行，使用=的操作符
• fulltext – the join uses the table’s FULLTEXT index.：全文索引的使用
• ref_or_null – this is the same as ref but also contains rows with a null value for the column.
• index_merge – the join uses a list of indexes to produce the result set. The key column of EXPLAIN‘s output will contain the keys used.:使用了一个索引列表产生的结果集（即包含多个索引）
• unique_subquery – an IN subquery returns only one result from the table and makes use of the primary key.
• index_subquery – the same as unique_subquery but returns more than one result row.
• range – an index is used to find matching rows in a specific range, typically when the key column is compared to a constant using operators like BETWEEN, IN, >, >=, etc.：范围查询
• index – the entire index tree is scanned to find matching rows.：整个索引树被扫描
• all – the entire table is scanned to find matching rows for the join. This is the worst join type and usually indicates the lack of appropriate indexes on the table.：全表扫描，效率最差

效率从高到低：system > const > eq_ref > ref > range > index > all

key_len: 索引的字节数

• 字符串
  • char(n): n bytes
  • varchar(n): utf8=3n+2, utf8mb4=4n+2
• 数值类型
  • tinyint: 1 bytes
  • smallint: 2 bytes
  • mediumint: 3 bytes
  • int: 4 bytes
  • bigint: 8 bytes
• 时间类型
  • date: 3 bytes
  • timestamp: 4 bytes
  • datetime: 8 bytes
• 字段属性: NULL 1 bytes, NOT NULL 0 bytes