mysql的explain

explain 一般用于分析sql.  
如下

[SQL] 纯文本查看 复制代码

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

drop table if exists test1; CREATE TABLE test1 (      id INT NOT NULL primary key  auto_increment, -- 自动递增      username varchar (5) not null -- 用户名 ) ENGINE=innodb ;   insert into test1 (username) values (concat( 'test' ,rand())); insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1; insert into test1 (username)   select username from test1;

先创建一部分数据. 然后执行

[SQL] 纯文本查看 复制代码

?

1	explain select * from test1;

结果

explain select username from test1;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 4187248 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

再执行

[SQL] 纯文本查看 复制代码

?

1	explain select username from test1 where username = 'test' ;

 

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 4187248 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

接下来再给表的username创建一个索引

[SQL] 纯文本查看 复制代码

?

1	ALTER TABLE `test1` ADD INDEX `i_name` (`username`) comment '' ;

   过程 会比较 卡.需要等待几秒到几十秒.取决于机器. 也可以少插入几条数据. 然后再执行刚才的两条语句

mysql> explain select username from test1 where username = 'test';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test1 | ref  | i_name        | i_name | 7       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select username from test1;
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | test1 | index | NULL          | i_name | 7       | NULL | 4187248 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.00 sec)

发现 type 变了   key也变了.  extra也不一样了. 对比一下就了解情况

其中，

type=const表示通过索引一次就找到了；

key=primary的话，表示使用了主键；

type=all,表示为全表扫描；

key=null表示没用到索引。
type=ref,因为这时认为是多个匹配行，在联合查询中，一般为REF。

经过分析后就可以判断出哪些字段需要建 索引,哪些条件可以优化等. 以及条数等



system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

转载于:https://www.cnblogs.com/yixiao21/p/8383586.html