MySQL ODBC 更新查询非常慢

Question

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

我在 Windows Server 2008 x64 上安装了 MySQL Server 5.6.1 x64。加载所有操作系统更新和补丁。

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

我的盒子有一个 i7-3960X，配备 64GB RAM 和 480GB SSD。

我使用 Access Query Designer，因为我更喜欢这个界面，而且我经常需要将缺失的记录从一个表追加到另一个表。

作为测试，我有一个带有两个链接表的简单 Access 数据库:

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

tblOnline 使用 SYSTEM DSN 链接 ODBC 表。

两个表都包含超过 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% 正常工作，而且速度快得令人难以置信，只是数据库变得太大了。

在 Linked Access Table 上，更新 11,500,000 条记录需要 2 分 15 秒。

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

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

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

两个表在结构上完全相同。

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

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

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

我打开和关闭双写并尝试了各种缓冲池大小，包括查询缓存。它对这个特定查询没有影响。

我当前的 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 服务器，并在该服务器内直接进行处理。因此，您的查询几乎与在本地访问表中查询一样快。