浅谈mysql数据库分库分表那些事-亿级数据存储方案

mysql分库分表一般有如下场景

成都创新互联从2013年开始，是专业互联网技术服务公司，拥有项目网站制作、网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元顺昌做网站,已为上家服务,为顺昌各地企业和个人服务,联系电话:18980820575

其中1，2相对较容易实现,本文重点讲讲水平拆表和水平拆库,以及基于mybatis插件方式实现水平拆分方案落地。

在 《聊一聊扩展字段设计》 一文中有讲解到基于KV水平存储扩展字段方案,这就是非常典型的可以水平分表的场景。主表和kv表是一对N关系,随着主表数据量增长,KV表最大N倍线性增长。

这里我们以分KV表水平拆分为场景

对于kv扩展字段查询,只会根据id + key 或者 id 为条件的方式查询,所以这里我们可以按照id 分片即可

分512张表(实际场景具体分多少表还得根据字段增加的频次而定)

分表后表名为kv_000 ~ kv_511

id % 512 = 1 .... 分到 kv_001,

id % 512 = 2 .... 分到 kv_002

依次类推!

水平分表相对比较容易,后面会讲到基于mybatis插件实现方案

场景:以下我们基于博客文章表分库场景来分析

目标:

表结构如下(节选部分字段):

按照user_id sharding

假如分1024个库,按照user_id % 1024 hash

user_id % 1024 = 1 分到db_001库

user_id % 1024 = 2 分到db_002库

依次类推

目前是2个节点,假如后期达到瓶颈,我们可以增加至4个节点

最多可以增加只1024个节点,性能线性增长

对于水平分表/分库后,非shardingKey查询首先得考虑到

基于mybatis分库分表,一般常用的一种是基于spring AOP方式, 另外一种基于mybatis插件。其实两种方式思路差不多。

为了比较直观解决这个问题,我分别在Executor 和StatementHandler阶段2个拦截器

实现动态数据源获取接口

测试结果如下

由此可知,我们需要在Executor阶段 切换数据源

对于分库:

原始sql:

目标sql:

其中定义了三个注解

@useMaster 是否强制读主

@shardingBy 分片标识

@DB 定义逻辑表名 库名以及分片策略

1)编写entity

Insert

select

以上顺利实现mysql分库,同样的道理实现同时分库分表也很容易实现。

此插件具体实现方案已开源:

目录如下:

mysql分库分表,首先得找到瓶颈在哪里(IO or CPU),是分库还是分表,分多少？不能为了分库分表而拆分。

原则上是尽量先垂直拆分 后 水平拆分。

以上基于mybatis插件分库分表是一种实现思路,还有很多不完善的地方,

例如:

mysql表很大sum不全

mysql表很大sum不全的解决办法：

1、优化sql和索引。

2、加缓存，memcached，redis。

3、以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas，其它的要么效率不高，要么没人维护。

4、以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对应用是透明的，无需更改代码，sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区。

5、以上都做了，那就先做垂直拆分，其实就是根据模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统。

6、水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的shardingkey，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带shardingkey，将数据定位到限定的表上去查，而不是扫描全部的表。

Mysql多字段大表的几种优化方法

一、优化表的数据类型

select * from tablename procedure analyse(16.265);

上面输出一列信息，牟你数据表的字段提出优化建义，

二、通过拆分表提高数据访问效率

拆分一是指针对表进行拆分，如果是针对myisam类型的表进行处理的话，可以有两种拆分方法

1、是垂直拆分，把主要的与一些散放到一个表，然后把主要的和另外的列放在另一张表。

2、水平拆分方法，根据一列或多列的值把数据行放到两个独立的表中，水平拆分通常几种情况。

表很大，拆分后可降低查询时数据和索引的查询速度，同时也降低了索引的层数，提高查询的速度。

表中的数据本来就有独立性，表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，厕国一些数据不常用的情况下，

需要把数据存放到多个不同的介质上。

三、逆规范化

四、使用中间表优化方法

对于数据库教程大的表源码天空

mysql数据库的垂直分表怎么做

mysql数据库最适合中型规模的项目。尽量不要垂直分表。单纯的垂直分表对性能提升没什么帮助。

mysql数据库表太大查询慢优化的几种方法

优化方案：

主从同步+读写分离：

这个表在有设备条件的情况下，读写分离，这样能减少很多压力，而且数据稳定性也能提高

纵向分表：

根据原则，每个表最多不要超过5个索引，纵向拆分字段，将部分字段拆到一个新表

通常我们按以下原则进行垂直拆分:（先区分这个表中的冷热数据字段）

把不常用的字段单独放在一张表;

把text，blob等大字段拆分出来放在附表中;

经常组合查询的列放在一张表中;

缺点是：很多逻辑需要重写，带来很大的工作量。

利用表分区：

这个是推荐的一个解决方案，不会带来重写逻辑等，可以根据时间来进行表分区，相当于在同一个磁盘上，表的数据存在不同的文件夹内，能够极大的提高查询速度。

横向分表：

1000W条数据不少的，会带来一些运维压力，备份的时候，单表备份所需时间会很长，所以可以根据服务器硬件条件进行水平分表，每个表有多少数据为准。

当前文章：mysql怎么垂直拆分 mysql 拆分
分享地址：http://scyanting.com/article/hgiheh.html