mysql的explain
mysql explain
2023-09-11 14:19:50 时间
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 `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
相关文章
- (转)Amoeba for MySQL 非常好用的mysql集群软件
- mysql经常使用查询:group by,左连接,子查询,having where
- 在 Navicat Monitor for MySQL/MariaDB 中配置实例
- 状态快照转移:一种MySQL Galera集群较优的备份方法
- 将主机IDS OSSEC日志文件存入MYSQL的方法
- mysql连接查询、联合查询、子查询以及执行explain后的type、key、rows
- mysql 查询优化 ~explain解读之select_type的解读
- 四、Mysql主从同步
- 有关Mysql的mysql_store_result函数返回NULL的情况以及其他注意事项
- lumen 通过事件记录日志mysql日志 监听
- mysql—MySQL数据库中10位或13位时间戳和标准时间相互转换
- Mysql_mysql多个TimeStamp设置
- MySQL主从复制与lvs+keepalived单点写入读负载均衡高可用实验【转】
- 数据库原理及MySQL应用 | 数据库安全加固
- (5.4)mysql高可用系列——MySQL异步复制(实践)
- mysql 造1亿条记录的单表--大数据表
- MySQL基本SQL语句3(DQL)