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 分表分页查询解决思路
当业务上按照月份分表,但是前端h5需要分页展示,小伙伴们不知有没有遇到这个这个需求最后怎么完成的。
我这里想了一个解决思路,可能还不完善,希望能抛转引玉。
1、入参pageNo 为页号码,如果为1那么就是第一页。pageSize 可以是入参也可定死,这里定死10条。Limit 是数据偏移标记,根据入参pageNo 计算出来的,Limit=(pageNo-1)*pageSize。假设A表有41条数据符合,B表有51条数据符合,如下图。
有几种种情况 1.如果limitA表41条 那么需要从A表中取数据,(1)如果Limit+pageSizeCount直接获取数据即可(2)如果Limit+pageSizeCount,那么需要从A 表取部分数据从B表取一部分数据。
1.如果limitA表41条 那么需要从B表取数据,如果B数据依然不足,那么重复以上的步骤。如下图
mysql水平分表的几种方法
1.按时间分表
这种分表方式有一定的局限性,当数据有较强的实效性,如微博发送记录、微信消息记录等,这种数据很少有用户会查询几个月前的数据,如就可以按月分表。
2.按区间范围分表
一般在有严格的自增id需求上,如按照user_id水平分表:
table_1 user_id从1~100w
table_2 user_id从101~200w
table_3 user_id从201~300w
...
3.hash分表
通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名,然后访问相应的表。
按如下分10张表:
function get_hash_table($table, $userid)
{
$str = crc32($userid);
if ($str 0) {
$hash = "0" . substr(abs($str), 0, 1);
} else {
$hash = substr($str, 0, 2);
}
return $table . "_" . $hash;
}
echo get_hash_table('message', 'user18991'); //结果为message_10
echo get_hash_table('message', 'user34523'); //结果为message_13
分享名称:mysql分表怎么使用,mysql分表语句
文章来源:http://myzitong.com/article/hsiipp.html