之前已经讲过mysql的性能优化，也介绍了一些面试过程中经常被问到的一些问题。想了解的请看我之前的文章：《能避开很多坑的mysql面试题，你知道吗》。其实不只是在数据库设计的过程中容易犯一些低级的错误，日常开发过g / I程中会经常忽略一些Sql的写法，从而导致系统性能低等一系列问题。今天就来总结哪些经常被我们忽视的SQL错误写法，看看你都踩过哪些坑？
一、LIMIT语句
Limit是分页查询是最常用的场景之一，但也通常也是最容易出问题的地方j 4 。比如对于下面简单的语句，一般我们觉得在type, name, c. A h ` xreate_time字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
SELECT *
FROM operation
WHE5 b Y !RE type = \'xxx\'
AND name = \'xxx} b v 0 G b\'
ORDER BY create_time
LIMIT 1000, 10;但是当数据量很大的时候，当我们查询最后几页数据时，分页会越来越慢。这就是我们经常碰到的海量数据的分页问题。这是为什么呢？
优化方案
因为数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次，即进行全表扫描。出现这种性能问题，主要还是我们没有考虑到大量数据的情况。
其C y E [ ( B实在前端数据浏览翻页时，是可以将上一页的最大值作为查询条件传给后台的。W I v ] @ x T ~ gSQL 重新设计如下：
select *
from operation
where id>1000000
AND type = \'xxx\'
AND nax T G u K p e N Ime = \'xxx\'
ORDER BY crea@ Q F 1 w tte_time
limit 10经过这种优化，可以保证系统不会随t V & $ Q =着数据量的增长而变慢。
二、隐式转换
SQLf N P语句中查询变量和字段定义类型不匹配z R e Z % / - Q 7是另一个常见的错误。比Y ; N % 9 R ~如下面的语句：
explain extended
select^ B $ r *
from my_balance b
where b.bpn = 14000000123
and b.isverified is null;字段 bpn 的定义为 varchar 类型，而查询条件传入的却是int 类型。MySm _ & , # r # n ?QL 会将字符串转换为数字之后再比较。V N o C j D函数作用于表字段，导致所以索引失效。如下图所示：
这个坑E m D n ( $ 2我们以前也遇见过，花了好半天才发现是这个问题。 所以程序员在开发的过程中，一定要认真仔细，确保查询变量和字段类型匹配。
优化方案
保证传入的参数类型和字段定义的类型一致。
所以，上面的sql语句改为如下即可：
explain extended
select *
ft # erom my_balance b
where b.bpn = \'14000000123\'
and b.isverified is null;三、关联更新、删除
MySQL会自动把SQL语句中的嵌, 5 N套子查询优化为关联a _ ^ %查询（join），所以有些时候你会发现嵌套子查询的效率和关联查询的效率差不多。但是需要特别注意mysql目前仅仅针对查询Y . c语句的优化。对于更新或删除需要手工重写成W - x R O Y n JOIN。
比如下面 UPDATE 语句$ l 0 h : | T，MySQL 实际执行的还是嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。
eL V t pxplain extended
UPDATE operation o
SET status = \'applying\'
WHERE o.id IN (SELECT id
FROM (SELEL $ LCT o.id,o.status
FROM operL p 9 M K o ration o
WHERE o.g h V y : h 6group = 123
AND o.K 8 ^ q 6 ustatus NOT I( F ? 0 KN (\'done\')
ORDER BY o.parent,o.id
LI] a W 0 h ; % C aMIT 1) t);执行计划：
优化方案
将嵌套子查询改为 JOIN 之后，子查询的选择模式从嵌套子查询(DEPENDENT SUBQUERY) 变成了关联查询(DERIVED)，执行速度大大加快
UPDATE operation o
JOIN (SELECT o.id,- t W R L I ^
o.status
FROM operation o
WHERE o.group = 123
AND o.status NOT IN (\'done\? k | = k F g &')
ORDER BY o.parent,o.id
LIMIT 1) t
ON o.id = t.id
SET status = \Z @ w s p F y {'applyinT u og1执行计划: m * K f简化为：
四、Order by排序
MySQL中的两种排序方式：
1、通过有序索引顺序扫描直接返回有e V c # P O e K序数据,因为索引的结构是B+树，索引中的数据是按! K x z z u h ,照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查z + 3 J . D . G询时，E f Q K 2Extn # zra显示为Using index。
2、Filesort排序，对返回的数据进行排序,所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EX6 # Z 4 / Z PPLAIN分析查询时，Extra显示6 _ y n ! ~ q M为Using filesort。
优化方案
一般排序的原则就是：尽量减少额外的排序，通过索引直接返I ? r I 5回有序数据。
所以我们需要注意以下这些情况：
1、排序字段在多个索引中，无法使用索引排序，查询一次只能使用一个索引：
explain
select store_id,emF - f m q Wail,last_name
from my_user
oL a ] @ z 5 M {rder by store_id,email,last_name;查询计划显示，没有走所以直接s ; P s C 返回有Q f U f x t序数据，额外排序放回结果：
2、排序字段顺序与索引列顺序不一致，同样也无法利用索引排序。这 , t n个就不举例了跟where条件是一样的。
需要注意的是：这些都是细节的东西，经常会在开B _ t发过程中忽略。然后SQL就莫名其妙的不走索引了。
五、混合排序
索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引直接返回数据，就能避免额外的排序操作。但是如果出现这种混合了升序和降序的情况，MySQL 无法利用索引直接返回排序结果的5 V ! ] S。
SELECT *
FROM my_order ox b ]
INNER JO@ M Y B F 7IN my_appraise a ON a.orderid = o.id
ORDER BY a.is_reply ASC,
a.appraise_time DJ B n B r RESC
LIMIT 0, 20执行计划显示为全表扫描：
优化方案
此类问题根据实际常见优化，原则就是应该避免这种排序的情况。如果确实有多种排序的需求，最好使用其他的方法提升性能。
六、where 条件的顺序
有些人会容易忽视where 条件的顺序问题，如果where 条件的顺序不对，很有可能G P 4 I i T 8会导致索引失效，查询性能慢等问题。以- L ! V z % F ,下两点是需要特别注意的：
1、排除数据越多的条件越靠前，where 条件从左往右执行的，在数据量小的时候不用N 5 3 a 3 =考虑，但数据量大的y 8 V g Q O E )时候必须要_ c _ . $ . q考虑条件的先后顺序。
2、最左前~ , u k t E 3缀3 Y I @ b原则，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引这里需要注意的是，查询的时候u @ n C o如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索x V 3 6 z引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。
七、EXISTS语句
MySQL 对待 EXISTS 子句时，会采用嵌套子查询的执行方式。如下面的 SQLC S , ( o g 语句：
explainx 2 T
SELm { J + j MECT *
FROM my_order n
LEFT JOIN my_appraise s; @ ( r = E Cra
ON n.id = sra.orderid
WHERE 1=1
A_ P H ? c : I @ND EXISTS(SELECr - 0 (T 1
FROM my_user m
WHERE n.user_id = m.id
AND m.usercode = \'111\' )
AND n.id <> 5执行计划为：
优化方案
去掉 exists 更改为1 ? ; join，能够避免嵌套子查询，这样会大大提高查询效率。
explain
SELECT *
FROM my_neighbor n
LEFT JOIN mB 0 i uy_neighbor_apply sra
ON n.id = sra.neighbored
AND sra.user_id = \'xxx\'
INNER JOI% , Q : F p Q N messQ . N G a . ) 9 Zage_info m
on n.id = m.neighbor_id
AND m.inuser = \'d r * V _xxx\'
WHERE n.topic_status < 4
AND n.topictype <G S G # t 5 B E;> 5新的执行计划显$ R ( R示没有了嵌套子查询：
八{ Z v p ( M、条件下推
外部查询条X ) X B 4 c T Q件不能够下推到复杂的视图或子查询| M 6 O的情况有：V R 3 v .
- 聚合子查询；
- 含有 LIMIT 的子查询；
- UNION 或 UNION ALL 子查询；
- 输出字段中的子查询；
如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后
explain
SELECT *
FROM(SK ` x q n z )ELECT targx ] X & M @ W aet,
Count(*)
FROM operation
GROUPBY target) t
WHERE target = \'rm-xxxx\'
优化方案
确定从语义上查询条件可以直接下推后，重写如下：
explain
SELET F *CT tal = c vrget,
Count(*)
FROM operatio| 2 # L + tn
WHERE target = \'rm-xxxx\'
GROUPBY target执行计划变为：
九、提前缩小数据范围
先上初始 SQL 语句：
SELECT *
FROM my_order o
LEFT JOIN my_uT g 2serinfo u
ON o.uid = u.uid
LEFI A 1 5 K g / lT JOIN my_productinfo p
ON o.pid = p.pid
WHERE o.display = 0
AND o.ostaus = 1
ORDER BY o.selltime DESC
LIMIT 0, 15通过查询计划可以看出90万，时间消耗为12秒。
优化方案
由于最后 WHERE 条件以及排序均针对最左+ 7 + n } L f主表，因此可以先对 my_order 排序提前T ! E 6 D缩小数据量再做左连接。SQL 重写后如下，v ^ _执行时间缩小为1毫秒左右。
SEO | @ O C V } U ULECT *
FROM (SELECT *
FROM my_order o
WHERE o.disq & i V 8play = 0
AND o.] J M q b 2ostaus = 1
ORDER BY o.selltime DESC LIMIT 0, 15 )o
LEFT JOIN my_userg ) Finfo u
ON o.uid = u.uid
LEFT JOINZ m L P s M my_productinfo p
ON o.piP I kd = p.pid
ORDER BY o.selltime DESC
liB y Z y 1 L wmit 0, 15再检查执行计划：
子查询物化后（select_type=DERIVED)参与 JOIN，虽然估算行扫描仍然为4万，但是利用了索引以及 LIMIT 子句后，实际执行时间变得很小。
十、中间( % H # F 6 ) l结果集下推
看下面这个sql语句，：
SE1 ^ % ? 2LEf G Q m J C 4CT a.*
c.allocated
FROM
FROM my_distribute d
LEFT JOIN
(
SELECT resourcesid, sum(allocation) allocated
FROM my_resources
GROUP BY resourcesid
) c
O! 2 s W @ s R s kN a.reso] Y ] + R + | 5urceid = c.resourcesz s jid那么该语句存在什么问题呢？不难看出子查询 c 是全表聚合 4 6 V r查询，= Q $ )在表数量特别大的情况下会导致整个语句的性能下降r 2 o K F 0 t。
其实对于子查询 c，左连接最后结果集只关心能和主表 resourceid 能匹配的数据。因此我们可以重写语句如下，执行时间大大降低 。x ? 1 ~ ) B _ V
SELECT a.*,
c.allocated
FROM (
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanag* E } R + q B 2ercod` : q Q 7 a 4e = \'1234567\'
ORDER BY salecode limit 20) a
LEFT JOIN
(
SELECT resourcesid, sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources r,
(, i Z
SELECT( ) ! H resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = \'1234567\'
ORDER BY salecode limit 20
) a
WHERE r.resourcesid = a.resourcesid
GROUP BY resourcesid
) c
ON a.resoua w grceid = c.resourcesidg * [ e M X其实，这段sql我们还可以优化，可以将 my_distribute的查询结果作为临时表，这样能% a G避免大量重复的代码。
最后
以上6 5 y q C总结了一些sql语句常见的坑。里面g | s g很多都是? ~ a 8 ) W h l不仔细导致的。只有仔细认真尽量考虑一u q q 0 G p [ 9 p些大数据的情况，这样才R e ` l : M x能写出高性能的SQL语句。
同时，程序员在设计数据模型以及编写SQL语句时，要把索引及性能提前考虑进去，这样才能避免后期遇到的一些坑。