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 库级优化
库级优化是站在数据库的维度上进行的优化策略,比如控制一个库中的数据表数量。另外,单一的数据库总会遇到各种限制,不如取长补短,利用 "外援" 的方式。通过 主从架构 优化我们的读写策略,通过对数据库进行垂直或者水平切分,突破单一数据库或数据表的访问限制,提升查询的性能。
- 读写分离
如果读和写的业务量都很大,并且它们都在同一个数据库服务器中进行操作,那么数据库的性能就会出现瓶颈,这是为了提升系统的性能,优化用户体验,我们可以采用 读写分离 的方式降低主数据库的负载,比如用 主数据库(master)完成写操作,用 从数据库(slave)完成读操作。
- 数据分片 - 数据库分库分表
对 数据库分库分表。当数据量级达到千万级以上时,有时候我们需要把一个数据库切成多份,放到不同的数据库服务器上,减少对单一数据库服务器的访问压力。如果使用的是 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。而 冷热数据分离的目的 就是:
- 减少磁盘IO,保证热数据的缓存使用率。
- 更有效的利用缓存,避免读入无用的数据(冷数据)。
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类似。
- 先创建一张临时表,将表中的所有数据复制到临时表中。(注意:复制的时候是没有存储空间碎片的)
- 然后删除原来的表,将临时表替换为原来的表。
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)