Published on

面试 Mysql

Authors
  • avatar
    Name
    Shelton Ma
    Twitter

Mysql

1. 如何在MySQL中进行事务管理？解释一下事务的ACID特性

事务通过 BEGIN, COMMIT, ROLLBACK 来管理。ACID特性：

  • Atomicity: 原子性，事务中的操作要么全部执行，要么全部不执行。
  • Consistency: 一致性，事务开始前后数据库的状态必须一致。
  • Isolation: 隔离性，一个事务的执行不应受其他事务的干扰。
  • Durability: 持久性，事务提交后，其结果应该永久保存。

2. 什么是数据库索引？它如何影响查询性能？如何优化查询？

索引是加速数据检索的结构，但会影响插入、更新和删除性能。通过创建适当的索引（如B树索引）可以优化查询性能，尤其是大数据量的查询。

数据库索引特点:

  • B+树, 性能好, 支持范围/排序/左前缀, 索引平衡, 不适合频繁更改
  • Hash索引, 速度快, 内存占用少, 只适合=, IN()查询
  • FUllTEXT 全文索引, 适合模糊查询
  • R-数, 主要用于多维数据(地理位置, 地图数据)等

MySQL中的GROUP BY和HAVING有什么区别？举例说明

GROUP BY用于将结果集按某字段分组，HAVING用于对分组后的数据进行筛选。WHERE用于筛选行数据，HAVING用于筛选分组后的数据。

如何在MySQL中实现数据库的备份与恢复？

使用mysqldump工具来备份，使用mysql命令恢复。例如：备份：mysqldump -u root -p mydb > backup.sql 恢复：mysql -u root -p mydb < backup.sql

MySQL中的外键约束是什么？它如何确保数据完整性？

外键约束确保表之间的关联数据一致性。如果某个表中的数据存在依赖关系，外键约束就可以防止不符合完整性的操作，如删除或更新与其他表关联的数据。

解释下mysql脏写, 以及如何避免

在MySQL中，脏写（Dirty Write）是指一个事务正在修改某一行数据，而另一个事务也在同时修改该行数据，导致数据库中出现不一致的状态。这种情况通常会在并发事务中出现，因此，MySQL提供了多种事务隔离级别来避免脏写并保证数据一致性。

MySQL提供了四种标准的事务隔离级别，每种级别都对脏写的防止程度有所不同。下面我们逐一解释这四种隔离级别，并特别说明如何防止脏写

  1. 读未提交（Read Uncommitted）

    • 在读未提交隔离级别下，一个事务可以读取另一个事务未提交的修改。这意味着脏读（Dirty Read）是允许的，并且如果一个事务修改的数据被另一个事务读取且未提交，就可能出现脏写的情况。
    • 脏写： 由于事务1修改了某行数据，但未提交，而事务2也修改了同一行数据并提交了，最终事务1的修改会被覆盖，导致数据不一致。
    • 防止脏写： 这种隔离级别无法防止脏写，因此它通常不建议在大多数应用中使用。

  2. 读已提交（Read Committed）

    • 在读已提交隔离级别下，一个事务只能读取另一个事务已经提交的数据。它可以防止脏读，但是仍然无法防止不可重复读（Non-repeatable Read）。即，一个事务读取了某个数据，在事务过程中该数据可能会被其他事务修改。
    • 脏写： 在这个隔离级别下，脏写依然是可能的。虽然事务只能读取已提交的数据，但如果一个事务修改了某一行数据而未提交，另一个事务仍然可以修改并提交该数据，从而导致脏写。
    • 防止脏写： 该隔离级别仍然无法完全防止脏写，因此也不适合在需要严格一致性的场景下使用。

  3. 可重复读（Repeatable Read）

    • 可重复读是MySQL的默认事务隔离级别。它通过保证一个事务在其生命周期内始终读取相同的数据（即使其他事务已修改该数据）来防止不可重复读。
    • 在这个隔离级别下，事务可以确保它在开始时读取的数据在整个事务期间是相同的，即使其他事务对该数据进行了修改。
    • 但是可重复读隔离级别并不能完全避免脏写，因为即使事务1修改了某一行数据，事务2依然可以修改并提交该数据，覆盖事务1的修改，导致脏写。
    • 防止脏写： 由于这个级别允许事务读取其他事务已提交的数据，因此脏写依然可能发生。

  4. 串行化（Serializable）

    • 在串行化隔离级别下，事务不仅能读取已提交的数据，还会通过加锁机制，确保在事务过程中，其他事务无法对该数据进行修改。换句话说，事务A对某一行数据进行修改时，事务B必须等待事务A完成并提交后才能对该数据进行操作。
    • 脏写： 串行化隔离级别通过强制事务排队来避免脏写。即使多个事务修改同一行数据，它们会按顺序执行，避免了一个事务覆盖另一个事务未提交的修改。
    • 防止脏写： 串行化隔离级别能有效防止脏写，因为它确保在同一时间只有一个事务能够修改某一行数据，其他事务需要等待。

解决脏写的关键: 要防止脏写，需要确保事务中的数据在执行时不会被其他事务修改。最有效的方式是使用串行化隔离级别（Serializable）。这种隔离级别通过强制加锁机制，确保一个事务在执行时不会被其他事务干扰，避免了脏写的发生。

MySQL索引数据结构

  • B+树索引: 适合大多数情况，尤其是范围查询。
    • 主键索引（PRIMARY KEY）: InnoDB 表的主键默认使用 B+Tree 存储，叶子节点存储整行数据（聚簇索引）。
    • 普通索引（INDEX）: 非主键索引的叶子节点存储的是主键值（非聚簇索引/二级索引），再通过主键值回表查找完整数据。
  • 哈希索引: 适合等值查询，但不支持范围查询。
  • 全文索引: 适合文本数据的搜索。
  • 空间索引: 适合地理位置数据和空间查询。

