MySQL其他调优策略

数据库其他调优策略

1. 数据库调优的措施

1.1 调优的目标

  • 尽可能 节省系统资源，以便系统可以提供更大负荷的服务。（吞吐量更大）
  • 合理的结构设计和参数调整，以提高用户操作 响应的速度。（响应速度更快）
  • 减少系统的瓶颈，提高 MySQL 数据库整体的性能。

1.2 如何定位调优问题

随着用户量的不断增加，以及应用程序复杂度的提升，我们很难用 "更快" 去定义数据库调优的目标，因为用户在不同时间访问服务器遇到的瓶颈不同，有时是大规模的 并发访问；有时是针对不同业务操作的时候，数据库的 事务处理 和 SQL查询 都会有所不同。因此我们需要更加精细的定位，去确定调优目标。

确定调优目标的几种方式：

  • 用户的反馈（主要）

用户是我们的服务对象，因此他们的反馈是最直接的也是最重要的。

  • 日志分析（主要）

我们可以通过查看数据库日志和操作系统日志等方式找出异常情况，通过它们来定位遇到的问题。

  • 服务器资源使用监控

通过 监控服务器 CPU，内存，I/O 等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比。

  • 数据库内部状况监控

在数据库监控中 活动会话（Active Session）监控， 是一个重要的指标。通过它，你可以清楚地了解数据库当前是否处于非常繁忙的状态，是否存在 SQL 推积等。

  • 其他

除了活动会话监控以外，我们也可以对 事务，锁等待 等进行监控，这些都可以帮助我们对数据库的运行状态有更全面的认识。

1.3 调优的维度和步骤

我们需要调优的对象是整个数据库管理系统，它不仅包括 SQL 查询，还包括数据库的部署配置，架构等。从这个角度来说，我们思考的维度就不仅仅局限在 SQL 优化上了。通过如下的步骤我们进行梳理：

1.3.1 选择合适的 DBMS（数据库管理系统）

如果对 事务性处理 以及 安全性要求高 的话，可以选择商业的数据库产品。比如采用 SQL server，Oracle，单表存储上亿条数据 是没有问题的。如果表设计的好，即使不用 分库分表 ，查询效率也不错。

同样我们也可以采用开源的 MySQL 进行存储，它有很多存储引擎可以选择，如果 进行事务处理选择 InnoDB，非事务处理选择 MyISAM。

NoSQL 包括 键值型数据库，文档型数据库，搜索引擎，列式存储 和 图形数据库。当然这些数据库的优缺点和使用场景各有不同。

DBMS 的选择关系到了后面的整个设计过程，所以第一步就是要选择合适的 DBMS。

1.3.2 优化表设计

当选择了 DBMS 之后，我们就需要进行表设计了。而数据表的设计方式也直接影响了后续的 SQL 查询语句。RDBMS中，每个对象都可以定义为一张表，表与表之间的关系代表了对象之间的关系。如果用的是 MySQL，我们还可以根据不同的使用需求，选择不同的存储引擎。

一些优化的原则：

  • 表结构要尽量 遵循三范式的原则。这样可以让数据结构更加清晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。

  • 如果 查询 应用比较多，尤其是需要进行 多表联查 的时候，可以采用 反范式化 进行优化。反范式化采用 空间换时间 的方式，通过增加冗余字段提高查询的效率。

  • 表字段的数据类型 选择，关系到了查询效率的高低以及存储空间的大小。一般来说可以采用数值类型就不要采用字符类型，如果字段；字符长度要尽可能设计得短一些。针对字符类型来说，当确定字符长度固定时，可以采用 char 类型；当长度不固定时，采用 varchar 类型。

数据表的结构设计很基础，也很关键。好的表结构可以在业务发展和用户量增加的情况下依然发挥作用，不好的表结构设计会让数据表变得非常臃肿，查询效率也会降低。

1.3.3 优化逻辑查询

