报名热线:

0898-08980898

个/性/化/教/育/行/业/领/跑/者

万事注册

CURRICULUM

万事代理

0898-08980898

广东省清远市

123456789

万事新闻 >更多
万事新闻

当前位置: 首页 > 万事新闻

MySQL8.0 性能优化(探索)

2024/04/22

系列目录

来看看MySQL8提供的引擎:
MySQL8.0 引擎

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

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

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

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

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

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

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

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

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

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

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

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

普通索引:无限制

主键索引:表中只能有一个,不能为NULL

唯一索引:值不能重复

全文索引:仅MyISAM支持,仅支持 char、varchar、text 类型

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


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

为经常需要排序、分组或联合操作的字段建立索引,经常需要使用 、、、 等的操作字段

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

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

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

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

索引覆盖,索引下推,避免回表查询(以下介绍)

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

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

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

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

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

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

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

去除不必要的括号,避免复杂逻辑查询。如 推荐为

简化减少条件范围区间的重叠部分。如 推荐为

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

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

某些场景对索引的失效或破坏,:指明优先使用的索引并生效;常用于JOIN。

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

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

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

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

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

某些场景下, 的使用,代替 ,成为单一的原子操作。

SQL片段的运用,Memory Table 的利用。

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

一个称为缓冲池的存储区域,用于在内存中缓存数据和索引,利用它将经常访问的数据保存在内存中,减少了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 的倍数;如果设置的不是倍数,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

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

关于万事娱乐 /ABOUT US
万事娱乐专注于娱乐领域,是拥有自主安全稳定的平台,在网络互娱领域拥有多项核心专利,涵盖万事注册、登录、开户、代理、招商及线路手机app客户端下载等,万事平台拥有完善的体系,致力用户提供价值高品质的服务,我们期待与您合作共赢!...

友情链接:

微信平台

手机官网

网站首页| 关于万事娱乐| 万事注册| 万事新闻| 万事登录| 万事平台| 万事开户| 万事代理| 在线报名|

平台注册入口