MySQL日常使用规范

子林super
70 阅读20分钟

概述

MySQL作为业界广泛采用的关系型数据库，在公司的生产环境IT应用系统中得到了大量使用。截止本文V1.0版本成文时（2024.03.11），公司生产环境业务系统使用的MySQL实例数已将近800个。

为了规范MySQL的部署和运维工作、指导开发人员正确地使用MySQL，IT运维团队结合MySQL的技术特点以及公司实际现状，制定了本规范。本文档应随公司的MySQL管理策略不断进化，扩展和更新内容。

本文档的面向对象为：公司所有与MySQL相关的IT架构设计、开发、运维和DBA人员。




一、 MySQL 介绍

  1. MySQL简介

MySQL是一款开源的关系型数据库管理系统，通过将数据存储在不同的表中，有效地提高了访问速度和灵活性。它被广泛应用于Web应用程序的后端数据存储。MySQL支持多种操作系统和编程语言，具有高性能、高可靠性、可扩展性等优势，同时支持标准的SQL数据语言形式。

  1. 适用场景

MySQL作为一种关系型数据库管理系统，其具有如下功能特点：

  • 高性能：MySQL具有快速的数据存储和检索引擎，能够有效处理大量的读写操作。
  • 多用户支持：MySQL允许多个用户同时访问和管理数据库，并提供了各种安全和权限控制功能。
  • 数据完整性：MySQL支持事务和ACID特性，确保数据库操作的原子性、一致性、隔离性和持久性。
  • 数据复制和备份：MySQL提供了数据复制和备份机制，以实现数据的冗余和灾难恢复。
  • 可扩展性：MySQL支持水平和垂直扩展，可以处理大规模的数据和高并发请求。

因此，MySQL在公司业务模式中的一些常见适用场景如下：

a. 在线零售和交易系统

在线零售和交易系统中应用通常需要实时处理大量交易数据，而MySQL可以提供快速查询和实时数据更新功能。

b. 企业级应用程序

MySQL在公司企业级应用程序中得到广泛应用，包括百宝箱、dsc工单管理系统、IT服务管理系统、HR移动端等。

c. 移动应用程序后台

MySQL可用于支持移动应用程序的后台数据库，如处理骑手用户数据、骑手派送轨迹等。

d. 数据仓库 和大数据应用

MySQL可以处理大量数据，适合作为数据仓库或大数据应用的一部分。它提供了强大的数据聚合功能和报告工具。




二、技术规范

  1. 版本规范

目前公司运维团队支持对如下MySQL版本的交付与维护工作：

  • MySQL 5.7+
  • MySQL 8.0+
  • 目前默认交付5.7.25、8.0.32版本

  1. 架构规范

公司MySQL服务现阶段采用paas服务。提供基于多从库的高可用容灾功能，当主节点发生故障时，可实现故障的快速恢复。公司运维团队支持交付与运维的高可用架构有：同站高可用、A级高可用、S级高可用3种。几种架构的对比如下：

高可用模式主可用 区 实例数备可用 区 实例数可扩容性故障恢复功能
同站高可用20可扩容实例数/可扩容套餐配置大小单站自动故障转移
A级高可用21可扩容实例数/可扩容套餐配置大小单站自动故障转移/跨站切换
S级高可用22可扩容套餐配置大小单站自动故障转移/跨站切换

a. 同站高可用（2+0）

同站高可用是指在同一机房部署一主一从两个MySQL实例。当数据库主库发生故障时，数据库管理平台可实现秒级别故障自动转移。客户端需要配置vip地址信息，来实现MySQL高可用和故障自动切换。

b. A级高可用（2+1）

A级高可用是指在应用主站所在机房部署一主一从两个MySQL实例，并在另一自建机房部署一个灾备实例。当数据库主库发生故障时，数据库管理平台可自动将同机房从库提升为主库实现故障转移。当主机房故障或特殊场景需要时，可人工将主库切换至备站机房的灾备实例上。客户端需要配置vip地址信息进行连接。

c. S级高可用（2+2）

