IN子句不使用索引 [英] IN clause not using index
问题描述
这是表定义
CREATE TABLE `dt_prdtime` (
`TCompany` varchar(3) NOT NULL DEFAULT '',
`TPerCode` varchar(8) NOT NULL,
`TBegDateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'วันที่',
`TQPay` int(1) NOT NULL DEFAULT '2',
`TYear` int(4) NOT NULL,
`TMonth` int(2) NOT NULL,
PRIMARY KEY (`TCompany`,`TPerCode`,`TBegDateTime`),
KEY `TMonth` (`TMonth`) USING BTREE,
KEY `TPerCode` (`TPerCode`,`TYear`,`TMonth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
这是数据样本。此表有10000多条记录，其值在 TMonth 字段不同
And this is data sample. This table has 10000+ records and value in TMonth field varies
+----------+----------+---------------------+-------+-------+--------+
| TCompany | TPerCode | TBegDateTime | TQPay | TYear | TMonth |
+----------+----------+---------------------+-------+-------+--------+
| S10 | 000001 | 2016-01-02 17:33:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-02 07:48:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-03 17:39:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-03 07:30:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-04 17:49:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-04 07:54:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-05 17:50:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-05 07:36:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-06 17:37:00 | 1 | 2016 | 1 |
| S10 | 000001 | 2016-01-06 07:35:00 | 1 | 2016 | 1 |
+----------+----------+---------------------+-------+-------+--------+
使用 EXPLAIN ，此查询使用 TMonth index：
With EXPLAIN, This query uses TMonth index:
SELECT * FROM dt_prdtime WHERE TMonth = 5
while这个拒绝使用索引：
while this one refuses to use the index:
SELECT * FROM dt_prdtime WHERE TMonth IN (5,6)
我用另一个简单的表格测试过，
I tested with another simple table,
CREATE TABLE `table1` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM table2 WHERE id IN (5,6)
和索引使用此表
任何人都能解释一下吗？ dt_prdtime 表有什么问题吗？
Can anybody explain this? Is there something wrong with dt_prdtime table?
推荐答案
我会出去并且说这是因为你正在使用MyISAM引擎。
I will go out on a limb and say it is because you are using the MyISAM engine.
它与INNODB完全正常，可以在答案。
It is working perfectly fine with INNODB as can be seen in this Answer of mine.
我会尽量在这个问题上找到至少一个光荣的参考资料。
I will try to spook up at least 1 honorable reference on the matter.
这里，范围加入类型，显然是INNODB焦点，因为它是默认引擎。如果在某些文档层次结构的手册中没有明确提到，则假定它。
Here, The range Join Type, clearly an INNODB focus as it is the default engine. And when not explicitly mentioned in the manual in some documentation hierarchy, it is assumed.
注意，我的示例链接中的id没有任何连续性。这意味着，请勿在其EXPLAIN输出中对 type = range 进行超聚焦。速度是通过优化器（CBO）得出的。
Note, there is nothing contiguous about the id's in my example link. Meaning, don't hyperfocus on type=range in its EXPLAIN output. The speed is arrived at via the Optimizer (the CBO).
我的例子中的基数非常高（ 4.3百万）。目标ID计数相对较低（1000）。使用该索引。
The cardinality in my example is very high (4.3 Million). The target id counts are relatively low (1000). The index is used.
您的情况可能相反：您的基数可能非常低，如3，优化程序决定放弃使用索引。
Your situation may be the opposite: your cardinality might be incredibly low, like 3, and the optimizer decides to abandon use of the index.
要检查索引基数，请参阅手册页 SHOW INDEX语法。
To check your index cardinality, see the Manual Page SHOW INDEX Syntax.
一个简单的调用，如：
show index from ratings;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ratings | 0 | PRIMARY | 1 | id | A | 4313544 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
这篇关于IN子句不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
