MySQL数据库的8种常见误用介绍--陈奇网络工作室

本文的内容

显示

1.极限陈述

2.隐式转换

3.关联更新和删除

4.混合排序

5.EXISTS语句

6.条件下推

7.提前缩小范围

8.下推中间结果集

摘要

10.

推荐阅读

如今，越来越多的客户在MySQL数据库上构建他们的应用，甚至从Oracle迁移到MySQL。但是有些客户在使用MySQL数据库时会遇到一些情况，比如响应时间慢，CPU满。阿里云RDS专家服务团队已经帮助云上的客户解决了很多急需解决的问题。《ApsaraDB专家诊断报告》中一些常见的SQL问题总结如下，供大家参考。

MySQL数据库的八种常见错误用法：

1.极限陈述

分页查询是最常用的场景之一，但通常也是最容易出现问题的。例如，对于下面的简单语句，一般DBA会考虑向type、name和create _ time字段添加一个复合索引。这样，条件排序可以有效地使用索引，性能得到快速提高。

选择*

from操作

其中type=\'SQLStats\ '

ANDname=\'SlowLog\ '

ORDERBYcreate_time

极限1000，10；

嗯，可能90%以上的DBA都会止步于此。但是当LIMIT子句变成“LIMIT 1000000，10”的时候，程序员还是会抱怨：为什么我只取10条记录还是很慢？

要知道，数据库不知道第1000000条记录从哪里开始，就算有索引也需要从头开始计算。在大多数情况下，当这种性能问题出现时，程序员是懒惰的。在前端数据浏览和翻页，或者大数据批量导出的场景下，可以将上一页的最大值作为参数作为查询条件。SQL重新设计如下：

选择*

from操作

其中type=\'SQLStats\ '

ANDname=\'SlowLog\ '

and create _ time \ ' 2017-03-16 14:00:00 \ '

ORDERBYcreate _ timelimit10

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

2.隐式转换

SQL语句中查询变量和字段定义类型不匹配是另一个常见错误。例如，下面的语句：

mysql解释扩展选择*

从我的_余额b

其中b.bpn=14000000123

并且b.isverified为空；

mysql显示警告；

|警告|1739|无法使用ref accessonindex \ ' bpn \ '因为类型或集合转换在字段上进行\ '

其中，字段bpn的定义是varchar(20)，MySQL的策略是将字符串转换成数字，然后进行比较。函数作用于表字段，索引无效。

以上情况可能是应用框架自动填充的参数，并非程序员的本意。目前有很多复杂的应用框架，要小心它在方便使用的同时可能给自己挖坑。

3.关联更新和删除

MySQL5.6虽然引入了物化，但需要特别注意的是，目前只是针对查询语句的优化。对于更新或删除，您需要手动将其重写为JOIN。

比如下面的UPDATE语句，MySQL实际执行的是循环/嵌套子查询，其执行时间可想而知。

更新操作o

SETstatus=\ '正在应用\ '

其中标识号(选择号

来自(SELECTo.id，

o .状态

从操作o

其中，组=123

ANDo.statusNOTIN(\'done\ ')

ORDERBYo.parent，

外径

极限1)t)；

实施计划：

- - - - - - - - - -

- - - - - - - - - -

| 1 | PRIMARY | o | index | PRIMARY | 8 | | 24 | Using where；使用临时|

| 2 |从属子查询| || || ||在读取常量表后发现不可能|

|3| DERIVED |o| ref |idx_2，idx _ 5 | idx _ 5 | 8 | const | 1 | Using where；使用文件排序|

- - - - - - - - - -

重写为JOIN后，子查询的选择方式从依赖子查询变为派生，执行速度大大加快，从7秒提高到2毫秒。

更新操作o

加入(SELECTo.id，

哦。状态

从操作o

其中，组=123

ANDo.statusNOTIN(\'done\ ')

ORDERBYo.parent，

外径

极限1) t

ONo.id=t.id

SETstatus=\ '正在应用\ '

执行计划简化为：

- - - - - - - - - -

- - - - - - - - - -

|1|主|| || ||在读取常量表后发现不可能|

| 2 |DERIVED| o |ref| idx_2，idx _ 5 | idx _ 5 | 8 | const | 1 | Using where；使用文件排序|

- - - - - - - - - -

4.混合排序

关系型数据库不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。

选择*

从我的订单o

