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