app后端设计-- 数据库分表

 当项目上线后，随着用户的增长，有些数据表的规模会以几何级增长，当数据达到一定规模的时候（例如100万条），查询，读取性能就下降得很厉害，这时，我们就要考虑分表。

 

   更新表数据时会导致索引更新，当单表数据量很大时这个过程比较耗时，这就是为什么对大表进行新增操作会比较慢的原因，并且更新表数据会进行表级锁或者行锁，这样就导致其他操作等待。

 

    所以我们将大表拆分为多个子表，那么在更新或者查询数据的时候，压力会分散到不同的表上。由于分表之后每个表的数据较小，不管是查询还是更新都极大的提高了速度，即使出现最坏的“锁表”的情况，那其他表还是可以并行使用。

 

1.分表的策略

 

分表有多种策略：

 

(1)按用户id分表，例如id为1-10000在表1，id为10001-20000在表2

(2)插入的时间分表

(3)按每个表固定记录行数拆分

 

    在项目，由于这个表是保存用户的通讯录，为了保证一个用户的所有通讯录数据都保存在同一个表，选择的分表方式就是（1），按用户id分表。

 

2. 分表策略确定下来了，还有一个非常严重的问题，因为现在用户的数据都分散在不同的表中，之前的业务功能如何保证呢？比如说我要插入一条记录、更新一条记录、删除一条记录、查询统计数据，现在要怎么处理呢?

 

    如果分表的存储引擎是MyISAM，这里有一种很简单的处理方法。利用merge存储引擎将拆分的表合并成一张表。当然了，如果使用InnoDB，也能通过alter table命令把InnoDB变为MyISAM。

 

     MERGE存储引擎可以将N个子表联合在一起,看成是一个整表,实际上还是N个真实的子表。

 

    当分表的时候，还要一个问题，因为我们是在线上项目中分表的，需要考虑怎么样使分表的操作对用户的影响最少。

 

3. 一个例子

 

    假设有表contact，存储了9000个用户的通讯录数据，平均一个用户有联系人100个，那么这个表的规模就达到了90万条数据，我们需要对这个表分表。

 

    下面的脚本演示了怎么在不关闭mysql服务的情况下对contact 分表

[sql]  view plain copy

1. -- ----------------------------  
2. -- 创建根据原来的表格式创建一个临时表，并把存储引擎改为MYISAM  
3. -- ---------------------------  
4. CREATE TABLE contact_temp LIKE contact;  
5. ALTER TABLE contact_temp ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci ;   
6.   
7. -- ----------------------------  
8. -- 创建分表contact_temp1，contact_temp2  
9. -- ---------------------------  
10. CREATE TABLE contact_temp1 LIKE contact_temp;  
11. CREATE TABLE contact_temp2 LIKE contact_temp;  
12.   
13. -- ----------------------------  
14. -- 按用户id分表，把id<5000 保存在表1，id>=5000 and id<10000 保存在表2，  
15. -- ----------------------------  
16. INSERT INTO contact_temp1 SELECT * FROM contact where uid<5000;  
17. INSERT INTO contact_temp2 SELECT * FROM contact where uid>=5000 and uid<10000;  
18.   
19.   
20. -- ----------------------------  
21. -- 把原来的表改名，因为在mysql中不能有重复的表明，这样子最终创建的MERGE引擎的表就能使用原来的表名  
22. -- ----------------------------  
23. RENAME TABLE contact TO contact_bak;  
24. CREATE TABLE contact LIKE contact_temp;  
25.   
26. -- ----------------------------  
27. -- 先把原来删除表上的主键的自增属性去掉，再删除主键  
28. -- ----------------------------  
29. ALTER TABLE contact CHANGE `id` `id`  int(11);  
30. ALTER TABLE contact DROP PRIMARY KEY;  
31.   
32. -- ----------------------------  
33. -- 把表的存储引擎改为MERGE  
34. -- ----------------------------  
35. ALTER TABLE contact  ENGINE=MERGE UNION=(contact_temp1,contact_temp2) INSERT_METHOD=LAST;  
36.   
37.   
38. -- ----------------------------  
39. -- 删除所有的临时表  
40. -- ----------------------------  
41. drop TABLE contact_bak;  
42. drop TABLE contact_temp;