SQL语句执行顺序

SELECT columns
FROM table1
JOIN table2 ON condition
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column
LIMIT number OFFSET number;

执行顺序:

  1. FROM
  2. ON (如果有 JOIN)
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. SELECT
  7. ORDER BY
  8. LIMIT / OFFSET

mysql什么情况会出现索引失效

  1. 使用了不适当的运算符或函数(索引列使用 LIKE 操作符)
  2. OR 操作符导致索引失效
  3. 在索引列上使用 NULL 值
  4. OIN 操作时的非最优顺序
  5. 索引列的数据类型不匹配
  6. 使用了 DISTINCT 或 GROUP BY 时，字段不包含在索引中
  7. ORDER BY 不符合索引顺序
  8. 使用 IN 时索引失效 ...

Mysql怎么判断查询走索引还是全表(explain)

sql慢查询优化

  1. 避免索引失效
  2. explain检查
  3. 避免锁表
  4. 规范化设计表结构
  5. 考虑使用缓存, 避免事务太长的查询, 特别是高并发的情况下

分页查询, 最后一页和第一页哪个快

  1. 传统分页方式（LIMIT + OFFSET）, 在使用 LIMIT 和 OFFSET 进行分页时，查询的性能通常会随着页数的增加而下降，特别是在查询较大数据集时。
  2. 基于主键或唯一标识符的分页（WHERE 子句）, 为了优化分页查询，尤其是在需要查询最后一页时，可以使用 基于主键或唯一标识符 的分页方式，这样就不需要依赖 OFFSET，查询性能会显著提高。
  3. 如果你需要频繁查询大数据集的分页，基于主键的分页方式会更高效，避免 OFFSET 带来的性能下降。

redis持久化机制, 优缺点

Redis 提供了几种持久化机制，允许将内存中的数据持久化到磁盘，以确保在服务器重启后数据不会丢失。Redis 提供了两种主要的持久化机制：

  1. RDB（Redis 数据库快照）持久化: 性能好, 但容易丢失数据
  2. AOF（Append-Only File）持久化: 安全性好, 但性能差, 恢复速度慢

mysql数据库调优

  1. 查询优化

    查询是数据库性能瓶颈的主要来源，优化查询可以显著提高性能。常见的查询优化方法包括：

    • 避免全表扫描：通过合理的索引和查询条件，避免扫描全表。全表扫描通常会消耗大量的 I/O 和 CPU 时间。
    • 优化 JOIN 操作：
    • 使用合适的连接类型，如选择内连接（INNER JOIN）而不是外连接（LEFT JOIN），减少计算量。
    • 尽量避免在 JOIN 中使用复杂的表达式，可以通过在查询前对表进行预处理来优化。
    • 使用子查询优化：避免使用不必要的子查询，尤其是在 SELECT 中的嵌套查询。可以考虑将子查询转换为连接查询。
    • 查询缓存：对于频繁访问的相同查询，可以开启数据库的查询缓存，减少重复查询的计算成本。
    • LIMIT 和分页优化：对于大数据量的分页查询，避免使用 OFFSET，因为 OFFSET 会跳过数据行，导致查询效率下降。可以使用基于范围的分页查询（如 WHERE id > x LIMIT 10）。

  2. 索引优化

    索引是数据库查询加速的核心，合理的索引能够显著提升查询性能，然而，过多或不合理的索引会导致性能下降。常见的索引优化方法：

    • 使用合适的索引类型：选择适当的索引类型（如 B+ 树、哈希索引、全文索引等）以提高查询效率。
    • 避免过多索引：尽量减少不必要的索引，因为每次数据插入、更新或删除时，都会对索引进行维护，影响性能。
    • 多列索引：对多列查询（尤其是 WHERE 子句中多个条件同时出现）可以创建多列索引，减少查询时间。
    • 覆盖索引：当查询所需的字段完全包含在索引中时，可以使用覆盖索引，这样数据库就不需要回表查询，从而提高查询性能。
    • 定期重建索引：当表发生大量插入、更新或删除操作时，索引可能会出现碎片化，影响查询性能。定期重建索引可以保持索引的效率。

  3. 并发控制与锁优化

    在多线程和高并发的数据库中，锁是影响性能的重要因素。过多的锁竞争会导致数据库性能下降。常见的并发控制优化方法：

    • 锁粒度：避免长时间持有锁，使用行级锁代替表级锁，以减少锁竞争。
    • 事务优化：尽量减少事务的持有时间，避免在事务中进行复杂的计算或查询操作，减少锁定时间。
    • 死锁检测与预防：使用数据库的死锁检测机制，避免死锁的发生。在设计应用时，避免嵌套事务和循环依赖。

  4. 缓存优化

    缓存可以显著减轻数据库的负载，减少查询延迟。常见的缓存优化方法包括：

    • 使用内存数据库：如 Redis、Memcached 等，可以将频繁查询的数据缓存到内存中，避免重复查询数据库。
    • 设置缓存过期策略：为缓存设置合理的过期时间，避免缓存过期过慢，导致数据库负载过重。
    • 缓存失效机制：合理设计缓存失效机制，避免缓存雪崩和缓存击穿等问题。
    • 缓存预热：对于一些热点数据，可以通过定期预热缓存来减少冷启动时的压力。
Discuss on Twitter • View on GitHub