MYSQL性能优化之数据库的分库分表

拼搏现实的明天。 2024-02-18 21:45 15阅读 0赞

> 数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，由于无法进行分布式式部署，而一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。  
> 说白了，就是分担写负载

分库分表一

> 节点：mysql数据库一主多从的数据库集群（数据一致），所以用节点表示  
> 重新对数据库连接进行配置就行  
> 优点：数据库拆分比较简单，尤其适合没有跨库查询的情况下  
> 缺点：如果写操作主要在订单表，那就分库分表的作用不是很大了

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

分库分表二

> 当表所在的服务器也达到瓶颈时，无法继续升级  
> ![这里写图片描述][SouthEast 1]

#### 分库分表三：水平拆分（分片->不同物理节点） ####

**类似分区表（不同：一个节点：数据库），分片很容易出现问题，难以维护，慎重**  
![这里写图片描述][SouthEast 2]

分片前准备

![这里写图片描述][SouthEast 3]  
![这里写图片描述][SouthEast 4]  
\- 不经常更新的表  
\- 数据量不大，字典列表  
\- 使用多写的方式维护一致性（表更新时，同时更新所有分片的表操作）  
\- 定期检查（防止业务逻辑问题）  
\- 第二种方式优点是数据没有冗余问题，查询效率差一些，因为要关联，合并

![这里写图片描述][SouthEast 5]

![这里写图片描述][SouthEast 6]

**数据量和访问量的平均，结合业务做分析**  
\- 第一种：分区键并不总是数值，所有要用模来转换成数值性数据  
\- 优点：可以比较平均的分配数据  
\- 缺点：很难人为控制哪个数据分配到哪个分片中  
\- 第二种：常用于分区键是日期类型或者是数据类型的情况  
\- 优点：很清楚知道数据在哪个分片中  
\- 缺点：数据（访问量）分配不平均  
\- 第三种：  
\- 灵活对数据在哪些分片进行控制  
\- 映射表会产生很大的服务压力，可以使用缓存（否则很可能成为系统的瓶颈）

![这里写图片描述][SouthEast 7]

*  auto\_increment\_increment（总分片数） auto\_increment\_offset（不同的值）
    
     *  只适用于一个节点保存一套分区表
 *  全局节点ID->建表使用auto\_increment属性，生成自增ID->性能瓶颈
 *  类似第二种

#### 演示 ####

![这里写图片描述][SouthEast 8]

分片工具（演示，不是很完美）

![这里写图片描述][SouthEast 9]

> 三个节点：节点1（服务器节点），节点2（分片数据库节点），节点3（oneProxy节点）

节点1,节点2添加oneProxy连接用户  
![这里写图片描述][SouthEast 10]

demo.sh 启动脚本

*  1

![这里写图片描述][SouthEast 11]

*  修改配置
    
     *  添加分片节点
     *  通过oneProxy客户端获取加密字符串密码 ./demo.sh
     *  mysql -P4041 -uadmin -pOneProxy -h127.0.0.1;
     *  passwd “123456” （获取密码）
     *  修改proxy-user-list 用户/加密密码@数据库名
     *  peoxy-part-template 分片方式地址
     *  remote-address 远程管理地址
     *  ![这里写图片描述][SouthEast 12]

设置分片方式

订单表，订单商品表，相互关联，分片键一致  
\- 表名  
\- 分片键  
\- 键类型  
\- 分片方式  
\- 分片方式（分片）  
\- suffix后缀  
\- 分片组

![这里写图片描述][SouthEast 13]

![这里写图片描述][SouthEast 14]

建表

节点1  
![这里写图片描述][SouthEast 15]  
节点2  
![这里写图片描述][SouthEast 16]  
![这里写图片描述][SouthEast 17]  
节点3

ps -ef | grep oneproxy
    重启oneProxy
    kill -9 1164
    ./demo.sh
    mysql -P4041 -uadmin -pOneProxy -h127.0.0.1;
    list backend;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6

![这里写图片描述][SouthEast 18]  
![这里写图片描述][SouthEast 19]

测试

![这里写图片描述][SouthEast 20]

> 通过oneproxy连接  
> mysql -utest -p123456 -h127.0.0.1 -p3306

![这里写图片描述][SouthEast 21]

节点1：  
![这里写图片描述][SouthEast 22]  
节点2：  
![这里写图片描述][SouthEast 23]

[SouthEast]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2da2e6a404ff49abbb363a81194c0d46.png
[SouthEast 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1e4b16fc0acf4b62a3bebe9089bacfe1.png
[SouthEast 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/878bf5767d3d4c72a88a67dfc360c697.png
[SouthEast 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2d3bf056e9ec48a492bae67dea4c0cc0.png
[SouthEast 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d591f5ca5df149e890fe58cada60a680.png
[SouthEast 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ca55e3a8a403447bbd698ca9ad0dd5c4.png
[SouthEast 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b3deb88b5f10417d88411689362d7144.png
[SouthEast 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d55f764800294f74b97fd7862bcf97ce.png
[SouthEast 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ad0e90b149554addab7ada662da95aee.png
[SouthEast 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d244da379e4b45129fb18ec8a30bf691.png
[SouthEast 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5b115d2ede864bafb7dea10c30149d64.png
[SouthEast 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f6600fd6cd4a41eaa19cca1ea87a9f64.png
[SouthEast 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3da88a93ff304a06aa749abb2fe0477c.png
[SouthEast 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/811af85b28b541798853142b09fffd54.png
[SouthEast 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/9df347d6907042408c0ef0e626ca6314.png
[SouthEast 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e67c4616146e40109404728b99a8cac0.png
[SouthEast 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b4ed5051e758423684742ec59fddeeed.png
[SouthEast 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a6bc945786fa46069a0fd16cd1757355.png
[SouthEast 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/64c1002db4934af8aa1b2bc94c7fd170.png
[SouthEast 19]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c48a7a46a87f44c78d2d41619f8bb4bf.png
[SouthEast 20]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/031e07f224f4491ca25656b07e8b2ab3.png
[SouthEast 21]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/398853d26cd441db8da53ecd1cd62c64.png
[SouthEast 22]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8fe9ff1c58eb41a0b07860cf7fc1324f.png
[SouthEast 23]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cdebb6cdf89342d5ab7584ff231b6ea2.png