掌握MySQL分库分表（一）数据库性能优化思路、分库分表优缺点

不能⼀上来就说分库分表！

MySQL数据库性能优化思路【面试题】

根据实际情况分析，两个角度思考：不分库分表、分库分表

不分库分表

软优化

1. 数据库参数调优
2. 分析慢查询SQL语句，分析执行计划，进行sql改写和程序改写
3. 优化数据库索引结构
4. 优化数据表结构优化
5. 引入NOSQL和程序架构调整

硬优化

提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘

分库分表

1. 根据业务情况而定，选择合适的分库分表策略（没有通用的策略）
   外卖、物流、电商领域
2. 先看只分表是否满⾜业务的需求和未来增长
   数据库分表能够解决单表数据量很大时，数据查询的效率问题
   无法给数据库的并发操作带来效率上的提高，分表的实质还是在⼀个数据库上进行的操作，受数据库IO性能的限制
3. 如果单分表满足不了需求，再分库分表⼀起

结论

在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案
如果数据量极⼤，且业务持续增长快，再考虑分库分表方案

分库分表能解决的问题

解决数据库本身瓶颈

连接数

连接数过多时，就会出现‘too many connections’的错误，访问量太大或者数据库设置的最大连接数太小的原因
Mysql默认的最大连接数为100可以修改，⽽mysql服务允许的最⼤连接数为16384

数据库分表可以解决单表海量数据的查询性能问题
数据库分库可以解决单台数据库的并发访问压力问题

解决系统本身IO、CPU瓶颈

1. 磁盘读写IO瓶颈，热点数据太多，尽管使用了数据库本身缓存，但是依旧有⼤量IO，导致sql执行速度慢
2. 网络IO瓶颈，请求的数据太多，数据传输大，网络带宽不够，链路响应时间变长
3. CPU瓶颈，尤其在基础数据量大单机复杂SQL计算，SQL语句执行占用CPU使用率高，也有扫描行数大、锁冲突、锁等待等原因

可以通过 show processlist、show full processlist，发现 CPU 使用率比较高的SQL

常见的对于查询时间长，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能问题SQL，清楚相关影响问题的情况可以kill掉

也存在执行时间短，但是CPU占用率⾼的SQL，通过上面命令查询不到，这个时候最好通过执行计划分析explain进行分析

分库分表带来的问题

问题⼀ 跨节点数据库Join关联查询

数据库切分前，多表关联查询，可以通过sql join进行实现分库分表后，数据可能分布在不同的节点上，sql join带来的问题就⽐较麻烦

问题二 分库操作带来的分布式事务问题

操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务

问题三 执行的SQL排序、翻页、函数计算问题

分库后，数据分布再不同的节点上， 跨节点多库进行查询时，会出现limit分页、order by排序等问题
而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资
源损耗）

问题四 数据库全局主键重复问题

常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题

问题五 容量规划,分库分表后二次扩容问题

业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容

问题六 分库分表技术选型问题

市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择