那些高级运维工程师，都是怎么给公司省机器的？

随着项目用户量的快速增长，前期可能由于应用程序设计、数据库设计及架构不当，大多项目会在用户量百万、日志/流水等表过千万、乃至过亿时，出现写入卡顿、查询缓慢、各种业务瘫痪的场景。

这个时候可能有人会说，花钱买机器吗？

然而，做技术大家也都明白，既然老板花钱请你来，当然就对机器预算有控制的，有些措施是有成本预算的。

这个时候作为技术，唯一能做的事找到系统可能出现瓶颈的地方。

一个系统出现问题，能优化的地方很多，比如应用层、负载层、网络层、缓存层、数据层、存储层等等。

本文主要分享的是，基于互联网公司业务增长的大多场景，当系统从 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 优化

1. SQL 尽量保持简单，MySQL 优化器不足，处理负责 SQL 时容易选错执行计划。
2. MySQL 没有 SQL 级并行、hashjoin、分析函数等特性，处理复杂 SQL 能力不强。
3. 高并发场景下，尽量保持简单 SQL，复杂 SQL 容易产生锁。
4. 复杂 SQL 拆分成简单 SQL。
5. 少用子查询嵌套。
6. SQL where 条件中的变量都要使用绑定变量。
7. 绑定变量可以提升系统性能，并且提高安全性。
8. in 字句，使用 lterate + 数组类型变量的方式实现绑定变量，而不是拼接。
9. 避免使用 MySQL 存储过程，除非是单一业务，非核心业务，只是边缘比如批量送券。
10. 减少数据库运算量，降低数据库压力。
11. 灵活使用数据库内置函数和功能，避免研发重复造轮子。
12. 不用 select * ，只查询需要的字段，减少 cpu、内存、网络等消耗，提升性能；减少由于表变更对应用的影响；使用覆盖索引提升性能。
13. 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 的朋友能有所收获。

由于笔者本人文字功底一般，有些地方可能不是很通畅，请见谅，欢迎各位朋友评论留言。