临川
文章
33
分类
6
评论
0

数据库其他调优策略

| MySQL •
字数总计 17536 | 阅读时长 43分钟 | 阅读量 201

1. 数据库调优的措施

1.1 调优目标

  • 尽可能节省系统资源，使系统吞吐量更大
  • 合理的结构设计和参数调整，提高用户操作的响应速度

1.2 如何定位调优问题

有以下几种方式：

  • 用户的反馈（主要）
  • 日志分析（主要）
  • 服务器资源使用监控
  • 数据库内状况监控

在此中，活动会话(Active Session)监控 是一个重要的指标。

  • 其他

1.4 调优的维度和步骤

第1步：选择合适的DBMS

第2步：优化表设计

  1. 表结构尽量遵循第三范式。使数据结构更加清晰，减少冗余字段。
  2. 若查询操作较多，特别是要进行多表联查时，可以采用反范式的方式进行优化。反范式采用空间换时间的方式，增加冗余字段提升查询效率.
  3. 表字段数据类型的选择

第3步：优化逻辑查询

SQL 查询优化，可以分为逻辑查询优化和物理查询优化。逻辑查询优化是优化SQL语句，采用是对 SQL 语句进行等价变化，对查询语句进行重写

SQL 语句重接包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

第4步：优化物理查询

这部分需要掌握的重点是对索引的创建和使用

第5步：使用 Redis 或 Memcached 作为缓存

Redis 支持持久化，可以将数据存放到硬盘上，性能消耗较大，而Memcached 仅仅是内存存储，不支持持久化

第6 步：库级优化

通过主从架构优化读写策略

1、读写分离

若读和写的业务量较大，并且都在同一个数据库服务器中进行操作，则数据库的性能就会出现瓶颈。为了提升系统的性能，优化用户体验，就可以采用读写分离的方式降低主数据库的负载。例如：在主数据库（master）进行写操作，在从数据库（slave）完成读操作

Snipaste_2022-07-24_09-46-30

2、数据分片

对数据库进行分库分表。可以使用 MySQL 自带的分区表功能，也可以自己做垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）

2. 优化 MySQL 服务器

优化 MySQL 服务器有两个方面：一方面对硬件进行优化；另一方面对 MySQL 服务的参数进行优化。

2.1 优化服务器硬件

服务器的硬件性能直接决定 MySQL 数据库的性能。

  1. 配置较大的内存。增加缓存区容量，减少磁盘I/O
  2. 配置高速磁盘系统
  3. 合理分布磁盘I/O
  4. 配置多处理器

2.2 优化 MySQL 的参数

MySQL 服务配置参数都在 my.cnf 或 my.ini 文件中的 [mysqld] 组中。配置完参数后，需要重启 MySQL 服务才会生效。

  • innodb_buffer_pool_size：表示用 InnoDB 存储引擎的表和索引的最大缓存。不仅缓存索引数据，还缓存表数据。

  • key_buffer_size：索引缓冲区大小。索引缓冲区是所有的线程共享。增加索引缓冲区可以更好的处理索引。不是越大越好，大小取决于内存大小。若值太多，则会导致系统频繁换页，降低系统性能。内存在 4GB 的服务器，可以设置为 256M 或 384M

  • table_cache：表示同时打开的表的个数。值越大，可以同时打开的表就越多。默认为 2402 ，设置到 512~1024.值不是越大越好，同时打开的表过多会影响系统的性能

  • query_cache_size：表示查询缓冲区的大小。

  • query_cache_type：值为 0 时，查询不使用查询缓冲区

    • 值为 1 时，查询都使用缓冲区，除非在查询语句中指定 SQL_NO_CACHE
    • 值为 2 时，只有查询语句中使用了 SQL_CACHE 关键字，才会使用查询缓冲区

  • sort_buffer_size ：表示每个需要进行排序的线程分配的缓冲区大小。增加这个参数的值可以提高 ORDER BY 或 GROUP BY 操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在 4GB 左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6＝ 600MB。

  • join_buffer_size = 8M ：表示 联合查询操作所能使用的缓冲区大小 ，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

  • read_buffer_size ：表示 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节） 。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。默认为64K，可以设置为4M。

  • innodb_flush_log_at_trx_commit ：表示 何时将缓冲区的数据写入日志文件 ，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值为1。

    • 值为 0 时，表示 每秒1次 的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
    • 值为 1 时，表示 每次提交事务时 将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。
    • 值为 2 时，表示 每次提交事务时 将数据写入日志文件， 每隔1秒 将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

  • innodb_log_buffer_size ：这是 InnoDB 存储引擎的 事务日志所使用的缓冲区 。为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。

  • max_connections ：表示 允许连接到MySQL数据库的最大数量 ，默认值是 151 。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数 不是越大越好 ，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。

  • back_log ：用于 控制MySQL监听TCP端口时设置的积压请求栈大小 。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 对于Linux系统推荐设置为小于512的整数，但最大不超过900。

    如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。

  • thread_cache_size ： 线程池缓存线程数量的大小 ，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120。

