返回
编程
分类

必威官网亚洲体育查看SQL执行计划,但数据库中的存的时间是时间戳

日期: 2020-01-02 08:08 浏览次数 : 147

mysql中利用DATE_FORMAT方法实现统计每日/每月的新增用户

要实现统计每日或每月的新增用户,但数据库中的存的时间是时间戳,所以就要利用DATE_FORMAT函数

数据库表存的数据:

必威官网亚洲体育 1

要统计每日的新增用户 ,就要把时间戳格式的时间FORMAT成年-月-日的格式,并按照format过的字段做分组(group by),计算count

sql语句:

 

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as time , count(*) as count FROM tb_users GROUP BY  time

查询结果:

 

必威官网亚洲体育 2

DATE_FORMAT的具体用法

 

要实现统计每日或每月的新增用户,但数据库中的存的时间是时间戳,所以就要利用...

mysql有个字段是DATETIME类型,要实现可以按月统计,该怎么写sql语句?
select month(f1) from tt group by month(f1)
or select DATE_FORMAT(f1,'%m') from tt group by DATE_FORMAT(f1,'%m')

 

比如数据库的为2008-01-15 12:10:00
则DATE_FORMAT的参数格式分别得到的结果为:
'%Y' 2008
'%Y-%m' 2008-01
'%Y-%c' 2008-1
'%m' 01
'%c' 1

 

