mysql优化利器之explain使用介绍

2024-03-05 14:59:04 322

一、语法

{EXPLAIN|DESCRIBE|DESC}
tbl_name[col_name|wild]
{EXPLAIN|DESCRIBE|DESC}
[explain_type]SELECTselect_options
explain_type:{EXTENDED|PARTITIONS}

二、数据库准备

表一：

DROPTABLEIFEXISTS`products`;
SET@saved_cs_client=@@character_set_client;
SETcharacter_set_client=utf8;
CREATETABLE`products`(
`products_id`int(11)unsignedNOTNULLauto_increment,
`products_type`int(11)unsignedNOTNULLdefault'1',
`products_quantity`floatNOTNULLdefault'0',
`products_model`varchar(32)defaultNULL,
`products_upc`varchar(32)defaultNULL,
`products_isbn`varchar(32)defaultNULL,
`products_image`varchar(128)defaultNULL,
`products_image_thumbnail`varchar(200)NOTNULL，
`products_price`decimal(15,4)NOTNULLdefault'0.0000',
`products_virtual`tinyint(1)NOTNULLdefault'0',
`products_date_added`datetimeNOTNULLdefault'0001-01-0100:00:00',
`products_last_modified`datetimedefaultNULL,
`products_date_available`datetimedefaultNULL,
`products_weight`floatNOTNULLdefault'0',
`products_status`tinyint(1)NOTNULLdefault'0',
`products_tax_class_id`int(11)NOTNULLdefault'0',
`manufacturers_id`int(11)defaultNULL,
`products_web_id`int(11)defaultNULL,
`products_ordered`floatNOTNULLdefault'0',
`products_quantity_order_min`floatNOTNULLdefault'1',
`products_quantity_order_units`floatNOTNULLdefault'1',
`products_priced_by_attribute`tinyint(1)NOTNULLdefault'0',
`product_is_free`tinyint(1)NOTNULLdefault'0',
`product_is_call`tinyint(1)NOTNULLdefault'0',
`products_quantity_mixed`tinyint(1)NOTNULLdefault'0',
`product_is_always_free_shipping`tinyint(1)NOTNULLdefault'0',
`products_qty_box_status`tinyint(1)NOTNULLdefault'1',
`products_quantity_order_max`floatNOTNULLdefault'0',
`products_sort_order`int(11)NOTNULLdefault'0',
`products_discount_type`tinyint(1)NOTNULLdefault'0',
`products_discount_type_from`tinyint(1)NOTNULLdefault'0',
`products_price_sorter`decimal(15,4)NOTNULLdefault'0.0000',
`master_categories_id`int(11)NOTNULLdefault'0',
`products_mixed_discount_quantity`tinyint(1)NOTNULLdefault'1',
`metatags_title_status`tinyint(1)NOTNULLdefault'0',
`metatags_products_name_status`tinyint(1)NOTNULLdefault'0',
`metatags_model_status`tinyint(1)NOTNULLdefault'0',
`metatags_price_status`tinyint(1)NOTNULLdefault'0',
`metatags_title_tagline_status`tinyint(1)NOTNULLdefault'0',
`itemno`varchar(32)defaultNULL,
`products_images_no`varchar(10)default'0',
`products_url`varchar(512)defaultNULL,
PRIMARYKEY(`products_id`),
UNIQUEKEY`itemno`(`itemno`)
)ENGINE=MyISAMAUTO_INCREMENT=1DEFAULTCHARSET=utf8;
SETcharacter_set_client=@saved_cs_client;

表二：

DROPTABLEIFEXISTS`products_image`;
SET@saved_cs_client=@@character_set_client;
SETcharacter_set_client=utf8;
CREATETABLE`products_image`(
`id`int(10)unsignedNOTNULLauto_increment,
`products_id`int(10)unsignedNOTNULL,
`products_images_no`varchar(10)default'0'，
`image_dir`varchar(200)defaultNULL,
`products_image_thumbnail`varchar(200)defaultNULL，
`flag`int(2)defaultNULL,
`up_time`datetimedefaultNULL,
`web_from`varchar(20)defaultNULL,
PRIMARYKEY(`id`),
KEY`idx_porducts_id`(`products_id`)
)ENGINE=MyISAMAUTO_INCREMENT=1DEFAULTCHARSET=utf8;
SETcharacter_set_client=@saved_cs_client;

三、关于explain选项

