单表60亿记录等大数据场景的mysql优化和运维之道|高可用架构。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-2">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年。


单表60亿记录等大数据场景的MySQL优化和运维之道|高可用架构 mysql mysql数据库 mysql ...
索引规范
单个索引字段数不超过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呢,是不是就无解了呢?当然不是!


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

上面表格里提到的 Facebook OSC和5.6 OSC也是目前两种比较靠谱的方案

MySQL 5.6的OSC方案还是解决不了DDL的时候到从库延时的问题,所以现在建议使用Facebook OSC这种思路更优雅

下图是Facebook OSC的思路


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

后来Percona公司根据Facebook OSC思路,用perl重写了一版,就是我们现在用得很多的pt-online-schema-change,软件本身非常成熟,支持目前主流版本。

使用pt-online-schema-change的优点有:

无阻塞写入 完善的条件检测和延时负载策略控制

值得一提的是,腾讯互娱的DBA在内部分支上也实现了Online DDL,之前测试过确实不错,速度快,原理是通过修改InnoDB存储格式来实现。

使用pt-online-schema-change的限制有:

改表时间会比较长(相比直接alter table改表) 修改的表需要有唯一键或主键 在同一端口上的并发修改不能太多
可用性

关于可用性,我们今天分享一种无缝切主库方案,可以用于日常切换,使用思路也比较简单

在正常条件下如何无缝去做主库切换,核心思路是让新主库和从库停在相同位置,主要依赖slave start until 语句,结合双主结构,考虑自增问题。


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

MySQL集群方案:

集群方案主要是如何组织MySQL实例的方案 主流方案核心依然采用的是MySQL原生的复制方案 原生主从同步肯定存在着性能和安全性问题

MySQL半同步复制:

现在也有一些理论上可用性更高的其它方案

Percona XtraDB Cluster(没有足够的把控力度,不建议上) MySQL Cluster(有官方支持,不过实际用的不多)
单表60亿记录等大数据场景的MySQL优化和运维之道|高可用架构 mysql mysql数据库 mysql ...

红框内是目前大家使用比较多的部署结构和方案。当然异常层面的HA也有很多第三方工具支持,比如MHA、MMM等,推荐使用MHA。

sharding拆分问题
Sharding is very complex, so it?s best not to shard until it?s obvious that you will actually need to! sharding是按照一定规则数据重新分布的方式 主要解决单机写入压力过大和容量问题 主要有垂直拆分和水平拆分两种方式 拆分要适度,切勿过度拆分 有中间层控制拆分逻辑最好,否则拆分过细管理成本会很高

曾经管理的单表最大60亿+,单表数据文件大小1TB+,人有时候就要懒一些。


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

上图是水平拆分和垂直拆分的示意图

数据库备份

首先要保证的,最核心的是数据库数据安全性。数据安全都保障不了的情况下谈其他的指标(如性能等),其实意义就不大了。

备份的意义是什么呢?

数据恢复! 数据恢复! 数据恢复!

目前备份方式的几个纬度:

全量备份 VS 增量备份 热备 VS 冷备 物理备份 VS 逻辑备份 延时备份 全量binlog备份

建议方式:

热备+物理备份 核心业务:延时备份+逻辑备份 全量binlog备份

借用一下某大型互联网公司做的备份系统数据:一年7000+次扩容,一年12+次数据恢复,日志量每天3TB,数据总量2PB,每天备份数据量百TB级,全年备份36万次,备份成功了99.9%。

主要做的几点:

备份策略集中式调度管理 xtrabackup热备 备份结果统计分析 备份数据一致性校验 采用分布式文件系统存储备份

备份系统采用分布式文件系统原因:

解决存储分配的问题 解决存储NFS备份效率低下问题 存储集中式管理 数据可靠性更好

使用分布式文件系统优化点:

Pbzip压缩,降低多副本存储带来的存储成本,降低网络带宽消耗 元数据节点HA,提高备份集群的可用性 erasure code方案调研
数据恢复方案

目前的MySQL数据恢复方案主要还是基于备份来恢复,可见备份的重要性。比如我今天下午15点删除了线上一张表,该如何恢复呢?首先确认删除语句,然后用备份扩容实例启动,假设备份时间点是凌晨3点,就还需要把凌晨3点到现在关于这个表的binlog导出来,然后应用到新扩容的实例上,确认好恢复的时间点,然后把删除表的数据导出来应用到线上。

性能优化
复制优化

MySQL复制:

是MySQL应用得最普遍的应用技术,扩展成本低 逻辑复制 单线程问题,从库延时问题 可以做备份或读复制

问题很多,但是能解决基本问题。


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

上图是MySQL复制原理图,红框内就是MySQL一直被人诟病的单线程问题。

单线程问题也是MySQL主从延时的一个重要原因,单线程解决方案:

官方5.6+多线程方案 Tungsten为代表的第三方并行复制工具 sharding
单表60亿记录等大数据场景的MySQL优化和运维之道|高可用架构 mysql mysql数据库 mysql ...
上图是MySQL5.6 目前实现的并行复制原理图,是基于库级别的复制,所以如果你只有一个库,使用这个意义不大。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

分页:12
转载请注明
本文标题:单表60亿记录等大数据场景的MySQL优化和运维之道|高可用架构 mysql mysql数据库 mysql ...
本站链接:http://www.codesec.net/view/534971.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(37)