一个mysql优化的问题

<code>**log表结构如下**id int(10) primary key ＃主键ip varchar(32) ＃iptime int(10) ＃时间channel varchar(128) ＃渠道id 普通索引status int(10) ＃状态标识</code>

channel字段为几个渠道号channel01,channel02…channel20
status为不同的状态标识 比如1代表打开次数，2代表关闭次数等

需求是：
统计不同渠道 不同状态 每天的总ip数量
统计不同渠道 不同状态 每天的去重ip数量
统计不同渠道 不同状态 每天的新增ip数量(当天ip，且当天之前数据库中不存在的ip)

下面为统计渠道channel01，状态status=1的各项数量

目前的实现方法是：
比如2015-11-11当天的数量
先用php算出2015-11-11的凌晨时间戳$begin和2015-11-12的凌晨时间戳$end

<code>**A-- 当天ip总数 $total**SELECT COUNT(*) FROM log WHERE time>={$begin} AND time={$begin} AND time={$begin} AND time<{$end} AND status=1 AND channel=channel01 GROUP BY ip;**D-- 当天ip在当天之前出现过的数量  $before**SELECT COUNT(*) FROM log WHERE ip IN ($ip_str) AND time<{$begin} GROUP BY ip;**E-- 新增的ip数量**$new = $group - $before</code>

现在表中一共有52万条数据，每天新增大概3万条数据，去重后的也有2万多，在执行第四步（语句D）的时候执行时间为3秒左右

请问有没有什么办法可以优化这个sql语句，或者有没有其它的什么方法实现这个需求？

！本文来源gaodai#ma#com搞*!代#%^码网5

搞gaodaima代码

回复内容：

<code>**log表结构如下**id int(10) primary key ＃主键ip varchar(32) ＃iptime int(10) ＃时间channel varchar(128) ＃渠道id 普通索引status int(10) ＃状态标识</code>

channel字段为几个渠道号channel01,channel02…channel20
status为不同的状态标识 比如1代表打开次数，2代表关闭次数等

需求是：
统计不同渠道 不同状态 每天的总ip数量
统计不同渠道 不同状态 每天的去重ip数量
统计不同渠道 不同状态 每天的新增ip数量(当天ip，且当天之前数据库中不存在的ip)

下面为统计渠道channel01，状态status=1的各项数量

目前的实现方法是：
比如2015-11-11当天的数量
先用php算出2015-11-11的凌晨时间戳$begin和2015-11-12的凌晨时间戳$end

<code>**A-- 当天ip总数 $total**SELECT COUNT(*) FROM log WHERE time>={$begin} AND time={$begin} AND time={$begin} AND time<{$end} AND status=1 AND channel=channel01 GROUP BY ip;**D-- 当天ip在当天之前出现过的数量  $before**SELECT COUNT(*) FROM log WHERE ip IN ($ip_str) AND time<{$begin} GROUP BY ip;**E-- 新增的ip数量**$new = $group - $before</code>

现在表中一共有52万条数据，每天新增大概3万条数据，去重后的也有2万多，在执行第四步（语句D）的时候执行时间为3秒左右

请问有没有什么办法可以优化这个sql语句，或者有没有其它的什么方法实现这个需求？

你的这个表有很大问题。
IP不该用varchar(32)，你想后续查询时，这比较得多低效。通用的做法是用unsigned int配合inet_aton函数。
类似的channel字段，如果固定不变，可以用enum代替varchar. 在varchar(128)上建索引，没有比这更低效的了。实在不想用enum可以考虑对局部做索引，比如前12个字符，具体看情况。
time字段应该纳入索引。你建一个索引，包含三个字段（channel, status, time），顺序很重要，少的在前多的在后。

d中in的效率比较低，用所有ip减1天前所有ip，group by 默认显示最上面的一条数据，时间上可能还要排序吧

去重IP的SQL可以使用如下改进：
B) 当天ip去重数

<code>SELECT COUNT(DISTINCT ip)   FROM log  WHERE time >= {$begin}        AND time < {$end}        AND status = 1        AND channel = channel01;</code>

C) 当天ip去重列表

<code>SELECT DISTINCT ip  FROM log  WHERE time >= {$begin}        AND time < {$end}        AND status = 1        AND channel = channel01;</code>

D) 当天ip在当天之前出现过的去重数量 写法类似于B

这种问题应该用计数器来解决，尽量避免复杂逻辑查询，不然数据量足够多的时候很难搞的。
计数器可以直接用db或者memcache,redis之类来做。
或者每天定时跑脚本进行数据统计，实时查看mysql不合适

喜欢 (0)赏

[搞代码]

分享 (0)