MySQL · 案例分析 · 大量连接状态 Writing to net

发表于 2019-11-05 | 更新于 2019-11-15 | 分类于 MySQL

问题现象

HDM 告警

您的自建DB实例

dbvipcenter@10.159.36.193:3307 ( 别名: HMCENTER_M, 集群: 互联互通 )

MySQL活跃会话数达到131

Show processlist 检查实时会话状态，发现出现大量的 "Writing to net"

| 41106 | canal  | 10.138.225.187:36737 | NULL | Binlog Dump | 14240 | Writing to net              | NULL |    0.000 |
| 41146 | canal  | 10.138.225.187:44157 | NULL | Binlog Dump | 14163 | Writing to net              | NULL |    0.000 |
| 41453 | canal  | 10.138.225.187:43588 | NULL | Binlog Dump | 13536 | Writing to net              | NULL |    0.000 |
| 41570 | canal  | 10.138.225.187:35880 | NULL | Binlog Dump | 13345 | Writing to net              | NULL |    0.000 |
| 41608 | canal  | 10.138.225.187:39624 | NULL | Binlog Dump | 13300 | Writing to net              | NULL |    0.000 |
| 41615 | canal  | 10.138.225.187:39629 | NULL | Binlog Dump | 13281 | Writing to net              | NULL |    0.000 |
| 41618 | canal  | 10.138.225.187:39632 | NULL | Binlog Dump | 13281 | Writing to net              | NULL |    0.000 |
| 41650 | canal  | 10.138.225.187:43390 | NULL | Binlog Dump | 13227 | Writing to net              | NULL |    0.000 |
| 41854 | canal  | 10.138.225.187:47054 | NULL | Binlog Dump | 12859 | Writing to net              | NULL |    0.000 |
| 41883 | canal  | 10.138.225.187:50815 | NULL | Binlog Dump | 12814 | Writing to net              | NULL |    0.000 |
| 42305 | canal  | 10.138.225.187:42869 | NULL | Binlog Dump | 12040 | Writing to net              | NULL |    0.000 |
| 42420 | canal  | 10.138.225.187:35098 | NULL | Binlog Dump | 11862 | Writing to net              | NULL |    0.000 |
| 42446 | canal  | 10.138.225.187:38964 | NULL | Binlog Dump | 11818 | Writing to net              | NULL |    0.000 |

原因分析

Writing to net：The server is writing a packet to the network.

SQL 语句查询流程：

扫描一行写到 net_buffer，这里 net_buffer 是按照 net_buffer_length 初始化，但可以动态增长到 max_allowed_packet 配置值
继续获取满足条件的行，直到写满 net_buffer，然后调用网络接口发送出去
客户端接收网络包完成，清空 net_buffer
重复上面 1 到 3 的步骤
如果发送未完成，表示 socket send buffer 写满，这时会进入等待状态，显示 Writing to net，直到 socket send buffer 重新可写，再继续发送

由于 MySQL 是边扫描边发送数据的，当客户端接收慢时，就会阻塞整个查询进程，导致执行变慢。

MariaDB [(none)]> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> select @@max_allowed_packet/1024/1024;
+--------------------------------+
| @@max_allowed_packet/1024/1024 |
+--------------------------------+
| 20.00000000                    |
+--------------------------------+
1 row in set (0.08 sec)

解决方法

调整 max_allowed_packet 参数
排查客户端异常情况

MariaDB [(none)]> set global max_allowed_packet=67108864;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like "max_allowed_packet";
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

阿里云 · HDR

发表于 2019-11-04 | 更新于 2019-11-13 | 分类于阿里云

背景

备份媒介的损坏、火灾、自然灾害等情况可能导致数据的永久丢失，造成难以承受的损失。

简介

混合云容灾服务（Hybrid Disaster Recovery，简称 HDR）是一个高性能业务持续性解决方案，为企业关键业务提供低至秒级 RPO，分钟级 RTO 的容灾服务，极大缩短业务宕机时间，减少数据丢失。

核心问题：

应用级容灾：在数据中心故障或长时间系统维护作业时，在云上快速恢复应用运行，缩短业务停机时间，极大减少损失，保障业务连续性。
数据级容灾：在数据中心备份数据库、虚拟机、物理机整机，备份数据存储在本地并自动上云，可在自建数据中心发生重大灾害时保障数据安全，同时提供高效的本地和云上的双重恢复。

