使用'%术语%‘这样的查询非常慢

Question

我有一个InnoDB表，大约1,000,000行：

Data            164.7   MiB
Index           250.1   MiB
Overhead        168.0   MiB
Effective       246.8   MiB
Total           414.8   MiB

表结构：

CREATE TABLE IF NOT EXISTS `gift` (
  `tm` varchar(15) NOT NULL,
  `col` smallint(2) NOT NULL,
  `myindex` varchar(255) NOT NULL,
  `date` int(10) NOT NULL,
  KEY `tm` (`tm`),
  KEY `date` (`date`),
  KEY `myindex` (`myindex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tm是由PHP (产品id)生成的不重复字符串，

date商店使用strtotime('now')，

myindex商店搜索关键词

因为有些产品有很长的描述(超过255个字符)，所以col是独立的长描述和许多部分(相同的产品，关键字第1部分，关键字第2部分.)。

这样的查询将产生(总共10次，查询时间为51.0829秒)

SELECT * 
FROM gift
WHERE myindex LIKE  '% keywords1 %'
AND myindex LIKE  '% keywords2 %'
GROUP BY tm
ORDER BY DATE DESC 
LIMIT 10

做个解释，得到：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      gift    index   NULL            date    4   NULL 10 Using where; Using temporary

possible_keys是空的，我做了一个糟糕的索引吗？怎么让它跑得最快？

这里是my.cnf，我的服务器有4GB内存，mysql也支持全文搜索。

我尝试将key_buffer_size和innodb_buffer_pool_size设置为512 to，但是服务器很容易崩溃，并发出警告apache child process still did not exit sending a sigterm。我必须为system和apache至少留下1GB的内存吗？(my.cnf只需2.5至3GB？)

[mysqld]
character_set_server=utf8
port        = 3306
socket      = /var/lib/mysql/mysql.sock
skip-external-locking
skip-networking
key_buffer = 256M
tmp_table_size = 64M
max_connections = 300
wait_timeout=15
back_log = 2048
key_buffer_size = 384M
max_allowed_packet = 2M
table_cache = 2048
table_open_cache = 2048
sort_buffer_size = 6M
read_buffer_size = 4M
net_buffer_length = 92K
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 256M
thread_cache = 384
query_cache_size= 256M
bulk_insert_buffer_size = 192M
ft_min_word_len=2
skip-networking

binlog_format=mixed

innodb_buffer_pool_size = 384M
innodb_buffer_pool_instances=4

innodb_use_sys_malloc = 0

[mysqldump]
quick
max_allowed_packet = 8M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer = 3M
write_buffer = 3M

Answer 1

首先，你使用的是非法组织。检查http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html的第一段

第二，似乎小组被甚至是不必要的，并导致你的‘临时’显示在解释中。

第三，(不要引用这一点，而不是100%肯定)您的临时tmp_table_size = 64M可能太小，仅限于此查询，因为它在分组前将筛选的结果存储在内存中。

第四，如果确实需要分组，则为该字符串分配一个整数，并对整数进行分组。

第五，如果您有关键字，将它们存储在item-关键字表中，并将它们与联接一起使用。将使您的性能受到一些影响，因为连接，但它将允许您使用一个索引。索引只在从一开始比较字符串时起作用(因此，在varchar上使用长度为6-10的索引可能与使用100长度索引一样有效)。

(快乐的优化:)

Answer 2

您的实际问题是，MySQL不可能为诸如myindex LIKE '% keywords1 %'这样的条件使用索引。这个主题在网络上被广泛的讨论，所以我建议你寻找全文索引的解决方案。您可以使用内部(MySQL中的索引)或外部(狮身人面像、Lucene等)。