PHP写入MYSQL代码性能如何提升

cqwcns

经常需要大量更新数据到MYSQL（根据操作类型决定增改删），动辄数万甚至十几万行，前端POST过来是JSON，为了提高稳定性和避免服务器限制数据大小，前端会将数据分割成1000条每组发到服务器（1000条不到1M）。
问题来了，我写的代码处理这1000条数据竟然要解决20秒，效率很低。我想应该是我写的代码不够科学，请大神抽时间帮忙改改，感谢

<?php
header( "Content-Type: text/html; charset=utf-8" );
//连接数据库
include( "conn.php" );
$arrDiZhi = json_decode( $_POST[ 'arrDaoRuDiZhi' ], true );
$DangQianShiJian = date( "Y-m-d H:i:s" );
//计数变量
$vXinZengT = 0;
$vXinZengF = 0;
$vXiuGaiT = 0;
$vXiuGaiF = 0;
$vShanChuT = 0;
$vShanChuF = 0;
$vLeiXingCuoWu = 0;

foreach ( $arrDiZhi as $arrRow ) {

        $DaoRuCaoZuo = $arrRow[ 'DaoRuCaoZuo' ];
        $WangGeID = $arrRow[ 'WangGeID' ];
        $WangGe = $arrRow[ 'WangGe' ];
        $DiZhiID = $arrRow[ 'DiZhiID' ];
        $PARENTID = $arrRow[ 'PARENTID' ];
        $BenJiMingCheng = $arrRow[ 'BenJiMingCheng' ];
        $DiZhiJiBie = $arrRow[ 'DiZhiJiBie' ];
        $DiZhiQuanCheng = $arrRow[ 'DiZhiQuanCheng' ];
        $ChangJingLeiXing = $arrRow[ 'ChangJingLeiXing' ];
        $DiYuShuXing = $arrRow[ 'DiYuShuXing' ];
        $DiZhi1Ji = $arrRow[ 'DiZhi1Ji' ];
        $DiZhi2Ji = $arrRow[ 'DiZhi2Ji' ];
        $DiZhi3Ji = $arrRow[ 'DiZhi3Ji' ];
        $DiZhi4Ji = $arrRow[ 'DiZhi4Ji' ];
        $DiZhi5Ji = $arrRow[ 'DiZhi5Ji' ];
        $WagnLuoLaiYuan = $arrRow[ 'WagnLuoLaiYuan' ];
        $DiZhiLaiYuan = $arrRow[ 'DiZhiLaiYuan' ];

        if ( $arrRow[ 'DaoRuCaoZuo' ] == 1 ) {

                if ( $result = $mysqli->query( "SELECT id FROM dizhi WHERE DiZhiID='$DiZhiID'" ) ) {
                        $row_cnt = $result->num_rows;
                        $result->close();
                        if ( $row_cnt > 0 ) {
                                //修改

                                if ( $result = $mysqli->query( "UPDATE `dizhi` SET `WangGeID` = '$WangGeID', `WangGe` = '$WangGe',  `PARENTID` = '$PARENTID', `BenJiMingCheng` = '$BenJiMingCheng', `DiZhiJiBie` = '$DiZhiJiBie', `DiZhiQuanCheng` = '$DiZhiQuanCheng', `ChangJingLeiXing` = '$ChangJingLeiXing', `DiYuShuXing` = '$DiYuShuXing', `DiZhi1Ji` = '$DiZhi1Ji', `DiZhi2Ji` = '$DiZhi2Ji', `DiZhi3Ji` = '$DiZhi3Ji', `DiZhi4Ji` = '$DiZhi4Ji', `DiZhi5Ji` = '$DiZhi5Ji', `WagnLuoLaiYuan` = '$WagnLuoLaiYuan', `DiZhiLaiYuan` = '$DiZhiLaiYuan' WHERE `DiZhiID` = '$DiZhiID'" ) ) {
                                        if ( $mysqli->affected_rows > 0 ) {
                                                ++$vXiuGaiT;
                                        } else {
                                                ++$vXiuGaiF;
                                        };
                                };
                        } else {
                                //新增
                                $query = "INSERT INTO `dizhi` (`id`, `WangGeID`, `WangGe`, `DiZhiID`, `PARENTID`, `BenJiMingCheng`, `DiZhiJiBie`, `DiZhiQuanCheng`, `ChangJingLeiXing`, `DiYuShuXing`, `DiZhi1Ji`, `DiZhi2Ji`, `DiZhi3Ji`, `DiZhi4Ji`, `DiZhi5Ji`, `WagnLuoLaiYuan`, `DiZhiLaiYuan`, `LuRuShiJian`, `LuRuRen`) VALUES (NULL, '$WangGeID', '$WangGe', '$DiZhiID', '$PARENTID', '$BenJiMingCheng', '$DiZhiJiBie', '$DiZhiQuanCheng','$ChangJingLeiXing', '$DiYuShuXing', '$DiZhi1Ji', '$DiZhi2Ji', '$DiZhi3Ji', '$DiZhi4Ji', '$DiZhi5Ji', '$WagnLuoLaiYuan', '$DiZhiLaiYuan',  '$DangQianShiJian', '管理员')";

                                $mysqli->query( $query );
                                if ( $mysqli->affected_rows > 0 ) {
                                        ++$vXinZengT;
                                } else {
                                        ++$vXinZengF;
                                };

                        }; //判断结果集是否大于0
                }; //判断是否已存在


        } else if ( $arrRow[ 'DaoRuCaoZuo' ] == 0 ) {
                //删除

                $query = "DELETE FROM dizhi WHERE DiZhiID='$DiZhiID'";
                $mysqli->query( $query );

                if ( $mysqli->affected_rows > 0 ) {
                        ++$vShanChuT;
                } else {
                        ++$vShanChuF;
                };

        } else {
                //操作类型错误
                ++$vLeiXingCuoWu;
        }; //判断操作
}; //循环Row

