Mysql - Q&A - 1

Kesa...大约 22 分钟

1. Basics

1.1 常用索引有哪些

普通索引、唯一索引、主键索引、组合索引、全文索引

普通索引：基本索引类型，用于加速系统对数据的访问速度

例如，建立 index_id 在 tb_student 表上：

CREATE INDEX index_id ON tb_student(id);

唯一索引：索引值必须唯一，允许空值

ALTER TABLE `table_name` ADD UNIQUE (`column`)

主键索引：特殊唯一索引，不允许空值

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)

组合索引：一个索引包含多个列

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

全文索引：仅用于 MyISAM，用于检索长文本

1.2 聚集索引和非聚集索引的区别

  • 聚集索引(Clustered Index)：
    • 聚集索引的数据行和索引的顺序相同，即其将数据行本身和索引按照相同的排序方式存储
    • 一个表只能有一个聚集索引，因为数据行只能以一种顺序存储
    • 聚集索引通常用主键建立，也可以使用其他具有唯一约束的列
    • 数据的物理存储顺序和索引的顺序相同，故聚集索引通常提供较快的数据检索
  • 非聚集索引(Non-clustered Index)：
    • 非聚集索引与聚集索引分开存储，数据行的顺序与索引的顺序无关
    • 一个表可以有多个非聚集索引
    • 非聚集索引包含用于检索的键值和一个指针，指向行数据的实际位置
    • 非聚集索引通常用于在不同于主键的其他列上提高查询性能

小结

聚集索引按照索引顺序存储数据行，而非聚集索引将索引与数据行分开存储。

一个表只能有一个聚集索引，但可以有多个非聚集索引。

在查询性能上，聚集索引通常提供较快的数据检索，而非聚集索引则在其他列上提高查询性能。

1.3 MySQL 为什么使用 B+ Tree 作为存储的数据结构

MySQL 使用 B+树（B+Tree）作为存储数据结构的主要原因是其在查询性能、空间利用和磁盘IO效率方面的优势。以下是 B+树在数据库中具有优势的几个方面：

  1. 查询性能：B+树使一种自平衡的多路搜索树，这意味着它能够在树的深度方面保持相对均衡。这样，在查找数据时，即使数据量非常大，也能保证较低的查询成本。
  2. 范围查询：B+树的叶子节点的记录(或键值)是有序的，使得其适合进行范围查询。
  3. I/O 效率：B+树的结构使其能够很好的处理磁盘I/O，在B+树中所有的数据记录都存在叶子节点中，非叶子节点只包含索引信息。这样I/O次数将会减少，提高查询性能。
  4. 空间利用：B+树的内部节点只存储索引信息而不存储实际数据，所以每个内部节点可容纳的索引量更多，树的高度将更低。意味着磁盘空间能够被更有效的利用。

1.4 Mysql 的 B+Tree 非叶子节点有多少数据，一般有几层

InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用 4 个字节）或 BIGINT（占用 8 个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的 K 取值为〖10〗^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护 10^3 _ 10^3 _ 10^3 = 10 亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

1.5 MyISAM、InnoDB 索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

Primary Key.png
Primary Key.png

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

区别：

  • InnoDB的数据文件本身就是索引文件。 表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
  • MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
Primary Key.png
Primary Key.png

1.6 MyISAM与InnoDB的主要区别

事务支持 (Transaction Support)

  • MyISAM: MyISAM 不支持事务。不适用于需要处理复杂事务和严格保证数据完整性的场景。
  • InnoDB：InnoDB 支持事务(ACID)，提供提交、回滚等事务相关操作，适用于处理复杂业务并确保数据完整性。

行级锁定(Row-level Locking)

  • MyISAM：使用表级锁定，当对表进行写操作（插入、删除和更新）时，会锁定整个表。将降低并发性能
  • InnoDB：使用行级锁定，允许高并发环境下对多个记录进行读写访问，提高数据库性能

数据存储和索引

  • MyISAM：将数据和索引文件分开存储。主要支持非聚集索引
  • InnoDB：以聚集的方式组织存储，支持聚集和非聚集索引。索引按照相同顺序存储，提高了查询性能