SQL 查询优化，可以分为 逻辑查询优化 和 物理查询优化。逻辑查询优化就是 通过改变 SQL 语句的内容让 SQL 执行效率更高效，对 SQL 语句进行等价变换，查询重写。

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

比如：在 existe 子查询 和 in 子查询的时候，要根据 小表驱动大表 的原则选择合适的子查询。以及在 where 字句中尽量避免对字段使用函数，导致索引失效。

举例：

SELECT * FROM data_minute_history_xld WHERE LEFT(OBJ_CODE,3) = 'xld';

等价转换为：

SELECT
	OBJ_CODE,
	RULE_ID,
	TARGET_NAME,
	TARGET_VALUE,
	COLLECT_TIME,
	TARGET_TYPE
FROM
	data_minute_history_xld
WHERE
	OBJ_CODE LIKE 'xld%';

1.3.4 优化物理查询

物理查询优化是在确定了逻辑查询优化之后，采用的物理优化技术（比如索引），通过计算代价模型对各种可能的访问路径进行估算，从而找到代价最小的模型作为执行计划。主要是对索引的使用。

具体优化规则请看见面几章。

1.3.5 使用 redis 或 Menmcached 缓存

  • redis 支持持久化，可靠性高，Memcached 仅仅是内存存储。
  • 通常我们对于查询响应要求高的场景（响应时间短，吞度量大），可以考虑内存数据库。

1.3.6 库级优化

库级优化是站在数据库的维度上进行的优化策略，比如控制一个库中的数据表数量。另外，单一的数据库总会遇到各种限制，不如取长补短，利用 "外援" 的方式。通过 主从架构 优化我们的读写策略，通过对数据库进行垂直或者水平切分，突破单一数据库或数据表的访问限制，提升查询的性能。

  1. 读写分离

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


  1. 数据分片 - 数据库分库分表

对 数据库分库分表。当数据量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。如果使用的是 MySQL，就 可以使用 MySQL 自带的分区表功能，当然可以考虑自己做 垂直拆分（分库），水平拆分（分表），垂直+水平拆分（分库分表）

  • 垂直拆分：按照一定的规则将表拆分不同库中。（单个数据库中的表，拆分到不同的库中。业务分库分表）

  • 水平拆分：将同一张表中的数据按照一定的规则拆分到不同库中。（同一个表，分到不同库中）

  • 分库分表：先分表，然后将分好的表，拆分到不同库中。

值得注意的是，在 使用分库分表提升数据库性能的同时，也会增加维护和使用成本。

2. 优化 MySQL 服务器

优化 MySQL 服务器主要从两个方面来优化，一方面是对 硬件 进行优化，另一方面是对 MySQL 服务的参数 进行优化。

这部分的内容需要较全面的知识，一般只有 专业的数据库管理员 才能进行这一类的优化。对于可以定制参数的操作系统，也可以针对 MySQL 进行操作系统优化。

2.1 优化服务器硬件 - 富人家庭（都是小事）

服务器的硬件性能直接决定着 MySQL 数据库的性能。硬件的性能瓶颈直接决定 MySQL 数据库的运行速度和效率。

针对性能瓶颈，可以通过提高硬件配置，来提高 MySQL 数据库查询，更新的速度。

  • 配置较大的内存。这样可以 增加缓冲区容量 使数据在内存中停留的时间更长，以 减少磁盘I/O。
  • 配置高速磁盘系统（SSD 固定），提高磁盘的 I/O 能力，以减少读盘（读取磁盘时）的等待时间，提高响应速度。
  • 合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
  • 配置多处理器，MySQL 是多线程的数据库，多处理器可同时执行多个线程。

2.2 优化 MySQL 的服务器参数 - 工薪家庭（精打细算）

