From 6f915f95bc23849a81bc522a6f158aa79ff49a07 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: 星期四, 23 二月 2023 10:01:41 +0800
Subject: [PATCH] 日志管理使用索引提升查询性能
---
src/main/resources/mybatis/monitor/SysOperLogMapper.xml | 4 ++--
sql/ry_20230223.sql | 9 +++++++--
src/main/resources/mybatis/monitor/SysLogininforMapper.xml | 4 ++--
3 files changed, 11 insertions(+), 6 deletions(-)
diff --git a/sql/ry_20230221.sql b/sql/ry_20230223.sql
similarity index 99%
rename from sql/ry_20230221.sql
rename to sql/ry_20230223.sql
index 123fab2..45c2ebb 100644
--- a/sql/ry_20230221.sql
+++ b/sql/ry_20230223.sql
@@ -433,7 +433,10 @@
error_msg varchar(2000) default '' comment '閿欒娑堟伅',
oper_time datetime comment '鎿嶄綔鏃堕棿',
cost_time bigint(20) default 0 comment '娑堣�楁椂闂�',
- primary key (oper_id)
+ primary key (oper_id),
+ key idx_sys_oper_log_bt (business_type),
+ key idx_sys_oper_log_s (status),
+ key idx_sys_oper_log_ot (oper_time)
) engine=innodb auto_increment=100 comment = '鎿嶄綔鏃ュ織璁板綍';
@@ -562,7 +565,9 @@
status char(1) default '0' comment '鐧诲綍鐘舵�侊紙0鎴愬姛 1澶辫触锛�',
msg varchar(255) default '' comment '鎻愮ず娑堟伅',
login_time datetime comment '璁块棶鏃堕棿',
- primary key (info_id)
+ primary key (info_id),
+ key idx_sys_logininfor_s (status),
+ key idx_sys_logininfor_lt (login_time)
) engine=innodb auto_increment=100 comment = '绯荤粺璁块棶璁板綍';
diff --git a/src/main/resources/mybatis/monitor/SysLogininforMapper.xml b/src/main/resources/mybatis/monitor/SysLogininforMapper.xml
index 73a287f..895e1c1 100644
--- a/src/main/resources/mybatis/monitor/SysLogininforMapper.xml
+++ b/src/main/resources/mybatis/monitor/SysLogininforMapper.xml
@@ -34,10 +34,10 @@
AND user_name like concat('%', #{userName}, '%')
</if>
<if test="params.beginTime != null and params.beginTime != ''"><!-- 寮�濮嬫椂闂存绱� -->
- and date_format(login_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
+ AND login_time >= #{params.beginTime}
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 缁撴潫鏃堕棿妫�绱� -->
- and date_format(login_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
+ AND login_time <= #{params.endTime}
</if>
</where>
order by info_id desc
diff --git a/src/main/resources/mybatis/monitor/SysOperLogMapper.xml b/src/main/resources/mybatis/monitor/SysOperLogMapper.xml
index bad0d33..2d379f7 100644
--- a/src/main/resources/mybatis/monitor/SysOperLogMapper.xml
+++ b/src/main/resources/mybatis/monitor/SysOperLogMapper.xml
@@ -56,10 +56,10 @@
AND oper_name like concat('%', #{operName}, '%')
</if>
<if test="params.beginTime != null and params.beginTime != ''"><!-- 寮�濮嬫椂闂存绱� -->
- and date_format(oper_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
+ AND oper_time >= #{params.beginTime}
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 缁撴潫鏃堕棿妫�绱� -->
- and date_format(oper_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
+ AND oper_time <= #{params.endTime}
</if>
</where>
order by oper_id desc
--
Gitblit v1.9.3