MySQL5.7 Find_In_Set替代方案

Question

我有一个包含来自不同国家的用户信息的表格。信息：Name，Country，LastLoginDate，IsUpdated，Certification。

这个表有150万行。为了过滤这一点，我有一个存储过程，它将输入作为- UserNames = 'John, Matt, Robin'。

这个查询看起来像是-

SELECT Name, Country, LastLoginDate FROM Users
WHERE find_in_set(Users.Name, UserNames) <> 0
;

EXPLAIN声明说，它有type - ALL，不使用任何索引。有什么方法可以使用文本并创建一个带有输入值的表并连接到我的核心表中，这将有助于使用索引和更快的输出吗？

Answer 1

您可以拆分搜索参数并使用联接。示例

-- Some data to test with
CREATE TABLE testdata ( x varchar(100) not null primary key );
INSERT INTO testdata (x) VALUES ('a'),('c'),('e');

-- This can be a temporary table, add all numbers up to
-- the maximum number of tokens in parameter string
CREATE TABLE positions (pos int not null primary key);
INSERT INTO positions (pos) VALUES (1),(2),(3),(4),(5);

-- Table that hold tokens, this can also be a temporary table
CREATE TABLE tokens (token varchar(100) not null primary key);

-- Split the search string in individual tokens, and store in table
INSERT INTO tokens
SELECT substring_index(
           substring_index(T.tokens, ',', p.pos)
         , ','
         , -1
       ) as token
FROM (SELECT 'a,b,c,d' as tokens) as T -- 'a,b,c,d' is the search string
JOIN positions p
  ON char_length(T.tokens) 
    - char_length(replace(T.tokens, ',', '')) 
    >= p.pos - 1;

-- Get all testdata that matches search string
SELECT t.*
FROM testdata t
JOIN tokens tt
    ON t.x = tt.token;

x
-
a
c

Answer 2

对于百万行，不要使用

WHERE find_in_set(Name, 'Leslie,Dana,Ricky') <> 0

因为INDEX(Name)帮不上忙。

相反，使用

WHERE Name IN ('Leslie', 'Dana', 'Ricky')

还有那个索引。

至于构造该字符串，我建议在应用程序代码中进行，而不是在SQL中。