MySQL性能优化小结

MySQL数据库作为目前流行的数据库大量应用于PHP、JAVA、Python等Web语言开发项目中，大多数情况下，数据库的操作性能成为整个应用的性能瓶颈。

数据库的性能是程序员需要去关注的事情，当设计数据库表结构以及操作数据库（尤其是查询数据时），都需要注意数据操作的性能。

优化目标

01减少IO次数

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

02降低CPU计算

除了IO瓶颈之外，SQL性能优化中需要考虑的就是CPU运算量的优化了。

order by、group by、distinct…都是消耗CPU的大户（这些操作基本上都是CPU处理内存中的数据比较运算）。

当我们的IO优化做到一定阶段之后，降低CPU计算也就成为了我们SQL性能优化的重要目标。

MySql查询过程

优化方法

01SQL语句优化

明确了优化目标之后，我们需要确定达到我们目标的方法。

对于SQL语句来说，达到上述2个性能优化目标的方法其实只有一个，那就是改变SQL的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到“减少IO次数”和“降低CPU计算”的目标。

1）尽量少Join

MySQL的优势在于简单，但这在某些方面其实也是其劣势。MySQL优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。

对于复杂的多表Join，一方面由于其优化器受限，再者在Join这方面所下的功夫还不够，所以性能表现离Oracle等关系型数据库前辈还是有一定距离。

但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

2）尽量少排序

排序操作会消耗较多的CPU资源，所以减少排序可以在缓存命中率高等IO能力足够的场景下会较大影响SQL的响应时间。

3）尽量避免select*

尽量避免select*，并尽量用join代替子查询。

4）尽量少使用“or”关键字

当where子句中存在多个条件以“或”并存的时候，MySQL的优化器并没有很好的解决其执行计划优化问题，再加上MySQL特有的SQL与Storage分层架构方式，造成了其性能比较低下，很多时候使用unionall或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

5）尽量用unionall代替union

union和unionall的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。

所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用unionall而不是union。

其次要注意：

• 避免类型转换
• 能用DISTINCT的就不用GROUPBY
• 尽量不要用SELECTINTO语句
• 从全局出发优化，而不是片面调整

SQL性能优化不能是单独针对某一个进行，而应充分考虑系统中所有的SQL，尤其是在通过调整索引优化SQL的执行计划的时候，千万不能顾此失彼，因小失大。

02表结构优化

由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作IO的时候是以page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次IO可访问的行数也就增多了。

反过来说，处理相同行数的数据，需要访问的page就会减少，也就是IO操作次数降低，直接提升性能。

数据类型选择

原则是：数据行的长度不要超过8020字节，如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片，降低查询效率。

字段的长度在最大限度的满足可能的需要的前提下，应该尽可能的设得短一些，这样可以提高查询的效率，而且在建立索引的时候也可以减少资源的消耗。

1）数字类型

非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。

同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。

2）字符类型

定长字段，建议使用CHAR类型（CHAR查询快，但是耗存储空间，可用于用户名、密码等长度变化不大的字段）。

不定长字段尽量使用VARCHAR（VARCHAR查询相对慢一些但是节省存储空间，可用于评论等长度变化大的字段），且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

3）时间类型

尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME类型的一半。

对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。

不建议通过INT类型类存储一个unixtimestamp的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。

4）ENUM&SET

对于状态字段，可以尝试使用ENUM来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。

字符编码

字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

尽量使用NOTNULL

NULL类型比较特殊，SQL难优化。虽然MySQLNULL类型和Oracle的NULL有差异，会进入索引中，但如果是一个组合索引，那么这个NULL类型的字段会极大影响整个索引的效率。

虽然NULL空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保DEFAULT值不是NULL，也是一个很好的表结构设计优化习惯。

03架构优化

分布式和集群化

1）负载均衡

负载均衡集群是由一组相互独立的计算机系统构成，通过常规网络或专用网络进行连接，由路由器衔接在一起，各节点相互协作、共同负载、均衡压力。

对客户端来说，整个群集可以视为一台具有超高性能的独立服务器。MySQL一般部署的是高可用性负载均衡集群，具备读写分离，一般只对读进行负载均衡。

2）读写分离

读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻IO压力。

主数据库提供写操作，从数据库提供读操作，其实在很多系统中，主要是读的操作。当主数据库进行写操作时，数据要同步到从的数据库，这样才能有效保证数据库完整性。

3）数据切分

通过某种特定的条件，将存放在同一个数据库中的数据分散存放到多个数据库上，实现分布存储，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。

04

其他优化

1）适当使用视图加速查询

把表的一个子集进行排序并创建视图，有时能加速查询（特别是要被多次执行的查询）。

它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。

2）算法优化

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。与临时表一样，游标并不是不可使用。

对小型数据集使用FAST_FORWARD游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

封装存储过程

经编译和优化后存储在数据库服务器中，运行效率高，可以降低客户机和服务器之间的通信量，有利于集中控制，易于维护。