数据库

mysql优化利器之explain使用介绍
数据库 2024/11/5 佚名
3 2

一、语法

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

完成

标签：
mysql,优化利器,explain
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
白云城资源网 Copyright www.dyhadc.com

评论“mysql优化利器之explain使用介绍”

暂无“mysql优化利器之explain使用介绍”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源
一句话新闻
一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5

这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。

一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。

另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com