c# - 为什么我的查询（包含作为命令文本的 sql 文件）超时？

Question

在我的应用程序中，我正在尝试使用 sql 转储文件使用表填充数据库。在填充数据库之前，我首先创建数据库，然后使用 use 命令选择创建的数据库。为此，我将文件保存为字符串资源并将其用作命令的文本。使用的连接字符串包含服务器 ip、具有所有必需权限的用户和相应的密码以及数据库名称。

using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    await connection.OpenAsync();

        using (var transAction = await connection.BeginTransactionAsync())
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.Transaction = transAction;
                cmd.Connection = connection;

                try
                {

                    cmd.CommandText = "CREATE DATABASE IF NOT EXISTS database;"
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = "USE database;"
                    await cmd.ExecuteNonQueryAsync();

                    cmd.CommandText = Properties.Resources.database;
                    await cmd.ExecuteNonQueryAsync();

                    await transAction.CommitAsync();
                }
                catch(MySqlException e)
                {
                    try
                    {
                        await transAction.RollbackAsync();
                    }
                    catch (MySqlException ex)
                    {
                        throw;
                    }

                    throw;  
                }
            }
        }
}

这是我的 sql 文件：

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


CREATE TABLE `filelog_entries` (
  `filelog_entry_id` int(10) UNSIGNED NOT NULL,
  `file_name` varchar(100) NOT NULL,
  `end_of_write_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `size` bigint(20) UNSIGNED NOT NULL,
  `logspace_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `languages` (
  `language_id` int(10) UNSIGNED NOT NULL,
  `language_name` varchar(50) NOT NULL,
  `language_short_name` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `languages` (`language_id`, `language_name`, `language_short_name`) VALUES
(1, 'English', 'EN');

CREATE TABLE `logspaces` (
  `logspace_id` int(10) UNSIGNED NOT NULL,
  `server_directory` varchar(256) NOT NULL,
  `client_directory` varchar(256) NOT NULL,
  `user_name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `main_entries` (
  `log_entry_id` int(10) UNSIGNED NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `application` varchar(100) NOT NULL,
  `pc` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `message_archive` (
  `text_id` int(10) UNSIGNED NOT NULL,
  `language_id` int(10) UNSIGNED NOT NULL,
  `content` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tags` (
  `tag_id` int(10) UNSIGNED NOT NULL,
  `tag_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tag_affiliations` (
  `log_entry_id` int(10) UNSIGNED NOT NULL,
  `tag_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `textlog_entries` (
  `textlog_entry_id` int(10) UNSIGNED NOT NULL,
  `type` enum('DEBUG','INFO','WARNING','ERROR','METHOD','FILE') NOT NULL,
  `text_id` int(10) UNSIGNED NOT NULL,
  `method` varchar(50) NOT NULL,
  `associated_file_id` int(10) UNSIGNED DEFAULT NULL,
  `associated_file_deleted` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `variable_data_affiliations` (
  `variable_data_id` int(10) UNSIGNED NOT NULL,
  `variable_data_content` varchar(100) NOT NULL,
  `textlog_entry_id` int(10) UNSIGNED NOT NULL,
  `index_in_data` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `filelog_entries`
  ADD PRIMARY KEY (`filelog_entry_id`),
  ADD UNIQUE KEY `file_name_logspace_unique` (`file_name`,`logspace_id`) USING BTREE,
  ADD KEY `logspace_id` (`logspace_id`);
ALTER TABLE `filelog_entries` ADD FULLTEXT KEY `file_name` (`file_name`);

ALTER TABLE `languages`
  ADD PRIMARY KEY (`language_id`),
  ADD UNIQUE KEY `language_name_short_name_unique` (`language_name`,`language_short_name`) USING BTREE;
ALTER TABLE `languages` ADD FULLTEXT KEY `name_shortname` (`language_name`,`language_short_name`);

ALTER TABLE `logspaces`
  ADD PRIMARY KEY (`logspace_id`),
  ADD UNIQUE KEY `server_directory_unique` (`server_directory`) USING BTREE,
  ADD UNIQUE KEY `client_directory_unique` (`client_directory`) USING BTREE;
ALTER TABLE `logspaces` ADD FULLTEXT KEY `server_client_dir_and_user` (`server_directory`,`client_directory`,`user_name`);

ALTER TABLE `main_entries`
  ADD PRIMARY KEY (`log_entry_id`);
ALTER TABLE `main_entries` ADD FULLTEXT KEY `application_pc` (`application`,`pc`);

ALTER TABLE `message_archive`
  ADD PRIMARY KEY (`text_id`,`language_id`),
  ADD UNIQUE KEY `content_unique` (`content`) USING BTREE,
  ADD KEY `language_id` (`language_id`);
ALTER TABLE `message_archive` ADD FULLTEXT KEY `content` (`content`);

ALTER TABLE `tags`
  ADD PRIMARY KEY (`tag_id`),
  ADD UNIQUE KEY `tag_name_unique` (`tag_name`) USING BTREE;
ALTER TABLE `tags` ADD FULLTEXT KEY `tag_name` (`tag_name`);

ALTER TABLE `tag_affiliations`
  ADD PRIMARY KEY (`log_entry_id`,`tag_id`),
  ADD KEY `tag_id` (`tag_id`);

ALTER TABLE `textlog_entries`
  ADD PRIMARY KEY (`textlog_entry_id`),
  ADD KEY `text_id` (`text_id`),
  ADD KEY `associated_file_id` (`associated_file_id`),
  ADD KEY `deleted` (`associated_file_deleted`);
ALTER TABLE `textlog_entries` ADD FULLTEXT KEY `method` (`method`);

ALTER TABLE `variable_data_affiliations`
  ADD PRIMARY KEY (`variable_data_id`,`textlog_entry_id`,`index_in_data`),
  ADD KEY `textlog_entry_id` (`textlog_entry_id`);
ALTER TABLE `variable_data_affiliations` ADD FULLTEXT KEY `variable_data_content` (`variable_data_content`);


ALTER TABLE `languages`
  MODIFY `language_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `logspaces`
  MODIFY `logspace_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `main_entries`
  MODIFY `log_entry_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `message_archive`
  MODIFY `text_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `tags`
  MODIFY `tag_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `variable_data_affiliations`
  MODIFY `variable_data_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;


ALTER TABLE `filelog_entries`
  ADD CONSTRAINT `filelog_entries_ibfk_1` FOREIGN KEY (`filelog_entry_id`) REFERENCES `main_entries` (`log_entry_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `filelog_entries_ibfk_2` FOREIGN KEY (`logspace_id`) REFERENCES `logspaces` (`logspace_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `message_archive`
  ADD CONSTRAINT `message_archive_ibfk_1` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `tag_affiliations`
  ADD CONSTRAINT `tag_affiliations_ibfk_1` FOREIGN KEY (`log_entry_id`) REFERENCES `main_entries` (`log_entry_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `tag_affiliations_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `textlog_entries`
  ADD CONSTRAINT `textlog_entries_ibfk_1` FOREIGN KEY (`textlog_entry_id`) REFERENCES `main_entries` (`log_entry_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `textlog_entries_ibfk_3` FOREIGN KEY (`associated_file_id`) REFERENCES `filelog_entries` (`filelog_entry_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `textlog_entries_ibfk_4` FOREIGN KEY (`text_id`) REFERENCES `message_archive` (`text_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `variable_data_affiliations`
  ADD CONSTRAINT `variable_data_affiliations_ibfk_1` FOREIGN KEY (`textlog_entry_id`) REFERENCES `textlog_entries` (`textlog_entry_id`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

调试时，查询的执行需要很长时间。以至于它实际上超过了默认的命令超时 30 秒。30 秒过去后，我收到一条MySqlException说明“命令超时在操作完成之前已过期”。即使将超时值增加到 60 秒也不会带来任何变化。然而，在取消调试过程后，事实证明数据库实际上已经填充了所有的表、索引、约束，甚至是 sql 文件中指定的一个数据集。这就是为什么我认为取消命令的执行存在问题。

sql 文件是在 phpMyAdmin 的导出功能的帮助下创建的。要在我的应用程序中连接到数据库，我使用的是 MySql 连接器/NET 版本 0.61.0。

编辑：我修改了文件并ALTER TABLE一次添加了一个命令。原来执行还是用了很长的时间，但是还是在我配置的60秒超时内执行。当将超时时间增加到 80 秒时，结果表明整个文件可以作为命令执行，因为它大约需要 62 秒。比我使用的最大超时时间长一点。

那么，为什么需要这么长时间？我可以减少任何开销以加快执行速度吗？

标签： c#mysqltimeoutmysql-connector