$mysqli->close();


$arrFanHui = array( $vXinZengT, $vXinZengF, $vXiuGaiT, $vXiuGaiF, $vShanChuT, $vShanChuF, $vLeiXingCuoWu );
echo json_encode( $arrFanHui, JSON_UNESCAPED_UNICODE );

?>

cqwcns

刚刚在网上查了一下，有人说循环太多时建议释放缓冲，不知道怎么弄，请大神指教。

smldhz

php+mysql 是同步操作没有并发，是一条一条来执行，效率比较低，看网上讨论一般update的话是一秒钟几十条 符合你的情况。
优化的话，尽量减少查询次数
比如update 原来是

[Asm] 纯文本查看 复制代码

update xx set a='a',b='1111' where c=1;
update xx set a='b',b='2222' where c=2;
update xx set a='c',b='3333' where c=3;

改成

[HTML] 纯文本查看 复制代码

UPDATE xx 
    SET a = CASE c 
        WHEN 1 THEN 'a' 
        WHEN 2 THEN 'b' 
        WHEN 3 THEN 'c' 
    END, 
    b = CASE c 
        WHEN 1 THEN '1111'
        WHEN 2 THEN '2222'
        WHEN 3 THEN '3333'
    END
WHERE c IN (1,2,3)

这样一条语句更新多个记录 速度会快非常多（比for循环单条更新快10倍以上）

insert的话 把数据按格式输出到文本 然后用LOAD DATA INFILE会快非常非常非常多（需要pdo方式连接数据库）

或者php就负责接收数据 输出到其他外部程序（脚本）来做并行查询，再返回结果。

加个参考链接
https://stackoverflow.com/questions/3952288/speeding-up-large-numbers-of-mysql-updates-and-inserts

6662680

楼上说的不错，写后端，特别是注重速度的，严禁循环里有对数据库操作，这个被主管看到要挨屌的

namedlxd

批量插入, 不要一条一条插

Rookietp

thinkphp

cqwcns

smldhz 发表于 2019-10-3 16:21
php+mysql 是同步操作没有并发，是一条一条来执行，效率比较低，看网上讨论一般update的话是一秒钟几十条  ...

谢谢大哥，你的回复对我非常有价值，另外还有一个问题，判断数据是否已存在，如果存在，就UPDATE，否则INSERT。这一点，搞得我全部都SELECT一次通过返回值了判断，这个有改善的空间吗？谢谢

cqwcns

谢谢各位，另外还有一个问题，判断数据是否已存在，如果存在，就UPDATE，否则INSERT。这一点，搞得我全部都SELECT一次通过返回值了判断，这个有改善的空间吗？谢谢

smldhz

cqwcns 发表于 2019-10-3 18:22
谢谢各位，另外还有一个问题，判断数据是否已存在，如果存在，就UPDATE，否则INSERT。这一点，搞得我全部都 ...

REPLACE INTO 啊

cqwcns

smldhz 发表于 2019-10-3 18:55
REPLACE INTO 啊

你好，REPLACE INTO有个问题。
因为前端POST过来的不是全部字段，例如该条记录的创建的时间，是首次创建时在PHP赋值的。
如果用REPLACE INTO，会覆盖掉首次创建时间（我们不希望创建时间被UPDATE）。
对于这种情况，还有什么好方法，感谢。