业务类型：

CDR：Continuous Data Replication，连续复制型容灾，解决企业关键应用的高标准容灾方案，提供秒-分级的 RPO 和 RTO 容灾。
SDR：Snapshot Data Replication，快照型容灾，解决企业核心应用的数据级容灾，可以对数据库、虚机、文件等实现多版本本地备份和自动备份上云，还可以实现应用服务器的小时级别 RPO、RTO 的整机容灾。

优点：

高性能：秒级 RPO，数据实时复制，分钟级 RTO，云上快速整机拉起
低成本：免机房建设，云上只需要极少计算资源，主要消耗云盘
可验证：一键演练，10分钟 完成验证
易运维：全链路监控、故障自动告警

名词概念：

概念	描述
故障转移	即容灾恢复，指您的 IDC 应用出现故障时，在阿里云上恢复应用的过程
故障恢复	当您的 IDC 内的环境恢复以后，将应用数据迁回自有 IDC 恢复应用运行的过程
RPO	Recovery Point Objective（数据恢复点目标），指应用发生故障时预期的数据丢失量
RTO	Recovery Time Objective（恢复时间目标），指故障发生时将应用恢复运行所需要的时间
混合云灾备一体机	阿里云推出具有容灾备份功能的一体机，适用于快照型容灾（SDR）与连续复制型容灾（CDR）
容灾恢复网关	适用于快照型容灾，是在阿里云上按需部署的容灾恢复服务器，它可以根据云上容灾存储中的备份数据，在 `ECS` 上恢复您的云下服务器整机、文件和应用数据。

GB/T 20988-2007标准：是中国国家标准化管理委员会制定的信息系统灾难恢复规范参考

方案

需求分析

数据保护和业务连续性对数据中心的意义重大，关键应用的故障或数据丢失会对您的业务造成重大损失。混合云容灾服务提供了三个层次的能力来保护数据，并确保业务连续性。

本地备份

混合云灾备一体机可以按照预设策略将服务器整机镜像和文件备份至本地，并在需要时恢复至虚拟化平台和物理平台，物理平台仅支持文件级恢复。
异地备份

服务器镜像和数据备份后会直接上传至阿里云灾备库，实现高可靠的云上异地备份。稳定的异地备份确保关键数据在本地数据中心发生火灾等极端情况下不丢失，在本地设施修复后恢复至本地。
云上容灾

为减少因应用故障导致的业务损失，当数据中心出现严重故障无法快速恢复时，混合云容灾服务可以高效地在 ECS 上快速恢复您的应用。

选型

CDR

原理：基于磁盘级实时数据复制技术，可以提供秒级-分钟级的 RPO/RTO。

方案：

类型：

型号	支持服务器数量
Apsara DR100	< 20
Apsara DR200	< 50

SDR

原理：基于数据定时备份能力，提供了小时级-天级的 RPO/RTO，并且可以与业务网络流量错峰。

型号	支持服务器数量	支持的源数据量	备份恢复吞吐
HDR1000	< 20	< 10 TB	0.5 TB/小时
HDR2000	20~100	20 TB~100 TB	1 TB/小时

对比：

指标	CDR	SDR
RPO	秒 - 分钟级	小时 - 天级
RTO	分钟级	小时级
多恢复点时限	30天	不限时长
网络要求	较高	较低
灾备一体机硬件	可选	必选

场景	案例	CDR	SDR
关键应用秒级 - 分钟级 RPO、RTO 容灾	企业关键业务允许 5 分钟 RPO，宕机后要求 15 分钟内在云上拉起，恢复业务运行。	√	×
重要应用小时级 RPO、 RTO 云容灾	企业内重要应用需要实现 24 小时 RPO、6 小时 RTO 的应用级容灾。	×	√
关键应用数据级容灾（本地+云双备份）	本地 Oracle、SQL Server 数据库，以及一系列重要文件需要实现多版本的本地备份，同时备份数据上云，实现数据级容灾。	×	√
跨地域容灾	容灾系统部署在阿里云的两个地域中，当主系统发生故障时，业务系统切换到容灾系统。主系统和容灾系统部署在不同的地域，可靠性更高，有效避免了地域性灾害导致的系统故障。	√	×
跨可用区容灾	支持业务的跨可用区（Zone）容灾能力，对整体的应用做容灾备份来应对单地区的故障，满足业务的 RTO/RPO 核心指标。	√	×

