索引无效——字符集惹的祸

今天郁闷了半天，一段SQL语句，在表上明明建了索引，但怎么都无法使用，总是在进行全表遍历，最终，在极度幸运的情况下，原因找到了，现分享一下经验吧，呵呵。

注：欢迎大家转载，但请注明出处，谢谢http://blog.csdn.net/xinghun61/archive/2010/07/19/5747344.aspx

 

背景介绍：

SQL语句很简单，只不过是2个表的内连接，表结构如下：

rs_mstr(分组关系表，用于对各种资料进行分类，例如，对物料类型进行分组统计等)

    rs_type - 分组类型，用于区分各种不同的分组方式

    rs_from - 组别，例如，原材料中的五金类、电子类、塑胶类等

    rs_to - 组别中的类别细分，例如，电子类可细分电阻、电容等

 

pt_mstr(物料编码基础表，存放各种物料的相关信息)

    pt_part - 物料编码

    pt_part_type - 物料类型，与上面的rs_to想对应，表示物料的类别细分

 

其中，表rs_mstr中的记录为386条，存放在名为Apps的数据库中；pt_mstr中的为44035条，存放在名为ERP的数据库中，在pt_mstr上，对pt_part_type建了索引，索引名也叫pt_part_type，SQL语句如下：

select pt_part from Apps.rs_mstr, ERP.pt_mstr where rs_type = "某种类型的取值" and rs_from = "某种物料类别" and rs_to = pt_part_type

 

问题分析：

首先，我们用explain语句来看看上面SQL语句的执行计划吧，如下图：

这个执行计划是不是有点看不懂，明明在pt_mstr上用到了pt_part_type索引，但为何还是在进行全表遍历呢？

毕竟，rs_mstr的数据量要少得多，非要全表遍历的话，也应该是在记录少的表上进行，然后，在数据量较大的表上，通过索引来加快查询速度，这样的执行计划才叫合理呀？

 

好了，对问题的分析就不再废话了，直接看下面的结果吧

上图分别是通过执行SQL语句——

show create table Apps.rs_mstr;

show create table ERP.pt_mstr;

所生成的结果(由于pt_mstr太大，我这里删除了很多字段)。

 

到这里，原因变得清晰了，原来pt_mstr在与rs_mstr做连接时，由于pt_mstr的字符集比较小，所以，为保证在字符集转换过程中不会丢失数据，MySQL总是将字符串从latin1转为utf8，所以，也难怪要对pt_mstr做全表遍历了。

 

SQL改进：

既然知道了原因，改起来就很简单了，用下面的SQL语句重新执行一下吧，

explain select pt_part from rs_mstr, ERP.pt_mstr where rs_type = "grouptype" and rs_from = "type1" and convert(rs_to using latin1) = pt_part_type

 

其执行结果如下图，

唉，问题解决了，世界也终于清静了，哈哈。。。