Skip to content
Moatkon

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机器上配置:

终端窗口
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机器上配置:

终端窗口
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执行时内部优化器忽略指定索引；验证删除索引后对查询性能影响。
    终端窗口
    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 恢复读写

网站当前构建日期: 2024.11.18