网络环境

数据中心到阿里云之间的网络：专线 / VPN / 公网
混合云灾备一体机到被保护服务器之间的网络：备份一体机提供了双千兆、双万兆网卡供选择，可以根据备份恢复吞吐要求按需配置。

权限要求

OSS 权限
云盘权限
VPC 权限

说明：如果用户已直接授予 AliyunHDRFullAccess 权限，则不需要再单独开通 OSS、 ECS、 VPC 等权限。

MySQL · 事件

发表于 2019-10-31 | 更新于 2019-11-07 | 分类于 MySQL

简介

MySQL 使用 EVENTS 来处理计划任务，类似于 Linux 上的 cron，默认情况下，事件未启用。

权限：EVENT

语法：

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

参数说明

Property	Value
Command-Line Format	`--event-scheduler[=value]`
System Variable	`event_scheduler`
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	`OFF`
Valid Values	`ON、OFF、DISABLED`

# 参数 event_scheduler
mysql> set global event_scheduler = on;
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

# 创建事件
CREATE EVENT Event_Archive_Daily
ON SCHEDULE EVERY 1 DAY STARTS '2019-11-01 02:00:00'
ON COMPLETION PRESERVE
ENABLE
DO call sp_archive();

# 查询事件
mysql> show create event Event_Archive_Daily\G
*************************** 1. row ***************************
               Event: Event_Archive_Daily
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `Event_Archive_Daily` ON SCHEDULE EVERY 1 DAY STARTS '2019-11-01 02:00:00' ON COMPLETION PRESERVE ENABLE DO call sp_archive()
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

MySQL · 调优案例 · MES

发表于 2019-10-31 | 更新于 2019-11-15 | 分类于 MySQL

数据归档

需求

工厂号	工厂名	月结结束时间	数据库地址	端口
1026	合肥波轮	2019/10/31 11:00	192.168.175.11	3306
1022	合肥空调	2019/10/31 12:00	10.142.201.254	3306

策略

订单日期是 3 个月以前，且订单状态是已完成

表	说明	关联字段	总数据量	归档数据量
base_production_order_t	订单表	Code	17627	5174
bns_pm_operation	条码表	WorkUser_MOrderCode	2656496	1530802
bns_pm_scanhistory_month	扫描记录表	Code	7392827	4370623
bns_qm_processtest	过程检验表	Code	7562577	4508488
bns_pm_prodprocess	工位过站记录表	Order_Code	….	….
bns_pm_barcodecode	物料绑定表	….	6614117	3967682

1 2	select count() from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2'; select count() from bns_pm_barcodecode where scan_time < now()-interval 3 month;

实践

准备

安装 Percona Toolkit
创建归档表

# 归档表的表结构与原表一致，命名统一采用原表名_archive的格式
bns_pm_operation_archive
bns_pm_scanhistory_month_archive
bns_qm_processtest_archive
bns_pm_prodprocess_archive
bns_pm_barcodecode_archive

开始归档

1
2

# 归档命令
nohup pt-archiver --source h=xx,P=3306,u=sre,p='xx',D='xx',t=bns_pm_barcodecode --charset=utf8 --dest  h=xx,P=3306,u=sre,p='xx',D='xx',t=bns_pm_barcodecode_archive --where "scan_time < now()-interval 3 month" --limit 1000 --commit-each --bulk-insert --no-version-check > myout.file 2>&1 &

实时查看进度

1 2	use information_schema; select table_name,table_rows from tables where TABLE_SCHEMA = 'xx' and table_name='xx';

bns_pm_barcodecode

# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_barcodecode --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_barcodecode_archive --where "scan_time < now()-interval 3 month" --limit 1000 --commit-each --bulk-insert --no-version-check > myout.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_barcodecode_archive';

bns_pm_operation

# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_operation --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_operation_archive --where "WorkUser_MOrderCode IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout1.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_operation_archive';

bns_pm_scanhistory_month

# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_scanhistory_month --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_scanhistory_month_archive --where "Code IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout2.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_scanhistory_month_archive';

bns_qm_processtest

# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_qm_processtest --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_qm_processtest_archive --where "Code IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout3.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_qm_processtest_archive';

bns_pm_prodprocess

