[转]单表60亿记录等大数据场景的MySQL优化和运维之道 | 高可用架构

作者: NickYang 分类: 大数据,技术文章 发布时间: 2015-11-18 15:50

此文是根据杨尚刚在【QCON高可用架构群】中,针对MySQL在单表海量记录等场景下,业界广泛关注的MySQL问题的经验分享整理而成,转发请注明出处。

杨尚刚,美图公司数据库高级DBA,负责美图后端数据存储平台建设和架构设计。前新浪高级数据库工程师,负责新浪微博核心数据库架构改造优化,以及数据库相关的服务器存储选型设计。

前言

MySQL数据库大家应该都很熟悉,而且随着前几年的阿里的去IOE,MySQL逐渐引起更多人的重视。

MySQL历史

  • 1979年,Monty Widenius写了最初的版本,96年发布1.0
  • 1995-2000年,MySQL AB成立,引入BDB
  • 2000年4月,集成MyISAM和replication
  • 2001年,Heikki Tuuri向MySQL建议集成InnoDB
  • 2003发布5.0,提供了视图、存储过程等功能
  • 2008年,MySQL AB被Sun收购,09年推出5.1
  • 2009年4月,Oracle收购Sun,2010年12月推出5.5
  • 2013年2月推出5.6 GA,5.7开发中

MySQL的优点

  • 使用简单
  • 开源免费
  • 扩展性“好”,在一定阶段扩展性好
  • 社区活跃
  • 性能可以满足互联网存储和性能需求,离不开硬件支持

上面这几个因素也是大多数公司选择考虑MySQL的原因。不过MySQL本身存在的问题和限制也很多,有些问题点也经常被其他数据库吐槽或鄙视

MySQL存在的问题

  • 优化器对复杂SQL支持不好
  • 对SQL标准支持不好
  • 大规模集群方案不成熟,主要指中间件
  • ID生成器,全局自增ID
  • 异步逻辑复制,数据安全性问题
  • Online DDL
  • HA方案不完善
  • 备份和恢复方案还是比较复杂,需要依赖外部组件
  • 展现给用户信息过少,排查问题困难
  • 众多分支,让人难以选择

看到了刚才讲的MySQL的优势和劣势,可以看到MySQL面临的问题还是远大于它的优势的,很多问题也是我们实际需要在运维中优化解决的,这也是MySQL DBA的一方面价值所在。并且MySQL的不断发展也离不开社区支持,比如Google最早提交的半同步patch,后来也合并到官方主线。Facebook Twitter等也都开源了内部使用MySQL分支版本,包含了他们内部使用的patch和特性。

数据库开发规范

数据库开发规范定义:开发规范是针对内部开发的一系列建议或规则, 由DBA制定(如果有DBA的话)。

开发规范本身也包含几部分:基本命名和约束规范,字段设计规范,索引规范,使用规范。

规范存在意义

  • 保证线上数据库schema规范
  • 减少出问题概率
  • 方便自动化管理
  • 规范需要长期坚持,对开发和DBA是一个双赢的事情

想想没有开发规范,有的开发写出各种全表扫描的SQL语句或者各种奇葩SQL语句,我们之前就看过开发写的SQL 可以打印出好几页纸。这种造成业务本身不稳定,也会让DBA天天忙于各种救火。

基本命名和约束规范

  • 表字符集选择UTF8 ,如果需要存储emoj表情,需要使用UTF8mb4(MySQL 5.5.3以后支持)
  • 存储引擎使用InnoDB
  • 变长字符串尽量使用varchar varbinary
  • 不在数据库中存储图片、文件等
  • 单表数据量控制在1亿以下
  • 库名、表名、字段名不使用保留字
  • 库名、表名、字段名、索引名使用小写字母,以下划线分割 ,需要见名知意
  • 库表名不要设计过长,尽可能用最少的字符表达出表的用途

字段规范

  • 所有字段均定义为NOT NULL ,除非你真的想存Null
  • 字段类型在满足需求条件下越小越好,使用UNSIGNED存储非负整数 ,实际使用时候存储负数场景不多
  • 使用TIMESTAMP存储时间
  • 使用varchar存储变长字符串 ,当然要注意varchar(M)里的M指的是字符数不是字节数;使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ,这种方式只能存储IPv4,存储不了IPv6
  • 使用DECIMAL存储精确浮点数,用float有的时候会有问题
  • 少用blob text

关于为什么定义不使用Null的原因

* 1.浪费存储空间,因为InnoDB需要有额外一个字节存储

* 2.表内默认值Null过多会影响优化器选择执行计划

关于使用datatime和timestamp,现在在5.6.4之后又有了变化,使用二者存储在存储空间上大差距越来越小 ,并且本身datatime存储范围就比timestamp大很多,timestamp只能存储到2038年

索引规范

  • 单个索引字段数不超过5,单表索引数量不超过5,索引设计遵循B+ Tree索引最左前缀匹配原则
  • 选择区分度高的列作为索引
  • 建立的索引能覆盖80%主要的查询,不求全,解决问题的主要矛盾
  • DML和order by和group by字段要建立合适的索引
  • 避免索引的隐式转换
  • 避免冗余索引

关于索引规范,一定要记住索引这个东西是一把双刃剑,在加速读的同时也引入了很多额外的写入和锁,降低写入能力,这也是为什么要控制索引数原因。之前看到过不少人给表里每个字段都建了索引,其实对查询可能起不到什么作用。

冗余索引例子

  • idx_abc(a,b,c)
  • idx_a(a) 冗余
  • idx_ab(a,b) 冗余

隐式转换例子

字段:remark varchar(50) NOT Null

MySQL>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127; 1 row in set (0.14 sec)

MySQL>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 1 row in set (0.005 sec)

字段定义为varchar,但传入的值是个int,就会导致全表扫描,要求程序端要做好类型检查

SQL类规范

  • 尽量不使用存储过程、触发器、函数等
  • 避免使用大表的JOIN,MySQL优化器对join优化策略过于简单
  • 避免在数据库中进行数学运算和其他大量计算任务
  • SQL合并,主要是指的DML时候多个value合并,减少和数据库交互
  • 合理的分页,尤其大分页
  • UPDATE、DELETE语句不使用LIMIT ,容易造成主从不一致

数据库运维规范

运维规范主要内容

  • SQL审核,DDL审核和操作时间,尤其是OnlineDDL
  • 高危操作检查,Drop前做好数据备份
  • 权限控制和审计
  • 日志分析,主要是指的MySQL慢日志和错误日志
  • 高可用方案
  • 数据备份方案

版本选择

  • MySQL社区版,用户群体最大
  • MySQL企业版,收费
  • Percona Server版,新特性多
  • MariaDB版,国内用户不多

建议选择优先级为:MySQL社区版 > Percona Server > MariaDB > MySQL 企业版

不过现在如果大家使用RDS服务,基本还以社区版为主

Online DDL问题

原生MySQL执行DDL时需要锁表,且锁表期间业务是无法写入数据的,对服务影响很大,MySQL对这方面的支持是比较差的。大表做DDL对DBA来说是很痛苦的,相信很多人经历过。如何做到Online DDL呢,是不是就无解了呢?当然不是!