MySQL 数据库优化

呦，又写BUG呢

已于 2024-01-30 15:12:17 修改

阅读量551

点赞数

分类专栏：数据库文章标签：数据库 mysql

于 2023-04-13 17:20:23 首次发布

本文链接：https://blog.csdn.net/qq_43686863/article/details/130117000

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

一、SQL 优化
二、范式化和反范式化
三、数据拆分
四、读写分离

一、SQL 优化

1.1 插入优化

插入大量数据优先考虑批量插入，这样可以减少与数据库的通信次数，提高效率。不过一条 INSERT 也尽量不要超过 1000 条数据，避免由于 SQL 过长引起内存不足或者占用锁的时间过长。

插入时采用手动事务提交，如果是自动事务提交，每条 INSERT 执行后都会有一次事务提交，效率较低。

SHOW VARIABLES LIKE 'autocommit';
# +-------------+-----+
# |Variable_name|Value|
# +-------------+-----+
# |autocommit   |OFF  |
# +-------------+-----+

SET AUTOCOMMIT = 0;
START TRANSACTION;
INSERT INTO `tb_user` VALUES (1, '1');
COMMIT;
SET AUTOCOMMIT = 1;

插入时尽量按照主键顺序插入，这样能减少页分裂，更好地利用页空间，提高插入效率。同时，主键长度不应过长，以避免非聚簇索引空间过大。

大批量插入数据时采用 load 命令，load 命令能够将数据从一个文本文件或 CSV 文件中读取并插入到数据库表中，并且可以快速地批量插入大量数据，比使用 INSERT 语句逐条插入更加高效。

# 连接服务端时加上参数 local-infile
mysql --local-infile -u root -p

# 开启从本地加载文件导入数据的开关
SET GLOBAL local_infile = 1;

# 执行 load 指令将准备好的数据加载到表结构中
LOAD DATA LOCAL INFILE '/root/data.csv' INTO TABLE `tb_user` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

1.2 排序优化

根据排序字段建立合适的索引，多字段排序时遵循最左前缀法则。
尽量使用覆盖索引，覆盖索引能避免回表。

多字段排序，尽量使排序的顺序与索引的排序顺序相同，必要时可以建立逆序索引。

EXPLAIN SELECT name, age FROM tb_user ORDER BY name ASC, age DESC;
# +--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+--------------+
# |id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
# +--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+--------------+
# |1 |SIMPLE     |tb_user|null      |ALL |null         |null|null   |null|1   |100     |Using filesort|
# +--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+--------------+

CREATE INDEX idx_name_age ON tb_user (name ASC, age DESC);

EXPLAIN SELECT name, age FROM tb_user ORDER BY name ASC, age DESC;
# +--+-----------+-------+----------+-----+-------------+------------+-------+----+----+--------+-----------+
# |id|select_type|table  |partitions|type |possible_keys|key         |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-------+----------+-----+-------------+------------+-------+----+----+--------+-----------+
# |1 |SIMPLE     |tb_user|null      |index|null         |idx_name_age|48     |null|1   |100     |Using index|
# +--+-----------+-------+----------+-----+-------------+------------+-------+----+----+--------+-----------+

如果不可避免地出现 filesort 且排序数据量较大，可以适当增加排序缓冲区的大小，默认 256 K。

SHOW VARIABLES LIKE 'sort_buffer_size';
# +----------------+------+
# |Variable_name   |Value |
# +----------------+------+
# |sort_buffer_size|262144|
# +----------------+------+

1.3 更新优化

更新时优先根据索引字段进行更新，因为 InnoDB 中的行锁是针对索引加锁，而不是针对记录加锁，并且该索引不能失效，否则会从行锁升级为表锁。

二、范式化和反范式化

函数依赖：

