主页 / dba / 问题 / 16284
Accepted
Jérôme Verstrynge
Asked: 2012-04-10 16:27:47 +0800 CST

从设计角度来看，SELECT FROM LIKE 语句的成本有多高？

  • 772

我有一种情况失控，因为我需要在大型目录结构中随机访问越来越多的小静态文件。我必须很快大幅减少这些文件的数量。我正在研究释放压力的快速解决方案。

一种选择是将文件的内容（UTF8 文本）移动到数据库中并执行SELECTs 以替换文件搜索（按名称）。选择语句如下：

SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE 'criteria%';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%othercriteria';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%andanothercriteria%';

我们谈论的是每天在 800K 行的表上总共有 200K 的请求（如果有帮助，我可以轻松地将其分成两部分）。MyContentName是键的一部分，将被索引。要么有一个条目与表中的条件匹配，要么没有。

我不是数据库管理员专家。这是共享服务器上的 MySQL 实例可以支持的，还是我的期望太高了？

我知道典型的答案是：我应该测试。不幸的是，由于紧急情况，我没有时间进行测试。我需要找到一个快速的解决方案，即使是临时的，即使它会稍微降低服务响应延迟。

我正在寻找经验丰富的数据库管理员对此策略的意见。也欢迎提示和建议。

mysql performance

2 个回答

  1. Best Answer

    如果您无法微调文件系统（例如，通过使用较小的块大小），并且您确实必须使用数据库，我建议您阅读以下内容：

    第一个将解释索引最常用的数据结构，B-Tree。第二个解释了 MySQL 如何使用 B-Tree。第三个将告诉您命令EXPLAIN SELECT ...，这是 MySQL 描述查询计划的方式（它会告诉您它正在使用哪个（如果有）索引，如果它正在执行表扫描——您必须不惜一切代价避免）。

    要创建优化索引，您应该首先考虑您需要的查询（或查询）的结构。例如，它可能类似于：select content from files where firstParameter = XXX and secondParameter like 'xxx%'.

    您应该分析每列的基数（即，该列可以有多少不同的值）。

    您选择具有最高基数的列作为索引中的第一个，而将基数较低的列放在最后。示例：假设您有 2M 行，并且firstParameter是 1 到 1M 之间的数字，随机分布，并且secondParameter是文件所有者的全名。在这种情况下，您希望 index(firstParameter, secondParameter)以这种顺序排列，因为该子句firstParameter = XXX平均只剩下 2 行。secondParameter另一方面，的基数要低得多：人名的可能性远低于 1M。因此，如果您的索引是(secondParameter, firstParameter)，则查询将首先查找以开头where firstParameter = 1 and secondParameter like 'bruno%'的每一行（可能是数万或数十万），然后才会查找其他条件。secondParameterbruno

    另外，请注意索引是从左到右使用的。也就是说，如果您有 3 列 ，A并且B您C索引(A, B, C)，则索引在诸如where A = 1 and C = 2. 它可能会用于查找匹配的行A = 1，但之后将检查每一行C = 2。如果您的大多数查询都是这样的（有些可能还指定 B），那么您的索引应该是(A, C, B).

    最后，注意like 'xxx%'可以使用索引，而like '%xxx'（或like '%xxx%'）不能。这又是因为索引是从左到右读取的。为了匹配xxx%，它知道从哪里开始寻找；为了匹配%xxx它必须检查每一行。

    关于索引的所有内容，我强烈建议您重新制定标准，以便您拥有更有条理的东西。正如你所说，你可以尝试预先计算一些东西。

    还有其他考虑因素，例如内容的大小。如果你能把它放在 8KB 以内（如果你使用 UTF-8，这就像 3000 个字符），那么 InnoDB 会将数据存储在与主键相同的页面中；否则，它将把数据存储在别处。如果您通过主键查询，在第一种情况下您有一个读取操作；如果您通过另一个索引查询，在第二种情况下，您有 3 个读取操作：一个查找匹配行的主键，一个通过主键查找行（读取数据的地址）和一个读取数据.

    哦，请检查您的服务器的 RAM 量。理想情况下，您的数据（或至少您的索引）应该适合 RAM。

    通过考虑所有这些点，您应该完全没有问题：我不知道您的服务器的硬件或其负载（因为您说它是共享的），但是如果您微调索引，800k 行几乎没有; 我离专家还很远，通过做上述所有事情，我每天都使用（非常优化的）具有 10M、大约 100M 行的表，并且查询速度非常快。

    我希望这会有所帮助。一旦你有了你的表，你可以问另一个问题，显示create table语句并描述一些关于你的数据（大小、基数等）和你将使用的选择查询，所以有人可以帮助你创建一个优化的索引。

    • 6

  2. 如果您观察到缓慢，我建议您应该使用MyISAMtable 并添加INDEX。FULLTEXT全文搜索是一种基于特殊类型索引（显然是全文）的搜索，在这种情况下性能非常理想虽然像 %% 总是会导致表全表扫描，这可能会非常慢（当你有 100k和更多行）。

    你可以参考这个链接： http ://www.gammelsaeter.com/programming/mysql-fulltext-search-example/

    • 2

相关问题