千万级的数据量查询方案

问题用户登录log表1000w以上查询，[统计未登陆用户]

 

2014-03-01 2016-03-31[也就到现在的全部数据]到现在 2700w

解决方案:1 二级索引,2 between and代替 大于小于  >  <

sql如下：

SELECT count(DISTINCT(uid)) FROM login_log WHERE  uid>=0   between 1396195200 and 1459353600

耗时：14.793s[第二次查询1s内，走query cache]

 

SELECT DISTINCT(uid) FROM login_log WHERE  uid>=0   between 1396195200 and 1459353600

耗时：1.414s

 

千万级log查全部 这个query时间还可以接受 

从60s以上减到 秒类 基本最优了。

 

[补充]

另外如果普通1000w级数据查询，基本都是取20条之类，分页等 这样query基本都可以到毫秒

 

结论

1.       没有任何条件的查询不一定走的是主键索引，mysql优化器会使用认为是最小代价的索引

2.       在count(*)的时候，采用主键索引比二级索引要慢，而且慢的原因不是因为两者的索引的长度不同

3.       Count(*)在没有查询条件的情况下，对innodb引擎的mysql会进行全表扫描，而myasm引擎的mysql无需进行全表扫描，因为myasm的引擎记录了每个表的多少记录。但是当有查询条件的时候，两者的查询效率一致。

4.       经过后来查询大量的资料，主键索引count(*)的时候之所以慢

l  InnoDB引擎

[1]     数据文件和索引文件存储在一个文件中，主键索引默认直接指向数据存储位置。

[2]     二级索引存储指定字段的索引，实际的指向位置是主键索引。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，所以主键索引统计没有二级索引效率高。

[3]     由于主键索引直接指向实际数据，所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。

l  MyAsm引擎

[1]     该引擎把每个表都分为几部分存储，比如用户表，包含user.frm，user.MYD和user.MYI。

[2]     User.frm负责存储表结构

[3]     User.MYD负责存储实际的数据记录，所有的用户记录都存储在这个文件中

[4]     User.MYI负责存储用户表的所有索引，这里也包括主键索引。