# mysql_02 **Repository Path**: fw19940314/mysql_02 ## Basic Information - **Project Name**: mysql_02 - **Description**: mysql作业二 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2020-07-11 - **Last Updated**: 2020-12-18 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README ### 视频地址 链接:https://pan.baidu.com/s/1zF2aLi-oyx9XuF5fcHChkg 密码:bx28 ### 环境搭建 |主机|ip| |---|---| |master0|192.168.19.161| |slave0|192.168.52.137| |slave1|192.168.52.131| |master1|192.168.52.133| |slave2|192.168.52.135| |slave3|192.168.52.136| - 两套主从环境 搭建mysql两套主从环境 maste0,slave0,slave1 maste1,slave2,slave3 ### sharding jdbc项目配置 ```properties #数据源 spring.shardingsphere.datasource.names=master0,slave0,slave1,master1,slave2,slave3 spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.19.161:3306/mysql_second?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.shardingsphere.datasource.master0.username=root spring.shardingsphere.datasource.master0.password=root spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.52.137:3306/mysql_second?useSSL=false spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=root spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.52.131:3306/mysql_second?useSSL=false spring.shardingsphere.datasource.slave1.username=root spring.shardingsphere.datasource.slave1.password=root spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.52.133:3306/mysql_second?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.shardingsphere.datasource.master1.username=root spring.shardingsphere.datasource.master1.password=root spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.52.135:3306/mysql_second?useSSL=false spring.shardingsphere.datasource.slave2.username=root spring.shardingsphere.datasource.slave2.password=root spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.52.136:3306/mysql_second?useSSL=false spring.shardingsphere.datasource.slave3.username=root spring.shardingsphere.datasource.slave3.password=root #分库分表 #根据userId取模分库 spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2} spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{0..1}.c_order #主键生成策略 spring.shardingsphere.sharding.tables.c_order.key-generator.column=id spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE #读写分离 spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0 spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0, slave1 spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1 spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave2, slave3 ``` ### 测试 #### 1. 数据插入 - 循环50次数据插入，查看分表情况 ```java @Test @Repeat(50) public void test1(){ Random random =new Random(); int nextInt = random.nextInt(100); COrder cOrder = new COrder(); cOrder.setPositionId(1001); cOrder.setDel(false); cOrder.setResumeType(1); cOrder.setCompanyId(11); cOrder.setUserId(nextInt); cOrder.setPublishUserId(10); cOrder.setDel(false); cOrder.setStatus("1"); cOrder.setCreateTime(new Date()); cOrder.setUpdateTime(new Date()); cOrderRepository.save(cOrder); } ``` - master0插入情况 ```sql select * from c_order; +--------------------+--------+---------+------------+-----------------+-------------+-------------+--------+---------------------+---------------------+ | id | is_del | user_id | company_id | publish_user_id | position_id | resume_type | status | create_time | update_time | +--------------------+--------+---------+------------+-----------------+-------------+-------------+--------+---------------------+---------------------+ | 488517027795828736 | | 32 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:15 | 2020-07-11 01:12:15 | | 488517028383031297 | | 92 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028416585728 | | 48 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028445945857 | | 52 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028542414849 | | 62 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028668243968 | | 8 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028701798401 | | 90 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028747935744 | | 50 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028777295873 | | 34 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028806656000 | | 90 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028836016129 | | 72 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028869570560 | | 2 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028936679424 | | 66 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029037342721 | | 94 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029112840193 | | 22 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029259640833 | | 6 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029293195264 | | 28 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029364498432 | | 86 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029523881984 | | 2 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029586796544 | | 40 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029666488320 | | 68 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029725208576 | | 54 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029788123136 | | 88 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029809094657 | | 28 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029838454784 | | 6 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517030073335809 | | 66 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517030215942144 | | 8 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | +--------------------+--------+---------+------------+-----------------+-------------+-------------+--------+---------------------+---------------------+ 27 rows in set (0.00 sec) ``` - slave0\slave1 同步 这里数据太多我直接展示统计的条数 这里细节展示见视频，因为本地贴图上传不显示 ```sql mysql> select count(1) from c_order; +----------+ | count(1) | +----------+ | 27 | +----------+ 1 row in set (0.03 sec) ``` ### master1插入情况 ```sql mysql> select * from c_order; +--------------------+--------+---------+------------+-----------------+-------------+-------------+--------+---------------------+---------------------+ | id | is_del | user_id | company_id | publish_user_id | position_id | resume_type | status | create_time | update_time | +--------------------+--------+---------+------------+-----------------+-------------+-------------+--------+---------------------+---------------------+ | 488517028483694592 | | 15 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028580163584 | | 23 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028617912321 | | 63 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028898930689 | | 99 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517028974428161 | | 79 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029003788288 | | 67 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029066702848 | | 67 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029142200320 | | 37 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029171560449 | | 27 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029205114880 | | 67 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029330944001 | | 39 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029398052865 | | 5 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029444190208 | | 31 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029481938945 | | 15 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029549047809 | | 77 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029624545281 | | 21 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029695848449 | | 9 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029758763009 | | 5 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029863620609 | | 17 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517029905563648 | | 61 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517030111084544 | | 95 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517030153027585 | | 69 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | | 488517030245302273 | | 59 | 11 | 10 | 1001 | 1 | 1 | 2020-07-11 01:12:16 | 2020-07-11 01:12:16 | +--------------------+--------+---------+------------+-----------------+-------------+-------------+--------+---------------------+---------------------+ 23 rows in set (0.00 sec) ``` - slave2\slave3 同步 ```sql mysql> select count(1) from c_order; +----------+ | count(1) | +----------+ | 23 | +----------+ 1 row in set (0.03 sec) ``` > 总结 两套主从环境数据和正好是50条，且按照我们的user_id 分片规则分片 ### 查询 ```java @Test public void query(){ List all = cOrderRepository.findAll(); all.forEach(x-> {System.out.println("id:"+x.getId()+"userId:"+x.getUserId() + "createTime："+x.getCreateTime());}); } ``` ### 结果 ```mysql 2020-07-11 01:26:46.782 INFO 27504 --- [ main] ShardingSphere-SQL : Actual SQL: slave0 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.update_time as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order corder0_ 2020-07-11 01:26:46.782 INFO 27504 --- [ main] ShardingSphere-SQL : Actual SQL: slave2 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.update_time as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order corder0_ id:488517027795828736userId:32createTime：2020-07-11 01:12:15.0 id:488517028383031297userId:92createTime：2020-07-11 01:12:16.0 id:488517028416585728userId:48createTime：2020-07-11 01:12:16.0 id:488517028445945857userId:52createTime：2020-07-11 01:12:16.0 id:488517028542414849userId:62createTime：2020-07-11 01:12:16.0 id:488517028668243968userId:8createTime：2020-07-11 01:12:16.0 id:488517028701798401userId:90createTime：2020-07-11 01:12:16.0 id:488517028747935744userId:50createTime：2020-07-11 01:12:16.0 id:488517028777295873userId:34createTime：2020-07-11 01:12:16.0 id:488517028806656000userId:90createTime：2020-07-11 01:12:16.0 id:488517028836016129userId:72createTime：2020-07-11 01:12:16.0 id:488517028869570560userId:2createTime：2020-07-11 01:12:16.0 id:488517028936679424userId:66createTime：2020-07-11 01:12:16.0 id:488517029037342721userId:94createTime：2020-07-11 01:12:16.0 id:488517029112840193userId:22createTime：2020-07-11 01:12:16.0 id:488517029259640833userId:6createTime：2020-07-11 01:12:16.0 id:488517029293195264userId:28createTime：2020-07-11 01:12:16.0 id:488517029364498432userId:86createTime：2020-07-11 01:12:16.0 id:488517029523881984userId:2createTime：2020-07-11 01:12:16.0 id:488517029586796544userId:40createTime：2020-07-11 01:12:16.0 id:488517029666488320userId:68createTime：2020-07-11 01:12:16.0 id:488517029725208576userId:54createTime：2020-07-11 01:12:16.0 id:488517029788123136userId:88createTime：2020-07-11 01:12:16.0 id:488517029809094657userId:28createTime：2020-07-11 01:12:16.0 id:488517029838454784userId:6createTime：2020-07-11 01:12:16.0 id:488517030073335809userId:66createTime：2020-07-11 01:12:16.0 id:488517030215942144userId:8createTime：2020-07-11 01:12:16.0 id:488517028483694592userId:15createTime：2020-07-11 01:12:16.0 id:488517028580163584userId:23createTime：2020-07-11 01:12:16.0 id:488517028617912321userId:63createTime：2020-07-11 01:12:16.0 id:488517028898930689userId:99createTime：2020-07-11 01:12:16.0 id:488517028974428161userId:79createTime：2020-07-11 01:12:16.0 id:488517029003788288userId:67createTime：2020-07-11 01:12:16.0 id:488517029066702848userId:67createTime：2020-07-11 01:12:16.0 id:488517029142200320userId:37createTime：2020-07-11 01:12:16.0 id:488517029171560449userId:27createTime：2020-07-11 01:12:16.0 id:488517029205114880userId:67createTime：2020-07-11 01:12:16.0 id:488517029330944001userId:39createTime：2020-07-11 01:12:16.0 id:488517029398052865userId:5createTime：2020-07-11 01:12:16.0 id:488517029444190208userId:31createTime：2020-07-11 01:12:16.0 id:488517029481938945userId:15createTime：2020-07-11 01:12:16.0 id:488517029549047809userId:77createTime：2020-07-11 01:12:16.0 id:488517029624545281userId:21createTime：2020-07-11 01:12:16.0 id:488517029695848449userId:9createTime：2020-07-11 01:12:16.0 id:488517029758763009userId:5createTime：2020-07-11 01:12:16.0 id:488517029863620609userId:17createTime：2020-07-11 01:12:16.0 id:488517029905563648userId:61createTime：2020-07-11 01:12:16.0 id:488517030111084544userId:95createTime：2020-07-11 01:12:16.0 id:488517030153027585userId:69createTime：2020-07-11 01:12:16.0 id:488517030245302273userId:59createTime：2020-07-11 01:12:16.0 ``` > 总结 通过查询打印信息看到，查询的结果是从 从库中获取 # 视频中查询讲错了，我以为是通过sql命令框查看从库数据，文档这里对查询做了补充。通过java查询，控制台信息贴出查询情况。