可以通过如下几个MySQL状态值来适当调整线程池的大小：

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name	    | Value |
+-------------------+-------+
| Threads_cached    | 2     |
| Threads_connected | 1     |
| Threads_created   | 3     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.01 sec)

当 Threads_cached 越来越少，但 Threads_connected 始终不降，且 Threads_created 持续升高，可适当增加 thread_cache_size 的大小。

  • wait_timeout ：指定 一个请求的最大连接时间 ，对于4GB左右内存的服务器可以设置为5-10。
  • interactive_timeout ：表示服务器在关闭连接前等待行动的秒数。

举例：

电商平台在项目初期稳定运行，在双十一活动期间 CPU使用率高达 99%

为了解决问题：

  • InnoDB_flush_log_at_trx_commit
  • InnoDB_buffer_pool_size
  • InnoDB_buffer_pool_instances

（1）调整系统参数 InnoDB_flush_log_at_trx_commit

适用于 InnoDB 存储引擎，默认值为 1 。每次提交事务时，将数据写入到日志，并将日志写入到磁盘中。这样的好处为数据安全性最佳。问题在于每次提交事务时，都要进行磁盘写入操作。

我们可以将参数修改为 2

（2）调整系统参数 InnoDB_buffer_pool_size

InnoDB 存储引擎使用缓存来存储索引和数据。值越大，可以加载到缓存区的索引和数据量就越多，需要磁盘读写就越少

（3）调整系统参数 InnoDB_buffer_pool_instances

此参数可以将 InnoDB 缓冲区分为几个部分，可以提升系统的并行处理能力。这样可以允许多个进程同时处理不同bu

3.优化数据库结构

好的数据库设计方案可以对数据库性能起到事半功倍的效果。合理的数据库结构可以使其占用更小的磁盘空间，加快查询速度。数据库结构的设计需要考虑到数据冗余、查询和更新的速度、字段的数据类型等多方面的内容

3.1 拆分表：冷热数据分离

拆分表：将一个表拆分成 2 个或多个表。将表中某些字段操作次数较多(热数据)，经常查询或更新操作的字段，拆分为单独一个表。另外一些字段的操作次数较低(冷数据)，则拆分成另外一张表。目的：实现：冷热数据分离。减少表的宽度。若放在同一张表中，则加载时需要消耗较多的资源。

MySQL 限制每张表最多存储 4096 列，并且每一行数据大小不可超过65535 字节。表越宽，将表加载到内存缓冲池所占用的内存就越大，也会导致消耗更多的 I/O。冷热数据分离目的：①减少磁盘 I/O，保证热数据的内存缓存命中率 ②更有效利用缓存，减少读入无用的冷数据

**举例：**会员members表

CREATE TABLE members (
    id int(11) NOT NULL AUTO_INCREMENT,
    username varchar(50) DEFAULT NULL,
    password varchar(50) DEFAULT NULL,
    last_login_time datetime DEFAULT NULL,
    last_login_ip varchar(100) DEFAULT NULL,
    PRIMARY KEY(Id)
);

CREATE TABLE members_detail (
    Member_id int(11) NOT NULL DEFAULT 0,
    address varchar(255) DEFAULT NULL,
    telephone varchar(255) DEFAULT NULL,
    description text
);

若需要基本信息和详细信息同时显示，则可以进行两表联查

SELECT * FROM members LEFT JOIN members_detail on members.id = members_detail.member_id;

3.2 增加中间表

若是经常需要进行两表连接查询的数据，可以建立一张中间表，将需要经常查询两表中的数据插入到中间表中，将两表连接查询改为对中间表的查询，提高查询效率

**举例：**学生信息表和班级表

CREATE TABLE `class` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `className` VARCHAR(30) DEFAULT NULL,
    `address` VARCHAR(40) DEFAULT NULL,
    `monitor` INT NULL ,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

若此时需要经常查询学生名称（name）、学生所在的班级名称（className）、学生班级班长（monitor）的学生信息。可以创建一个 temp_student 表来存储上述字段

CREATE TABLE `temp_student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stu_name` INT NOT NULL ,
    `className` VARCHAR(20) DEFAULT NULL,
    `monitor` INT(3) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into temp_student(stu_name,className,monitor)
    select s.name,c.className,c.monitor
    from student as s,class as c
    where s.classId = c.id

若用户信息修改了，会导致临时表中数据不一致的情况

方式一：清空数据 ➡ 重新添加数据

方式二：使用视图

3.3 增加冗余字段

即第十一章中的**反范式化小节**

