『Mysql进阶』Mysql Show Warnings 详解(六)

gorgor在码农

于 2024-10-11 10:49:38 发布

阅读量653

点赞数 16

分类专栏： msyql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_46203834/article/details/142846362

版权

msyql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Show Warnings 介绍

show warnings 命令的主要用途有两个，如下：

显示上一个命令执行过程中产生的警告信息。‌ 这个命令对于诊断和调试SQL语句非常有用，因为它可以帮助开发者或数据库管理员及时发现可能影响数据准确性和完整性的问题。
显示优化器对SQL优化的结果。show warnings只对select语句有效，对update、delete和insert是无效的，命令显示的是在select语句中优化器是怎样标准化表名和列名，这里显示的sql语句是经过重写和应用优化规则后看起来的样子，还有关于优化器过程的其它信息。

实践

数据准备

CREATE TABLE `actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `create_time`) VALUES (1,'a',NOW()), (2,'b',NOW()), (3,'c',NOW());

 CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
	`actor_id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
	`create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `actor_id`, `name`, `create_time`) VALUES (1,1,'一路向东',NOW()),(2,2,'一路向西',NOW()),(3,3,'一路向北',NOW());

CREATE TABLE `film_evaluate` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `film_id` int(10) NOT NULL,
  `sore` int(10) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
INSERT INTO `gy`.`film_evaluate`(`id`, `film_id`, `sore`, `create_time`) VALUES (1, 1, 90, '2024-10-11 10:19:22'),(2, 2, 99, '2024-10-11 10:19:31'),(3, 3, 85, '2024-10-11 10:19:44');

Sql执行，此sql没任何意义，只是为了模拟特定场景。

explain extended select * from film a 
INNER JOIN actor b on a.actor_id=b.id 
INNER JOIN film_evaluate c on c.film_id=a.id  
where c.film_id in(
		select id from film where name <> '一路向东' 
		UNION  
		select id from film where name <> '一路向北'
)
and a.actor_id not in(select id from actor where id=3);
SHOW WARNINGS;

查看 SHOW WARNINGS 结果：

可以看出，第一行结果是警告信息。第二行结果是优化器优化的sql。

我们直接看优化器优化的sql长什么样

/* select#1 */ SELECT
`gy`.`a`.`id` AS `id`,
`gy`.`a`.`actor_id` AS `actor_id`,
`gy`.`a`.`name` AS `name`,
`gy`.`a`.`create_time` AS `create_time`,
`gy`.`b`.`id` AS `id`,
`gy`.`b`.`name` AS `name`,
`gy`.`b`.`create_time` AS `create_time`,
`gy`.`c`.`id` AS `id`,
`gy`.`c`.`film_id` AS `film_id`,
`gy`.`c`.`sore` AS `sore`,
`gy`.`c`.`create_time` AS `create_time` 
FROM
	`gy`.`film` `a`
	JOIN `gy`.`actor` `b`
	JOIN `gy`.`film_evaluate` `c` 
WHERE
	((
			`gy`.`b`.`id` = `gy`.`a`.`actor_id` 
			) 
		AND ( `gy`.`a`.`id` = `gy`.`c`.`film_id` ) 
		AND < in_optimizer >(
			`gy`.`c`.`film_id`,< EXISTS >(
/* select#2 */
			SELECT
				1 
			FROM
				`gy`.`film` 
			WHERE
				((
						`gy`.`film`.`name` <> '一路向东' 
						) 
				AND (< CACHE >( `gy`.`c`.`film_id` ) = `gy`.`film`.`id` )) UNION
/* select#3 */
			SELECT
				1 
			FROM
				`gy`.`film` 
			WHERE
				((
						`gy`.`film`.`name` <> '一路向北' 
						) 
				AND (< CACHE >( `gy`.`c`.`film_id` ) = `gy`.`film`.`id` )))) 
		AND (
			NOT (< in_optimizer >(
					`gy`.`a`.`actor_id`,
					`gy`.`a`.`actor_id` IN (
						< materialize > ( /* select#4 */ SELECT '3' FROM `gy`.`actor` WHERE 1 ),
						< primary_index_lookup >(
							`gy`.`a`.`actor_id` IN < TEMPORARY TABLE > ON < auto_key > 
						WHERE
							((
								`gy`.`a`.`actor_id` = `materialized-subquery`.`id` 
	))))))))

show warnings的输出里面有很多特殊标记，其中几个常见的标记的含义如下面所示：

<auto_key>是为临时表自动创建的键（索引），
<cache>表达式被执行了一次，获得的值存储到了内存中以供后来使用。如果表达式的执行结果是多个值，可能会创建一个临时表，那么这里看到的是<temporary table> 。
<exists> 子查询匹配被转换成exists匹配，子查询需要转换，然后可以和exists一起使用的。
<in_optimizer>优化器对象，对用户来说没有意义。
<materialize>使用了子查询物化
<primary_index_lookup> 使用主键来查询符合的行。

知道了上面几个特殊标记的含义，就可以对show warnings的结果做出解释来了。

/* select#1 */对应与执行计划中id为1的操作，一共有三个操作，执行的顺序是按从上到下依次执行的，这是一个三表连接操作，连接的顺序是a,b,c .后面的where条件里先出现的是这三个表连接的join键，后面的两个部分分别对应sql语句中的其它两个where条件，<in_optimizer>表示后面的对象是优化器对象，可以不用关注。

第一个where条件的<exists>表明后面的子查询被优化器转换成了exist表达式，/* select#2 */和/* select#3 */分别对应执行计划中id为2和3的查询，id为2的是依赖子查询，id为3 的是依赖union，它们依赖外层的film表传来的film_id，首次访问film_id后，其值被缓存起来以供后续使用。

第二个where条件中的子查询使用了物化优化，/* select#4 */对应执行计划中的id为4的操作，从<primary_index_lookup>可以看出这个查询使用主键查询物化的临时表的行，使用的主键是MySQL为物化临时表自动创建的<auto_key>。

从show warnings的结果中把所有的特殊标记去掉，就是经过优化器改写和转换后的sql语句。可以看出，MySQL优化器把第一个子查询转换成了exists表达式，对第二个子查询进行了物化优化。