c# - 运行时优化 C# -> MySQL

Question

我正在尝试通过 C# 将数据写入 MySql 数据库。问题是所有列在一起应该是唯一的，但对于键来说太长了。为了检查重复项，我当前有一个选择查询来检查记录是否已存在并且不会通过插入查询添加。现在，该脚本每天运行一次，并检查数百万条记录是否已存在，如果不存在，则会添加它们。

示例:如果为 200 万，这将是超过 x 百万行的 200 万个选择查询，并再次为所有不重复的数据插入查询。

有没有更好的方法来检查重复项？正如我所说的唯一，只有所有列都在一起。行数越多，选择需要的时间就越长...

foreach (var item in list)
{
    string query = "SELECT id FROM dirs WHERE Directory = \"" + item.dir + "\" AND IdentityReference = \"" + item.IdentityReference + "\" AND AccessControlType = \"" + item.AccessControlType + "\" AND FileSystemRights = \"" + item.FileSystemRights + "\" AND IsInherited = " + item.IsInherited.ToString();

    MySqlCommand commanDatabase = new MySqlCommand(query, databaseConnection);
    MySqlDataReader dataReader = commanDatabase.ExecuteReader();

    bool duplicate = false;
    while (dataReader.Read())
    {
        duplicate = true;
        break;
    }

    dataReader.Close();

    if (!duplicate)
    {
        query = "INSERT INTO dirs (Directory, IdentityReference, AccessControlType, FileSystemRights, IsInherited) VALUES ";
        query += "(\"" + item.dir + "\", \"" + item.IdentityReference + "\", \"" + item.AccessControlType + "\", \"" + item.FileSystemRights + "\", " + item.IsInherited.ToString() + ")";

        commanDatabase = new MySqlCommand(query, databaseConnection);
        commanDatabase.CommandTimeout = 60;
        commanDatabase.ExecuteNonQuery();
    }
}

Answer 1

您可以使用虚拟列，从您的 ROW 自动生成 MD5 校验和，并将其存储在具有唯一索引的新字段中。

注意:MySQL 和 MariaDB 之间的语法略有不同。

示例

你的 table

CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Directory` varchar(128) DEFAULT NULL,
  `IdentityReference` varchar(128) DEFAULT NULL,
  `AccessControlType` varchar(128) DEFAULT NULL,
  `FileSystemRights` varchar(128) DEFAULT NULL,
  `IsInherited` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加 2 条记录

MariaDB [chksum]> INSERT INTO `mytable` (`id`, `Directory`, `IdentityReference`, `AccessControlType`, `FileSystemRights`, `IsInherited`)
    ->     VALUES
    ->         (1, '/usr/local', 'yes', 'rwxrwxrwx', 'NO', 7),
    ->         (2, '/etc/var/log', 'yes', 'xxxxx', 'YES', 99);
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [chksum]> select * from mytable;
+----+--------------+-------------------+-------------------+------------------+-------------+
| id | Directory    | IdentityReference | AccessControlType | FileSystemRights | IsInherited |
+----+--------------+-------------------+-------------------+------------------+-------------+
|  1 | /usr/local   | yes               | rwxrwxrwx         | NO               |           7 |
|  2 | /etc/var/log | yes               | xxxxx             | YES              |          99 |
+----+--------------+-------------------+-------------------+------------------+-------------+
2 rows in set (0.000 sec)

MariaDB [chksum]>

更改您的表格，添加新字段

MariaDB [chksum]> ALTER TABLE `mytable`
    -> ADD COLUMN
    -> `chksum` VARCHAR(32) AS (
    ->     MD5(
    ->         CONCAT(    `DIRECTORY`,'-',
    ->                 `IdentityReference`,'-',
    ->                 `AccessControlType`,'-',
    ->                 `FileSystemRights` ,'-',
    ->                 `IsInherited`
    ->             )
    ->         )
    -> )  PERSISTENT ,
    -> ADD  UNIQUE KEY `idx_chksum` (`chksum`);
Query OK, 2 rows affected (0.029 sec)
Records: 2  Duplicates: 0  Warnings: 0

现在使用新字段查看内容

MariaDB [chksum]> select * from mytable;
+----+--------------+-------------------+-------------------+------------------+-------------+----------------------------------+
| id | Directory    | IdentityReference | AccessControlType | FileSystemRights | IsInherited | chksum                           |
+----+--------------+-------------------+-------------------+------------------+-------------+----------------------------------+
|  1 | /usr/local   | yes               | rwxrwxrwx         | NO               |           7 | 5b812d391703099e6fea16f8a590891e |
|  2 | /etc/var/log | yes               | xxxxx             | YES              |          99 | 99a5e08acb58b23618bdb9f40737e5cf |
+----+--------------+-------------------+-------------------+------------------+-------------+----------------------------------+
2 rows in set (0.002 sec)

MariaDB [chksum]>

插入没有重复键的行

MariaDB [chksum]> INSERT INTO `mytable` (`id`, `Directory`, `IdentityReference`, `AccessControlType`, `FileSystemRights`, `IsInherited`)
    -> VALUES
    ->     (NULL, '/usr/local/XXX', 'yes', 'rwxrwxrwx', 'NO', 7);
Query OK, 1 row affected (0.002 sec)

MariaDB [chksum]> INSERT INTO `mytable` (`id`, `Directory`, `IdentityReference`, `AccessControlType`, `FileSystemRights`, `IsInherited`)
    -> VALUES
    ->     (NULL, '/usr/local', 'yes', 'rwxrwxrwx', 'NO', 7);
ERROR 1062 (23000): Duplicate entry '5b812d391703099e6fea16f8a590891e' for key 'idx_chksum'
MariaDB [chksum]>