下面是一个实例：

mysql>explainselectproducts_idfromproductslimit1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|1|SIMPLE|products|index|NULL|PRIMARY|4|NULL|3113|Usingindex|
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

MySQL Query Optimizer选定的执行计划中查询的序列号。
表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行。id相同，执行顺序由上至下

select_type

1、SIMPLE：简单的select查询，不使用union及子查询
2、PRIMARY：最外层的select查询
3、UNION：UNION中的第二个或随后的select查询，不依赖于外部查询的结果集
4、DEPENDENTUNION：UNION中的第二个或随后的select查询，依赖于外部查询的结果集
5、UNIONRESULT：UNION查询的结果集SUBQUERY子查询中的第一个select查询，不依赖于外部查询的结果集
6、DEPENDENTSUBQUERY:子查询中的第一个select查询，依赖于外部查询的结果集DERIVED用于from子句里有子查询的情况。
MySQL会递归执行这些子查询，把结果放在临时表里。
7、UNCACHEABLESUBQUERY:结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估
8、UNCACHEABLEUNION:UNION中的第二个或随后的select查询，属于不可缓存的子查询

table

1、system：表仅有一行(系统表)。这是const连接类型的一个特例。
2、const：const用于用常数值比较PRIMARYKEY时。当查询的表仅有一行时，使用system。
3、eq_ref：除const类型外最好的可能实现的连接类型。它用在一个索引的所有部分被连接使用并且索引是UNIQUE或PRIMARYKEY，
对于每个索引键，表中只有一条记录与之匹配。
4、ref：连接不能基于关键字选择单个行，可能查找到多个符合条件的行。叫做ref是因为索引要跟某个参考值相比较。
这个参考值或者是一个常数，或者是来自一个表里的多表查询的结果值。
5、ref_or_null：如同ref，但是MySQL必须在初次查找的结果里找出null条目，然后进行二次查找。
6、index_merge：说明索引合并优化被使用了。
7、unique_subquery：在某些IN查询中使用此种类型，而不是常规的ref：
valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)
index_subquery在某些IN查询中使用此种类型，与unique_subquery类似，但是查询的是非唯一性索引：
valueIN(SELECTkey_columnFROMsingle_tableWHEREsome_expr)
8、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。
当使用=、<>、>、>=、<、<=、ISNULL、<=>、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、Notexists:mysql优化了LEFTJOIN，一旦它找到了匹配LEFTJOIN标准的行，就不再搜索了。
3、Rangecheckedforeach:Record（indexmap:#）没有找到理想的索引，
因此对于从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
4、Usingfilesort:表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。
可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为“文件排序”。
5、Usingindex:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
这发生在对表的全部的请求列都是同一个索引的部分的时候。
6、Usingtemporary:mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDERBY上，而不是GROUPBY上。
7、Usingwhere:使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。
如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

四、具体的实例

1、mysql版本

mysql>selectversion();
+------------+
|version()|
+------------+
|5.1.73-log|
+------------+
1rowinset(0.00sec)

2、sql语句分析1

mysql>explainselectproducts_idfromproducts;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|1|SIMPLE|products|index|NULL|PRIMARY|4|NULL|3113|Usingindex|
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------

3、sql语句分析2

mysql>explainselectproducts_idfrom(select*fromproductslimit10)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>explainselectproducts_idfromproductswhereproducts_id=10unionselectproducts_id\
fromproductswhereproducts_id=20;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|1|PRIMARY|products|const|PRIMARY|PRIMARY|4|const|1|Usingindex|
|2|UNION|products|const|PRIMARY|PRIMARY|4|const|1|Usingindex|
|NULL|UNIONRESULT|<union1,2>|ALL|NULL|NULL|NULL|NULL|NULL||
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+

5、sql语句分析4

mysql>explainselect*fromproductswhereproducts_idin(selectproducts_idfromproductswhere\
products_id=10unionselectproducts_idfromproductswhereproducts_id=20);
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|1|PRIMARY|products|ALL|NULL|NULL|NULL|NULL|3113|Usingwhere|
|2|DEPENDENTSUBQUERY|products|const|PRIMARY|PRIMARY|4|const|1|Usingindex|
|3|DEPENDENTUNION|products|const|PRIMARY|PRIMARY|4|const|1|Usingindex|
|NULL|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL||
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+

完成

mysql优化利器之explain使用介绍

热门推荐

随机推荐