INNERJOINmy _ evaluate aona。订单id=o . id

ORDERBYa.is_replyASC，

答。评价_时间描述

限制0,20

执行计划显示为全表扫描：

- - - - - - - - - -

- - - - - - - - - -

|1|简单| o | eq _ ref | PRIMARY | PRIMARY | 122 | a . orderid | 1 | NULL |

- - - - - - - - - -

由于是_回复只有0和一两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。

选择？*?

从哪里？((选择？*

？？从哪里？我的订单？

？？内心？加入？我的_评价答？

？？？开？a.orderid=o.id？

？？？？然后呢. is_reply=？0?

？？订单？被谁？估_时间？DESC？

？？极限？0,20)?

？工会？全部？

？（选择？*

？？从哪里？我的订单？

？？内心？加入？我的_评价答？

？？？开？a.orderid=o.id？

？？？？然后呢. is_reply=？1?

？？订单？被谁？估_时间？DESC？

？？极限？0,20)) t？

订单？被谁？是_回复？ASC，

？？考核时间？DESC？

极限？20;

5.存在语句

关系型数据库对待存在子句时，仍然采用嵌套子查询的执行方式。如下面的结构化查询语言语句：

选择*

来自我的邻居

LEFTJOINmy_neighbor_apply sra

ONn.id=sra.neighbor_id

ANDsra.user_id=\'xxx\ '

主题状态四

和现有者（选择一

发件人信息

其中n.id=m.neighbor_id

ANDm.inuser=\'xxx\ ')

ANDn.topic_type 5

执行计划为：

- - - - -

| id |？select_type？|表|?类型？| possible_keys |？钥匙？| key_len |？裁判？|行|?额外？|

- - - - - - - - - -

|?1?|主|?n？|全部|?| NULL |？NULL？| NULL |？1086041?|使用位置|

| 1 |?初级？| sra |？裁判？| |?idx_user_id？| 123 |?const？| 1 |?用在哪里？|

|?2?|从属子查询|?m？| ref |？| idx_message_info |？122?| const |？1?|使用索引条件；使用哪里|

- - - - - - - - - -

去掉存在更改为加入，能够避免嵌套子查询，将执行时间从1.93秒降低为一毫秒。

选择*

来自我的邻居

INNERJOINmessage_info m

ONn.id=m.neighbor_id

ANDm.inuser=\'xxx\ '

LEFTJOINmy_neighbor_apply sra

ONn.id=sra.neighbor_id

ANDsra.user_id=\'xxx\ '

主题状态四

ANDn.topic_type 5

新的执行计划：

- - - - - - - - - -

---- ------------- ------------ -------- --------------- --------- --------- ------- -------- ----------------------------------------------------

---- ------------- ------------ -------- --------------- --------- --------- ------- -------- ----------------------------------------------------

| 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\'

ORDERBYsalecodelimit20) a

LEFTJOIN

(

SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated

FROMmy_resources

GROUPBYresourcesid) c

ONa.resourceid=c.resourcesid

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

其实对于子查询c，左连接最后结果集只关心能和主表resourceid能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。

SELECTa.*,

c.allocated

FROM(

SELECTresourceid

FROMmy_distribute d

WHEREisdelete=0

ANDcusmanagercode=\'1234567\'

ORDERBYsalecodelimit20) a

LEFTJOIN

(

SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated

FROMmy_resources r,

(

SELECTresourceid

FROMmy_distribute d

WHEREisdelete=0

ANDcusmanagercode=\'1234567\'

ORDERBYsalecodelimit20) a

WHEREr.resourcesid=a.resourcesid

GROUPBYresourcesid) c

ONa.resourceid=c.resourcesid

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

WITH a AS

(

SELECTresourceid

FROMmy_distribute d

WHEREisdelete=0

ANDcusmanagercode=\'1234567\'

ORDERBYsalecodelimit20)

SELECTa.*,

c.allocated

FROMa

LEFTJOIN

(

SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated

FROMmy_resources r,

WHEREr.resourcesid=a.resourcesid

GROUPBYresourcesid) c

ONa.resourceid=c.resourcesid

总结

数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。

程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。编写复杂SQL语句要养成使用WITH语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担^^。

更多关于云服务器，域名注册，虚拟主机的问题，请访问西部数码代理商官网：www.chenqinet.cn

相关推荐