mysql产生的错误服务咋解决，mysql查询基础知识常见错误

本内容由系统网小编为大家分享，Windows系统安装教程、办公系统、软件怎么使用、软件使用教程、办公软件攻略等信息。

MySQL在2016年保持了一个强劲的增长数据库流行趋势。越来越多的客户在MySQL数据库上建立应用程序,它甚至从Oracle转移到MySQL。还有一些客户在使用MySQL数据库时遇到一些问题,例如慢响应时间,CPU打满等情况。阿里云RDS专家服务团队帮助云客户解决许多紧急问题。下面总结了apsaraDB专家诊断报告中出现的一些常见SQL问题:供大家参考。

常见SQL错误用法

1. LIMIT 句子

页面查询是最常见的场景之一,但它也常常是最容易陷入麻烦的地方。例如,下列简单的句子,DBA通常想将组合索引添加到类型、名称、 create_time字段中。这样条件序列可以有效地用于索引,性能迅速提升。

SELECT* FROMoperation WHERE type='SQLStats' AND name='SlowLog' ORDER BYcreate_time LIMIT 1000,10;

好吧，可能90%以上的DBA解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？

知道数据库不知道第一百万条的记录从何处开始,即使有指数, 它必须首先计算.出现这种性能问题，在大多数情况下,程序员是懒惰的。浏览前面的数据,或者大数据批量输出等等,可以使用前面的页面的最大值作为参数作为查询条件。SQL被重新设计如下:

SELECT* FROMoperation WHERE type='SQLStats' AND name='SlowLog' ANDcreate_time >'2017-03-16 14:00:00' ORDER BYcreate_timelimit 10;

在新的设计下,查询时间基本上是固定的,随数据量增加而不会改变。

2. 隐式转换

另一个常见的错误是SQL文中查询变量和字段定义类型不匹配。

mysql> explain extendedSELECT*>FROMmy_balance b>WHEREb.bpn =14000000123 >ANDb.isverifiedISNULL ;mysql> show warnings;| Warning |1739| Cannot use ref accessonindex'bpn' due to type or collation conversion on field 'bpn'

其中,字段 bpn 定义为 varchar(20), MySQL 的策略是将字符串转换成数字并再比较它们。

上面的可能是应用程序框架自动填充的参数,而不是程序员原来的想法。 现在应用程序框架非常繁琐,使用方便,而且要小心它可能自己挖出一个洞。

3.相关更新和删除

虽然MySQL5.6引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。

例如,在下面的更新声明中,MySQL实际上执行了一个循环/嵌入子查询(DEPENDENT SUBQUERY),其执行时间已知。

UPDATEoperation o SET status='applying' WHEREo.idIN(SELECT id FROM(SELECTo.id,o.status FROMoperation o WHEREo.group =123 ANDo.statusNOT IN('done') ORDER BYo.parent,o.id LIMIT 1) t);

执行计划：

+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+ | id |select_type| table |type| possible_keys |key| key_len |ref| rows |Extra|+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+| 1 | PRIMARY            |o| index | | PRIMARY | 8 |       | 24 | Using where; Using temporary                        | | 2  |DEPENDENT SUBQUERY|       | |               | |         | |      |Impossible WHERE noticed after reading const tables|| 3 | DERIVED            |o| ref   |idx_2,idx_5| idx_5   | 8 | const | 1 | Using where; Using filesort                         | +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+

在重新编写为“Join”后,子查询的选择模式从“DEPENDENT SUBQUERY”改为“ DEIVED”,执行速度从7秒提高到2毫秒。

UPDATEoperation o JOIN(SELECTo.id,o.status FROMoperation o WHEREo.group =123 ANDo.statusNOT IN('done') ORDER BYo.parent,o.id LIMIT 1) t ONo.id = t.id SET status='applying'

执行计划简化为：

+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+ | id |select_type| table |type| possible_keys |key| key_len |ref| rows |Extra|+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+| 1 | PRIMARY     | |      | |       | |       | | Impossible WHERE noticed after reading const tables | | 2  |DERIVED| o     |ref| idx_2,idx_5   |idx_5| 8       |const| 1    |Using where; Using filesort|+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+

4. 混合排序

MySQL无法使用索引进行混合分类,但在某些情况下,仍然有使用特殊方法来提高性能的机会。

SELECT* FROMmy_order o INNER JOINmy_appraise aONa.orderid = o.id ORDER BYa.is_replyASC,a.appraise_timeDESC LIMIT 0,20

执行计划用于全屏幕扫描:

