mysql查询起先慢后快 [英] mysql query slow at first fast afterwards
问题描述
我有2个myISAM表，分别称为测试"和"completed_tests"，一个表有170个条目，另一个表有118k条目.当我运行此查询时:
I have 2 myISAM tables, called 'tests' and 'completed_tests', one with 170 entries and the other with 118k entries. When I run this query:
SELECT ct.archive, ct.status, ct.score, ct.users_LOGIN, t.lessons_ID, t.content_ID, t.keep_best
FROM completed_tests ct,tests t
WHERE ct.status != 'deleted' and ct.status != 'incomplete' and t.id=ct.tests_ID and t.lessons_ID=10;
然后大约需要30秒才能完成.随后对同一查询或相关查询(例如，不同的lessons_ID)的调用要快得多.即使我重置查询缓存或重新启动mysql服务器，它们仍然保持更快的速度.我想这意味着表被缓存到内存中(并留在内存中). 我的问题是，此特定查询似乎在运行此应用程序的高流量站点上引起了问题(我想这是因为服务器的内存不足并清空了其缓存?). 我的问题是:
Then it takes around 30 second to accomplish. Subsequent calls to the same query, or related queries (different lessons_ID for example), are much faster. They remain faster even if I reset the query cache or restart the mysql server. I suppose this means that the tables are cached into memory (and stay there). My problem is that this specific query seems to be causing problems on high traffic sites that run this application (I guess because the server is slow on memory and emptying its cache?). My questions are:
- 是否可以始终如一地在系统上复制30英寸的延迟，因此我可以尝试优化查询?例如，我应该清空系统的缓存吗?
- 有没有一种方法可以优化上面的查询?运行一个解释会给出:
运行说明给出:
mysql> explain SELECT ct.archive, ct.status, ct.score, ct.users_LOGIN, t.lessons_ID, t.content_ID, t.keep_best FROM completed_tests ct,tests t WHERE ct.status != 'deleted' and ct.status != 'incomplete' and t.id=ct.tests_ID and t.lessons_ID=10;
+----+-------------+-------+------+-----------------+----------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+----------+---------+---------------+------+-------------+
| 1 | SIMPLE | t | ref | PRIMARY,idx1 | idx1 | 3 | const | 4 | |
| 1 | SIMPLE | ct | ref | tests_ID,status | tests_ID | 3 | firstcho.t.id | 1025 | Using where |
+----+-------------+-------+------+-----------------+----------+---------+---------------+------+-------------+
据我所知，这表示成功使用了索引. 谢谢大家.
Which, to my understanding, indicates that indexing is used successfully. Thanks to all.
表结构
>show create table 'tests';
CREATE TABLE `tests` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`active` tinyint(1) NOT NULL DEFAULT '1',
`content_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`lessons_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
`mastery_score` tinyint(4) unsigned NOT NULL DEFAULT '0',
`description` text,
`options` text,
`publish` tinyint(1) DEFAULT '1',
`keep_best` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx1` (`lessons_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=171 DEFAULT CHARSET=utf8
>show create table completed_tests;
CREATE TABLE `completed_tests` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`users_LOGIN` varchar(100) DEFAULT NULL,
`tests_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`test` longblob,
`status` varchar(255) DEFAULT NULL,
`timestamp` int(10) unsigned NOT NULL DEFAULT '0',
`archive` tinyint(1) NOT NULL DEFAULT '0',
`time_start` int(10) unsigned DEFAULT NULL,
`time_end` int(10) unsigned DEFAULT NULL,
`time_spent` int(10) unsigned DEFAULT NULL,
`score` float DEFAULT NULL,
`pending` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `users_login` (`users_LOGIN`),
KEY `tests_ID` (`tests_ID`),
KEY `status` (`status`),
KEY `timestamp` (`timestamp`),
KEY `archive` (`archive`),
KEY `score` (`score`),
KEY `pending` (`pending`)
) ENGINE=MyISAM AUTO_INCREMENT=117996 DEFAULT CHARSET=utf8
推荐答案
最后，我求助于将表拆分为2，将blob对象移动到第二个表，并在需要时进行联接.不幸的是，这涉及到更改许多代码行.
Finally I resorted to splitting the table to 2, moving the blob object to the second table and joining wherever needed. Unfortunately that involved changing many lines of code.
这篇关于mysql查询起先慢后快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