3.4 优化数据类型

原则：优先选择符合存储需要的最小的数据类型

举例：

情况1：对整数类型数据进行优化

整数型用 INT 型

非负数整数，优先使用无符号整型UNSIGNED 来存储

情况2：既可以用文本类型也可以使用整数类型的字段，优先使用整数类型

相比于文本类型，整数类型占用的存储空间更小

情况3：避免使用 TEXT、BLOB数据类型

注意：MySQL内存临时表不支持 TEXT、BLOB 大数据类型，若查询中包含这些类型的数据，排序时就无法使用内存临时表，必须使用磁盘临时表。且MySQL 还要进行二次查询

若一定要使用，将这两个类型分离到单独的扩展表中

情况4：避免使用 ENUM 类型

修改ENUM 值需要使用 ALTER 语句

ENUM 类型的ORDER BY 操作效率较低，需要额外的操作。使用 TINYINT 来代替 ENUM 类型

情况5：使用TIMESTAMP 存储时间

TIMESTAMP 存储的范围为 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07。TIMESTAMP占用 4 个字节，DATETIME 占用 8 个字节。同时 TIMESTAMP 具有自动赋值以及自动更新的特性

情况6：用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数

计算时不会丢失精度

总结：在充分了解业务需求的前提下，合理优化数据类型，才可以提高效率

3.5 优化插入记录的速度

1、MyISAM 引擎的表

①禁用索引

对于非空的表，插入大量数据时，建立索引会降低插入记录的速度。可以在插入记录前禁用索引，插入完毕后再开启索引。

禁用语句：

ALTER TABLE 表名 DISABLE KEYS;

开启语句：

ALTER TABLE 表明 ENABLE KEYS;

②禁用唯一性检查

插入数据时，MySQL 会对插入的数据进行唯一性校验。会降低插入数据的速度。可以在插入数据之前禁用唯一性检查，等插入后再开启

禁用语句：

SET UNIQUE_CHECKS = 0;

开启：

SET UNIQUE_CHECKS = 1;

③使用批量插入

尽量使用批量插入比多条插入速度更快

多条插入：

insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',17,1);
insert into student values(3,'wangwu',17,1);
insert into student values(4,'zhaoliu',19,1);

批量插入：

insert into student values
(1,'zhangsan',18,1),
(2,'lisi',17,1),
(3,'wangwu',17,1),
(4,'zhaoliu',19,1);

④使用 LOAD DATA INFILE 批量导入

需要批量导入数据时，若可以使用 LOAD DATA INFILE 则尽量使用，因为其语句导入数据的速度比 INSERT 语句更快。

2、InnoDB 引擎的表

①禁用唯一性检查

插入数据之前 set unique_check = 0 禁用唯一索引的检查，数据插入完成后 set unique_checks = 1 。

②禁用外键检查

插入数据前禁用对外键的检查，插入完后再开启对外键的检查

SET foreign_key_checks = 0;

开启：

SET foreign_key_checks = 1;

③禁止自动提交

插入数据前禁用自动提交，插入完后再开启自动提交

set autocommit = 0;

开启：

set autocommit = 1;

3.6 使用非空约束

在设计表时，若允许，则尽量使用非空约束。好处：

① 进行比较和计算时，省去要对 NULL 值的字段判断是否为空的开销，提高存储效率

② 非空字段也容易创建索引。索引 NULL 列需要额外的空间来存储，需要占用更多的空间。使用非空约束，就可以节省空间（每个字段 1 个bit）

3.7 分析表、检查表与优化表

MySQL 提供了分析表、检查表和优化表的语句。分析表 主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新数据造成的空间浪费。

1.分析表

语句：

ANALYZE (LOCAL/NO_WRITE_TO_BINLOG) TABLE 表名(,表名,..);

可选参数：LOCAL 和 NO_WRITE_TO_BINLOG 作用相同。决定 ANALYZE 语句不写入到 binlog 日志中

在默认情况下，MySQL 服务器会将其语句写入到 binlog 中，为了在主从架构中，从服务能够同步数据。

使用 ANALYZE TABLE 分析表过程中，数据库会自动为表加一个 只读锁。分析期间，只可读取表中数据，无法更新或插入数据。ANALYZE TABLE 语句可以分析 InnoDB 和 MyISAM 类型的表，但是不可以作用于视图。

2. 检查表

CHECK TABLE 语句可以检查 InnoDB 和 MyISAM 类型的表是否存在错误。CHECK TABLE 语句在执行过程中也会给表加上 只读锁

语句：

CHECK TABLE 表名(,..) ... option ...
option = {QUICK / FAST / MEDIUM / EXTENDED /CHANGED}