+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+ | id |select_type| table |type| possible_keys     |key| key_len |ref| rows    |Extra+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+ |  1 |SIMPLE| a     |ALL| idx_orderid |NULL| NULL    |NULL| 1967647 |Using filesort|| 1 | SIMPLE      |o| eq_ref |PRIMARY| PRIMARY | 122 | a.orderid | 1 | NULL           | +----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+

由于 is_reply只有两个状态,即0和1,我们用下面的方式重写,执行时间从1.58秒到2毫秒。

SELECT* FROM((SELECT* FROMmy_order o INNER JOINmy_appraise a ONa.orderid = o.id ANDis_reply =0 ORDER BYappraise_timeDESC LIMIT 0,20) UNIONALL(SELECT* FROMmy_order o INNER JOINmy_appraise a ONa.orderid = o.id ANDis_reply =1 ORDER BYappraise_timeDESC LIMIT 0,20)) t ORDER BYis_replyASC,appraisetimeDESC LIMIT 20;

5.有句子

MySQL在处理EXISTS子句子时仍然使用嵌入式查询执行方法,例如以下的SQL声明:

SELECT* FROMmy_neighbor n LEFT JOINmy_neighbor_apply sra ONn.id = sra.neighbor_id ANDsra.user_id ='xxx' WHEREn.topic_status <4 AND EXISTS(SELECT 1 FROMmessage_info m WHEREn.id = m.neighbor_id ANDm.inuser ='xxx') ANDn.topic_type <>5

执行计划为：

+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+ | id |select_type| table |type| possible_keys     |key| key_len |ref| rows    |Extra|+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+| 1 | PRIMARY            |n| ALL  | | NULL     |NULL| NULL  | 1086041 | Using where                   | |  1 |PRIMARY| sra   |ref|  |idx_user_id| 123     |const|       1 |Using where|| 2 | DEPENDENT SUBQUERY |m| ref  | | idx_message_info   | 122 | const | 1 | Using index condition; Using where | +----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+

删除加入现有更改,避免嵌入式查询,并执行从1.93秒减少到1毫秒的时间。

SELECT* FROMmy_neighbor n INNER JOINmessage_info m ONn.id = m.neighbor_id ANDm.inuser ='xxx' LEFT JOINmy_neighbor_apply sra ONn.id = sra.neighbor_id ANDsra.user_id ='xxx' WHEREn.topic_status <4 ANDn.topic_type <>5

新的执行计划：

+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+ | id |select_type| table |type| possible_keys     |key| key_len |ref| rows |Extra|+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| 1 | SIMPLE      |m| ref    | | idx_message_info   | 122 | const    | 1 | Using index condition | |  1 |SIMPLE| n     |eq_ref| |PRIMARY| 122     |ighbor_id|    1 |Using where|| 1 | SIMPLE      |sra| ref    | | idx_user_id | 123 | const     | 1 | Using where           | +----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+

6. 条件下推

无法推到复杂视图或子查询的外部查询条件包括:

聚合子查询；

包含LIMIT的子问题;

联合国或联合国所有子问题;

输出领域中的子问题;

例如,从执行计划中可以看出,它的条件在多项式查询后运行:

SELECT* FROM(SELECTtarget, Count(*) FROMoperation GROUP BYtarget) t WHEREtarget ='rm-xxxx' +----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+ |id| select_type |table|type| possible_keys |key| key_len |ref|rows| Extra       |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+ |1| PRIMARY     | <derived2> |ref| <auto_key0>   | <auto_key0> |514| const |2|Using where||2| DERIVED     | operation  |index| idx_4         | idx_4       |519|NULL|20|Using index|+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+

为了确定查询条件是否可以直接从语义中推导,重新写如下:

SELECTtarget, Count(*) FROMoperation WHEREtarget ='rm-xxxx' GROUP BYtarget

执行计划变为：

+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+ | id |select_type| table |type| possible_keys |key| key_len |ref| rows |Extra|+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+| 1 | SIMPLE |operation| ref |idx_4| idx_4 | 514 | const | 1 | Using where; Using index | +----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

关于MySQL外部条件不能下推的详细解释说明请参考以前文章：MySQL · 性能优化 · 条件下推到物化表

7. 提前缩小范围

首先,SQL声明:

SELECT* FROMmy_order o LEFT JOINmy_userinfo u ONo.uid = u.uid LEFT JOINmy_productinfo p ONo.pid = p.pid WHERE( o.display =0) AND( o.ostaus =1) ORDER BYo.selltimeDESC LIMIT 0,15

