Maison > Article > développement back-end > 1亿条数据在PHP中实现Mysql数据库分表100张,mysql100张_PHP教程
当数据量猛增的时候,大家都会选择库表散列等等方式去优化数据读写速度。笔者做了一个简单的尝试,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数据了。