關於mysql set欄位類型的模糊查詢問題

有個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解析出來是這樣的，有什麼最佳化建議？

