关于一条SQL语句的优化

nightgoblin 2016-07-28 06:21:25
目前有这样一张表，查询要求：查询出当每个用户交易金额累计达到70时的交易时间。
源码：


DROP TABLE IF EXISTS t_g;

CREATE TABLE t_g

(t_id BIGINT AUTO_INCREMENT,

 t_userid VARCHAR(20) DEFAULT '' COMMENT '用户id',

 t_je VARCHAR(20) DEFAULT '' COMMENT '交易金额',

 t_date VARCHAR(20) DEFAULT '' COMMENT '交易时间',

 PRIMARY KEY(t_id)

);

-- 创建索引

CREATE INDEX i_a ON t_g (t_userid,t_date);



INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160701');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','10','20160702');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','15','20160703');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','15','20160704');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160705');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','50','20160706');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160707');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160708');



INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','40','20160701');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','20','20160702');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','15','20160703');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','15','20160704');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160701');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','50','20160702');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160703');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160704');


我的查询语句是这样的写的：


select x.t_userid,MIN(x.t_date) from

(select a.t_userid,a.t_date,(select sum(t_je) from t_g where t_userid = a.t_userid and t_date <= a.t_date 

)total from t_g a)x 

where x.total/100 > 70  GROUP BY x.t_userid;

这样写可以是实现查询的功能，但是这张表有30万数据的时候，运行速度超级慢，大概需要2分钟。请问高手们，这句该怎么样优化，或者有其他更好的办法实现没，用存储过程也可以，只要能提升性能。
...全文
124 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2016-07-29
  • 打赏
  • 举报
 回复
你的 t_userid 有多少？ 如果很多，那说明返回的数据多，查询慢也比较正常 另外，如果你的每个 t_userid 的记录需要累计达到限额的记录也非常多的话，那么查询慢也正常 如果设计上能够加累计金额，确实会方便很多，不用实时算，也能索引
soton_dolphin 2016-07-29
  • 打赏
  • 举报
 回复
不如再加一列叫累计消费金额，那样会比较方便
nightgoblin 2016-07-29
  • 打赏
  • 举报
 回复
引用 2 楼 ACMAIN_CHM 的回复:
[quote=引用 2 楼 ACMAIN_CHM 的回复:] 
mysql> select * from t_g;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    1 | A        | 20   | 20160701 |
|    2 | A        | 10   | 20160702 |
|    3 | A        | 15   | 20160703 |
|    4 | A        | 15   | 20160704 |
|    5 | A        | 20   | 20160705 |
|    6 | A        | 50   | 20160706 |
|    7 | A        | 20   | 20160707 |
|    8 | A        | 20   | 20160708 |
|    9 | B        | 40   | 20160701 |
|   10 | B        | 20   | 20160702 |
|   11 | B        | 15   | 20160703 |
|   12 | B        | 15   | 20160704 |
|   13 | C        | 20   | 20160701 |
|   14 | C        | 50   | 20160702 |
|   15 | C        | 20   | 20160703 |
|   16 | C        | 20   | 20160704 |
+------+----------+------+----------+
16 rows in set (0.00 sec)

mysql> select *
    -> from t_g t
    -> where (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)<70
    -> and (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)>=70-t.t_je;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    5 | A        | 20   | 20160705 |
|   11 | B        | 15   | 20160703 |
|   14 | C        | 50   | 20160702 |
+------+----------+------+----------+
3 rows in set (0.00 sec)

mysql>
.[/quote] 你这个也很慢
nightgoblin 2016-07-29
  • 打赏
  • 举报
 回复
引用 1 楼 fatsimple 的回复:
select t.user_id,t.date_id from (select t_userid as user_id,t_date as date_id,sum(t_je) as threadhold from t_g group by t_userid,t_date) as t where t.threadhold>=70 PS:t_date => datetime类型 t_je=>decimal类型
明显是错误的结果
soton_dolphin 2016-07-29
  • 打赏
  • 举报
 回复
引用 5 楼 soton_dolphin 的回复:
不如再加一列叫累计消费金额，那样会比较方便
如果有这一列，就可以写 SELECT T_USERID, T_DATE, T_AGGREGATED_AMT FROM T_G WHERE MIN(T_AGGREGATED_AMT) >=70 GROUP BY T_USERID, T_DATE
ACMAIN_CHM 2016-07-28
  • 打赏
  • 举报
 回复
引用 2 楼 ACMAIN_CHM 的回复:
mysql> select * from t_g;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    1 | A        | 20   | 20160701 |
|    2 | A        | 10   | 20160702 |
|    3 | A        | 15   | 20160703 |
|    4 | A        | 15   | 20160704 |
|    5 | A        | 20   | 20160705 |
|    6 | A        | 50   | 20160706 |
|    7 | A        | 20   | 20160707 |
|    8 | A        | 20   | 20160708 |
|    9 | B        | 40   | 20160701 |
|   10 | B        | 20   | 20160702 |
|   11 | B        | 15   | 20160703 |
|   12 | B        | 15   | 20160704 |
|   13 | C        | 20   | 20160701 |
|   14 | C        | 50   | 20160702 |
|   15 | C        | 20   | 20160703 |
|   16 | C        | 20   | 20160704 |
+------+----------+------+----------+
16 rows in set (0.00 sec)

mysql> select *
    -> from t_g t
    -> where (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)<70
    -> and (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)>=70-t.t_je;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    5 | A        | 20   | 20160705 |
|   11 | B        | 15   | 20160703 |
|   14 | C        | 50   | 20160702 |
+------+----------+------+----------+
3 rows in set (0.00 sec)

mysql>
.
小胖总 2016-07-28
  • 打赏
  • 举报
 回复
select t.user_id,t.date_id from (select t_userid as user_id,t_date as date_id,sum(t_je) as threadhold from t_g group by t_userid,t_date) as t where t.threadhold>=70 PS:t_date => datetime类型 t_je=>decimal类型
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
慢SQL语句优化
对慢SQL语句优化一般可以按下面几步思路：开启慢查询日志，设置超过几秒为慢SQL语句，抓取慢SQL语句；通过explain查看执行计划，对慢SQL语句分析；创建索引并调整语句，再查看执行计划，对比调优结果。 　　参数 slow_query_log：表示是否开启慢查询日志。语句“set global slow_query_log=on”临时开启慢查询日志，如果想关闭慢查询日志只需要执行“set global slow_query_log=off ”即可。 参数slow_query_log_..
如何对 SQL 语句进行优化
当我们在设计一个项目时，开发阶段的业务数据量相对较少，一些 SQL 语句的执行效率对程序运行效率的影响不太明显，所以对于开发或运维人员来说不会注重对 SQL 语句的优化。但是，随着时间的积累，业务数据量会越来越多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。 本篇章主要从以下几个方面对 SQL 语句的优化进行介绍： 插入数据优化 主键优化 order by 优化 group by 优化 limit 优化 count 优化 update 优化
sql server 数据分析优化实战（一）——SQL语句优化
前言 在我们进行数据分析的时候，首要的目标是根据业务逻辑，通过编写SQL代码得到我们想要的结果，这是毋庸置疑的。一般情况下，由于我们分析的数据量比较少，体会不出SQL语句各种写法的性能优劣，对SQL代码的优化往往没那么重要。但是随着数据库中数据的增加，尤其是当一个系统需要对海量的数据进行持续性的分析时，SQL的运行效率就成为系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍甚至更多，可见对于数据分析，不是简
sql语句优化的13种方法
原文链接：http://blog.csdn.net/u012942818/article/details/53969972 1，什么是“执行计划”？ 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下500
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章