MySQL 优化和运维之道
一、 前言
- 历史
- MySQL优点
- 使用简单
- 开源免费
- 扩展性好，在一定阶段扩展性好
- 社区活跃
- 性能可以满足互联网存储和性能需求，离不开硬件的支持
- MySQL 存在的问题
- 优化器对复杂的SQL支持不好
- 对SQL标准支持不好
- 大规模集群方案不成熟，主要指中间件
- ID生成器，全局自增ID
- 异步逻辑复制，数据安全性问题
- Online DDL
- HA 方案不完全
- 备份和恢复方案还是比较复杂，需要依赖外部组件
- 展现给用户的信息过少，排查问题困难
- 众多分支，让人难以选择
二、 数据库开发规范
数据库开发规范定义：
开发规范是针对内部开发的一系列建议或规则，由 OBA 制订(如 果有 OBA 的话)。
开发规范本身也包含几部分 : 基本命名 、约束规范、 字段设计规范、索引规范、使用 规范。
规范存在的意义
- 保证线上数据库scheme 规范；
- 减少出问题概率；
- 方便自动化管理；
- 需要长期坚持规范，这对开发和DBA 来说是双赢；
基本命令和约束规范
- 表字符集选择UTF8，如果需要存储 emoj 表情，需要使用 UTF8mb4（MySQL 5.5.3 版本以后支持）；
- 存储引擎使用InnoDB;
- 变长字符串尽量使用 varchar 或 varbinary;
- 不在数据库中存储图片、文件等；
- 单标数据量控制在1亿以下；
- 库名、表名、字段名不适用保留字；
- 库名、表名、字段名、索引名使用小写字母，以下划线分割，需要见名知意；
- 库表名不要涉及得过长，尽可能用最少的字符表达出表的用途；
字段规范
- 所有字段均定义为 NOT NULL, 除非你想的想存 NULL；
- 字段类型在满足需求条件下越小越好，使用 UNSIGNED 存储非负整数，实际使用时存储负数的场景不多；
- 使用 TMESTAMP 存储时间；
- 使用 varchar存储变长字符串，当然要注意 varchar CM ) 里的 M 指的是字符数而不是字节数:使用 UNSIGNED INT 存储 1Pv4 地址而不是 CHAR (15)，这种方式只能存储 1Pv4，存储不了 1Pv6。
- 使用 DECIMAL存储精确浮点数，用 float 类型可能会存在数据误差；
- 少用 blob text；
关于为什么定义不用NULL 的原因：
- 浪费存储空间，应为 InnoDB 需要额外一个字节来存储；
- 表内默认值 NULL 过多会影响优化器选择执行计划；
索引规范
- 单个索引字段不超过5，单表索引数量不超过5，索引设计遵循 B+Tree 索引最左前缀匹配原则。
- 选择区分度高的列作为索引；
- 建立的索引覆盖80% 主要的查询，不求全，解决问题的主要矛盾就好；
- DML 要和order by、group by 字段建立合适的索引；
- 避免索引的隐式转换；
- 避免冗余索引；
SQL 类规范
- 建议尽量不适用存储过程、触发器、函数等，减少维护成本和性能隐患；
- 避免适用达标的JOIN， MySQL优化器对JOIN 优化策略过于简单；
- 避免在数据库中进行数据运算和其他大量计算任务；
- SQL 合并，主要是DML时 多个value 合并，减少和数据库交互；
- 合理分页，尤其大分页；
- 如果 UPDATE、DELETE 语句不是 LIMIT, 在Staement 日志格式下容易造成主从不一致；
三、数据库运维规范
运维规范主要内容
- SQL 审核，DDL 审核和操作时间，尤其是 Online DDL；
- 高危操作检查，DROP前做好数据备份；
- 权限控制和审计；
- 日志分析，主要是指的MySQL 慢日志和错误日志；
- 高可用方案；
- 数据备份方案
版本选择
- MySQL 社区版，用户群体最大；
- MySQL 企业版，收费；
- Percona Server 版，新特性多；
- MariaDB 版，国内用户不多；
Online DDL 问题
原生MySQL执行DDL时需要锁表， 且在锁表期间业务无法写入数据，这对服务影响很大， MySQL 对这方面的支持比较差。大表做 DDL 对 DBA 来说是很痛苦的，相信很多 人都经历过。如何做到 Online DDL，这个问题是不是就无解了呢? 当然不是 !
可用性
关于可用性，我们来分享一种无缝切主库方案，如下图所示。可以用于日常切换，使用思 路也比较简单。
