今天突然想试试看亿级会员单表的性能,分三步,首先创建表,接着生成会员数据,最后导入既可。注意如果有索引必须先创建好,不然等你导入后再创建就。。。
还有记得要表要用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个小时多几分钟, 可见索引创建在大表里面多难。
其它包括索引,查询大家都可以自己调整试试看效率如何。
接下来如何优化呢?