MySQL8.0 性能优化（探索）

系列目录

MySQL8.0 性能优化（探索）

MySQL8.0 性能优化（实践）

一、MySQL8.0 引擎：

来看看MySQL8提供的引擎：

作者：[Sol·wang] - 博客园，原文出处：https://www.cnblogs.com/Sol-wang/p/14792751.html

1.1 常用引擎：

InnoDB：支持事务，行级锁，外键，崩溃修复，多版本并发控制；读写效率相对较差，内存使用相对较高，占用数据空间相对较大。

MyISAM：不支持事务，不支持外键，仅支持非聚集索引，支持全文索引，仅支持到表级锁，支持数据压缩，占用空间相对小，内存使用相对较低，读写性能相对极佳。

Memory：依赖于内存空间，数据处理速度快，仅支持到表级锁。

1.2 应用场景：

InnoDB：依赖于 事务，回滚，并发，大数据量，外键，行级锁 的场景。

MyISAM：过多的大数据量的频繁的查询优势。

Memory：临时性的，更多作为查询中转数据集的使用，大数据量表的查询优势。

在创建表的时候，可依据应用场景选择合适的引擎。

二、分表 / 拆表 / 分库 / 分盘

分表：解决单表数据量过大的性能瓶颈，小范围的数据处理，避免过多数据扫描；可按业务类型数据、时间跨度等实际场景分表。

拆表：按场景合理的将大表分为多个小表来降低锁竞争

分库：分实例分别各自处理，量与性能的分散优化处理，欠缺的事务一致性，可按实际场景合理分库。

分盘：主要解决磁盘IO瓶颈，多磁盘分散并行运行。

三、索引

3.1 索引分类

普通索引：无限制

主键索引：表中只能有一个，不能为NULL

唯一索引：值不能重复

全文索引：仅MyISAM支持，仅支持 char、varchar、text 类型

组合索引：多列一起创建的合并索引，非单列分别创建的索引

所有存储引擎对每个表至少支持了16个索引。

3.2 索引设计原则：

为经常需要排序、分组或联合操作的字段建立索引，经常需要使用 order by、group by、distinct、uninon 等的操作字段

为经常查询出的列建立索引，为经常作为查询条件的字段建立索引

推荐长度较少的列建索引，推荐列使用固定长度。

过多的索引建立对表数据变更操作的性能下降的影响

删除不再使用或很少使用的索引，减少索引对更新时的影响

索引覆盖，索引下推，避免回表查询（以下介绍）

3.3 索引命中：

依据索引查询，查询条件常以索引列开始

组合索引的最左原则：必须以组合索引列的首列开始的条件查询，按序依次。

索引覆盖，推荐要查询出的字段全部为索引列。假如页面列表呈现出个别主要的字段内容的场景；具体的详细内容在详细页呈现，透过主键查询单条数据。

避免回表查询：MySQL首先查出带索引的列数据，再透过主键列去查询非索引列的数据信息，把两次查询的数据组合后返回客户端。所以推荐索引覆盖。

四、脚本优化

尽量避免嵌套子查询，改用JOIN方式。

尽量减少 查询中的全表扫描次数 ，尤其是对于大表，如采用EXISTS、WHERE的条件顺序等。

避免字段以难以理解的方式转换查询，采用更为合理的转换方式。

去除不必要的括号，避免复杂逻辑查询。如 (1=1 and (b>a and b=c)) and a=5 推荐为 b>5 and b=c and a=5

简化减少WHERE条件范围区间的重叠部分。如 (key1 < 'abc' and 1=1) or (key1 < 'bar') or (false) 推荐为 key1 < 'bar'

避免WHERE后用函数临时的计算，可事先生成结果列或虚拟列。

推荐WHERE中首次出现的IS NULL赋予更大的作用，因为MySQL8仅对首次出现的IS NULL做大量优化。

某些场景对索引的失效或破坏，FORCE INDEX：指明优先使用的索引并生效；常用于JOIN。

IN的嵌套查询改为EXISTS的嵌套查询。

INNER JOIN时，STRAIGHT_JOIN指明优先检索的主表，使其特定场景中达到我们的预期效果。

被嵌套的查询更多的筛选和处理，使其减少外表查询的数据基数。

