一个mysql优化技巧的误区

 关于sql优化技巧，大家可能见过N个版本，尤其容易博得初中级程序员的眼球。倘若没有一点分析实践能力，直接将其拿来当作圣经记在心中并实践于工作中，那你极有可能被掉坑。轻则代码运行转圈圈无响应，重则导致项目瘫痪造成经济损失。

        废话不多说，直接上图。

        上面这条技巧粗略看一眼好像也没有什么问题。可事实是这样的吗？

        

        结论当然是否定的。且看实例分析：

        

1 2 3 4 5 6 7 8 9 10 11 12

CREATE  TABLE  `t_auxiliary_info` (    `id`  int (11) unsigned  NOT  NULL  AUTO_INCREMENT,    `ac_id` tinyint(3) unsigned  NOT  NULL  COMMENT  '分类ID' ,    ` name `  varchar (250)  NOT  NULL  DEFAULT  ''  COMMENT  '名称' ,    `number`  smallint (6) unsigned  NOT  NULL  DEFAULT  '1'  COMMENT  '编号' ,    `attr`  varchar (500)  NOT  NULL  DEFAULT  ''  COMMENT  '属性' ,    `fdbid`  int (10) unsigned  NOT  NULL  COMMENT  '用户ID' ,    `status` tinyint(1) unsigned  NOT  NULL  DEFAULT  '1'  COMMENT  '状态:1有效,0无效' ,    `stock_type` tinyint(1) unsigned  NOT  NULL  DEFAULT  '0'  COMMENT  '存货类型：1库存商品，2原材料，3周转材料' ,    PRIMARY  KEY  (`id`),#请注意这里的索引    KEY  `uniq_cid_acid` (`fdbid`,`ac_id`) ) ENGINE=InnoDB AUTO_INCREMENT=645101  DEFAULT  CHARSET=utf8 ROW_FORMAT= DYNAMIC

    上面是一张普通的业务表，仔细看表中设置的索引：

1 2	PRIMARY  KEY  (`id`),#主键索引    KEY  `uniq_cid_acid` (`fdbid`,`ac_id`)#联合索引

        

        再使用上述的in 或not in 来实践以下，通过explain执行计划工具看看实际效果。（在这里为了公平起见，我不使用主键id，且in操作中的数据不是连续的。）

        

1 2 3

select  *  from  t_auxiliary_info  where  fdbid  in ( '1000' , '1500' , '1234' , '5155' , '6789' , '3423' , '5368' , '245645' );

        

1	在上面的sql中，我们使用包含在联合索引`uniq_cid_acid`中的字段 `fdbid`作为搜索条件

 

       见证奇迹的时刻到了。

    

        通过执行计划， 我们可以清晰的看到这条sql的检索类型为简单简单检索，属于范围查询，且已经使用到了索引  uniq_cid_acid，且没有全表扫描（扫描行数为2804，而本表中数据条数为645101）。

        

        由此可以得出结论：不是所有sql中的in查询会全表扫描。这里推翻了in会导致全表扫描的结论。

        那么在什么情况下，使用in操作一样可以使用到索引，不会全表扫描呢？

       答：  in的字段必须是带有索引的字段。

       ps：  in(...) 中的数据最好加上引号,即使字段类型是数字。

        在 看看not in        

1 2 3

select  *  from  t_auxiliary_info  where  fdbid  not  in (1000,1500,1234,5155,6789,3423,5368,245645);

   

        真相在这里：

not in确实会全表扫描。

本文转自 hgditren 51CTO博客，原文链接：http://blog.51cto.com/phpme/1916491，如需转载请自行联系原作者