MySQL性能优化(二)存储引擎

MySQL性能优化(二)存储引擎

1. MyISAM

image.png

1.1 MyISAM数据库文件组成

image.png

  • frm:存储表结构,任何存储引擎都具备的(MySQL8.0以后没有.frm了,元数据都存在系统表空间里)
  • MYD:数据文件
  • MYI:索引文件
  • 因为MyISAM引擎的数据和索引分开存放,所以它是非聚集索引

1.2 特性

image.png

压缩测试

myisampack.exe -b -f "C:\ProgramData\MySQL\MySQL Server 8.0\Data\enjoy_mysql\testmyisam.MYI"

原来的大小: image.png

压缩后: image.png

  • 压缩后生成了.OLD文件
  • 如果删除.OLD文件可能导致表无法插入和更新数据
  • 使用CHECK TABLE testmyisam可以查看当前表的问题, 使用REPAIR TABLE testmyisam可以修复表的问题

1.3 适用场景

image.png

  • MyISAM不支持事务
  • 查询效率较高 所以用于只读类应用
  • 空间类作用:GIS类应用 如:地图,经纬度计算的应用

2. InnoDB

2.1 概述

image.png

  • .ibd中存储的是该表的数据+索引 所以该存储的引擎的索引是聚集索引
  • 独立表空间:表的数据和索引会单独存储在xxx.ibd文件中
  • 系统表空间:表的数据和索引会存放在共用的ibdata和mysql.ibd文件中

2.2 独立表空间和系统表空间优缺点

image.png

  • 只是删除表数据 不会改变文件大小(文件内容没有被释放 需要使用压缩文件的指令才可以)
  • 独立表空间可以收缩文件的大小 类似于磁盘的整理
  • 系统表空间只有一个文件 索引会有IO瓶颈

独立表空间进行压缩:

OPTIMIZE TABLES testmyisam

image.png

2.3 特性

image.png

2.4 InnoDB和MyISAM的比较

image.png

3.CSV

image.png

  • MySQL8.0 以后移除了.frm文件 image.png

4.Archive

image.png

image.png

5.Memory

image.png

  • MEMORYMySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表,而且数据全部放在内存中。
  • 每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同,类型为frm类型。该文件中只存储表的结构。而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。值得注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了,可以释放内存,甚至删除不需要的表。
  • MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引,可以在创建索引时指定。

注意,MEMORY用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。如果重启或者关机,所有数据都会消失。因此,基于MEMORY的表的生命周期很短,一般是一次性的。

查看最大大小:

SHOW VARIABLES LIKE 'MAX_HEAP_TABLE_SIZE'

查看表的状态

SHOW TABLE STATUS LIKE 'testmemory'

5.2 临时表的区分

image.png

5.2.1 外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除)。

5.2.2内部临时表(系统使用临时表)

  • 内部临时表是一种特殊轻量级的临时表,用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的,但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色, MySQL中的很多操作都要依赖于内部临时表来进行优化。
  • 但是使用内部临时表需要创建表以及中间数据的存取代价,所以用户在写SQL语句的时候应该尽量的去避免使用临时表
    • 内部临时表有两种类型:一种是HEAP临时表,这种临时表的所有数据都会存在内存中,对于这种表的操作不需要IO操作
    • 另一种是OnDisk临时表,顾名思义,这种临时表会将数据存储在磁盘上OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE(详情请参考MySQL手册中系统变量部分),HEAP临时表将会被自动转换成OnDisk临时表OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。 本篇文章主要介绍哪些操作可能会利用到内部临时表。如果用户在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化,将有效的提高查询执行的效率。

总结

MySQL将利用内部临时表进行中间结果缓存,如果数据量比较大的话,内部临时表将会把数据存储在磁盘上,这样显然会对性能有所影响。为了尽可能的减少性能损失,我们需要尽量避免上述情况的出现。

5.3 使用场景

image.png

6. Federated

Federated存储引擎默认是关闭的,想要开启需要修改配置文件 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini 在配置文件中添加一行:

federated = 1

image.png

  • 远程表和本地表的表结构必须相同
  • 两个表的表名、存储引擎可以不同 image.png

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×