数据完整性(Data Integrity)

  • MyISAM：不支持外键约束，无法保证数据间的引用完整性
  • InnoDB：支持外键约束，可以在数据表间维护引用关系，确保数据完整性

崩溃恢复(Crash Recovery)

  • MyISAM：不具备原生的崩溃恢复能力。在数据库崩溃或电源故障的情况下可能造成数据损坏或丢失
  • InnoDB：提供日志和检查点(Check Point)机制，支持故障和现场恢复。

全文搜索(Full-text Search)

  • MyISAM：提供全文搜索功能
  • InnoDB：早期不支持，5.6.4 之后也支持

小结

  • MyISAM更适合用于读取速度要求高、不要求事务支持和数据完整性严格保障的简单查询场景。

  • InnoDB 更适合事务处理、并发读写较高且需要保证数据完整性的场景。

在实际应用中，根据不同的需求和场景选择合适的存储引擎非常重要。

1.7 为字段建立索引的好处？越多越好么？

建立索引的好处：

  1. 提高查询速度；通过索引可以避免扫描全表，而是直接定位到记录，节省查询时间
  2. 加速排序和分组；索引可以用于对排序和分组操作进行优化，提高查询性能
  3. 支持外键约束；InnoDB中，索引可用于支持外键约束，确保数据引用的完整性

索引不是越多越好，索引的缺点：

  1. 占用存储空间；索引需要额外的存储空间，过多的索引会占用更多的磁盘空间
  2. 数据插入、更新和删除性能；数据库在插入、更新和删除数据时，需要维护相关索引，过多的索引会影响性能。
  3. 索引维护成本：索引需要维护，包括碎片整理、索引重建等操作。过多的索引意味着更高的维护成本。

小结

创建索引可以提高数据库查询性能，但是过多的索引会导致额外的存储空间和性能开销。在创建索引时，应根据实际需求进行适当的权衡。

1.8 MySQL 慢查询常见原因

MySQL慢查询可能出现的原因有很多。以下是一些常见的慢查询原因：

  1. 缺少索引或使用不当：未对查询中的关键字段创建索引、索引选取不当或统计信息不准确，导致 MySQL 无法高效地查询数据，从而降低查询速度。
  2. 查询语句设计不合理：复杂的子查询、使用了大量的临时表、笛卡尔积查询等，可能导致查询性能低下。
  3. 数据量过大：当表中的数据量过大时，未经优化的查询可能需要处理大量数据，从而降低查询速度。
  4. 锁争用：在高并发读写的场景下，如果 MySQL 存储引擎（如 MyISAM）使用表级锁或行级锁锁定不当，可能导致锁争用，进而降低查询性能。
  5. 硬件资源限制：如 CPU、内存、磁盘和网络等硬件资源不足，可能导致查询性能低下。
  6. MySQL 配置不当：MySQL 的配置参数未经优化，可能在一定程度上影响查询性能（如缓冲区大小不合适、连接数设置不正确等）。
  7. 数据分布不均：在使用分布式数据库或分区表时，如果数据分布不均匀，可能导致部分查询性能较低。
  8. 过时的统计信息：MySQL 使用统计信息来选择最佳索引。如果统计信息过时或不准确，可能导致 MySQL 选择不佳的查询计划。

针对这些常见的慢查询原因，可以采取相应措施进行优化。

例如：创建和优化索引、改进查询语句、调整硬件配置、优化 MySQL参数设置等。同时，可以开启慢查询日志来分析和诊断慢查询问题。

1.9 SQL 优化原则，索引注意事项？

SQL 优化原则

  1. 减少数据请求量：尽量减少请求的数据量，只请求需要的列和必要的记录。
  2. 避免 SELECT * 查询：仅查询所需的列，以减少数据传输和处理的开销。
  3. 优化连接和子查询：连接查询时，避免全外连接（Full Outer Join）和笛卡尔积连接，而使用内连接（Inner Join）或左（右）连接（Left/Right Join）。尽量减少子查询，考虑使用连接查询或者临时表代替。
  4. 分批处理数据：如果需要处理大量数据，可以使用分页查询、limit 语句或其他批处理技术，以降低数据处理压力。
  5. 利用索引：在查询语句中使用索引相关的列，尽量避免对索引字段进行计算、函数或表达式操作，以充分利用现有索引加速查询。
  6. 避免隐式类型转换：确保在查询中进行比较或计算的字段类型匹配，以避免隐式类型转换导致查询性能下降。
  7. 聚合操作优化：在对大量数据执行聚合查询（如求和、计数等）时，尽量使用索引进行覆盖查询，减少数据扫描量。

