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详解  # 使用了  # 第二个  # 或者是  # 是一个  # 这是  # 子句  # 第一个  # 而不是  # 依赖于  # 是从  # 情况下  # 在对  # 递归  # 此种  # 都是  # 找到了  # 有一  # 是因为  # 放在  # 相关内容 

相关文章： 如何高效利用亚马逊云主机搭建企业网站？  如何有效防御Web建站篡改攻击？  中山网站制作网页,中山新生登记系统登记流程？  建站之星安装模板失败：服务器环境不兼容？  ,有什么在线背英语单词效率比较高的网站？  用v-html解决Vue.js渲染中html标签不被解析的问题  平台云上自助建站如何快速打造专业网站？  宝华建站服务条款解析：五站合一功能与SEO优化设置指南  已有域名建站全流程解析：网站搭建步骤与建站工具选择  建站主机如何选？高性价比方案全解析  北京制作网站的公司排名,北京三快科技有限公司是做什么？北京三快科技？  深圳网站制作案例,网页的相关名词有哪些？  如何撰写建站申请书？关键要点有哪些？  音乐网站服务器如何优化API响应速度？  无锡营销型网站制作公司,无锡网选车牌流程？  ,交易猫的商品怎么发布到网站上去？  IOS倒计时设置UIButton标题title的抖动问题  寿县云建站：智能SEO优化与多行业模板快速上线指南  盐城做公司网站,江苏电子版退休证办理流程？  python的本地网站制作,如何创建本地站点？  如何在阿里云通过域名搭建网站？  武汉网站设计制作公司,武汉有哪些比较大的同城网站或论坛，就是里面都是武汉人的？  韩国代理服务器如何选？解析IP设置技巧与跨境访问优化指南  免费网站制作模板下载,除了易企秀之外还有什么H5平台可以制作H5长页面，最好是免费的？  如何在阿里云虚拟服务器快速搭建网站？  中山网站推广排名,中山信息港登录入口？  如何在Windows虚拟主机上快速搭建网站？  如何快速生成可下载的建站源码工具？  宝塔建站无法访问？如何排查配置与端口问题？  建站之星如何优化SEO以实现高效排名？  番禺网站制作公司哪家值得合作,番禺图书馆新馆开放了吗？  建站主机选哪种环境更利于SEO优化？  网站制作公司,橙子建站是合法的吗？  制作旅游网站html,怎样注册旅游网站？  宝盒自助建站智能生成技巧：SEO优化与关键词设置指南  黑客如何利用漏洞与弱口令入侵网站服务器？  网站制作外包价格怎么算,招聘网站上写的“外包”是什么意思？  成都网站制作报价公司,成都工业用气开户费用？  电脑免费海报制作网站推荐,招聘海报哪个网站多？  整人网站在线制作软件,整蛊网站退不出去必须要打我是白痴才能出去？  C++中引用和指针有什么区别？（代码说明）  建站之星如何实现网站加密操作？  如何通过cPanel快速搭建网站？  宿州网站制作公司兴策,安徽省低保查询网站？  成都网站制作公司哪家好,四川省职工服务网是做什么用？  *服务器网站为何频现安全漏洞？  简易网站制作视频教程,使用记事本编写一个简单的网页html文件？  儿童网站界面设计图片,中国少年儿童教育网站-怎么去注册？  h5在线制作网站电脑版下载,h5网页制作软件？  建站之星安装后界面空白如何解决？