首页 / MYSQL / 用于大型表连接的mysql查询优化

用于大型表连接的mysql查询优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了用于大型表连接的mysql查询优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10166字，纯文字阅读大概需要15分钟。

内容图文

我正在为广播电台创建一个报告,该报告生成在线听众的日志,以记录IP,日期,时间,用户总听力等.

听众表

client_ip        date        time      date_time            listeners  
---------------  ----------  --------  -------------------  -----------
166.147.81.179   2012-04-30  00:19:46  2012-04-30 00:19:46            1
64.12.243.203    2012-04-30  04:38:37  2012-04-30 04:38:37            1
198.228.211.195  2012-04-30  05:36:33  2012-04-30 05:36:33            1
198.228.211.195  2012-04-30  05:36:34  2012-04-30 05:36:34            2
198.228.211.195  2012-04-30  05:36:35  2012-04-30 05:36:35            2
198.228.211.195  2012-04-30  05:36:35  2012-04-30 05:36:35            3
166.147.81.179   2012-04-30  05:47:13  2012-04-30 05:47:13            2
76.170.251.97    2012-04-30  06:01:37  2012-04-30 06:01:37            2
76.170.251.97    2012-04-30  06:01:39  2012-04-30 06:01:39            2
76.170.251.97    2012-04-30  06:01:39  2012-04-30 06:01:39            2

同时它保存歌曲细节(标题,艺术家,专辑,长度,日期,时间等)的记录.

播放列表表

title                       artist                           length_in_secs  played_date  played_time  start_date_time      end_date_time        
--------------------------  -------------------------------  --------------  -----------  -----------  -------------------  ---------------------
We Found Love               Rihanna                                     184  2012-04-30   00:00:21     2012-04-30 00:00:21  2012-04-30 00:03:25  
Photograph                  Nickelback                                  216  2012-04-30   00:03:31     2012-04-30 00:03:31  2012-04-30 00:07:07  
Not Over You                Gavin DeGraw                                214  2012-04-30   00:07:18     2012-04-30 00:07:18  2012-04-30 00:10:52  
Stereo Hearts               Gym Class Heroes Ft Adam Levine             210  2012-04-30   00:10:55     2012-04-30 00:10:55  2012-04-30 00:14:25  
I Gotta Feeling             Black  Eyed Peas                            243  2012-04-30   00:15:03     2012-04-30 00:15:03  2012-04-30 00:19:06  
One Thing Leads To Another  Fixx                                        182  2012-04-30   00:19:14     2012-04-30 00:19:14  2012-04-30 00:22:16  
Raise Your Glass            Pink                                        202  2012-04-30   00:22:29     2012-04-30 00:22:29  2012-04-30 00:25:51  
Better In Time              Leona Lewis                                 216  2012-04-30   00:30:13     2012-04-30 00:30:13  2012-04-30 00:33:49  
Tainted Love                Soft Cell                                   153  2012-04-30   00:33:56     2012-04-30 00:33:56  2012-04-30 00:36:29  
Haven't Met You Yet         Michael Buble'                              242  2012-04-30   00:37:14     2012-04-30 00:37:14  2012-04-30 00:41:16

因此,报告要求是“在日期或日期范围内有多少用户收听歌曲”,我就是这样写的查询.它给出了正确的输出(据我所知),但查询执行需要时间与数据大小不成比例 – 从5秒到5-10分钟,具体取决于日期范围.

SELECT DATE_FORMAT(p.played_date, "%m/%d/%Y") `played_date`, p.played_time, p.length_in_secs, p.title, p.artist, RTRIM(p.label) `label`, RTRIM(p.album) `album`, IFNULL((SELECT SUM(l.listeners) FROM listeners `l` WHERE l.date_time >= p.start_date_time AND l.date_time <= p.end_date_time LIMIT 1), 0) `listeners` FROM playlists `p` WHERE p.title <> "" AND (p.played_date >= '2012-04-30' AND p.played_date <= '2012-05-30') HAVING listeners > 0 ORDER BY p.title ASC;
// formatted //
SELECT 
    DATE_FORMAT(p.played_date, "%m/%d/%Y") `played_date`,
    p.played_time,
    p.length_in_secs,
    p.title,
    p.artist,
    RTRIM(p.label) `label`,
    RTRIM(p.album) `album`,
    IFNULL(
        (SELECT 
            SUM(l.listeners) 
        FROM
            listeners `l` 
        WHERE l.date_time >= p.start_date_time 
            AND l.date_time <= p.end_date_time 
        LIMIT 1),
        0
    ) `listeners` 
FROM
    playlists `p` 
WHERE p.title <> "" 
    AND (
        p.played_date >= '2012-04-30' 
        AND p.played_date <= '2012-05-30'
    ) 
HAVING listeners > 0 
ORDER BY p.title ASC

输出：

played_date  played_time  length_in_secs  title                  artist                    label               album               listeners  
-----------  -----------  --------------  ---------------------  ------------------------  ------------------  ------------------  -----------
04/30/2012   08:06:26                228  Brighter Than The Sun  Colbie Caillat (Cal-Lay)  Universal Republic  All of You                    9

04/30/2012   08:44:16                248  Breakfast At Tiffanys  Deep Blue Something                                                         6

04/30/2012   18:06:40                253  Bizarre Love Triangle  New Order                                                                   2

04/30/2012   17:05:21                183  Animal                 Neon Trees                Mercury             Habits                        5

04/30/2012   08:58:05                253  Always Be My Baby      Mariah Carey                                                                2