索引注意事项

  1. 选择合适的索引列：在查询中常用作过滤、连接和排序的列是创建索引的最佳候选。另外，考虑数据的区分度，数据分布较均匀且重复值较少的列更适合创建索引。
  2. 避免过多索引：索引虽然可以提高查询性能，但它会占用额外的存储空间并影响数据的插入、更新和删除操作。因此，应权衡实际情况，充分考虑查询需求和数据变更频率，以避免过多索引。
  3. 使用组合索引：在多个字段上经常一起进行查询的场景，可以创建组合索引。但应注意，组合索引的顺序会影响到查询性能。
  4. 避免在索引列上进行计算、函数或表达式操作：在 SQL 查询中，尽量不要对索引列进行计算、使用函数或进行表达式操作，这会导致索引失效。
  5. 定期评估索引效果：定期评估现有索引的使用情况、查询性能和索引开销等方面，根据实际需求添加、删除或调整索引。
  6. 考虑使用部分索引：如果某个列上只有部分数据需要被查询，可以考虑创建部分索引，以降低索引维护成本。

结合实际数据库使用情况调整和优化 SQL 查询语句以及索引策略，有助于提升数据库查询性能和整体性能。所采取的优化措施应根据业务需求、数据量级和具体场景进行调整。

1.10 事务的 ACID

  • 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
  • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。
  • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
  • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

1.11 事务的隔离级别

读数据一致性及并发副作用读数据一致性脏读不可重复读幻读
读未提交(read uncommitted)最低级别，不读物理上损坏的数据是是是
读已提交(read committed)语句级否是是
可重复读(Repeatable read)事务级否否是
可序列化(Serializable)最高级别，事务级否否否
  • 更新丢失(Lost Update)：当两个或多个事务选择同一行，然后基于最初选定的值更新，由于事务不知道其他事务的存在，导致最后的更新覆盖其他事务更新，发生更新丢失。
  • 脏读(Dirty Reads)：一个事务正在对记录进行修改，在提交之前，另一个事务读取该记录，此时读取到的是脏数据。
  • 不可重复读(Non-repeatable Reads)：一个事务在读取某些数据后，再次读取时，数据被修改或已删除
  • 幻读(Phantom Reads)：一个事务按照相同的条件重新读取之前检索过的数据，读取到其他事务新插入的满足条件的数据。

1.12 什么是当前读和快照读？

当前读

读取的记录的最新版本，读取时保证其他并发事务不能修改当前记录，会对记录进行加锁。

  • select lock in share mode(共享锁)
  • select for update ， update, insert ,delete(排他锁)

这些操作都是一种当前读。

快照读

不加锁的非阻塞读，基于多版本并发控制MVCC。

特性：

  1. 快照读读取的不一定是最新版本，可能是之前的历史版本
  2. 当隔离级别为串行级别(Serializable)，快照读将退化成当前读

不加锁的SELECT操作就是快照读。

leck in shave mwe.png
leck in shave mwe.png

1.13 什么是MVCC

MVCC(Multi-Version Concurrency Control，多版本并发控制) 是一种允许多个事务同时访问数据库的技术，旨在提高数据库在高并发环境下的性能。

核心思想

在同一个事务中，对同一个数据的多次查询始终保持一致性，而不加锁来实现隔离，以避免加锁带来的性能损耗。

实现

MVCC 通过为每行数据生成多个版本，在不同的事物中访问不同版本的数据，实现并发控制。

每个事务开始时将会获得唯一的事务ID，读取的数据行需要满足以下条件：

  1. 数据行的创建版本小于等于事务ID
  2. 数据行的删除版本大于等于事务ID

每个事务将能以一致的快照(snapshot)视图访问数据库，且互不影响。

