一、id:select查询的序号,包含一组数字,表示查询中执行select字句或者操作顺序。
1、id相同,自上到下顺序执行;
2、id不同,序号大的先执行;
3、id相同不同共存,序号大的先执行,相同的顺序执行;
二、select_type: 查询类型,主要用于区别普通查询、联合查询、子查询等复杂查询
常见simple、primary、subquery、derived、union、union result六种
1、simple:简单的select查询,查询中不包含子查询或者union;
2、primary:查询中若包含任何复杂的子部分,最外层查询则被标记为primary;
3、subquery:在select或者where列表中包含子查询;
4、derived:在from列表中包含的子查询被标记为derived,
mysql会递归执行这些子查询,把结果放在临时表中,临时表会增加系统负担;
5、union:若第二个select出现在union之后,则被标记为union;
若union包含在from字句的查询中,外层select将被标记为:derived;
6、union result:从union表获取的select;
三、table:这行数据关于哪张表。
四、type:
system > const > eq_ref > ref > range > index > all
一般来说,得保证查询至少达到range级别,最好能达到ref
system 表只有一行记录(系统表)
const 表示通过索引一次就可以找到了,const用于比较primary key或者unique列索引。因为只匹配一行数据,所以很快
如:将primary key放置wehere中
eq_ref 唯一性索引扫描。对于每个索引键,表中只有一条记录与之匹配,常见于主键或者唯一索引扫描
explain select au.user_name, an.unit_name from acc_user au left join acc_unit an on au.unit_id = an.unit_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
| 1 | SIMPLE | au | NULL | ALL | NULL | NULL | NULL | NULL | 28 | 100.00 | NULL |
| 1 | SIMPLE | an | NULL | eq_ref | PRIMARY | PRIMARY | 8 | smartpatroldb.au.unit_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
ref 非唯一性索引扫描,返回匹配某个单独值的所有行
explain select au.user_name, an.unit_name from acc_unit an left join acc_user au on an.unit_id = au.unit_id;
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+--------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+--------------------------+------+----------+-------------+
| 1 | SIMPLE | an | NULL | index | NULL | idx_unit_name | 767 | NULL | 6 | 100.00 | Using index |
| 1 | SIMPLE | au | NULL | ref | FK_Reference_unit_user | FK_Reference_unit_user | 9 | smartpatroldb.an.unit_id | 7 | 100.00 | NULL |
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+--------------------------+------+----------+-------------+
range 索引给定范围行,使用一个索引选择行
explain select * from acc_user where user_id between 1000 and 3000;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | acc_user | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 2 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
index Full Index Scan,index与all区别,index只扫描索引树。通常比all快。
all 全表扫描
五、possible_keys: 显示可能应用到这张表的索引,一个或多个;但不一定查询实际使用到;
六、key:使用到索引;
explain select * from p_base_data where patrol_task_id = 1;
+----+-------------+-------------+------------+-------+------------------------------+---------------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+------------------------------+---------------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | p_base_data | NULL | ref | idx_p_base_data_patrolTaskId | idx_p_base_data_patrolTaskId | 9 | const| 2 | 100.00 | Using where |
+----+-------------+-------------+------------+-------+------------------------------+---------------------------------+---------+------+------+----------+-------------+
七、key_len:索引中使用的字节个数,可以通过该列计算查询中索引使用的长度;
explain select * from t1 where col1='ab';
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_col1_col2 | idx_col1_col2 | 13 | NULL | 143 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
explain select * from t1 where col1='ab' and col2='ac';
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_col1_col2 | idx_col1_col2 | 26 | NULL | 1 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
八、ref:显示索引列哪一列被使用了,如果可能的话,是一个常量;
九、rows: 根据表统计信息及索引使用情况。大致估算出找出所需读取行数; 越小越好
十、Extra:
using filesort
using temporary
using index
using where
using join buffer