创意电子

标题: 关系型数据库的瓶颈与优化 [打印本页]

作者: java乐园 时间: 2021-9-1 21:02
标题: 关系型数据库的瓶颈与优化
1. 数据库的分类

数据库大致可以分为两部分:

传统的关系型数据库, 如: MySQL, Oracle, SQLServer 以及 PostgreSQL; MySQL 是国内使用最广泛的数据库, Oracle 在传统行业应用最为广泛, PostgreSQL 性能和功能都比较完善, 但目前文档和社区尚有待成长.
非关系型数据库, 如 HBase(列式数据库), MongoDB(文档型数据库), Redis(高性能 KV 存储), Lucene(搜刮引擎) 等等.

2. 关系型数据库的瓶颈与优化

2.1 为什么数据库的架构需要调整

互联网的数据增长往往是指数型的;
读写分离, 分布式: 单机性能上存在瓶颈;
NoSQL, 搜刮引擎: 特殊场景的需求无法满足;
分析系统: 无法满足大数据的分析需求;
摆设要求: 同城容灾/异地容灾.

2.2 数据库会遇到什么问题

2.2.1 性能

查询性能
写入更新
并发, 数据量等

2.2.2 功能

新功能: LBS/JSON/特殊业务场景
数据安全性: 强一致性/非强一致性
大数据分析
搜刮等

3. 不同业务场景的存储选型

3.1 一个简单的问题

MySQL 已经有 cache 了, 为何还需要加一层 Redis

3.2 数据库查询开销

其中比较耗时的步骤有:

建立 TCP 连接
生成执行计划
开表
从磁盘扫描数据
关闭连接

3.2.1 SQL 解析

假设有如下三条语句, 均是根据主键的查询.

12345678910

# 1 SELECT id, name, price FROM products WHERE id IN (1, 2, 3, 4, ... 30000); # (1-2s)# 2. 将第一条查询转换成 30000 条语句SELECT id, name, price FROM products WHERE id = 1;...SELECT id, name, price FROM products WHERE id = 30000; # (2-3s)# 3. 将第一条转换成 OR 语句SELECT id, name, price FROM products WHERE id = 1 OR id = 2 OR ... OR id = 30000; # (8-10s)

造成第三条语句执行时间如此长的主要原因就是大量的 OR 语句会导致 SQL 解析非常耗时.
3.2.2 以 MySQL 的 InnoDB 存储引擎主键查询为例

1	SELECT * FROM t WHERE id = ?;

通例配置的服务器基本可以到达 400000 QPS.
3.2.3 如果查询条件不是主键

1	SELECT * FROM t WHERE name = ?;

对于非主键的查询, MySQL 会根据二级索引查询到主索引对应节点的位置. 按照图中的情况, 会首先通过三次 IO 找到对应主键, 在二级索引的叶子节点会同时保存索引字段的值以及主键的值, 再回到主索引通过主键查询到整条记录.
在 MySQL 中, 主键查询是最为高效的一类查询.
DBA 往往盼望全部的 SQL 语句都是 KV 查询, 但是往往是不现实的.

主键查询有限, 有些主键没有业务含义;
计划表布局时, 并没有考虑过主键问题.

SQL 语句允许开发人员用各种方式从表中获取数据, 但 DBA 却不会盼望我们这么做.
3.2.3 数据库的大字段

1	content varchar(2046) NOT NULL COMMENT '原始消息';

以 InnoDB 存储引擎为例:

TinyText/Text/Mediumtext
varchar(256)/varchar(500)/varchar(20000)
tinyBlob/blob/mediumBlob

text 范例本质上和 varchar 范例没有区别.

MySQL 中, 数据是以页的方式来组织的, 每个数据页默认大小 16 KB, 其中包罗页头, 页尾, 中间是一行一行的记录.
图中的每条记录包罗 ID, NAME, AGE 和 DETAIL. 假设 DETAIL 是一个大字段, 到达超过了单页的大小, 此时 DB 会新开一个数据页, 当前页通过指针指向该页. 如果一页依然不敷, MySQL 就会不断新加数据页直到可以或许存下为止.
一旦存在这样的大字段, 会带来如下问题:

