mysql 优化sql
一.更新数据,原sql:
set @beginTime='2019-11-26 00:00:00';
set @endTime='2019-12-25 23:59:59';
#注册时间>30天(1-3单)-sql 370724
update users_jianfu set activity_id=10 where user_id in (
select a.user_id from(
select u.user_id from baojia_bike.users_jianfu as u ,(select user_id from user_orders_last30days_temp where user_order_num>=1 and user_order_num<=3 ) as b
where u.user_id=b.user_id and u.register_time<@beginTime
)as a
)
优化:
#优化
set @beginTime='2019-11-26 00:00:00';
set @endTime='2019-12-25 23:59:59';
update users_jianfu a, user_orders_last30days_temp as b set a.activity_id=10
where a.user_id=b.user_id and a.register_time<@beginTime and b.user_order_num>=1 and b.user_order_num<=3
二. 查询
set @beginTime='2019-11-25 00:00:00';
set @endTime='2019-12-24 23:59:59';
select a.user_id from (select user_id from user_orders_last30days_temp where user_order_num>=1 and user_order_num<=3) as a left join (select user_id from baojia_bike.users_jianfu where register_time<@beginTime) as b on a.user_id=b.user_id
优化后:
#
set @beginTime='2019-11-25 00:00:00';
set @endTime='2019-12-24 23:59:59';
select a.user_id from user_orders_last30days_temp as a left join baojia_bike.users_jianfu b on a.user_id=b.user_id where register_time<@beginTime and a.user_order_num>=1 and a.user_order_num<=3
通过以上的例子看到,结论:mysql的语句能外层一次关联查询出来,不要嵌套子查询。
相关文章
- Mysql SQL语句大全
- CSV文件用python导入mysql
- 【MySQL高级】优化SQL步骤
- 【MySQL高级】SQL优化
- SQL Server分组连接并去重类似Mysql的Group_Concat功能实现
- RDBMS SQL 编辑器 | MySQL、Oracle、MariaDB、SQLsever、SQLite、PostgreSQL
- 从SQL Server到MySQL,4款主流迁移工具到底哪家强?
- MySQL命令行导出数据库(sql脚本)
- 基于Python(Django)+MySQL 实现(Web)SQL智能检测系统的设计与实现【100010694】
- 一条SQL语句执行得很慢的原因有哪些?| MySQL高性能优化规范建议
- mysql如何处理亿级数据,第一个阶段——优化SQL语句
- com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 ‘DUAL‘ 无效 | Druid双数据源MySQL+SQL server
- MySQL中的SQL语言
- 【MySQL基础篇】SQL通用语法及分类
- 一条SQL语句获取具有父子关系的分类列表(mysql)
- (1.11)SQL优化——mysql提示(hint)
- 燕十八mysql复习
- MySQL之聚合函数与分组查询
- mysql开启日志sql语句
- mysql 日期转换sql函数