Column Analysis of explain

id: Number representing the select statement. If it is a join query, the tables are equal. The select numbers are all 1, starting from 1. If there are sub-queries in a select, the number increases.

mysql> explain select goods_id,goods_name from  goods where goods_id in (sele
ct goods_id from  goods where cat_id=4) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table:  goods
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table:  goods
         type: unique_subquery
possible_keys: PRIMARY,cat_id
          key: PRIMARY
      key_len: 3
          ref: func
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

select_type: Query type

1. simple (excluding subqueries)
2. primary (intron query or derived query)
3. Subquery (non-from subquery)
4. derived (from type sub-query)
5. union
6. union result

Table: The table for which the query is directed

It could be
Actual table names such as select * from t1;
Table aliases such as select * from t2 as tmp;
When derived, such as from subqueries
null calculates the result directly without running the meter.

possible_key: Possible index

Note: The system estimates several possible indexes, but ultimately only one.

key: The final index.

key_len: Maximum length of index used

type column: refers to the way of query, is very important, is an important basis for the analysis of "data search process"

Possible value
- all: It means scanning the whole table line by line from the first line to the last line.

Example: Remove the index from the goods_name column and query according to goods_name

mysql> explain select goods_name from goods where goods_name='NOKIA N85' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31
        Extra: Using where
1 row in set (0.00 sec)

• index: A little better than all.
  

  Generally speaking, all scans all data rows, equivalent to data_all index scans all index nodes, equivalent to index_all.

Two scenarios may arise:
1: In the case of index-covered queries, the upper index can be used, but it must be scanned in full index.

mysql> explain select goods_id from  goods where goods_id=1 or goods_id+1>20
\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 31
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select goods_id,click_count from  goods where goods_id=1 or go
ods_id+1>20 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31
        Extra: Using where
1 row in set (0.00 sec)

2: Use index to sort, but take out all nodes select goods_id from goods order by goods_id desc; Analysis: Without where condition, all index nodes must be selected. At the same time, there is no return line, only index nodes. Rearrange and pass through all index nodes.

mysql> explain select goods_id from  goods order by goods_id asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 31
        Extra: Using index
1 row in set (0.00 sec)

• Range: When querying, you can scan the range according to the index.

mysql> explain select goods_id,goods_name,shop_price from  goods where goods
id >25 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 8
        Extra: Using where
1 row in set (0.00 sec)

• ref means that some data rows can be directly referenced through index columns

mysql> explain select goods_id,goods_name from  goods where cat_id=4 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: ref
possible_keys: cat_id
          key: cat_id
      key_len: 2
          ref: const
         rows: 3
        Extra:
1 row in set (0.00 sec)

In this example, the cat_id index points to N rows of goods data to find the results.

• eq_ref refers to referencing a row of data directly through an index column
  Common in join queries

mysql> explain select goods_id,shop_price from  goods innert join ecs_catego
y using(cat_id) where goods_id> 25 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: innert
         type: range
possible_keys: PRIMARY,cat_id
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 8
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ecs_category
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: shop.innert.cat_id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

• Const, system and null refer to the query optimization to the constant level, even without looking up time.

When querying according to the primary key, const,system will appear easily.
Or directly query an expression and NULL appears when it does not pass through a table

mysql> explain select goods_id,goods_name,click_count from  goods wher
_id=4 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

mysql> explain select max(goods_id) from  goods \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL myisam Table max,min,count Optimized in tables,Unwanted\True search,by NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

ref column refers to the field reference relationship between tables when joining queries.

mysql> explain select goods_id,cat_name,goods_name from  goods inner join ec
_category using(cat_id) where ecs_category.cat_name='' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table:  goods
         type: ALL
possible_keys: cat_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ecs_category
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: shop. goods.cat_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

Rows: An estimate of how many rows to scan.

extra:

1. Index: refers to the use of index coverage, very efficient
2. Where is not located only by index, but also by where.
3. using temporary refers to columns of temporary tables, groups by and orders by different columns, or groups by, orders by other tables.
4. Filesort: File sorting (files may be on disk or in memory), (?????????????

Select sum (shop_price) from goods group by cat_id (???????????????????????????????????????????????????????????????????