SQL優化,百萬級2張表關聯,從40分鐘到3秒的歷程 [sql] <strong>表結構如下:</strong> [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] <strong>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;</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值。