Skip to content

Commit 5bacf7f

Browse files
author
zhangjiangbin
committed
日志表加入索引，提升查询速度，去除无用字段
1 parent c788f14 commit 5bacf7f

File tree

7 files changed

+59
-12
lines changed

7 files changed

+59
-12
lines changed

‎app/Console/Commands/AutoBanUserJob.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
class AutoBanUserJob extends Command
1313
{
1414
protected $signature = 'command:autoBanUserJob';
15-
protected $description = '自动封禁账号';
15+
protected $description = '自动封禁用户';
1616

1717
protected static $config;
1818

‎app/Console/Commands/AutoReopenUserJob.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
class AutoReopenUserJob extends Command
1111
{
1212
protected $signature = 'command:autoReopenUserJob';
13-
protected $description = '自动解封账号';
13+
protected $description = '自动解封用户';
1414

1515
public function __construct()
1616
{

‎app/Http/Models/SsNodeInfo.php‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ class SsNodeInfo extends Model
1313
{
1414
protected $table = 'ss_node_info';
1515
protected $primaryKey = 'id';
16+
public $timestamps = false;
1617
protected $fillable = [
1718
'node_id',
1819
'uptime',

‎app/Http/Models/SsNodeOnlineLog.php‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ class SsNodeOnlineLog extends Model
1313
{
1414
protected $table = 'ss_node_online_log';
1515
protected $primaryKey = 'id';
16+
public $timestamps = false;
1617
protected $fillable = [
1718
'node_id',
1819
'online_user',

‎readme.md‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -265,6 +265,18 @@ vim user-config.json
265265
266266
````
267267

268+
## 校时
269+
````
270+
如果架构是“一面板机-一数据库机-多节点机”，请务必保持各个服务器之间的时间一致，否则会影响节点在线数的准确性和单端口多用户功能的正常使用。
271+
推荐统一使用CST并安装校时服务：
272+
vim /etc/sysconfig/clock 把值改为 Asia/Shanghai
273+
cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
274+
275+
重启一下服务器，然后：
276+
yum install ntp
277+
ntpdate cn.pool.ntp.org
278+
````
279+
268280
## 致敬
269281
````
270282
@breakwa11

‎sql/db.sql‎

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -78,10 +78,8 @@ CREATE TABLE `ss_node_online_log` (
7878
`node_id` int(11) NOT NULL COMMENT '节点ID',
7979
`online_user` int(11) NOT NULL COMMENT '在线用户数',
8080
`log_time` int(11) NOT NULL COMMENT '记录时间',
81-
`created_at` datetime NOT NULL,
82-
`updated_at` datetime NOT NULL,
8381
PRIMARY KEY (`id`),
84-
KEY `idx_online` (`node_id`,`log_time`)
82+
KEY `idx_node_id` (`node_id`) USING BTREE
8583
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='节点在线信息';
8684

8785

@@ -178,7 +176,8 @@ CREATE TABLE `user_traffic_log` (
178176
`log_time` int(11) NOT NULL COMMENT '记录时间',
179177
PRIMARY KEY (`id`),
180178
KEY `idx_user` (`user_id`),
181-
KEY `idx_node` (`node_id`)
179+
KEY `idx_node` (`node_id`),
180+
KEY `idx_user_node` (`user_id`,`node_id`) USING BTREE
182181
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
183182

184183

@@ -193,7 +192,7 @@ CREATE TABLE `ss_config` (
193192
`is_default` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否默认：0-不是、1-是',
194193
`sort` int(11) NOT NULL DEFAULT '0' COMMENT '排序：值越大排越前',
195194
`created_at` datetime DEFAULT NULL COMMENT '创建时间',
196-
`updated_at` datetime DEFAULT NULL COMMENT '最后一次更新时间',
195+
`updated_at` datetime DEFAULT NULL COMMENT '最后更新时间',
197196
PRIMARY KEY (`id`)
198197
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
199198

@@ -566,7 +565,7 @@ CREATE TABLE `referral_log` (
566565
`ref_amount` int(11) NOT NULL DEFAULT '0' COMMENT '返利金额',
567566
`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-未提现、1-审核中、2-已提现',
568567
`created_at` datetime DEFAULT NULL COMMENT '创建时间',
569-
`updated_at` datetime DEFAULT NULL COMMENT '最后一次更新时间',
568+
`updated_at` datetime DEFAULT NULL COMMENT '最后更新时间',
570569
PRIMARY KEY (`id`)
571570
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消费返利日志';
572571

@@ -627,7 +626,9 @@ CREATE TABLE `user_traffic_daily` (
627626
`traffic` varchar(255) DEFAULT '' COMMENT '总流量（带单位）',
628627
`created_at` datetime DEFAULT NULL COMMENT '创建时间',
629628
`updated_at` datetime DEFAULT NULL COMMENT '最后更新时间',
630-
PRIMARY KEY (`id`)
629+
PRIMARY KEY (`id`),
630+
KEY `idx_user` (`user_id`) USING BTREE,
631+
KEY `idx_user_node` (`user_id`,`node_id`) USING BTREE
631632
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
632633

633634

@@ -644,7 +645,9 @@ CREATE TABLE `user_traffic_hourly` (
644645
`traffic` varchar(255) DEFAULT '' COMMENT '总流量（带单位）',
645646
`created_at` datetime DEFAULT NULL COMMENT '创建时间',
646647
`updated_at` datetime DEFAULT NULL COMMENT '最后更新时间',
647-
PRIMARY KEY (`id`)
648+
PRIMARY KEY (`id`),
649+
KEY `idx_user` (`user_id`) USING BTREE,
650+
KEY `idx_user_node` (`user_id`,`node_id`) USING BTREE
648651
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
649652

650653

@@ -676,7 +679,7 @@ CREATE TABLE `ss_node_traffic_hourly` (
676679
`total` bigint(20) NOT NULL DEFAULT '0' COMMENT '总流量',
677680
`traffic` varchar(255) DEFAULT '' COMMENT '总流量（带单位）',
678681
`created_at` datetime DEFAULT NULL COMMENT '创建时间',
679-
`updated_at` datetime DEFAULT NULL COMMENT '最后一起更新时间',
682+
`updated_at` datetime DEFAULT NULL COMMENT '最后更新时间',
680683
PRIMARY KEY (`id`),
681684
KEY `idx_node_id` (`node_id`)
682685
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
@@ -688,7 +691,7 @@ CREATE TABLE `ss_node_traffic_hourly` (
688691
CREATE TABLE `user_ban_log` (
689692
`id` int(11) NOT NULL AUTO_INCREMENT,
690693
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
691-
`minutes` int(11) NOT NULL DEFAULT '0' COMMENT '封禁账号市场，单位分钟',
694+
`minutes` int(11) NOT NULL DEFAULT '0' COMMENT '封禁账号时长，单位分钟',
692695
`desc` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '操作描述',
693696
`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-未处理、1-已处理',
694697
`created_at` datetime DEFAULT NULL COMMENT ' 创建时间',

‎sql/update/20171130.sql‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
-- 日志表加索引，提升查询速度
2+
ALTER TABLE `user_traffic_log`
3+
ADD INDEX `idx_user_node` (`user_id`, `node_id`) USING BTREE ;
4+
5+
ALTER TABLE `user_traffic_daily`
6+
ADD INDEX `idx_user` (`user_id`) USING BTREE ,
7+
ADD INDEX `idx_user_node` (`user_id`, `node_id`) USING BTREE ;
8+
9+
ALTER TABLE `user_traffic_hourly`
10+
ADD INDEX `idx_user` (`user_id`) USING BTREE ,
11+
ADD INDEX `idx_user_node` (`user_id`, `node_id`) USING BTREE ;
12+
13+
ALTER TABLE `ss_node_info`
14+
ADD INDEX `idx_node_id` (`node_id`) USING BTREE ;
15+
16+
ALTER TABLE `ss_node_online_log`
17+
ADD INDEX `idx_node_id` (`node_id`) USING BTREE ;
18+
19+
-- 去除节点负载信息表无用字段
20+
ALTER TABLE `ss_node_info`
21+
DROP COLUMN `created_at`,
22+
DROP COLUMN `updated_at`;
23+
24+
-- 去除在线用户表无用字段
25+
ALTER TABLE `ss_node_online_log`
26+
DROP COLUMN `created_at`,
27+
DROP COLUMN `updated_at`;
28+
29+
30+

0 commit comments

Comments
 (0)