文档中心 云数据库 MySQL 最佳实践 云数据库 MySQL 使用规范

云数据库 MySQL 使用规范

最近更新时间:2019-03-08 15:56:05

目的

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

受众

云数据库 MySQL 产品用户。

权限管理规范

  1. 由于考虑到云数据库 MySQL 的稳定性和安全性,云数据库 MySQL 限制了 super、shutdown、file 权限,有时在云数据库 MySQL 上执行 set 语句时,会出现如下的报错:

    #1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation

    解决方法:如果需要 set 修改相关参数,可以使用控制台【参数修改】功能完成,如果需要修改的参数不在其中,可以提交工单后经评估后协助修改,确保实例稳定;

  2. 按需授权,一般应用程序只授权 DML(SELECT、UPDATE、INSERT、DELETE)权限即可;

  3. 授权对象最小化原则,一般的应用程序访问用户按库级别来授权;

  4. 授权用户访问时只允许特定 IP 或 IP 段访问,可以在控制台配置安全组来做限制,安全组的设置请一定按照控制台提示的标准来操作,如果是公网访问设置安全组的场景,请一定放通所有涉及到的出口 IP;

  5. 管理帐号与开发帐号分离。

日常操作规范

  1. 尽量避免业务高峰期做 online ddl 操作,可以使用的工具请参考:pt-online-sche-machange
  2. 尽量避免业务高峰期批量操作数据,最好在业务低峰期分批来操作;
  3. 尽量避免一个实例跑多个业务,耦合度太高会存在业务之间互相影响的风险;
  4. 禁止使用弱密码,提升数据库实例安全性;
  5. 建议关闭事务自动提交,线上操作养成 begin; 先行的习惯,降低误操作导致数据丢失的风险,误操作亦可使用 TencentDB 的回档功能(目前支持 5 天内任意时间点回档),若相关表不涉及跨库跨表的逻辑,可使用快速回档或者极速回档来更快恢复数据,回档新生成的库表名默认是原库表名_bak
  6. 业务有推广活动等,请提前预估资源并做好实例相关优化,如需求量比较大时请及时与对应的服务经理联系;
  7. 内网连接登录须确保 client 端的 CVM 机器与 TencentDB 是同一账号同一地域的机器;
  8. 控制台下载的 binlog 日志,需要在本地解析的话,须确保客户端的 MySQL 版本与云数据库 MySQL 实例的版本一致,否则会出现解析出乱码的情况,建议使用 3.4 或以上版本的 mysqlbinlog;
  9. 控制台上通过内网在 CVM 上下载冷备文件时,请用引号将 url 包起来,否则会出现 404 报错。

库表设计规范

1.通过业务场景分析和数据访问(包括数据库读写 QPS、 TPS、存储空间等)的预估,合理规划数据库使用资源,也可以在控制台云监控界面,配置云数据库 MySQL 实例的各项监控;

2.建库原则就是同一类业务的表放一个库,不同业务的表尽量避免公用同一个库,尽量避免在程序中执行跨库的关联操作,此操作对后续的快速回档也会产生一定的影响;

3.每张表必须要有主键,即使选不出合适的列做主键,亦必须添加一个无意义的列做主键 MySQL 第一范式标准 InnoDB 辅助索引叶子节点会保存一份主键值,推荐用自增短列作为主键,降低索引所占磁盘空间提升效率, binlog_format 为 row 的场景下,批量删数据没主键会导致严重的主从延迟;

4.认准 InnoDB 引擎 线上 TencentDB 产品,5.6 版本开始即不支持 MyISAM 引擎(官方 MySQL 8.0 开始也不支持),有 memory 引擎需求的,建议使用 redis 或者 memcached 自建数据库迁移到 TencentDB 会 MyISAM 自动转 InnoDB,故需满足以下两点否则会任务失败;

5.存在自增列的表,自增列上必须存在一个单独的索引,若在复合索引中,自增列必须置于第一位;

6.row_format 必须保证为非 fixed;

7.字符集统一使用 utf8mb4 降低乱码风险,部分复杂汉字和 emoji 表情必须使用 utf8mb4 方可正常显示,修改字符集只对修改后创建的表生效,故建议新购 TencentDB 初始化实例时即选择 utf8mb4;

8.小数字段推荐使用 decimal 类型,float 和 double 精度不够,特别是涉及金钱的业务,必须使用 decimal;

9.尽量避免据库中使用 text/blob 来存储大段文本、二进制数据、图片、文件等内容,而是将这些数据保存成本地磁盘文件,数据库中只保存其索引信息;

10.尽量不使用外键,建议在应用层实现外键的逻辑, 外键与级联更新不适合高并发场景,降低插入性能,大并发下容易产生死锁;

11.字段尽量定义为 NOT NULL 并加上默认值,NULL 会给 SQL 开发带来很多坑导致走不了索引,对 NULL 计算时只能用 IS NULL 和 IS NOT NULL 来判断;

