今天郁闷了半天,一段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语句如下:
问题分析:
首先,我们用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语句重新执行一下吧,
其执行结果如下图,
唉,问题解决了,世界也终于清静了,哈哈。。。