首页 >后端开发 >php教程 >1亿条数据在PHP中实现Mysql数据库分表100张,mysql100张_PHP教程

1亿条数据在PHP中实现Mysql数据库分表100张,mysql100张_PHP教程

WBOY
WBOY原创
2016-07-13 09:45:37938浏览

1亿条数据在PHP中实现Mysql数据库分表100张,mysql100张

当数据量猛增的时候,大家都会选择库表散列等等方式去优化数据读写速度。笔者做了一个简单的尝试,1亿条数据,分100张表。具体实现过程如下:

首先创建100张表:

<span><span> 1</span> <span>$i=0;
</span><span> 2</span> while($i<span><</span><span>=99</span><span>){
</span><span> 3</span> <span>echo "$newNumber \r\n";
</span><span> 4</span> <span>$sql</span><span>="CREATE TABLE `code_"</span><span>.$i."` (
</span><span> 5</span> <span> `full_code` char(10) NOT NULL,
</span><span> 6</span> <span> `create_time` int(10) unsigned NOT NULL,
</span><span> 7</span> <span> PRIMARY KEY  (`full_code`),
</span><span> 8</span> <span>) ENGINE</span><span>=MyISAM </span><span>DEFAULT CHARSET</span><span>=utf8";
</span><span> 9</span> <span>mysql_query($sql);
</span><span>10</span> <span>$i++; </span></span>

下面说一下我的分表规则,full_code作为主键,我们对full_code做hash

函数如下:

<span><span>1</span> <span>$table_name=get_hash_table('code',$full_code);
</span><span>2</span> <span>function get_hash_table($table,$code,$s=100){
</span><span>3</span> <span>$hash = sprintf("%u", crc32($code));
</span><span>4</span> <span>echo $hash;
</span><span>5</span> <span>$hash1 = intval(fmod($hash, $s));
</span><span>6</span> <span> return $table."_".$hash1;
</span><span>7</span> } </span>

这样插入数据前通过get_hash_table获取数据存放的表名。

最后我们使用merge存储引擎来实现一张完整的code表

<span><span>1</span> <span>CREATE TABLE IF NOT EXISTS `code` (  
</span><span>2</span> <span>`full_code` char(10) NOT NULL,
</span><span>3</span> <span>`create_time` int(10) unsigned NOT NULL,
</span><span>4</span> <span>INDEX(full_code)  
</span><span>5</span> ) TYPE=MERGE UNION=(code_0,code_1,code_2.......) INSERT_METHOD=LAST ; </span>

这样我们通过select * from code就可以得到所有的full_code数据了。

 

www.bkjia.comtruehttp://www.bkjia.com/PHPjc/1040159.htmlTechArticle1亿条数据在PHP中实现Mysql数据库分表100张,mysql100张 当数据量猛增的时候,大家都会选择库表散列等等方式去优化数据读写速度。笔者做了...
声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn