当前位置:Gxlcms > PHP教程 > 关于mysqlset字段类型的模糊查询问题

关于mysqlset字段类型的模糊查询问题

时间:2021-07-01 10:21:17 帮助过:45人阅读

有个40万条的测试数据表
flag set('r', 'l', 'c', 'p')

SELECT a. * , b.typedir
FROM mzrui_archives a
LEFT JOIN mzrui_kind b ON a.kid = b.uid
WHERE a.flag LIKE '%p%'
AND a.kid
IN ( 3, 17, 18 )
ORDER BY a.uid
LIMIT 0 , 15

这个语句查询需要2.5秒的时间,把like去掉后查询相当快,不知道怎么优化,求教。

uid是主键
key kid(kid,flag) 索引


回复讨论(解决方案)

既然是set,为何要like查询? find_in_set('p',a.flag)

既然是set,为何要like查询? find_in_set('p',a.flag)

find_in_set 的效率也是一样,我测试了,关键是find_in_set不能('c,p',a.flag)多条件,这样我查不出记录来

索引是不是有问题


既然是set,为何要like查询? find_in_set('p',a.flag)

find_in_set 的效率也是一样,我测试了,关键是find_in_set不能('c,p',a.flag)多条件,这样我查不出记录来

你可以

find_in_set('p',a.flag) and find_in_set('c',a.flag)



既然是set,为何要like查询? find_in_set('p',a.flag)

find_in_set 的效率也是一样,我测试了,关键是find_in_set不能('c,p',a.flag)多条件,这样我查不出记录来

你可以

find_in_set('p',a.flag) and find_in_set('c',a.flag)


是可以这样,但是效率还是一样,我现在关心的是效率问题,有没有方式可以提高效率

find_in_set会全表扫描,效率确实不高。
可以考虑更改你的表结构。
新建另一个中间表存储set的内容。每次进行连接查询,同时做好字段的索引,效率应该会稍微好一点。

如果你仅仅4个标识('r', 'l', 'c', 'p') ,不如用 1 2 4 8 作为标识位,用例如 1|2 = 3 的方式来标识flag,查询的时候可以 where flag & 2 的方式,应该会快不少

如果你仅仅4个标识('r', 'l', 'c', 'p') ,不如用 1 2 4 8 作为标识位,用例如 1|2 = 3 的方式来标识flag,查询的时候可以 where flag & 2 的方式,应该会快不少

你这样的不能模糊查询啊,比如我一条记录里有 1,2,4 这样的那值应该保存的是7
我要查询包含2的记录,where flag & 2 就查询不了吧????

如果你仅仅4个标识('r', 'l', 'c', 'p') ,不如用 1 2 4 8 作为标识位,用例如 1|2 = 3 的方式来标识flag,查询的时候可以 where flag & 2 的方式,应该会快不少

我测试了下 where flag & 2 效果和 like '%l%' 效果差不多,效率并无提高啊

对于 set 类型字段 find_in_set 用的就是位运算
不过 like '%l%' 这样肯定是不可取的,如果是 like '%l,r%' 或 like '%l,p%' 不就什么都找不到了吗?
当然,无论是 like 还是 find_in_set 都是要遍历整个表的,不然就不知道哪条记录能匹配的上
set 类型是按长整形存储的,加上索引可能会快些

对于 set 类型字段 find_in_set 用的就是位运算
不过 like '%l%' 这样肯定是不可取的,如果是 like '%l,r%' 或 like '%l,p%' 不就什么都找不到了吗?
当然,无论是 like 还是 find_in_set 都是要遍历整个表的,不然就不知道哪条记录能匹配的上
set 类型是按长整形存储的,加上索引可能会快些

是啊,你说的对。我加了这个key kid(kid,flag) 复合索引,不知道这样索引对不对,目前是2.5秒左右的查询时间,我要优化到0.0几秒以内。没别的办法的话只能修改表结构了


对于 set 类型字段 find_in_set 用的就是位运算
不过 like '%l%' 这样肯定是不可取的,如果是 like '%l,r%' 或 like '%l,p%' 不就什么都找不到了吗?
当然,无论是 like 还是 find_in_set 都是要遍历整个表的,不然就不知道哪条记录能匹配的上
set 类型是按长整形存储的,加上索引可能会快些

是啊,你说的对。我加了这个key kid(kid,flag) 复合索引,不知道这样索引对不对,目前是2.5秒左右的查询时间,我要优化到0.0几秒以内。没别的办法的话只能修改表结构了

like "%xxx"这种形式的查询。.由于索引基本都是B树或者B+树,所以是不会使用索引的

like和find_in_set在查询上都会遍历整个表,对四十来万的数据也许是2.5秒,但是以后如果数据再多的话上百万的话,还会更慢,并且,这两个都不会使用索引的,所以,如果想要再提升效率的话,只能再根据业务需求,去看看能不能改善sql。

你还可以 EXPLAIN 看看 mysql 的建议

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a index kid PRIMARY 4 NULL 15 Using where
1 SIMPLE b eq_ref PRIMARY PRIMARY 3 mzrui.a.kid 1


用explain解析出来是这样的,有什么优化建议?

人气教程排行