通过 优化 MySQL 的参数可以提高资源利用率（在有限的资源下，提升性能），从而达到 提高 MySQL 服务器性能的目的。

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

  • innodb_buffer_pool_size：表示 InnoDB 类型的 表和索引的最大缓存。它不仅仅缓存 索引数据，还会缓存 表的数据。这个值越大，查询的速度就会越快。相对的这个值太大会影响操作系统的性能（设置为系统内存的50%。最好设置为：innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances）。
  • innodb_buffer_pool_instances：表示 将 InnoDB 的缓冲区拆分成多个部分。来提高数据库系统的 并行处理能力，因为这样就可以允许多个进程同时处理不同部分的缓存区。
  • key_buffer_size：表示 索引缓冲区的大小。索引缓冲区是所有的 线程共享。增加索引缓冲区可以更好的处理索引（所有读和多重写）。当然，这个值不是越大越好，它的大小取决于系统内存的大小（设置为系统内存的6.25% ~ 9.37%）
  • table_open_cache：表示 所有线程打开的表的数量（同时可以打开表的个数）。这个值越大，能够同时打开的表就越多。物理内存越大，设置就越大。
  • query_cache_size：表示 查询缓冲区的大小。该参数配合 query_cache_type 参数使用 （MySQL 8.0 已删除）。
  • query_cache_type 的值为0时，所有的查询都不使用查询缓冲区。但是，在不使用查询缓冲区时，MySQL并不会释放 query_cache_size 配置的内存。
    • 等于 1 时，所有的查询都将使用查询缓冲区，除非在查询语句中指定 SQL_NO_CACHE。如：SELECT SQL_NO_CACHE * FROM DUAL;
    • 等于 2 时，只有在查询语句中使用 SQL_CACHE 关键字，查询时才会使用查询缓冲区（适用于修改操作少且经常执行相同的查询的情况）。
  • sort_buffer_size：表示每个 需要进行排序的线程分配的缓冲区的大小（为每个线程单独分配的内存）。增加这个参数的值可以提高 order by 或 group by 操作的速度（设置为系统内存的 0.15%）。如果设置6M，现在有 300个连接（会话连接），那么实际使用排序缓冲区大小为 300 * 6 = 1800M。
  • join_buffer_size：表示 联合查询操作所能使用的缓冲区大小，和 sort_buffer_size 一样，该 参数分配的内存空间也是每个连接独享。（设置为系统内存的 0.2%）
  • read_buffer_size：表示 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（为每个线程中每张表分配的内存）。当线程从表中连续读取记录时需要用到这个缓冲区。（设置为系统内存的 0.05%）
  • innodb_flush_log_at_trx_commit：表示 将缓冲区的数据写入日志文件的策略，并且将日志文件写入磁盘中。（默认值：1）
    • 等于 0 时，表示 每秒1次 的频率将数据写入日志文件并将日志文件写入磁盘，不受事务状态的影响。速度快，但不太安全、
    • 等于 1 时，表示 每次提交事务时 将数据写入日志文件并将日志文件写入磁盘进行同步。速度慢，但最安全。
    • 等于 2 时，表示 每次提交事务时 将数据写入日志文件，每隔1秒 将日志文件写入磁盘。速度快，也比较安全。
  • innodb_log_buffer_size：InnoDB 存储引擎的 事务日志所使用的缓冲区。为了提高性能，也是先将数据写入 日志缓冲区 中，当满足 innodb_flush_log_at_trx_commit 参数所设置的规则时（或者日志缓冲区写满）之后，会将数据写入到日志文件（或者同步磁盘）中。
  • max_connections：表示 数据库的最多连接数。如果 状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时就可以 考虑增大 max_connections 的值。
  • back_log：用于 控制 MySQL 监听TCP端口时设置的积压请求的栈大小（个数）。如果 MySQL 的连接数达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一个连接释放资源，该堆栈的数量即 back_log，如果等待连接的数据量超过 back_log,，将会报错。（对于 linux 系统推荐设置为小于 512 的整数，但最大不超过900）
  • thread_cache_size：表示 线程池缓存线程的数量，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程。
