mysql优化利器之使用explain的方法

来源：www.45fan.com 2017-02-02 08:03:35

一、语法

{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 |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

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的方法

推荐阅读