php - 100% CPU 使用率 : MySQL 2, 000,000 行并使用 LIKE 运算符进行查询

Question

我有一个包含 2,000,000 行的 MySQL 表，我的网站每天有 40.000 到 50.000 次访问，PHP 每秒总共运行 150 个查询，MySQL CPU 使用率约为 90%。该网站非常慢。

专用服务器:AMD Opteron 8 核、16 GB DDR3。

以下是 MYSQL 查询详细信息:

Search Example: Guns And Roses
Table Storage Engine: MyISAM

Query example:

SELECT SQL_CACHE mp3list.*, likes.* FROM mp3list
   LEFT JOIN likes ON mp3list.mp3id = likes.mp3id
   WHERE mp3list.active=1 AND mp3list.songname LIKE '%guns%'
   AND mp3list.songname LIKE '%and%' AND mp3list.songname LIKE '%roses%'
   ORDER BY likes.likes DESC LIMIT 0, 15"

列“songname”为 VARCHAR(255)。

我想知道我必须做什么才能实现更轻量级的 mysql 搜索，如果有人可以帮助我，我将永远感激不已，我已经寻找了数周的解决方案。

提前谢谢您。

Answer 1

嗯，一种解决方案是停止使用像“%something%”这样的性能 killer 。

我们过去这样做的一种方法是维护我们自己的查找表。我的意思是，将插入、更新和删除触发器放在一起，将任何更改应用于表，例如:

word      varchar(20)
id        int references mp3list(id)
primary key (word,id)

每当您对 mp3list 进行更改时，它都会反射(reflect)到该表中，该表的搜索速度应该比您当前的解决方案快得多。

这会将确定 MP3 包含哪些单词的成本转移到更新时，，而不是每次选择时，摊销成本。由于绝大多数数据库的读取次数远多于写入次数，因此这可以带来显着的改进。一些 DBMS 通过全文搜索索引提供此功能(MySQL 就是其中之一)。

您甚至可以在触发器(和查询)中添加一些智能功能，以完全忽略 a、an 和 the 等干扰词，节省空间和时间，让您可以更精细地控制要存储的内容。