explain ---mysql
Explain的type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref
> ref > fulltext >
ref_or_null > index_merge >
unique_subquery > index_subquery >
range > index > ALL
用explain解释你的select语句，完善语句。
mysql> explain select artist_id ,type,founded from artist where name='zj'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: ALL //没有走任何索引，
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL //索引的列没有被引用
rows: 4 //全部记录数
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select name from artist\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: index //查询走了索引
possible_keys: NULL
key: name
key_len: 257
ref: NULL //索引的列没有被引用
rows: 4
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select artist_id ,type,founded from artist where name='zj'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: ref //name字段上创建索引，走了索引
possible_keys: name
key: name
key_len: 257
ref: const //索引列固定的值引用
rows: 2 //走的行数
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select artist_id ,type,founded from artist where artist_id>3 and artist_id<5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: range //在创建的索引的字段上给出范围查找
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL //没有索引列被引用
rows: 1 //检查数据走的行数
Extra: Using where
1 row in set (0.00 sec)
当数据量非常大的时候，可以看出更有效的区别
mysql> desc stdV;
+---------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | | |
| sex | enum('male','female') | NO | | male | |
| classId | tinyint(3) unsigned | NO | | 0 | |
+---------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc exam;
+----------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | | |
| category | enum('Q','T') | NO | | NULL | |
| examDate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> desc score;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
| examId | int(10) unsigned | NO | PRI | 0 | |
| score | tinyint(4) | NO | | NULL | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> explain select stdV.name,score.score,exam.name from score inner join stdV on stdV.id=score.id inner join exam on exam.id=score.examID\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: score
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 103449
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: exam
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: mysqlOpt.score.examId
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: stdV
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: mysqlOpt.score.id
rows: 1
Extra:
3 rows in set (0.00 sec)
mysql> explain select stdV.name,score.score,exam.name from score inner join stdV on stdV.id=score.id inner join exam on exam.id=score.examId where score.id=1000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stdV
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: score
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: exam
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: mysqlOpt.score.examId
rows: 1
Extra:
3 rows in set (0.00 sec)
mysql> explain select stdV.name,score.score,exam.name from score left join stdV on stdV.id=score.id left join exam on exam.id=score.examId where score.id=1000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: score
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: stdV
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: exam
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: mysqlOpt.score.examId
rows: 1
Extra:
3 rows in set (0.00 sec)
