Mysql大表优化方案

偏执的太偏执、 2022-05-28 12:20 219阅读 0赞

# Mysql大表优化方案 #

> 原文地址:[https://segmentfault.com/a/1190000006158186][https_segmentfault.com_a_1190000006158186]

标签： Mysql

--------------------

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

## 单表优化 ##

除非单表数据后来会一直上涨,否则不要考虑拆分,拆分会带来逻辑,部署,运维的各种复杂度.  
一般以整型值为主的表在千万级以下,字符型为主的表在500万以下是没有太大问题的.

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

### 字段优化 ###

*  尽量使用 `TINYINT`,`SMALLINT`,`MWDIUM_INT`作为整数类型而非 `INT`,如果非负则加上`UNSIGNED`.
 *  `VARCHAR` 的长度只分配需要的空间.
 *  使用枚举或者整数代替字符串类型.
 *  尽量使用`TIMESTAMP`而非`DATETIME`.
 *  单表不要有太多字段,建议在20以内.
 *  避免使用`null`字段,很难查询优化且占用额外索引空间.
 *  用整形来存ip

### 索引优化 ###

*  索引并不是越多越好,要根据查询有针对性的创建,考虑在 `ORDER BY`和`WHERE`命令上设计的列上建立索引可根据`EXPLAIN`来查看是否用了索引还是全表扫描.
 *  应尽量避免在`WHERE`字句中对字段进行`null`值判断.否则将导致引擎放弃索引改用全表扫描
 *  值分布很少的字段不适合建立索引.比如性别这种只有两三个值得字段.
 *  字符字段只建前缀索引.
 *  字符字段最好不要做主键.
 *  不用外键,用程序保证约束.
 *  尽量不用`UNIQUE`,由程序保证约束.
 *  使用多列索引时注意顺序和查询条件保持一致,同时删除不必要的单列索引.

### 查询SQL优化 ###

*  可通过开启慢查询日志查找出较慢的SQL.
 *  不做列运算: `SELECT id WHERE age+1=10` ,任何对列的操作都将导致全表扫描.它包括数据库教程函数,计算表达式等等,查询时尽量将操作移至等号右边.
 *  SQL语句尽可能简单:一条SQL只能在一个cpu进行运算;大语句拆分成小语句,减少锁时间;一条大SQL可以堵死整个库.
 *  不用 `SELECT *`.
 *  少用 `JOIN`.
 *  避免 `%xxx`式查询.
 *  不用函数和触发器,在应用程序实现.
 *  `OR`改写成`IN`:`OR`的效率是 n 级别,`IN`的效率是 log(n)级别.`IN`的个数建议控制在200以内.
 *  使用同类型进行比较:比如’123’和’123’比,123和123比.
 *  尽量避免在`WHERE` 子句中使用`!= <>`操作,否则将导致引擎放弃索引使用全表扫描.
 *  对于连续数值,使用`BETWEEN`而不用`IN`
 *  列数据不要拿全表,要使用 `LIMIT`进行分页,每页数量页不要太大.

## 引擎 ##

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

### MyISAM ###

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

*  不支持行锁,读取时需要对所有的表加锁.写入时则需要对对表加排他锁.
 *  不支持事物.
 *  不支持外键
 *  不支持崩溃后的安全恢复.
 *  在表有读取查询的时候,支持往表中添加新纪录.
 *  支持`BLOB`和`TEXT`的前500个字符的索引.支持全文索引.
 *  支持延迟更新索引,极大地提升写入性能.
 *  对于不会修改的表,支持压缩表,极大地减少磁盘空间占用.

### InnoDB ###

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

*  支持行锁,采用 MVCC 来支持高并发.
 *  支持事物
 *  支持外键
 *  支持崩溃后的恢复
 *  不支持全文索引

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

## 升级硬件 ##

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

## 读写分离 ##

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

## 缓存 ##

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

*  Mysql内部，对系统参数进行调优。
 *  **数据访问层**：比如Mybatis针对针对SQL语句做缓存，而Hibernate可以针对单个记录做缓存。这里缓存的对象主要是持久化对象 PersistenceObject。
 *  **应用服务层：** 这里可以使用编程的手段对缓存进行更精准的控制和更多的实现策略。这里缓存的对象使数据传输对象：DataTransferObject。
 *  **Web层：** 针对web页面做缓存。
 *  **浏览器客户端：** 针对用户端的缓存。

可以根据情况结合一个或者多个层次加入缓存。这里主要介绍服务层缓存的实现，目前主要有两种方式：  
\* 1. **直写式（Write Through）：** 在数据写入数据库后，同时更新缓存，位置缓存和数据库的一致性。这也是大多数缓存应用框架如：SpringCache 的工作方式。这种实现非常简单，同步好，但效率一般。  
\* 2. **回写式（Write Back）：** 当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存同步到数据库中。这种方式实现比较复杂需要较多的应用层逻辑，同时可能会产生数据库和缓存的不同步，但是效率非常高。

## 垂直拆分 ##

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

比如原始的用户表是：

![这里写图片描述][70]

垂直拆分后是：

![这里写图片描述][70 1]

垂直拆分的优点是:

*  可以使行数据变小,一个数据块 Block就可以存放更多的数据在查询时就会减少IO次数(每次查询时读取的Block就少)
 *  可以达到最大化利用Cache的目的,具体可以在春之拆分的时候讲不变的字段放在一起,讲常变化的字段放在一起.
 *  数据维护简单.

垂直拆分的缺点是:

*  主键出现冗余,需要管理冗余列
 *  会引起表连接(JOIN)操作,更加CPU开销,可以通过在业务服务器上进行join操作减少数据库压力
 *  **依然出出现单表数据量过大的问题,需要水平拆分.**

## 水平拆分 ##

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

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

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

![这里写图片描述][70 2]

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

水平拆分的优点是:

*  不存在单库大数据和高并发的性能瓶颈
 *  应用端改造较少
 *  提高了系统的稳定性和负载能力

缺点是：

*  分片事物一致性难以解决.
 *  跨界点Join性能较差,逻辑复杂.
 *  数据多次扩展和维护难度极大.

### 解决方案 ###

由于水平拆分涉及的逻辑比较复杂,当前也有了不少比较成熟的解决方案.  
这些方案分为两大类: 客户端架构和代理架构.

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

客户端架构的优点:

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

客户端架构的缺点:

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

**代理架构:**

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

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

代理架构的优点:

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

代理架构的缺点:

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

**各种方案的比较:**

![这里写图片描述][70 3]

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

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

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

*  客户端架构: Sharding JDBC
 *  代理架构: Mycat或者Atlas

## NoSQL ##

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

*  日志类,监控类,统计类数据
 *  非结构化或者弱结构化数据.
 *  对事物要求不强,并且无太多关操作的数据.

[https_segmentfault.com_a_1190000006158186]: https://segmentfault.com/a/1190000006158186
[70]: /images/20220528/455c21a5af0a4b0c8242abc7e9273002.png
[70 1]: /images/20220528/b1666d54896c4eff83ecb88c1ad5c018.png
[70 2]: /images/20220528/5a8fa787c0904582a438dc1ad8865d56.png
[70 3]: /images/20220528/d1d6e8a53f8641929bd05d3e6f1ec1d7.png