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