mysql優(yōu)化利器之explain使用介紹

更新時間：2017年01月09日 23:16:54 作者：踏雪無痕

這篇文章主要介紹了mysql優(yōu)化利器之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 |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

MySQL Query Optimizer選定的執(zhí)行計劃中查詢的序列號。
表示查詢中執(zhí)行select子句或操作表的順序，id值越大優(yōu)先級越高，越先被執(zhí)行。id相同，執(zhí)行順序由上至下

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會遞歸執(zhí)行這些子查詢，把結果放在臨時表里。
7、UNCACHEABLE SUBQUERY:結果集不能被緩存的子查詢，必須重新為外層查詢的每一行進行評估
8、UNCACHEABLE UNION:UNION中的第二個或隨后的select查詢，屬于不可緩存的子查詢

table

1、system：表僅有一行(系統(tǒng)表)。這是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：說明索引合并優(yōu)化被使用了。
7、unique_subquery：在某些IN查詢中使用此種類型，而不是常規(guī)的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：全表掃描，只是掃描表的時候按照索引次序進行而不是行。主要優(yōu)點就是避免了排序，但是開銷仍然非常大。
10、all：最壞的情況，從頭到尾全表掃描

others

possible_keys：指出mysql能在該表中使用哪些索引有助于查詢。如果為空，說明沒有可用的索引
key：mysql實際從possible_key選擇使用的索引。如果為null，則沒有使用索引。
    很少的情況下，mysql會選擇優(yōu)化不足的索引。這種情況下，
    可以在select語句中使用use index（indexname）來強制使用一個索引
    或者用ignore index（indexname）來強制mysql忽略索引
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref：顯示索引的哪一列被使用了
rows：mysql認為必須檢查的用來返回請求數據的行數

extra

1、Distinct: 一旦mysql找到了與行相聯(lián)合匹配的行，就不再搜索了。
2、Not exists: mysql 優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了。
3、Range checked for each: Record（index map:#）沒有找到理想的索引，
    因此對于從前面表中來的每一個行組合，mysql檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一。
4、Using filesort: 表示MySQL會對結果使用一個外部索引排序，而不是從表里按索引次序讀到相關內容。
    可能在內存或者磁盤上進行排序。MySQL中無法利用索引完成的排序操作稱為“文件排序”。
5、Using index: 列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，
    這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候。
6、Using temporary: mysql需要創(chuàng)建一個臨時表來存儲結果，這通常發(fā)生在對不同的列集進行ORDER BY上，而不是GROUP BY上。
7、Using where: 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。
    如果不想返回表中的全部行，并且連接類型ALL或index，這就會發(fā)生，或者是查詢有問題。

四、具體的實例

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唯一索引和普通索引選哪個？
這篇文章主要介紹了MySQL唯一索引和普通索引的優(yōu)劣，幫助大家更好的理解和使用MySQL索引，感興趣的朋友可以了解下
2020-10-10
MySQL查詢和修改auto_increment的方法
這篇文章主要介紹了MySQL查詢和修改auto_increment的方法,實例分析了select查詢auto_increment及ALTER修改auto_increment的技巧,需要的朋友可以參考下
2015-02-02
Mysql命令大全(詳細篇)
這篇文章主要介紹了Mysql命令大全，這里整理的算是比較詳細的,需要的朋友可以參考下
2015-11-11
MySQL多版本并發(fā)控制mvcc原理淺析
mvcc多版本并發(fā)控制是一種數據庫的并發(fā)控制機制,本文主要介紹了MySQL多版本并發(fā)控制mvcc原理淺析,具有一定的參考價值,感興趣的可以了解一下
2024-07-07
詳解讓MySQL和Redis數據保持一致的四種策略
在分布式系統(tǒng)中,保證Redis和MySQL之間的數據一致性是一個復雜且重要的問題,下面這篇文章主要給大家介紹了關于讓MySQL和Redis數據保持一致的四種策略,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2024-07-07
MySQL中設置NULL和空白字符串的問題及解決
這篇文章主要介紹了MySQL中設置NULL和空白字符串的問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2022-08-08
Mysql字符串截取及獲取指定字符串中的數據
小編童鞋最近接了一個新需求，需要在MySql的字段中截取一段字符串中的特定字符，下面小編把我的核心代碼分享給大家，對mysql 字符串截取相關知識感興趣的朋友一起看看吧
2019-11-11
mysql err錯誤提示You need to use --log-
今天在檢查mysql服務器錯誤日志的時候發(fā)現You need to use --log-bin to make --binlog-format work，通過如下方法解決了特分享一下
2023-06-06
sql面試題(查看數據中指定幾行記錄)
一個不錯的sql面試題，表 table1,主鍵為 ID,ID為自動編號（ID可能不連續(xù)），要求查詢第31-40行記錄，如何實現呢？感興趣的朋友參考下
2014-05-05
MySQL中order?by的執(zhí)行過程
這篇文章主要介紹了MySQL中order?by的執(zhí)行過程，一訂單表為例展開相應的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下
2022-06-06