SQL優化,百萬級2張表關聯,從40分鐘到3秒的歷程
表結構如下:
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;
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如下:
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*
FROMdevicebackd,20130602_AppLogg
WHEREd.mid=g.mid
ANDd.imei=g.imei
ANDd.mac=g.mac
ANDd.brands=0limit20;
去掉<>條件試試看,God,結果是37秒就出來了,好,大概問題找到了,出在<>這條判斷語句裡面,也就是
AND g.coperator <> '' 這個影響還蠻大的。
FROMdevicebackd,20130602_AppLogg
WHEREd.mid=g.mid
ANDd.imei=g.imei
ANDd.mac=g.mac
ANDd.brands=0limit20;
去掉<>條件試試看,God,結果是37秒就出來了,好,大概問題找到了,出在<>這條判斷語句裡面,也就是
AND g.coperator <> '' 這個影響還蠻大的。
那就先在20130602_AppLog表的coperator欄位單獨建索引試試看,然後查下20130602_AppLog裡面g.coperator<>''的有多少?
結果是比較令人欣慰的,單獨查詢,一秒不到出來結果了,6W多條紀錄。
結果是比較令人欣慰的,單獨查詢,一秒不到出來結果了,6W多條紀錄。
SELECT COUNT(1)
FROM 20130602_AppLog gg
WHERE gg.coperator <> ''
63987
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;
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 <> '' ;
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 <> '' ;
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值。
相關文章
- 百萬級資料庫優化資料庫優化
- 資料庫SQL優化大總結之 百萬級資料庫優化方案資料庫SQL優化
- 資料庫SQL優化大總結之百萬級資料庫優化方案資料庫SQL優化
- 通過圖表簡化sql語句的表關聯SQL
- sql語句左連結left join--3張表關聯SQL
- 執行長達2分多鐘的大型sql優化SQL優化
- mysql三張表關聯查詢MySql
- Hibernate級聯關係的說明和優化優化
- db2 sql批量插入一張表插入另一張表DB2SQL
- mysql從一張表中取出資料插入到另一張表MySql
- MySQL INNER JOIN關聯多張表的寫法MySql
- [MySQL View]最有意思的檢視view優化過程,從30分鐘到0.08秒MySqlView優化
- SQL優化之多表關聯查詢-案例一SQL優化
- 達夢SQL優化-回表BLKUP2SQL優化
- Angular 2 升級 Angular 4 歷程Angular
- 百萬級別資料Excel匯出優化Excel優化
- 百萬推薦關係優化實戰優化
- 一條sql的優化過程SQL優化
- sql優化相關SQL優化
- 更新一張與另一張表關聯的連線欄位記錄
- SQL優化(二)(聯合索引的使用)SQL優化索引
- mysql 關於大資料量日誌表的優化過程MySql大資料優化
- 關於關聯查詢sql的一次最佳化過程及其他SQL
- 從谷歌面試翻車到offer收割的心路歷程谷歌面試
- 效能優化部分——高階SQL優化2優化SQL
- 關於SQL優化的闢謠SQL優化
- 關於sql語句的優化SQL優化
- Update 多個關聯表SQL的寫法SQL
- SQL三表左關聯查詢SQL
- 兩表關聯查詢:sql、mybatisSQLMyBatis
- SQL優化常用方法2SQL優化
- 【SQL 資料庫】將一張資料表資訊複製到另一張資料表SQL資料庫
- mysql關聯查詢優化MySql優化
- 程式的一生:從源程式到程式的辛苦歷程
- 一條SQL語句的優化過程SQL優化
- 轉摘_關於sql tuning優化__何為回表SQL優化
- MemCached Cache Java Client封裝優化歷程Javaclient封裝優化
- SQL優化案例-單表分頁語句的優化(八)SQL優化