今天突然想试试看亿级会员单表的性能,分三步,首先创建表,接着生成会员数据,最后导入既可。注意如果有索引必须先创建好,不然等你导入后再创建就。。。
还有记得要表要用MyISAM,如果是innodb应该超过24小时都完成不了。配置缓存参数也要调高,例如以下:
[mysqld]
myisam_max_sort_file_size = 30G
myisam_sort_buffer_size = 2048M
首先创建表结构,实际内容你们随便改了。
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '会员ID',
`username` varchar(20) DEFAULT NULL COMMENT '用户名',
`nickname` varchar(20) DEFAULT NULL COMMENT '会员呢称',
`password` varchar(255) DEFAULT NULL COMMENT '用户密码',
`mobile` varchar(11) DEFAULT NULL COMMENT '手机号码',
`open_id` char(32) DEFAULT NULL COMMENT '微信公众号唯一ID',
`price` int(11) DEFAULT '1' COMMENT '存款',
`status` smallint(1) NOT NULL DEFAULT '1' COMMENT '用户状态:0 - 未启用;1 - 已启用',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `create_time` (`create_time`),
KEY `price` (`price`) USING BTREE,
KEY `username` (`username`),
KEY `mobile` (`mobile`),
KEY `open_id` (`open_id`),
KEY `nickname` (`nickname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='会员表';
接着用php循环生成模拟会员数据,代码:
ini_set('display_errors',1); //错误信息
ini_set('display_startup_errors',1); //php启动错误信息
error_reporting(-1); //打印出所有的 错误信息
set_time_limit(0);//防止超时
ini_set('memory_limit',"2048M");//怕内存太小
$strs="QWERTYUIOPASDFGHJKLZXCVBNM1234567890qwertyuiopasdfghjklzxcvbnm";//用于生成随机数
for ($j = 1;$j <= 50; $j++) {//循环50次一亿条
$str='';
for ($i = 1;$i <= 2000000; $i++) {//200万写入文件1次
$str.=substr(str_shuffle($strs),mt_rand(0,strlen($strs)-7),6)."\t".getChar(2)."\t".substr(str_shuffle($strs),mt_rand(0,strlen($strs)-9),8)."\t". mt_rand(100000,199999).mt_rand(10000,99999)."\t".md5(uniqid(microtime(true),true))."\t".mt_rand(10000,99999)."\t".mt_rand(0,1)."\t".date("Y-m-d H:i:s", mt_rand(946659661,1551690447))."\n";
}
file_put_contents('member.sql', $str,FILE_APPEND);
}
echo ok;
function getChar($num) // $num为生成汉字的数量
{
$b = '';
for ($i=0; $i<$num; $i++) {
// 使用chr()函数拼接双字节汉字,前一个chr()为高位字节,后一个为低位字节
$a = chr(mt_rand(0xB0,0xD0)).chr(mt_rand(0xA1, 0xF0));
// 转码
$b .= iconv('GB2312', 'UTF-8', $a);
}
return $b;
}
我生成一亿条数据大概10g,接下来就要快速插入数据了。
这里选择load data infile方式,命令如下:
use test;
load data infile '/data/member.sql' into table member(username,nickname,password,mobile,open_id,price,status,create_time);
最后就等待结果即可,2亿条导入时间大概需要21分钟,之后创建索引大概花了1个小时多几分钟, 可见索引创建在大表里面多难。
其它包括索引,查询大家都可以自己调整试试看效率如何。
接下来如何优化呢?