在MySQL中调试SQL查询

最新推荐文章于 2023-09-27 11:03:20 发布

danpu0978

最新推荐文章于 2023-09-27 11:03:20 发布

阅读量506

点赞数

文章标签：字符串 mysql java sql python

最近，我开始编写用于分析和调试生产代码的SQL查询。但是令我惊讶的是，有些查询需要更长的时间来执行以实现相同的输出。我进行了研究，发现了一些有关如何调试SQL查询的有趣信息。我有一个非常简单的表，其定义如下。在测试环境中，此表中填充了超过1000K的行。

+-----------------------+--------------+------+-----+----------------+
| Field                 | Type         | Null | Key | Extra          |
+-----------------------+--------------+------+-----+----------------+
| id                    | bigint(20)   | NO   | PRI | auto_increment |
| dateCreated           | datetime     | NO   |     |                |
| dateModified          | datetime     | NO   |     |                |
| phoneNumber           | varchar(255) | YES  | MUL |                |
| version               | bigint(20)   | NO   |     |                |
| oldPhoneNumber        | varchar(255) | YES  |     |                |
+-----------------------+--------------+------+-----+----------------+

我执行了一个非常简单的查询，以查找包含5107357058作为phoneNumber的元组。花费了将近4秒钟来获取结果。

select * from Device where phoneNumber = 5107357058;
takes 4 sec.

这个简单的查询应该花费几毫秒的时间。我注意到phoneNumber数据类型为varchar，但在查询中以数字形式提供。当我修改查询以匹配数据类型时，花费了几毫秒的时间。

select * from Device where phoneNumber = '5107357058';
takes almost no time.

谷歌搜索并阅读关于stackoverflow的文章后，我发现EXPLAIN SQL clouse可帮助调试查询。 EXPLAIN语句提供有关SELECT语句的执行计划的信息。当我使用它来获取有关两个查询的信息时，得到了以下结果。

mysql> EXPLAIN select * from Device where phoneNumber = 5107357058;
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys                         | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | Device    | ALL  | phoneNumber,idx_Device_phoneNumber    | NULL | NULL    | NULL | 6482116 | Using where |
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from Device where phoneNumber = '5107357058';
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys                         | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      |   Device  | ref  | phoneNumber,idx_Device_phoneNumber    | phoneNumber | 258     | const |    2 | Using where |
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN为您提供不同的查询属性。在分析查询时，应注意以下属性。

possible_keys :显示适用于查询的索引
key :用来查找记录的键。 NULL值表示没有用于线性查询和SQL搜索的键，最终需要很长时间。
rows :结果行数较少的SQL查询非常有效。人们应该始终尝试改进查询，并避免使用通用查询clouse。在大量记录上执行时，查询性能非常明显。
type :是“联接类型”。 Ref表示从表中读取所有具有匹配索引值的行； All表示全表扫描。

EXPLAIN的两个输出清楚地表明了细微的差异。稍后的查询使用正确的数据类型的string ，将结果phoneNumber作为键并仅检查两行。前者在查询中使用整数，而后者是不同的数据类型，因此SQL会将整数转换为字符串值，并与该表中存在的每个记录进行比较。结果为键为NULL ，行输出为6482116。您还可以看到，后一个查询类型的值为ref而前一个查询类型的值为All ，这清楚地表明前一个查询是错误的查询。

参考：来自Code4Reference博客上的JCG合作伙伴 Rakesh Cusat的调试SQL查询。