查询开销大;
查询影响大, 严峻时会触发热页换出, 引起系统抖动. MySQL 将记录从磁盘读取出来的时候, 大概会有很多数据页, MySQL 自带缓存时非常名贵的, 会导致真正使用频率高的数据页被替换成大字段的数据页. 此外, 对 MySQL 来说, 即便只查记录中的某几个字段, 数据库依然会把整条记录取出, 读进内存, 再举行指定字段的筛选

对于大字段场景可以尝试的优化方案:

是否得当存储关系型数据库;
是否全部数据都需要存数据库;
是否可以新建一张表存储大字段.

3.2.4 数据库缓存利用率

以 InnoDB 存储引擎为例:

MySQL 默认数据页为 16KB, 哪怕只读一行记录, 也需要从磁盘中取出 16KB 数据取出;
MySQL 是以页为最小的缓存单位;
如果每行数据 1kb, 256kb 内存空间能缓存多少行有用数据, 最好的情况是每条数据整齐排列在一个数据页中, 那么可以缓存256条记录, 最坏的情况下每一页只存在一条数据, 那么就只能缓存16条;
在 256KB 的 Buffer Pool 中, 并不是全部空间都用来做数据页缓存, 有很大的一块在 Write Buffer(MySQL 为了优化写操作, 会将一段时间内的写操作先放在 Write Buffer, 再由后台线程定时异步刷新到磁盘上). 然而剩下的 128KB 中还存在一部分脏页.

缓存为什么如此重要:

互联网产品往往读多写少;
扩展缓存远比扩展 DB 简单;
数据库缓存利用率很低;
互联网应用对 DB 响应时间比较敏感, 缓存系统一般性能比较好
只要符合条件的数据都应该走缓存:
修改不频繁的数据;
非实时的数据, 一致性要求不严的数据;
查询频率较高, 带有明显热点请求的数据;

3.2.5 缓存带来的问题

用了缓存并不一定代表没有问题

缓存命中
缓存穿透
缓存失效
缓存一致

3.2.6 选择精确的索引

降低扫描数据量照旧降低排序代价

大多数查询只能使用一个索引, 因此在需要对多个列举行操作的 SQL 语句中, 我们需要准确评估每个索引的开销.

key idx_create_time(createTime)
key idx_price(price)1
SELECT * FROM tb_order WHERE createTime > xxx AND createTime < xxx ORDER BY price DESC;

3.2.7 索引的使用

3.2.7.1 索引字段过长, 超过索引支持

1234	# name varchar(512)# ket idx_name(name(100))SELECT * FROM comment WHERE name >= 'destiny' ORDER BY name ASC LIMIT 100;

上面的例子在实际场景中执行非常慢, 使用 EXPLAIN 打印查询计划:
select_type: SIMPLE table: comment type: rangepossible_keys: id_name key: uk_sess key_len: 403 ref: NULL rows: 462642 Extra:Using where; Using filesort1 row in set(0.00sec)其中需要重点关注的是: Extra:Using where; Using filesort

Using where: 表用到了索引
Using filesort: MySQL 自带的磁盘排序, 并没有用到索引的排序

问题是为什么使用了索引, 查询效率依然非常慢?
真正的原因是字段太长, 而索引的长度只能覆盖 256 字节, 导致 ORDER BY 无法在内存中完成排序
3.2.7.2

查询某个用户 id 的分值总和

123456

-- uid varchar(190) NOT NULL DEFAULT '' COMMENT '用户 id',-- score bigint(20) NOT NULL DEFAULT '0' COMMENT '变动分值, 正增, 负减',-- primary key ID-- KEY idx_uid(uid)SELECT SUM(score) FROM name WHERE uid = '5993156'