S级高可用是指在应用主站所在机房部署一主一从两个MySQL实例，并在另一自建机房部署两个灾备实例。当数据库主库发生故障时，数据库管理平台可自动将同机房从库提升为主库实现故障转移。当主机房故障或特殊场景需要时，可人工将主库切换至备站机房的灾备实例上。客户端需要配置vip地址信息进行连接。

d. 含uproxy架构

Uproxy是一款轻量级的网络代理工具，能有效提升网络访问速度和安全性，在公司业务中，uproxy通常被用于MySQL的读写分离、负载均衡功能使用。一套标准的uproxy集群中包含一主一从两个实例，当master发生故障时，管理平台可将uproxy的从实例自动提升为主，从而实现秒级的故障转移。客户端需要配置uproxy的vip地址信息进行连接。

e. 含dble架构

dble是一种用于处理数据库分库分表问题的代理中间件。它位于应用系统和数据库之间，负责数据的路由、分发、同步和数据访问控制等任务。通过使用分库分表中间件，数据库能够更好地应对数据规模增长和业务扩展带来的挑战，并且能够有效降低分库分表的复杂性和运维成本。

【注意】 ：

  • S级2+2套餐必须搭配uproxy使用，标准需配备完整4站uproxy服务，本站应用需连本站uproxy
  • 无特殊情况外，不包含uproxy的paas实例，应用必须连接数据库的vip，不可直连主从地址



三、资源规格

RDS超分基线如下：CPU超分比：400%，内存超分比：85%

MySQL资源配置以paas服务的形式提供。可用套餐如下：(可参考IAC报价)

无法复制加载中的内容

MySQL套餐套餐规格iops基线高可用架构备注价格备份价格
mysql.v1.s18c/16g/500GB10002+0NA￥37,998.0014天：￥734.0030天：￥1,470.00
2+1NA￥56,630.00
2+22+2套餐必须搭配uproxy使用该价格已包含4个单元uproxy主机的费用￥186,338.00
mysql.v1.m18c/32g/1024GB20002+0NA￥47,400.0014天：￥1,504.0030天：￥3,010.00
2+1NA￥70,348.00
2+22+2套餐必须搭配uproxy使用该价格已包含4个单元uproxy主机的费用￥204,373.00
mysql.v1.m216c/32g/1024GB30002+0NA￥56,032.0014天：￥1,504.0030天：￥3,010.00
2+1NA￥83,297.00
2+22+2套餐必须搭配uproxy使用该价格已包含4个单元uproxy主机的费用￥221,637.00
mysql.v1.h116c/64g/2048GB30002+0NA￥74,803.0014天：￥3,010.0030天：￥6,020.00
2+1NA￥110,699.00
2+22+2套餐必须搭配uproxy使用该价格已包含4个单元uproxy主机的费用￥257,671.00
mysql.v1.h232c/64g/2048GB30002+0NA￥95,077.0014天：￥3,010.0030天：￥6,020.00
2+1NA￥139,605.00
2+22+2套餐必须搭配uproxy使用该价格已包含4个单元uproxy主机的费用￥295,209.00
uproxy.v1.ksa8c/16g/100GBNA主备HAKSA单元MySQL读写分离中间件（1主1备）KSA单元8c/16g/100GB规格VM * 2NA
uproxy.v1.ksb8c/16g/100GBNA主备HAKSB单元MySQL读写分离中间件（1主1备）KSB单元8c/16g/100GB规格VM * 2NA
uproxy.v1.wga8c/16g/100GBNA主备HAWGA单元MySQL读写分离中间件（1主1备）WGA单元8c/16g/100GB规格VM * 2NA
uproxy.v1.wgb8c /6g/100GBNA主备HAWGB单元MySQL读写分离中间件（1主1备）WGB单元8c/16g/100GB规格VM * 2NA

基于以上配置，交付的MySQL性能基线参考值如下：

  • IOPS参考基数

a. 磁盘 500GB，IOPS约1000

b. 磁盘 1T， IOPS约2000

C. 磁盘1T~2T间 ，IOPS约3000