SQL声明最初的意思是:首先创建一系列左键连接,然后序列前15个记录。 如从执行计划中可以看出,最后一步估计序列记录的数目为90,00,时间消耗为12秒。

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+ | id |select_type| table |type| possible_keys |key| key_len |ref| rows   |Extra|+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+| 1 | SIMPLE      |o| ALL    |NULL| NULL    |NULL| NULL            | 909119 | Using where; Using temporary; Using filesort       | |  1 |SIMPLE| u     |eq_ref| PRIMARY       |PRIMARY| 4       |o.uid|      1 |NULL|| 1 | SIMPLE      |p| ALL    |PRIMARY| NULL    |NULL| NULL            | 6 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

由于最后的WHERE条件和序列是针对最左的主表,可以在连接到左之前最小化my_order序列。 SQL重写后,执行时间减少到大约1毫秒。

SELECT* FROM( SELECT* FROMmy_order o WHERE( o.display =0) AND( o.ostaus =1) ORDER BYo.selltimeDESC LIMIT 0,15 ) o LEFT JOINmy_userinfo u ONo.uid = u.uid LEFT JOINmy_productinfo p ONo.pid = p.pid ORDER BYo.selltimeDESC limit 0,15

再次检查执行计划:在子查询实物化后参加JOIN(select_type=DERIVED)。虽然估计的线扫描时间仍然为90,00,但使用索引和LIMIT分项后实际执行时间变得较小。

+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+ | id |select_type| table      |type| possible_keys |key| key_len |ref| rows   |Extra|+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+| 1 | PRIMARY     |<derived2>| ALL    |NULL| NULL    |NULL| NULL  | 15 | Using temporary; Using filesort                    | |  1 |PRIMARY| u          |eq_ref| PRIMARY       |PRIMARY| 4       |o.uid|      1 |NULL|| 1 | PRIMARY     |p| ALL    |PRIMARY| NULL    |NULL| NULL  | 6 | Using where; Using join buffer (Block Nested Loop) | |  2 |DERIVED| o          |index| NULL          |idx_1| 5       |NULL| 909112 |Using where|+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+

8. 中等 结果 除去

让我们再看一下最初优化的下面的例子(左连接中的主表优先查询条件):

SELECTa.*,c.allocated FROM( SELECTresourceid FROMmy_distribute d WHEREisdelete =0 ANDcusmanagercode ='1234567' ORDER BYsalecodelimit 20) a LEFT JOIN ( SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated FROMmy_resources GROUP BYresourcesid) c ONa.resourceid = c.resourcesid

那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。

事实上,对于子问题c,左连结的最终结果集只关心可以匹配主资源ID的数据。 因此,我们可以如下重写句子,执行时间从原来的2秒下降到2毫秒。

SELECTa.*,c.allocated FROM( SELECTresourceid FROMmy_distribute d WHEREisdelete =0 ANDcusmanagercode ='1234567' ORDER BYsalecodelimit 20) a LEFT JOIN ( SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated FROMmy_resources r,( SELECTresourceid FROMmy_distribute d WHEREisdelete =0 ANDcusmanagercode ='1234567' ORDER BYsalecodelimit 20) a WHEREr.resourcesid = a.resourcesid GROUP BYresourcesid) c ONa.resourceid = c.resourcesid

但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用WITH语句再次重写：

WITH a AS( SELECTresourceid FROMmy_distribute d WHEREisdelete =0 ANDcusmanagercode ='1234567' ORDER BYsalecodelimit 20) SELECTa.*,c.allocated FROMa LEFT JOIN ( SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated FROMmy_resources r,a WHEREr.resourcesid = a.resourcesid GROUP BYresourcesid) c ONa.resourceid = c.resourcesid

AliSQL即将推出语法, 请期待.

总结

数据库编译器生成执行计划,确定SQL的实际执行方法。但是编译器只是想提供服务,并非所有的数据库编译器都是漂亮的。上述的大部分情形,其他数据库也有性能问题。了解数据库编译器的特点,才能避规其短处，编写高性能SQL声明。

在设计数据模型和编译SQL文时,程序员将算法的概念或意识引入。

编译复杂的SQL文档需要使用 WITH文档的习惯。 一个简单而有思想的SQL文档也可以减少数据库的负担。

解决云数据库的问题(不局限于SQL问题),并一直寻求阿里云工厂专家的帮助。

XTw.com.Cn系统网专业应用软件下载教程，免费windows10系统,win11,办公软件,OA办公系统,OA软件,办公自动化软件,开源系统,移动办公软件等信息，解决一体化的办公方案。