mysql - SQL查询性能问题(多对多关系表)

Question

我有一个目标文件到源文件的关系，如下所示

--
OBJECT_FILE: F:/XX/YY/ZZ/OperatingSystem.o  e4cd09e5fc1c74ec6a2e24c361f7103d3a4036a2    5
    F:/XX/YY/ZZ/OperatingSystem.cpp ba06447d296ceae294962bbf130406052ebb9d7c    2
    F:/XX/YY/ZZ/OperatingSystem.hpp     272b23c2590b2f2e908b9f7e148a1dfcb61183d8    4
    F:/XX/YY/ZZ/Types.h     2375eeec03d837b351a0c105e663dcea6aee434d    3
    F:/XX/YY/ZZ/time.h      f7a9165daf21d6f200ad656fbace652fcde11c4b    3
    F:/XX/YY/ZZ/cdefs.h     f0704e779b9252398f7859a05cc65bbd563cdd0e    3
    F:/XX/YY/ZZ/cdefs_elf.h     63e208b3b175f84e32918d08096693688d860869    3
    F:/XX/YY/ZZ/time.h      f4eaf411f6b1a8817f3baed1bfbacd4ea2f51b9f    3
    F:/XX/YY/ZZ/cdefs.h     f0704e779b9252398f7859a05cc65bbd563cdd0e    3
OBJECT_FILE: F:/XX/YY/ZZ/CIpInterface.o e4cd09e5fc1c74ec6a2e24c361f7103d3a4036a2    5
    F:/XX/YY/ZZ/CIpInterface.cpp    ba06447d296ceae294962bbf130406052ebb9d7c    2
    F:/XX/YY/ZZ/OperatingSystem.hpp     272b23c2590b2f2e908b9f7e148a1dfcb61183d8    4
    F:/XX/YY/ZZ/Types.h     2375eeec03d837b351a0c105e663dcea6aee434d    3
    F:/XX/YY/ZZ/time.h      f7a9165daf21d6f200ad656fbace652fcde11c4b    3
    F:/XX/YY/ZZ/cdefs.h     f0704e779b9252398f7859a05cc65bbd563cdd0e    3
    F:/XX/YY/ZZ/cdefs_elf.h     63e208b3b175f84e32918d08096693688d860869    3
    F:/XX/YY/ZZ/time.h      f4eaf411f6b1a8817f3baed1bfbacd4ea2f51b9f    3
    F:/XX/YY/ZZ/cdefs.h     f0704e779b9252398f7859a05cc65bbd563cdd0e    3
    F:/XX/YY/ZZ/malloc.h        f0704e779b9252398f7859a05cc65bbd563cdd0e    3
    F:/XX/YY/ZZ/stddef.h        f0704e779b9252398f7859a05cc65bbd563cdd0e    3
--

以上信息在文本文件中，我有一个 perl 解析器它将逐行读取文本文件并尝试插入数据库。对象 ( .o) 到源 (.cpp/c/h/hpp ) 文件关系是在单独的多对多关系(映射)表中建立的。

代码摘要如下

1. 读取 OBJECT_FILE: 行，将其插入，并将其 PK、SHA1 校验和作为唯一的。
2. 获取其 PK.say PK1
3. 读取第二行，即OperatingSystem.cpp，插入它。如果错误，检查错误代码
4. 如果错误代码是重复条目，或者没有错误 - 获取其 PK，例如 PK2

   5。在多对多关系表中插入PK1，PK2，如果错误，并且错误代码是重复条目-忽略它，如果有其他错误退出。

示例代码如下

