MySql水平分库案例

思路:在大型电商系统中，每天的会员人数不断的增加。达到一定瓶颈后如何优化查询。
可能大家会想到索引，万一用户量达到上亿级别，如何进行优化呢？
答：使用水平分割拆分数据库表。

使用水平分割拆分表，具体根据业务需求，有的按照注册时间、取摸、账号规则、年份等。

首先我创建三张表 user0 / user1 /user2 , 然后我再创建 uuid表，该表的作用就是提供自增的id。

create table user0(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd  varchar(32) not null default '')
engine=myisam charset utf8;

create table user1(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd  varchar(32) not null default '')
engine=myisam charset utf8;

create table user2(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd  varchar(32) not null default '')
engine=myisam charset utf8;


create table uuid(
id int unsigned primary key auto_increment)engine=myisam charset utf8;

创建一个demo项目

POM文件

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.3.3.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
	</dependencies>

Service代码

@Service
public class UserService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public String regit(String name, String pwd) {
		// 1.先获取到 自定增长ID
		String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
		jdbcTemplate.update(idInsertSQL);
		Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		// 2.判断存储表名称
		String tableName = "user" + insertId % 3;
		// 3.注册数据
		String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
				+ "');";
		System.out.println("insertUserSql:" + insertUserSql);
		jdbcTemplate.update(insertUserSql);
		return "success";
	}

	public String get(Long id) {
		String tableName = "user" + id % 3;
		String sql = "select name from " + tableName + "  where id="+id;
		System.out.println("SQL:" + sql);
		String name = jdbcTemplate.queryForObject(sql, String.class);
		return name;
	}

}

Controller

@RestController
public class UserController {
	@Autowired
	private UserService userService;

	@RequestMapping("/regit")
	public String regit(String name, String pwd) {
		return userService.regit(name, pwd);
	}

	@RequestMapping("/get")
	public String get(Long id) {
		String name = userService.get(id);
		return name;
	}

}