《MYSQL數據庫mysql優化利器之explain使用介紹》要點：
本文介紹了MYSQL數據庫mysql優化利器之explain使用介紹，希望對您有用。如果有疑問，可以聯系我們。
MYSQL數據庫一、語法
MYSQL數據庫
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type] SELECT select_options
explain_type: {EXTENDED | PARTITIONS}
MYSQL數據庫二、數據庫準備
MYSQL數據庫表一：
MYSQL數據庫
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;
MYSQL數據庫表二：
MYSQL數據庫
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;
MYSQL數據庫三、關于explain選項
MYSQL數據庫下面是一個實例：
MYSQL數據庫
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數據庫id
MYSQL數據庫MySQL? Query? Optimizer選定的執行計劃中查詢的序列號.
表示查詢中執行select子句或操作表的順序,id值越大優先級越高,越先被執行.id相同,執行順序由上至下
MYSQL數據庫select_type
MYSQL數據庫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查詢,屬于不可緩存的子查詢
MYSQL數據庫table
MYSQL數據庫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：最壞的情況,從頭到尾全表掃描
MYSQL數據庫?others
MYSQL數據庫possible_keys：指出mysql能在該表中使用哪些索引有助于查詢.如果為空,說明沒有可用的索引
key：mysql實際從possible_key選擇使用的索引.如果為null,則沒有使用索引.
??? 很少的情況下,mysql會選擇優化不足的索引.這種情況下,
??? 可以在select語句中使用use? index(indexname)來強制使用一個索引
??? 或者用ignore? index(indexname)來強制mysql忽略索引
key_len：使用的索引的長度.在不損失精確性的情況下,長度越短越好
ref：顯示索引的哪一列被使用了
rows：mysql認為必須檢查的用來返回請求數據的行數
MYSQL數據庫extra
MYSQL數據庫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,這就會發生,或者是查詢有問題.
MYSQL數據庫四、具體的實例
MYSQL數據庫1、mysql版本
MYSQL數據庫
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)
MYSQL數據庫2、sql語句分析1
MYSQL數據庫
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 |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------
MYSQL數據庫3、sql語句分析2
MYSQL數據庫
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 | |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
MYSQL數據庫4、sql語句分析3
MYSQL數據庫
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 | |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
MYSQL數據庫5、sql語句分析4
MYSQL數據庫
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數據庫完成
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/2667.html
