网络安全设备
- 是否具备测试环境进行功能性验证；
- 设备接入方式为旁路还是串联；
- 设备能否支持 Bypass 或者 PoE 功能；
- 设备是否与其它设备有联动机制；
- 设备离线会不会触发其它系统上配置的策略；
- 没有升级经验的情况下，设备版本升级最好由原厂工程师进行或提供手册；
- 没有测试环境的情况下，能升级特征库解决的问题最好就不要升级版本；
- 拟定回退方案；在不支持升级回退的情况下提前准备备机；
- 提前做好配置及数据的备份；
- 准备升级过程中要用到的工具；
- 升级完成后进行功能验证。
查看 MSSQL 的审计日志
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
-- 注意：event_time 使用的是 UTC 时间，所以要加上当地时区的差值 SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET()))) AS event_time, f.sequence_number, f.action_id, a.name AS action_name, f.succeeded, f.session_server_principal_name, f.server_principal_name, f.database_name, f.object_id, f.schema_name, f.object_name, f.class_type, m.class_type_desc, f.statement FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f INNER JOIN sys.dm_audit_actions a ON f.action_id=a.action_id INNER JOIN sys.dm_audit_class_type_map m ON f.class_type=m.class_type WHERE f.object_name='news_info' AND CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET())))>'2020-03-11 12:00:00' ORDER BY f.event_time; -- 或者 SELECT * FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f WHERE f.object_name='news_info' ORDER BY f.event_time; |
一动一静养正气
前言 本文内容来源于北京卫视“养生堂”节目； 本文内容仅供参考、请勿过于克制，结论来源于生活但不一定是100%正确； 不同场景不同患者适用程度不同，请谨慎采纳以下内容、博主不对本文内容承担任何责任。 阅读全文
MSSQL 查询之 NOT IN & LEFT JOIN
NOT IN 方式查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
SELECT T1.region, T1.streets, T1.village, T1.organize, T1.uid, T2.point_id FROM member T1 INNER JOIN member_info T2 ON T2.account_code = T1.uid WHERE T1.region = '华东' -- 条件记录 229873 条 AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条 AND ( T1.user_name IS NULL OR T1.user_name = '' OR T1.card_id IS NULL OR T1.card_id = '') -- 条件记录 6286655 条 AND T1.user_status = '01' -- 条件记录 6634104 条 AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条 -- 查询语句一耗时 7s, 返回记录 150681 条 UNION SELECT T1.region, T1.streets, T1.village, T1.organize, T1.uid, T2.point_id FROM member T1 INNER JOIN member_info T2 ON T2.account_code = T1.uid WHERE T1.region = '华东' -- 条件记录 229873 条 AND ( T1.user_name IS NULL OR T1.user_name = '' OR T1.card_id IS NULL OR T1.card_id = '' ) -- 条件记录 6286656 条 AND T1.user_status = '01' -- 条件记录 6634104 条 AND T1.uid NOT IN ( SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 225 条 SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 271 条 SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 221 条 SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 388 条 SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 373 条 SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 5986 条 SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 23079 条 SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1 -- 子查询记录 24095 条 -- 子查询总耗时 1m14s, 返回记录 28411 条 ) -- 条件记录 6711840 条 -- 查询语句二耗时 1h13m52s, 返回记录 191067 条 ORDER BY T1.region, T1.streets, T1.village, T1.organize; -- 总耗时 1h15m35s, 返回记录 191067 条 |
LEFT JOIN 方式查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
SELECT T1.region, T1.streets, T1.village, T1.organize, T1.uid, T2.point_id FROM member T1 INNER JOIN member_info T2 ON T2.account_code = T1.uid WHERE T1.region = '华东' -- 条件记录 229873 条 AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条 AND ( T1.user_name IS NULL OR T1.user_name = '' OR T1.card_id IS NULL OR T1.card_id = '') -- 条件记录 6286655 条 AND T1.user_status = '01' -- 条件记录 6634104 条 AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条 -- 查询语句一耗时 7s, 返回记录 150681 条 UNION SELECT T1.region, T1.streets, T1.village, T1.organize, T1.uid, T2.point_id FROM member T1 INNER JOIN member_info T2 ON T2.account_code = T1.uid LEFT JOIN ( SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 225 条 SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 271 条 SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 221 条 SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 388 条 SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 373 条 SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 5986 条 SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 23079 条 SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1 -- 子查询记录 24095 条 -- 子查询耗时 1m14s, 返回记录 28411 条 ) AS T3 ON T3.account_code=T1.uid WHERE T1.region = '华东' -- 条件记录 229873 条 AND ( T1.user_name IS NULL OR T1.user_name = '' OR T1.card_id IS NULL OR T1.card_id = '' ) -- 条件记录 6286656 条 AND T1.user_status = '01' -- 条件记录 6634104 条 AND T3.id IS NULL -- 条件记录 6711856 条 ORDER BY T1.region, T1.streets, T1.village, T1.organize; -- 总耗时 1m46s, 返回记录 191067 条 |
总结
- UNION 与 WHERE 语句应用将返回记录小的条件最先进行匹配，以小表的记录去遍历大表耗时更短；
- 本次查询中 NOT IN 与 NOT EXISTS 耗时基本相同，由于查询字段存在唯一值、故无法通过索引优化；所以 NOT EXISTS 方式没有明显优势。
密码保护：Linux 系统检查用
MySQL 存储过程示例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
DROP PROCEDURE IF EXISTS order_summary_month; DELIMITER // CREATE PROCEDURE order_summary_month() BEGIN DECLARE m datetime DEFAULT '2019-01-01'; WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') ) DO IF NOT EXISTS ( SELECT insertdate FROM app.order_summary WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1' ) THEN INSERT INTO app.order_summary ( statdate, time_unit, type, cnt_order, sum_price, cnt_weight, insertdate ) VALUES ( DATE_FORMAT(m,'%Y-%m'), 3, 1, ( SELECT COUNT(order_sn) AS cnt_order FROM app.wx_order WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' ), ( SELECT SUM(price) AS sum_price FROM app.wx_order WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' AND gains_type='1' ), ( SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight FROM app.wx_order O LEFT JOIN app.wx_order_goods G on O.id=G.order_id LEFT JOIN app.wx_goods_class C on G.goods_id=C.id WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND O.type='1' ), NOW() ); -- /* --不更新旧数据则可以跳过 ELSE UPDATE app.wx_order_summary SET time_unit = 3, type = 1, cnt_order = ( SELECT COUNT(order_sn) AS cnt_order FROM app.wx_order WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' ), sum_price = ( SELECT SUM(price) AS sum_price FROM app.wx_order WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' AND gains_type='1' ), cnt_weight = ( SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight FROM app.wx_order O LEFT JOIN app.wx_order_goods G on O.id=G.order_id LEFT JOIN app.wx_goods_class C on G.goods_id=C.id WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND O.type='1' ), insertdate = NOW() WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'; -- */ -- 不更新旧数据则可以跳过 END IF; COMMIT; SET m = DATE_SUB(m, INTERVAL -1 MONTH); END WHILE; END; // DELIMITER ; CALL order_summary_month(); |
MSSQL 查询所有表结构
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SELECT 表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END, 表描述 = CASE WHEN a.colorder = 1 THEN ISNULL(f.[value], '') ELSE '' END, 字段序号 = a.colorder, 字段名称 = a.name, 类型 = b.name, 长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'), 小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),0), 允许为空 = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END, 主键 = CASE WHEN EXISTS( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN( SELECT name FROM sysindexes WHERE indid IN( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid ) ) ) THEN '√' ELSE '' END, 默认值 = ISNULL(e.text, ''), 占用字节数 = a.length, 标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END, 字段描述 = ISNULL(g.[value], '') FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 WHERE b.name IS NOT NULL -- AND d.name='要查询的表' -- 如果只查询表,加上 AND 条件 ORDER BY d.name, a.colorder; -- 验证表数量，避免空表或表中没有ID为1的记录 SELECT name FROM sysobjects WHERE xtype='U'; |
MySQL 问题汇总
- ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist
- 执行以下 SQL 语句删除并重新创建 mysql.servers 表
1234567891011121314151617USE mysqlDROP TABLE IF EXISTS servers;CREATE TABLE `servers` (`Server_name` char(64) NOT NULL DEFAULT '',`Host` char(64) NOT NULL DEFAULT '',`Db` char(64) NOT NULL DEFAULT '',`Username` char(64) NOT NULL DEFAULT '',`Password` char(64) NOT NULL DEFAULT '',`Port` int(4) NOT NULL DEFAULT '0',`Socket` char(64) NOT NULL DEFAULT '',`Wrapper` char(64) NOT NULL DEFAULT '',`Owner` char(64) NOT NULL DEFAULT '',PRIMARY KEY (`Server_name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='MySQL Foreign Servers table';CREATE USER report01@'192.168.100.20';GRANT SELECT ON *.* TO 'report02'@'192.168.100.20' IDENTIFIED BY "Password";FLUSH PRIVILEGES;
试水-阿里云大使
重新生成或重新组织索引
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
-- 返回有关 SQL Server 实例中所有表和索引的所有统计信息. SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) where avg_fragmentation_in_percent>10; -- 查看库的 database_id 值. SELECT DB_ID ('数据库名'); -- 确保已执行USE <databasename> 语句. SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- 从sys.dm_db_index_physical_stats函数中筛选表和索引，并将对象和索引ID转换为名称. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; -- 向游标声明要处理的分区列表。 DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- 开启游标. OPEN partitions; -- 循环调用分区。 WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30是在 REORGANIZE 和 REBUILD 之间切换的判断条件. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- 关闭并释放游标. CLOSE partitions; DEALLOCATE partitions; -- 删除临时表. DROP TABLE #work_to_do; GO |