MySQL分库分表的原则

  • 2020 年 7 月 11 日
  • 笔记

一、分表

　　当一个表的数据达到几千万条的时候，每一次查询都会花费更长的时间，如果这时候在使用链表查询，那么我想应该会实在那里，那么我们应该如何解决这个问题呢？

　　1、为什么要分表：

　　　　分表的目的就是为了解决当数据量太大的时候，数据查询变慢的问题，那么我们应该如何去解决这个问题呢？这时候就用到了分表。

　　　　那么什么是分表呢？
　　　　a.根据业务逻辑进行纵向拆分（按照数据库字段的必要性进行拆分）

　　　　b.根据数据库数据量级进行拆分，一般拆分是在几百万这个量级上，因为超过千万条数据库查询就会明显变慢，具体如何拆分可以根据自己数据库服务器的实际情况进行测试和分析后决定。

　　2、分表的逻辑是什么？

　　　　分表方法一：预先估计出要频繁插入的表，这样的表之后大概率会是一个数据量非常大的表，那么我们就可以预先分出n个表来存储数据，存储数据时可以根据一定的规则判断存储到那张表，读取时也是使用相同的规则进行判断。如可以使取模的方式，也可使用hash的方式进行判断。

　　取模方式：
      function hash_table($table_name, $user_id, $total){

         return $table_name . '_' . (($user_id % $total) + 1);

      }
　　hash方式：　

　　　　function get_hash_table($table,$userid) { 

 　　　　　　$str = crc32($userid); 

　　　　　　 if($str<0){ 

 　　　　　　　　$hash = “0”.substr(abs($str), 0, 1); 

 　　　　　　}else{ 

 　　　　　　　　$hash = substr($str, 0, 2); 

 　　　　　　} 

 　　　　　　return $table.”_”.$hash; 

　　　　} 

　　　　查询数据也可以使用上面的逻辑，先判断出要查询的数据位于哪些表中，然后在进行查询

　　　　该方法一旦形成表数据，那么想要改变规则非常困难，那将是一个梦魇，所以扩展性极差

　　　　分表方法二：利用merge存储引擎来实现分表
　　　　　　merge引擎是就是一组MyISAM表的组合，类似于视图，merge表本身不存储任何数据，你对merge表的操作，实际上是对其字表的操作，但是这个对于程序来说是透明的，在插入的时候默认插入到第一张或最后一张的字表中，这个可以通过merge创建时进行设置。如果子表是预先建立的那么你也可以选择在插入的时候按照一定的规则，这几对子表进行插入。

　　　　实例：
　　　　　　分表1：

                     CREATE TABLE `AuthTrace1` (
                             `Id` int(11) NOT NULL AUTO_INCREMENT,
                             `AuthId` char(255) DEFAULT NULL,
                             `Addtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘添加记录的当前时间’,
                             `UserIp` varchar(255) DEFAULT NULL COMMENT ‘用户Ip’,
                             `Date` date DEFAULT NULL COMMENT ‘yyyyMMdd格式’,
                             `ExtraInfo` varchar(255) DEFAULT NULL COMMENT ‘额外信息’,
                             `UserMAC` varchar(255) DEFAULT NULL COMMENT ‘mac地址’,
                             PRIMARY KEY (`Id`)
                     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

　　　　　　分表2：

                     CREATE TABLE `AuthTrace1` (
                             `Id` int(11) NOT NULL AUTO_INCREMENT,
                             `AuthId` char(255) DEFAULT NULL,
                             `Addtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘添加记录的当前时间’,
                             `UserIp` varchar(255) DEFAULT NULL COMMENT ‘用户Ip’,
                             `Date` date DEFAULT NULL COMMENT ‘yyyyMMdd格式’,
                             `ExtraInfo` varchar(255) DEFAULT NULL COMMENT ‘额外信息’,
                             `UserMAC` varchar(255) DEFAULT NULL COMMENT ‘mac地址’,
                             PRIMARY KEY (`Id`)
                     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

　　　　Merge表：

      CREATE TABLE `AuthTrace_Merge` (
          `Id` int(11) NOT NULL AUTO_INCREMENT,
          `AuthId` char(255) DEFAULT NULL,
          `Addtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '添加记录的当前时间',
          `UserIp` varchar(255) DEFAULT NULL COMMENT '用户Ip',
          `Date` date DEFAULT NULL COMMENT 'yyyyMMdd格式',
          `ExtraInfo` varchar(255) DEFAULT NULL COMMENT '额外信息',
          `UserMAC` varchar(255) DEFAULT NULL COMMENT 'mac地址',
          PRIMARY KEY (`Id`)
      )ENGINE=MERGE DEFAULT CHARSET=utf8 INSERT_METHOD=LAST AUTO_INCREMENT=1 UNION=(`AuthTrace1`,`AuthTrace2`);

              INSERT_METHOD：表示插入方式；

              INSERT_METHOD=LAST 添加数据的时候插入到最后一个表，这里就是t2

              INSERT_METHOD=FIRST 添加数据的时候插入到第一个表，这里就是t1

              INSERT_METHOD=0 不允许插入

              UNION = (`AuthTrace1`,`AuthTrace2`) 关联AuthTrace1和AuthTrace2表 

              Merge表 数据结构必须和分表完全一致

　　       merge存在的问题：数据插入式无法保证数据id的不重复

 当然分表还有其他方式很多种方式和逻辑，我在这里做个抛砖引玉，大家有什么别的方法可以留言

VirMach 便宜 VPS

QNews

QNews