一、mysql分表简单介绍

 

一、Mysql分表的原因

 

1、当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。

分表的目的就在于此，减小数据库的负担，缩短查询时间。

2、mysql中有一种机制是表锁定和行锁定，为什么要出现这种机制，是为了保证数据的完整性，

我举个例子来说吧，如果有二个sql都要修改同一张表的同一条数据，这个时候怎么办呢，是不是二个sql都可以同时修改这条数据呢？

很显然mysql对这种情况的处理是，一种是表锁定（myisam存储引擎），一个是行锁定（innodb存储引擎）。

表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。

如果数据太多，一次执行的时间太长，等待的时间就越长，这也是我们为什么要分表的原因。

 

二、分表的规则

这里只讨论最简单的分表规则取模

假如我们需要把用户表分为100张表 

user进行水平的切分，产生两个表结构完全一样的user_1,user_2等表，user_1 + user_2 + …的数据刚好是一份完整的数据。 

 

1）我们采用最简单的分表方案  取模

我们根据用户的id来模100

比如 用户id为1000156    模上100  为56  那添加数据或者读取数据的时候  都是在user_56这个表操作

 

下面是分表的脚本

#!/bin/sh
 
# dev | idc
dbenv=dev
 
if [ "${dbenv}" = "dev" ]; then
    mysql_user=root
    mysql_pass=root
    mysql_host=
fi
if [ "${dbenv}" = "idc" ]; then 
    mysql_user=root
    mysql_pass=root
    mysql_host="-h 10.10.10.10"
fi
 
mysql_cmd="mysql -u${mysql_user} -p${mysql_pass} ${mysql_host} --default-character-set=utf8"
 
for i in {1..100}
do
    ${mysql_cmd} <<EOF
 
    use md_mydatabasel;
 
    create table t_user_$i
    (
        f_uin bigint(20) NOT NULL DEFAULT '0',
        f_name varchar(256) NOT NULL DEFAULT '',
        PRIMARY KEY (f_uin)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
EOF
 
done

 

下面是删除表的脚本

 

#!/bin/sh
 
# dev | idc
dbenv=dev
 
if [ "${dbenv}" = "dev" ]; then
    mysql_user=root
    mysql_pass=root
    mysql_host=
fi
if [ "${dbenv}" = "idc" ]; then 
    mysql_user=midea
    mysql_pass=MD_midea
    mysql_host="-h 10.10.10.10"
fi
 
mysql_cmd="mysql -u${mysql_user} -p${mysql_pass} ${mysql_host} --default-character-set=utf8"
 
for i in {1..100}
do
    ${mysql_cmd} <<EOF
 
    use md_mydatabasel;
     
    drop table t_user_$i;
 
EOF
done

 

 

2）每张表需要保证 用户的id为全局唯一的

有个简单的解决方案是  

在数据库里面 建立一个表  只有一个字段 是专门用来取号码的

即每次  需要创建一个新用户，则从这个表里面取一个号码 同时让这个号码增加一个

这个来保证每个用户的id都是全局唯一的

CREATE TABLE t_global_number (
  f_global_id bigint(20) NOT NULL,
  PRIMARY KEY (f_global_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

3)关于数据读取

我们尽量让请求不要直接去查询数据库

我们可以每次查到数据就放到redis的缓存里面。

如果数据没改变则一直用缓存

一旦数据改变了就 删除缓存

这样可以减轻数据库的查询负担

 

 

 

三、分库分表产生的问题，及注意事项 
1.   分库分表维度的问题 
假如用户购买了商品,需要将交易记录保存取来，如果按照用户的纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的购买情况，但是某商品被购买的情况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找到买人的交易记录比较麻烦。 

所以常见的解决方式有： 
     a.通过扫表的方式解决，此方法基本不可能，效率太低了。 
     b.记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。 
     c.通过搜索引擎解决，但如果实时性要求很高，又得关系到实时搜索。 

2.   联合查询的问题 
联合查询基本不可能，因为关联的表有可能不在同一数据库中。 

3.   避免跨库事务 
避免在一个事务中修改db0中的表的时候同时修改db1中的表，一个是操作起来更复杂，效率也会有一定影响。 

4.   尽量把同一组数据放到同一DB服务器上 
例如将卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据。