为什么这个查询很慢？我应该在这里使用 MyISAM 而不是 InnoDB 吗？
我在慢速查询日志中每小时大约会收到 5 次这样的信息：
# Query_time: 11.420629 Lock_time: 0.000033 Rows_sent: 0 Rows_examined: 0
SET timestamp=1267487708;
INSERT INTO record_lock (record_lock.module_id, record_lock.module_record_id, record_lock.site_id, record_lock.user_id, record_lock.expiration_date_time, record_lock.date_time_created) VALUES ('40', '12581', '940', '155254', '2010-03-02 00:24:57', '2010-03-01 23:54:57');
# Query_time: 2.095374 Lock_time: 0.000031 Rows_sent: 0 Rows_examined: 0
SET timestamp=1267488361;
DELETE
FROM record_lock
WHERE record_lock.user_id = 221659 AND record_lock.expiration_date_time IS NOT NULL;
record_lock 表当前使用 InnoDB，并且其中现在有不到 12 条记录。
我们的系统中有数千名活跃用户。每次他们编辑一条记录时，我们都会插入到该表中。在系统中任何位置的每个页面加载上，我们 1) 从表中进行 SELECT 以查看当前用户是否有任何锁，2) 如果有该用户的任何记录，则对该表运行 DELETE 查询，引用WHERE 子句中表的主键。
这是该表的架构：
CREATE TABLE IF NOT EXISTS `record_lock` (
`module_id` int(10) unsigned NOT NULL,
`module_record_id` int(10) unsigned NOT NULL,
`site_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`expiration_date_time` datetime NOT NULL,
`date_time_created` datetime DEFAULT NULL,
PRIMARY KEY (`module_id`,`module_record_id`),
KEY `record_lock_site_id` (`site_id`),
KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
I'm getting these about 5 times an hour in my slow query logs:
# Query_time: 11.420629 Lock_time: 0.000033 Rows_sent: 0 Rows_examined: 0
SET timestamp=1267487708;
INSERT INTO record_lock (record_lock.module_id, record_lock.module_record_id, record_lock.site_id, record_lock.user_id, record_lock.expiration_date_time, record_lock.date_time_created) VALUES ('40', '12581', '940', '155254', '2010-03-02 00:24:57', '2010-03-01 23:54:57');
# Query_time: 2.095374 Lock_time: 0.000031 Rows_sent: 0 Rows_examined: 0
SET timestamp=1267488361;
DELETE
FROM record_lock
WHERE record_lock.user_id = 221659 AND record_lock.expiration_date_time IS NOT NULL;
The record_lock table currently uses InnoDB, and it has under a dozen records in it right now.
We have several thousand active users in our system. Each time they edit a record, we INSERT into this table. And on each and every page load anywhere in the system, we 1) SELECT from the table to see if there are any locks for the current user and 2) run a DELETE query against that table if there are any records for the user, referencing the table's primary keys in the WHERE clause.
Here is the table's schema:
CREATE TABLE IF NOT EXISTS `record_lock` (
`module_id` int(10) unsigned NOT NULL,
`module_record_id` int(10) unsigned NOT NULL,
`site_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`expiration_date_time` datetime NOT NULL,
`date_time_created` datetime DEFAULT NULL,
PRIMARY KEY (`module_id`,`module_record_id`),
KEY `record_lock_site_id` (`site_id`),
KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您每秒执行多少次查询？
您不能在记录本身中放置一个锁定字段吗？我想无论如何你都会得到记录。您还可以使用 memcached 之类的东西来存储锁。
我不知道具体细节，但我的理解是，InnoDB 对于并发读取非常有用，但对于并发写入却很糟糕。 MyISAM 可能会更好，但我的直觉告诉我当前的设计是有缺陷的。
How many queries are you doing per second?
Could you not just put a locked field in the records themselves? I assume you are getting the record anyway. You could also use something like memcached for storing the locks.
I don't know the specifics off the top of my head, but my understanding is that InnoDB is great for concurrent reads, but sucks for concurrent writes. MyISAM might be better, but my gut tells me the current design is flawed.
您是否尝试过对查询运行 EXPLAIN？
Have you tried running an EXPLAIN on the queries?
是否可能有太多连接尝试访问同一个表？您可以尝试根据 user_id 对表进行分段来帮助解决此问题。
Is it possibly too many connections trying to hit the same table? You could try segmenting the table on user_id to help with that.
打开 Innodb 监视器可以帮助缩小性能不佳的原因：
显示引擎 INNODB 状态和 InnoDB 监视器
Switching on the Innodb monitors can help narrow down the causes of poor performance:
SHOW ENGINE INNODB STATUS and the InnoDB Monitors