我觉得对于创新型业务系统的设计, 首先满足需求, 其次考虑到万一业务井喷发展所要考虑到的临时解决方案, 为系统升级预留时间.
前言
有人说单表超千万数据就应该分库分表了, 这么玩不合理啊. 但是对于创新业务来讲, 业务系统的设计不可能一上来就预估这么大的容量, 成本和工期都不足矣完成系统的开发工作. 我觉得对于创新型业务系统的设计, 首先满足需求, 其次考虑到万一业务井喷发展所要考虑到的临时解决方案, 为系统升级预留时间.
谁都希望业务井喷, 那么它来了!
具体时间点就不说了, 开始做了一个新业务, 见了一个表, 该表累计数据条不超过 100 万, 提供查询功能. 后来业务量持续上涨, MySQL 磁盘开始报警, 查询超时报警. 而且, 客户需要实时查询该业务表的数据并下载. 头大, 临时改存储方案已经来不及了, 不能耽误 KPI.
先解决眼下问题, 先扩充磁盘. 停止双机房同步, 减少不必要的报警.
但是 1000G 估计也扛不了多久, 和业务同学讨论后, 业务接受的范围 T-7 范围内的数据实时查询下载. 按这个增长量, 7 天也是过亿的记录条数. 但是 7 天的数据磁盘肯定是够用的, 那就要先把历史数据离线存储.
这个也简单, 几行代码的事儿. 当然这样依靠完善的基建.
容量的问题解决了, 那么改对数据分页查询的进行优化. 为了说明问题, 去掉敏感的业务数据, 数据表结构如下:
- CREATE TABLE `t` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
- `a` char(32) DEFAULT ''COMMENT'',
- `b` varchar(64) DEFAULT NULL COMMENT '',
- `c` bigint(20) unsigned NOT NULL COMMENT '',
- `d` varchar(64) NOT NULL COMMENT '',
- `e` tinyint(4) DEFAULT NULL COMMENT '',
- `f` int(11) NOT NULL DEFAULT '0' COMMENT '',
- `g` varchar(32) NOT NULL COMMENT '',
- `h` char(32) DEFAULT NULL COMMENT '',
- `i` varchar(64) DEFAULT NULL COMMENT '',
- `j` varchar(64) DEFAULT NULL COMMENT '',
- `k` datetime DEFAULT NULL COMMENT '',
- `l` int(11) DEFAULT NULL COMMENT '',
- `m` timestamp NULL DEFAULT NULL COMMENT '',
- `n` timestamp NULL DEFAULT NULL COMMENT ''
- PRIMARY KEY (`id`),
- UNIQUE KEY `UK_b` (`b`),
- KEY `IDX_c` (`c`,) USING BTREE
- )
当数据量少时, 我们用下面的分页是没有问题的:
SELECT id,a,b... FROM t LIMIT n,m
例如:
pagesize : 每页显示条数.
pageno: 页码
那么 m=pagesize; n=(pageno-1)*pagesize.
MySQL 的 limit 工作原理就是先读取前面 n 条记录, 然后抛弃前 n 条, 读后面 m 条想要的, 所以 n 越大, 偏移量越大, 性能就越差.
修改 sql, 减少 io 的消耗
SELECT id,a,b... FROM t where id in(SELECT id FROM t LIMIT n,m)
其实这样也避免不了扫描前 n 条, 但是时间已经节约了很多.
上面是每页请求的 RT, 可见随着页数的增加, RT 逐渐上升.
Qps 逐渐下降.
那么如果数据太多的话, 最后一页超时的概率会非常大.
优化后
先卖个关子, 先看看优化后的表现, 这个接口的性能明显提升. 如图所示:
RT 平均在 10ms 左右, 因为返回做了数据处理, RT 最终在 15ms 左右
qps 也很平稳, 应该可以再高一些, 取决于客户的调用.
优化思路
全表扫描肯定不现实, 这时我想到了 LSM, Log Structured Merge Trees. 这种数据结构, 被用在许多产品的文件结构策略: HBase, Cassandra, LevelDB, SQLite,Kafka 等. 是一种非常复杂的复合数据结构, 它包含了 WAL(Write Ahead Log), 跳表 (SkipList) 和一个分层的有序表(SSTable,Sorted String Table).
这里, 没有必要实现一个 LSM 树, 只是参考了其稀疏索引的思想, 能够准确定位数据. 这样就简单了. 步骤如下:
1. 根据分析业务, 构建一个 字段 a,b 的联合索引. 因为 a,b 是数据的查询条件, 且能分离出 1/7 的数据.
ALTER table ADD INDEX index_a_b('a','b')
2. 因为这个表的数据 都是通过 insert ... on duplicate key update ... 来更新的,[这也是线上死锁分析的那篇文章留下的伏笔] , 而且 id 是自增主键, 所以, 所有的数据都是按照入库时的顺序来的, 且后面遇到冲突时修改也是 update 的, 所以主键 id 是不会变的.
在 Redis 中设计 稀疏索引.
在 Redis 中设计 稀疏索引.
key = a+b + 页面
value = 这页的起始 id
比如 以每页 2 条数据为例
- key1 = ab1 value =0;
- key2 = ab1 value =4;
- key3 = ab1 value =8;
- .....
那么第一页:
select * from t where id>0 and a='a' b='b' limit 2;
第二页:
select * from t where id>4 and a='a' b='b' limit 2;
第三页:
- select * from t where id>8 and a='a' b='b' limit 2;
- ....
那么这样就能很快定位到每页的起始 id, 少了大量的扫描操作, 同时使用了索引, 虽然 ab 联合索引 在 ab 值都是一样的时候 区分度不高, 但是这样也保证了 id 的顺序, 不用 order by. 因为主键索引的 id 本来就是有序的.
稀疏索引的计算时机:
在一批数据入库完成后开始稀疏索引的计算.
计算方法:
第一页 :id = 0
第一页数据
select * from t where id>0 and a='a' b='b' limit 2;
第二页: id 计算方法;
select max(t.id) from (select * from t where id>0 and a='a' b='b' limit 2) t;
第三页: id 计算方法;
- select max(t.id) from (select * from t where id>[第二页 id] and a='a' b='b' limit 2) t;
- ..........
依次类推.....
然后写入 Redis , 更新也是同样的道理.
为什么不用覆盖索引呢?
有人肯定会说为什么不用覆盖索引呢, 这样就不用回表了啊!
答案是不能;
假如我们返回的 字段 是 a,b ,c d,e,f, 那么我们建一个 覆盖索引 x.x 的 B + 树如下:
那如果这个时候 我改了 id=5 的值 a=4 改为 a =1
那现在 id 就是不是顺序的了!!!!!!
那用覆盖索引 + order by id 呢?
数据量不大的话也是可以的, 但是这又是何必呢. 我们看看 order by 的原理.
首先 MySQL 会为每个查询线程分配一块内存, 叫做 sort_buffer, 这块内存的作用就是用来排序的. 这块内存有多大呢? 由参数 **sort_buffer_size** 控制, 可以通过如下命令来查看.
- # 查看 sort_buffer 的大小
- show variables like 'sort_buffer_size';
这样有两个问题:
每次都是按照筛选条件全量排序
如果数据量太大内存不够会触发文件排序, 比较慢.
所以还是老老实实用了刚刚的方案. 效果也还不错, 也是仅仅加了几行代码而已
这个临时方案也是平稳运行了 1 年多.(>)
来源: http://database.51cto.com/art/202106/668746.htm