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

bitsCN.com

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

 

[sql] 

表结构如下:  

[sql] 

CREATE TABLE `deviceback` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `imei` varchar(100) NOT NULL COMMENT '手机唯一标识',  

  `mid` varchar(50) DEFAULT NULL,  

  `mac` varchar(100) DEFAULT NULL,  

  `APNType` varchar(100) DEFAULT NULL,  

  `status` int(11) DEFAULT '0',  

  `ip` varchar(100) DEFAULT NULL,  

  `sn` varchar(100) DEFAULT NULL COMMENT '系列号',  

  `oem` varchar(100) DEFAULT NULL COMMENT '厂商',  

  `product` varchar(100) DEFAULT NULL COMMENT '产品',  

  `region` varchar(100) DEFAULT NULL COMMENT '区域',  

  `operator` varchar(100) DEFAULT NULL COMMENT '运营商',  

  `sim` varchar(100) DEFAULT NULL COMMENT 'sim卡号',  

  `push_time` timestamp NULL DEFAULT NULL COMMENT '第一次登陆时间',  

  `origin_version` varchar(100) DEFAULT NULL COMMENT '原始版本',  

  `province` varchar(100) DEFAULT NULL COMMENT '省份',  

  `provinceCode` varchar(100) DEFAULT NULL COMMENT '省份code',  

  `city` varchar(50) DEFAULT NULL COMMENT '城市',  

  `cityCode` varchar(50) DEFAULT NULL COMMENT '城市code',  

  `brands` varchar(50) DEFAULT '0',  

  `version` varchar(100) DEFAULT '0' COMMENT '客户端版本号',  

  `last_checktime` timestamp NULL DEFAULT NULL COMMENT '最后一次登录时间',  

  PRIMARY KEY (`id`),  

  FULLTEXT KEY `NewIndex1` (`imei`),  

  FULLTEXT KEY `NewIndex2` (`mid`),  

  FULLTEXT KEY `NewIndex3` (`product`),  

  FULLTEXT KEY `NewIndex4` (`brands`)  

) ENGINE=MyISAM AUTO_INCREMENT=6832460 DEFAULT CHARSET=utf8;  

[sql] 

CREATE TABLE `20130602_AppLog` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `imei` varchar(100) DEFAULT NULL,  

  `mid` varchar(50) NOT NULL DEFAULT '',  

  `status` char(1) NOT NULL DEFAULT '0',  

  `mac` varchar(100) DEFAULT 'NULL',  

  `sn` varchar(100) DEFAULT NULL,  

  `sim` varchar(100) DEFAULT NULL,  

  `coperator` varchar(100) DEFAULT NULL,  

  `version` varchar(100) DEFAULT NULL,  

  `logintime` datetime DEFAULT NULL,  

  `ip` varchar(20) DEFAULT NULL,  

  `origin_version` varchar(100) DEFAULT NULL,  

  `now_version` varchar(100) DEFAULT NULL,  

  `APNType` varchar(20) DEFAULT NULL,  

  `oem` varchar(20) DEFAULT NULL,  

  `product` varchar(100) DEFAULT NULL,  

  `region` varchar(100) DEFAULT NULL,  

  `operator` varchar(100) DEFAULT NULL,  

  PRIMARY KEY (`id`),  

  FULLTEXT KEY `NewIndex1` (`imei`),  

  FULLTEXT KEY `NewIndex2` (`mid`)  

) ENGINE=MyISAM AUTO_INCREMENT=3123866 DEFAULT CHARSET=utf8;  

 

SQL如下:

 

 

[sql] 

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;  

 

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值。

bitsCN.com