Heim >Backend-Entwicklung >PHP-Tutorial >一个mysql优化的问题

一个mysql优化的问题

WBOY
WBOYOriginal
2016-06-06 20:24:331340Durchsuche

<code>**log表结构如下**
id int(10) primary key #主键
ip varchar(32) #ip
time 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</code>

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

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

回复内容:

<code>**log表结构如下**
id int(10) primary key #主键
ip varchar(32) #ip
time 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</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 </code>

C) 当天ip去重列表

<code>SELECT DISTINCT ip
  FROM log 
 WHERE time >= {$begin} 
       AND time </code>

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

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

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn