我有一个约有12万行的表，其中包含一个带BLOB的字段(每个条目的大小不超过1MB，通常小得多)。我的问题是，每当我运行查询询问该表上的任何列(不包括BLOB)时，如果文件系统缓存为空，则大约需要40英寸才能完成。同一张表上的所有后续查询都需要少于1''(从命令行客户端在服务器本身上进行测试)。查询中返回的行数从空集到60k +

我消除了查询缓存，因此与它无关。
该表是myisam，但我也尝试将其更改为innodb(并设置ROW_FORMAT = COMPACT)，但是没有任何运气。

如果我删除BLOB列，查询总是很快。

因此，我假设服务器从磁盘(或其一部分)读取Blob，然后文件系统将其缓存。问题在于，在具有高流量和有限内存的服务器上，文件系统缓存偶尔会刷新一次，因此此特定查询不断给我带来麻烦。

所以我的问题是，有没有一种方法可以在不从表中删除blob列的情况下大大加快速度？

这是两个示例查询，一个接一个地运行，以及解释，索引和表定义:

mysql> SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 100;
Empty set (48.21 sec)
mysql> SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
Empty set (1.16 sec)

mysql> explain SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
|  1 | SIMPLE      | ct    | range | status,score  | status | 768     | NULL | 82096 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
1 row in set (0.00 sec)


mysql> show indexes from completed_tests;
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| completed_tests |          0 | PRIMARY     |            1 | id          | A         |      583938 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | users_login |            1 | users_LOGIN | A         |       11449 |     NULL | NULL   | YES  | BTREE      |         |
| completed_tests |          1 | tests_ID    |            1 | tests_ID    | A         |         140 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | status      |            1 | status      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
| completed_tests |          1 | timestamp   |            1 | timestamp   | A         |      291969 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | archive     |            1 | archive     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | score       |            1 | score       | A         |         783 |     NULL | NULL   | YES  | BTREE      |         |
| completed_tests |          1 | pending     |            1 | pending     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> show create table completed_tests;
+-----------------+--------------------------------------
| Table           | 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=InnoDB AUTO_INCREMENT=117996 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)

最佳答案

我在这个问题上做了一段时间的研究。许多人建议在单独的表中仅使用一个主键来使用blob，并使用对blob表的外键将blob元数据存储在另一表中。这样，性能将大大提高。

关于mysql - 包含blob的表上的mysql查询速度取决于文件系统缓存，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9511476/