# 开始归档
nohup pt-archiver --source h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_prodprocess --charset=utf8 --dest  h=192.168.175.11,P=3306,u=sre,p='sre@@p$i',D='cosmo_im_1026',t=bns_pm_prodprocess_archive --where "Order_Code IN (select Code from base_production_order_t where EST < now()-interval 3 month and OrderStatus = '2')" --limit 1000 --commit-each --bulk-insert --no-version-check > myout4.file 2>&1 &

# 查看归档进度
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'cosmo_im_1026' and table_name='bns_pm_prodprocess_archive';

设置自动归档

# 存储过程
DELIMITER $$ 

DROP PROCEDURE IF EXISTS sp_archive$$ 
CREATE PROCEDURE sp_archive()
BEGIN 	
	INSERT INTO bns_pm_barcodecode_archive SELECT * FROM bns_pm_barcodecode WHERE 
	scan_time < now() - interval 3 month;
	DELETE FROM bns_pm_barcodecode WHERE scan_time < now() - interval 3 month;
END $$

DELIMITER ;

# 事件
CREATE EVENT Event_Archive_Daily
ON SCHEDULE EVERY 1 DAY STARTS '2019-11-01 02:00:00'
ON COMPLETION PRESERVE
ENABLE
DO call sp_archive();

慢 SQL 优化

工厂：黄岛滚筒

数据库：10.180.129.24:3306

# 慢SQL1
UPDATE bns_pm_operation a
SET a.T_WMS_1 = '1'
WHERE a.Create_Date < '2019-04-01'
AND a.T_WMS_1 = '0'

# 慢SQL2
SELECT ZYH
FROM t_mes_scan
WHERE PPLACE = '一线总装箱体上线'
ORDER BY REALDATE DESC
LIMIT 0, 1

# 视图
SELECT
	t.ID AS N_ID,
	ifnull( t.WorkUser_RandomBarCode, t.WorkUser_BarCode ) AS SERN,
	t.WorkUser_MOrderCode AS ORDER_ID,
	t.Work_OperationOutMainItemCode AS ZYH,
	t.WorkUser_RightMostItemName AS XH_ID,
	t.WorkUser_LineName AS LINENAME,
	t3.Work_Cell_Desc AS PPLACE,
	t2.ScanTime AS REALDATE,
	t2.User_Code AS YG_ID,
	t2.Create_Date AS CREATEDATE,
	t4.Team_Code AS Team_Code,
	t4.Team_Desc AS Team_Desc,
	t5.Shift_Code AS Shift_Code,
	t5.Shift_Desc AS Shift_Desc 
FROM bns_pm_operation t 
LEFT JOIN bns_pm_scanhistory_month t2 ON t.WorkUser_BarCode = t2.WorkUser_BarCode
LEFT JOIN pm_work_cells_t t3 ON t2.Work_Cell_Code = t3.Work_Cell_Code 
LEFT JOIN base_team_t t4 ON t2.Team_Code = t4.Team_Code
LEFT JOIN base_shifts_t t5 ON t2.Shift_Code = t5.Shift_Code 
WHERE t2.Type = 0
ORDER BY t2.ScanTime

MySQL · 存储过程

发表于 2019-10-31 | 更新于 2019-12-12 | 分类于 MySQL

简介

存储过程处理的是一组 SQL 语句，且没有返回值。

优点

简单：把单条 SQL 封装起来，简化复杂的操作。
安全
高性能

缺点

移植困难，不同数据库的语法差别很大
不易扩展，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱

权限：CREATE ROUTINE 、ALTER ROUTINE 、EXECUTE

语法：

