blocks|key|2254287|text|我没有意识到这一点；所以先试用(Fiddle+http://sqlfiddle.com/#!2/f88bb/2)，是的，这是正确的；对于多列IN子句，它使用的是full+table+scan而不是可用的索引。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2254288|经过更多的搜索后，发现这是MySQL中的一个BUG，将在MySQL+6.x中发布或修复。|2254289|多列IN不使用索引的完整信息见这里|2254290|因此，现在您可以将查询转换为使用单个IN子句，以便它识别索引；|2254291|select+*+from+my_table+
where+col1+in+(+1000,2000)+and+col2+in+(1,2)|code-block|syntax|javascript|2254292|创建BUG/优化请求的人在这里解释了(或)给定的复制步骤|2254293|多列IN子句-意外的MySQL问题|2254294|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/f88bb/2|1|http://bugs.mysql.com/bug.php?id=35819|2|http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/^0|28|F|N|W|0|0|S|9|0|0|9|1|0|0|0|0|0|H|2|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|F|3|G|5|6|7|1C|8|@$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1F|8|@]|D|@$9|1G|A|1H|1|1I]]|E|$]]|$1|J|3|K|5|6|7|1J|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|1K|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|1L|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1M|8|@]|D|@$9|1N|A|1O|1|1P]]|E|$]]|$1|U|3|-4|5|6|7|1Q|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]|12|$5|X|Y|Z|E|$10|13]]|14|$5|X|Y|Z|E|$10|15]]]]

I wasn't not aware about this; so tried out first (Fiddle <a href="http://sqlfiddle.com/#!2/f88bb/2" rel="nofollow">http://sqlfiddle.com/#!2/f88bb/2</a>) and yes it's correct; for a multi column IN clause it's using <code>full table scan</code> instead of using the index available.

After some more search found that it's a BUG in MySQL which is targeted to be released or fixed in <code>MySQL 6.x</code>.

See here for a full info <a href="http://bugs.mysql.com/bug.php?id=35819" rel="nofollow">Multi column IN does not use index </a> 

So for now you can transform your query to use single IN clause so that it recognizes the index; like

<pre><code>select * from my_table 
where col1 in ( 1000,2000) and col2 in (1,2)
</code></pre>

Person who created the BUG/Optimization request has explained (OR) given reproduction steps here

<a href="http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/" rel="nofollow">Multi-Column IN clause – Unexpected MySQL Issue</a>

blocks|key|2531045|text|您需要MySql版本5.7.3或更高版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2531046|早期版本不为行构造函数表达式实现优化。|2531047|请按照此链接获取详细信息：https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-optimization|offset|length|2531048|8.2.1.3.4行构造表达式的范围优化
在MySQL+5.7.3中，优化器能够将范围扫描访问方法应用于此表单的查询：
选择。来自t1；其中(+col_1，col_2+)+IN+((+'a'，'b‘)，(+'c'，'d’)；
以前，要使用范围扫描，必须将查询编写为：
选择。来自t1，其中(+col_1+=+'a‘和col_2+=+'b’)+OR+(+col_1+=+'c‘和col_2+=+'d’)；|blockquote|style|BOLD|2531049|注意：行构造函数是表单的表达式：|2531050|(+value1,+value2,+...+,valueN)或ROW(+val1,+val2,+...,+valN).|CODE|2531051|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-optimization^0|0|0|D|2Q|0|0|0|F|0|3|5|0|0|U|V|R|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|11|8|@]|9|@$F|12|G|13|1|14]]|A|$]]|$1|H|3|I|5|J|7|15|8|@$F|16|G|17|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|18|8|@$F|19|G|1A|K|L]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1B|8|@$F|1C|G|1D|K|Q]|$F|1E|G|1F|K|Q]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]]]

You need MySql version 5.7.3 or above 
Earlier versions do not implement an optimization for row constructor expressions 
Please follow this link for details: <a href="https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-optimization" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-optimization</a>
<blockquote>
8.2.1.3.4 Range Optimization of Row Constructor Expressions
As of MySQL 5.7.3, the optimizer is able to apply the range scan access method to queries of this form:
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
Previously, for range scans to be used it was necessary for the query to be written as:
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );
</blockquote>
Note: a row constructor is an expression of a form: <code>( value1, value2, ... ,valueN)</code> or <code>ROW( val1, val2, ..., valN)</code>.

blocks|key|2531073|text|在不升级mysql版本的情况下，我设法解决了这个问题，只在其中一个列上添加了一个附加where：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2531074|select+*+from+my_table+
where+(col1,+col2)+in+(+(1000,1),+(2000,2)+)
and+col1+in+(1000,2000)|code-block|syntax|javascript|2531075|这样，优化器将在col1上找到索引，并按它进行搜索，这为我完成了这项工作。|2531076|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I managed to solve this without upgrading the mysql version, by adding an extra where in condition on one of the columns only:

<pre><code>select * from my_table 
where (col1, col2) in ( (1000,1), (2000,2) )
and col1 in (1000,2000)
</code></pre>

This way, the optimizer will find the index on col1, and will search by it, which did the job for me.

I have the following query:

<pre><code>select * from my_table 
where (col1, col2) in ( (1000,1), (2000,2) )
</code></pre>

I have indexes defined on col1, col2 and col1 and col2 together. If I run an explain on it, I see that mysql does not find an available index to use. Interestingly enough if I put only one pair in the IN clause, the explain finds all the indexes.

Tried force index, does not help

Any ideas, how can I make mysql recognize the index?

Mysql not using an index on where in with two columns

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有以下查询：select * from my_table where (col1, col2) in ( (1000,1), (2000,2) )我在col1、col2、col1和col2上定义了索引。如果我在上面运行解释，我会发现mysql找不到可用的索引来使用。有趣的是，如果我在in子句中只放了一对，那么解释就会...

问Mysql不使用包含两列的where索引
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql不使用包含两列的where索引EN