MySQL ODBC更新查询非常慢

Question

我们的Access 2010数据库最近达到了2 2GB的文件大小限制，所以我将数据库移植到了MySQL。

我在Windows Server2008 x64上安装了MySQL Server5.6.1 x64。所有操作系统更新和修补程序都已加载。

我使用的是MySQL ODBC 5.2w x64驱动程序，因为它似乎是最快的。

我的机顶盒有一台i7-3960X，内存64 My，固态硬盘480 My。

我使用Access查询设计器，因为我喜欢它的界面，而且我经常需要将丢失的记录从一个表追加到另一个表中。

作为测试，我有一个简单的Access数据库，其中包含两个链接表：

tblData链接到另一个Access数据库并

tblOnline使用链接的ODBC表的系统DSN。

这两个表都包含1000多万条记录。我移植的一些工作表已经有超过3000万条记录。

为了选择要追加的记录，我使用了一个名为INDBYN的字段，它可以是true，也可以是false。

首先，我在tblData上运行一个更新查询：

UPDATE tblData SET tblData.InDBYN = False;

然后我更新所有匹配的记录：

UPDATE tblData INNER JOIN tblData ON tblData.IDMaster = tblOnline.IDMaster SET tblData.InDBYN = True; 

这样做的速度相当快，甚至对链接的ODBC表也是如此。

最后，我将INDBYN为False的所有记录附加到tblOnline。这也是可以接受的速度，尽管比附加到链接的访问表要慢。

在Access中，除了数据库变得太大之外，一切都能100%正常工作，而且速度快得令人难以置信。

在链接访问表上，更新11,500,000条记录需要2m15秒。

但是，我现在需要将源表移动到MySQL，因为它即将达到2 2GB的限制。

因此，将来我将需要在链接的ODBC表上运行UPDATE语句。

到目前为止，当我在链接的ODBC表上运行相同的简单UPDATE查询时，它运行了20多分钟，然后突然说查询已经超过了2 2GB的内存限制。

两个表在结构上是相同的。

我不知道如何解决这个问题，需要建议。

我更喜欢使用Access作为前端，因为我已经为应用程序设计了数百个查询，并且没有时间重新开发应用程序。

我使用了InnoDB引擎，并尝试了各种调整，但都没有成功。因为我的数据库使用关系表，所以看起来是使用INNODB而不是MyISAM的最佳选择。

我打开和关闭了doublewrite，并尝试了各种缓冲池大小，包括查询缓存。它不会对这个特定的查询产生影响。

我当前的my.ini文件如下所示：

#-----------------------------------------------------------------------
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

[client]
no-beep

port=3306

[mysql]

default-character-set=utf8

server_type=3
[mysqld]

port=3306

basedir="C:\Program Files\MySQL\MySQL Server 5.6\"

datadir="E:\MySQLData\data\"

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=0
general_log_file="SQLSERVER.log"
slow-query-log=1
slow_query_log_file="SQLSERVER-slow.log"
long_query_time=10

log-error="SQLSERVER.err"

max_connections=100

query_cache_size = 20M

table_open_cache=2000

tmp_table_size=502M

thread_cache_size=9

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=1002M

key_buffer_size=8M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K

innodb_additional_mem_pool_size=32M

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size=16M

innodb_buffer_pool_size = 48G

innodb_log_file_size=48M

innodb_thread_concurrency = 0

innodb_autoextend_increment=64M

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=2000

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=70

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4110

query_cache_type = 1

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_relay_log=10000
sync_relay_log_info=10000

tmpdir = "G:/MySQLTemp"
innodb_write_io_threads = 16
innodb_doublewrite
innodb = ON
innodb_fast_shutdown = 1

query_cache_min_res_unit = 4096

query_cache_limit = 1048576

innodb_data_home_dir = "E:/MySQLData/data"

bulk_insert_buffer_size = 8388608

我们将非常感谢您的任何建议。提前谢谢你。

Answer 1

MS Access通过链接表与MySQL的通信速度较慢。慢得可怕。这是不能改变的事实。为什么会发生这种情况？Access首先从MySQL加载数据，然后处理命令，最后将数据放回原处。此外，它会逐行执行此过程！但是，如果您不需要在“更新”查询中使用本地表的参数或数据，则可以避免这种情况。(换句话说，如果您的查询总是相同的，并且只使用MySQL数据)

技巧是强制MySQL服务器处理查询，而不是访问！这可以通过在Access中创建“直通”查询来实现，在Access SQL中，您可以直接编写代码(使用MySQL语法)。然后，Access将此命令发送到MySQL服务器，并在该服务器中直接处理该命令。因此，您查询速度几乎与在本地访问表中一样快。

Answer 2

Access是单用户系统。带有InnoDB的MySQL是一个受事务保护的多用户系统。

当您发出命中10个或更多兆行的UPDATE命令时，MySQL必须构造回滚信息，以防操作在命中所有行之前失败。这需要大量的时间和内存。

如果要执行这些真正庞大的UPDATE和INSERT命令，请尝试将表访问方法切换为MyISAM。MyISAM不受事务保护，因此这些操作可能运行得更快。

您可能会发现，使用ODBC以外的其他工具进行数据迁移会很有帮助。正如您已经发现的，ODBC在处理大量数据的能力方面受到严重限制。例如，您可以将Access表导出为平面文件，然后使用MySQL客户端程序导入它们。看这里..。https://stackoverflow.com/questions/9185/what-is-the-best-mysql-client-application-for-windows

将数据导入MySQL后，就可以运行基于访问的查询了。但要避免命中数据库中所有内容的UPDATE请求。

Answer 3

奥利，我明白你关于避免命中所有行的更新的观点。我使用它来标记目标数据库中缺少的行，这是一种快速而简单的方法，可以只附加缺少的行。我看到SQLyog有一个只追加新记录的导入工具，但它仍然会遍历导入表中的所有行，并且会运行数小时。我将看看是否可以只将我想要的数据导出到CSV，但如果可能的话，让ODBC连接器比现在更快地工作还是很好的。