数据库学习笔记(3)

原创已于 2025-05-08 12:21:19 修改 · 428 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2022-10-13 19:13:53 首次发布

BE 专栏收录该内容

18 篇文章

订阅专栏

数据库学习笔记(3)

文章目录

数据库学习笔记(3)

数据汇总

看一遍就理解：Group By详解

通常我们想到数据汇总就会想到使用 group by + 聚合函数来实现数据汇总，但实际上其也有很多瓶颈

group by 的问题

当我们执行下面的语句时会看到下图结果，可见 group by 使用到了临时表以及排序

explain select sum(x) as x from bill_billdetail group by test;

到了最重要的一个注意问题啦，group by 使用不当，很容易就会产生慢 SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

如果执行过程中，会发现内存临时表大小到达了上限(控制这个上限的参数就是 tmp_table_size)，会把内存临时表转成磁盘临时表。
如果数据量很大，使用了磁盘临时表，就会占用大量的磁盘空间。

group by 优化

我们一起来想下，执行group by语句为什么需要临时表呢?group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?

group by 后面的字段加索引：加索引之后这些字段值就变的有序了
order by null 不用排序：group by 后的字段不方便加索引时则可以使用这种方式
尽量只使用内存临时表：调大 tmp_table_size，通常大小是 16777216(16M)，show global variables like 'tmp_table_size';
使用 SQL_BIG_RESULT

其他方式：

TODO

null or not null?

关于表字段需不需要配置 not null 呢？

默认使用 NOT NULL：除非有明确理由使用 NULL
为 NOT NULL 字段提供默认值：避免插入错误
理解 NULL 的语义：NULL ≠ 空字符串 ≠ 0
考虑查询模式：频繁查询的字段**避免使用 NULL****
考虑业务规则**：字段是否真的可选？**
一致性：在整个数据库中保持一致的 NULL 处理策略

数据库分片

数据库分片（Database Sharding)详解

引入

水平切分：横向切割一个表，列一样，但是每个表的数据不一样

垂直切分：纵向切割一个表，每个表中列不同

概念

分片（Sharding）是一种与水平切分（horizontal partitioning）相关的数据库架构模式——将一个表里面的行，分成多个不同的表的做法（称为分区）。

每个区都具有相同的模式和列，但每个表有完全不同的行。

同样，每个分区中保存的数据都是唯一的，并且与其他分区中保存的数据无关。

分类

逻辑分片：分片（Sharding）将一个数据分成两个或多个较小的块，称为逻辑分片（logical shards）
物理分片：逻辑分片（logical shards）分布在单独的数据库节点上，称为物理分片（physical shards）

关系：物理分片（physical shards）可以容纳多个逻辑分片（logical shards）。尽管如此，所有分片中保存的数据，共同代表整个逻辑数据集。

特点

分片是自治的：分片间不共享任何相同的数据或服务器资源。
某些特殊情况下，将某些表复制到每个分片作为参考表是有意义的

优点 benefits

可以帮助水平扩展（horizontal scaling）：将更多的机器添加到现有堆栈中，以分散负载，允许更多的流量和更快的处理
1. 引申：垂直扩展（vertical scaling）：升级现有服务器的硬件，通常是添加更多内存或CPU，垂直扩展总是有限的，水平扩展能让架构更加灵活
加速查询响应的时间：通过将一个表分成多个，查询过程会遍历更少的行，并且返回结果集的速度要快得多。
减少宕机（outage）影响：分片数据库宕机后只会影响单个分片，相较于非分片数据库全部宕机后整个应用程序不可用的场景明显更好

缺点 drawbacks

如果操作不正确，则分片过程可能会导致数据丢失或表损坏，这是一个很大的风险；
分片可能最终会变得不平衡；
一旦对数据库进行了分片，就很难将其恢复到未分片的架构。分片前数据库的备份数据，都无法与分片后写入的数据合并。因此，重建原始的非分片架构，需要将新的分区数据与旧备份合并，或者将分区的数据库转换回单个数据库，这两种方法都是昂贵且耗时的。

