MySQL数据库优化方法_性能优化方法，看这一篇文章就够了

关于MySQL方法，看这一篇文章就够了

在众多运用数据库的应用项目里，数据仓库的运作效能往往构成整个系统运行速度的主要阻碍。数据仓库的运作速度是技术人员必须重视的环节，在规划数据仓库内部架构以及执行数据仓库动作（特别是检索数据时），都应当留意数据动作的效率。本文我们以MySQL数据仓库为参照实例展开论述。

一、数据库优化目标

1. 减少 IO 次数

IO始终是数据库运行中最容易产生阻碍的部分，这是由数据库所承担的任务性质所决定的，绝大多数数据库活动中有超过九成的执行时间都消耗在IO处理上，降低IO发生的频次是SQL调优时必须最先着手处理的环节，同时，这也是能够取得最显著成效的改进措施。

2. 降低 CPU 计算

在 SQL 优化方面，除了 IO 瓶颈，还需要关注 CPU 运算的优化问题。诸如 order by,group by 等操作，都是 CPU 资源消耗较大的部分，因为它们基本上都是通过 CPU 对内存中的数据进行比较和运算。当 IO 优化达到一定水平后，减少 CPU 计算，就成为了 SQL 优化的关键目标。

MySql查询过程

二、数据库优化方法

1. SQL语句优化

确定了改进方向后，要找出实现目标的具体途径。针对SQL查询，达成前述两项优化要求的方式其实很明确，就是调整SQL的运行方案，使其能尽量避开不必要的步骤，通过多种有效途径来获取所需信息，从而实现降低数据读写量和减轻处理器运算负荷的目的。

尽量减少使用连接操作,MySQL 以简洁著称,这种特性在某些场景下反而成为短板。它的优化器虽然运行效率很高,但由于收集的统计信息不够全面,导致优化过程更容易出现失误。在处理涉及多张表的连接操作时，由于当前系统的优化器存在局限性，同时对于连接操作本身的研究和投入尚显不足，其运行效率与***等先前的数据库产品相比，还存在明显差距。然而，当面对单表检索这类基础操作时，这种性能差异会变得非常微小，甚至在特定情况下，该系统的表现还会超越这些老牌数据库产品。

(2) 尽量少排序

排序过程会占用大量处理器资源，因此降低排序频率，在缓存读取效率高且输入输出性能良好的情况下，能够显著缩短 SQL 查询的执行周期。

(4) 尽量避免 *，并尽量用join代替子查询

(5) 尽量少使用“or”关键字

当 where 子句里包含好几个条件以“或”连接时，MySQL 的优化器在执行计划优化方面表现不佳，同时 MySQL 独有的 SQL 与 分层架构方式，导致其运行效率不高，很多时候采用 union all 或者 union（必要时）来替代“或”，能够获得更优的执行结果。

(6) 尽量用 union all 代替 union

联合和联合全取的区别在于前者需要把两个或多个查询结果进行合并处理，然后再执行去重步骤，这个过程会涉及排序，从而消耗更多 CPU 资源，导致资源占用增加和响应时间变长。因此，如果我们能够确定查询结果不会产生重复数据，或者对结果中的重复项并不在意，那么最好选用联合全取而非联合。

(7) 避免类型转换

(8) 能用的就不用GROUP BY

(9) 尽量不要用 INTO语句 ?

(10) 从全局出发优化，而不是片面调整

数据库性能提升不能局限于某个环节，必须全面审视系统内所有数据库查询语句，特别是在借助索引调整来改善查询效率时，绝不能只顾局部而忽略整体，以免因小错误导致重大损失。

2. 表结构优化

MySQL数据库以行为单位进行数据存储，在执行数据库操作时，其IO访问是以页为单位进行的，即每次读写操作针对的是整个页面的数据，而不是单独的记录，如果每条记录所占用的存储空间能够缩小，那么每个页面就能容纳更多的数据行，这样在执行IO操作时，单次访问就能获取更多的数据行。反过来讲，若数据行数一致，那么所需访问的页数会变少，进而 IO 操作频次降低，性能得以直接改善。

(1) 数据类型选择

数据行的总长度不可以超出8020字节,一旦超出就会在物理页中占据两行,进而导致存储出现碎片化,从而降低查询的速度;各个字段的长度应当在满足实际需求的最大范围内尽可能地压缩,这样做既可以提升查询的效率,同时在创建索引时也能节省更多的资源。

