Mysql大表优化方案

当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化。

单表优化

除非单表数据后来会一直上涨,否则不要考虑拆分,拆分会带来逻辑,部署,运维的各种复杂度.

一般以整型值为主的表在千万级以下,字符型为主的表在500万以下是没有太大问题的.

事实上,Mysql的单表性能还是有很多优化空间的.甚至能正常支撑千万级以上的数据量.

字段优化 索引优化 查询SQL优化 引擎

目前广泛使用的是MyISAM引擎和InnoDB两种引擎.

MyISAM

引擎是Mysql5.1版本之前的默认引擎.他的特点是:

InnoDB

InnoDB在Mysql5.5之后成为默认引擎,他的特点是:

总的来说,MyISAM适合SELECT密集型的表,而InnoDB适合insert和update密集型的表.

升级硬件

Scale up,这个不必多少,根据MYSQL是CPU密集新还是I/O密集型通过提升CPU和内存,或者使用SSD,都能显著提升MYSQL性能.

读写分离

也是目前常用的优化方式. 从库读主库写.不建议采用双主或者多主引入不必要的复杂性，尽量采用稳重其它方案来提高性能。同时目前很多拆分的解决方案也考虑到了读写分离。

缓存

缓存可以发生在这些层次：

可以根据情况结合一个或者多个层次加入缓存。这里主要介绍服务层缓存的实现，目前主要有两种方式：

* 1. 直写式（Write Through）： 在数据写入数据库后，同时更新缓存，位置缓存和数据库的一致性。这也是大多数缓存应用框架如：SpringCache 的工作方式。这种实现非常简单mysql 优化，同步好，但效率一般。

* 2. 回写式（Write Back）： 当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存同步到数据库中。这种方式实现比较复杂需要较多的应用层逻辑，同时可能会产生数据库和缓存的不同步，但是效率非常高。

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

比如原始的用户表是：

垂直拆分后是：

垂直拆分的优点是:

垂直拆分的缺点是:

水平拆分

水平拆分就是通过某种策略将数据分片存储,每片数据会分配到不同的mysql数据表或者库中达到分布式的效果,能够支持非常大的数据量.

库内分表: 仅仅是解决了单一数据表过大的问题,由于没有把表的数据分配到不同的机器上,因此对于减轻Mysql服务器压力来说没有太大的作用.大家还是竞争同一物理机上的CPU,IO,网络,这就需要通过分库开解决.

前面垂直拆分的用户表如果进行水平拆分，结果是：

实际情况中往往会是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表

水平拆分的优点是:

缺点是：

解决方案

由于水平拆分涉及的逻辑比较复杂,当前也有了不少比较成熟的解决方案.

这些方案分为两大类: 客户端架构和代理架构.

客户端架构:

通过修改数据库访问层,如 JDBC,Data Source,Mybatis,通过配置来管理多个数据源,直连数据库,并在模块内完成数据的分片整合,一般以jar包的形式出现.

客户端架构的优点:

    1.应用直连数据库,降低外围系统宕机带来的风险.
    2.集成成本低,无需额外的运维组件.

客户端架构的缺点:

    1.限于只能在数据库访问层上做文章,扩展性一般,但对于比较复杂的系统可能力不从心.
    2.将分片的逻辑放在应用服务器,造成额外风险.

代理架构:

通过独立的中间件来统一管理所有的互数据源和数据分片集合.后端数据库集群对前端应用透明.需要独立部署和运维代理组件.

代理组件为了分流和防止单点,一般采用集群方式出现.同事可能需要zookeeper之类服务组件来管理.

代理架构的优点:

    1.能够处理非常复杂的需求,不受数据库访问层原来实现的限制,扩展性强.
    2.对于应用服务器透明且没有增加任何额外负担.

代理架构的缺点:

    1.需部署和运维独立的代理中间件,成本高.
    2.需要经过代理来连接数据库,网络上多了一跳,性能有损失且偶额外风险.

各种方案的比较:

如此多的方案，如何进行选择？可以按以下思路来考虑：

首先确定使用代理架构还是客户端架构.中小型规模或是比较简单的应用倾向于选择客户端架构,复杂场景或者大规模系统倾向于选择代理架构.具体功能是否满足,比如跨界点 ORDER BY,那么支持该功能的优先考虑.不考虑一年内都没有更新或者维护的项目最好按照 大公司->社区->小公司 -> 个人 这样的出品方顺序来进行选择.选择口碑比较好的,比如github 星数,使用者数量和使用者反馈.开源 的优先,往往项目有需要膝盖源代码.

按照以上思路,推荐选择:

NoSQL

在Mysql上做Sharding是一种戴着镣铐的跳舞,事实上很多大表本身对Mysql这种RDBMS需求并不大,并不要求ACID,可以考虑将这些表迁移到 NoSQL,彻底解决水平扩展的问题.例如:

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!