SQL优化,百万级2张表关联,从40分钟到3秒的历程

  1. <strong>表结构如下:</strong>  
  1. CREATE TABLE `deviceback` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `imei` varchar(100) NOT NULL COMMENT '手机唯一标识',  
  4.   `mid` varchar(50) DEFAULT NULL,  
  5.   `mac` varchar(100) DEFAULT NULL,  
  6.   `APNType` varchar(100) DEFAULT NULL,  
  7.   `status` int(11) DEFAULT '0',  
  8.   `ip` varchar(100) DEFAULT NULL,  
  9.   `sn` varchar(100) DEFAULT NULL COMMENT '系列号',  
  10.   `oem` varchar(100) DEFAULT NULL COMMENT '厂商',  
  11.   `product` varchar(100) DEFAULT NULL COMMENT '产品',  
  12.   `region` varchar(100) DEFAULT NULL COMMENT '区域',  
  13.   `operator` varchar(100) DEFAULT NULL COMMENT '运营商',  
  14.   `sim` varchar(100) DEFAULT NULL COMMENT 'sim卡号',  
  15.   `push_time` timestamp NULL DEFAULT NULL COMMENT '第一次登陆时间',  
  16.   `origin_version` varchar(100) DEFAULT NULL COMMENT '原始版本',  
  17.   `province` varchar(100) DEFAULT NULL COMMENT '省份',  
  18.   `provinceCode` varchar(100) DEFAULT NULL COMMENT '省份code',  
  19.   `city` varchar(50) DEFAULT NULL COMMENT '城市',  
  20.   `cityCode` varchar(50) DEFAULT NULL COMMENT '城市code',  
  21.   `brands` varchar(50) DEFAULT '0',  
  22.   `version` varchar(100) DEFAULT '0' COMMENT '客户端版本号',  
  23.   `last_checktime` timestamp NULL DEFAULT NULL COMMENT '最后一次登录时间',  
  24.   PRIMARY KEY (`id`),  
  25.   FULLTEXT KEY `NewIndex1` (`imei`),  
  26.   FULLTEXT KEY `NewIndex2` (`mid`),  
  27.   FULLTEXT KEY `NewIndex3` (`product`),  
  28.   FULLTEXT KEY `NewIndex4` (`brands`)  
  29. ) ENGINE=MyISAM AUTO_INCREMENT=6832460 DEFAULT CHARSET=utf8;  
  1. CREATE TABLE `20130602_AppLog` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `imei` varchar(100) DEFAULT NULL,  
  4.   `mid` varchar(50) NOT NULL DEFAULT '',  
  5.   `status` char(1) NOT NULL DEFAULT '0',  
  6.   `mac` varchar(100) DEFAULT 'NULL',  
  7.   `sn` varchar(100) DEFAULT NULL,  
  8.   `sim` varchar(100) DEFAULT NULL,  
  9.   `coperator` varchar(100) DEFAULT NULL,  
  10.   `version` varchar(100) DEFAULT NULL,  
  11.   `logintime` datetime DEFAULT NULL,  
  12.   `ip` varchar(20) DEFAULT NULL,  
  13.   `origin_version` varchar(100) DEFAULT NULL,  
  14.   `now_version` varchar(100) DEFAULT NULL,  
  15.   `APNType` varchar(20) DEFAULT NULL,  
  16.   `oem` varchar(20) DEFAULT NULL,  
  17.   `product` varchar(100) DEFAULT NULL,  
  18.   `region` varchar(100) DEFAULT NULL,  
  19.   `operator` varchar(100) DEFAULT NULL,  
  20.   PRIMARY KEY (`id`),  
  21.   FULLTEXT KEY `NewIndex1` (`imei`),  
  22.   FULLTEXT KEY `NewIndex2` (`mid`)  
  23. ) ENGINE=MyISAM AUTO_INCREMENT=3123866 DEFAULT CHARSET=utf8;  


SQL如下:


 

  1. <strong>SELECT *  
  2. FROM   deviceback d,20130602_AppLog g  
  3. WHERE d.mid=g.mid  
  4.       AND d.imei=g.imei  
  5.       AND d.mac=g.mac  
  6.       AND d.brands=0  
  7.       AND g.coperator <> '' limit 20;</strong>  


explain

结果:

从这里可以看到ALL以及key为NULL,就是全表扫描,没有走索引,索引失效了!

 

1 然后我建议建上加上联合索引,试试看效果如何:

 

 

2 效果还是比较慢的,看来得换种办法了,检查所有关联字段,将为null的数据改成''。还是没有效果。

 

3  不查*了,直接count(*)  看看结果集大小,结果还是卡住了,短时间内没有出查询结果。

 

4,到这里我猜测估计是数据的构成问题,那就一个个条件去掉去尝试了。

SELECT *
FROM   deviceback d,20130602_AppLog g
WHERE d.mid=g.mid
      AND d.imei=g.imei
      AND d.mac=g.mac
      AND d.brands=0 limit 20;
去掉<> 条件试试看,God,结果是37秒就出来了,好,大概问题找到了,出在<>这条判断语句里面,也就是
AND g.coperator <> '' 这个影响还蛮大的。
 
那就先在20130602_AppLog 表的coperator字段单独建索引试试看,然后查下 20130602_AppLog里面 g.coperator <> ''的有多少?
结果是比较令人欣慰的,单独查询,一秒不到出来结果了,6W多条纪录。
 SELECT COUNT(1) 
       FROM 20130602_AppLog gg
       WHERE gg.coperator <> ''
63987
 
然后再试试整个sql,看需要多长时间。
先看下explain结果:
 
 
SELECT *
FROM   deviceback d,20130602_AppLog g
WHERE d.mid=g.mid
      AND d.imei=g.imei
      AND d.mac=g.mac
      AND d.brands=0
      AND g.coperator <> '' limit 20;
Great,不到3秒就出来结果了。
 
5,limit 20是OK了,我还想试试不限制limit的话,要多久可以查询出来结果。
直接执行
SELECT *
FROM   deviceback d,20130602_AppLog g
WHERE d.mid=g.mid
      AND d.imei=g.imei
      AND d.mac=g.mac
      AND d.brands=0
      AND g.coperator <> '' ;
卡住了,很久都没有出来结果,在想是否是数据的问题?切换下where条件后面的字段顺序试试看。
 
 
SELECT d.mid,g.mac,
FROM   deviceback d,20130602_AppLog g
WHERE 
       d.imei=g.imei
      AND d.mac=g.mac
      AND d.mid=g.mid
      AND d.brands=0
      AND g.coperator <> '' ;

 OK,27秒出来了,AND d.mid=g.mid这个之前在第一条,现在放在后面,原因是手机唯一标示这个字段有空值。

God,最讨厌关键业务字段null值了,给我们的优化工作带来巨大的烦恼。

 

切记:大家以后千万要记住关键业务字段不能允许录入null值。

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值