数据库优化方案
1、SQL 索引
2、表与存储引擎
3、架构->主从同步,读写分离->分片
4、数据库配置
5、操作系统
分库分表
垂直分表,大表拆小表
水平分表,表结构不变,数据分表
分库分表带来的问题
跨库关联查询
分布式事务
排序、翻页、函数计算问题
全局主键避重问题
多数据源/动态数据源的解决方案
1、客户端DAO 层 利用AbstractRoutingDataSource
2、ORM 框架层 例如mybatis拦截器,可以插件通过设置Statement 的Connection,或者使用不同的
SqlSessionFactory 实现
3、驱动层 封装JDBC,自己实现一个DataSource,例如 Sharding-JDBC
4、代理层 例如:Mycat 和Sharding-Proxy
5、数据库服务 例如 redis Cluster
mycat
核心概念
mycat配置
直接解压后再conf目录下
主要配置三个文件
schema.xml
rule.xml
server.xml
先配server.xml
需要对哪些数据库分表
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">imall,gupao</property>
</user>
schema.xml
配置具体的数据库实例,表,如何分片
<schema name="imall" checkSQLschema="false" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table name="customer" primaryKey="id" dataNode="137-imall,138-imall,139-imall" rule="auto-sharding-long" />
<table name="order_info" dataNode="137-imall,138-imall,139-imall" rule="mod-long-order" >
<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="order_id"/>
</table>
<table name="mycat_sequence" dataNode="137-imall" autoIncrement="true" primaryKey="id"></table>
</schema>
<schema name="gupao" checkSQLschema="false" sqlMaxLimit="100">
<table name="student" primaryKey="sid" dataNode="137-gupao,138-gupao,139-gupao" rule="mod-long" />
<table name="noshard" primaryKey="id" autoIncrement="true" dataNode="137-gupao" />
<table name="dict" primaryKey="id" type="global" dataNode="137-gupao,138-gupao,139-gupao" />
<table name="fee" primaryKey="id" subTables="fee2025$1-3" dataNode="137-gupao" rule="sharding-by-month" />
</schema>
<dataNode name="137-imall" dataHost="host137" database="imall" />
<dataNode name="138-imall" dataHost="host138" database="imall" />
<dataNode name="139-imall" dataHost="host139" database="imall" />
<dataNode name="137-gupao" dataHost="host137" database="gupao" />
<dataNode name="138-gupao" dataHost="host138" database="gupao" />
<dataNode name="139-gupao" dataHost="host139" database="gupao" />
<dataHost name="host137" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.243.137:3306" user="root"
password="123456">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="host138" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.243.138:3306" user="root"
password="123456">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="host139" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.243.139:3306" user="root"
password="123456">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
rule.xml
主要是分片规则,再调用具体的规则配置文件,如 mod-long
<tableRule name="mod-long">
<rule>
<columns>sid</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
mycat 使用
启动mycat
./mycat start
./mycat status
./mycat stop
创建mycat连接,其他都一样,就是端口是8066
mycat验证
上面配置了schema.xml
customer是按范围分片
student 是按取模分片
order_info,order_detail 是取模分片(ER表)
noshard 是不分片
dict 是全局表
fee 是按月分表
按范围分片
根据配置找到autopartition-long.txt
0-10000=0
10001-20000=1
20001-30000=2
根据id范围分别落在哪个分片上
取模分片
根据配置的mod-long 找到rule.xml,有几个节点,这里就是几
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
取模分片(ER表)
存在主外键关系的数据会根据这个配置,存放在同一数据库,
order_info的一条记录落在哪个节点,order_detail 的数据就插在哪个节点,根据order_id关联
全局表
非分片表
库内分表
按照月份分表,这里是2025年1-3月份,得先创建三张表分别是fee20251、fee20252、fee20253
INSERT INTO `fee` (`id`, `create_time`) VALUES (1, '2025-1-1 14:46:19');
INSERT INTO `fee` (`id`, `create_time`) VALUES (2, '2025-2-1 14:46:19');
INSERT INTO `fee` (`id`, `create_time`) VALUES (3, '2025-3-1 14:46:19');
三条记录,每张表一条
全局ID
server.xml中
< property name="sequnceHandlerType">0</ property>配置
0 文件1 数据库2 本地时间戳3 ZK
文件方式 sequence_conf.properties
数据库方式 sequence_db_conf.properties
本地时间戳 sequence_time_conf.properties
ZK方式
table 标签上配置autoIncrement="true",自动生成id