SHOW GLOBAL STATUS LIKE 'thread%';
variables_name value
Threads_cached 10
Threads_connected 69
Threads_created 7645
Threads_running 2

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

  • wait_timeout：一个请求的最大连接时间（单位：秒）。
  • interactive_timeout：表示 服务器在关闭连接前等待行动的秒数（单位：秒）。

这里给出一份 my.cnf 的部分参考配置：

# 业务场景：数据库服务器内存16G，8核，dell的pc服务器。

[mysqld]
......
#大了,8M即可 
sort_buffer_size = 32M
#大了,8M即可 
read_buffer_size = 32M 
#大了,8M即可 
read_rnd_buffer_size = 16M 
#小了,建议改成2048
table_open_cache = 512
#小了,建议改成16M
max_allowed_packet = 5M
#小了,建议改成2G
tmp_table_size=64M
#小了,改成db服务器总内存的50%
innodb_buffer_pool_size = 3000M
#小了,改成128M
innodb_additional_mem_pool_size = 20M 
#必须补上，这个是跟join table 关联的，很重要。join_buffer_size 这个参数为什么没有看到,必须设置值，
join_buffer_size = 8M

当然很多情况需要具体情况具体分析。

3. 优化数据库结构

一个好的 数据库设计方案 对于数据库的性能常常会起到 事半功倍 的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计需要考虑 数据冗余，查询和更新的速度，字段的数据类型，表与表之间关系，索引的使用等... 是否合理等多方面的内容。

3.1 拆分表：冷热数据分离

拆分表的思路是：把 1 个包含很多字段的表拆分成 2 个或者多个相对较小的表。这样做主要是为了将 表中经常操作的字段（热数据）和 不经常操作的字段（冷数据）分离，做冷热数据分离，从而减少该表的宽度。如果冷热数据都放在一个表里面，每次查询时都要读取所有的数据，会消耗较多的资源。

  • InnoDB 限制每个表最多存储 1017 列，并且每一行数据的大小不能超过 65535 字节。最多 64 个二级索引，单个索引最多包含 16 列，索引最大长度 767 字节（其实行格式为REDUNDANT,COMPACT最高为767字节,但行格式为DYNAMIC,COMPRESSED最高可达为3072字节）。
  • MyISAM 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节。最多 64 个二级索引，单个索引最多包含 16 列，索引最大长度 1000 字节。

在查询时，表越宽，就越占用缓冲池的内存空间，也会消耗更多的IO。而 冷热数据分离的目的 就是：

  1. 减少磁盘IO，保证热数据的缓存使用率。
  2. 更有效的利用缓存，避免读入无用的数据（冷数据）。

3.2 增加中间表 - 很鸡肋

对于需要经常联合查询的表，可以建立中间表以提高查询效率。提高建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率（多表查询转换成单表查询）。

如果 表 中的信息修改了，会导致 中间表 中的 数据不一致 的问题！

方式1：清空数据 -> 重新添加数据

方式2：使用视图

个人认为：联合查询慢，主要还是因为表中的数据量大导致的。然后再去建一张中间表去存储联合查询的数据。同时还要为了保证 "中间表" 的数据一致性，做删除，新增等操作，这么多的操作这么多的数据，是及其消耗资源的。

注意：该方式只适用于不频繁更新的表。

3.3 增加冗余字段

设计数据库时要尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致，优雅。但是，合理地加入冗余字段可以提高查询速度

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

建议：一般遵循 第三范式（3NF） 设计表就可以了。然后具体情况，增加冗余字段。

3.4 优化数据类型

改进表的设计时，可以考虑优化字段的数据类型。在数据量越多越大的时，就不能仅仅光考虑系统稳定性了，还要考虑到系统整体的性能。那么此时，优先选择符合存储需要的最小的数据类型。

