mysql 效率 find_in_set 和 where in 以及 where or 的效率explain比较

最新推荐文章于 2024-07-23 12:00:40 发布

原创最新推荐文章于 2024-07-23 12:00:40 发布 · 2.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #优化 #explain

本文对比了在MySQL中，使用FIND_IN_SET、WHERE IN以及WHERE OR查询效率，实验结果显示，FIND_IN_SET效率最低，而WHERE IN和WHERE OR在相同条件下表现相当，建议尽量避免使用FIND_IN_SET。

需求

字段varchar 多个可能查询，使用where in/where or/ find_in_set不同查询比较。
符合条件记录数为 3735 条。

FIND_IN_SET

+----+-------------+----------------+------------+------+---------------+---------+---------+-------+---------+----------+-----------------------------------------------------------+
| id | select_type | table          | partitions | type | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                                                     |
+----+-------------+----------------+------------+------+---------------+---------+---------+-------+---------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | adjust_tracker | NULL       | ref  | idx_nca       | idx_nca | 152     | const | 2227895 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------------+------------+------+---------------+---------+---------+-------+---------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.03 sec)

WHERE IN

+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                                     |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | adjust_tracker | NULL       | range | idx_nca       | idx_nca | 454     | NULL | 13316 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

WHERE OR

+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                                     |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | adjust_tracker | NULL       | range | idx_nca       | idx_nca | 454     | NULL | 13316 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)