MYSQL优化实战

1.示例一：数据库 600万条记录
查询的条件：查询属于当天的记录
select * from table where condition
其中 日期字段是timestamp类型
方案一：select * from table where substring(datetime,1,10) = currtime limit 50
耗时：0.0010 秒

方案二：select datetime from serverlog where datetime < '2009-08-18 23:59:59'
and datetime > '2009-08-18 00:00:00' limit 50
耗时：0.0356s



示例二：
1.第一条SQL语句
select LocalIP,ForeignIP,substring(datetime,1,10) as date,15 as times,count(*) as count,
avg(loadavgLog) as avg_cpu,avg(substring_index(substring_index(memoryLog,'|',3),'|',-1)) as avg_MemoryFree,
avg(substring_index(substring_index(memoryLog,'|',1),'|',-1)) as avg_MemoryTotal,avg(ConnNum) as Avg_IpConn,
round(avg(substring_index(diskioLog,'|',1)),2) as Rio,round(avg(substring_index(substring_index(diskioLog,'|',2),'|',-1)),2) as Wio,
round(avg(substring_index(substring_index(diskioLog,'|',3),'|',-1)),2) as Wuse,round(avg(substring_index(diskioLog,'|',-1)),2) as uses,
round(avg(substring_index(substring_index(netLog,'|',1),';',-1)),2) as eth0,
round(avg(substring_index(substring_index(netLog,'|',-1),';',-1)),2) as eth1 
from serverlog
where foreignIP = '124.225.135.10' And substring(datetime,1,19)>'2009-09-30 14:00:00' And substring(datetime,1,19)<'2009-09-30 15:00:00'
group by foreignip

时间：75.6625 s

原因：datetime 本身是timestamp类型的。所以没有必要给它添加函数进来！

优化：select LocalIP,ForeignIP,datetime as date,15 as times,count(*) as count,
avg(loadavgLog) as avg_cpu,avg(substring_index(substring_index(memoryLog,'|',3),'|',-1)) as avg_MemoryFree,
avg(substring_index(substring_index(memoryLog,'|',1),'|',-1)) as avg_MemoryTotal,avg(ConnNum) as Avg_IpConn,
round(avg(substring_index(diskioLog,'|',1)),2) as Rio,round(avg(substring_index(substring_index(diskioLog,'|',2),'|',-1)),2) as Wio,
round(avg(substring_index(substring_index(diskioLog,'|',3),'|',-1)),2) as Wuse,round(avg(substring_index(diskioLog,'|',-1)),2) as uses,
round(avg(substring_index(substring_index(netLog,'|',1),';',-1)),2) as eth0,
round(avg(substring_index(substring_index(netLog,'|',-1),';',-1)),2) as eth1 



from serverlog


where foreignIP = '124.225.135.10' And datetime>'2009-09-30 15:00:00' And datetime<'2009-09-30 16:00:00'
group by foreignip

时间： 45s
count(*) 所花的时间更多了！
考虑将其去掉。

果然是这样的。好将count() 还有就是如果使用了MYSQL的内置函数的话是非常耗时间的。所以坚决不要使用内置的函数进行当WHERE 条件处理！