首页 / MYSQL / 优化具有时区转换的Mysql查询并逐个小时

优化具有时区转换的Mysql查询并逐个小时

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了优化具有时区转换的Mysql查询并逐个小时，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4893字，纯文字阅读大概需要7分钟。

内容图文

这是MySql 5.5中的表,有3000万条记录

CREATE TABLE `campaign_logs` (
  `domain` varchar(50) DEFAULT NULL,
  `campaign_id` varchar(50) DEFAULT NULL,
  `subscriber_id` varchar(50) DEFAULT NULL,
  `message` varchar(21000) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `log_type` varchar(50) DEFAULT NULL,
  `level` varchar(50) DEFAULT NULL,
  `campaign_name` varchar(500) DEFAULT NULL,
  KEY `subscriber_id_index` (`subscriber_id`),
  KEY `log_type_index` (`log_type`),
  KEY `log_time_index` (`log_time`),
  KEY `campid_domain_logtype_logtime_subid_index` (`campaign_id`,`domain`,`log_type`,`log_time`,`subscriber_id`),
  KEY `domain_logtype_logtime_index` (`domain`,`log_type`,`log_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

在下面的查询中,我正在按时间分组进行分组

QUERY

SELECT 
    log_type
    ,DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS log_date
    ,count(*) AS total
    ,count(DISTINCT subscriber_id) d 
FROM
    stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index)
WHERE
    DOMAIN='xxx' 
    AND campaign_id='123' 
    AND log_type = 'EMAIL_OPENED' 
    AND log_time BETWEEN 
        CONVERT_TZ('2015-02-01 00:00:00','+00:00','+05:30') AND
        CONVERT_TZ('2015-03-01 23:59:58','+00:00','+05:30')
GROUP BY log_date

UNION ALL

SELECT
    log_type
    ,DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS log_date
    ,count(*) AS total
    ,count(DISTINCT subscriber_id) d 
FROM
    stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index) 
WHERE
    DOMAIN='xxx' 
    AND campaign_id='123' 
    AND log_type = 'EMAIL_SENT' 
    AND log_time BETWEEN 
        CONVERT_TZ('2015-02-01 00:00:00','+00:00','+05:30') AND
        CONVERT_TZ('2015-03-01 23:59:58','+00:00','+05:30')
GROUP BY log_date 

UNION ALL 

SELECT 
    log_type
    ,DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS log_date
    ,count(*) AS total
    ,count(DISTINCT subscriber_id) d
FROM
    stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index)
WHERE
    DOMAIN='xxx' 
    AND campaign_id='123' 
    AND log_type = 'EMAIL_CLICKED' 
    AND log_time BETWEEN 
        CONVERT_TZ('2015-02-01 00:00:00','+00:00','+05:30') AND
        CONVERT_TZ('2015-03-01 23:59:58','+00:00','+05:30')
GROUP BY log_date;

结果

上面的查询将给出这样的结果

+---------------+-------+----------------+-------------+
| EMAIL_CLICKED | 1 AM  |             71 |          83 |
| EMAIL_CLICKED | 1 PM  |             25 |          27 |
| EMAIL_SENT    | 10 AM |             51 |          59 |
| EMAIL_OPENED  | 10 PM |             16 |          18 |

这是上述查询的解释

说明

+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+------------------------------------------+
| id | select_type  | table         | type  | possible_keys                             | key                                       | key_len | ref  | rows   | Extra                                    |
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+------------------------------------------+
|  1 | PRIMARY      | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468     | NULL |  55074 | Using where; Using index; Using filesort |
|  2 | UNION        | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468     | NULL | 330578 | Using where; Using index; Using filesort |
|  3 | UNION        | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468     | NULL |   1589 | Using where; Using index; Using filesort |
|NULL| UNION RESULT | <union1,2,3>  | ALL   | NULL                                      | NULL                                      | NULL    | NULL |   NULL |                                          |
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+------------------------------------------+

优化？

我们在这张桌子上有一个覆盖索引.

此查询需要很长时间(超过1分钟).

如果我从查询中删除distinct_count(subscriber_id),那么我们将在1.5秒内获得结果,但我需要查询中的distinct_count subscriber_id.

有没有办法优化这个查询？

谢谢

解决方法:

您没有处理大量数据,因此该组不应该花费40秒 – 假设您不在桌面上具有大量锁定活动的非常繁忙的服务器上.

试试这个版本的查询(限于一个log_type)：

SELECT log_type,
       DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS time,
       count(DISTINCT subscriber_id) AS distinct_count,
       count(subscriber_id) AS total_count
FROM stats.campaign_logs
WHERE DOMAIN = 'xxxx' AND
      campaign_id='1234' AND
      log_type = 'EMAIL_SENT' AND
      log_time BETWEEN CONVERT_TZ('2015-02-07 00:00:00','+00:00','+05:30') AND CONVERT_TZ('2015-02-14 23:59:58','+00:00','+05:30')
GROUP BY time;

这应该最佳地使用索引.如果这很快,那么使用union all将行组合在一起.丑陋,但由于索引优化,有时union all比OR / IN快得多.

内容总结

以上是互联网集市为您收集整理的优化具有时区转换的Mysql查询并逐个小时全部内容，希望文章能够帮你解决优化具有时区转换的Mysql查询并逐个小时所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/903450.html

来源：【匿名】

【上一篇】mysql – 数据库,表和列的不同字符集和排序规则是否会导致性能问题？【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【优化具有时区转换的Mysql查询并逐个小时】教程文章相关的互联网学习教程文章

MySQL查询所有表的数据量【代码】

#数据库中所有表的信息 SELECT*FROM information_schema.TABLES WHERE TABLE_SCHEMA =‘数据库名‘ #数据库中每个表的数据量 SELECT table_name,table_rows FROM information_schema.tables WHERE TABLE_SCHEMA =‘数据库名‘ORDERBY table_rows DESC; 原文：https://www.cnblogs.com/shenyixin/p/14789074.html

23个mysql查询语句

一查询数值型数据: SELECT * FROM tb_name WHERE sum > 100; 查询谓词:>,=,<,<>,!=,!>,!<,=>,=< 二查询字符串 SELECT * FROM tb_stu WHERE sname = ‘小刘‘ SELECT * FROM tb_stu WHERE sname like ‘刘%‘ SELECT * FROM tb_stu WHERE sname like ‘%程序员‘ SELECT * FROM tb_stu WHERE sname like ‘%PHP%‘ 三查询日期型数据 SELECT * FROM tb_stu WHERE date = ‘2011-04-08‘ 注:不同数据库对日期型数据存在差异: ：...

如何提高 Mysql 查询性能？【图】

MySQL 查询优化器SELECT 执行过程那么如何提高 MySQL 的查询性能呢？首先你需要了解查询优化器处理 SQL 的全过程。以 SELECT 的 SQL 的执行过程为例，如下图所示。客户端发送一条 SELECT 查询给服务器；服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；服务器进行 SQL 解析、预处理、再由查询优化器生成对应的执行计划；MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询；...

MySQL查询表内重复记录【代码】

(一)1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有一个记录 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and min(id) not in ...

mysql查询表内容【图】

一，说明：表名为student1，查询某个表里的字段select 字段名1，字段名1 form student;例如：查询student中的snameselect sname from student;2,查询某个表里的字段（字段下面有重复的内容），并且显示的查询结果不显示重复的select distinct class from student;3,查询加条件select * from student where sno=‘107‘;4,查询某个字段下满足某条件的（class为95031中ssex为女的）select * from student where class in(95031) and ...

MySQL查询缓存打开、设置、参数查询、性能变量【代码】

query_cache_type 使用查询缓存的方式一般，我们会把query_cache_type 设置为 ON,默认情况下应该是ONmysql>SELECT@@query_cache_type; +--------------------+|@@query_cache_type|+--------------------+|ON|+--------------------+query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存，如：select SQL_CACHE user_namefrom users whereuser_id=‘100‘;这样当我们...

MySQL查询本周、上周、本月、上个月份数据的sql代码

查询当前这周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,‘%Y-%m-%d‘)) = YEARWEEK(now()); 查询上周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,‘%Y-%m-%d‘)) = YEARWEEK(now())-1; 查询当前月份的数据 select name,submittime from enterprise where date_format(submittime,‘%Y-%m‘)=date_format(now(),‘%Y-%m‘) 查询距离当前现在...

MySQL8的WITH查询学习【代码】

前言对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性 MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。可以查看官方文档【点击跳转】示例官方第一个示例，可以看出该查询语句创建了cte1,cte2,cte3,cte4这4个临时表，后面的临时表依赖前面的临时表数据。最后一行为最终查询结果，实际ct4因为ct3结果包含3行数据，但是使用MAX,MIN得到一行结果。WITH cte1(txt) AS (SE...

mysql查询列为空【代码】

SELECT*FROM `表名` WHEREISNULL(列名) 原文：https://www.cnblogs.com/xiaostudy/p/11562018.html

用mysql查询某字段是否有索引【代码】【图】

可以使用SHOW INDEX FROM table_name来查看表的索引，从而查看字段的索引；查询结果中table为表名，key_name为索引名，Column_name为列名原文：https://www.cnblogs.com/lxwphp/p/8797036.html

MySQL 查询一【图】

student表teacher 表course 表score 表查询student中所有记录查询student表中所有记录中的sname、ssex、class列查询教师所有单位不重复的depart列distinct排除重复列。查询score表中成绩在60到80之间的所有记录。 between and 查询score表中成绩为85、86、88的记录。in 范围查询student表中“95031”班或（与）性别“女”的同学记录。以class降序查询student表中的所有记录。以cno升序、degree降序查询score表中的所有记录。查询“...

mysql查询今天、昨天、近7天、近30天、本月、上一月的SQL语句

mysql查询今天、昨天、近7天、近30天、本月、上一月的SQL语句这篇文章主要介绍了mysql查询今天、昨天、近7天、近30天、本月、上一月的SQL语句,一般在一些统计报表中比较常用这个时间段,需要的朋友可以参考下mysql查询今天,昨天,近7天,近30天,本月,上一月数据的方法分析总结：话说有一文章表article，存储文章的添加文章的时间是add_time字段，该字段为int(5)类型的，现需要查询今天添加的文章总数并且按照时间从大到小排序，则查询...

mysql查询指定表所有的字段信息 columns

show columns from 表名； desc 表名;（全写：describe 表名） show create table 表名;原文：https://www.cnblogs.com/niuben/p/13071598.html

mysql 查询库表磁盘占用

查整个库的状态：select concat(truncate(sum(data_length)/1024/1024,2),‘MB‘) as data_size, concat(truncate(sum(max_data_length)/1024/1024,2),‘MB‘) as max_data_size, concat(truncate(sum(data_free)/1024/1024,2),‘MB‘) as data_free, concat(truncate(sum(index_length)/1024/1024,2),‘MB‘) as index_size from information_schema.tableswhere TABLE_SCHEMA = ‘databasename‘;查...

[转]向facebook学习，通过协程实现mysql查询的异步化【图】

FROM ：　通过协程实现mysql查询的异步化前言最近学习了赵海平的演讲，了解到facebook的mysql查询可以进行异步化，从而提高性能。由于facebook实现的比较早，他们不得不对php进行hack才得以实现。现在的php5.5，已经无需hack就可以实现了。对于一个web网站的性能来说，瓶颈多半是来自于数据库。一般数据库查询会在某个请求的整体耗时中占很大比例。如果能提高数据库查询的效率，网站的整体响应时间会有很大的下降。如果能实现mysql...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / 优化具有时区转换的Mysql查询并逐个小时

优化具有时区转换的Mysql查询并逐个小时

内容导读

内容图文

内容总结

内容备注

内容手机端

【优化具有时区转换的Mysql查询并逐个小时】教程文章相关的互联网学习教程文章

MySQL查询所有表的数据量【代码】

23个mysql查询语句

如何提高 Mysql 查询性能？【图】

MySQL查询表内重复记录【代码】

mysql查询表内容【图】

MySQL查询缓存打开、设置、参数查询、性能变量【代码】

MySQL查询本周、上周、本月、上个月份数据的sql代码

MySQL8的WITH查询学习【代码】

mysql查询列为空【代码】

用mysql查询某字段是否有索引【代码】【图】

MySQL 查询一【图】

mysql查询今天、昨天、近7天、近30天、本月、上一月的SQL语句

mysql查询指定表所有的字段信息 columns

mysql 查询库表磁盘占用

[转]向facebook学习，通过协程实现mysql查询的异步化【图】

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程