mysql优化利器之explain使用介绍

一、语法

{EXPLAIN | DESCRIBE | DESC}
  tbl_name [col_name | wild] 
{EXPLAIN | DESCRIBE | DESC}
  [explain_type] SELECT select_options 
explain_type: {EXTENDED | PARTITIONS}

二、数据库准备

表一：

DROP TABLE IF EXISTS `products`;
SET @saved_cs_client   = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `products` (
 `products_id` int(11) unsigned NOT NULL auto_increment,
 `products_type` int(11) unsigned NOT NULL default '1',
 `products_quantity` float NOT NULL default '0',
 `products_model` varchar(32) default NULL,
 `products_upc` varchar(32) default NULL,
 `products_isbn` varchar(32) default NULL,
 `products_image` varchar(128) default NULL,
 `products_image_thumbnail` varchar(200) NOT NULL，
 `products_price` decimal(15,4) NOT NULL default '0.0000',
 `products_virtual` tinyint(1) NOT NULL default '0',
 `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00',
 `products_last_modified` datetime default NULL,
 `products_date_available` datetime default NULL,
 `products_weight` float NOT NULL default '0',
 `products_status` tinyint(1) NOT NULL default '0',
 `products_tax_class_id` int(11) NOT NULL default '0',
 `manufacturers_id` int(11) default NULL,
 `products_web_id` int(11) default NULL,
 `products_ordered` float NOT NULL default '0',
 `products_quantity_order_min` float NOT NULL default '1',
 `products_quantity_order_units` float NOT NULL default '1',
 `products_priced_by_attribute` tinyint(1) NOT NULL default '0',
 `product_is_free` tinyint(1) NOT NULL default '0',
 `product_is_call` tinyint(1) NOT NULL default '0',
 `products_quantity_mixed` tinyint(1) NOT NULL default '0',
 `product_is_always_free_shipping` tinyint(1) NOT NULL default '0',
 `products_qty_box_status` tinyint(1) NOT NULL default '1',
 `products_quantity_order_max` float NOT NULL default '0',
 `products_sort_order` int(11) NOT NULL default '0',
 `products_discount_type` tinyint(1) NOT NULL default '0',
 `products_discount_type_from` tinyint(1) NOT NULL default '0',
 `products_price_sorter` decimal(15,4) NOT NULL default '0.0000',
 `master_categories_id` int(11) NOT NULL default '0',
 `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1',
 `metatags_title_status` tinyint(1) NOT NULL default '0',
 `metatags_products_name_status` tinyint(1) NOT NULL default '0',
 `metatags_model_status` tinyint(1) NOT NULL default '0',
 `metatags_price_status` tinyint(1) NOT NULL default '0',
 `metatags_title_tagline_status` tinyint(1) NOT NULL default '0',
 `itemno` varchar(32) default NULL,
 `products_images_no` varchar(10) default '0',
 `products_url` varchar(512) default NULL,
 PRIMARY KEY (`products_id`),
 UNIQUE KEY `itemno` (`itemno`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

表二：

DROP TABLE IF EXISTS `products_image`;
SET @saved_cs_client   = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `products_image` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `products_id` int(10) unsigned NOT NULL,
 `products_images_no` varchar(10) default '0'，
 `image_dir` varchar(200) default NULL,
 `products_image_thumbnail` varchar(200) default NULL，
 `flag` int(2) default NULL,
 `up_time` datetime default NULL,
 `web_from` varchar(20) default NULL,
 PRIMARY KEY (`id`),
 KEY `idx_porducts_id` (`products_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

三、关于explain选项

下面是一个实例：

mysql> explain select products_id from products limit 1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref | rows | Extra    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE   | products | index | NULL     | PRIMARY | 4    | NULL | 3113 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

id

MySQL  Query  Optimizer选定的执行计划中查询的序列号。
表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行。id相同，执行顺序由上至下

select_type

1、SIMPLE：简单的select查询，不使用union及子查询
2、PRIMARY：最外层的select查询
3、UNION：UNION中的第二个或随后的select查询，不依赖于外部查询的结果集
4、DEPENDENT UNION：UNION中的第二个或随后的select查询，依赖于外部查询的结果集
5、UNION RESULT： UNION查询的结果集SUBQUERY子查询中的第一个select查询，不依赖于外部查询的结果集
6、DEPENDENT SUBQUERY:子查询中的第一个select查询，依赖于外部查询的结果集DERIVED用于from子句里有子查询的情况。
    MySQL会递归执行这些子查询，把结果放在临时表里。
7、UNCACHEABLE SUBQUERY:结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估
8、UNCACHEABLE UNION:UNION中的第二个或随后的select查询，属于不可缓存的子查询

table

1、system：表仅有一行(系统表)。这是const连接类型的一个特例。
2、const：const用于用常数值比较PRIMARY KEY时。当查询的表仅有一行时，使用system。
3、eq_ref：除const类型外最好的可能实现的连接类型。它用在一个索引的所有部分被连接使用并且索引是UNIQUE或PRIMARY KEY，
    对于每个索引键，表中只有一条记录与之匹配。
4、ref：连接不能基于关键字选择单个行，可能查找到多个符合条件的行。叫做ref是因为索引要跟某个参考值相比较。
    这个参考值或者是一个常数，或者是来自一个表里的多表查询的结果值。
5、ref_or_null：如同ref，但是MySQL必须在初次查找的结果里找出null条目，然后进行二次查找。
6、index_merge：说明索引合并优化被使用了。
7、unique_subquery：在某些IN查询中使用此种类型，而不是常规的ref：
    value IN (SELECT primary_key FROM single_table WHERE some_expr)
    index_subquery在某些IN查询中使用此种类型，与unique_subquery类似，但是查询的是非唯一性索引：
    value IN (SELECT key_column FROM single_table WHERE some_expr)
8、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。
    当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。
9、index：全表扫描，只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序，但是开销仍然非常大。
10、all：最坏的情况，从头到尾全表扫描

 others

possible_keys：指出mysql能在该表中使用哪些索引有助于查询。如果为空，说明没有可用的索引
key：mysql实际从possible_key选择使用的索引。如果为null，则没有使用索引。
    很少的情况下，mysql会选择优化不足的索引。这种情况下，
    可以在select语句中使用use  index（indexname）来强制使用一个索引
    或者用ignore  index（indexname）来强制mysql忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了
rows：mysql认为必须检查的用来返回请求数据的行数

extra

1、Distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了。
2、Not exists: mysql 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
3、Range checked for each: Record（index map:#）没有找到理想的索引，
    因此对于从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
4、Using filesort: 表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。
    可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为“文件排序”。
5、Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
    这发生在对表的全部的请求列都是同一个索引的部分的时候。
6、Using temporary: mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
7、Using where: 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。
    如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

四、具体的实例

1、mysql版本

mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

2、sql语句分析1

mysql> explain select products_id from products; 
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref | rows | Extra    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE   | products | index | NULL     | PRIMARY | 4    | NULL | 3113 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------

3、sql语句分析2

mysql> explain select products_id from (select * from products limit 10) b ;    
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY   | <derived2> | ALL | NULL     | NULL | NULL  | NULL |  10 |    |
| 2 | DERIVED   | products  | ALL | NULL     | NULL | NULL  | NULL | 3113 |    |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

4、sql语句分析3

mysql> explain select products_id from products where products_id=10 union select products_id \
from products where products_id=20 ;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref  | rows | Extra    |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY   | products  | const | PRIMARY    | PRIMARY | 4    | const |  1 | Using index |
| 2 | UNION    | products  | const | PRIMARY    | PRIMARY | 4    | const |  1 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL     | NULL  | NULL  | NULL | NULL |       |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+

5、sql语句分析4

mysql> explain select * from products where products_id in ( select products_id from products where \
products_id=10 union select products_id from products where products_id=20 );
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type    | table   | type | possible_keys | key   | key_len | ref  | rows | Extra    |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY      | products  | ALL  | NULL     | NULL  | NULL  | NULL | 3113 | Using where |
| 2 | DEPENDENT SUBQUERY | products  | const | PRIMARY    | PRIMARY | 4    | const |  1 | Using index |
| 3 | DEPENDENT UNION  | products  | const | PRIMARY    | PRIMARY | 4    | const |  1 | Using index |
| NULL | UNION RESULT    | <union2,3> | ALL  | NULL     | NULL  | NULL  | NULL | NULL |       |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+

完成

# mysql  # 优化利器  # explain  # MySQL查询优化之explain的深入解析  # mysql explain的用法(使用explain优化查询语句)  # MySQL查询语句过程和EXPLAIN语句基本概念及其优化  # MySQL性能优化神器Explain的基本使用分析  # MySQL索引优化Explain详解  # MySQL如何基于Explain关键字优化索引功能  # mysql优化之慢查询分析+explain命令分析+优化技巧总结  # 全面解析MySQL Explain如何优化SQL查询性能  # 轻松上手MYSQL之SQL优化之Explain详解  # 使用了  # 第二个  # 或者是  # 是一个  # 这是  # 子句  # 第一个  # 而不是  # 依赖于  # 是从  # 情况下  # 在对  # 递归  # 此种  # 都是  # 找到了  # 有一  # 是因为  # 放在  # 相关内容 

相关文章： 为什么Go需要go mod文件_Go go mod文件作用说明  开心动漫网站制作软件下载,十分开心动画为何停播？  网站建设设计制作营销公司南阳,如何策划设计和建设网站？  魔方云NAT建站如何实现端口转发？  c++怎么实现高并发下的无锁队列_c++ std::atomic原子变量与CAS操作【详解】  香港服务器如何优化才能显著提升网站加载速度？  定制建站价位费用解析与套餐推荐全攻略  公司网站制作价格怎么算,公司办个官网需要多少钱？  贸易公司网站制作流程,出口贸易网站设计怎么做？  在线制作视频的网站有哪些,电脑如何制作视频短片？  如何选择高效响应式自助建站源码系统？  如何用低价快速搭建高质量网站？  怎么将XML数据可视化 D3.js加载XML  建站之星好吗？新手能否轻松上手建站？  韩国服务器如何优化跨境访问实现高效连接？  视频网站制作教程,怎么样制作优酷网的小视频？  建站主机核心功能解析：服务器选择与网站搭建流程指南  如何获取免费开源的自助建站系统源码？  如何正确选择百度移动适配建站域名？  ,想在网上投简历，哪几个网站比较好？  制作企业网站建设方案,怎样建设一个公司网站？  建站之星代理如何获取技术支持？  如何快速生成凡客建站的专业级图册？  网站制作培训多少钱一个月,网站优化seo培训课程有哪些？  建站org新手必看：2024最新搭建流程与模板选择技巧  教程网站设计制作软件,怎么创建自己的一个网站？  定制建站策划方案_专业建站与网站建设方案一站式指南  高端智能建站公司优选：品牌定制与SEO优化一站式服务  唐山网站制作公司有哪些,唐山找工作哪个网站最靠谱？  如何快速搭建FTP站点实现文件共享？  网站视频怎么制作,哪个网站可以免费收看好莱坞经典大片？  如何在搬瓦工VPS快速搭建网站？  武清网站制作公司,天津武清个人营业执照注销查询系统网站？  建站之星免费版是否永久可用？  如何在万网主机上快速搭建网站？  学校建站服务器如何选型才能满足性能需求？  建站之星安装路径如何正确选择及配置？  如何用5美元大硬盘VPS安全高效搭建个人网站？  Android自定义控件实现温度旋转按钮效果  长春网站建设制作公司,长春的网络公司怎么样主要是能做网站的？  无锡营销型网站制作公司,无锡网选车牌流程？  C++ static_cast和dynamic_cast区别_C++静态转换与动态类型安全转换  如何高效完成独享虚拟主机建站？  制作门户网站的参考文献在哪,小说网站怎么建立？  网站规划与制作是什么,电子商务网站系统规划的内容及步骤是什么？  香港网站服务器数量如何影响SEO优化效果？  建站主机与服务器功能差异如何区分？  如何挑选高效建站主机与优质域名？  建站中国官网：模板定制+SEO优化+建站流程一站式指南  ,石家庄四十八中学官网？