04/30/2012   07:25:52                264  Already Gone           Kelly Clarkson            RCA                 All I Ever Wante              3

04/30/2012   16:21:33                236  All The Right Moves    One Republic              Interscope          Waking Up                     7

04/30/2012   11:58:26                199  All That She Wants     Ace Of Base                                                                12

04/30/2012   11:14:17                247  All I Wanna Do         Sheryl Crow                                                                 2

04/30/2012   16:12:59                235  A Thousand Miles       Vanessa Carlton                                                             5

有没有办法优化此查询以更快地运行,或写一个新的,更快的？请建议/帮助我.谢谢！！

使用EXPLAIN

EXPLAIN playlists;


Field            Type              Null    Key     Default            Extra                        
---------------  ----------------  ------  ------  -----------------  -----------------------------
playlist_id      int(10) unsigned  NO      PRI     (NULL)             auto_increment               
title            varchar(255)      YES             (NULL)                                          
artist           varchar(255)      YES             (NULL)                                          
label            varchar(255)      YES             (NULL)                                          
album            varchar(255)      YES             (NULL)                                          
length_in_secs   int(11)           NO              (NULL)                                          
played_date      date              NO              (NULL)                                          
played_time      time              NO              (NULL)                                          
start_date_time  datetime          NO              (NULL)                                          
end_date_time    datetime          NO              (NULL)                                          
added_date       datetime          NO              (NULL)                                          
modified_date    timestamp         NO              CURRENT_TIMESTAMP  on update CURRENT_TIMESTAMP


EXPLAIN listeners;


Field          Type                 Null    Key     Default            Extra                        
-------------  -------------------  ------  ------  -----------------  -----------------------------
listener_id    bigint(20) unsigned  NO      PRI     (NULL)             auto_increment               
station_id     int(10) unsigned     NO              (NULL)                                          
client_ip      varchar(50)          NO              (NULL)                                          
time           time                 NO              (NULL)                                          
date           date                 NO              (NULL)                                          
date_time      datetime             YES             (NULL)                                          
timestamp      bigint(20) unsigned  NO              (NULL)                                          
listeners      int(10) unsigned     NO              (NULL)                                          
processes      int(10) unsigned     NO              (NULL)                                          
uid            int(10) unsigned     NO              (NULL)                                          
user_agent     varchar(255)         YES             (NULL)                                          
added_date     datetime             NO              (NULL)                                          
modified_date  timestamp            NO              CURRENT_TIMESTAMP  on update CURRENT_TIMESTAMP

解决方法:

正如评论中所讨论的,您的查询实际上并不是您希望它执行的操作.根据你拥有的数据,我会亲自在SQL之外处理这个以创建一个表来存储每首歌的听众数量,然后你可以在SQL中查询以获取这些信息.如果你绝对想要一个SQL查询来做到这一点,那么它将需要像这个怪物一样;

SELECT 
DATE_FORMAT(p.played_date, "%m/%d/%Y") `played_date`,
p.played_time,
p.length_in_secs,
p.title,
p.artist,
RTRIM(p.label) `label`,
RTRIM(p.album) `album`,
SUM(SMALLEST(prev_listeners,next_listeners,dur_listeners) AS listeners
FROM (
  SELECT
  P.start_date_time,
  SUBSTRING_INDEX(GROUP_CONCAT(l_before.listeners ORDER BY l_before.date_time DESC),',',1) AS prev_listeners, 
  SUBSTRING_INDEX(GROUP_CONCAT(l_after.listeners ORDER BY l_after.date_time ASC),',',1) AS next_listeners, 
  MIN(l_during) AS dur_listeners
  FROM playlists p
  JOIN listeners l_before ON l_before.date_time < p.start_date_time 
  LEFT JOIN listeners l_after ON l_before.client_ip = l_after.client_ip AND l_after.date_time > p.end_date_time 
  LEFT JOIN listeners l_during ON l.client_ip = l_during.client_ip AND l_during.date_time BETWEEN p.start_date_time AND p.end_date_time
  WHERE p.title <> ""
  AND p.played_date BETWEEN '2012-04-30' AND '2012-05-30'
  GROUP BY p.start_date_time, l_before.client_ip
) l
JOIN playlists p USING (start_date_time)
GROUP BY p.start_date_time
ORDER BY p.start_date_time

其中SMALLEST是返回最小non_null参数的函数.

这将比您当前的查询花费更长的时间,但这是我能够想到的最有效的方式来获得每首歌曲的实际听众数量.

哦,这假设当来自ip地址的每个人都停止收听时,日志会记录一个零监听器的行,否则实际上没有办法做到这一点.

内容总结

以上是互联网集市为您收集整理的用于大型表连接的mysql查询优化全部内容，希望文章能够帮你解决用于大型表连接的mysql查询优化所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

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

内容手机端

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

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

来源：【匿名】

【上一篇】Mysql更快INSERT 【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【用于大型表连接的mysql查询优化】教程文章相关的互联网学习教程文章

MySQL 查询时间差值大于某一个值的记录【代码】

"SELECT table_id, FROM table WHERE (timediff(‘%s‘,raise_time)<‘00:05:00‘)" % \(table_id, str(datetime.datetime.now()))原文：http://www.cnblogs.com/buxizhizhoum/p/6780181.html

#数据库中所有表的信息 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‘;查...

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 查询时间差值大于某一个值的记录【代码】

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

23个mysql查询语句

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

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

mysql查询表内容【图】

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

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

MySQL8的WITH查询学习【代码】

mysql查询列为空【代码】

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

MySQL 查询一【图】

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

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

mysql 查询库表磁盘占用

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程