之前已经讲过mysql的性能优化&＃xff0c;也介绍了一些面试过程中经常被问到的一些问题。想了解的请看我之前的文章&＃xff1a;《能避开很多坑的mysql面试题&＃xff0c;你知道吗》。其实不只是在数据库设计的过程中容易犯一些低级的错误&＃xff0c;日常开发过程中会经常忽略一些Sql的写法&＃xff0c;从而导致系统性能低等一系列问题。今天就来总结哪些经常被我们忽视的SQL错误写法&＃xff0c;看看你都踩过哪些坑&＃xff1f;

Limit是分页查询是最常用的场景之一&＃xff0c;但也通常也是最容易出问题的地方。比如对于下面简单的语句&＃xff0c;一般我们觉得在type, name, create_time字段上加组合索引。这样条件排序都能有效的利用到索引&＃xff0c;性能迅速提升。

SELECT * FROM operation WHERE type &＃61; &＃39;xxx&＃39; AND name &＃61; &＃39;xxx&＃39; ORDER BY create_time LIMIT 1000, 10;

但是当数据量很大的时候&＃xff0c;当我们查询最后几页数据时&＃xff0c;分页会越来越慢。这就是我们经常碰到的海量数据的分页问题。这是为什么呢&＃xff1f;

优化方案

因为数据库也并不知道第1000000条记录从什么地方开始&＃xff0c;即使有索引也需要从头计算一次&＃xff0c;即进行全表扫描。出现这种性能问题&＃xff0c;主要还是我们没有考虑到大量数据的情况。

其实在前端数据浏览翻页时&＃xff0c;是可以将上一页的最大值作为查询条件传给后台的。SQL 重新设计如下&＃xff1a;

select *from operationwhere id>1000000AND type &＃61; &＃39;xxx&＃39;AND name &＃61; &＃39;xxx&＃39;ORDER BY create_timelimit 10

经过这种优化&＃xff0c;可以保证系统不会随着数据量的增长而变慢。

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句&＃xff1a;

explain extendedselect * from my_balance bwhere b.bpn &＃61; 14000000123and b.isverified is null;

字段 bpn 的定义为 varchar 类型&＃xff0c;而查询条件传入的却是int 类型。MySQL 会将字符串转换为数字之后再比较。函数作用于表字段&＃xff0c;导致所以索引失效。如下图所示&＃xff1a;

这个坑我们以前也遇见过&＃xff0c;花了好半天才发现是这个问题。 所以程序员在开发的过程中&＃xff0c;一定要认真仔细&＃xff0c;确保查询变量和字段类型匹配。

优化方案

保证传入的参数类型和字段定义的类型一致。

所以&＃xff0c;上面的sql语句改为如下即可&＃xff1a;

explain extendedselect * from my_balance bwhere b.bpn &＃61; &＃39;14000000123&＃39;and b.isverified is null;

MySQL会自动把SQL语句中的嵌套子查询优化为关联查询(join)&＃xff0c;所以有些时候你会发现嵌套子查询的效率和关联查询的效率差不多。但是需要特别注意mysql目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。

比如下面 UPDATE 语句&＃xff0c;MySQL 实际执行的还是嵌套子查询(DEPENDENT SUBQUERY)&＃xff0c;其执行时间可想而知。

explain extendedUPDATE operation oSET status &＃61; &＃39;applying&＃39;WHERE o.id IN (SELECT id FROM (SELECT o.id,o.status FROM operation o WHERE o.group &＃61; 123 AND o.status NOT IN (&＃39;done&＃39;) ORDER BY o.parent,o.id LIMIT 1) t);

执行计划&＃xff1a;

优化方案

将嵌套子查询改为 JOIN 之后&＃xff0c;子查询的选择模式从嵌套子查询(DEPENDENT SUBQUERY) 变成了关联查询(DERIVED)&＃xff0c;执行速度大大加快

UPDATE operation o JOIN (SELECT o.id, o.status FROM operation o WHERE o.group &＃61; 123 AND o.status NOT IN (&＃39;done&＃39;) ORDER BY o.parent,o.id LIMIT 1) t ON o.id &＃61; t.id SET status &＃61; &＃39;applying1

执行计划简化为&＃xff1a;