批量INSERT使用包含多个VALUES列表的语句一次插入多行，量越大效果越明显。（加大 bulk_insert_buffer_size、Max_allowed_packet、Net_buffer_length 的值，满足更大量的处理）

推荐默认值列，非显示的插入减少必要的解析。

某些场景下，replace into 的使用，代替 insert/update，成为单一的原子操作。

SQL片段WITH AS的运用，Memory Table 的利用。

查询仅返回需要的字段，避免 *，避免回表查询；仅返回需要的数据量。

五、InnoDB缓冲池

一个称为缓冲池的存储区域，用于在内存中缓存数据和索引，利用它将经常访问的数据保存在内存中，减少了SQL执行及磁盘IO的资源消耗。为了更多的需要暂存空间，满足更大数据量的暂存。

5.1 参数设置

innodb_buffer_pool_size：缓冲池的承载总量，建议设为系统内存的50%-70%

innodb_buffer_pool_chunk_size：缓冲池每块大小，默认128M

innodb_buffer_pool_instances：多线程缓冲池实例并行运行，默认1实例，最大64实例

5.2 设置规则

innodb_buffer_pool_size = （innodb_buffer_pool_chunk_size * {N}块 ）* innodb_buffer_pool_instances

也就是说，innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size 的倍数；如果设置的不是倍数，MySQL会自动改成最近的倍数值。相应的，innodb_buffer_pool_instances 也会自动得出数值。

举例说明：

符合的例子：innodb_buffer_pool_size=8G，innodb_buffer_pool_chunk_size=128M，innodb_buffer_pool_instances=16

不符的例子：innodb_buffer_pool_size=9G，innodb_buffer_pool_chunk_size=128M，innodb_buffer_pool_instances=16

对于以上非倍数的状况：

MySQL会自动将 innodb_buffer_pool_size 调整为 innodb_buffer_pool_chunk_size 的倍数；所以会变为 innodb_buffer_pool_size = 10G

5.3 运行机制

缓冲区分为 热数据区 / 冷数据区，两者空间占比约为 7/3，每区中的数据集依使用频率按顺序依次排列。

当一个新的查询结果出现后，首先考虑存放到冷数据区，当冷数据区的结果集使用达到一定频率，会被改存到热数据区，使用频率最好的数据集会被存放到热区的首位，当然也有热区转到冷区的状况。

MySQL8 去除了 [查询缓存] query_cache_type、query_cache_size、query_cache_limit

六、连接池

MySQL连接器中的连接池，用以提高数据库密集型应用程序的性能和可扩展性。默认启用。MySQL连接器负责管理连接池中的多个连接，自动创建、打开、关闭和破坏连接，多个连接的创建，可满足多客户端的频繁连接，连接的重复使用获得最佳性能。
MySQL连接器 每三分钟运行一次后台作业，并从池中删除闲置（未使用）超过三分钟的连接。池清理释放客户端和服务器端的资源。这是因为在客户端每个连接都使用一个Socket，而在服务器端每个连接都使用一个Socket和一个线程。

透过连接字符串参数对连接池主要参数的设定调整其性能特点：

• 开启连接池：Pooling = true，默认开启
• 复用时重置连接：ConnectionReset = True
• 保持连接设置：CacheServerProperties = True
• 连接超时回收(秒)：ConnectionLifeTime = 300
• 支持的最大连接数量：Max Pool Size = 100
• 保持最小的连接数量：Min Pool Size = 10

七、日志

MySQL在运行时，会有各种不同日志的记录，大量的各种类型的日志产生，会对资源的开销产生严重的影响，必要的时候我们选择性的开启。但在生产环境时，有些日志并不是必须，以下列出MySQL各种日志信息：

• 错误日志：启动、关闭、运行时 产生的异常记录，建议开启，设置 log_error
• 查询日志：客户端连接和执行的脚本，建议关闭，设置 general_log
• 慢查询日志：记录超时的查询，记录不适用索引的查询等，建议关闭，设置 slow_query_log
• 二进制日志：用于数据同步复制，需发送的数据日志，多用于集群，如需开启，设置 log_bin
• 中继日志：用于数据同步复制时，接收到的数据日志，多用于集群，如需开启，设置 relay_log

姊妹篇：

MySQL8.0 性能优化（实践）https://www.cnblogs.com/Sol-wang/p/17076128.html