From 7c9903e0579f6dfd54b6de93496e39b3b6c3ea7f Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: Thu, 23 Feb 2023 12:32:02 +0800
Subject: [PATCH] 日志管理使用索引提升查询性能
---
ruoyi-ui/src/views/system/logininfor/index.vue | 5 +++--
sql/ry_20230223.sql | 9 +++++++--
ruoyi-ui/src/views/system/operlog/index.vue | 3 ++-
ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysOperLogMapper.xml | 4 ++--
ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysLogininforMapper.xml | 4 ++--
5 files changed, 16 insertions(+), 9 deletions(-)
diff --git a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysLogininforMapper.xml b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysLogininforMapper.xml
index 4f40069..67a4b1c 100644
--- a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysLogininforMapper.xml
+++ b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysLogininforMapper.xml
@@ -31,10 +31,10 @@
AND user_name like concat('%', #{userName}, '%')
</if>
<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
- and date_format(access_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
+ AND access_time >= #{params.beginTime}
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
- and date_format(access_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
+ AND access_time <= #{params.endTime}
</if>
</where>
order by info_id desc
diff --git a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysOperLogMapper.xml b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysOperLogMapper.xml
index 9c1e374..9474091 100644
--- a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysOperLogMapper.xml
+++ b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysOperLogMapper.xml
@@ -55,10 +55,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
diff --git a/ruoyi-ui/src/views/system/logininfor/index.vue b/ruoyi-ui/src/views/system/logininfor/index.vue
index 4a1a4fa..f13f789 100644
--- a/ruoyi-ui/src/views/system/logininfor/index.vue
+++ b/ruoyi-ui/src/views/system/logininfor/index.vue
@@ -38,11 +38,12 @@
<el-date-picker
v-model="dateRange"
style="width: 240px"
- value-format="yyyy-MM-dd"
+ value-format="yyyy-MM-dd HH:mm:ss"
type="daterange"
range-separator="-"
start-placeholder="开始日期"
end-placeholder="结束日期"
+ :default-time="['00:00:00', '23:59:59']"
></el-date-picker>
</el-form-item>
<el-form-item>
@@ -152,7 +153,7 @@
// 日期范围
dateRange: [],
// 默认排序
- defaultSort: {prop: 'loginTime', order: 'descending'},
+ defaultSort: {prop: 'accessTime', order: 'descending'},
// 查询参数
queryParams: {
pageNum: 1,
diff --git a/ruoyi-ui/src/views/system/operlog/index.vue b/ruoyi-ui/src/views/system/operlog/index.vue
index 6504fe5..4f1888b 100644
--- a/ruoyi-ui/src/views/system/operlog/index.vue
+++ b/ruoyi-ui/src/views/system/operlog/index.vue
@@ -53,11 +53,12 @@
<el-date-picker
v-model="dateRange"
style="width: 240px"
- value-format="yyyy-MM-dd"
+ value-format="yyyy-MM-dd HH:mm:ss"
type="daterange"
range-separator="-"
start-placeholder="开始日期"
end-placeholder="结束日期"
+ :default-time="['00:00:00', '23:59:59']"
></el-date-picker>
</el-form-item>
<el-form-item>
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 ed1aa93..0006989 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 = '操作日志记录';
@@ -558,7 +561,9 @@
status char(1) default '0' comment '登录状态（0成功 1失败）',
msg varchar(255) default '' comment '提示信息',
access_time datetime comment '访问时间',
- primary key (info_id)
+ primary key (info_id),
+ key idx_sys_logininfor_s (status),
+ key idx_sys_logininfor_lt (access_time)
) engine=innodb auto_increment=100 comment = '系统访问记录';
--
Gitblit v1.9.3