每行记录除了用户自定义字段外，还有数据库隐式定义字段：

  • DB_TRX_ID，6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
  • DB_ROLL_PTR，7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  • DB_ROW_ID，6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  • 删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了。

1.14 事务开始的时机

begin/start transaction不是事务的起点，执行之后的第一个操作数据表的语句时才会启动事务。

start transaction with consistent snapshot 可以立刻启动事务。

1.15 什么是预读

读取硬盘数据，是按页读取，一次至少读取一页的数据，能够减少I/O，提高效率。

1.16 什么是缓存池(Buffer Pool)

  1. 缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；
  2. 缓冲池通常以页(page)为单位缓存数据；
  3. 缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；
  4. InnoDB对普通LRU进行了优化：
    • 将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题
    • 页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题
head new-sublist.png
head new-sublist.png

1.17 简述SQL查询过程

优化器.png
优化器.png

1.18 简述SQL更新过程

写入新行.png
写入新行.png

1.19 什么是脏页

内存数据页和硬盘数据页不一致时，内存页被称为脏页。

1.20 InnoDB锁的类型

image.png
image.png

1.21 什么是两阶段锁

在事务中，行锁只在需要时加上，而在事务提交时释放，并不是在不需要时释放。

1.22 如何解决死锁

当两个事务互相等待时，进入死锁状态，有两种方式解决：

  • 等待超时，通过innodb_lock_wait_timeout设置
  • 发起死锁检测，主动回滚死锁链条中的某个事物，让其他事物继续执行。 通过innodb_deadlock_detect设置为 on 开启
事务A.png
事务A.png

2. Log

2.1 什么是 binlog

binlog是逻辑日志，记录语句的原始逻辑。

  • 使用追加写入模式，不覆盖之前的数据，可以提供完整的数据归档能力。

2.2 什么是 redo log

redo log是物理日志，记录在某个数据页上做了什么修改。

  • 拥有 crash-safe 能力
  • 一般只有 4GiB，4个文件，循环复写

2.3 binlog和redo log的区别

  • redo log 为InnoDB独有； binlog为MySQL底层实现，所有的引擎均可使用
  • redo log为物理日志，记录在数据页上的具体修改； binlog为逻辑日志，记录SQL语句的原始逻辑
  • redo log采用循环写入，文件大小固定； binlog：采用追加写入，不会覆盖之前的记录

2.4 简述binlog的写入机制

写入流程：

  1. 事务执行过程中，将日志写入binlog cache(内存中)
  2. 事务提交之后，将binlog cache写入到binlog文件(硬盘中)，清空binlog cache
image
image

2.5 简述redo log的写入机制

写入流程：

  1. 将redo log写入到redo log buffer中
  2. 将buffer的内容写入到文件系统的page cache
  3. 调用fsync将page cache的内容持久化到硬盘中
Server.png
Server.png

InnoDB可以通过innodb_flush_log_at_trx_commit控制redo log的写入：

  • 为 0 时，表示每次事务提交时，只写入到redo log buffer
  • 为 1 时，表示每次事务提交时，持久化到硬盘
  • 为 2 时，表示每次事务提交时，写入到page cache中

InnoDB的后台线程，每个 1s ，将redo log buffer 中的日志，调用write写入page cache，然后调用fsync写入硬盘。

2.6 简述 redo log的存储方式，事务没有提交时redo log会写入到硬盘中吗？

存储方式

redo log只在四个文件中循环写入：

  • write pos记录当前位置，一边写入一边后移，结束最后一个文件时就从第一个文件开始继续
  • check point表示当前擦除的位置，移动方式和write pos相同
  • 两者之间就是新增加的数据，若checkpoint和write pos相遇则表示无法在写入，需要擦除数据
write POs.png
write POs.png

redo log可以保证数据库异常重启之后，已提交的数据不会丢失，被称为cache safe

未提交事务

未提交的事务是有可能被写入到硬盘中的：

  1. 当后台线程执行持久化操作时，缓存中的未提交事务redo log会被一起写入硬盘
  2. 当redo log buffer容量达到innodb_log_buffer_size时，后台线程会主动写入page cache，之后可能会被写入硬盘
  3. 当一个线程的innodb_flush_log_at_trx_commit被设置为 1，表示任何提交将直接写入硬盘，此时若缓存中包含其他事务的记录，将被一同写入

