是否使用FIND

本文介绍了是否使用FIND_IN_SET()?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个当前看起来像的查询:

I have a query that currently looks like:

SELECT [column a], [column b], [column c], [column d] FROM [table] WHERE FIND_IN_SET(2, column d) ORDER BY [column a] DESC

[列d]的类型为varchar，其中包含一组数字(例如，3, 2, 4, 6, 1, 9).因此，基本上我想返回所有2在其数字集中的记录.但是，当我对上述查询执行EXPLAIN时，这是我的输出:

Where [column d] is of type varchar, and holds a set of numbers (ex, 3, 2, 4, 6, 1, 9). So basically I am trying to return all records where 2 is in its set of numbers. However, when I execute an EXPLAIN on the above query, this is my output:

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE [table] ALL NULL NULL NULL NULL 500000 Using where; Using filesort

在执行此查询期间，该查询似乎未使用任何索引. [column a]是主键，因此该列上已经有一个索引.有什么办法可以利用索引来使查询更快地运行吗?还是我可以通过另一种方法来提高此查询的性能?

This query does not seem to be using any indices during the execution of this query. [column a] is the primary key, so there is an index on that column already. Is there any way to utilize an index for this query to run faster? Or is there another way for me to improve the performance of this query?

推荐答案

替代:正确规范化模式.

Alternative: properly normalize the schema.

FIND_IN_SET是 not 可修改，索引不能.

FIND_IN_SET is not Sargable and the index cannot be used.