SQLite两个大表联合查询的高效方法,比低效方法效率提升1000倍.docxVIP

SQLite 两个大表联合查询的高效方法，比低效方法 效率提升 1000 倍 （原创） 1、高效方法（效率提升上千倍，在 3700 万记录 1.6G 大小的数据库中查询时间能够有效控制到 100 毫秒，时间基本无感知）：利用 SELECT 子句先从大表中查询出符合条件的小表，再对小表进行连接 FROM InfoFile WHERE InfoFile.FileHashID IN (SELECT FileHashID FROM SegInfoSELECT A.FilePath, A.FileHashID, B.Info, B.InfoCount, A.FileSize, A.SegDateTime FROM (SELECT InfoFile.FilePath, InfoFile.FileSize, InfoFile.SegDateTime, InfoFile.FileHashID FROM InfoFile WHERE InfoFile.FileHashID IN (SELECT FileHashID FROM SegInfo WHERE SegInfo.Info=GUI GROUP BY SegInfo.FileHashID ORDER BY WHERE SegInfo.Info=GUI GROUP BY SegInfo.FileHashID ORDER BY COUNT(SegInfo.Info) DESC)) A COUNT(SegInfo.Info) DESC)) A INNER JOIN (SELECT SegInfo.Info, COUNT(SegInfo.Info) InfoCount, FileHashID FROM SegInfo WHERE COUNT(SegInfo.Info) InfoCount, FileHashID FROM SegInfo WHERE SegInfo.Info=GUI GROUP BY SegInfo.FileHashID ORDER BY COUNT(SegInfo.Info) SegInfo.Info=GUI GROUP BY SegInfo.FileHashID ORDER BY COUNT(SegInfo.Info) DESC) B DESC) B ON B.FileHashID = A.FileHashID ORDER BY B.InfoCount DESC; 结果如下：Run Time: real 0.104 user 0.031250 sys 0.000000 2、低效方法：直接在两个表中进行WHERE 条件查询 SELECT InfoFile.FilePath, COUNT(SegInfo.Info), InfoFile.FileSize, InfoFile.SegDateTime FROM SegInfo, InfoFile WHERE SegInfo.Info=GUI AND InfoFile.FileHashID=SegInfo.FileHashID GROUP BY SegInfo.FileHashID ORDER BY COUNT(SegInfo.Info) DESC; 结果如下：Run Time: real 98.039 user 12.562500 sys 12.031250 3、低效方法：在大表基础上直接使用JOIN 子句，效率与 WHERE 直接查大表的性能是一样的，并不像其它数据库可能存在巨大的效率差 SELECT InfoFile.FilePath, COUNT(SegInfo.Info), InfoFile.FileSize, InfoFile.SegDateTime FROM InfoFile LEFT JOIN SegInfo ON (SegInfo.FileHashID = InfoFile.FileHashID AND SegInfo.Info=GUI ) GROUP BY SegInfo.FileHashID ORDER BY COUNT(SegInfo.Info) DE结SC; 果如下：Run Time: real 102.060 user 15.875000 sys 11.343750