程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> SQL優化,百萬級2張表關聯,從40分鐘到3秒的歷程

SQL優化,百萬級2張表關聯,從40分鐘到3秒的歷程

編輯:MySQL綜合教程

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved