Skip to content

MySQL QA

MySQL基础架构

从上图可以看出,MySQL主要分为Server层和存储引擎层

Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎

  • 连接器: 管理链接,权限验证
  • 查询缓存: 命中则直接返回结果
  • 分析器: 词法分析,语法分析
  • 优化器: 执行计划生成,索引选择
  • 执行器: 操作引擎,返回结果
  • 存储引擎: 存储数据,提供读写接口

MySQL表多大才算大?

MySQL本身并没有对单表最大记录数进行限制。

阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。

事实上,MySql数据库一张表中能存储的最大数据量和实际记录的条数无关,而与 MySQL 的配置以及机器的硬件有关。因为,MySQL 为了提高性能,会将表的索引装载到内存中。InnoDB buffer size 足够的情况下,其能完成全加载进内存,查询不会有问题。

但是,当单表数据库到达某个量级的上限时,导致内存无法存储其索引,使得之后的 SQL 查询会产生磁盘 IO,从而导致性能下降。当然,这个还有具体的表结构的设计有关,最终导致的问题都是内存限制。

MySQL表里面的数据删除后,磁盘空间会立马释放吗?

在MySQL中,删除数据的工作原理是将数据标记为已删除,而不是立即从磁盘上删除它们。这是因为磁盘上的数据需要被定期清理,以便数据库性能更好。删除大量数据会导致磁盘空间被占用,因此MySQL采用了一种称为“延迟清理”(delayed clean)的机制。

MySQL延迟清理机制的优点

MySQL使用延迟清理机制的主要优点是提高数据库性能。在删除大量数据时,如果MySQL立即从磁盘上删除数据,则会产生大量的I/O操作,这会影响数据库性能。因此,MySQL选择延迟清理机制来提高性能。

此外,MySQL使用延迟清理机制还可以减少磁盘碎片。磁盘碎片是指磁盘上分散的、不连续的数据块。当MySQL立即删除数据时,磁盘上会产生大量的碎片。然而,使用延迟清理机制可以减少磁盘碎片,并提高磁盘的读写效率。

MySQL延迟清理机制的缺点

磁盘不足

当数据库中的数据不断增加时,磁盘空间也会相应减少。如果您使用DELETE语句删除数据,磁盘上的空间不会被立即释放,而是会被标记为可用空间。这意味着如果您不定期清理已删除的数据,数据库的磁盘空间将会不断减少,最终可能导致磁盘空间不足的问题。

如何让MySQL表中的数据立即减少呢?
  1. 在MySQL中,解决磁盘空间不足问题的方法是定期清理已删除的数据。您可以使用OPTIMIZE TABLE命令来清理已删除的数据。OPTIMIZE TABLE命令会重新组织表,并删除已删除的数据。这样可以释放磁盘空间,并提高数据库性能。

    delete + optimize table 的方式。

    OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用

  2. 直接 TRUNCATE TABLE

    TRUNCATE TABLE命令可以快速删除表中的所有数据,并释放磁盘空间

如何配置MySQL主从?

master机器上配置:

Terminal window
server-id = 1 #[必须]服务器唯一ID,默认是1
log-bin = mysql-bin #[必须]启用二进制日志
#需要开启生成二进制日志记录相关配置,配置在需要被复制的服务器上,即:master
binlog-do-db = my_test1 #指定对名称为test_db的数据库记录二进制日志
# binlog-ignore-db = mysql #指定不对名称为mysql的数据库记录二进制日
binlog_format = mixed #binlog日志格式,mysql默认采用,如果从服务器slave有别的slave要复制那么该slave也需要这一项
expire_logs_days = 7 #超过7天的binlog删除

slave机器上配置:

Terminal window
server-id = 2
# replicate-do-db 需要做复制的数据库,如果复制多个数据库,重复设置这选项即可master上不需要此项,slave上需要
replicate-do-db = my_test1 #复制名称为test_db的数据库
# replicate-ignore-db 不需要复制的数据库,如果要忽略复制多个数据库,重复设置这个选项即可
# replicate-ignore-db = mysql #不需要(忽略)复制名称为mysql的数据库
如何配置MySQL集群? —— MHA

MHA(Master High Availability)方案,该方案有两部分组成:

MHA Manager(管理节点)和 MHA Node(数据节点)

MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上,MHA Manager会定时探测集群中的master节点,当master出现故障时,它可以自动将最新数据的slave提升为新的master,然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

目前MHA主要支持一主多从的架构,要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库,因为至少需要三台服务器.

具体配置可以参考 MySQL集群高可用架构之MHA

MySQL为什么使用树结构?

  1. 文件很大,不可能全部存储在内存中,故要存储到磁盘上
  2. 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数(为什么使用B-/+Tree,还跟磁盘存取原理有关)
  3. 局部性原理与磁盘预读,预读的长度一般为页(page)的整倍数(根据操作系统的不同,操作系统有的页大小为4k,有的为16k)。其中MySQL B+树中的 叶/非叶节点 都是以MySQL的页为单位(大小通常也为16k),存放完整行记录。
  4. 数据库系统巧妙利用了磁盘预读原理,将一个节点大小设为操作系统内存页的整数倍(4倍),这样每个节点只需要一次I/O就可以完全载入。而红黑树这种节构,高度明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性。

MySQL一般怎么优化

SQL优化方式有:

  • 索引优化
  • 查询优化
  • 数据表设计优化
一、索引优化