MySQL中的两种排序方式&＃xff1a;

1、通过有序索引顺序扫描直接返回有序数据,因为索引的结构是B&＃43;树&＃xff0c;索引中的数据是按照一定顺序进行排列的&＃xff0c;所以在排序查询中如果能利用索引&＃xff0c;就能避免额外的排序操作。EXPLAIN分析查询时&＃xff0c;Extra显示为Using index。

2、Filesort排序&＃xff0c;对返回的数据进行排序,所有不是通过索引直接返回排序结果的操作都是Filesort排序&＃xff0c;也就是说进行了额外的排序操作。EXPLAIN分析查询时&＃xff0c;Extra显示为Using filesort。

优化方案

一般排序的原则就是&＃xff1a;尽量减少额外的排序&＃xff0c;通过索引直接返回有序数据。

所以我们需要注意以下这些情况&＃xff1a;

1、排序字段在多个索引中&＃xff0c;无法使用索引排序&＃xff0c;查询一次只能使用一个索引&＃xff1a;

explain select store_id,email,last_name from my_user order by store_id,email,last_name;

查询计划显示&＃xff0c;没有走所以直接返回有序数据&＃xff0c;额外排序放回结果&＃xff1a;

2、排序字段顺序与索引列顺序不一致&＃xff0c;同样也无法利用索引排序。这个就不举例了跟where条件是一样的。

需要注意的是&＃xff1a;这些都是细节的东西&＃xff0c;经常会在开发过程中忽略。然后SQL就莫名其妙的不走索引了。

索引中的数据是按照一定顺序进行排列的&＃xff0c;所以在排序查询中如果能利用索引直接返回数据&＃xff0c;就能避免额外的排序操作。但是如果出现这种混合了升序和降序的情况&＃xff0c;MySQL 无法利用索引直接返回排序结果的。

SELECT *FROM my_order o INNER JOIN my_appraise a ON a.orderid &＃61; o.id ORDER BY a.is_reply ASC, a.appraise_time DESC LIMIT 0, 20

执行计划显示为全表扫描&＃xff1a;

优化方案

此类问题根据实际常见优化&＃xff0c;原则就是应该避免这种排序的情况。如果确实有多种排序的需求&＃xff0c;最好使用其他的方法提升性能。

有些人会容易忽视where 条件的顺序问题&＃xff0c;如果where 条件的顺序不对&＃xff0c;很有可能会导致索引失效&＃xff0c;查询性能慢等问题。以下两点是需要特别注意的&＃xff1a;

1、排除数据越多的条件越靠前&＃xff0c;where 条件从左往右执行的&＃xff0c;在数据量小的时候不用考虑&＃xff0c;但数据量大的时候必须要考虑条件的先后顺序。

2、最左前缀原则&＃xff0c;如果查询的时候查询条件精确匹配索引的左边连续一列或几列&＃xff0c;则此列就可以被用到。如下&＃xff1a;

select * from user where name&＃61;xx and city&＃61;xx ; &＃xff0f;&＃xff0f;可以命中索引select * from user where name&＃61;xx ; // 可以命中索引select * from user where city&＃61;xx ; // 无法命中索引

这里需要注意的是&＃xff0c;查询的时候如果两个条件都用上了&＃xff0c;但是顺序不同&＃xff0c;如 city&＃61; xx and name &＃xff1d;xx&＃xff0c;那么现在的查询引擎会自动优化为匹配联合索引的顺序&＃xff0c;这样是能够命中索引的。

由于最左前缀原则&＃xff0c;在创建联合索引时&＃xff0c;索引字段的顺序需要考虑字段值去重之后的个数&＃xff0c;较多的放前面。ORDER BY子句也遵循此规则。

MySQL 对待 EXISTS 子句时&＃xff0c;会采用嵌套子查询的执行方式。如下面的 SQL 语句&＃xff1a;

explain SELECT *FROM my_order n LEFT JOIN my_appraise sra ON n.id &＃61; sra.orderid WHERE 1&＃61;1 AND EXISTS(SELECT 1 FROM my_user m WHERE n.user_id &＃61; m.id AND m.usercode &＃61; &＃39;111&＃39; ) AND n.id 5

执行计划为&＃xff1a;

