mysql - 如何在mysql中进行模糊查询？ 1表10GB数据，8KW记录

Question

我有一个表tbdb.eratedat，10GB大小，8KW记录，结构:

mysql> DESC ratedat;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| trade_id    | bigint(20)    | NO   | PRI | NULL    |       |
| trade_time  | int(10)       | NO   |     | NULL    |       |
| uid_buy     | bigint(20)    | NO   | MUL | NULL    |       |
| uid_sell    | bigint(20)    | NO   | MUL | NULL    |       |
| goods_title | varchar(120)  | NO   | MUL | NULL    |       |
| goods_price | decimal(10,2) | NO   |     | NULL    |       |
| rate_txt    | text          | NO   | MUL | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

rate_buyer的结构

mysql> DESC rate_buyer;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| uid       | bigint(20)    | NO   | PRI | NULL    |       |
| sex       | tinyint(1)    | NO   | MUL | 0       |       |
| costsum   | decimal(12,2) | NO   |     | 0.00    |       |
| costavg   | decimal(12,2) | NO   |     | 0.00    |       |
| costmax   | decimal(12,2) | NO   |     | 0.00    |       |
| costmin   | decimal(12,2) | NO   |     | 0.00    |       |
| costcount | int(10)       | NO   |     | 0       |       |
| timefirst | int(10)       | NO   |     | NULL    |       |
| timelast  | int(10)       | NO   |     | NULL    |       |
| is_seller | tinyint(1)    | NO   | MUL | 0       |       |
| uptime    | int(10)       | NO   |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

我想要这个结果，搜索任意关键字列出来自 tbdb.eratedat 的不同 uid_sell:

SELECT a.*,b.goods_title,b.goods_price,COUNT(b.trade_id) AS relike
FROM rate_buyer a 
INNER JOIN ratedat b ON a.uid=b.uid_buy 
WHERE b.goods_title LIKE '%MP3%' 
GROUP BY a.uid ORDER BY relike DESC LIMIT 0 , 100 ; 

但是tbdb.eratedat的大小超过10GB，当我运行sql时，程序运行超时。我该怎么做才能得到像sql这样的结果？

Answer 1

使用fulltext指数。例如:

create fulltext index fulltext_goods_title on ratedat (goods_title);

select * from ratedat WHERE MATCH (goods_title) AGAINST ('MP3');