关于模糊查询 like 的优化，求高手解答

Question

SELECT a.id,a.userid,a.weibo,a.sina_uid,a.sina_nick,a.addtime,a.avatarUrl,b.nickname as nick FROM tgw_weibo  a 
NNER JOIN tgw_userinfor b on  a.userid != 0 AND a.userid= b.userid WHERE b.platformid = 1 AND a.platformid =1 AND a.weibo like '%1%' LIMIT 0,5现在有300多万条数据，查出时间要3秒多。有什么好方法能提高速度吗？表结构是innodb,没法做全文索引

Answer 1

like '%1%'：这种形式无法使用索引
在tgw_weibo、tgw_userinfor ：建立userid、platformid 复合索引

Answer 2

userid、platformid 复合索引 已经建了，感觉速度没什么改变

Answer 3

去掉AND a.weibo like '%1%' LIMIT 0,5 后速度如何
去掉 LIMIT 0,5 后速度如何
 分别测试一下

Answer 4

show index from tgw_weibo  
show index from tgw_userinfor explain SELECT a.id,a.userid,a.weibo,a.sina_uid,a.sina_nick,a.addtime,a.avatarUrl,b.nickname as nick    FROM        tgw_weibo  a 
        NNER JOIN tgw_userinfor b    on  a.userid != 0 
AND a.userid= b.userid 
WHERE b.platformid = 1 AND a.platformid =1 AND a.weibo like '%1%' LIMIT 0,5
贴出来以供分析。

Answer 5

去掉AND a.weibo like '%1%' LIMIT 0,5 要17秒多 160万多条记录
去掉 LIMIT 0,5 速度没什么改变

Answer 6

估计是a.weibo like '%1%'这个慢了，无法使用索引，weibo内容是什么

Answer 7

+---------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tgw_userinfor |          0 | PRIMARY               |            1 | id          | A         |         117 | NULL     | NULL   |      | BTREE      |         |
| tgw_userinfor |          0 | uniq_platform_user_id |            1 | platformid  | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| tgw_userinfor |          0 | uniq_platform_user_id |            2 | userid      | A         |         117 | NULL     | NULL   |      | BTREE      |         |
| tgw_userinfor |          1 | userid                |            1 | userid      | A         |         117 | NULL     | NULL   |      | BTREE      |         |
+---------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 8

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tgw_weibo |          0 | PRIMARY  |            1 | id          | A         |     3605627 | NULL     | NULL   |      | BTREE      |         |
| tgw_weibo |          1 | addtime  |            1 | addtime     | A         |     1802813 | NULL     | NULL   | YES  | BTREE      |         |
| tgw_weibo |          1 | user     |            1 | userid      | A         |      901406 | NULL     | NULL   | YES  | BTREE      |         |
| tgw_weibo |          1 | sinauid  |            1 | sina_uid    | A         |     3605627 | NULL     | NULL   | YES  | BTREE      |         |
| tgw_weibo |          1 | weibo    |            1 | weibo       | NULL      |           1 | NULL     | NULL   | YES  | FULLTEXT   |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 9

+----+-------------+-------+------+------------------------------+-----------------------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys                | key                   | key_len | ref                  | rows | Extra       |
+----+-------------+-------+------+------------------------------+-----------------------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | b     | ref  | uniq_platform_user_id,userid | uniq_platform_user_id | 4       | const                |   51 | Using where |
|  1 | SIMPLE      | a     | ref  | user                         | user                  | 9       | db_tiangewb.b.userid |    4 | Using where |
+----+-------------+-------+------+------------------------------+-----------------------+---------+----------------------+------+-------------+

Answer 10

create index xxxx on tgw_weibo  (userid,platformid);

Answer 11

a.weibo like '%1%' 这玩意有什么优化方案吗，感觉最费时了

Answer 12

userid,platformid 不能建联合的索引，里面有数据这2个字段是一样的

Answer 13

create index xxxx on tgw_weibo  (userid,platformid); 这个语句的结果是什么？ 有一样的并不影响创建索引啊？ 你又不是要创建唯一索引，只是个普通索引而已。

Answer 14

 
再贴一下，show index 和 explain 呢。

Answer 15

还有两个表的platformid = 1的记录数又各有多少

Answer 16

这种方式无法用到索引，无法优化a.weibo内容是什么，贴出来看看

Answer 17

a.weibo 的内容就是一些文字信息，比如‘//@三联生活周刊:自然创造了美，我们只是在发现。’

Answer 18

