[转] MySQL中in（常量列表）的执行计划

我们在写sql的时候，经常用到in，in后面跟一堆常量列表，如id。有人说in的效率很高，而有人说很低；有人说in能使用索引，还有人说in不能使用索引。。。
到底是一个怎样的情况呢？我们分析以下几种情况
在这之前，我们先了解一下explain的几种type类型（本次分析即参照type类型），按照性能从高到低：

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

一，in后面只有1个值
1.1 对于主键或者唯一索引，那么type=const，这种性能最高，表示表中只有1个记录能满足查询



1.2 对于普通索引、或者联合主键，type=ref


1.3 对于普通字段，type=all，这种性能最差



二，in后面多余1个值，但少于某一个值，这个值具体是多少，之后会揭晓。
这时，不管是主键，还是唯一索引，还是普通索引，type=range


但是在这里需要注意的一个特例是：当你的索引的Cardinality属性比较低时，type=all，意思就是这个索引的区分度很低，建立的意义不大，
这时他的执行计划type=all，mysql认为走这个索引还不如全表扫描：

到底Cardinality处于什么水平时，性能最好？一般认为这个值越接近count(*)，性能最好。而对于像性别这种字段，就没必要加索引了。




三，相对于第二种情况，当in后面的值多于某一个值，会导致扫描全表。这个经验值我目前不能确定，咨询过相关DBA，他们也不能给出其经验值
3.1 test1表总共27条数据，当in后面的值少于8个时，type=range，而当超过8个时，type=all，如下：





3.2 这个t_word_cost表，总共有38244条数据，word_id上有索引，我测试了一把，当in后面不超过5千多或者6千多时，type=range，这是什么意思？
因为令我不解的是，这个值还不确定，它是波动的，我执行了好多次，有时候是5千多，有时候是6千多，或者其他值

通过对test1、t_word_cost表进行测试，我确实没有找出规律来，我原来妄想，通过大量实践得出一个经验值，然后通过经验值来判断到底in后面的个数占count(*)百分比多少的时候，能走索引，看来我徒劳了。

既然不能得出经验值，那我们只有在实际应用环境下具体选择解决方案了。
譬如我这次操作t_word_cost表，in后面值的个数都超过count(*)了，如果一次性全部写进in后面，一次查询所耗时间是30-40s左右！
根据上面我分析的结果，貌似我应该选择5000作为临界值，然后分批、多次查询，这样性能应该最高。但实际情况是这样吗？
通过我在程序中不断地人肉测试发现，并不是5000耗时最少，选择2000或者2500时，总体耗时最少，至于为什么，可能与内存、频繁的数据库连接有关吧，
因为我们知道，内存、IO都会影响整体性能，所以怎样平衡这个度需要自己把握。

 

转自：http://blog.itpub.net/28912557/viewspace-1255568/