# 创建
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_mysql_while_loop`()
BEGIN
 DECLARE x  INT;
 DECLARE str  TEXT;

 SET x = 1000000001;
 SET str =  '';

 WHILE x  <= 1100000001 DO
 SET  str = CONCAT(str,x,',');
 SET  x = x + 1;
 END WHILE;

 SELECT str;
 END ;;
DELIMITER ;

# 修改
ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  
# 删除
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

# 调用
CALL sp_name([parameter[,...]])
CALL sp_name[()]

# 查询
show procedure status;
show create procedure proc_name;

示例

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

MySQL · 视图

发表于 2019-10-30 | 更新于 2019-12-10 | 分类于 MySQL

简介

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询 SQL。一般情况下，应该将视图用于检索（SELETE），而不用于更新（INSRTE、UPDATE、DELETE）。

视图具有表结构文件，但不存在数据文件。

优点

简化，隐藏复杂的 SQL ，简化复杂 SQL 的数据处理，还可以重新格式化数据
安全，保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

缺点

性能相对较差

性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任何一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，就会发现性能下降得很厉害，主要原因是不能建立索引。

权限

CREATE VIEW：创建视图
SHOW VIEW：查看视图

操作

创建视图

1
2
3

CREATE [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS [SELECT statement]

MySQL 有三种视图策略, 分别是 MERGE， TEMPTABLE， UNDEFINED

MERGE，先将输入的查询语句和视图的声明语句进行合并，然后执行合并后的语句并返回。
TEMPTABLE，先基于视图的声明创建一张 temporary table，当输入查询语句时会直接查询这张 temporary table，TEMPTABLE 的效率要比 MERGE 低。
UNDEFINED，如果创建视图的时候不指定策略，默认使用此策略。UNDEFINED 会自动选择使用上述两种策略中的一个，优先选择 MERGE 策略，无法使用则转为 TEMPTABLE 策略。

显示视图

# 查询单个视图
show create view view_name;

mysql> show create view cosmo_zzwk_2048.yangjisaomiao_view\G
*************************** 1. row ***************************
                View: yangjisaomiao_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`wms`@`%` SQL SECURITY DEFINER VIEW `cosmo_zzwk_2048`.`yangjisaomiao_view` AS select distinct `a`.`ORDER_NO` AS `order_id`,`a`.`OUT_CODE` AS `sm_id`,`a`.`BARCODE` AS `sern`,`a`.`SCAN_TIME` AS `scandate`,`a`.`SCANED_BY` AS `scanuser`,'10.138.228.165' AS `database_ip`,'90J0' AS `SAP_Plant`,'黄岛智慧厨电' AS `sap_description` from `cosmo_zzwk_2048`.`ods_pro_yj_sn` `a` where (`a`.`SCAN_TIME` >= (curdate() - interval 30 day))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

# 查询所有视图
SELECT * FROM information_schema.views;

删除视图

1	drop view view_name;

MySQL · 调优案例 · WMS

发表于 2019-10-30 | 更新于 2019-11-15 | 分类于 MySQL

实例信息

实例	角色	版本	业务库	数据量
10.138.98.20:3306	Master	5.7.23	cosmo_base (5)	82.68G
10.138.98.21:3306	Slave	5.7.23	….	….
10.138.228.40:3306	Master	5.6.39	cosmo_wms_xx (23)	139.5G
10.138.228.41:3306	Slave	5.6.39	….	….
10.138.228.43:3306	Master	5.6.39	cosmo_wms_xx (23)	….
10.138.42.94:3306	Master	10.1.21	cosmo_wms_xx (9)	….
10.138.232.165:3306	Master	5.6.39	cosmo_xx_xx (30)	155.9G
10.138.232.164:3306	Slave	5.6.39	….	….
10.138.232.166:3306	Slave	5.6.39	….	….
10.133.7.31:3306	Master	5.7.23	epg_xx (19)	66.6G

# 10.138.98.20:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_base         | 45.28GB   | 37.38GB    |
| cosmo_wms          | 0.17GB    | 0.03GB     |
| xwsy               | 0.09GB    | 0.03GB     |
| cosmo_epg_5001     | 0.02GB    | 0.00GB     |
| xwsytest           | 0.01GB    | 0.00GB     |
+--------------------+-----------+------------+
9 rows in set, 28 warnings (0.30 sec)

# 10.138.228.40:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_wms_1006     | 17.48GB   | 4.73GB     |
| cosmo_wms_1011     | 17.00GB   | 6.16GB     |
| cosmo_wms_1007     | 11.75GB   | 7.36GB     |
| cosmo_wms_1013     | 9.78GB    | 4.60GB     |
| cosmo_wms_1009     | 7.82GB    | 4.17GB     |
| cosmo_wms_1010     | 6.52GB    | 3.48GB     |
| cosmo_wms_1012     | 5.24GB    | 4.32GB     |
| cosmo_wms_1018     | 3.39GB    | 0.88GB     |
| cosmo_wms_1019     | 3.31GB    | 0.66GB     |
| cosmo_wms_2012     | 2.77GB    | 0.61GB     |
| cosmo_wms_2015     | 2.45GB    | 0.46GB     |
| cosmo_wms_1008     | 1.68GB    | 1.46GB     |
| cosmo_wms_1015     | 1.48GB    | 0.68GB     |
| cosmo_wms_2008     | 1.31GB    | 0.29GB     |
| cosmo_wms_2009     | 1.23GB    | 0.21GB     |
| cosmo_wms_2005     | 1.12GB    | 0.23GB     |
| cosmo_wms_2007     | 1.04GB    | 0.22GB     |
| cosmo_wms_2010     | 0.87GB    | 0.18GB     |
| cosmo_wms_2065     | 0.76GB    | 0.30GB     |
| SPC_COM            | 0.48GB    | 0.29GB     |
| cosmo_wms_2006     | 0.27GB    | 0.05GB     |
| cosmo_wms_2066     | 0.04GB    | 0.05GB     |
| cosmo_wms_3001     | 0.02GB    | 0.00GB     |
+--------------------+-----------+------------+
27 rows in set (3.19 sec)

# 10.138.228.43:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_wms_1031     | 13.06GB   | 4.44GB     |
| cosmo_wms_1026     | 9.78GB    | 4.22GB     |
| cosmo_wms_1022     | 5.39GB    | 3.57GB     |
| cosmo_wms_2019     | 5.34GB    | 1.68GB     |
| cosmo_wms_1028     | 4.92GB    | 2.38GB     |
| cosmo_wms_1029     | 4.20GB    | 2.09GB     |
| cosmo_wms_1025     | 3.83GB    | 2.95GB     |
| cosmo_wms_1032     | 3.67GB    | 1.95GB     |
| cosmo_wms_1030     | 2.96GB    | 2.00GB     |
| cosmo_wms_2025     | 2.79GB    | 0.49GB     |
| cosmo_wms_2026     | 2.52GB    | 0.36GB     |
| cosmo_wms_1027     | 2.47GB    | 1.01GB     |
| cosmo_wms_2018     | 1.96GB    | 0.52GB     |
| cosmo_wms_1024     | 1.57GB    | 1.30GB     |
| cosmo_wms_2023     | 1.02GB    | 0.26GB     |
| cosmo_wms_2020     | 0.78GB    | 0.19GB     |
| cosmo_wms_2022     | 0.77GB    | 0.18GB     |
| cosmo_wms_2021     | 0.44GB    | 0.17GB     |
| cosmo_wms_1023     | 0.27GB    | 0.09GB     |
| cosmo_wms_2024     | 0.26GB    | 0.12GB     |
| cosmo_wms_2027     | 0.14GB    | 0.08GB     |
| cosmo_wms          | 0.12GB    | 0.07GB     |
| cosmo_wms_2028     | 0.02GB    | 0.03GB     |
+--------------------+-----------+------------+
27 rows in set (3.19 sec)

# 10.138.42.94:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_wms_uniall   | 8.21GB    | 1.36GB     |
| cosmo_wms          | 6.10GB    | 4.27GB     |
| cosmo_wms_1002     | 4.12GB    | 3.46GB     |
| cosmo_base         | 3.20GB    | 1.57GB     |
| cosmo_wms_1005     | 1.51GB    | 1.03GB     |
| cosmo_wms_test     | 0.75GB    | 1.00GB     |
| cosmo_wms_testn    | 0.48GB    | 0.31GB     |
| cosmo_wms_2001     | 0.39GB    | 0.10GB     |
| cosmo_wms_2002     | 0.14GB    | 0.00GB     |
+--------------------+-----------+------------+
13 rows in set, 1 warning (0.63 sec)

# 10.138.232.165:3306按库大小排序
+------------------------+-----------+------------+
| table_schema           | data_size | index_size |
+------------------------+-----------+------------+
| cosmo_zzkt_1046        | 30.88GB   | 1.80GB     |
| cosmo_hdgt_1003        | 25.19GB   | 3.87GB     |
| cosmo_fsgt_1045        | 23.05GB   | 0.87GB     |
| cosmo_hdzk             | 20.03GB   | 0.69GB     |
| cosmo_xyyj             | 18.18GB   | 3.63GB     |
| cosmo_jndr_1017        | 5.58GB    | 3.21GB     |
| cosmo_zysj             | 2.39GB    | 1.73GB     |
| cosmo_szjsj_1049       | 1.87GB    | 0.85GB     |
| cosmo_tjbl_1021        | 1.45GB    | 0.81GB     |
| cosmo_szjsj_1048       | 1.35GB    | 0.61GB     |
| cosmo_jzzjc_2064       | 1.01GB    | 0.28GB     |
| cosmo_zddr_1057        | 1.00GB    | 0.12GB     |
| cosmo_lycd_1014        | 0.92GB    | 0.05GB     |
| cosmo_wms              | 0.78GB    | 0.52GB     |
| cosmo_wms_penghai      | 0.52GB    | 0.47GB     |
| cosmo_zzktwk_ruig_2053 | 0.49GB    | 0.17GB     |
| cosmo_zzktwk_2054      | 0.16GB    | 0.07GB     |
| cosmo_fsgtwk_2047      | 0.14GB    | 0.05GB     |
| cosmo_zzwk_2048        | 0.12GB    | 0.05GB     |
| cosmo_plat             | 0.12GB    | 0.06GB     |
| cosmo_jzkg_2056        | 0.11GB    | 0.05GB     |
| cosmo_zysjwk_jad       | 0.09GB    | 0.05GB     |
| cosmo_zysjwk_2085      | 0.04GB    | 0.01GB     |
| cosmo_qddz_1055        | 0.03GB    | 0.02GB     |
| cosmo_zysjwk_bw        | 0.02GB    | 0.00GB     |
| cosmo_xyyjwk           | 0.02GB    | 0.00GB     |
| cosmo_eng_1414         | 0.01GB    | 0.00GB     |
| cosmo_penghai_1215     | 0.01GB    | 0.00GB     |
| cosmo_zdbx_1058        | 0.00GB    | 0.00GB     |
| cosmo_plat_penghai     | 0.00GB    | 0.00GB     |
+------------------------+-----------+------------+
33 rows in set, 20 warnings (6.91 sec)

# 10.133.7.31:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_epg_5001     | 9.37GB    | 4.27GB     |
| cosmo_cqjm_5006    | 7.32GB    | 3.72GB     |
| epg_hdme_5004      | 6.64GB    | 3.31GB     |
| epg_hdzn_5015      | 5.85GB    | 3.56GB     |
| epg_hfsj_5007      | 5.44GB    | 2.64GB     |
| epg_jzbp_5002      | 4.44GB    | 1.65GB     |
| cosmo_dljm_5005    | 3.24GB    | 1.08GB     |
| epg_jnjm_5003      | 2.63GB    | 0.64GB     |
| cosmo_wms          | 0.30GB    | 0.13GB     |
| cosmo_plat         | 0.16GB    | 0.07GB     |
| epg_tjwk_2095      | 0.01GB    | 0.00GB     |
| epg_whzn_5012      | 0.00GB    | 0.00GB     |
| epg_cqzn_5009      | 0.00GB    | 0.00GB     |
| epg_hfzn_5013      | 0.00GB    | 0.00GB     |
| epg_qdzn_5016      | 0.00GB    | 0.00GB     |
| epg_sdbp_5011      | 0.00GB    | 0.00GB     |
| epg_whdk_5014      | 0.00GB    | 0.00GB     |
| epg_zzzn_5010      | 0.00GB    | 0.00GB     |
| epg_sddz_5008      | 0.00GB    | 0.00GB     |
+--------------------+-----------+------------+
23 rows in set (0.90 sec)

架构

wms:
  datasource:
    initialSize: 50
    maxActive: 400
    maxIdle: 50
    minIdle: 50
    maxWait: 10000
    ds1:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.138.228.165:3306?juseUnicode=true&characterEncoding=utf-8
      username: wms
      password: xx
    ds2:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.133.7.31:3306?useUnicode=true&characterEncoding=utf-8
      username: cosmowms_epg
      password: xx
    ds3:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.138.228.165:3306/cosmo_hdzk?useUnicode=true&characterEncoding=utf-8
      username: wms
      password: xx
    ds4:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.138.228.165:3306/cosmo_hdzkwk?useUnicode=true&characterEncoding=utf-8
      username: wms
      password: xx

存在的问题

大量使用视图

Java 笔记

发表于 2019-10-29 | 更新于 2019-11-25 | 分类于 Java

参考

MySQL · 配置参数 · sql_mode

发表于 2019-10-29 | 更新于 2019-11-15 | 分类于 MySQL

参数说明

sql_mode：它定义了 MySQL 应该支持的 SQL 模式，对数据的校验等。

Property	Value
Command-Line Format	`--sql-mode=name`
System Variable	`sql_mode`
Scope	Global, Session
Dynamic	Yes
Type	Set
Default Value (>= 5.7.8)	`ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION`
Default Value (5.7.7)	`ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION`
Default Value (>= 5.7.5, <= 5.7.6)	`ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION`
Default Value (<= 5.7.4)	`NO_ENGINE_SUBSTITUTION`
Valid Values	….

mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ONLY_FULL_GROUP_BY：SELECT 内指定字段必须出现在 ORDER BY 中，否则错误。
STRICT_TRANS_TABLES：为事务存储引擎启用严格的 SQL 模式。
NO_ZERO_IN_DATE：严格模式下，不接受月或日部分为 0 的日期。
NO_ZERO_DATE：严格模式下，认为日期 '0000-00-00' 非法。
ERROR_FOR_DIVISION_BY_ZERO：严格模式下，除 0 错误。
NO_AUTO_CREATE_USER：授权之前必须先创建用户。
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。
STRICT_ALL_TABLES：为所有存储引擎启用严格 SQL 模式，无效的数据值报错。

设置

动态设置

1	mysql> set global sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

静态设置

1 2	[mysqld] sql_mode = NO_ENGINE_SUBSTITUTION

阿里云 RDS 设置

sql_mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,NO_DIR_
IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL
323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,HIGH_NOT
_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH

MySQL · 配置参数 · skip_name_resolve

发表于 2019-10-29 | 分类于 MySQL

参数说明

skip_name_resolve：检查客户端连接时是否解析主机名。

OFF：默认状态，仅在第一次建立连接时缓存 IP 和 Host Name 的映射关系，同一主机的后续连接将直接查看 Host Cache，而不用再次进行 DNS 解析。
ON：禁止 DNS 解析，启用后 MySQL 授权表中就不能使用主机名了，只能使用 IP。

Property	Value
Command-Line Format	`--skip-name-resolve=#`
System Variable	`skip_name_resolve`
Scope	Global
Dynamic	No
Type	Boolean
Default Value	`OFF`