Date_format格式说明:
格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微妙
必威官网亚洲体育 ,%H 小时(00-23)
%h 小时(01-12)
%I 小时(01-12)
%i 分钟,数值(00-59)
%j 年的天(001-366)
%k 小时(0-23)
%l 小时(1-12)
%M 月名
%m 月,数值(00-12)
%p AM或PM
%r 时间,12-小时(hh:mm:ss AM或PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时(hh:mm:ss)
%U 周(00-53)星期日是一周的第一天
%u 周(00-53)星期一是一周的第一天
%V 周(01-53)星期日是一周的第一天,与%X使用
%v 周(01-53)星期一是一周的第一天,与%x使用
%W 星期名
%w 周的天(0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4位,与%V使用
%x 年,其中的星期一是周的第一天,4位,与%v使用
%Y 年,4位
%y 年,2位

正文

按季度存数据
select YEAR(procurement_dt)*10+((MONTH(procurement_dt)-1) DIV 3) +1, MONTH(procurement_dt) , procurement_dt from xs001
groupbyconcat(date_format(savetime,'%Y'),FLOOR((date_format(savetime,'%m')+2)/3))

公司订单系统每日订单量庞大,有很多表数据超千万。公司SQL优化这块做的很不好,可以说是没有做,所以导致查询很慢。

另外的按月统计方式

节选某个功能中的一句SQL EXPLAIN查看执行计划,EXPLAIN + SQL 查看SQL执行计划

MySQL-按月统计数据
统计2010年 每月的资金select DATE_FORMAT(date,'%Y-%m') as month,sum(money) as money from finance where DATE_FORMAT(date,'%Y')=2010 group by 
month order by month 1如果周一为一周的第一天,则(小写) DATE_FORMAT(date,'%x %v') 2如果周日为一周的第一天,则(大写) DATE_FORMAT(date,'%X %V')

必威官网亚洲体育 3

统计每个星期select DATE_FORMAT(date,'%x年-第%v周') as week,sum(money) as money from finance_base where DATE_FORMAT(date,'%Y')=2010 group by 
week 
select ((year(`recview`.`paytime`) * 100) + month(`recview`.`paytime`)) AS `paytime`,sum(`recview`.`rent`) AS `rent` from `recview` group by 
((year(`recview`.`paytime`) * 100) + month(`recview`.`paytime`)) order by ((year(`recview`.`paytime`) * 100) + month(`recview`.`paytime`));

一个索引没用到,受影响行接近2000万,难怪会慢。

 

原来的SQL打印出来估计有好几张A4纸,我发个整理后的简版。

SELECT
  COUNT(t.w_order_id) lineCount,
  SUM(ROUND(t.feel_total_money / 100, 2)) AS lineTotalFee,
  SUM(ROUND(t.feel_fact_money / 100, 2)) AS lineFactFee
FROM
  w_orders_his t
WHERE 1=1
  AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= STR_TO_DATE(#{beginTime},'%Y-%m-%d')
  AND DATE_FORMAT(t.create_time, '%Y-%m-%d') <= STR_TO_DATE(#{endTime},'%Y-%m-%d')
  AND t.pay_state = #{payState}
  AND t.store_id LIKE '%#{storeId}%'
  limit 0,10

这条sql需求是在两千万的表中捞出指定时间和条件的订单进行总数总金额汇总处理。

优化sql需要根据公司的业务,技术的架构等,且针对不同业务每条SQL的优化都是有差异的。

 

  优化点1:

AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= STR_TO_DATE(#{beginTime},'%Y-%m-%d')
AND DATE_FORMAT(t.create_time, '%Y-%m-%d') <= STR_TO_DATE(#{endTime},'%Y-%m-%d')

我们知道sql中绝对要减少函数的使用,像左边DATE_FORMAT(t.create_time, '%Y-%m-%d') 是绝对禁止使用的,如果数据库有一百万数据那么就会执行一百万次函数,非常非常影响效率。右边STR_TO_DATE(#{beginTime},'%Y-%m-%d')的函数会执行一次,但还是不建议使用函数。所以去掉函数直接使用 >=,<= 或BETWEEN AND速度就会快很多,但有的数据库设计时间字段只有日期没有时间,所以需要在日期后面拼接时间如:"2017-01-01"

  • " 00:00:00"。

更好的办法是用时间戳,数据库中存时间戳,然后拿时间戳去比较,如:BETWEEN '开始时间时间戳' AND '结束时间时间戳'

 

优化点2:

AND t.store_id LIKE '%#{storeId}%'

这句使用了LIKE并且前后匹配,前后匹配会导致索引失效,一般情况下避免使用,应该改成 AND t.store_id LIKE '#{storeId}%'

 

优化点3:

一般利用好索引,根据主键、唯一索引查询某一条记录,就算上亿数据查询也是非常快的。但这条sql需要查询数据统计需要用到COUNT和SUM,所以可以建立联合索引。

联合索引有一点需要注意:key index (a,b,c)可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 ,当最左侧字段是常量引用时,索引就十分有效。

所以把必要字段排放在左边key index(create_time,w_order_id,feel_total_money,feel_fact_money,payState,storeId)

 

 

结果

  优化之前大概几分钟,现在是毫秒级。其实改的东西也不多,避免在语句上踩雷,善用EXPLAIN查询SQL效率。 

  有时间我会举点别的SQL优化的例子

      

  说几点平常可以优化的地方

  • JOIN 后的的条件必须是索引,最好是唯一索引,否则数据一旦很多会直接卡死
  • 一般禁止使用UNIION ON,除非UNION ON 前后的记录数很少 
  • 禁止使用OR
  • 查总数使用COUNT(*)就可以,不需要COUNT(ID),MYSQL会自动优化
  • 数据库字段设置 NOT NULL,字段类型 INT > VARCHAR 越小越好
  • 禁止SELECT  * ,需要确定到使用的字段
  • 一般情况不在SQL中进行数值计算
  • SQL要写的简洁明了

      

参考

EXPLAIN type(从上到下,性能从差到好)

  • all 全表查询
  • index 索引全扫描
  • range 索引范围扫描
  • ref 使用非唯一或唯一索引的前缀扫描,返回相同值的记录
  • eq_ref 使用唯一索引,只返回一条记录
  • const,system 单表中最多只有一行匹配,根据唯一索引或主键进行查询
  • null 不访问表或索引就可以直接得到结果

 

MYSQL 五大引擎

  • ISAM :读取快,不占用内存和存储资源。 不支持事物,不能容错。
  • MyISAM :读取块,扩展多。
  • HEAP :驻留在内存里的临时表格,比ISAM和MyISAM都快。数据是不稳定的,关机没保存,数据都会丢失。
  • InnoDB :支持事物和外键,速度不如前面的引擎块。
  • Berkley(BDB) :支持事物和外键,速度不如前面的引擎块。

一般需要事物的设为InnoDB,其他设为MyISAM