tencent cloud

PrevNext

文档反馈

请输入关键字

Recent Pages

文档
云数据库 MySQL
    文档
    Download PDF

    云数据库 MySQL 使用规范

    最后更新时间：2024-03-20 15:38:16
    下载PDF

      目的

      规范化对云数据库 MySQL 的管理和维护，避免操作不当对云数据库 MySQL 造成不可用等影响。
      指导数据库开发人员合理编写 SQL，发挥云数据库 MySQL 最优性能。

      权限管理规范

      考虑到云数据库 MySQL 的稳定性和安全性，云数据库 MySQL 限制了 super、shutdown、file 权限，有时在云数据库 MySQL 上执行 set 语句时，会出现如下的报错： 
      #1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation
      解决方法：如果需要 set 修改相关参数，可以使用控制台实例管理页的数据库管理>参数设置功能完成。
      按需授权，一般应用程序只授权 DML（SELECT、UPDATE、INSERT、DELETE）权限即可。
      授权对象最小化原则，一般的应用程序访问用户按库级别来授权。
      授权用户访问时只允许特定 IP 或 IP 段访问，可以在控制台配置安全组来做限制，安全组的设置请一定按照控制台提示的标准来操作，如果是公网访问设置安全组的场景，请一定放通所有涉及到的出口 IP。
      管理账号与开发账号分离。

      日常操作规范

      注意事项

      禁止使用弱密码，提升数据库实例安全性。
      内网连接登录须确保 client 端的云服务器 CVM 与云数据库 MySQL 是同一账号同一地域以及同一 VPC 的机器。
      控制台下载的 binlog 日志，若需要在本地解析，须确保客户端 MySQL 版本与云数据库 MySQL 实例版本一致，否则会解析出乱码，建议使用3.4或以上版本的 mysqlbinlog。
      控制台上通过内网在 CVM 上下载冷备文件时，请用引号将 url 包起来，否则会出现404报错。

      建议事项

      尽量避免业务高峰期做 online ddl 操作，可以使用的工具请参考：pt-online-schema-change。
      尽量避免业务高峰期批量操作数据，最好在业务低峰期分批来操作。
      尽量避免一个实例跑多个业务，耦合度太高会存在业务之间互相影响的风险。
      建议关闭事务自动提交，线上操作养成begin;先行的习惯，降低误操作导致数据丢失的风险，误操作亦可使用云数据库 MySQL 的回档功能（目前支持5天内任意时间点回档），若相关表不涉及跨库跨表的逻辑，可使用快速回档或者极速回档来更快恢复数据，回档新生成的库表名默认是原库表名_bak。
      业务有推广活动等，请提前预估资源并做好实例相关优化，如需求量比较大时请及时与对应的服务经理联系。

      库表设计规范

      注意事项

      云数据库 MySQL 5.6 及以上版本不支持 MyISAM 引擎和 Memory 引擎，若有 Memory 引擎的需求建议使用云数据库 Redis 、Memcached；自建数据库迁移到云数据库 MySQL 时，会自动将 MyISAM 引擎转换成 InnoDB 引擎。
      存在自增列的表，自增列上应该至少有一个单独的索引，或者以自增列开头的一个复合索引。
      row_format必须保证为非 fixed。
      每张表必须要有主键，即使选不出合适的列做主键，亦必须添加一个无意义的列做主键，MySQL 第一范式标准 InnoDB 辅助索引叶子节点会保存一份主键值，推荐用自增短列作为主键，降低索引所占磁盘空间提升效率，binlog_format为 row 的场景下，批量删数据没主键会导致严重的主从延迟。
      字段尽量定义为 NOT NULL 并加上默认值，NULL 会给 SQL 开发带来很多问题导致走不了索引，对 NULL 计算时只能用 IS NULL 和 IS NOT NULL 来判断。

      建议事项

      通过业务场景分析和数据访问（包括数据库读写 QPS、 TPS、存储空间等）的预估，合理规划数据库使用资源，也可以在控制台腾讯云可观测平台界面，配置云数据库 MySQL 实例的各项监控。
      建库原则就是同一类业务的表放一个库，不同业务的表尽量避免共用同一个库，尽量避免在程序中执行跨库的关联操作，此操作对后续的快速回档也会产生一定的影响。
      字符集统一使用 utf8mb4 降低乱码风险，部分复杂汉字和 emoji 表情必须使用 utf8mb4 方可正常显示，修改字符集只对修改后创建的表生效，故建议新购云数据库 MySQL 初始化实例时即选择 utf8mb4。
      小数字段推荐使用 decimal 类型，float 和 double 精度不够，特别是涉及金钱的业务，必须使用 decimal。
      尽量避免数据库中使用 text/blob 来存储大段文本、二进制数据、图片、文件等内容，而是将这些数据保存成本地磁盘文件，数据库中只保存其索引信息。
      尽量不使用外键，建议在应用层实现外键的逻辑，外键与级联更新不适合高并发场景，降低插入性能，大并发下容易产生死锁。
      降低业务逻辑和数据存储的耦合度，数据库存储数据为主，业务逻辑尽量通过应用层实现，尽可能减少对存储过程、触发器、函数、event、视图等高级功能的使用，这些功能移植性、可扩展性较差，若实例中存在此类对象，建议默认不要设置 definer，避免因迁移账号和 definer 不一致导致的迁移失败。
      短期内业务达不到一个比较大的量级，建议禁止使用分区表。分区表主要用作归档管理，多用于快递行业和电商行业订单表，分区表没有提升性能的作用， 除非业务中80%以上的查询走分区字段。
      对读压力较大，且一致性要求较低（接受数据秒级延时）的业务场景，建议购买只读实例从库来实现读写分离策略。

      索引设计规范

      注意事项

      禁止在更新十分频繁、区分度不高的列上建立索引，记录更新会变更 B+ 树，更新频繁的字段建立索引会极大降低数据库性能。
      建复合索引时，区分度最高的列放索引的最左边，例如select xxx where a = x and b = x;，a 和 b 一起建组合索引，a 的区分度更高，则建idx_ab(a,b)。存在非等号和等号混合判断条件时，必须把等号条件的列前置，例如，where a xxx and b = xxx那么即使 a 的区分度更高，也必须把 b 放在索引的最前列，因为走不到索引 a。

      建议事项

      单表的索引数建议不超过5个，单个索引中的字段数建议不超过5个，太多起不到过滤作用，索引也占空间，管理起来也耗资源。
      选择业务中 SQL 过滤走的最多的并且 cardinality 值比较高的列建索引，业务 SQL 不走的列建索引是无意义的，字段的唯一性越高即代表 cardinality 值越高，索引过滤效果也越好，一般索引列的 cardinality 记录数小于10%我们可认为这是一个低效索引，例如性别字段。
      varchar 字段上建索引时，建议指定索引长度，不要直接将整个列建索引，一般 varchar 列比较长，指定一定长度作索引已经区分度够高，没必要整列建索引，整列建索引会显得比较重，增大了索引维护的代价，可以用 count(distinct left(列名, 索引长度))/count(*) 来看索引区分度。
      避免冗余索引，两个索引 (a,b) (a) 同时存在，则 (a) 属于冗余索引 redundant index，若查询过滤条件为 a 列，(a,b) 索引就够了，不用单独建 (a) 索引。
      合理利用覆盖索引来降低 IO 开销，在 InnoDB 中二级索引的叶子节点只保存本身的键值和主键值，若一个 SQL 查询的不是索引列或者主键，走这个索引就会先找到对应主键然后根据主键去找需要找的列，这就是回表，这样会带来额外的 IO 开销，此时我们可以利用覆盖索引来解决这个问题，例如select a,b from xxx where a = xxx，若 a 不是主键，这时候我们可以创建 a,b 两个列的复合索引，这样就不会回表。

      SQL 编写规范

      注意事项

      UPDATE、DELETE 操作不使用 LIMIT，必须走 WHERE 精准匹配，LIMIT 是随机的，此类操作会导致数据出错。
      禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显式指定插入的列属性，避免表结构变动导致数据出错。
      SQL 语句中最常见的导致索引失效的情况需注意：
      隐式类型转换，如索引 a 的类型是 varchar，SQL 语句写成 where a = 1; varchar 变成了int。
      对索引列进行数学计算和函数等操作，例如，使用函数对日期列进行格式化处理。
      join 列字符集不统一。
      多列排序顺序不一致问题，如索引是 (a,b)，SQL 语句是 order by a ASC b DESC。
      模糊查询使用的时候对于字符型xxx%形式可以走到一些索引，其他情况都走不到索引。
      使用了负方向查询（not，!=，not in 等）。

      建议事项

      按需索取，拒绝select *，规避以下问题：
      无法索引覆盖，回表操作，增加 I/O。
      额外的内存负担，大量冷数据灌入innodb_buffer_pool_size，降低查询命中率。
      额外的网络传输开销。
      尽量避免使用大事务，建议大事务拆小事务，规避主从延迟。
      业务代码中事务及时提交，避免产生没必要的锁等待。
      少用多表 join，大表禁止 join，两张表 join 必须让小表做驱动表，join 列必须字符集一致并且都建有索引。
      LIMIT 分页优化，LIMIT 80000，10这种操作是取出80010条记录，再返回后10条，数据库压力很大，推荐先确认首记录的位置再分页，例如SELECT * FROM test WHERE id >= ( SELECT sql_no_cache id FROM test order by id LIMIT 80000,1 ) LIMIT 10 ;。
      避免多层子查询嵌套的 SQL 语句，MySQL 5.5 之前的查询优化器会把 in 改成 exists，会导致索引失效，若外表很大则性能会很差。
      说明：
      上述情况很难完全避免，推荐方案是不要将此类条件作为主要过滤条件，跟在走索引的主要过滤条件之后则问题不大。
      监控上发现全表扫描的量比较大，可以在控制台参数设置log_queries_not_using_indexes，稍后下载慢日志文件分析，但不要开太久以免慢日志暴增。
      业务上线之前做有必要的 SQL 审核，日常运维需定期下载慢查询日志做针对性优化。
      联系我们

      联系我们，为您的业务提供专属服务。

      联系我们
      技术支持

      如果你想寻求进一步的帮助，通过工单与我们进行联络。我们提供7x24的工单服务。

      提交工单
      7x24 电话支持
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      隐私条款服务条款缓存条款