MySQL 关于优化大型数据库查询的帮助/建议

Question

我有一个 MyISAM mysql 表：

CREATE TABLE IF NOT EXISTS `songs` (
  `rid` int(11) NOT NULL auto_increment,
  `aid` int(11) NOT NULL,
  `song_title` varchar(256) NOT NULL,
  `download_url` varchar(256) NOT NULL,
  PRIMARY KEY  (`rid`),
  UNIQUE KEY `download_url` (`download_url`),
  KEY `song_title` (`song_title`),
  FULLTEXT KEY `song_title_2` (`song_title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1

它有大约 1400 万行。这是我第一次处理这么大的数据库，之前我并没有真正关心过优化。我一直在尝试各种方法来测试速度和准确性。

1) 全文

select song_title from songs 
where match (song_title) againt ('search term') limit 0, 50
-- This gives me very unreliable results but speed is good.

2) LIKE

select song_title from songs 
where song_title LIKE '%search term%' limit 0, 50
-- Moderate matching results, speed is good when the query is 
-- easily able to fetch the first 50 results... but when i 
-- search for a term that does not exist then... here is the result..
-- MySQL returned an empty result set (i.e. zero rows). ( Query took 107.1371 sec )

3) 多个LIKE

select song_title from songs 
where song_title like '%word_1%' and 
      song_title like '%word_2%' and 
      song_title like '%word_3%' and 
      song_title like '%word_N%' LIMIT 0, 50;
-- It takes about 0.2 seconds when the search terms are easily found.
-- Ran this exact above query just now to find the execution time when 
-- no results are found.
-- MySQL returned an empty result set (i.e. zero rows). ( Query took 30.8625 sec )

我正在寻找的是有关优化数据库/查询速度的提示和建议和准确性。

我无法使用像 sphinx 这样的其他搜索引擎，因为我无法访问网站根目录之外的内容，也无法要求处理服务器的人员进行设置。

Answer 1

由于用于该类型索引的 B 树索引的性质，MySQL 无法创建将覆盖带有前导百分号的 LIKE '%word%' 查询的索引。它将使用索引进行前缀搜索，例如 LIKE 'word%'。请注意，MySQL 全文索引根本不涵盖 LIKE 查询。 MyISAM 全文索引涵盖的唯一查询是 MATCH ... AGAINST ...

假设您的数据集大小，您确实需要外部搜索引擎，特别是如果您计划增加数据量您搜索的数据。

我没有关于您的托管环境的详细信息，但如果您可以通过 SSH 访问托管服务器，我相信您可以作为非特权用户安装和运行 Sphinx。使用 ./configure 脚本将位置前缀设置为您的主目录（但请确保它无法从网络访问），如下所示：

./configure --prefix=/path/to/your/home

然后执行

make && make install

然后创建 sphinx 配置，如 http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/ 最后通过从命令行运行 searchd 来启动守护进程：

/path/to/your/home/bin/searchd

希望如此有帮助。

Answer 2

使用 like '%text%' 的查询不使用索引。
如果您正在寻找良好的性能，请使用全文版本，即使它不能返回准确的结果。
如果可以使用命令explain select ...来查看查询中使用了哪些索引。

您可以在此处查看更多信息：http://dev.mysql。 com/doc/refman/5.0/en/mysql-indexes.html