列的 字段越大，建立索引时所需要的 空间也就越大，这样一个 数据页中 所能存储的 索引数量也就越少，在遍历时所需要的 IO次数也就越多，索引的性能也就越差。

具体来说就是：

  • 整数类型 的字段可以用 INT 类型。
  • 对于 非负数类型 的字段，要优先使用无符号整型 UNSIGNED 来存储。
  • 文本类型 和 整数类型都可以时，必须选择使用整数类型。
  • 避免使用 TEXT，BLOB 数据类型。
  • 避免使用 ENUM 类型。
  • 尽量使用 timestamp 存储时间。
  • 浮点数必须使用 declmal。

总之一句话：合理的使用数据类型，这样才能充分利用资源，使系统达到最优。

3.5 优化插入记录的速度

  • MyISAM 存储引擎 - 插入记录时优化
    • 禁用索引
    • 禁用唯一性检查
    • 批量插入时使用一条语句： insert into 表名 (字段,....) values (xx),(xx)...
  • InnoDB 存储引擎 - 插入记录时优化
    • 禁用唯一性检查
    • 禁用外键检查
    • 禁止自动提交
  • 禁用索引命令
alter table 表名 disable keys;
  • 开启索引命令
alter table 表名 enable keys;
  • 禁用唯一性检查
set unique_checks = 0;
  • 开启唯一性检查
set unique_checks = 1;
  • 禁用外键检查
set foreign_key_checks = 0;
  • 开启外键检查
set foreign_key_checks = 1;
  • 禁止自动提交
SET autocommit = 0;
  • 开启自动提交
SET autocommit = 1;

3.6 使用非空约束

在设计字段的时候，如果业务允许，建议尽量使用非空约束。这样做的好处是：

  • 进行比较和计算时，省去了判空的操作。
  • 非空字段创建索引时，可以节省存储空间，避免索引失效。

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

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

3.7.1 分析表 - analyze

分析表 - 基本语法：

analyze [local | no_write_to_binlog] table 表名 [表名,表名,...]

默认会将 analyze table 语句写到 binlog 日志中，以便在主从架构中进行数据同步。我们也可以在 analyze table 语句中添加 local 或者 no_write_to_binlog 取消将该语句写入到 binlog 日志中。

注意：在使用 analyze table 分析表的过程中，被分析的表会自动加上 只读锁。并且该语句只能分析 InnoDB 和 MyISAM 类型的表，不能对视图使用。

分析表：就是更新了表中各个索引的区分度。同时也会更新 "系统库" 中该 "表" 的信息。

analyze table 分析后的统计结果会反应到 查询索引结果中的 cardinality 的值，该值统计了表中某一键所在的列不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。也就是索引结果中的 cardinality 的值于表中数据的总条数 差距越大，即使查询的时候使用了该索引作为查询条件，存储引擎实际 查询的时候使用的概率就越低。

3.7.2 检查表 - check

check table 语句能够检查 InnoDB 和 MyISAM 类型的 表是否存在错误，也可以检查 "视图"。 check table 语句在执行时也会给表自动加上 只读锁。

检查表 - 基本语法：

check table 表名 [表名,表名,...] [option] ... 
option = {quick | fast | medium | extended | changed}

option 参数有 5 个取值，分别是 quick，fast，medium，extended 和 changed。各个选项的意义分别是：

  • quick：不扫描行，不检查错误的连接。
  • fast：只检查没有被正确关闭的表。
  • changed：只检查上次检查后被更改的表和没有正确关闭的表。
  • medium：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。
  • extended：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是 100% 一致的。

option 只对 MyISAM 类型的表有效，对 InnoDB 类型的表无效。

执行以下语句：

CHECK TABLE data_minute_history_xld;
Table Op Msg_type Msg_text
zhsw_dev.data_minute_history_xld check status OK

