MySQL8.0性能优化

MySQL8.0 引擎：

来看看MySQL8提供的引擎：

常用引擎：

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

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

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

应用场景：

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

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

Memory：临时性的，大数据量表的查询优势。

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

 

分表 / 拆表 / 分库 / 分盘

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

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

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

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

 

索引

索引分类

普通索引：无限制

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

唯一索引：值不能重复

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

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

 

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

 

索引设计原则：

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

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

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

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

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

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

 

索引命中：

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

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

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

避免回表查询：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的资源消耗。为了更多的需要暂存空间，满足更大数据量的暂存。

参数设置：

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

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

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

设置规则：

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

也就是说，innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size 的倍数

举例说明：

符合的例子：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

运行机制：

缓冲区分为 热数据区 / 冷数据区，两者空间占比约为 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