我遇到了一个问题,即在分配了 4GB 的 VirtualBox 机器上查询花费不到一秒,但在分配了 16GB 的实际服务器上需要 20 多秒。
首先,下面是架构的相关部分:
accounts (about 13k records)
(
act_id
)
contacts (about 13k records)
(
cnt_id,
cnt_description,
cnt_hascontact_id,
cnt_hascontact_type
)
addresses (about 13k records)
(
add_id,
add_description,
add_hasaddress_id,
add_hasaddress_type
)
这是我正在运行的查询:
SELECT
act_id,
act_name,
act_short,
act_ptnumber,
cnt_firstname,
cnt_lastname,
cnt_phone,
cnt_email,
add_street1,
add_city,
add_state,
add_zip
FROM accounts
INNER JOIN addresses ON act_id = add_hasaddress_id
INNER JOIN contacts ON act_id = cnt_hascontact_id
WHERE act_name LIKE '%<some string>%'
AND cnt_description = 1
AND add_description = 3
GROUP BY act_id
LIMIT 100;
现在。联系人和地址表是多态的,因此除了accounts.act_id之外,此处没有索引。我认为这很重要,但同样,我的 VM 运行查询需要不到一秒钟的时间。
我在每台机器上打开了分析,发现在真实服务器上花费这么长时间的是"复制到 tmp 表"。这本身可能需要 20 秒。我觉得奇怪的是,VM服务器甚至不执行此步骤。所以,我明白为什么它这么快。
假设真实服务器正在使用中,因此数据库一次受到大约 100 个左右用户的访问,而不是我的 VM,只有我的查询在运行。我尝试将"tmp_table_size"和"max_heap_table_size"的值增加到 1024M,但这没有任何作用。
有没有人知道发生了什么或我如何解决这个问题?提前感谢任何帮助。
编辑:
所有表都是InnoDB
一些混淆:
虚拟机:
tmpdir = /var/lib/mysqltmp
table-definition-cache = 4096
table-open-cache = 4096
max-connections = 400
max-connect-errors = 1000000
max-allowed-packet = 16M
skip-name-resolve
wait-timeout = 600
key-buffer-size = 32M
myisam-sort-buffer-size = 128M
innodb-file-format = Barracuda
服务器:
tmpdir = /var/lib/mysqltmp
skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
table-open-cache = 4096
table-definition-cache = 4048
tmp-table-size = 2048M
max-heap-table-size = 2048M
back-log = 100
max-connect-errors = 10000
max-allowed-packet = 64M
interactive-timeout = 3600
wait-timeout = 600
default-storage-engine = InnoDB
innodb = FORCE
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
如果配置不存在,则它是默认的。
编辑2:
以下是每台服务器上查询的说明选择:
虚拟机:
1 SIMPLE contacts ref contacts_cnt_description_foreign contacts_cnt_description_foreign 4 const 5724 Using temporary; Using filesort
1 SIMPLE accounts eq_ref PRIMARY,accounts_act_name_unique,accounts_act_type_foreign,accounts_act_businesstype_foreign,accounts_act_parent_foreign PRIMARY 4 supportnet.contacts.cnt_hascontact_id 1 Using where
1 SIMPLE addresses ALL addresses_add_description_foreign 12548 Using where; Using join buffer (Block Nested Loop)
服务器:
1 SIMPLE contacts ref contacts_cnt_description_foreign contacts_cnt_description_foreign 4 const 6155 Using temporary; Using filesort
1 SIMPLE addresses ALL addresses_add_description_foreign 12903 Using where; Using join buffer
1 SIMPLE accounts eq_ref PRIMARY PRIMARY 4 supportnet.contacts.cnt_hascontact_id 1 Using where
由于每个表中的记录如此之少,因此不可能花费 20 秒,也许如果您忘记了索引。您应该检查诸如锁,防火墙阻塞或网络问题之类的内容
InnoDB -- 好。 但我没有看到innodb_buffer_pool_size的设置。 这很重要,应该是可用 RAM 的 70% 左右。 由于 RAM 不同,并且您可能使用的是不同的版本(具有不同的默认值),请查看SHOW VARIABLES LIKE 'innodb%';
还有其他重要的设置可以以不同的方式默认设置。
如果其他查询正在生产计算机上运行,则它们可能会破坏缓存 (buffer_pool)。 这可能会导致您所看到的。 如果还有其他"大"查询,让我们专注于它们。
数据集的大小和内容是否相同?
不要使用 RAM 磁盘 - 备用空间在buffer_pool中更有用。 而且,如果你的查询太大,它会崩溃。
"tmp_table_size"和"max_heap_table_size"至1024M
这很危险! 它可能导致 RAM 耗尽,这将导致"交换",这比将它们设置得更低对性能要差得多。
编辑(基于解释)
添加INDEX(add_hasaddress_id)
-- 注意它在进入addresses
时是如何说ALL
的? 这意味着没有索引是有用的。 如有其他问题,请向我们提供SHOW CREATE TABLE
。
最终的解决方案是升级mysql。一旦我从 5.5 移动到 5.6,这个问题就消失了。