数据库大致可以分为两部分:
其中比较耗时的步骤有:
假设有如下三条语句, 均是根据主键的查询.
# 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 解析非常耗时.
SELECT * FROM t WHERE id = ?;
常规配置的服务器基本可以达到 400000 QPS.
SELECT * FROM t WHERE name = ?;
对于非主键的查询, MySQL 会根据二级索引查询到主索引对应节点的位置. 按照图中的情况, 会首先通过三次 IO 找到对应主键, 在二级索引的叶子节点会同时保存索引字段的值以及主键的值, 再回到主索引通过主键查询到整条记录.
在 MySQL 中, 主键查询是最为高效的一类查询.
DBA 往往希望所有的 SQL 语句都是 KV 查询, 但是往往是不现实的.
SQL 语句允许开发人员用各种方式从表中获取数据, 但 DBA 却不会希望我们这么做.
content varchar(2046) NOT NULL COMMENT '原始消息';
以 InnoDB 存储引擎为例:
text 类型本质上和 varchar 类型没有区别.
MySQL 中, 数据是以页的方式来组织的, 每个数据页默认大小 16 KB, 其中包括页头, 页尾, 中间是一行一行的记录.
图中的每条记录包括 ID, NAME, AGE 和 DETAIL. 假设 DETAIL 是一个大字段, 达到超过了单页的大小, 此时 DB 会新开一个数据页, 当前页通过指针指向该页. 如果一页依然不够, MySQL 就会不断新加数据页直到能够存下为止.
一旦存在这样的大字段, 会带来如下问题:
对于大字段场景可以尝试的优化方案:
以 InnoDB 存储引擎为例:
16KB
, 哪怕只读一行记录, 也需要从磁盘中取出 16KB
数据取出;128KB
中还存在一部分脏页.缓存为什么如此重要:
用了缓存并不一定代表没有问题
降低扫描数据量还是降低排序代价
大多数查询只能使用一个索引, 因此在需要对多个列进行操作的 SQL 语句中, 我们需要准确评估每个索引的开销.
key idx_create_time(createTime)
key idx_price(price)
# 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: range
possible_keys: id_name
key: uk_sess
key_len: 403
ref: NULL
rows: 462642
Extra:Using where; Using filesort
1 row in set(0.00sec)
其中需要重点关注的是: Extra:Using where; Using filesort
问题是为什么使用了索引, 查询效率依然非常慢?
真正的原因是字段太长, 而索引的长度只能覆盖 256 字节, 导致 ORDER BY 无法在内存中完成排序
查询某个用户 id 的分值总和
-- 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'
这条 SQL 的执行顺序:
这个 SQL 的问题在于需要进行大量的回表操作(从二级索引回到一级索引), 然后将全部符合过滤条件的记录放在内存中完成聚合操作.
改进的方法其实很简单, 可以尝试使用 (uid, score) 建立联合索引, 这样只需要查询二级索引就可以获得全部数据.
随机插入 100W 条数据, 现在对比下两条索引的开销.
悲观锁实现:
BEGIN;SELECT count FROM tb WHERE id = ? FOR UPDATE;-- do sthUPDATE tb SET count = count - ? WHERE id = ?;COMMIT;
乐观锁实现:
BEGIN;SELECT count FROM tb WHERE id = ?;UPDATE tb SET count = count - ? WHERE id = ? AND count = :count;COMMIT;-- do sth
读写分离的原理就是将数据库读写操作分散到不同的节点上
读写分离的基本原理就是:
使用读写分离之后, 可能会引入两个问题:
主从复制的延迟可能达到秒级, 如果有大量数据短时间需要完成同步, 延迟甚至可能达到分钟.
主从复制所带来的问题:
如果业务服务器将数据写入到主库后进行读取, 此时读操作访问从库, 而主库的数据没有完全复制过来, 从库是无法读取到最新数据的.
解决方案:
将读写操作区分开来, 然后访问不同的数据库服务器, 一般有两种方式: 程序代码封装和中间件封装
在代码中抽象一个数据访问层, 实现读写操作分离和数据库服务器连接的管理.
特点:
独立一套系统出来, 实现读写分离和数据库服务器连接的管理, 中间件对业务服务器提供 SQL 兼容的协议, 业务服务器无需自己进行读写分离, 对于业务服务器来说, 访问中间件和访问数据库没有区别
特点:
读写分离分散了读写操作的压力, 但没有分散存储的压力, 当数据量达到千万级以上的时候, 单台数据库服务器的存储能力就会成为瓶颈:
路由
, join 操作
, count 操作
等https://destinywang.github.io/blog/2019/01/19/关系型数据库的瓶颈与优化
喜欢,在看