Mysql BLOB 性能

Question

我有一个简单的 Mysql 5.1(也尝试过版本 5.6)BLOB 表，其中包含字符串主键“文件名”和 BLOB 字段。

CREATE TABLE records (filename VARCHAR(255) PRIMARY KEY, file_content LONGBLOB)

该表大约有 1520 条记录，file_content 大小从大约 30KB 到 1MB 不等。以下是一些令人惊讶的查询时间:

SELECT filename FROM records -- (31 milli-secs, 1520 rows)
SELECT filename FROM records WHERE filename like '%1-11-1.param' -- (31 milli-secs, 304 rows)
SELECT * FROM records  -- (8.5 seconds, 1520 rows)
SELECT * FROM records WHERE filename = 'file-1-11-1.param' -- (32 milli-secs, 1 row)
SELECT * FROM records WHERE filename like '%1-11-1.param' -- (3.5 seconds, 304 rows)

当选择 file_content 时，查询会很慢，除非 WHERE 子句具有直接标识。但是，如果我改为执行 self JOIN (模仿最后一个 SELECT)

SELECT a1.filename, a1.file_content FROM records AS a1
INNER JOIN
(SELECT filename FROM records WHERE filename LIKE '%1-11-1.param') AS a2
ON a1.filename = a2.filename
-- (359 milli-secs, 304 rows)

当 JOIN 产生相同的结果时，为什么 JOIN 比简单的 select * 快得多(359 毫秒 < 3.5 秒)？

编辑我在 SQLServer 2008R2 上尝试过同样的操作。我可以报告说这个数据库没有变慢。对于所有测试来说它都很快，但是当必须检索所有数据(如 MySQL)时，它需要 9 秒，因为它返回大约 500MB 的数据。

我还尝试过使用 MySQL Engine=InnoDB、ROW_FORMAT=DYNAMIC。没有帮助。我希望这意味着每个 LIKE 测试的 BLOB 不会加载到内存中。

Answer 1

经过更多的研究和测试，对于 MySQL 来说:

如果必须测试行中的字段(例如 LIKE)，则即使测试失败或可能快速失败，它也会检索 SELECT 或 WHERE 中出现的每个字段。对于大型 BLOB 字段，尤其是无法放入行内存(超过约 65K)的 BLOB，这是一个真正的问题。

如果可以通过字段上的 INDEX 确定测试，则仅检索匹配的行。