数据库查询优化

海量数据通过 order by 排序查询的时候会比较耗时，我们可以通过建索引的方式来缩短时间。同时结合 api 加缓存的方式来降低接口响应时间。

MySQL ORDER BY LIMIT Performance Optimization

!Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index:

SELECT * FROM sites ORDER BY date_created DESC LIMIT 10
# add index like this
(date_created) 

SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC LIMIT 10;
# add index like this
(category_id, date_created)

binlog

5.4.4 The Binary Log

What is MySQL BinLog? : A Practical Guide to Using MySQL BinLog in 2023

mysql binary log(bin log) 记录了数据库的所有修改改动，可以用来做数据恢复、复制、监控等。

The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 5.4.3, “The General Query Log”.

当对数据库更新频繁时 binlog 就会累积过多时会比较占用磁盘空间，比如 grafana 使用 MySQL 时会产生大量的 binlog

du -sh /var/lib/mysql/*|grep G
mysql -uroot -ppassword
SHOW BINARY LOGS;
# 删掉 binlog.000xxx 前的所有日志
PURGE BINARY LOGS TO 'binlog.000xxx';

# 可以通过设置 binlog 过期时间进行限制 binlog 对磁盘的占用

shell

How to run SQL script in MySQL?

-- 执行 sql 文件
mysql -u yourusername -p yourpassword yourdatabase < text_file

数据库老化

指在数据库中存储的数据随着时间的推移变得越来越无用或无效，因为它们已经过时或不再需要。

为了解决数据老化问题，可以采取以下措施：

数据库清理和维护：定期清理数据库中的过期数据，可以使用MySQL自带的定时任务（如事件）或第三方工具来实现。此外，还可以定期备份数据库以确保数据的完整性。
数据库优化：优化数据库的性能，例如增加索引、使用缓存、优化查询等，可以减少数据库访问时间，提高查询速度。
数据分析：对数据库中的数据进行分析，找出有用的数据并进行存储，可以减少数据库存储空间的占用，提高数据库性能。
数据归档：对于一些不需要频繁访问的数据，可以将其归档到其他存储介质，例如磁盘、磁带等。这可以释放数据库存储空间，并且可以将不必要的数据迁移到其他存储介质中以节省数据库服务器资源。
数据库预测：对数据进行一些预测分析，利用自动化调整功能将根据配置，自动调整保存池、内存、缓存和其他参数，从而更大限度地提高数据库的性能。

配置问题

通常我们可以通过 bind-address 配置 mysql 远程连接的地址。即可以通过访问哪个 host 访问到该 mysql 实例，MariaDB 安装好后是默认不能通过远程连接的，需要做以下修改：

# 远程连接 mysql 连不上优先考虑这个问题
vim /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 0.0.0.0

事务

事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。
mysql 事务有默认的隔离级别：REPEATABLE READ，对于一个事物中未提交的数据，其他事务是拿不到的，但是同一个事务是可以拿到（golang 实践中拿不到 https://stackoverflow.com/questions/77814803/mysql-cannot-read-uncommited-data-in-the-same-transaction）

dump

mysql dump 是一个命令行工具，用于将 mysql 数据库备份到一个 SQL 文件中，该文件包含创建数据库、表和数据的 SQL 语句，可以用于在其他地方恢复数据库

mysqldump -u username -p my_database > backup.sql

作用

创建数据库备份
将数据库迁移到其他服务器
将数据库恢复到之前的状态
将数据库结构和数据分享给其他人

truncate

truncate table employees;

作用

truncate table 是 mysql 中用于快速清空表中所有数据的命令
比 delete 操作更快，但具有隐式提交事务、重置 AUTO_INCREMENT 计数器，无法回滚等特性
执行该操作需要 drop 权限，不会触发 delete 触发器
主要用于需要快速清空表数据且不需要触发器逻辑的场景