参数：option（MyISAM 可以使用，InnoDB 不支持使用）

  • QUICK：不扫描行，不检查错误的连接
  • FAST：只检查没有被正确关闭的表
  • CHANGED：只检查上次检查后被修改的表和没有被正确关闭的表
  • MEDIUM：扫描行，验证被删除的连接是有效的
  • EXTENDED：对每行的所有关键字进行全面查找

3. 优化表

方式1：OPTIMIZE TABLE

可以使用 OPTIMIZE TABLE 来优化表，但只可优化表中的 VARCHAR、BLOB或TEXT 类型的字段。

若表中删除了一大部分数据，或对含有可变长度的字段进行多次更新，则应该使用 OPTIMIZE TABLE 来整理文件的碎片，以便重新利用空间

语句：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名(,..) ...

OPTIMIZE TABLE 语句对 InnoDB 和 MyISAM 都支持。且执行时会给表加上只读锁。

不同点：

  • 在 MyISAM 中，分析这张表，后整理相关的 MySQL datafile，再回收未使用的空间
  • InnoDB 中，回收空间是通过 Alter table 进行整理空间。在优化期间，MySQL 会创建一张临时表，优化完后会删除原始表，将临时表名 重命名为 原始表

说明： 在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更新，也不需要经常运行， 每周一次 或 每月一次 即可，并且只需要对 特定的表 运行。

3.8 小结

上述方法有利也有弊。例如：

  • 修改数据类型。若要存储的数据超出了取值范围
  • 增加冗余字段，要确保数据一致性
  • 拆分表。则会需要增加连接查询，从而增加额外的开销和成本

需要根据实际的业务需求权衡。

4. 大表优化

当 MySQL 单表中记录数过大时，数据库的 CRUD 性能会明显降低，常见优化措施如下：

4.1 限定查询的范围

禁止不带任何限制数据范围条件的查询语句。例如：当用户查询历史订单时，可以控制在一个月的范围内

4.2 读/写分离

主库负责写，从库负责读

  • 一主一从模式：

  • 双主双从模式：

4.3 垂直拆分

若数据量达到千万级以上，将数据库拆分成多分，不同数据库放在不同服务器上

  • 若数据库中的表过多，可以使用垂直分库的方式，将关联的数据表放在同一个数据库上
  • 若数据表中的列过多，可以使用 垂直分表 的方式，将一张表拆分为多张表，经常使用的字段放在同一张表中

**垂直拆分的优点：**减少读取的 Block 数，减少 I/O 次数。可以简化表结构，便于维护

垂直拆分的缺点：主键出现冗余，会多出连接查询的操作

4.4 水平拆分

  • 尽量将单表的数据控制在 1000万以内。可以使用历史数据归档（应用于日志数据），水平分表（应用于业务数据）来控制数据量大小
  • 水平分表。将大的数据表按照某个字段纬度拆分成不同的小表，每张小表都是相同的表结构。例如：按照年份划分，将不同年份的数据存放到不同的数据表中

缺点：对数据进行分片，会出现分片事务难以解决、跨界点 Join 性能较差。

若要进行分片，则尽量选择客户端分片架构。

两种常见方案：

  • 客户端代理：分片逻辑封装到应用端，封装到 jar 包中，通过修改或封装 JDBC 层来实现
  • 中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。

5. 其他调优策略

MySQL 8.0 新特性

5.1 服务器语句超时处理

MySQL 8.0 时可以设置服务器语句超时限制

设置系统变量 MAX_EXECUTION_TIME实现。（默认值为 0，表示没有时间限制）

SET GLOBAL MAX_EXECUTION_TIME = 2000; 

SET SESSION MAX_EXECUTION_TIME = 2000;

5.2 创建全局通用表空间

MySQL8.0 中可以使用 CREATE TABLESPACE 创建一个全局通用表空间。相比于独立表空间，**使用手动创建共享表空间可以节约元数据方面的内存。**可以在创建表时，指定表属于哪个表空间，也可对已存在的表进行表空间修改

创建共享表空间

CREATE TABLESPACE 共享表空间名 ADD datafile '共享表空间名.ibd' file_block_size = 16K;

指定表空间：

CREATE TABLE 表名(字段名 字段类型,..) engine = innodb default charset utf8mb4 tablespace 共享表空间名;

也可通过 ALTER TABLE 指定表空间

ALTER TABLE 表名 tablespace 共享表空间名;

删除表空间

需要将表空间中的表删除后才可以删除表空间

DROP TABLESPACE 表空间名;

5.3 隐藏索引对调优的帮助

在 MySQL 8.0 中的新特性。当一个索引被隐藏时，则不会被查询优化器使用。这样可以来观察索引对数据库影响。

注意：当索引被隐藏时，仍然会和正常索引一样实时更新。 主键不能设置为 invisible

文章作者： 临川
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 临川羡鱼！
MySQL MySQL
喜欢就支持一下吧