2.7 简述redo log组提交(group commit)机制

日志逻辑序列号(log sequence number, LSN)，单调递增，用于表示redo log的写入点，每次写入长度为length的log之后，LSN 将增加length。

LSN 也会被写到InnoDB数据页中，确保数据页不会被多次执行重复的redo log。

image
image

如上图所示，当事务trx1准备写入硬盘时：

  • 缓存中trx2,trx3均已完成，LSN此时为 160
  • 此时会将 160 之前的数据全部写入硬盘

每次一组提交中，一组事务将会被一次写入硬盘，提高了性能。

2.8 Binlog和Redolog记录如何保持一致

进行两段提交，双方会等到两方都写入缓存之后才会开始写入到硬盘中。

  1. redolog写入缓存
  2. binlog写入缓存
  3. redolog写入文件
  4. binlog写入文件
log prepore.png
log prepore.png

2.9 执行一个 update 语句以后，再执行 hexdump 命令直接查看 ibd 文件内容，为什么没有看到数据有改变呢

这可能是因为WAL机制的原因。update语句执行完成后，InnoDB只保证写完了redo log、内存，可能还没来得及将数据写到磁盘。

2.10 为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的

  • binlog不能被中断，一个事务的 binlog必须连续写，等到事务完成之后再写入到文件中
  • redolog没有这种要求，只要有生成redolog就可能被写入到文件中

2.11 事务未提交时若发生crash，会导致主备不一致么

不会

binlog和redolog均在缓存中，crash 之后将丢失，但因事务没有提交，所以此时主备数据是一致的

2.12 MySQL 如何知道binlog是完整的

一个事务的binlog是存在标识：

  • statement格式，事务完成后会有COMMIT
  • row格式，事务结束后会有XID event

MySQL 有binlog-checksum参数，用于验证内容的正确性。

2.13 redolog和binlog如何关联

通过XID字段，当发生崩溃恢复时，按顺序扫描redolog：

  • 若扫描到完整事务，即事务的prepare到commit均存在，则直接提交
  • 若仅扫描到事务prepare，此时会根据XID在binlog寻找事务

2.14 为何binlog和redolog需要进行两段提交？不可以redolog提交之后在提交binlog吗

因为redolog若已经提交，写入到文件中，事务此时无法回滚（若回滚则会覆盖其他的事务）。后续若binlog提交失败，则会导致数据不一致，无法恢复。

所以，需要两者都提交成功后，在写入到文件中。

2.15 可以只用redolog，不用binlog么

若只考虑崩溃恢复，可以只是用redolog。

若需要历史记录，则需要binlog。因为redolog时在四个文件中循环写入，会覆盖掉历史数据，造成历史记录丢失。

2.16 redolog buffer是什么？

begin;
insert into t1 ...
insert into t2 ...
commit;

对于事务来说，执行时会将日志写入到缓存redolog buffer中，只有到commit时才会被写入到文件中。

2.17 为何binlog不能被中断

binlog用于记录事务的操作，若事务记录被中断，则破环了事务的原子性特征。

2.18 undolog是什么

undo用于回滚记录到某个版本，undolog一般是逻辑记录，根据每行进行记录。

2.19 简述binlog的三种模式

  1. STATEMENT模式(SBR)：每一条会修改数据的SQL语句会记录到binlog中；

    • 优点是并不需要记录每一条SQL语句和每一行的数据变化，减少日志量。
    • 缺点是在某些情况下会导致master-slave中的数据不一致(如 sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
    master.000001.png
    master.000001.png

  2. ROW模式(RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改，修改的结果。

    • 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题
    • 缺点：缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨
    image
    image

  3. MIXED模式(MBR)，以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于 STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式

2.20 MIXED模式的好处

MIXED模式会判断SQL语句是否可能会引起主备不一致，若有可能则使用ROW模式，否则才使用STATEMENT模式。利用了STATEMENT模式的优点又避免了主备不一致的问题。

上次编辑于:
0  字
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.15.2