原创 GitChat精品课 2018-05-12
作者 奋斗
随着项目用户量的快速增长，前期可能由于应用程序设计、数据库设计及架构不当，大多项目会在用户量百万、日志/流水等表过千万、乃至过亿时，出现写入卡顿、查询缓慢、各种业务瘫痪的场景。
这个时候可能有人会说，花钱买机器吗？
然而，做技术大家也都明白，既然老板花钱请你来，当然就对机器预算有控制的，有些措施是有成本预算的。
这个时候作为技术，唯一能做的事找到系统可能出现瓶颈的地方。
一个系统出现问题，能优化的地方很多，比如应用层、负载层、网络层、缓存层、数据层、存储层等等。
本文主要分享的是，基于互联网公司业务增长的大多场景，当系统从 0 用户到百万用户时，针对数据库层面的优化和手段。项目都是真实可借鉴的，千万级的项目原理也差不多，更多的分而治之。
下面将根据系统阶段发展，逐一描述。
阶段一：数据库设计
项目立项初之数据库表设计
从项目立项开始到未来版本开发及上线，大多公司研发在没有 DBA / 有 DBA 的情况下，对于表的结构设计，可能秉承能用就好，不会注重字段、表关系、存储引擎等选择。
于是第一个版本如期开发，可能当时心中很激动，公司也融资啦，然而随着后续几个版本的迭代，产品开始出现各种各样的问题。
比如：
为什么有的 SQL 在有些地方有数据；
为什么业务表的关系明显是 1:1，却发生数据多条的问题；
为什么写入性能很差，为什么查询性能很慢等。
当这些问题慢慢浮出时，当时作为项目后端研发主要负责人的我成为了直接被领导叼、为什么系统现体验这么差等。因此在后续的版本研发中，我会慢慢从这些地方开始数据库优化之旅。
数据库表设计之字段选型
关于 MySQL 字段类型选择
数字类型：tinyint、smallint、mediumint、int、bigint 等
字符类型：char、varchar
时间类型：date、datetime、timestamp
首先秉承的原则如下：
原则 1: 小就是美
原则 2: 简单就是美
原则 3: 先规范，必要时灵活
数据库关于表设计之优化
① 比如 ip 地址用数字类型存储，第一考虑用字符串，占用字节空间及查询效率，索引占用空间。
② 时间字段放弃了时间类型 datetime，选择了 int，为了查询和索引空间及程序层灵活处理。
③ 比如用户表名字、个人描述等不需要很多字符的数据，尽量可能用固定类型 char 或者 varchar（n）, 但是 n 必须严格控制，而不再是之前随意的 100、200、255，这样严重浪费空间及接口数据传输时的消耗。
④ 避免使用 text，比如用时，独立存放。
⑤ 不建议在数据库中保存图片、文档、视频对象，数据库时用来存储结构话数据，尽量保证它的简单 6、主键字段用于多表关联时，用自增数据类型，不建议用字符类型做主键。
⑥ OLTP 业务需建主键。
⑦ OLAP 系统一般不用主键和外键。
⑧ OLTP 系统看情况是否需要建立外键，对性能要求高，对数据一致性要求不高的情况下，可以不用外键，个人建议最好不需要外键，比如一些可能涉及外键的更新、查询，可以让程序层去处理。
数据库存储引擎之优化
1
MyISAM：MyISAM 查询性能优越，这是因为它的数据及索引都在一个节点上，比如单纯文章、资讯类业务可以使用，无需数据一致性、高并发。
2
InnoDB：InnoDB 是为专注于高并发业务而生的存储引擎，拥有事务来保证数据的一致性和行锁机制，而这些是后面变更部分业务表为 InnoDB 的原因。
3
TokuDB：海量数据、采集数据、高压缩数据。早期日志存储引擎选用 TokuDB，随着用户量及访问量增长，尤其在高峰时期，经常出现 db 写入卡顿、主从 waiting for ack 等。
于是开始基于数据库层面存储引擎的变更，鉴于 TokuDB 优秀的写入性能及数据压缩性能，关键写入时，对业务影响不大，就果断变更。
目前日志写入每天单个端与几百万，还没出现问题，当前已经在构造日志服务系统，毕竟不管哪种存储引擎，DB 单表也不能过大，而且自增主键大多 int 时会有上限。
4
Infobright：由于统计系统需要频繁汇总和分析多大至少 5 张业务大表，鉴于此特意调研了它，感觉有点跟数据仓库差不多，不过由于当时的数据库没有自带这个存储引擎就换 es 了。
阶段二：数据库性能优化
通过阶段一的一些优化和变更，已经解决了一些问题，但是这只是开始第一步。在公司 B 轮融资后，随着公司技术人员的加入，便需要开始数据库索引、SQL 的优化。
索引优化
1
由于 MySQL 索引是一棵平衡 b + 树，然而 b + 树最好的就是查找最小或最大很快，并且随着数据量的增长，树的高度不会很大，因此基于主键查找一条数据时也就是树高度 + 1 次 IO 扫描。
如果查询字段涉及到回表，可能就需要一次回表 IO，根据 MySQL 官方单次 IO 预计是 10ms，也就是说基于主键查询会超级快。
2
MySQL 更新操作尽量基于主键更新，因为很多研发喜欢 udpate xxxx where yyyy ，可是很多时候 where 容易不写条件会导致可怕的数据异常（关于这个问题，哪怕很多知名互联网公司也出现过，呵呵），还有就是 where 条件里没有索引，会锁住 where 里需要扫描的行数。
如果需要扫描的行是 all，哪这个问题，估计业务长期卡顿，我们当初一个 10 年的研发，当时写里个定时脚本，居然从凌晨到六点，核心业务完全不能运行。
还好这个时间点不适用车时期，所以任何的小操作在特殊场景会发生可怕的危害呀！
3
谨慎合理添加索引，不是越多越好。需要平衡 select 和 dml，考虑索引的效率
数据排除 predicate 及数据过滤 fiter。
4
5
不在列上做运算，让程序去做运算，数据比较时类型一致。
6
索引列一般尽量不更新，频繁更新的列见索引，得慎重。
7
SQL 优化
SQL 尽量保持简单，MySQL 优化器不足，处理负责 SQL 时容易选错执行计划。
MySQL 没有 SQL 级并行、hashjoin、分析函数等特性，处理复杂 SQL 能力不强。
高并发场景下，尽量保持简单 SQL，复杂 SQL 容易产生锁。
复杂 SQL 拆分成简单 SQL。
少用子查询嵌套。
SQL where 条件中的变量都要使用绑定变量。
绑定变量可以提升系统性能，并且提高安全性。
in 字句，使用 lterate + 数组类型变量的方式实现绑定变量，而不是拼接。
避免使用 MySQL 存储过程，除非是单一业务，非核心业务，只是边缘比如批量送券。
减少数据库运算量，降低数据库压力。
灵活使用数据库内置函数和功能，避免研发重复造轮子。
不用 select * ，只查询需要的字段，减少 cpu、内存、网络等消耗，提升性能；减少由于表变更对应用的影响；使用覆盖索引提升性能。
MySQL 一些 SQL 书写尝试：
or 改成 in or 改写成 union in 改成 exists 或 join 避免负向查询或带 % 前缀的模糊查询 count（*）常用处理方式
14. MySQL 分页
1）传统分页偏移量越大代价越大 select ID，name from user limit 100000，102）推荐使用方式 select id，name from user where ID>1000000 limit 11； 3）多表 join 的分页语句，如果过滤条件在单个表上，需要先分页，先 join 4）充分利用索引消除排序 5）性能要求很高时，可以考虑考虑在关系数据库外实现分页
15. 数据 SQL 的 cache
1）关闭 query cache，不然会影响 tps 2）redis 缓存，减少数据库 ops，降低数据库压力
阶段小结
SQL 需要的资源是 cpu+mem+io+net+lock。
优化 SQL 本质上利用合理算法，平衡这些资源，更好的执行 SQL，满足应用需求，最终解决吞吐量和响应时间。
好的 SQL 是数据量增加或者并发增加，SQL 运行时间不变，后者影响不大。
好的 SQL 基本来自于：好的软件架构、好的存储架构、良好的 SQL 书写。
SQL 优化最重要的思想：减少 IO、减少 IO、减少 IO。
阶段三：数据库性能监控及容灾
随着项目的系统优化，用户量在大量增长，运营也在扩张，系统也在良好地运行，公司经历了 C 轮融资。
然而不久之后可能突然出现系统被对手各种攻击，导致了有些时候 DB 主挂全挂、DB 各种事务锁等问题。
MySQL 常用性能监控信息
鉴于上述问题，需要关注 MySQL 的监控信息及演练可能出现的瓶颈。此时研发人员开始用 Python 脚本搭建可视化页面监控一些信息：
1）select * from information_schema.innodb_trx\g 每隔几秒更新当前 MySQL 的事务 id 信息。 2)找到锁等待更多的 SQL 及事务。 select * from information_schema.innodb_locks\g 3）show full processlist； 查看排行靠前的 SQL 及一些连接信息。 4）show engine innodb status\g可以详细的查看 innodb 的 buffer、free buffer、锁、tps 等信息，也能看到一些具体的锁信息。
MySQL 架构扩展
随着业务量越来越大，单台数据库服务器性能已无法满足业务需求，该考虑增加服务器扩展架构了。主要思想是分解单台数据库负载，突破磁盘 I/O 性能，热数据存放缓存中，降低磁盘 I/O 访问频率，还要考虑过程中数据的安全性、高可用性。
增加缓存
数据库增加缓存系统，把热数据缓存到内存，如果缓存中有请求的数据就不再去请求 MySQL，为了减少数据库负载。缓存实现包括本地缓存和分布式缓存。
本地缓存是将数据缓存到本地服务器内存中或者文件中，分布式缓存可以缓存海量数据。
扩展性好，主流的分布式缓存系统包括：Memcached、Redis。
Memcached 性能稳定，数据缓存在内存中，速度很快，QPS 理论可达 8w 左右。如果想数据持久化就选择用 Redis，性能不低于 Memcached。
工作过程：请求数据 ==> redis 是否存在 ==>无（MySQL 数据库）
主从复制与读写分离
鉴于我们系统是读多写少，可部署一主多从架构，主数据库负责写操作，并做双机热备，多台从数据库做负载均衡，负责读操作。
怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率高。另一个种方式通过代理程序实现读写分离，企业中应用较少，会增加中间件消耗。主流中间件代理系统有 MyCat、Atlas 等。
在这种 MySQL 主从复制拓扑架构中，分散单台负载，大大提高数据库并发能力。如果一台从服务器能处理 2000 QPS，那么 3 台就能处理 4000 QPS，而且容易横向扩展，当时系统扩容了四从，高峰时期也能顶住接近 8000QPS，毕竟系统也不是经常做活动，这种架构也可以随时扩容机器。
分库
分库是根据业务将数据库中相关的表分离到不同的数据库中，例如 WEB、日志、车辆轨迹等库。如果业务量很大，还可将分离后的数据库做主从复制架构，进一步避免单库压力过大。
阶段四：数据库维护及监控
系统已经发展到百万用户量，日志表每天写入接近千万、车辆轨迹表几百万的数据，要开始做性能监控的工作了。
性能状态关键指标
关键词： QPS（Queries Per Second，每秒查询书）和 TPS（Transactions Per Second），通过 show status 查看运行状态。
开启慢查询日志
MySQL 开启慢查询日志，分析出哪条 SQL 语句比较慢，支持动态开启。
在 my.cnf 文件中开启，可以指定慢查询多长时间，系统认定为慢 SQL。
数据库备份
使用 XtraBackup 凌晨定时备份数据。
总结
到了这里，如果数据库层面做了以上的优化规范，对于百万用户量、日志过亿、日活几十万的业务，基本上应该说足以支撑了。
个人曾经专职做过 DBA，更能体会研发的一些问题，也看过很多有关百万、千万架构的文章。其中的问题是，有些架构是通用的、可借鉴的，但不代表都能通用，那样恐怕就只需要一个架构师了。
个人始终觉得，业务驱动技术，唯有在业务中体会、并且实施的方案才更加靠谱，希望看完本篇 Chat 的朋友能有所收获。
由于笔者本人文字功底一般，有些地方可能不是很通畅，请见谅，欢迎各位朋友评论留言。
阅读原文请扫描下方二维码。
购买超级会员全场Chat文章免费读，
另有更多VIP特权，每月仅需99元！
12
阅读 4213
投诉
商品推广
精选留言
留言
3
吴东权
文章很棒
昨天
2
作者
谢谢支持
昨天
2
Zzzzzz
看不懂，假装看看
昨天
商品推广
