文档章节

MySQL的EXPLAIN指令使用例解

forluv83
 forluv83
发布于 2016/01/25 20:31
字数 1153
阅读 70
收藏 12

数库库一共5个表(tab1,tab2等等),表结构如下:

mysql> desc tab1; -- 共有400行记录
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| col1  | varchar(15) | YES  |     | NULL    |                |
| col2  | datetime    | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc tab2; -- 共有4000行记录
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| col1    | varchar(15) | YES  |     | NULL    |                |
| col2    | datetime    | YES  | MUL | NULL    |                |
| tab1_id | int(11)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

-- tab3,tab4,tab5结构类似于tab2,分别有40000、400000,4000000行记录;

看第一个例子:

mysql> EXPLAIN
    ->    SELECT COUNT(*)
    ->      FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id
    ->     WHERE tab5.id <= 500000;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
|  1 | SIMPLE      | tab5  | range  | PRIMARY       | PRIMARY | 4       | NULL              | 504980 | Using where |
|  1 | SIMPLE      | tab4  | eq_ref | PRIMARY       | PRIMARY | 4       | test.tab5.tab4_id |      1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
2 rows in set (0.00 sec)

查看上面EXPLAIN指令的输出结果,一共有两行,前后顺序也是MySQL扫描表的先后顺序(1)。共有两种扫描类型“range”、“eq_ref”,说明MySQL先使用tab5表的主键索引来过滤条件(<=500000),然后再利用参照tab4的主键索引来找到关联表的记录。

再来看一个例子:

mysql> EXPLAIN
    ->    SELECT COUNT(*)
    ->      FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id
    ->     WHERE tab5.id <= 1000000;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows    | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+-------------+
|  1 | SIMPLE      | tab5  | ALL    | PRIMARY       | NULL    | NULL    | NULL              | 4000000 | Using where |
|  1 | SIMPLE      | tab4  | eq_ref | PRIMARY       | PRIMARY | 4       | test.tab5.tab4_id |       1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+-------------+
2 rows in set (0.00 sec)

和前面的EXPLAIN语句相比,把查询的范围扩大了一倍。再看扫描类型,变化为“ALL”,“eq_ref”,这说明MySQL再扫描tab5时不使用主键索引了,直接扫描全表,然后拿记录来过滤条件(<=1000000),然后再利用参照tab4的主键索引来找到关联表的记录。为什么不使用tab5的主键索引了呢?想必是MySQL判定在索引(二叉树)中查找一个范围所用的时间比扫描全表还慢,所以选择扫描全表(2)

下面用“强制使用索引”来验证下上面的想法:

mysql> SELECT COUNT(*) FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id WHERE tab5.id <= 1000000;
1 row in set (1.73 sec)

mysql> SELECT COUNT(*) FROM tab5 FORCE INDEX (PRIMARY) JOIN tab4 ON tab4.id = tab5.tab4_id WHERE tab5.id <= 1000000;
1 row in set (4.02 sec)

结果和设想的一致,索引的较大范围判定还是比较耗时的。

看下一条EXPLAIN语句:

mysql> EXPLAIN
    ->    SELECT COUNT(*)
    ->      FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id
    ->     WHERE tab5.id <= 2000000 AND tab4.id = 200000;
+----+-------------+-------+-------+---------------+---------+---------+-------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | tab4  | const | PRIMARY       | PRIMARY | 4       | const |       1 | Using index |
|  1 | SIMPLE      | tab5  | ALL   | PRIMARY       | NULL    | NULL    | NULL  | 4000000 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)

如前面的相比,我指定查询条件tab4的一条(id = 200000)记录,此时MySQL的扫描顺序发生的变化,先扫描tab4,再扫描tab5,扫描类型也变为“const”,“ALL”。也就是说MySQL先根据tab4的索引找到id等于200000的记录,然后再对tab5表进行全表扫描,逐条比较是否满足关联条件和id小于等2000000的条件。来看下这个查询语句需要的时间:

mysql> SELECT COUNT(*) FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id WHERE tab5.id <= 2000000 AND tab4.id = 200000;
1 row in set (0.52 sec)

如果,我对tab5表中的tab4_id字段做一个索引,会不会让上面语句查询速度有很大的提高呢?试一下,再看查询速度:

-- 原来tab5表tab4_id字段没有索引,这条语句是在加上索引后执行的
mysql> SELECT COUNT(*) FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id WHERE tab5.id <= 2000000 AND tab4.id = 200000;
1 row in set (0.00 sec)

速度果然有很大提高,已经不超过10毫秒了。再执行下EXPLAIN语句:

