mysql怎么分表分库 mysql分表分库方案
分库分表技术及技术方案
一、分库分表的必要性
为和田等地区用户提供了全套网页设计制作服务,及和田网站建设行业解决方案。主营业务为成都网站设计、成都做网站、和田网站设计,以传统方式定制建设网站,并提供域名空间备案等一条龙服务,秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求,就会得到认可,从而选择与我们长期合作。这样,我们也可以走得更远!
分库分表技术的使用,主要是数据库产生了瓶颈,如单库的并发访问或单表的查询都超出了阈值。对系统使用造成一定的影响,不得已而产生的技术。
通过分库分表技术来解决此类问题,但正因为使用此技术,会产生ACID一系列的问题,各类中间件解决此类问题各有各的优势。
提示:如场景无必要,千万不要使用分库分表。
二、分库分表的思路
1、垂直区分
垂直分库:从业务角度,一个库分成多个库,如把订单和用户信息分成两个库来存储。这样的好处就是可以微服务了。每块的业务单独部署,互不影响,通过接口去调用。
垂直分表:把大表分成多个小表,如热点数据和非热点数据分开,提高查询速度。
2、水平区分
水平分表:同一业务如数据量大了以后,根据一定的规则分为不同的表进行存储。
水平分库:如订单分成多个库存储,分解服务器压力。
以上一般来说,垂直分库和水平分表用的会多些。
三、分库分表的原理分析
分库分表常用的方案:Hash取模方案和range范围方案;
路由算法为最主要的算法,指得是把路由的Key按照指定的算法进行存放;
1、Hash取模方案
根据取余分配到不同的表里。要根据实际情况确认模的大小。此方案由于平均分配,不存在热点问题,但数据迁移很复杂。
2、Range范围方案
range根据范围进行划分,如日期,大小。此方案不存在数据迁移,但存在热点问题。
四、分库分表的技术选型
1、技术选型
解决方案主要分为4种:MySQL的分区技术、NoSql、NewSQL、MySQL的分库分表。
(1)mysql分区技术:把一张表存放在不同存储文件。由于无法负载,使用较少。
(2)NoSQL(如MongoDB):如是订单等比较重要数据,强关联关系,需约束一致性,不太适应。
(3)NewSql(具有NoSQL对海量数据的存储管理能力,还保持了传统数据库支持ACID和SQL等特性):如TiDB可满足需求。
(4)MySQL的分库分表:如使用mysql,此种方案为主流方式。
2、中间件
解决此类问题的中间件主要为:Proxy模式、Client模式。
(1)Proxy模式
(2)Client模式
把分库分表相关逻辑存放在客户端,一版客户端的应用会引用一个jar,然后再jar中处理SQL组合、数据库路由、执行结果合并等相关功能。
(3)中间件的比较
由于Client模式少了一层,运维方便,相对来说容易些。
五、分库分表的实践
根据容量(当前容量和增长量)评估分库或分表个数 - 选key(均匀)- 分表规则(hash或range等)- 执行(一般双写)- 扩容问题(尽量减少数据的移动)。
在这里我们选用中间件share-jdbc。
1、引入maven依赖
2、spring boot规则配置
行表达式标识符可以使用${...}或$-{...},但前者与Spring本身的属性文件占位符冲突,因此在Spring环境中使用行表达式标识符建议使用$-{...}。
3、创建DataSource
通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource,ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发,或者使用JPA, MyBatis等ORM工具。
浅谈mysql数据库分库分表那些事-亿级数据存储方案
mysql分库分表一般有如下场景
其中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单表(innoDB)可以存储10亿级数据,只是这时候性能比较差,业界公认MySQL单表容量在1KW以下是最佳状态,因为这时它的BTREE索引树高在3~5之间。
参考阿里开发手册建议:
1.单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表;如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。
2.实际情况受mysql机器配置等多方面影响,可能数据量很大但性能依旧不错,但考虑后续发展一定要进行分库分表考虑。
根据实际的业务场景选择合适的分片数据,参考如下:
根据实际的业务场景选择适当的分片字段,要达到如下要求:
分表数量和分表字段确定后,要设计一个合理的分表规则,良好的分表规则要达到如下条件:
如何保证分片数据均匀,参考:
如何保证方便后续分片扩容,参考:
如何高效的使用分库分表,核心是做到尽量的路由到最少的表,最好是只路由到一个表里面
核心规则如下:
阶段一
阶段二
阶段三
本文链接:
如何实现mysql的分库分表
1,接收到sql;2,把sql放到排队队列中 ;3,执行sql;4,返回执行结果。在这个执行过程中最花时间在什么地方呢?第一,是排队等待的时间,第二,sql的执行时间。其实这二个是一回事,等待的同时,肯定有sql在执行。所以我们要缩短sql的执行时间。
mysql中有一种机制是表锁定和行锁定,为什么要出现这种机制,是为了保证数据的完整 性,我举个例子来说吧,如果有二个sql都要修改同一张表的同一条数据,这个时候怎么办呢,是不是二个sql都可以同时修改这条数据呢?很显然mysql 对这种情况的处理是,一种是表锁定(myisam存储引擎),一个是行锁定(innodb存储引擎)。表锁定表示你们都不能对这张表进行操作,必须等我对 表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。如果数据太多,一次执行的时间太长,等待的时间就越长,这 也是我们为什么要分表的原因。
文章标题:mysql怎么分表分库 mysql分表分库方案
网页URL:http://myzitong.com/article/hidicj.html