mysql優化利器之explain運用引見。本站提示廣大學習愛好者：（mysql優化利器之explain運用引見）文章只能為提供參考，不一定能成為您想要的結果。以下是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 | | +----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
完成
