浅析MySQL分库分表

为什么分表

由于业务发展，单表的数据量激增，单库单表无法承载整体的数据存储时，采取的一种将整体数据分散存储到不同服务器上的不同数据库中的不同数据表的存储方案。这样可以缓解数据存储的压力。

分表方式

• 水平分表
• 垂直分表

垂直分表的策略

• 垂直拆分理念：
  对数据库竖着拆分，将数据库的表拆分到多个不同的数据库中。
• 垂直拆分表的策略：
  依赖业务类型拆分，核心思想是专库专用，将业务耦合度比较高的表拆分到单独的库中去。

水平分表的策略

• 水平拆分表的策略：
  主要是将单一数据表按照某一种规则拆分到多个数据库和多个数据表中，关注点在数据的特点上。
• 水平拆分方式：
  基因法、某个字段区间。

某个字段区间拆分实现

• 拆分思路：如可以根据热点数据时间段、历史数据的时间段、记录创建时间段等业务要求来进行水平拆分，拆分的单位也可以为年份、季度、月度、日期等，那么分片键就是时间。

• 举例：想要将一张千万级大表bus_detail拆分，根据业务流水创建时间切分后bus_detail2022、bus_detail2023、bus_detail2024。

• 实现：
  1.编写路由规则：传入时间分片键，根据时间查询所在的表，然后定位表之后去查询。至于查找数据所在的表，第一种方式我们可以使用sql标签，第二种方式也可以传入动态的表名（可能存在sql注入的风险）。

<sql id="routeTable">
	<if test="year != null and year != ''">
		<if test="year.equals('2022'.toString)">
			bus_detail2022
		</if>
		<if test="year.equals('2023'.toString)">
			bus_detail2023
		</if>
		<if test="year.equals('2024'.toString)">
			bus_detail2024
		</if>
	</if>
</sql>

<select id="findDataByYeatr">
	select * from <include refid="routeTable" />
	<where>
		....
	</where>
</select>

2.搜集整合数据：将分散在不同分片表的数据查询出来，然后整合起来。第一种方式我们可以使用union关键字将在同一个库的多张分片表的数据整合到一起。第二种方式我们可以使用java中的addAll方法将一个List加入到另一个List中。

select * from bus_detail2022
union
select * from bus_detail2023

基因法拆分实现

• 拆分思路：按照某一个字段的hash值或者crc32值做拆分，这种拆分规则适用于实体表，比如说用户信息表、内容表，一般按照这些实体表的ID字段来拆分，而我们的hash和crc32的操作都是为了将ID字段打散，取余数就得到了所在的表。
• 举例：将用户表sys_user按照hash值求余数拆分成64个不同的子表，sys_user1、sys_user2、sys_user3...
• 实现：

1.编写路由规则

public static void main(String[] args) {
	// hash
	String str = "sunpy123";
	int hashValue = str.hashCode() & Integer.MAX_VALUE;
	System.out.println(hashValue % 64);
	// CRC32
	CRC32 crc32 = new CRC32();
	crc32.update(str.getBytes(StandardCharsets.UTF_8));
	long crc32Value = crc32.getValue();
	System.out.println(crc32Value % 64);
}

结果：
61
37

<sql id="routeTable">
	<if test="partValue != null and partValue != ''">
		<if test="partValue.equals('1'.toString)">
			sys_user1
		</if>
		<if test="partValue.equals('2'.toString)">
			sys_user2
		</if>
		<if test="partValue.equals('3'.toString)">
			sys_user3
		</if>
	</if>
</sql>

2.数据整合

select * from sys_user1
union
select * from sys_user2

水平分表产生的问题

1. 业务复杂度增大并且对业务侵入性较大。
2. 跨表跨库join查询难。
3. 数据库的一些特性无法使用。
4. 必须使用分片键查询。
5. 需要将拆分的表提前建立。

水平分表问题的解决方案

1. 针对一些条件查询没有分片键的情况，譬如根据id分片，实际上想根据年龄范围条件查询。可以设计一个id和年龄的映射关系表，该表也可以通过分表来实现。我们先去查询年龄，再通过年龄去查询id，根据id值在查询所在的分表，再查询。
2. 跨库进行join和count的问题，那么我们可以将数据都查询到jvm内存中整合。
3. 拆分表的创建，我们可以提前创建，也可以使用定时器，自动去创建。

参考

https://www.163.com/dy/article/HUOK160205316FM3.html