--
            $sth = $dbh->prepare("INSERT INTO source_r_binarylist_table
                                (   Source_r_Binary_Id,
                                    Source_r_Binary_name,
                                    Source_r_Binary_FileName,
                                    Source_r_Binary_Version,
                                    FileTypeId,
                                    SourceofSource_r_Binary,
                                    Source_r_Binary_Supplier,
                                    Source_r_Binary_Originator,
                                    Source_r_Binary_Home_Page,
                                    Source_r_Binary_Download_Location,
                                    Source_r_Binary_Checksum,
                                    Source_r_Binary_Verification_Code,
                                    Excluded_Files,
                                    Source_Info,
                                    License_Concluded,
                                    LicenseIdsFromAllFiles,
                                    LicenseComments,
                                    Summary,
                                    Description,
                                    Technology_category
                                )
                                VALUES
                                    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $error_code =0;
             $sth->execute(undef,undef,$file_name,undef,$file_type,undef,undef,undef,undef,undef,$sha1_chksum,undef,undef,undef,undef,undef,undef,undef,undef,undef)
             or $error_code = $sth->err;
            #print "Error code: $error_code ; return value $DBI::state \n";

            if (($error_code != 0) && ($error_code != ERROR_CODE_DUP_ENTRY))
            {
                die "File:[".__FILE__."] Line:[".__LINE__."]:ERROR: refer DB error code, and take appropriate action".$DBI::errstr;
            }

            $sth->finish();
            #$dbh->commit or die $DBI::errstr;

            # If first time entered or same file is entered again, the checksum will remain same, hence Duplicate entry error is thrown ,its error code is 1062
            if((($error_code == 0) && ($file_type == $FILE_TYPE_OBJ)) ||(($error_code == ERROR_CODE_DUP_ENTRY) && ($file_type == $FILE_TYPE_OBJ)))
            {
                $sth = $dbh->prepare("SELECT Source_r_Binary_Id from source_r_binarylist_table where Source_r_Binary_Checksum = ?");
                $sth->execute( $sha1_chksum ) or die "File:[".__FILE__."] Line:[".__LINE__."]:ERROR:".$DBI::errstr;;
                while (my @row = $sth->fetchrow_array())
                {
                        $src_bin_id_for_obj = $row[0];
                        #print "Source Bin Id = $src_bin_id_for_obj\n";
                }
                $sth->finish();
            }

            if((($error_code == 0) || ($error_code == ERROR_CODE_DUP_ENTRY))&& 
                (($file_type == $FILE_TYPE_SRC_C) || ($file_type == $FILE_TYPE_SRC_CPP)|| 
                ($file_type == $FILE_TYPE_SRC_H) || ($file_type == $FILE_TYPE_SRC_HPP) || 
                ($file_type == $FILE_TYPE_SRC_JAVA)))
            {
                    # get the Id of newly added source (cpp or c or h or hpp or java)
                $sth = $dbh->prepare("SELECT Source_r_Binary_Id from source_r_binarylist_table where Source_r_Binary_Checksum = ?");
                $sth->execute( $sha1_chksum ) or die "File:[".__FILE__."] Line:[".__LINE__."]:ERROR:".$DBI::errstr;

                while (my @row = $sth->fetchrow_array()) {
                        $src_bin_id_for_src = $row[0];
                        #print "Source Bin Id = $src_bin_id_for_obj\n";
                }
                $sth->finish();


                $error_code_sub =0;
                #add an entry in to junction table
                $sth = $dbh->prepare(" INSERT INTO object_source_id_junctiontable (Object_Id_ref,Source_Id_ref) VALUES (?,?)");
                $sth->execute( $src_bin_id_for_obj, $src_bin_id_for_src) or  ($error_code_sub = $DBI::err);

                if (($error_code_sub != 0) && ($error_code_sub != ERROR_CODE_DUP_ENTRY))
                {
                    die "File:[".__FILE__."] Line:[".__LINE__."]:ERROR: $error_code_sub refer DB error code, and take appropriate action: ".$DBI::errstr;
                }
                $sth->finish();

                print "Line:[".__LINE__."]:Insertion successfull [Obj Id: $src_bin_id_for_obj] [Source Id: $src_bin_id_for_src]\n";
            }
    }
--

上面的代码存在巨大的性能问题，插入每一行都需要一秒钟的时间。想象一下，如果输入文件大约有 800000 行，那么完成脚本执行大约需要 10 天。

如果可以完成数据库中所需的更改，请指导减少插入时间的最佳方法。

Answer 1

请提供表的SHOW CREATE TABLE。我特别担心表的索引和引擎。另外， table 有多大。

假设 Source_r_Binary_Id 是 AUTO_INCRMENT，您可以使用 LAST_INSERT_ID 或 获取 ID insert_id。这会比您使用的 SELECT 快一点。然而，我看到类似的 SELECT 再次发生；是吗？

请将问题归结为一系列 SQL 语句；很难读懂 Perl(?) 代码。

请提供`显示变量如'%buffer%'；你有多少内存？

在 sha1 上拥有 UNIQUE 键是致命的，但仍然不应该花费一整秒来执行 2 次插入和 2 次选择，每次都是单行并建立索引。

啊。如果您已“对每一列建立索引”，则执行 INSERT 将花费大部分时间。

建议您检测代码(也许使用 Time::Hires)来查看哪些部分花费的时间最长。

(您提供以上信息后我可能会有更多建议。)