12.降低业务逻辑和数据存储的耦合度,数据库存储数据为主,业务逻辑尽量通过应用层实现,尽可能减少对存储过程、触发器、函数、event、视图等高级功能的使用,这些功能移植性、可扩展性较差,若实例中存在此类对象,建议默认不要设置 definer,避免因迁移账号和 definer 不一致导致的迁移失败;

13.短期内业务达不到一个比较大的量级,建议禁止使用分区表。分区表主要用作归档管理,多用于快递行业和电商行业订单表,莫迷信分区表提升性能, 除非业务中 80% 以上的查询走分区字段;

14.对读压力较大,且一致性要求较低(接受数据秒级延时)的业务场景,建议购买只读实例从库来实现读写分离策略。

索引设计规范

  1. 单表的索引数建议不超过 5 个,单个索引中的字段数建议不超过 5 个,太多就起不到过滤作用了,索引也占空间,管理起来也耗资源;
  2. 选择业务中 SQL 过滤走的最多的并且 cardinality 值比较高的列建索引,业务 SQL 不走的列建索引是无意义的,字段的唯一性越高即代表 cardinality 值越高,索引过滤效果也越好,一般索引列的 cardinality 记录数小于 10% 我们可认为这是一个低效索引,例如性别字段;
  3. varchar 字段上建索引时,建议指定索引长度,不要直接将整个列建索引, 一般 varchar 列比较长,指定一定长度作索引已经区分度够高,没必要整列建索引,整列建索引会显得比较重,增大了索引维护的代价,可以用 count(distinct left(列名, 索引长度))/count(*)来看索引区分度;
  4. 避免冗余索引,两个索引(a,b) (a)同时存在,则(a)属于冗余索引——redundant index,若查询过滤条件为 a 列,(a,b)索引就够了,不用单独建(a)索引;
  5. 建复合索引的时候,区分度最高的列放索引的在最左边, 例如 select xxx where a = x and b = x; ,a 和 b 一起建组合索引,a 的区分度更高,则建 idx_ab(a,b) 存在非等号和等号混合判断条件时,必须把等号条件的列前置。如:where a xxx and b = xxx 那么即使 a 的区分度更高,也必须把 b放在索引的最前列,因为走不到索引 a;
  6. 禁止在更新十分频繁、区分度不高的列上建立索引,记录更新会变更 B+ 树,更新频繁的字段建立索引会大大降低数据库性能;
  7. 合理利用覆盖索引来降低 io 开销,在 InnoDB 中二级索引的叶子节点保存的只保存本身的键值和主键值,若一个 SQL 查询的不是索引列或者主键,走这个索引就会先找到对应主键然后根据主键去找需要找的列,这就是回表,这样会带来额外的 io 开销,此时我们可以利用覆盖索引来解决这个问题,比如select a,b from xxx where a = xxx,若 a 不是主键,这时候我们可以创建 a,b 两个列的复合索引,这样就不会回表。

SQL 编写规范

  1. 按需索取,拒绝 select *,规避以下问题:
    无法索引覆盖,回表操作,增加 io;
    额外的内存负担,大量冷数据灌入innodb_buffer_pool_size,降低查询命中率;
    额外的网络传输开销;
  2. UPDATE、 DELETE 操作不使用 LIMIT,必须走 WHERE 精准匹配,limit 是随机的,此类操作会导致数据出错;
  3. 禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性,避免表结构变动导致数据出错;
  4. 尽量避免使用大事务,建议大事务拆小事务,规避主从延迟;
  5. 业务代码中事务及时提交,避免产生没必要的锁等待;
  6. 少用多表 join,大表禁止 join,两张表 join 必须让小表做驱动表,join 列必须字符集一致并且都建有索引;
  7. 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 ;
  8. 避免多层子查询嵌套的 SQL 语句 MySQL5.5 之前的查询优化器把会把 in 改成 exists,走不到索引性,若外表很大则性能会很差;
  9. SQL 语句中最常见的导致索引失效的情况需注意:
    • 隐式类型转换,如索引 a 的类型是 varchar,SQL 语句写成 where a = 1;; varchar 变成了int ;
    • 对索引列进行数学计算和函数等操作,例如,使用函数对日期列进行格式化处理;
    • join 列字符集不统一;
    • 多列排序顺序不一致问题,如索引是 (a,b),SQL 语句是 order by a b desclike;
    • 模糊查询使用的时候对于字符型xxx%形式可以走到一些索引,其他情况都走不到索引;
    • 使用了负方向查询(not,!=,not in 等)。

TIPS:

  1. 上述情况很难完全避免,推荐方案是不要将此类条件作为主要过滤条件,跟在走索引的主要过滤条件之后则问题不大;
  2. 监控上发现全表扫描的量比较大,可以在控制台参数设置log_queries_not_using_indexes,稍后下载慢日志文件分析,但不要开太久以免慢日志暴增;
  3. 业务上线之前做有必要的 SQL 审核,日常运维需定期下载慢查询日志做针对性优化。