mysql> EXPLAIN
    ->    SELECT COUNT(*)
    ->      FROM tab5 JOIN tab4 ON tab4.id = tab5.tab4_id
    ->     WHERE tab5.id <= 2000000 AND tab4.id = 200000;
+----+-------------+-------+-------+---------------------+-------------+---------+-------+------+-------------
| id | select_type | table | type  | possible_keys       | key         | key_len | ref   | rows | Extra
+----+-------------+-------+-------+---------------------+-------------+---------+-------+------+-------------
|  1 | SIMPLE      | tab4  | const | PRIMARY             | PRIMARY     | 4       | const |    1 | Using index
|  1 | SIMPLE      | tab5  | ref   | PRIMARY,idx_tab4_id | idx_tab4_id | 5       | const |   10 | Using where
+----+-------------+-------+-------+---------------------+-------------+---------+-------+------+-------------
2 rows in set (0.00 sec)

此时的扫描类型已经变为“const”,“ref”,也就是说MySQL先根据tab4的索引找到id等于200000的记录,然后再参照tab4的id,利用tab5表中的对应索引划定一个范围,在这个范围中再比较其是否满足条件(<=2000000)。也提示我们关联字段要不要建索引,视关联表在查询时的使用条件而定(3)

© 著作权归作者所有

forluv83
粉丝 3
博文 19
码字总数 5721
作品 0
东城
高级程序员
私信 提问
linux zip/unzip命令详解+实例

linux zip/unzip命令 命令名: zip 功能说明:压缩文件。 语  法:zip [-AcdDfFghjJKlLmoqrSTuvVwXyz$][-b <工 作目录>][-ll][-n <字 尾字符串>][-t <日 期时间>][-<压 缩效率>][压 缩文件......

尘世如潮
2016/03/21
148
0
使用Explain分析select语句

explain可以分析某条select语句会查询多少条记录、以怎样的方式查询,以及复杂select的执行顺序,借此可以了解到select语句的性能和查询是如何执行的 如: select子句和from子句,先执行from子...

井然有序
2018/06/26
0
0
sed

#拿掉单一一个 | 竖杠,采用词首和词尾定位符 sed -e "s|<|>||g" SED手册 1.Introduction Sed(Stream EDitor)为UNIX系统上提供将编辑工作自动化的编辑器,使用者无需直接编辑资料。使用者可利...

企图穿越
2010/05/13
449
0
MySql优化的一般步骤

MySql优化的一般步骤: 1.通过show status 命令了解各种sql的执行效率   SHOW STATUS提供msyql服务器的状态信息   一般情况下,我们只需要了解以”Com”开头的指令   show session st...

落叶刀
2016/06/07
50
0
linux下操作 mysql的基本命令

1, 创建mysqld数据库的管理用户: 要把root用户设置为管理员,我们应该运行下面的命令; [root@linuxsir01 root]# mysqladmin -u root password 123456 一般情况下,mysqladmin所在目录已经...

beibugulf
2016/09/27
19
0

没有更多内容

加载失败,请刷新页面

加载更多

Jenkins的配置

1 修改jenkins的根目录,默认地在C:\Documents and Settings\AAA\.jenkins 。 .jenkins ├─jobs │ └─JavaHelloWorld │ ├─builds │ │ ├─2011-11-03_16-48-17 │ │ ├─2011-11-0......

shzwork
27分钟前
1
0
使用 spring 的 IOC 解决程序耦合

工厂模式解耦 在实际开发中我们可以把三层的对象都使用配置文件配置起来,当启动服务器应用加载的时候,让一个类中的方法通过读取配置文件,把这些对象创建出来并存起来。在接下来的使用的时...

骚年锦时
31分钟前
1
0
group by分组后获得每组中时间最大的那条记录

用途: GROUP BY 语句用于 对一个或多个列对结果集进行分组。 例子: 原表: 现在,我们希望根据USER_ID 字段进行分组,那么,可使用 GROUP BY 语句。 我们使用下列 SQL 语句: SELECT ID,US...

豆花饭烧土豆
今天
3
0
android6.0源码分析之Camera API2.0下的Preview(预览)流程分析

本文将基于android6.0的源码,对Camera API2.0下Camera的preview的流程进行分析。在文章android6.0源码分析之Camera API2.0下的初始化流程分析中,已经对Camera2内置应用的Open即初始化流程进...

天王盖地虎626
今天
4
0
java 序列化和反序列化

1. 概述 序列恢复为Java对象的过程。 对象的序列化主要有两 首先我们介绍下序列化和反序列化的概念: 序列化:把Java对象转换为字节序列的过程。 反序列化:把字节序列恢复为Java对象的过程。...

edison_kwok
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部