您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了“MySQL分庫分表的示例分析”,內(nèi)容簡而易懂,條理清晰,希望能夠幫助大家解決疑惑,下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL分庫分表的示例分析”這篇文章吧。
本文將完成如下目標(biāo):
分表數(shù)量: 256 分庫數(shù)量: 4
以用戶ID(user_id) 為數(shù)據(jù)庫分片Key
最后測試訂單創(chuàng)建,更新,刪除, 單訂單號(hào)查詢,根據(jù)user_id查詢列表操作。
架構(gòu)圖:
表結(jié)構(gòu)如下:
CREATE TABLE `order_XXX` ( `order_id` bigint(20) unsigned NOT NULL, `user_id` int(11) DEFAULT '0' COMMENT '訂單id', `status` int(11) DEFAULT '0' COMMENT '訂單狀態(tài)', `booking_date` datetime DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `idx_user_id` (`user_id`), KEY `idx_bdate` (`booking_date`), KEY `idx_ctime` (`create_time`), KEY `idx_utime` (`update_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注: 000<= XXX <= 255, 本文重點(diǎn)在于分庫分表實(shí)踐, 只保留具有代表性字段,其它場景可以在此基礎(chǔ)上做改進(jìn)。
全局唯一ID設(shè)計(jì)
要求:1.全局唯一 2:粗略有序 3:可反解出庫編號(hào)
1bit + 39bit時(shí)間差 + 8bit機(jī)器號(hào) + 8bit用戶編號(hào)(庫號(hào)) + 8bit自增序列
訂單號(hào)組成項(xiàng) | 保留字段 | 毫秒級(jí)時(shí)間差 | 機(jī)器數(shù) | 用戶編號(hào)(表編號(hào)) | 自增序列 |
---|---|---|---|---|---|
所占字節(jié)(單位bit) | 1 | 39 | 8 | 8 | 8 |
單機(jī)最大QPS: 256000 使用壽命: 17年
項(xiàng) | 版本 | 備注 |
---|---|---|
SpringBoot | 2.1.10.RELEASE | |
Mango | 1.6.16 | wiki地址:https://github.com/jfaster/mango |
HikariCP | 3.2.0 | |
Mysql | 5.7 | 測試使用docker一鍵搭建 |
進(jìn)入mysql:
#主庫 mysql -h 172.30.1.21 -uroot -pbytearch #從庫 mysql -h 172.30.1.31 -uroot -pbytearch
進(jìn)入容器
#主 docker exec -it db_1_master /bin/bash #從 docker exec -it db_1_slave /bin/bash
查看運(yùn)行狀態(tài)
#主 docker exec db_1_master sh -c 'mysql -u root -pbytearch -e "SHOW MASTER STATUS \G"' #從 docker exec db_1_slave sh -c 'mysql -u root -pbytearch -e "SHOW SLAVE STATUS \G"'
(1)在mysql master實(shí)例分別建庫
172.30.1.21( o rder_db_ 1) , 172.30.1.22( order_db_2) ,
172.30.1.23( ord er_db_3) , 172.30.1.24( order_db_4 )
(2)依次導(dǎo)入建表SQL 命令為
mysql -uroot -pbytearch -h272.30.1.21 order_db_1<fast-cloud-mysql-sharding/doc/sql/order_db_1.sql; mysql -uroot -pbytearch -h272.30.1.22 order_db_2<fast-cloud-mysql-sharding/doc/sql/order_db_2.sql; mysql -uroot -pbytearch -h272.30.1.23 order_db_3<fast-cloud-mysql-sharding/doc/sql/order_db_3.sql; mysql -uroot -pbytearch -h272.30.1.24 order_db_4<fast-cloud-mysql-sharding/doc/sql/order_db_4.sql;
<!-- mango 分庫分表中間件 --> <dependency> <groupId>org.jfaster</groupId> <artifactId>mango-spring-boot-starter</artifactId> <version>2.0.1</version> </dependency> <!-- 分布式ID生成器 --> <dependency> <groupId>com.bytearch</groupId> <artifactId>fast-cloud-id-generator</artifactId> <version>${version}</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency>
package com.bytearch.fast.cloud.mysql.sharding.common; /** * 分庫分表策略常用常量 */ public class ShardingStrategyConstant { /** * database 邏輯名稱 ,真實(shí)庫名為 order_db_XXX */ public static final String LOGIC_ORDER_DATABASE_NAME = "order_db"; /** * 分表數(shù) 256,一旦確定不可更改 */ public static final int SHARDING_TABLE_NUM = 256; /** * 分庫數(shù), 不建議更改, 可以更改,但是需要DBA遷移數(shù)據(jù) */ public static final int SHARDING_DATABASE_NODE_NUM = 4; }
4主4從數(shù)據(jù)庫配置, 這里僅測試默認(rèn)使用root用戶密碼,生產(chǎn)環(huán)境不建議使用root用戶。
mango: scan-package: com.bytearch.fast.cloud.mysql.sharding.dao datasources: - name: order_db_1 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.21:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.31:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 - name: order_db_2 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.22:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.32:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 - name: order_db_3 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.23:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.33:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 - name: order_db_4 master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.24:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 3000 slaves: - driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://172.30.1.34:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false user-name: root password: bytearch maximum-pool-size: 10 connection-timeout: 300
1). 根據(jù)order_id為shardKey分庫分表策略
package com.bytearch.fast.cloud.mysql.sharding.strategy; import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; import com.bytearch.id.generator.IdEntity; import com.bytearch.id.generator.SeqIdUtil; import org.jfaster.mango.sharding.ShardingStrategy; /** * 訂單號(hào)分庫分表策略 */ public class OrderIdShardingStrategy implements ShardingStrategy<Long, Long> { @Override public String getDataSourceFactoryName(Long orderId) { if (orderId == null || orderId < 0L) { throw new IllegalArgumentException("order_id is invalid!"); } IdEntity idEntity = SeqIdUtil.decodeId(orderId); if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) { throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId()); } //1. 計(jì)算步長 int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM; //2. 計(jì)算出庫編號(hào) long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1; //3. 返回?cái)?shù)據(jù)源名 return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo); } @Override public String getTargetTable(String logicTableName, Long orderId) { if (orderId == null || orderId < 0L) { throw new IllegalArgumentException("order_id is invalid!"); } IdEntity idEntity = SeqIdUtil.decodeId(orderId); if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) { throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId()); } // 基于約定,真實(shí)表名為 logicTableName_XXX, XXX不足三位補(bǔ)0 return String.format("%s_%03d", logicTableName, idEntity.getExtraId()); } }
2). 根據(jù)user_id 為shardKey分庫分表策略
package com.bytearch.fast.cloud.mysql.sharding.strategy; import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; import org.jfaster.mango.sharding.ShardingStrategy; /** * 指定分片KEY 分庫分表策略 */ public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> { @Override public String getDataSourceFactoryName(Integer userId) { //1. 計(jì)算步長 即單庫放得表數(shù)量 int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM; //2. 計(jì)算出庫編號(hào) long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1; //3. 返回?cái)?shù)據(jù)源名 return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo); } @Override public String getTargetTable(String logicTableName, Integer userId) { // 基于約定,真實(shí)表名為 logicTableName_XXX, XXX不足三位補(bǔ)0 return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM); } }
1). OrderPartitionByIdDao
package com.bytearch.fast.cloud.mysql.sharding.dao; import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant; import com.bytearch.fast.cloud.mysql.sharding.pojo.entity.OrderEntity; import com.bytearch.fast.cloud.mysql.sharding.strategy.OrderIdShardingStrategy; import org.jfaster.mango.annotation.*; @DB(name = ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, table = "order") @Sharding(shardingStrategy = OrderIdShardingStrategy.class) public interface OrderPartitionByIdDao { @SQL("INSERT INTO #table (order_id, user_id, status, booking_date, create_time, update_time) VALUES" + "(:orderId,:userId,:status,:bookingDate,:createTime,:updateTime)" ) int insertOrder(@TableShardingBy("orderId") @DatabaseShardingBy("orderId") OrderEntity orderEntity); @SQL("UPDATE #table set update_time = now()" + "#if(:bookingDate != null),booking_date = :bookingDate #end " + "#if (:status != null), status = :status #end" + "WHERE order_id = :orderId" ) int updateOrderByOrderId(@TableShardingBy("orderId") @DatabaseShardingBy("orderId") OrderEntity orderEntity); @SQL("SELECT * FROM #table WHERE order_id = :1") OrderEntity getOrderById(@TableShardingBy @DatabaseShardingBy Long orderId); @SQL("SELECT * FROM #table WHERE order_id = :1") @UseMaster OrderEntity getOrderByIdFromMaster(@TableShardingBy @DatabaseShardingBy Long orderId);
@SpringBootTest(classes = {Application.class}) @RunWith(SpringJUnit4ClassRunner.class) public class ShardingTest { @Autowired OrderPartitionByIdDao orderPartitionByIdDao; @Autowired OrderPartitionByUserIdDao orderPartitionByUserIdDao; @Test public void testCreateOrderRandom() { for (int i = 0; i < 20; i++) { int userId = ThreadLocalRandom.current().nextInt(1000,1000000); OrderEntity orderEntity = new OrderEntity(); orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); orderEntity.setStatus(1); orderEntity.setUserId(userId); orderEntity.setCreateTime(new Date()); orderEntity.setUpdateTime(new Date()); orderEntity.setBookingDate(new Date()); int ret = orderPartitionByIdDao.insertOrder(orderEntity); Assert.assertEquals(1, ret); } } @Test public void testOrderAll() { //insert int userId = ThreadLocalRandom.current().nextInt(1000,1000000); OrderEntity orderEntity = new OrderEntity(); orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); orderEntity.setStatus(1); orderEntity.setUserId(userId); orderEntity.setCreateTime(new Date()); orderEntity.setUpdateTime(new Date()); orderEntity.setBookingDate(new Date()); int i = orderPartitionByIdDao.insertOrder(orderEntity); Assert.assertEquals(1, i); //get from master OrderEntity orderInfo = orderPartitionByIdDao.getOrderByIdFromMaster(orderEntity.getOrderId()); Assert.assertNotNull(orderInfo); Assert.assertEquals(orderInfo.getOrderId(), orderEntity.getOrderId()); //get from slave OrderEntity slaveOrderInfo = orderPartitionByIdDao.getOrderById(orderEntity.getOrderId()); Assert.assertNotNull(slaveOrderInfo); //update OrderEntity updateEntity = new OrderEntity(); updateEntity.setOrderId(orderInfo.getOrderId()); updateEntity.setStatus(2); updateEntity.setUpdateTime(new Date()); int affectRows = orderPartitionByIdDao.updateOrderByOrderId(updateEntity); Assert.assertTrue( affectRows > 0); } @Test public void testGetListByUserId() { int userId = ThreadLocalRandom.current().nextInt(1000,1000000); for (int i = 0; i < 5; i++) { OrderEntity orderEntity = new OrderEntity(); orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM)); orderEntity.setStatus(1); orderEntity.setUserId(userId); orderEntity.setCreateTime(new Date()); orderEntity.setUpdateTime(new Date()); orderEntity.setBookingDate(new Date()); orderPartitionByIdDao.insertOrder(orderEntity); } try { //防止主從延遲引起的校驗(yàn)錯(cuò)誤 Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } List<OrderEntity> orderListByUserId = orderPartitionByUserIdDao.getOrderListByUserId(userId); Assert.assertNotNull(orderListByUserId); Assert.assertTrue(orderListByUserId.size() == 5); } }
大功告成:
以上是“MySQL分庫分表的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。