php 批量导入10万数据，且数据库匹配查重 # php 批量导入10万数据，且数据库匹配查重 前提：公司计划做10万张兑换券，发放给用户引流 功能在1个小时写完，上测试机（1C2U的垃圾服务器），生成10万个，直接内存溢出，我裂开。 开始优化。 ## 批量生成不重复的秘钥 自己写的批量生成秘钥 ```php $str = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; $a = 0; $secret_array = []; while ($a < 1000000) { $secret = ""; for ($i = 0; $i < 8; $i++) { $secret .= $str[mt_rand(0, strlen($str) - 1)]; } $secret_array[] = $secret; $a++; } array_unique($secret_array); ``` 网上找的（网上找的看不懂，但是结果是对的，看起来很牛逼） ```php $code = 'ABCDEFGHJKLMNPQRSTUVWXYZ'; $rand = $code[rand(0, strlen($code) - 1)] . strtoupper(dechex(date('m'))) . date('d') . substr(time(), -5) . substr(microtime(), 2, 5) . sprintf('%02d', rand(0, 99)); $a = md5($rand, true); $s = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ'; $d = ''; for ($f = 0; $f < 8; $f++) { $g = ord($a[$f]); $d .= $s[($g ^ ord($a[$f + 8])) - $g & 0x1F]; } ``` ## 查重 在数据库新增唯一索引，unique_secret（BTREE），保证插入的数据不会重复，当然，在数据入库前，要确保数据不会重复 1. 生成的秘钥需要去重，去重后如果不够数量，还需要补齐。 2. 数据库检索后发现重复的数据，也要去重，去重后同样需要补齐。 > 这里使用IN查询，通过explan发现（innoDB），IN查询是走索引的，所以效率还可以 上面阐述需要补齐，就要涉及到一个问题，递归。 递归后，需要把已经查重效验过的数据剔除，防止再次入库效验。 结果：将10万条数据，分成1万条每次，使用in查询效验。 生成数据 + 效验，最终耗时7秒多。 如果有更快的方式，欢迎讨论，邮箱：chenlisong1021@163.com 最终代码 ```php /** * 生成8位不重复随机秘钥 * 这里去除了0,1,I,O，防止用户难辨认 * @param array $data * @param int $num 生成个数 * @return array|mixed */ protected function make_coupon_card(&$data = [], $num = 100000, $has_check_data = []) { $code = 'ABCDEFGHJKLMNPQRSTUVWXYZ'; $rand = $code[rand(0, strlen($code) - 1)] . strtoupper(dechex(date('m'))) . date('d') . substr(time(), -5) . substr(microtime(), 2, 5) . sprintf('%02d', rand(0, 99)); $a = md5($rand, true); $s = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ'; $d = ''; for ($f = 0; $f < 8; $f++) { $g = ord($a[$f]); $d .= $s[($g ^ ord($a[$f + 8])) - $g & 0x1F]; } //将值作为key，防止数组重复 $data[$d] = $d; if (count($data) < $num) { $this->make_coupon_card($data, $num); } else { //去数据库检索值是否重复 $chunk_data = (new Collection(array_diff($data, $has_check_data)))->chunk(10000)->toArray(); foreach ($chunk_data as $key => $value) { $secrets = Db::name('red_packet')->whereIn('secret', $value)->cache(true)->column('secret'); foreach ($secrets as $item) { unset($data[$item]); } } if (count($data) !== $num) { //存储已经效验过的数据，无需二次效验 $has_check_data = array_merge($has_check_data, $data); $this->make_coupon_card($data, $num, $has_check_data); } } return $data; } ``` ## 导入10万条数据到数据库 常规的优化包含使用insert table xxx values (),()... 经过发现，使用此方式容易内存溢出，且效率也很低下，当然，肯定比一条一条插入快。 最终方案采用load data 方案。 ```php $now = Carbon::now()->toDateTimeString(); foreach ($secrets as $secret) { $max_sn++; $detail_data .= $model->id . "\t" . sprintf("%.2f", $max_sn) . "\t" . $secret . "\t" . "-1" . "\t" . $now . "\t" . $now . "\n"; } //使用LOAD DATA方式导入数据，将数据保存为txt文件 $load_data_path = $this->writeTxt($detail_data, $batch_no . "_insert"); $load_data_path = Config::get('app_file_path') . $load_data_path; $table = model('red_packet')->getTable(); $sql = "load data local infile '$load_data_path' into table $table (batch_id,sn,secret,status,create_time,update_time) "; $connect = array_merge(config('database.'), [ 'params' => [ PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_EMULATE_PREPARES => true, PDO::MYSQL_ATTR_LOCAL_INFILE => true ] ]); $rs = Db::connect($connect)->execute($sql); ``` 这里保存原始秘钥与插入信息到txt文件 ```php /** * 写入到TXT文件 */ protected function writeTxt($data, $file = 'txt') { //设置路径目录信息 $file_name = '/data/red_card/' . $file . '.txt'; $url = Config::get('app_file_path') . $file_name; $dir_name = dirname($url); //目录不存在就创建 if (!file_exists($dir_name)) { //iconv防止中文名乱码 $res = mkdir(iconv("UTF-8", "GBK", $dir_name), 0777, true); } file_put_contents($url, $data); return $file_name; } ``` 经过测试，导入10万条数据，大约耗时3秒。 至此，一个小小的功能，花费了1天的时间才写完。