MySQL之分库分表（二）实践

准备工作，如果根据之前的文章搭建了MyCat双主双从的读写分离架构，需要更改一些配置 —> 改为一主一从，如果还未搭建一主一从，请点击跳转MyCat之读写分离（一主一从）

• 更改MyCat配置，改为一主一从

  vim conf/clusters/dataSource.cluster.json
  1

  

  切换到datasources下，注释rwSepW2和rwSepR2主从数据源

  cd conf/datasources/
  1

  
  启动MyCat

---

登录MyCat

• 创建数据源、存储数据源

  /*+ mycat:createDataSource{ "name":"dw0","url":"jdbc:mysql://192.168.171.101:3306", "user":"root", "password":"123456" } */; 
  /*+ mycat:createDataSource{ "name":"dr0","url":"jdbc:mysql://192.168.171.101:3306", "user":"root", "password":"123456" } */; 
  /*+ mycat:createDataSource{ "name":"dw1","url":"jdbc:mysql://192.168.171.102:3306", "user":"root", "password":"123456" } */;
  /*+ mycat:createDataSource{ "name":"dr1","url":"jdbc:mysql://192.168.171.102:3306", "user":"root", "password":"123456" } */;
  1
  2
  3
  4

  查看数据源

  /*+ mycat:showDataSources*/;
  1

  

• 配置集群

  /*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */;
  /*! mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]} */;
  1
  2

  查看集群配置信息

  /*+ mycat:showClusters{}*/;
  1

  

• 执行建表语句

  -- 客户表 
  CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) );
  -- 订单表 
  CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) );
  -- 订单详细表 
  CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) );
  -- 订单状态字典表 
  CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) );
  1
  2
  3
  4
  5
  6
  7
  8

• 创建全局表（广播表）

  CREATE DATABASE mycat_01;
  1

  在建表语句上加上关键字BORADCAST;

  CREATE TABLE mycat_01.`travelrecord` (
  `id` bigint NOT NULL AUTO_INCREMENT, 
  `user_id` varchar(100) DEFAULT NULL, 
  `traveldate` date DEFAULT NULL, 
  `fee` decimal(10,0) DEFAULT NULL, 
  `days` int DEFAULT NULL, 
  `blob` longblob, 
  PRIMARY KEY (`id`), 
  KEY `id` (`id`) ) 
  ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

• 创建分片表（分库分表）

  	CREATE TABLE mycat_01.orders( 
  id BIGINT NOT NULL AUTO_INCREMENT, 
  order_type INT,
  customer_id INT, 
  amount DECIMAL(10,2), 
  PRIMARY KEY(id),
  KEY `id` (`id`) 
  )ENGINE=INNODB DEFAULT CHARSET=utf8 
  dbpartition BY mod_hash(customer_id) 
  tbpartition BY mod_hash(customer_id) 
  tbpartitions 1 
  dbpartitions 2;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

  
  mod_hash 相当于Java 的 % 运算符，进行取余运算

  插入数据

  INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 1, 1, 10, 259 );
  INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 2, 1, 11, 399 );
  INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 3, 1, 10, 219 );
  INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 4, 1, 10, 998 );
  INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 5, 2, 11, 2599 );
  INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 6, 2, 11, 1999 );
  1
  2
  3
  4
  5
  6

  查看物理表，根据（customer_id）进行分库分表
  

• 创建ER表
  ER表-> 即与↑↑↑↑↑分片表相关联的表，对相关的关联表进行分表

  CREATE TABLE mycat_01.orders_detail( 
  `id` BIGINT NOT NULL AUTO_INCREMENT, 
  detail VARCHAR(2000), 
  order_id INT, 
  PRIMARY KEY(id) 
  )ENGINE=INNODB DEFAULT CHARSET=utf8 
  dbpartition BY mod_hash(order_id) 
  tbpartition BY mod_hash(order_id) 
  tbpartitions 1 
  dbpartitions 2;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  插入数据

  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 1, '内存128G', 1 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 2, 'intel 7', 2 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 3, '固态硬盘', 3 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 4, '硬盘存储512GB', 4 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 5, '3年质保', 5 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 6, '7天无理由退换货', 6 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 7, '运存16G', 1 );
  INSERT INTO orders_detail ( id, detail, order_id )VALUES( 8, '5亿快充', 1 );
  1
  2
  3
  4
  5
  6
  7
  8

  查看物理库
  
  在使用 JOIN ON 语句查询时，MyCat2会自动识别ER关系，查看ER关系命令：

  /*+ mycat:showErGroup*/;
  1

  
  groupId相同的为一组，自动识别

---

其他章节 -> 跳转

end...
1