优化方案

去掉 exists 更改为 join&＃xff0c;能够避免嵌套子查询&＃xff0c;这样会大大提高查询效率。

explain SELECT *FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id &＃61; sra.neighbored AND sra.user_id &＃61; &＃39;xxx&＃39; INNER JOIN message_info m on n.id &＃61; m.neighbor_id AND m.inuser &＃61; &＃39;xxx&＃39;WHERE n.topic_status 5

新的执行计划显示没有了嵌套子查询&＃xff1a;

外部查询条件不能够下推到复杂的视图或子查询的情况有&＃xff1a;

如下面的语句&＃xff0c;从执行计划可以看出其条件作用于聚合子查询之后

explain SELECT *FROM(SELECT target, Count(*) FROM operation GROUPBY target) tWHERE target &＃61; &＃39;rm-xxxx&＃39;

优化方案

确定从语义上查询条件可以直接下推后&＃xff0c;重写如下&＃xff1a;

explain SELECT target, Count(*)FROM operationWHERE target &＃61; &＃39;rm-xxxx&＃39;GROUPBY target

执行计划变为&＃xff1a;

先上初始 SQL 语句&＃xff1a;

SELECT *FROM my_order o LEFT JOIN my_userinfo u ON o.uid &＃61; u.uid LEFT JOIN my_productinfo p ON o.pid &＃61; p.pid WHERE o.display &＃61; 0 AND o.ostaus &＃61; 1ORDER BY o.selltime DESC LIMIT 0, 15

通过查询计划可以看出90万&＃xff0c;时间消耗为12秒。

优化方案

由于最后 WHERE 条件以及排序均针对最左主表&＃xff0c;因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下&＃xff0c;执行时间缩小为1毫秒左右。

SELECT *FROM (SELECT * FROM my_order o WHERE o.display &＃61; 0 AND o.ostaus &＃61; 1 ORDER BY o.selltime DESC LIMIT 0, 15 )oLEFT JOIN my_userinfo u ON o.uid &＃61; u.uid LEFT JOIN my_productinfo p ON o.pid &＃61; p.pid ORDER BY o.selltime DESC limit 0, 15

再检查执行计划&＃xff1a;

子查询物化后(select_type&＃61;DERIVED)参与 JOIN&＃xff0c;虽然估算行扫描仍然为4万&＃xff0c;但是利用了索引以及 LIMIT 子句后&＃xff0c;实际执行时间变得很小。

看下面这个sql语句&＃xff0c;&＃xff1a;

SELECT a.* c.allocatedFROM FROM my_distribute d LEFT JOIN ( SELECT resourcesid, sum(allocation) allocated FROM my_resources GROUP BY resourcesid ) cON a.resourceid &＃61; c.resourcesid

那么该语句存在什么问题呢&＃xff1f;不难看出子查询 c 是全表聚合查询&＃xff0c;在表数量特别大的情况下会导致整个语句的性能下降。

其实对于子查询 c&＃xff0c;左连接最后结果集只关心能和主表 resourceid 能匹配的数据。因此我们可以重写语句如下&＃xff0c;执行时间大大降低 。

SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete &＃61; 0 AND cusmanagercode &＃61; &＃39;1234567&＃39; ORDER BY salecode limit 20) aLEFT JOIN ( SELECT resourcesid, sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, ( SELECT resourceid FROM my_distribute d WHERE isdelete &＃61; 0 AND cusmanagercode &＃61; &＃39;1234567&＃39; ORDER BY salecode limit 20 ) a WHERE r.resourcesid &＃61; a.resourcesid GROUP BY resourcesid ) cON a.resourceid &＃61; c.resourcesid

其实&＃xff0c;这段sql我们还可以优化&＃xff0c;可以将 my_distribute的查询结果作为临时表&＃xff0c;这样能避免大量重复的代码。

以上总结了一些sql语句常见的坑。里面很多都是不仔细导致的。只有仔细认真尽量考虑一些大数据的情况&＃xff0c;这样才能写出高性能的SQL语句。

同时&＃xff0c;程序员在设计数据模型以及编写SQL语句时&＃xff0c;要把索引及性能提前考虑进去&＃xff0c;这样才能避免后期遇到的一些坑。