MySQL数据库性能优化之SQL优化

这篇文章是以MySQL为背景，很多内容同时适用于其他关系型数据库，需要有一些索引知识为基础。51Testing软件测试网l~NK}Tp^\

　　优化目标51Testing软件测试网W(~;WshxUj1z a

^%w{gsp'WX(g0　　1、减少 IO 次数

KK+xUb0

9Yq;g3@L2Z%G CI0　　IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是SQL优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

_9c,R-HkO^[051Testing软件测试网IZ o$viKC

　　2、降低 CPU 计算51Testing软件测试网9v6w:C2I(gCTHl

51Testing软件测试网Qore/Ef lj

　　除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by， group by，distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标51Testing软件测试网C6q)E-stb!B

,HO/eZlnV^\0　　优化方法51Testing软件测试网%X)]"` hr7^

[,wdsum0　　1、改变 SQL 执行计划

EC6O a8{&^051Testing软件测试网D)ql8O L&EH8?R#j

　　明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标51Testing软件测试网3n T
c2t6}G,@'\JR%MF

51Testing软件测试网 `LL:Y
_

　　常见误区51Testing软件测试网j#S!Xo8Q

(K5BH)y#G@0　　1、count(1)和count(primary_key) 优于 count(*)

:i9U^+e vG9F0

)[7QV)FT7k,h/Uv\#F2u$k0　　很多人为了统计记录条数，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对 count(*) 计数操作做了一些特别的优化。

!x$D/Oc-hw0

8`MkS aP0　　2、count(column) 和 count(*) 是一样的51Testing软件测试网'N,Z ~lo/b"@P

"M$]6m.jB9?0\ D0　　这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和 count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。51Testing软件测试网tQc'd&ik k

A ~9sa$}Yj _a5_0　　count(column) 是表示结果集中有多少个column字段不为空的记录；51Testing软件测试网+@$F(m,HM z)~

+I3C_0{2`!gghtw9}0　　count(*) 是表示整个结果集有多少条记录；51Testing软件测试网^j$}1d8G$\

51Testing软件测试网4TiY;f(XN[B

　　3、select a，b from … 比 select a，b，c from … 可以让数据库访问更少的数据量

:M9Ji5Z-f6{srr9?0

9VSHwf5zRIA,y/v0　　这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。51Testing软件测试网2J:FG}1E

5Gj"T8{"I#R0　　实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。51Testing软件测试网[%k#K8X#|pS!j

sJ]p1tQ qS"cc0　　所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。

u@!k#h_5q-Sf1TV0

&F X_[ M.p9H
R JN0　　当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取 a，b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。

}-Q%yz;`"\n0yN051Testing软件测试网4xd/ZvC6i ?"L

　　4、order by 一定需要排序操作51Testing软件测试网yWp'i OzaI

Tm
l3t cH0　　我们知道索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。

pm@5o8E%Rz^9zo051Testing软件测试网zW P`(\L:ah2IKE

　　实际上，利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段51Testing软件测试网dJc4`QW

51Testing软件测试网%w0ASw[gH#|

　　5、执行计划中有 filesort 就会进行磁盘文件排序

}
wuP6bCNW"P0X0

:~:^f*m5B*myp0　　有这个误区其实并不能怪我们，而是因为 MySQL 开发者在用词方面的问题。filesort 是我们在使用 explain 命令查看一条 SQL 的执行计划的时候可能会看到在 “Extra” 一列显示的信息。51Testing软件测试网+w)l#|:P1wlJ

*}aK){ t)t}(wp&B)L0　　实际上，只要一条 SQL 语句需要进行排序操作，都会显示“Using filesort”，这并不表示就会有文件排序操作。

*tUglJb(sc0