mysql> show index from tgw_weibo;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tgw_weibo |          0 | PRIMARY  |            1 | id          | A         |     3605627 | NULL     | NULL   |      | BTREE      |         |
| tgw_weibo |          1 | addtime  |            1 | addtime     | A         |     1802813 | NULL     | NULL   | YES  | BTREE      |         |
| tgw_weibo |          1 | sinauid  |            1 | sina_uid    | A         |     3605627 | NULL     | NULL   | YES  | BTREE      |         |
| tgw_weibo |          1 | user     |            1 | userid      | A         |      901406 | NULL     | NULL   | YES  | BTREE      |         |
| tgw_weibo |          1 | user     |            2 | platformid  | A         |      901406 | NULL     | NULL   |      | BTREE      |         |
| tgw_weibo |          1 | weibo    |            1 | weibo       | NULL      |           1 | NULL     | NULL   | YES  | FULLTEXT   |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+mysql> show index from tgw_userinfor
;
+---------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tgw_userinfor |          0 | PRIMARY               |            1 | id          | A         |         117 | NULL     | NULL   |      | BTREE      |         |
| tgw_userinfor |          0 | uniq_platform_user_id |            1 | platformid  | A         |           2 | NULL     | NULL   |      | BTREE      |         |
| tgw_userinfor |          0 | uniq_platform_user_id |            2 | userid      | A         |         117 | NULL     | NULL   |      | BTREE      |         |
| tgw_userinfor |          1 | userid                |            1 | userid      | A         |         117 | NULL     | NULL   |      | BTREE      |         |
+---------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+----+-------------+-------+------+------------------------------+-----------------------+---------+----------------------------+------+-------------+
| id | select_type | table | type | possible_keys                | key                   | key_len | ref                        | rows | Extra       |
+----+-------------+-------+------+------------------------------+-----------------------+---------+----------------------------+------+-------------+
|  1 | SIMPLE      | b     | ref  | uniq_platform_user_id,userid | uniq_platform_user_id | 4       | const                      |   51 | Using where |
|  1 | SIMPLE      | a     | ref  | user                         | user                  | 13      | db_tiangewb.b.userid,const |    4 | Using where |
+----+-------------+-------+------+------------------------------+-----------------------+---------+----------------------------+------+-------------+

Answer 19

去掉AND a.weibo like '%1%' LIMIT 0,5 要17秒多 160万多条记录
不去掉要多长时间a.weibo like '%1%'->在weibo上建立复合索引
修改成这样：
a.weibo like '1%'
union
a.weibo like '%1'

Answer 20

不去掉就是3秒多，a.weibo like '1%'
union
a.weibo like '%1' 这样能搜索出所有包含1的数据吗

Answer 21

测试了一下，确实是这样，a.weibo like '%1%'无法优化

Answer 22

在ORACle中我一般用以下方法：
１、表分区，并行查询
２、模拟全文索引，做一下结构化索引优化
３、尽可能插入更多的其他限制条件减少遍历WEIBO的记录次数

Answer 23

如果我把表改成MYISAM的话，怎么使用模糊查询

Answer 24

MYISAM或者INNODB只是在存储上不同，对你的语句来说，是一样的。仍然是 like '%1%'对于这种 %1% 是无法进行优化的。建议你把优化的关注点放在其它连接字段上。

Answer 25

试试这个语句。
SELECT a.id,a.userid,a.weibo,a.sina_uid,a.sina_nick,a.addtime,a.avatarUrl,b.nickname as nick
FROM tgw_weibo a INNER JOIN tgw_userinfor b on a.userid= b.userid
where b.userid != 0 and b.platformid = 1 and a.platformid =1 AND a.weibo like '%1%'

Answer 26

试试先过滤呢
select a.id,a.userid,a.weibo,a.sina_uid,a.sina_nick,a.addtime,a.avatarUrl,b.nickname as nick
from
(select id, userid, weibo, sina_uid, sina_nick, addtime, avatarurl 
from tgw_weibo 
where platformid =1 and userid != 0) a, 
inner join
(select userid, nickname from tgw_weibo where platformid =1 and userid != 0) b 
on a.userid = b.userid
where a.weibo like '%1%'
limit 0,5

Answer 27

好像没什么好办法，我把表结构改成MYISAM，然后做了个全文索引

Answer 28

a.userid != 0,改成a.userid <0 or a.userid >0

Answer 29

2. tgw_weibo和tgw_userinfor 换下位置

Answer 30

我们公司现在有一亿条记录了，like一下必须在五秒内。
主要是用MyIsam,将大表单独分区,建索引。查询尽时使用索引，另外如果有连接，尽量先把大表用子查询+where过滤了，再连接。

Answer 31

SQL已经无法优化了，还是从设计和硬件着手吧。SQL SERVER上尽量做全文索引和表分区。