具体资源规格请根据实际业务情况进行合理选择，避免造成资源浪费。

【注意】 ：mysql paas为物理机资源池部署，即一台物理机上存在多实例，由套餐配置大小做资源隔离，端口作为集群区分标识，因此实际ip地址通常不向开发暴露使用。




四、使用规范

本节主要介绍实践中的公司MySQL最佳实践，并从表结构设计、索引设计、sql使用和应用规约等方面，描述公司MySQL运维和使用规范。

  1. 数据库表结构设计

  • To do

a .【强制】表名及字段名全部使用小写字母、数字与下划线组合

库名、表名、字段名必须使用小写字母或数字，表名及字段名需有实际含义，除约定俗成的英文缩写外，尽量避免使用纯缩写，建议以 "业务名称_表的作用" 来进行命名；禁止出现数字开头，禁止两个下划线中间出现数字，多个单词用下划线'_'分隔，一个项目一个数据库，多个项目慎用同一个数据库。

b.【推荐】表及字段需要明确标注中文注释

表及字段均需标注中文注释，以便维护以及新员工熟悉业务。

c.【强制】表中必须包含 主键

  • 所有表均需要有主键且需要用与业务无关的自增ID，bigint类型做主键(保证ID列不会被修改)

  • innodb为索引组织表，因此主键越短数据检索速度越快

  • 如果不设置主键，系统会维护一个隐藏的主键，然而所有的隐藏主键共享一个自增锁，如果太多的表没有主键此处可能会是一个瓶颈

  • 若表无主键，而存在唯一索引，那么MySQL会使用该唯一索引作为主键，如果该唯一索引很长，数据库表性能会很差

d .【推荐】主键及索引名称统一标识

建议主键索引名为 pk_字段名；唯一 索引名为 uk_字段名；普通索引名则为 idx_字段名

e.【推荐】建议表中创建create_date，update_date时间字段

为方便后期历史数据的归档及清理，或报表系统数据的抽取，建议表中创建以下时间字段

  • create_date datetime类型,记录创建时间,自动生成

  • update_date datetime 类型,记录更新时间,字段更新

f.【推荐】字段尽量添加NOT NULL属性并设置默认值

建议不使用default null。MySQL里的null是一个特殊字符，不是实际意义上的空。因此推荐所有字段都添加not null并设置默认值形式。

g.【强制】字段存储非负数值时增加UNSIGNED属性

任何字段如果为非负数，必须是 unsigned。表达是与否概念的字段，建议使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。

h .【推荐】字段类型选择建议

  • 单表字段VARCHAR类型不超过16000(UTF8MB4)、21000(UTF8)

  • 尽量使用VARCHAR替代CHAR类型

  • 尽量使用DECIMAL替代FLOAT/DOUBLE类型

  • 字段数据包含小数时尽量使用decimal类型而不使用浮点型

float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

i.【推荐】表均使用UTF8或UTF8MB4字符集

j.【推荐】索引的合理创建

  • 合理创建索引可加快查询速度，却影响写入性能，因此要建真正有效的索引，无用索引会增加表维护负担

  • 使用复合索引代替单一索引

  • 字符类型字段尽量使用前缀索引（影响写入性能）

k.【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致

冗余字段应遵循：

  • 不是频繁修改的字段

  • 不是 varchar 超长字段，及 text 字段

  • 冗余字段长度一定要和原始字段长度一致

  • Not to do

a. 【强制】表名及字段禁止过长

所有表名及字段名尽量不超过32个字节,最大不能超过64个字节。字段越短，能更少的占用存储空间，更少的消耗网络IO，减少MySQL内存消耗和APP内存消耗。

b. 【强制】禁止所有表名及字段名使用 MySQL 的保留字和关键字

禁用保留字，如 desc、range、match、delayed 等，详情请参考 MySQL 官方保留字。

c.【强制】禁止使用 外键 约束

有外键约束字段的增、删、改都需要去关联相关表，检查约束消耗性能

