如何查看mysql警告信息_MySQL查询执行计划的警告信息辨析一例

最新推荐文章于 2022-04-01 11:28:44 发布
最新推荐文章于 2022-04-01 11:28:44 发布
阅读量902 收藏
点赞数
文章标签： 如何查看mysql警告信息
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_36312271/article/details/113714644
版权

实例1 ：引入实例

Step 1: 创建表

CREATE TABLE `t1` (

`id` SMALLINT(6) NOT NULL DEFAULT '99',

`txt` TEXT,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO t1 (id,txt) VALUES (1,'a'), (2,'b'), (3,'c');

DROP TABLE IF EXISTS t2;

CREATE TABLE `t2` (

`id` SMALLINT(6) NOT NULL DEFAULT '99',

`txt` TEXT,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO t2 (id,txt) VALUES (1,'x'), (2,'y'), (3,'x');

Step 2: 显示执行计划,并获取警告信息

mysql> EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3 and t1.id=2;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index |

| 1 | SIMPLE | t2 | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

2 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;

+---------+------+-------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+-------------------------------------------------------------------------+

| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |

| Note | 1003 | /* select#1 */ select '2' AS `id` from `d2`.`t1` join `d2`.`t2` where 1 |

+---------+------+-------------------------------------------------------------------------+

2 rows in set (0.00 sec)

问题：

Q1：为什么警告信息中的SQL语句不等同于原始的SQL语句？

Q2：为什么警告信息中显示的WHERE子句内容, 是“where 1” ?

原因探索：

A1：MySQL在手册8.8.3节中描述如下(refman-5.7-en.html-chapter/optimization.html)：

Because the statement displayed by SHOW WARNINGS may contain special markers to provide information about query rewriting or optimizer actions, the statement is not necessarily valid SQL and is not intended to be executed.The output may also include rows with Message values that provide additional non-SQL explanatory notes about actions taken by the optimizer.

这表明：

1)查询执行计划的警告信息，只是起提示作用的，不等同于原始SQL，也不等同于优化后的被执行器执行的SQL

A2：“where 1”，产生于等式表达式，看如下实例：

实例2：等式与不等式的查询执行计划实例

Case 0:

mysql>EXPLAIN EXTENDED SELECT t2.id FROM t2 WHERE t2.id=3;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

|1|SIMPLE|t2|NULL|const|PRIMARY|PRIMARY|2|const|1|100.00|Usingindex|

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

1rowinset,2warnings(0.00sec)

mysql>show warnings;

+---------+------+-------------------------------------------------------------------+

|Level|Code|Message|

+---------+------+-------------------------------------------------------------------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.|

|Note|1003|/* select#1 */select'3'AS`id`from`d2`.`t2`where1|

+---------+------+-------------------------------------------------------------------+

2rowsinset(0.00sec)

Case 1:

--------

mysql>EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3andt1.id=2;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

|1|SIMPLE|t1|NULL|const|PRIMARY|PRIMARY|2|const|1|100.00|Usingindex|

|1|SIMPLE|t2|NULL|const|PRIMARY|PRIMARY|2|const|1|100.00|Usingindex|

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

2rowsinset,2warnings(10.87sec)

mysql>show warnings;

+---------+------+-----------------------------------------------------------------------------+

|Level|Code|Message|

+---------+------+-----------------------------------------------------------------------------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.|

|Note|1003|/* select#1 */select'2'AS`id`from`test`.`t1`join`test`.`t2`where1|

+---------+------+-----------------------------------------------------------------------------+

2rowsinset(0.00sec)

Case 2:

mysql>EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3andt1.id>2;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

|1|SIMPLE|t2|NULL|const|PRIMARY|PRIMARY|2|const|1|100.00|Usingindex|

|1|SIMPLE|t1|NULL|range|PRIMARY|PRIMARY|2|NULL|1|100.00|Usingwhere;Usingindex|

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

2rowsinset,2warnings(0.00sec)

mysql>show warnings;

+---------+------+-----------------------------------------------------------------------------------------------------------------+

|Level|Code|Message|

+---------+------+-----------------------------------------------------------------------------------------------------------------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.|

|Note|1003|/* select#1 */select`test`.`t1`.`id`AS`id`from`test`.`t1`join`test`.`t2`where((`test`.`t1`.`id`>2))|

+---------+------+-----------------------------------------------------------------------------------------------------------------+

2rowsinset(0.00sec)

Case 3:

mysql>EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3ort1.id>2;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

-------------------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra

|

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

-------------------------+

|1|SIMPLE|t1|NULL|index|PRIMARY|PRIMARY|2|NULL|3|100.00|Usingindex

|

|1|SIMPLE|t2|NULL|index|PRIMARY|PRIMARY|2|NULL|3|100.00|Usingwhere;Usingindex;Usingjoin buffer(BlockNestedLoop)|

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

-------------------------+

2rowsinset,2warnings(0.00sec)

mysql>show warnings;

+---------+------+------------------------------------------------------------------------------------------------------------------------------------

-------+

|Level|Code|Message

|

+---------+------+------------------------------------------------------------------------------------------------------------------------------------

-------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.

|

|Note|1003|/* select#1 */select`test`.`t1`.`id`AS`id`from`test`.`t1`join`test`.`t2`where((`test`.`t2`.`id`=3)or(`test`.`t1`.`id`>2))|

+---------+------+------------------------------------------------------------------------------------------------------------------------------------

-------+

2rowsinset(0.00sec)

Case 4:

mysql>EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3ort1.id=2;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

-------------------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra

|

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

-------------------------+

|1|SIMPLE|t1|NULL|index|PRIMARY|PRIMARY|2|NULL|3|100.00|Usingindex

|

|1|SIMPLE|t2|NULL|index|PRIMARY|PRIMARY|2|NULL|3|100.00|Usingwhere;Usingindex;Usingjoin buffer(BlockNestedLoop)|

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

-------------------------+

2rowsinset,2warnings(0.00sec)

mysql>show warnings;

+---------+------+------------------------------------------------------------------------------------------------------------------------------------

-------+

|Level|Code|Message

|

+---------+------+------------------------------------------------------------------------------------------------------------------------------------

-------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.

|

|Note|1003|/* select#1 */select`test`.`t1`.`id`AS`id`from`test`.`t1`join`test`.`t2`where((`test`.`t2`.`id`=3)or(`test`.`t1`.`id`=2))|

+---------+------+------------------------------------------------------------------------------------------------------------------------------------

-------+

2rowsinset(0.00sec)

Case 5:

mysql>EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t1.id<2;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra

|

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

------------+

|1|SIMPLE|t1|NULL|range|PRIMARY|PRIMARY|2|NULL|1|100.00|Usingwhere;Usingindex

|

|1|SIMPLE|t2|NULL|index|NULL|PRIMARY|2|NULL|3|100.00|Usingindex;Usingjoin buffer(BlockNestedLoop)|

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------

------------+

2rowsinset,2warnings(0.00sec)

mysql>show warnings;

+---------+------+---------------------------------------------------------------------------------------------------------------+

|Level|Code|Message|

+---------+------+---------------------------------------------------------------------------------------------------------------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.|

|Note|1003|/* select#1 */select`test`.`t1`.`id`AS`id`from`test`.`t1`join`test`.`t2`where(`test`.`t1`.`id`<2)|

+---------+------+---------------------------------------------------------------------------------------------------------------+

2rowsinset(0.00sec)

总结：

case|format|output of WARNINGS

-------+-------------------------+-------------------

case0|equality|where1

Case1|equality AND equality|where1

Case2|equality AND inequality|(`test`.`t1`.`id`>2)

Case3|equality OR inequality|((`test`.`t2`.`id`=3)or(`test`.`t1`.`id`>2))

Case4|equality OR equality|((`test`.`t2`.`id`=3)or(`test`.`t1`.`id`=2))

Case5|inequality|(`test`.`t1`.`id`<2)

-------+-------------------------+--------------------

从总结中，我们可以看出，当有等式且用AND操作的时候，表的访问类型是const，则优化器找到与等式匹配的元组，这样等式变为“3=3”(case 0)，进一步可以优化为TRUE，所以WHERE子句变为“1”表示TRUE。

实例3：与以上结论相应的实例(改造case 0的条件)

mysql>EXPLAIN EXTENDED SELECT t2.id FROM t2 WHERE t2.id=30;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

|1|SIMPLE|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|nomatching rowinconsttable|

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

1rowinset,2warnings(0.00sec)

mysql>show warnings;

+---------+------+---------------------------------------------------------------------------------+

|Level|Code|Message|

+---------+------+---------------------------------------------------------------------------------+

|Warning|1681|'EXTENDED'isdeprecatedandwill be removedina future release.|

|Note|1003|/* select#1 */selectNULL AS`id`from`d2`.`t2`wheremultiple equal(30,NULL)|

+---------+------+---------------------------------------------------------------------------------+

这表明，A2对于问题2的回答，是说明了一种特殊情况。需要注意此特殊情况成立的条件。

tianran li
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PHP+MySQL使用mysql_num_rows实现模糊查询图书信息功能
10-18
主要介绍了PHP+MySQL使用mysql_num_rows实现模糊查询图书信息功能,涉及php使用mysql的like查询语句进行模糊查询以及mysql_num_rows进行结构统计的相关操作技巧,需要的朋友可以参考下
python+mysql实现学生信息查询系统
09-09
【Python+MySQL实现学生信息查询系统】是一种常见的数据管理应用，它允许用户通过Python程序与MySQL数据库交互，以便查询和管理学生信息。在这个系统中，Python作为后端编程语言，负责处理用户输入、执行SQL查询以及...
查看程序运行过程中的错误信息
Ftworld21的专栏
09-28 368
#!/bin/bash PARA=$1 errinfo() { case $PARA in fu) INFO=`find ./run -iname "FunctionServer*err"` ;; *) INFO=`find ./run -iname "*err*"` esac cat $INFO | asan_symbolize.py | c++filt } errinfo ...
MySQL执行命令警告,MySQL的：执行命令拒绝用户``@'本地主机''的常规错误
weixin_35278163的博客
02-08 323
i got some problem during open my old website. My dataTable show:DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.After that, I tried to debug m...
mysql查询当天的告警_在mysql中查询今天、昨天、7天、近30天、本月、上一月 数据...
weixin_36429576的博客
01-19 195
今天select * from 表名 where to_days(时间字段名) = to_days(now());昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 17天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字...
如何查看mysql警告信息_查看MySQL的警告信息
weixin_33217202的博客
01-21 1972
在王MySQL数据库导入数据的时候经常会出现警告，这些警告很容易被忽视，今天到数据的时候突然想看看警告的内容是什么，百度了一下mysql查看警告的命令show warnings;命令很简明，一查看发现一般的警告是这样的Warning | 1265 | Data truncated for column ‘刊名‘ at row 1265还有一种是这样的| Warning | 1366 | Incor...
windows的MySql:查看警告信息
开发笔记
09-20 1198
SHOW WARNINGS;
基于javaweb、mysql的 学生信息管理系统
11-24
【标题】"基于javaweb、mysql的 学生信息管理系统"揭示了这是一个使用JavaWeb技术和MySQL数据库构建的教育管理软件。JavaWeb是Java技术在Web应用中的运用，包括Servlet、JSP（JavaServer Pages）和JavaBean等组件，...
MySql中如何使用 explain 查询 SQL 的执行计划
12-16
explain命令是查看查询优化器如何决定执行查询的主要方法。 这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，值得花时间去了解，因为可以学习到查询是如何执行的。 1、什么是MySQL执行计划  ...
MySQL执行计划的深入分析
09-09
MySQL执行计划是数据库管理员和开发人员用来理解和优化SQL查询性能的关键工具。它是数据库解析和执行SQL语句的蓝图，提供有关数据库如何执行查询的详细信息。执行计划可以帮助我们理解SQL语句的执行流程，包括数据的...
170: Warning: (1681, ‘Integer display width is deprecated and will be removed in a future release.’)
12-21
问题描述 在Python中使用pymysql出现如题警告： /home/data/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py:170: Warning: (1681, 'Integer display width is deprecated and will be removed in a future release.') result = self._query(query) 解决方案 创建表格时INT(M)这种用法是一种扩展属性，M表示整数数据类型的显示宽度（display width）。 对于浮点和定点数据类型
如何验证警告/确认/提示信息
echo_茵子的专栏
06-02 797
如何验证警告/确认/提示信息 对于由 window.alert() 生成的警告对话框，使用 selenium.getAlert() 来检索前一操作期间生成的 JavaScript 警告的消息。如果没有警告，该函数将会失败。得到一个警告与手动单击 OK 的结果相同。 对于由 window.confirmation() 生成的确认对话框，使用 selenium.getConfirmation() 来检索前一操作期间生成的 JavaScript 确认对话框的消息。默认情况下，该函数会返回 true，与
mysql实用技巧
binger819623的专栏
10-26 1079
 http://blog.csdn.net/fenglibing/archive/2007/10/24/1841537.aspx  MYSQL的BENCHMARK函数MYSQL中提供了一个计算表达式性能的函数BENCHMARKBENCHMARK(count,expr)BENCHMARK会重复计算expr表达式count次，通过这种方式就可以评估出mysql执行这
mysql---ENCODE警告
weixin_34253126的博客
11-15 300
'ENCODE' is deprecated and will be removed in a future release. Please use AES_ENCRYPT instead                     **********************************************************************...
MySQL出现警告：Integer display width is deprecated and will be removed in a future release
KeleQAQ的博客
02-29 1万+
Integer display width is deprecated and will be removed in a future release：整数显示宽度已弃用，将在以后的版本中删除 对于整数数据类型如INT [M]，M指示显示宽度，对于浮点和定点数据类型， M是可以存储的总位数。从MySQL 8.0.17开始，对于整数数据类型，不建议使用display width属性，即不用M显示宽...
解决Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future:
杜小白的博客
07-14 859
在php程序代码里面设置报警级别：&lt;?php error_reporting(E_ALL ^ E_DEPRECATED);还有其他解决方法，不过我是使用这个方法解决的参考链接：http://www.ttlsa.com/php/deprecated-mysql-connect/...
DeprecationWarning: sklearn.externals.joblib is deprecated in 0.21 and will be removed in 0.23. Plea
qq_40286836的博客
04-01 2149
调代码时遇到了 DeprecationWarning: sklearn.externals.joblib is deprecated in 0.21 and will be removed in 0.23. Please import this functionality directly from joblib, which can be installed with: pip install joblib. If this warning is raised when loading pickled
MySQL——执行计划
热门推荐
亦碎流年的博客
01-20 1万+
MySQL——执行计划 项目开发中，性能是我们比较关注的问题，特别是数据库的性能；作为一个开发，经常和SQL语句打交道，想要写出合格的SQL语句，我们需要了解SQL语句在数据库中是如何扫描表、如何使用索引的； MySQL提供explain/desc命令输出执行计划，我们通过执行计划优化SQL语句。 下面我们以MySQL5.7为例了解一下执行计划： 注：文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划，有不合理之处勿喷 explain/desc 用法 只需要在我们的查询.
MySQL：一文搞懂EXPLAIN命令
简单随风的博客
08-18 614
TIPS 本文基于MySQL 8.0编写，理论支持MySQL 5.0及更高版本。 EXPLAIN使用 explain可用来分析SQL的执行计划。格式如下： {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} {EXPLAIN | DES.
理解MySQL执行计划：从语法到执行
1. 了解MySQL查询的四个阶段：语法分析、生成逻辑查询执行计划、生成物理查询执行计划和执行查询计划。 2. 学会如何使用`EXPLAIN`命令显示执行计划。 3. 掌握如何解析和理解执行计划的结果。 ### MYSQL查询过程 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值