可以使 MySQL FIND_IN_SET 或等效物使用索引吗? [英] Can MySQL FIND_IN_SET or equivalent be made to use indices?

问题描述

如果我比较

explain select * from Foo where find_in_set(id,'2,3');+----+-------------+-------+------+--------------+------+---------+------+------+--------------+|身份证 |选择类型 |表|类型 |可能的密钥|键 |key_len |参考 |行 |额外 |+----+-------------+-------+------+--------------+------+---------+------+------+--------------+|1 |简单 |用户 |所有 |空 |空 |空 |空 |4 |使用 where |+----+-------------+-------+------+--------------+------+---------+------+------+--------------+

有了这个

explain select * from Foo where id in (2,3);+----+-------------+-------+-------+---------------+---------+---------+------+------+------------+|身份证 |选择类型 |表|类型 |可能的密钥|键 |key_len |参考 |行 |额外 |+----+-------------+-------+-------+---------------+---------+---------+------+------+------------+|1 |简单 |用户 |范围|主要 |主要 |8 |空 |2 |使用 where |+----+-------------+-------+-------+---------------+---------+---------+------+------+------------+

很明显FIND_IN_SET没有利用主键.

我想将上述查询放入存储过程中，以逗号分隔的字符串作为参数.

有什么办法可以让查询表现得像第二个版本一样，其中使用了索引，但不知道在编写查询时设置的 id 的内容?

解决方案

参考您的评论:

<块引用>

@MarcB 数据库已规范化，CSV 字符串来自 UI.为我获取以下人员的数据:101,202,303"

这个答案只关注用逗号分隔的那些数字.因为，事实证明，你根本没有在谈论 FIND_IN_SET.

是的，你可以实现你想要的.您创建了一个准备好的语句，它接受一个字符串作为参数，就像我的这个

目标达成.您可以使用类似于我之前在

If I compare

explain select * from Foo where find_in_set(id,'2,3');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | User  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

with this one

explain select * from Foo where id in (2,3);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | User  | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

It is apparent that FIND_IN_SET does not exploit the primary key.

I want to put a query such as the above into a stored procedure, with the comma-separated string as an argument.

Is there any way to make the query behave like the second version, in which the index is used, but without knowing the content of the id set at the time the query is written?

解决方案

In reference to your comment:

@MarcB the database is normalized, the CSV string comes from the UI. "Get me data for the following people: 101,202,303"

This answer has a narrow focus on just those numbers separated by a comma. Because, as it turns out, you were not even talking about FIND_IN_SET afterall.

Yes, you can achieve what you want. You create a prepared statement that accepts a string as a parameter like in this Recent Answer of mine. In that answer, look at the second block that shows the CREATE PROCEDURE and its 2nd parameter which accepts a string like (1,2,3). I will get back to this point in a moment.

Not that you need to see it @spraff but others might. The mission is to get the type != ALL, and possible_keys and keys of Explain to not show null, as you showed in your second block. For a general reading on the topic, see the article Understanding EXPLAIN’s Output and the MySQL Manual Page entitled EXPLAIN Extra Information.

Now, back to the (1,2,3) reference above. We know from your comment, and your second Explain output in your question that it hits the following desired conditions:

1. type = range (and in particular not ALL) . See the docs above on this.
2. key is not null

These are precisely the conditions you have in your second Explain output, and the output that can be seen with the following query:

explain 
select * from ratings where id in (2331425, 430364, 4557546, 2696638, 4510549, 362832, 2382514, 1424071, 4672814, 291859, 1540849, 2128670, 1320803, 218006, 1827619, 3784075, 4037520, 4135373, ... use your imagination ..., ...,  4369522, 3312835);

where I have 999 values in that in clause list. That is an sample from this answer of mine in Appendix D than generates such a random string of csv, surrounded by open and close parentheses.

And note the following Explain output for that 999 element in clause below:

Objective achieved. You achieve this with a stored proc similar to the one I mentioned before in this link using a PREPARED STATEMENT (and those things use concat() followed by an EXECUTE).

The index is used, a Tablescan (meaning bad) is not experienced. Further readings are The range Join Type, any reference you can find on MySQL's Cost-Based Optimizer (CBO), this answer from vladr though dated, with a eye on the ANALYZE TABLE part, in particular after significant data changes. Note that ANALYZE can take a significant amount of time to run on ultra-huge datasets. Sometimes many many hours.

Sql Injection Attacks:

Use of strings passed to Stored Procedures are an attack vector for SQL Injection attacks. Precautions must be in place to prevent them when using user-supplied data. If your routine is applied against your own id's generated by your system, then you are safe. Note, however, that 2nd level SQL Injection attacks occur when data was put in place by routines that did not sanitize that data in a prior insert or update. Attacks put in place prior via data and used later (a sort of time bomb).

So this answer is Finished for the most part.

The below is a view of the same table with a minor modification to it to show what a dreaded Tablescan would look like in the prior query (but against a non-indexed column called thing).

Take a look at our current table definition:

CREATE TABLE `ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thing` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5046214 DEFAULT CHARSET=utf8;

select min(id), max(id),count(*) as theCount from ratings;
+---------+---------+----------+
| min(id) | max(id) | theCount |
+---------+---------+----------+
|       1 | 5046213 |  4718592 |
+---------+---------+----------+

Note that the column thing was a nullable int column before.

update ratings set thing=id where id<1000000;
update ratings set thing=id where id>=1000000 and id<2000000;
update ratings set thing=id where id>=2000000 and id<3000000;
update ratings set thing=id where id>=3000000 and id<4000000;
update ratings set thing=id where id>=4000000 and id<5100000;
select count(*) from ratings where thing!=id;
-- 0 rows

ALTER TABLE ratings MODIFY COLUMN thing int not null;

-- current table definition (after above ALTER):
CREATE TABLE `ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thing` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5046214 DEFAULT CHARSET=utf8;

And then the Explain that is a Tablescan (against column thing):

这篇关于可以使 MySQL FIND_IN_SET 或等效物使用索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！