记一次SQL优化——FIND_IN_SET函数
背景
数据库：MySQL 5.7.20
数据库管理工具：DBeaver
今天在检查数据库慢查询日志时，看到了这么一条统计SQL（数据已做脱敏处理）
SELECT count(left_type) # 脱敏处理，不用在意业务逻辑
FROM temp t
LEFT JOIN company c ON t.cid = c.id
WHERE FIND_IN_SET(t.kid, '1001103,1001100,1000071,…中间省略500多字符…,1001074,1001073,1001072')
AND FIND_IN_SET(t.sho_id, '11183,11474,11336,11034,11189,11260,…中间省略2000多字符…,11410,11370,11108');执行结果如下：
整条SQL执行了377毫秒，不符合执行小于200ms的要求，现在对这条SQL进行优化。
优化思路
首先，需要确定，SQL中使用FIND_IN_SET函数对查询列进行操作，应该会导致索引失效。
为了检查当前SQL是否导致索引失效，使用Explain关键字查看SQL的执行计划，结果如下：
确实，这里因为使用函数对字段进行运算，导致查询执行了全表扫描。
值得注意的是，这里的filtered字段值为100%，也就是说
存储引擎返回的数据在server层过滤后,剩下满足查询条件的比例为100%
理论上这个值是越大越好的，但是注意，这并不代表filtered越大，效率越高
优化步骤
为了让查询可以正确地走索引，这里把整个查询改为 使用 IN 来过滤字段，如下：
SELECT count(left_type)
FROM temp t
LEFT JOIN company c ON t.cid = c.id
WHERE t.kid IN(1001103,1001100,1000071,…中间省略500多字符…,1001074,1001073,1001072)
AND t.sho_id IN(111183,11474,11336,…中间省略2000多字符…,11410,11370,11108);这里需要注意，当前字段和IN括号中的类型是否匹配，如果不匹配，会发生隐式的类型转换，也可能导致索引失效。
这里确定查询字段都是数字类型，和括号内的类型保持一致，不会发生隐式转换。
查看当前SQL的执行计划：
可以看到这里依然没有使用索引，但是再次执行SQL,会发现执行时间由原来的377ms缩短到了42ms !!
猜测FIND_IN_SET低效原因
FIND_IN_SET(str,strlist)Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 # 代表'a,b,c,d'这个字符串列表的第 2 个子串由 'b' 组成翻译:如果字符串str在字符串列表中，则返回1至n范围内的值，表示子串出现在字符串列表中的位置。
字符串列表是由由字符分开的子字符串组成的字符串。
如果第一个参数是常数字符串，而第二个是类型集的列，则优化了Find_in_set（）函数以使用bit算术。
如果Str不在Strlist中，或者Strlist是空字符串,则返回0。
如果任何一个参数为null，则返回null。
如果第一个参数包含逗号（，）字符，则此函数无法正常工作。
可以看到这个方法主要是用于判断字符串中是否包含子串的方法，再反观最初SQL：
SELECT count(left_type) # 脱敏处理，不用在意业务逻辑
FROM temp t
LEFT JOIN company c ON t.cid = c.id
WHERE FIND_IN_SET(t.kid, '1001103,1001100,1000071,…中间省略500多字符…,1001074,1001073,1001072')
AND FIND_IN_SET(t.sho_id, '11183,11474,11336,…中间省略2000多字符…,11410,11370,11108');在这个最初的SQL中，由于`kid`和`sho_id`都是数字类型，而这里使用的`FIND_IN_SET`函数，导致在全表扫描的同时，还不断对查询的字段进行类型转换，再去和字符串列表的值进行比对，从而导致效率低下。
虽然整张表只有3.5w行左右的数据，但是当进行全表扫描的时候，`FIND_IN_SET`造成的效率下降还是非常明显。
索引是否会提高效率？
从另外一个角度来说，如果查询的字段存在索引，是否`FIND_IN_SET`是否可以走索引从而提高效率呢？
为了验证是否可以走索引，我们在查询条件的两个字段上创建一个联合索引
CREATE INDEX idx_kid_sho_id ON temp(kid,sho_id);
SHOW INDEX from usertemp;
Table |Non_unique|Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
--------+----------+--------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+
usertemp| 0|PRIMARY | 1|id |A | 36108| | | |BTREE | | |
usertemp| 1|idx_kid_sho_id| 1|kid |A | 68| | |YES |BTREE | | |
usertemp| 1|idx_kid_sho_id| 2|sho_id |A | 797| | |YES |BTREE | | |此时再查看使用`FIND_IN_SET` 的SQL执行计划：
可以看到`FIND_IN_SET`无法走索引，同样的`IN`此时也无法走索引
强制使用索引
为了测试索引是否可行，下面将尝试强制使用新创建的联合索引
explain SELECT count(left_type) # 脱敏处理，不用在意业务逻辑
FROM temp t FORCE index(idx_kid_sho_id) # 强制走联合索引
LEFT JOIN company c ON t.cid = c.id
WHERE FIND_IN_SET(t.kid, '1001103,1001100,1000071,…中间省略500多字符…,1001074,1001073,1001072')
AND FIND_IN_SET(t.sho_id, '11183,11474,11336,11034,11189,11260,…中间省略2000多字符…,11410,11370,11108');
可以通过执行计划看到，使用`FIND_IN_SET`尝试强制走索引，然而并不能成功，且执行时间也依然是370ms左右
接着对使用`IN`的SQL强制走索引
EXPLAIN SELECT count(left_type)
FROM temp t FORCE index(idx_kid_sho_id)
LEFT JOIN company c ON t.cid = c.id
WHERE t.kid IN(1001103,1001100,1000071,…中间省略500多字符…,1001074,1001073,1001072)
AND t.sho_id IN(111183,11474,11336,…中间省略2000多字符…,11410,11370,11108);
经过强制走索引后，发现匹配的行数变多了，而且执行时间由原来的40ms上升到了110ms左右！
可能的原因：索引创建不当，其中的kid和sho_id在查询的temp主表中，具有较强的重复性，强制走复合索引反而增加笛卡尔积，且增大回表查询的消耗（Using index condition），其效率反而不如全表扫描
对于这种总行数不多（3.5w），或者数据表量在初期阶段较小的场景下，可能会出现使用索引效率低于全表扫描的情况
拓展：`In`和`Exists`应该用哪个
先说结论：`IN`适合于外表大而内表小的情况，而`EXISTS`适合于外表小而内表大的情况。
也就是，如果子查询与外查询结果集大小相差较大，则子查询大的用Exists，子查询小的用IN。
如果子查询与外查询的结果集大小基本一致，那么用IN和Exists差别不大（是吗？往下看）
举个栗子，对于在某个子查询的结果集中进行筛选，一般有两种写法：
# Exists方式
select count(*) from temp t
where exists(select 1 from company c where c.id=t.cid);
# In方式
select count(*) from temp
where cid in(select id from company);在这个案例中，
-
如果外部查询
temp表数据量比company（子查询表）大，那么应该使用IN -
如果外部查询
temp表数据量比company（子查询表）小，那么应该使用Exists
原因：
-
Exists执行顺序如下： （子查询结果集较大时使用）
-
首先执行一次外部查询
-
对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。
-
使用子查询的结果来确定外部查询的结果集。（如果外部查询返回100行，SQL 就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。但实际上，SQL的查询 优化器有可能会找到一种更好的方法来执行相关子查询，而不需要实际执行101次查询。）
-
-
IN的执行过程如下：（子查询结果集较小时使用）
-
首先运行子查询，获取子结果集
-
主查询再去结果集里去找符合要求的字段列表，.符合要求的输出,反之则不输出
-
MySQL官网有一篇文档，用于介绍使用exists优化子查询，大致是讲：
在某些情况下,使用EXISTS子查询可以比直接使用IN子查询更高效。
具体来说,应该在以下情况使用EXISTS子查询:
- 单表查询中,使用EXISTS替代IN子查询。因为EXISTS子查询只要找到一条匹配的记录就会立即返回结果,无需扫描完整个子查询结果集。
- 对于非常大的子查询结果集,使用EXISTS子查询可以极大提高效率。因为EXISTS只在找到第一条匹配记录后就停止扫描,而IN子查询需要扫描完整个结果集。
- EXISTS子查询可以利用索引更加高效。对子查询中的表添加索引可以大大提高EXISTS子查询的效率。
- 当子查询结果集只返回一列时,使用EXISTS效率更高。因为它只关心存在与否,而不需要返回实际的值。
- 当外面的查询只需要知道子查询是否至少返回一行,而不需要实际的查询结果时,使用EXISTS效率更高。
总结下来：当子查询结果集非常大,并且外查询只关心存在性而不需要实际的结果值时,使用EXISTS子查询可以获得更好的性能优化
对应当前优化的SQL来说，由于已知的子查询结果集较小，所以使用IN效率会更高
原因汇总
使用FIND_IN_SET函数对数字类型进行查询,效率会远低于使用IN子查询的主要原因是:
FIND_IN_SET是字符串操作函数,它接收两个参数,第一个是要查找的字符串,第二个是包含要查找字符串的字符串组。所以使用FIND_IN_SET,MySQL会先将数字转换为字符串,再去字符串组中匹配,这会额外消耗资源。IN子查询可以利用索引,FIND_IN_SET不能利用索引。IN子查询可以直接使用索引快速判断查询列是否在候选列表中,而FIND_IN_SET每次都需要逐个字符串匹配,无法利用索引。FIND_IN_SET是逐个字符串匹配,匹配速度较慢。而IN子查询可以将候选列表预处理为Hash表,然后直接Hash查找,速度更快。FIND_IN_SET需要扫描整个候选列表字符串才能匹配成功或失败,而IN子查询使用索引可以很快判断匹配成功或失败,无需全表扫描。
所以,总的来说,FIND_IN_SET作为字符串操作函数,无法很好地支持数字类型参数,并且无法利用索引等MySQL的优化功能。相比之下,IN子查询可以充分利用索引和MySQL的查询优化,速度自然远超FIND_IN_SET函数。
在实际SQL查询中,如果参数类型为数字,我们应该尽量使用IN子查询,而不要使用FIND_IN_SET函数,这可以大大提高查询效率。
结论
在使用FIND_IN_SET函数对非字符串进行范围查询时，会导致查询列需要进行类型的隐式转换，从而引发查询效率低的问题