当前位置:Gxlcms > mysql > SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

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

时间:2021-07-01 10:21:17 帮助过:6人阅读

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

人气教程排行