d .【强制】禁止创建大字段

  • 表中创建大字段，会极度消耗数据库性能。尽量不使用BLOB和TEXT，对于需要使用BLOB、TEXT等大字段，建议单独拆分一个表,只存放主表ID及BLOB或TEXT字段

  • 禁止使用BIT,枚举数据类型

e.【强制】禁止使用字段存储 明文 密码

不使用字段存储明文密码，密码必须加密存储

f.【推荐】添加字段避免使用after、before

由于无法保证所有的开发在做查询或插入时只选择需要的相关字段，若修改字段前，恰好有人在做select* 等操作，那么通过after、before增加字段时有可能就会对程序有影响，进而可能会影响其他开发使用该表。

g.【强制】禁止对列设置单独的字符集

h .【推荐】索引建议

  • 避免冗余，idx（a,b,c）相当于idx(a),idx(a,b),idx(a,b,c) ，冗余索引增加维护成本，并且优化器在优化查询时需要逐个进行代价计算，影响查询性能

  • 避免将过滤性低的字段创建索引（如表示状态的字段）

  • 避免使用更新非常频繁的字段做索引

  • 避免完全不建索引

  • 一个表不建议超过五个索引

  1. 命令使用

  • To do

a.【推荐】插入多条数据时建议采用insert批量插入命令

insert语句使用批量插入，可降低网络IO所需要的时间。

b. 【强制】where条件中必须使用合适的数据类型，防止隐 式 转换

where条件中必须使用合适的类型，避免隐式转换；如tinyint 类型字段type,写成type='1' ，会导致相关索引无效，可能全表扫描。

c. 【推荐】SQL 性能优化的目标：至少要达到 range 级别，最优为 consts

优化sql时可参考explain查看sql执行计划：

  • consts ：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

  • ref ：指的是使用普通的索引（normal index）。

  • range： 对索引进行范围检索。

d. 【强制】如果有 order by 的场景，请注意索引的有序性

如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

e. 【推荐】SQL代码写成小写

  • 可读性：将SQL代码写成小写可以使代码更加清晰易读。小写字母相对于大写字母来说更加简洁，不易混淆。

  • 兼容性：为了确保代码在不同的数据库平台上能够正常执行，将代码统一写成小写有助于提高兼容性。

  • Not to do

a .【强制】避免使用select * 语句

SELECT语句应只获取需要的字段,不建议使用SELECT *，增加很多不必要的消耗（cpu、io、内存、网络带宽）,且降低了使用覆盖索引的可能性。也为避免以后相关表增加字段造成程序错误，比如INSERT INTO SELECT和SELECT INTO语句会报错。

b.【强制】SELECT 语句禁止显示开启事务

不要显示指定begin,start transaction等事务语句,避免忘记结束事务造成对undo空间的占用。

c.【强制】禁止对where条件对列进行函数或运算

避免在where条件对列进行数学运算或函数运算，会导致相关索引无效，可能出现全表扫描。

d.【强制】禁止where条件中使用模糊匹配：like 前置%

like查询只能使用前缀索引，因此要避免使用like '%abc'，防止因为索引失效导致全表扫描。

e.【推荐】避免多余排序

避免多余排序，使用group by时，默认会进行排序，如不需排序则可以使用order by null。

f.【强制】避免使用嵌套查询

嵌套查询sql性能较低，请避免使用嵌套语句，建议都走单表单sql。

g.【强制】禁止使用INSIDE函数

INSIDE函数易导致主从数据不一致，且会严重影响mysql性能。

h【强制】where子句中禁止对字段进行null值判断

where条件中，应禁止对字段进行null值判断，否则会索引失效，导致全表扫描。

i【强制】where子句中避免使用!=或<>操作符以及not in

where条件中使用！=或<>以及not in，易造成索引失效，触发全表扫描。

j【强制】where子句中避免使用or

应尽量避免在where 子句中通过 or 链接条件；如同一字段少量条件可使用in，如不同字段可使用union all。

  1. 应用规约

To do

a .【推荐】提前规划好表的容量、大表做好数据归档策略

创建新表需要提前预估并规划表的容量大小，对于大表需要做好归档及清理策略，防止表过大影响数据库性能。

