mysql的explain

explain 一般用于分析sql.
如下

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

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;

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

[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` `test`.`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 > A

巴特西

mysql的explain

最新文章

热门文章