ToB企服应用市场:ToB评测及商务社交产业平台

标题: Explain:你见过这样的Sql吗? [打印本页]
作者: 梦见你的名字    时间: 2022-10-13 19:05
标题: Explain:你见过这样的Sql吗?
上一篇我们讲到Mysql索引底层逻辑，为了了解后续sql知识，我们还是需要先学习一下相关“工具”得使用
一、Explain介绍

EXPLAIN是MySQl必不可少的一个分析工具，主要用来测试sql语句的性能及对sql语句的优化，或者说模拟优化器执行SQL语句。在select语句之前增加explain关键字，执行后MySQL就会返回执行计划的信息，而不是执行sql。
注意：如果from中包含子查询，仍会执行子查询，将结果放入到临时表中
Explain的用法还是很简单的，类似一个关键字，无需记住什么语法相关的东西，我们主要来看他的输出，接下来我们看一下他的常见输出并分情况进行讨论：
首先我们创建三张表并插入一些相关数据
  1. -- ----------------------------
  3. -- Table structure for user
  5. -- ----------------------------
  7. DROP TABLE IF EXISTS `user`;
  9. CREATE TABLE `user` (
  11.   `id` int(11) NOT NULL,
  13.   `name` varchar(45) DEFAULT NULL,
  15.   `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  17.   `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  19.   PRIMARY KEY (`id`)
  21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  23. -- ----------------------------
  25. -- Records of user
  27. -- ----------------------------
  29. INSERT INTO `user` VALUES ('1', '小明', '2022-09-15 15:52:18', '2022-09-13 15:52:21');
  31. INSERT INTO `user` VALUES ('2', '小红', '2022-09-14 15:52:35', '2022-09-06 15:52:41');
  33. INSERT INTO `user` VALUES ('3', '小可', '2022-09-15 15:52:55', '2022-09-15 15:52:58');
  35. INSERT INTO `user` VALUES ('4', '张三', '2022-09-13 15:53:13', '2022-09-14 15:53:17');
  37. INSERT INTO `user` VALUES ('5', '李四', '2022-09-15 15:53:35', '2022-09-15 15:53:37');
  39. INSERT INTO `user` VALUES ('6', '王五', '2022-09-15 15:53:47', '2022-09-15 15:53:49');
  41. INSERT INTO `user` VALUES ('7', '小小', '2022-09-15 15:54:06', '2022-09-15 15:54:08');
  43. -- ----------------------------
  45. -- Table structure for address
  47. -- ----------------------------
  49. DROP TABLE IF EXISTS `address`;
  51. CREATE TABLE `address` (
  53.   `id` int(11) NOT NULL AUTO_INCREMENT,
  55.   `name` varchar(45) DEFAULT NULL,
  57.   PRIMARY KEY (`id`),
  59.   KEY `idx_name` (`name`)
  61. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
  63. -- ----------------------------
  65. -- Records of address
  67. -- ----------------------------
  69. INSERT INTO `address` VALUES ('2', '上海');
  71. INSERT INTO `address` VALUES ('1', '北京');
  73. INSERT INTO `address` VALUES ('3', '北京');
  75. INSERT INTO `address` VALUES ('5', '南京');
  77. INSERT INTO `address` VALUES ('6', '武汉');
  79. INSERT INTO `address` VALUES ('4', '深圳');
  81. INSERT INTO `address` VALUES ('7', '长沙');
  83. -- ----------------------------
  85. -- Table structure for user_address
  87. -- ----------------------------
  89. DROP TABLE IF EXISTS `user_address`;
  91. CREATE TABLE `user_address` (
  93.   `id` int(11) NOT NULL,
  95.   `address_id` int(11) NOT NULL,
  97.   `user_id` int(11) NOT NULL,
  99.   `remark` varchar(255) DEFAULT NULL,
  101.   PRIMARY KEY (`id`),
  103.   KEY `idx_user_address_id` (`address_id`,`user_id`)
  105. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码
首先来分析一下我们的表结构user表我们只有一个聚集索引，其实就是主键索引，那他的索引结构就只是id列，如下图所示：

 
 
 
address表不单单只有我们的聚集索引，也添加了一个二级索引，索引结构如下图所示：

 

user_address也不仅只有聚集索引，也有一个联合索引,相对应的列分别是address_id和user_id，索引结构如下图所示：


 
二、Explain中的列

1、id列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。
id列越大执行优先级越高，id相同则从上向下执行，id为null最后执行。
2、select_type列

2.1、Simple:简单查询，查询不包含子查询和union
  1. EXPLAIN SELECT * from `user` WHERE id = 1;
复制代码

2.2、Primary:复杂查询中最外层的select
2.3、Subquery:包含在select中的子查询（不在from子句中）
2.4、Derived：包含在from子句中的子查询。Mysql会将结果存放到一个临时表中，也成为派生表（derived的英文含义）
  1. EXPLAIN SELECT (SELECT 1 FROM `user` WHERE id = 1) FROM (SELECT * FROM address WHERE id =1) der;
复制代码

注意：这里要先关闭一下mysql5.7新特性对衍生表的合并优化
  1. set session optimizer_switch='derived_merge=off';
复制代码

3、Table

对应正在访问的哪一个表，显示的是表明或者是别命，可能是临时表或者union合并结果集如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别命
表明是derived的形式，表明使用了id为N的查询产生的衍生表，如下图所示：

derived后面的id号为3，表明使用id为3的这个查询产生的衍生表，也就是（SELECT * FROM address WHERE id =1）这个查询语句结果集所在的的临时表
当有union result的时候，表名是union n1,n2等形式， n1,n2表示参与union的id
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中取最小值，可以单独查找索引来完成，不需要在执行时访问表

上面的语句我们是通过主键id的方式来查找的，如果看过我们上一篇博客的读者就能够明白，此时直接查索引就可以了，找到最小的，无需查表。
4、possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
5、key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
如果没有使用索引，则该列是null，如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index。
基于4、5两个，有可能出现这种情况：possible_keys有值，key没有值，这种情况下有可能是因为分析的时候需要用索引，真正执行的时候发现不走索引的化还会快一点。
6、Type

这一列b表示关联类型或者访问类型，即Mysql决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：
system>const>eq_ref>ref>range>index>ALL
一般来说，的保证查询达到range级别，最好达到ref。
1）NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中取最小值，可以单独查找索引来完成，不需要在执行时访问表

2）system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

3）const：这个表至多有一个匹配行，
Const为常量的意思，他可能想要表达出查询的效率非常高，跟查一个常量式的，用我们的唯一索引，或者主键的时候，因为无重复值，所以查询效率非常高

4）eq_ref:多表连接中使用primary key或者 unique key作为关联条件，使用唯一性索引进行数据查找，也就是被关联表上的关联列走的是主键或者唯一索引这可能是在const之外最好的联接类型了，简单的select查询不会出现这种type

5）ref：使用了非唯一性索引进行数据的查找或者非唯一性索引的部分前缀
5.1)简单查询(非唯一索引)

5.2）关联表查询,idx_user_address_id是address_id和user_id的联合索引，这里使用到了user_address的左边前缀address_id部分

6）ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式,简单得来说就是二级索引等值查询也能搜索到值为null得行。（注意，此时在表中添加了一行为null得数据）

7）index_merge：在查询过程中需要多个索引组合使用


8）range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, , >, >=,



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4