该语句对于检查的表可能会产生多行信息。最后一行有一个状态的 Msg_type 值，Msg_text 通常为 OK（一切正常）。如果得到的不是 OK，通常要对其进行修复；

3.7.3 优化表 - optimize

  • 方式1：optimize table

optimize table 语句只能优化表中的 varchar，blob，text 类型的字段。一个表使用了这些字段的数据类型，若已经 删除 了表的一大部分数据，或者已经对含有可变长度行的表（含有varchar，blob，text类型的表）进行了很多 更新，则应使用 optimize table 来重新利用未使用的空间，并整理数据文件的 碎片。

optimize table 语句（优化表）：就是整理操作（删除，更新）后的数据文件的碎片空间。（只对 varchar，blob，text 类型的字段有效）

底层使用的是：复制算法 和 jvm中的gc类似。

  1. 先创建一张临时表，将表中的所有数据复制到临时表中。（注意：复制的时候是没有存储空间碎片的）
  2. 然后删除原来的表，将临时表替换为原来的表。

optimize table 语句对 InnoDB 和 MyISAM 类型的表都有效。该语句在执行时也会给表自动加上 只读锁。

优化表 - 基本语法：

optimize [local | no_write_to_binlog ] table 表名 [表名,表名,...]

默认会将 optimize table 语句写到 binlog 日志中，以便在主从架构中进行数据同步。我们也可以在 optimize table 语句中添加 local 或者 no_write_to_binlog 取消将该语句写入到 binlog 日志中。

说明：

在 MyISAM 中，是先分析这张表，然后会整理相关的 datafile（文件系统中数据文件），之后回收未使用的空间；

在 InnoDB 中，回收空间是简单通过 alter table 进行整理空间。在优化期间，MySQL 会创建一个 "临时表"，优化完成之后会删除 "原始表"，然后会将 "临时表" rename 修改为 "原始表"。

在多数情况下，根本不需要运行 optimize table 。每周一次 或 每月一次即可，并且只需要对 特定的表 使用。

  • 方式2：使用 mysqlcheck 命令

说明：这个 MySQL 自带的一个客户端命令（插件命令）

语法：

mysqlcheck -o 数据库名称 表名 -u 用户名 -p 密码 -P 端口号

例如：

mysqlcheck -o xld xld_user -u root -p xldmysql -P 3306

mysqlcheck 是 linux 中的 rompt，-o 表示 Optimize

3.8 小结

上述 这些方法都是有利有弊 的。比如：

  • 修改数据类型，节省存储空间的同时，还要考虑到数据不能超过取值范围。
  • 增加冗余字段的时候，不要忘了确保数据一致性。
  • 把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维成本。

因此，一定要结合实际的业务需求进行权衡。

4. 大表优化

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

4.1 限定查询的范围

禁止不带任何限制数据范围条件的查询语句。比如：当我们在查询历史数据的时候，只查询一个月范围内的数据。

4.2 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

  • 一主一从模式：

  • 双主双从模式：

4.3 垂直拆分 - (垂直分库，垂直分表)

当数据量级达到 千万级 以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

  • 垂直分库：如果 数据库中的数据表过多时，可以采用 垂直分库 的方式，将库中的表拆分到不同的数据库中（单个数据库中的表，拆分到不同的库中。业务分库分表）。
  • 垂直分表：如果 数据表中的列过多时，可以采用 垂直分表 的方式，将一张表拆分成多张表，把经常使用的列放在同一张表里。（将一张表，拆分成多张表。冷热数据分离）

垂直拆分的优点：可以使得 列数据变小，在查询时 减少读取的 Block（块） 数，减少 I/O 次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会。此外引起 JOIN 操作，"垂直拆分" 会让事务变得更加复杂。