函数依赖：设 $R (U)$ 是属性集 $U$ 上的关系模式， $X$ 与 $Y$ 是 $U$ 的子集，如果对于 $X$ 的每一个具体值， $Y$ 都有唯一的值与之对应，则称 $X$ 函数决定 $Y$ 或 $Y$ 函数依赖于 $X$ ，记作 $\to Y$ 。函数依赖是关系模式中属性之间的逻辑依赖关系。
决定因子与依赖因子：如果 $X$ 决定 $Y$ ，则 $X$ 为决定因子， $Y$ 为依赖因子。
非平凡的函数依赖：如果 $X$ 决定 $Y$ ，但 $Y$ 不是 $X$ 的子集，则称 $X$ 决定 $Y$ 是非平凡的函数依赖。如 (学号，课程号) 决定 成绩。
平凡的函数依赖：如果 $X$ 决定 $Y$ ，且 $Y$ 是 $X$ 的子集，则称 $X$ 决定 $Y$ 是平凡的函数依赖。如 (学号，课程号) 决定 学号。
完全函数依赖：如果 $X$ 决定 $Y$ ，并且 $X$ 的任何一个真子集都无法决定 $Y$ ，则称 $Y$ 完全函数依赖于 $X$ 。如 (学号，课程号) 决定 成绩。
部分函数依赖：如果 $X$ 决定 $Y$ ，并且 $X$ 的某一个真子集可以决定 $Y$ ，则称 $Y$ 部分函数依赖于 $X$ ，如 (学号，课程号) 决定 姓名。如果 $Y$ 部分函数依赖于 $X$ ，则 $X$ 必定是组合属性。
传递函数依赖：如果 $X$ 决定 $Y$ ，== $Y$ 不是 $X$ 的子集且 $Y$ 无法决定 $X$ ， $Y$ 决定 $Z$ ， $Z$ 不是 $Y$ 的子集==，则称 $Z$ 传递函数依赖于 $X$ 。如 学号 决定 班级，班级 决定 系。

范式理论：

第一范式：对于一个关系模式，如果其所有的属性都是不可分的基本数据项，则称其属于第一范式，简称 1NF。在任何一个关系数据库系统中，第一范式是对关系模式最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。如：(学号，课程号，系，系主任)。
第二范式：对于一个属于第一范式的关系模式，如果其所有非主属性都完全函数依赖于码，则称其属于第二范式，简称 2NF。第二范式即关系模式中不存在非主属性对主属性的部分函数依赖问题。如：(学号，系，系主任)。
第三范式：对于一个属于第二范式的关系模式，如果其所有非主属性都不传递函数依赖于码，则称R属于第三范式，简称 3NF。第三范式解决了关系模式中非主属性对主属性的传递函数依赖问题。如：(学号，系)。
BCNF范式：对于一个属于第三范式的关系模式，如果其所有主属性都不部分和传递函数依赖于码，则称其属于 BCNF 范式。如：(学号，系)。

在这里插入图片描述

范式优化是指合理化表的设计，比如令其符合3NF、消除冗余、节省空间。而反范式化则允许信息冗余或者存放在多个不同的数据表。

范式设计和反范式设计都有优点和缺点:

通常在遇到性能问题的时候，会推荐使用范式设计，因为范式设计数据表更新相比反范式而言会更快。同时由于没有冗余数据，因此需要更改的数据更少，单表存储空间也更小。此外由于缺乏冗余数据，意味着使用 DISTINCT 和 GROUP BY 的查询的需求会更少，可以通过直接查询相关的主表完成这类操作。范式表的缺点在于通常会需要至少一次的联表查询，甚至多张表联合查询。这种查询不但代价高，还可能导致有些索引策略失效。
反范式表最大的特点是同一张表包含了所有信息，因此避免了联合查询。如果不使用联合查询的话，在不使用索引的前提下大部分查询的最糟糕的情况是全表扫描，但即便是这样，也会比没有命中缓存的联合查询快，因为这样避免了随机I/O访问。此外反范式表的单表索引策略会更有效。当然，反范式设计也会有其缺点，一是数据表冗余后会存储空间会变大。二是如果冗余列对应的主表发生了变更，可能需要进行大量的数据行更新。

三、数据拆分

数据拆分主要分为分表和分库，二者又各自对应水平拆分和垂直拆分。

垂直角度（改变结构不改变记录数量）：

垂直分表：将一个表字段拆分成多个表，每个表存储部分字段。好处是避免 I/O 时锁表的次数，分离热点字段和非热点字段，避免大字段 I/O 导致性能下降。原则是业务经常组合查询的字段一个表，不常用字段一个表，文本类型单独分表。
垂直分库：根据业务类型将表分类放到不同的数据库服务器上。好处是避免表之间竞争同一个物理机的资源。原则是根据业务相关性进行划分。

水平角度（改变记录数量不改变结构）：

水平分表：同个数据库内，把一个表的数据按照一定规则拆分到多个表中，对数据进行拆分，不影响表结构。好处是单个表的数据量少了，业务 SQL 执行效率高，降低了系统压力。原则与水平分库类似。
水平分库：把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上。好处是通过多个数据库降低了系统压力。原则是选择合适的分片键和分片策略，和业务场景配合，避免数据热点和访问不均衡，避免二次扩容难度大。