MariaDB [(none)]> show variables like 'skip_name_resolve';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | OFF   |
+-------------------+-------+

MariaDB [mysql]> select user,host,password from user;
+-------------+---------------+-------------------------------------------+
| user        | host          | password                                  |
+-------------+---------------+-------------------------------------------+
| root        | localhost     | *C655557AE3BD4264288BB5C1ED8126A364A6F7ED |
| root        | hlht\_db3     | *C655557AE3BD4264288BB5C1ED8126A364A6F7ED |
| root        | 127.0.0.1     | *C655557AE3BD4264288BB5C1ED8126A364A6F7ED |
| root        | ::1           | *C655557AE3BD4264288BB5C1ED8126A364A6F7ED |
| read_only   | %             | *47646ED664BFD993442F175266B6F4367E8CA123 |
| retl        | %             | *44AB89E92CDF5A148EC5A8BCE2BBEF1E51AAC48D |
| sre         | %             | *66CC8D8F94978869E17ABDA6FD2E1A5D817E619E |
| hdm         | %             | *14533888C6CA3952B3957903FF7E69469C7E3A2B |
+-------------+---------------+-------------------------------------------+

设置

配置文件 my.cnf

1 2	[mysqld] skip-name-resolve

日志警告

# skip_name_resolve = OFF
[Warning] IP address '10.163.25.68' could not be resolved: Name or service not known

# skip_name_resolve = ON
[Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode

Hui Rao

最好的成长是分享

GitHub E-Mail

问题现象

原因分析

解决方法

背景

简介

方案

需求分析

选型

CDR

SDR

网络环境

权限要求

简介

参数说明

数据归档

需求

策略

实践

准备

bns_pm_barcodecode

bns_pm_operation

bns_pm_scanhistory_month

bns_qm_processtest

bns_pm_prodprocess

设置自动归档

慢 SQL 优化

简介

示例

简介

权限

操作

创建视图

显示视图

删除视图

实例信息

架构

存在的问题

目录

参考

参数说明

设置

参数说明

设置