PHP写mysql测试数据代码

用途，每刷新页面一次就写入X万数据，用于随机写入数据用于查询等性能分析测试

本想写五亿条数据，结果1200万数据的时候CPU突然狂飙 

写入的时候有记录写入耗时，读取统计耗时等用于性能变化分析。

<?php
error_reporting(0); //打开报错：去行首双斜杠

//功能：访问一次写入一次X万行数据：ID+学籍号+多科目成绩+计算总分
//记录日志供性能变化分析，1H2G服务器写到千万数据就突然CPU狂飙

$webtitle = "某某市中考查询系统演示页面";	//网站标题
$tiaojian = "学籍号";	//查询条件填列标题
$lie1 = "语文+数学+英语+化学+物理";	//数字字段|科目|不含ID、条件、总分等
$pnum = "50000"; //每批次写入数量，推荐:10000-100000
$biaoge = "chalide";	//表
$dar = date("Ymd_His");		//日志用时间

//数据库连接信息
$dbhost = "localhost";	//数据库地址本地localhost
$dbuser = "wuyitest";	//数据库账号
$dbpass = "sFi4cRkT";	//数据库密码
$dbname = "wuyitest";	//数据库名称
$dbport = "3306";		//数据库端口号
$dbcode = "UTF8";		//数据库编码  UTF8 GB2312

/*------没有把握不要修改后面代码------*/
$tlie = explode("+",$lie1); $lies = count($tlie); $lti = join("`,`",$tlie);

//用于数据统一首尾空格和大小写
function dako($txt) {
//$txt = strtoupper($txt);
$txt = Trim($txt); return addslashes($txt);
}
function rafs($nb) {
$a=[]; for($ii=0;$ii<$nb;$ii++){ $a[]=rand(55,99);}
$a[] = array_sum($a); return $a;
}
function txtarea($nb,$ht="30vh") {
 return "\r\n\r\n<textarea style='display:block; height:$ht;width:80vw;'>$nb</textarea>";
}

$stime=microtime(true);
//mysqli_report(MYSQLI_REPORT_OFF);
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport);
if (!$conn){ die("errq1"); }
//生产表结构
$dsr = "";
foreach($tlie as $vv){ $dsr .= "  `$vv` varchar(64) DEFAULT NULL,\r\n"; }
$dsr = Trim($dsr);
$sqa = "CREATE TABLE IF NOT EXISTS `$biaoge` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `$tiaojian` varchar(12) DEFAULT NULL,
  $dsr
  `总分` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

//得到数据
$sqb = "INSERT INTO `$biaoge` (`$tiaojian`, `$lti`, `总分`) VALUES ";
$sqb2 = [];
    for($ii=0;$ii<$pnum;$ii++){ //批数量
        $xjh = rand(10000000000,99999999999); $cj1=rafs($lies); $cjz=join("', '",$cj1);
        $sqb2[] = "('$xjh', '$cjz')";
    }
$sqb .= join(",",$sqb2).";";

//索引函数，推荐数据完毕后phpmyadmin手动mysql语句
$sqc = "ALTER TABLE `$biaoge` ADD INDEX `$tiaojian` (`$tiaojian`);"; //最后操作他

echo txtarea($sqa);
echo txtarea($sqb);
echo txtarea($sqc,"30px");

//执行写表结构
$resu = mysqli_query($conn, $sqa); //
if (!$resu){ $okecha = mysqli_error($conn);}else{ $okecha = "OK";}
//执行写数据
$resv = mysqli_query($conn, $sqb); //
if (!$resv){ $okecho = mysqli_error($conn);}else{ $okecho = mysqli_affected_rows($conn);}

$etime=microtime(true); $to1=$etime-$stime; //求时间

//计算总书记量
$sqe = "select COUNT(`id`) AS count from `$biaoge` ";
$resw = mysqli_query($conn, $sqe);
if (!$resw){ $recs = "0";} else{ $rowx = mysqli_fetch_assoc($resw); $recs = $rowx['count']; }

$otime=microtime(true); $to2=$otime-$etime;
$mus = memory_get_peak_usage();
 //时间 是否写入 建表异常 写库数量 写库时间 总数量 统计行数耗时 当前内存
 $oli ="$dar\t$okecha\t$okecho\t$to1\t$recs\t$to2\t$mus";
echo $oli;
file_put_contents("$biao.txt", "\r\n".$oli, FILE_APPEND | LOCK_EX);
if($conn){ mysqli_close($conn); }
?>