mysql explain

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}
1
2
3
4
5
6
7
8
9
10
11
12
explain
    id
    select_type
    partitions
    type
    possible_keys
    key
    key_len
    ref
    row
    filtered
    extra
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
id
    相同:从上到下
    不同:大->小
select_type
    simple
    primary
    subquery
    derived
    union
    union result
type
    null
    system
    const
    eq_ref
    ref
    ref_or_null
        类似ref,但是可以搜索null到行
    index_merge
        使用率索引合并
    range
    index
        从索引中读取
    all
        从硬盘读
extra
    using file sort
        外部排序,不是按照表内索引顺序排序
    using temporary
        使用临时表,常见排序和分组查询
    using index
        覆盖索引
    using where
        使用where条件
    using join buffer
        使用连接缓存
    impossible where
        where总是false
    distinct
        找到匹配上就不再搜索
    select tables optimized away
        不能再优化