索引是提高数据库查询性能的关键因素之一。在SQL查询中,通过建立合理的索引,可以加快数据的检索速度。索引可以理解为数据库表格中的目录,它类似于书中的索引,帮助数据库系统快速定位所需数据。以下是索引优化的一些关键方式:

  • 主键索引:为每个表格设置主键索引,主键索引可以保证表格中的数据少数性,并且自带聚集索引的特性,能够有效加快查询速度。
  • 少数索引:对于需要保证少数性的字段,可以创建少数索引,以防止重复数据的插入,提高数据插入的效率。
  • 组合索引:对于经常同时查询多个字段的情况,可以创建组合索引,将多个字段一起构成索引,以提高联合查询的效率。
  • 稀疏索引:对于稀疏数据列,可以使用稀疏索引,减少索引的存储空间。
  • 避免过多索引:尽量避免在一个表格中创建过多的索引,过多的索引会增加数据库的维护成本,降低查询性能。
二、查询优化

查询优化是SQL优化的核心内容,合理优化查询语句可以大幅提高数据库的查询效率。以下是查询优化的一些关键方式:

  • 避免使用通配符:在查询语句中避免使用通配符(如“%”),通配符查询会导致全表扫描,降低查询性能。
  • 使用JOIN语句:合理使用JOIN语句可以将多个表格连接在一起,减少查询次数,提高查询效率。
  • 避免使用子查询:子查询会导致嵌套查询,降低查询性能,尽量避免使用不必要的子查询。
  • 使用EXISTS或IN语句:在查询需要判断某个条件是否存在时,优先使用EXISTS或IN语句,它们的性能通常比COUNT()函数更高效。
  • 优化WHERE条件:在WHERE条件中使用索引字段可以提高查询效率,避免使用函数操作符或类型转换,这会导致索引失效。
三、数据表设计优化

数据表的设计直接影响到SQL查询的性能,合理的数据表设计可以提高数据的存储效率和查询效率。以下是数据表设计优化的一些关键方式:

  • 规范化设计:遵循数据库规范化原则,将数据分解成多个相关联的表格,以避免数据冗余和更新异常。
  • 避免使用过多的字段:在设计数据表时,避免使用过多的字段,将字段设计简洁明了,减少数据表的存储空间和查询成本。
  • 使用合适的数据类型:选择合适的数据类型可以减少数据表的存储空间,提高数据插入和查询的效率。
  • 分区表设计:对于大型数据表,可以采用分区表设计,将数据按照一定规则划分成多个分区,提高查询效率。
  • 冗余表设计:对于频繁查询的数据,可以创建冗余表,避免频繁连接查询,提高查询速度。
除了索引优化、查询优化和数据表设计优化,还有其他一些优化技巧可以进一步提升SQL查询的性能。以下是一些其他优化技巧:
  • 预编译查询:使用预编译查询可以将SQL语句编译为二进制代码,并缓存起来,减少重复编译的开销,提高查询性能。
  • 使用连接池:连接池可以预先创建和管理数据库连接,避免频繁创建和销毁连接,提高连接的复用率和查询效率。
  • 分批次查询:对于大数据量的查询,可以采用分批次查询的方式,分批次获取数据,减少内存开销和数据库负载。
  • 定期维护:定期进行数据库的维护工作,如索引重建、表格优化、数据清理等,以保持数据库的良好性能。
  • 使用缓存:对于频繁查询的数据,可以使用缓存技术,将数据缓存到内存中,提高数据访问速度。

在进行SQL优化时,需要根据具体场景和业务需求,综合考虑各种优化方式,确保数据库系统能够高效、稳定地处理大量数据和查询请求。优化SQL查询是一个持续优化的过程,只有不断优化和改进,才能保障数据库系统的稳定性、高效性和可靠性。

MySQL 8 新增了哪些特性

8.0版本是5.7版本后的跨越大版本 以下新特性:

  • 大表秒级加列,只改数据字典表的元数据信息。5.7的版本加列需要重建表(消耗大量的IO资源和时间),8.0加列没有这个步骤。秒级加列(不要指定列位置,如after str1)
  • 文档数据库
    • NoSQL + SQL = MySQL
    • 多文档事务,ACID特性
    • 支持更新JSON中部分filed
  • SQL增强。共用表表达式(CTEs)和窗口函数是报表、OLAP业务一大利器
  • 不可见索引。SQL执行时内部优化器忽略指定索引;验证删除索引后对查询性能影响。
    Terminal window
    Alter table t1 alter index idx_str invisible或者visible;
  • 降序索引(Descending Indexes):索引按倒序存储,之前方式都是按顺序存储;使用到具有倒序、升序列的复合索引
  • 函数索引(Functional Indexes): select * from moatkon where year(date_str)=2023; 也可以使用到索引
  • 默认字符集为utf8mb4。更好的存储补充字符,如emojis表情符号;可变长度编码字符性能提升
  • 一致性查询改进(Better Handling of Hot Rows):
    • SKIP LOCKED: 需要加锁的记录若被其它线程占有锁,则跳过,而不是等待
    • NOWAIT: 需要加锁的记录有锁则报错

    SKIP LOCKED和NOWAIT的使用场景: 适合热行(Hot Rows)场景,例如秒杀,抢红包之类的业务

  • 资源组(Resource Groups): 线程赋给不同的资源组;资源组和不同的内存、IO、CPU(现仅支持)进行关联;官方版多租户资源隔离成为了可能,更好提升在不同读写业务场景下的性能
  • 新的数据字典:以前版本MySQL数据字典存放在多个地方,一机器多实例时存在大量文件描述符性能消耗,8.0版本都存放在事务性InnoDB表,MySQL异常挂掉后也不会再出现表损坏情况;DDL操作失败也不会再留下占空间的“临时文件”
    • 基于innodb的库表元数据信息
    • 增强了MySQL crash-safe能力
    • 原子DDL( Atomic DDLs )
  • MGR增强: MGR的增强大大提升了在网络异常(机房级故障)下的健壮、稳定性;之前多次的机房故障演练8.0 MGR也都符合预期,网络恢复后MGR节点自动加入group 恢复读写