4.4 水平拆分

  • 尽量控制单表数据量的大小，建议控制在 1000万以内。1000万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。此时可以用 历史数据归档（应用于日志数据），水平分表（应用于业务数据）等手段来控制数据量大小。
  • 我们主要考虑的是业务数据的 水平分表 策略。将大的数据表按照 某个属性维度 拆分成不同的小表，每张小表都保持相同的表结构。例如：时间，code，业务类型 等字段拆分。
  • 水平分表 仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升 MySQL 并发能力没有任何意义。最好 水平拆分 - 分库，从而达到分布式的目的。

水平拆分：能够支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决，跨节点 join 性能较差，逻辑复杂。

《java工程师修炼之道》的作者推荐：

尽量不要对数据进行分片，因为拆分会带来逻辑，部署，运维的各种复杂度，一般的数据库表在优化得当的情况下千万以下的数据量没有问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

补充一下数据库分片的两种常见方案：

  • 客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装 JDBC 层来实现。当当网 Sharding-JDBC，阿里的 TDDL 。
  • 中间件代理：在应用和数据库中间加了一个代理层。分片逻辑统一维护在中间件服务中。 Mycat，360的Atlas，网易的 DDB 等等。

5. 其它调优策略 - 8.0 新特性

5.1 服务器语句超时处理

在 MySQL 8.0 中可以设置 服务器语句超时的限制，单位可以达到 毫秒级别。当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后报错。

由系统变量 max_execution_time （全局/会话）控制服务器语句超限限制（默认：0。没有时间限制）

  • 查看：
SHOW VARIABLES LIKE '%max_execution_time%'
  • 设置：
# 全局 select 语句的超时时间
SET GLOBAL max_execution_time = 2000; 
# 指定该会话中 select 语句的超时时间
SET SESSION max_execution_time = 1000;

5.2 创建全局通用表空间

MySQL 8.0 使用 create tablespace 语句来创建一个 全局通用表空间。全局表空间可以被数据库中所有的表共享，而且相比于单独表空间，使用手动创建的共享表空间可以节约元数据方面的内存。

我们可以在创建表的时候，指定表空间。也可以对已有的表进行表空间的修改等。

  • 创建全局通用表空间
create tablespace 表空间名称 add datafile '表空间文件名称.ibd' file_block_size = 表空间的大小;

例如：

CREATE TABLESPACE xld_space add datafile 'xld_space.ibd' file_block_size = 16K;

注意：在指定全局通用表空间的大小时，单位只能使用 "K"。

  • 在创建表时指定表空间
create table 表名(id int,name varchar(15)) engine = innodb default charset utf8mb4 tablespace 表空间名称;

例如：

create table xld_space(id int,name varchar(15)) engine = innodb default charset utf8mb4 tablespace xld_space;
  • 修改已有表的表空间
alter table 表名 tablespace 表空间名称;

例如：

alter table xld TABLESPACE xld_space;
  • 删除全局通用表空间：drop tablespace
drop tablespace 表空间名称;

例如：

drop tablespace xld_space;

注意：如果依赖该共享表空间的表没有被删除（还存在），就会删除失败。所以，要先删除依赖该共享表空间的表，再删除该表空间。

[SQL]DROP TABLESPACE xld_space;
[Err] 3120 - Tablespace `xld_space` is not empty.

5.3 MySQL 8.0 新特性：隐藏索引对调优的帮助

不可见索引的特性对于性能调试非常有用。在 MySQL 8.0 中，索引可以被 "隐藏" 和 "显示"。当一个索引被隐藏时。它不会被查询优化器所使用。

不可见索引的特性对于性能调试说明：

管理员可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，将其 ”恢复使用“ 即可；如果数据库性能看不出变化，就说明这个索引是多余的，可以删掉了。

但我们需要注意的是，当索引被隐藏时，它的内容仍然是和正常索引一样 实时更新 的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响数据插入，更新和删除的性能。

数据表中的主键索引不能被隐藏（invisible）

posted @ 2023-11-18 15:31  小林当  阅读(60)  评论(0)  编辑  收藏  举报