数据类型选择要谨慎，不是万不得已就不采用，不仅关乎占用容量，还牵涉到准确度问题。固定精度的小数类型，同样不太适合，最好乘以一个固定系数，将其转变为整数形式来保存，这样能够显著减少存储需求，并且不会增加后续维护负担。字符类型分为定长与不定长两种，定长字段推荐使用 CHAR 类型，查询速度快但占用更多存储空间，适合用户名、密码等长度相对固定的场景，需设定合理最大长度而非随意给定过大数值，因为不同长度范围在 MySQL 存储处理上存在差异，不定长字段则建议采用 类型，查询相对较慢但能节省存储空间，适用于评论等长度变化较大的内容，同样需要根据实际需求设定适当的最大长度限制。时间数据最好选用类型，这样占用空间能减半，对于仅需要记录某一天的情况，最好采用DATE类型，它的存储容量只要3个字节，比其他类型更小，不适宜用INT类型来保存UNIX时间戳，这种方式既不清晰，又可能引发维护困难，而且没有任何优势。状态字段可以考虑采用 ENUM 类型存储，这样做能显著节省存储空间，新增类型时只需在末尾追加即可，调整结构无需重建表内数据

(2) 字符编码

数据的存储编码方式由字符集直接决定，在MySQL中体现出来，内容相同但字符集不同，所占用的空间大小差异很大，因此选择合适的字符集，能够帮助我们尽可能压缩数据量，进而降低IO操作的频率。

(3) 尽量使用 NOT NULL

空值的表现与众不同，数据库难以对其优化处理。即便在 MySQL 中，空值与标准空值存在不同，仍能被索引收录，不过一旦涉及复合索引，含有空值字段的索引会显著降低整体索引效能。空值存在或许能节省一些资源，却引发诸多优化难题，非但未减少输入输出负担，反而增加了数据库查询的输入输出压力。因此，尽量保证字段数据不为空，也是构建高效表结构的重要考量方式。

3. 数据库架构优化

分布式和集群化：

服务器分配。服务器分配群体包含若干台各自独立的计算设备，它们借助公共网络或专用网络实现互联，并由路由设备加以连接，各台设备相互配合、共同承担任务、合理分配工作负担，对于用户而言，整个群体就像一台拥有极高处理能力的单一服务器。MySQL通常配置成服务器分配群体，具备读与写的分离处理，一般仅对读取操作进行分配调节。读与写分离。数据读写分离就是把查询和修改任务分配给不同的数据库机器，这样做可以降低数据库的负担，同时也能减少输入输出的工作。主要的数据库负责处理修改请求，其他的数据库则用来响应查询请求，实际上很多系统更频繁地使用查询功能。当主数据库执行修改时，需要把变更信息传递给从数据库，这样才能确保数据的一致性。通过这种方式，可以将数据分散处理。在特定条件下，把同个数据库里的数据分开放到好几个数据库里去，达成分散存放的效果，依据路由规则去访问某个数据库，如此一来每次访问碰到的就不再是一台服务器，而是N台服务器，因此能够减轻单台设备的负担。

4. 其他优化

(1) 适当使用视图加速查询。

对表的某个部分进行整理并生成新表，偶尔能提升检索效率（尤其是反复运行的检索）。这有助于防止多次排序动作，同时在其他层面也能减轻优化器的负担。新表里的数据行数少于原始表，并且实际排列顺序符合需求，从而降低了磁盘读写量，使得查询任务得以显著减轻。

(2) 算法优化。

尽量少用游标，这种工具的运行速度比较慢，一旦运用游标处理的数据量达到一万条以上，就必须思考其他替代方案。在决定采用游标技术或者临时表技术来处理问题之前，应该先去寻找基于集合的处理办法，通常来说，集合式的处理方式更加高效。游标和临时表，并不是绝对不能使用。针对小规模数据集合而言，运用游标进行逐条数据访问往往比其他处理方式更高效，特别是在需要关联多个数据表才能获取目标信息的情况下。

(3)封装存储过程。

经过处理和改进后保存在数据库主机上，执行速度快，能够减少客户端与服务器间的数据传输，有助于统一管理，方便进行后续维护工作。