Logo

MySQL必知必会:常见问题解答与实用指南

作者

MySQL必知必会:常见问题解答与实用指南

欢迎阅读本篇MySQL必知必会的常见问题解答与实用指南。无论您是初学者还是有经验的开发者,这些问题与解答将帮助您更好地理解和优化MySQL数据库。

如何优化分页查询?

当处理大量数据时,限制用户只能一页一页地翻阅,每次查询都提供当前页的最大ID,然后基于该最大ID查询特定数量的数据。

可靠性实现

  • Redo Log(重做日志)
  • Undo Log(撤销日志)
  • Binlog(二进制日志)

事务的可重复读是如何实现的?

  • 通过为每一行添加两个隐藏字段实现多版本并发控制(MVCC),这两个字段用于存储事务的版本号。
  • 每次启动新事务时,事务的版本号会递增。
  • 如果数据的事务ID(trx_id)位于低水位(水位以下),则可见。
  • 如果数据的事务ID位于高水位(水位以上),则不可见。
  • 如果数据位于黄色区间,则表示活跃事务ID数组。
  • 如果事务ID在数组中,则表示未提交,不可见。
  • 如果事务ID不在数组中,则表示已提交,可见。

读提交和可重复读实现的区别?

  • 可重复读在事务开始时创建一致性视图。
  • 读提交在语句执行时创建一致性视图。

Redo Log和Undo Log的区别?

  • Redo Log是前滚日志,Undo Log是回滚日志。
  • Redo Log保证持久性,Undo Log保证原子性。
  • Undo Log保存数据的历史版本,可以通过历史版本将数据回滚到某个事务开始之前的状态。
  • Redo Log记录数据修改的操作日志,通过操作日志可以保证数据库的数据不丢失。

MySQL如何保证数据不丢失?

MySQL通过两阶段提交来保证数据不丢失:

  1. 首先进行prepare操作,实际上是将数据刷入磁盘。
  2. 如果Redo Log完整,则提交;如果Binlog完整,则提交。
  3. 如果不完整,则回滚。
  4. 如果commit已经刷入磁盘,则直接提交。

MyISAM和InnoDB的区别?

  • MyISAM不支持事务,而InnoDB支持事务。
  • MyISAM不支持行级锁定,而InnoDB支持行级锁定。
  • MyISAM支持FULLTEXT全文索引,而InnoDB不支持。

count(*)、count(1)和count(col)的区别?

  • count(*)和count(1)是相同的,用于统计行数,不会忽略NULL值。
  • count(col)用于统计行数,会忽略col=NULL的行。

可重复读隔离级别下,MVCC在数据的CRUD层面分别如何实现?

  • SELECT:
    • 读取创建版本小于或等于当前事务版本号,并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
  • INSERT:
    • 将当前事务的版本号保存为行的创建版本号。
  • UPDATE:
    • 新插入一行,并以当前事务的版本号作为新行的创建版本号,同时将原记录行的删除版本号设置为当前事务版本号。
  • DELETE:
    • 将当前事务的版本号保存为行的删除版本号。

索引命名规范?

通常使用以下命名规范:

  • 唯一索引:uk
  • 普通索引:idx

select into from和insert into select from的区别?

  • select into from要求目标表不存在。
  • insert into select from要求目标表存在。

如何保证MySQL不丢失数据?

  • 设置innodb_flush_log_at_trx_commit为1,每次提交都会刷盘(针对Redo Log)。
  • 设置sync_binlog为1,每次提交事务都会执行fsync(针对Binlog)。

Redo Log + Binlog写入策略

Redo Log的写入策略

InnoDB提供了innodb_flush_log_at_trx_commit参数,有三种可能的取值:

  • 设置为0时,每次事务提交时只将Redo Log保留在Redo Log Buffer中。
  • 设置为1时,每次事务提交时将Redo Log直接持久化到磁盘。
  • 设置为2时,每次事务提交时只将Redo Log写入页缓存

Binlog的写入策略

Binlog的写入策略由参数sync_binlog控制:

  • 当sync_binlog = 0时,每次提交事务只进行写操作,不执行fsync。
  • 当sync_binlog = 1时,每次提交事务都会执行fsync。
  • 当sync_binlog = N(N > 1)时,每次提交事务进行写操作,但累积N个事务后才执行fsync。

在出现IO瓶颈的情况下,将sync_binlog设置为较大的值可以提升性能。常见的设置是100~1000之间的某个数值。

然而,将sync_binlog设置为N的风险是,如果主机发生异常重启,会丢失最近N个事务的Binlog日志。

Binlog日志类型

  • Statement模式:记录SQL语句的执行过程。
  • Mixed模式:主要用于解决在Row模式下执行表结构变更时产生大量数据的问题。
  • Row模式:记录每一行数据的变更情况。

生产配置建议

通常情况下,建议使用"双1"的配置,即将sync_binlog和innodb_flush_log_at_trx_commit都设置为1。这意味着在一个事务完整提交之前,需要等待两次刷盘操作,一次是Redo Log的刷盘,一次是Binlog的刷盘。

性能瓶颈和提升方法

如果你的 MySQL 现在出现了性能瓶颈,而且瓶颈在 IO 上,可以通过哪些方法来提升性能呢?

  • 设置 binlog_group_commit_sync_delaybinlog_group_commit_sync_no_delay_count参数,减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但没有丢失数据的风险。
  • 将 sync_ binlog 设置为大于 1 的值(比较常见是 100~1000)。这样做的风险是,主机掉电时会丢 binlog 日志。
  • 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是,主机掉电的时候会丢数据。

不建议把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0,表示 redolog 只保存在内存中,这样的话 MySQL 本身异常重启也会丢数据,风险太大。而 redolog 写到文件系统的 page cache 的速度也是很快的,所以将这个参数设置成 2 跟设置成 0 其实性能差不多,但这样做 MySQL 异常重启时就不会丢数据了,相比之下风险会更小。

MySQL 为什么建议使用自增组件?

  • 便于范围查询
  • 避免插入的时候页分裂,影响插入效率

分享内容