b.【推荐】账号命名建议

人用账号：xxx_dev（开发）、xxx_admin（管理员）

应用账号：xxxx_r（只读）、xxxx_w（读写）、xxxx_app（另一种读写）

c.【推荐】设置合理的数据库 jdbc 连接池

建议设置合理的数据库jdbc连接池，连接池设置过大，在高并发时会对数据库产生较大的冲击性，对数据库性能造成很大影响。严重会将数据库服务打挂。

Not to do

  1. 【强制】禁止在数据库中存放图片、二进制大文件
  2. 【强制】不修改 主键

为避免数据页大量移动，禁止修改表的主键。

  1. 【强制】不得使用root@%、root@ localhost 账号。

为保障数据库安全，禁止程序使用root用户连接数据库，请使用专用的程序生产账号进行连接。

  1. 【强制】数据不得放在 mysql 、universe，或者其他系统schema下

禁止将生产环境数据放置在系统库表下，每个业务需建立单独的database，避免相互影响。

  1. 【强制】不得使用Event Scheduler做定时任务调度

Event Scheduler依赖特定节点作为任务调度头。在高可用环境下，为保证所有节点的对等性，不应依赖MySQL的Event Scheduler进行定时任务调度。所有需定时执行的SQL，应通过应用侧的任务管理框架（如Quartz）或平台（如XXL-Job、DolphinScheduler等）调度。

  1. 【建议】建议避免使用视图做业务相关的操作

视图的查询性能较差，同时基表结构变更，需要对视图进行维护，如果视图可读性差且包含复杂的逻辑，都会增加维护的成本。生产环境建议避免创建视图做业务相关操作，可用来作为日常查询和运维使用。

  1. 【建议】生产环境避免创建存储过程

在MySQL中不推荐使用存储过程,容易将业务与数据库耦合,增加系统复杂性,不利于扩展。存储过程在一定程度上也会使程序难以调试和拓展，各种数据库的存储过程语法相差很大，给将来的数据库移植带来很大的困难，且会极大的增加出现BUG的概率。

  1. 【建议】生产环境避免使用触发器

触发器难以开发和维护，不能高效移植，且在复杂的逻辑以及高并发下，容易出现死锁影响业务。

【注意】 sql类变更需求均需通过云枢工作流或公司云发布平台(pms)提交工单，当sql中存在error级别错误时，工单则不予通过。完整的sql审核规则清单请见本文档结尾的附录1。

  1. 特别说明

  1. 隔离级别

公司云交付的MySQL PaaS服务，默认的事务隔离级别为read commited (RC)。




五、监控告警

  1. 监控维度

IT运维团队会基于以下KPI对MySQL进行监控和告警：

  • 主机资源监控：CPU、内存、磁盘、网络
  • MySQL服务指标：实例存活情况、主从复制情况、从库延迟状态
  • MySQL性能指标：QPS、TPS、慢查数量、CPU开销、内存开销、网络开销、连接数、刷脏

  1. 监控告警架构

MySQL由公司MySQL DMP平台管理统一管理，目前正在对接公司云监控体系，通过夜莺发送告警，并对核心指标提供监控大屏。

监控屏地址：infra-grafana.hwwt2.com/d/DMP_ALL/t…

  1. 服务等级

IT运维团队对MySQL的服务支持由DBA团队提供，服务等级定义同线上业务应用分级。

原则上MySQL的服务等级等于所属应用的分级。




六、备份规则

默认保留14天备份

  1. 14天备份规则

一周中，一天做全备，六天做增备，保留2份备份集，即14天备份

  1. 28天备份规则

一周中，一天做全备，六天做增备，保留4份备份集，即28天备份




七、SQLE入口

入口1：http://172.25.182.93:10000/system

入口2：http://172.21.22.216:10000/system




附录

  1. SQL变更审核规则清单

sql变更的规则清单请见下表：

无法复制加载中的内容

  1. MySQL参数清单

MySQL的参数清单请见下表：

无法复制加载中的内容




avatar
文章
阅读
粉丝