Java面试必问题29：MySQL篇（重点必问）

1. 数据库的ACID特性

1. 原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败。事务是一个不可分割的单元，要么全部执行，要么全部回滚。如果事务中的任何操作失败，所有操作都将被回滚到事务开始之前的状态，以保证数据的一致性。
2. 一致性（Consistency）：事务的执行应使数据库从一个一致性状态转移到另一个一致性状态。在事务开始和结束时，数据库的完整性约束应得到满足，确保数据的正确性和一致性。
3. 隔离性（Isolation）：每个事务在执行过程中都应该与其他事务隔离。并发事务的执行应当互不干扰，每个事务应该感知不到其他事务的存在或并发执行。隔离级别定义了不同事务之间的可见性和互相影响的程度。
4. 持久性（Durability）：一旦事务提交成功，其对数据库的修改应该永久保存，即使系统发生故障或重启，也应该能够保持数据的持久性。

最简回答：ACID特性是指原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），用于保证数据库事务的可靠性和一致性。

1. MySQL存储引擎
2. 

MySQL默认是Innodb存储引擎，适合比较庞大的应用场景

1. InnoDB：MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。
2. MyISAM：MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

最简回答：InnoDB是MySQL的默认存储引擎，支持事务处理、行级锁和外键；而MyISAM不支持事务、只有表级锁，并且不支持外键。

1. 数据库的事务隔离级别

SQL 标准定义了四个隔离级别：

• • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

事务隔离级别越严格，数据库效率越低。MySQL默认的事务隔离级别是：REPEATABLE-READ可重复读级别，简称RR级别，会出现幻读问题。

最简回答：数据库事务隔离级别是指在多个并发事务同时执行时，各个事务之间的隔离程度，常见的隔离级别有读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

1. 索引会失效的情况有哪些

下面列举几种不走索引的 SQL 语句：

1. 索引列参与表达式计算：

1. SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;

1. 函数运算：

1. SELECT 'sname' FROM 'stu' WHERE LEFT('date',4) < 1990;

1. %词语%–模糊查询：

1. SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引
2. SELECT * FROM 'manong' WHERE `uname` LIKE '%码农%' -- 不走索引

1. 字符串与数字比较不走索引：

1. CREATE TABLE 'a' ('a' char(10));
2. EXPLAIN SELECT * FROM 'a' WHERE 'a'="1" — 走索引
3. EXPLAIN SELECT * FROM 'a'WHERE 'a'=1 — 不走索引，同样也是使用了函数运算

1. 查询条件中有 or ，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引：

1. select * from dept where dname='xxx' or loc='xx' or deptno = 45;

1. 正则表达式不使用索引。

1. MySQL索引底层原理

MySQL的索引底层结构是B+树。

B+树是一种平衡多路搜索树，具有以下特点：

1. 所有关键字保存在叶子节点，并且叶子节点之间通过链表连接，形成一个有序的叶子节点序列。

2. 非叶子节点只存储索引字段的值和子节点的指针，不保存实际的数据。这样可以使得一个节点可以存储更多的关键字，减少了树的高度，加快搜索速度。

3. 叶子节点包含所有索引字段的值和指向对应数据的指针。

在B+树索引中，每个节点的大小是固定的，与磁盘页的大小相当。节点的大小通常是数据库页的大小，例如16KB或32KB。每个节点可以存储多个关键字和指针。叶子节点的关键字是有序的，且通过链表连接在一起。

索引查询快的原因有以下几点：

1. 路径长度短：B+树具有平衡性，所有叶子节点的深度相同，因此在查询过程中只需要沿着树的高度进行几次磁盘I/O操作，所以查询速度较快。

2. 顺序访问优势：B+树的叶子节点之间使用链表连接，并且叶子节点的关键字是有序的，因此对于范围查询操作，可以通过顺序扫描叶子节点来获取有序的数据结果，提高查询速度。

3. 最小化磁盘I/O操作：B+树具有较高的填充因子，每个磁盘页上存储的关键字数量较多，能够减少磁盘I/O操作的次数，提高查询效率。

综上所述，B+树的平衡性、有序的叶子节点、顺序访问以及最小化的磁盘I/O操作是使得索引查询快速的关键因素。通过B+树的数据结构和索引的建立，可以大幅度减少磁盘访问次数，提高数据库查询的效率。

最简回答：MySQL索引底层原理使用了B+树数据结构，它是一种平衡树，能快速定位和检索数据；B+树的叶子节点存储实际数据，中间节点存储索引，通过减少磁盘IO来提高查询效率；索引按照值的大小顺序排列，使得范围查询效率更高。

1. MySQL优化方案
   • 服务器优化（增加CPU、内存、网络、更换高性能磁盘）
   • 表设计优化（字段长度控制、添加必要的索引）
   • SQL优化（避免SQL命中不到索引的情况）
   • 架构部署优化（一主多从集群部署）
   • 编码优化实现读写分离

1. SQL优化方案

1. 优化查询条件

   使用索引：确保所有涉及到的列都有适当的索引。

1.    SELECT * FROM table WHERE column = 'value';
2.    CREATE INDEX idx_column ON table(column);

   避免模糊查询：%开头的通配符会使索引失效，尽量避免在查询条件中使用以%开头的LIKE语句。

1. SELECT * FROM table WHERE column LIKE 'value%';

2. 使用合适的数据类型

   使用最小可能的数据类型：选择最合适的数据类型，不要使用比实际需要更大的数据类型。

1.   CREATE TABLE example (column1 INT, column2 VARCHAR(50));

   避免使用存储过大的数据类型：避免使用TEXT、BLOB等存储过大的数据类型，因为它们会占用更多的存储空间和I/O操作。

3. 减少查询次数

   使用JOIN查询：通过优化JOIN语句，避免使用多个单表查询。

1. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

  使用批量操作：合并多个相似的操作为一个更大的操作，减少多次查询和事务提交。

1. INSERT INTO table (column1, column2) VALUES (value1, value2), (value3, value4), ...;

4. 优化索引

 检查索引使用情况：通过EXPLAIN或其他性能分析工具，检查查询是否使用了适当的索引。

1.    EXPLAIN SELECT * FROM table WHERE column = 'value';

   - 删除不必要的索引：移除未使用或被其他索引覆盖的冗余索引，减少索引维护的开销。

5. 避免使用SELECT *

 明确列出所需的列：只选择需要的列，避免不必要的数据传输和处理。

1. SELECT column1, column2 FROM table;

1. 如何设计数据库表

1. 数据库设计规范
   • 根据业务模块拆分数据库（业务模块垂直分库）
   • 同一业务模块的表在一个数据库里
2. 表设计规范
   • 表名、字段名全部小写英文带下划线
   • 字段类型长度根据实际需求选择
   • 设计基础字段（主外键ID、时间、逻辑删除、版本）
   • 添加必要冗余字段
   • 添加必要索引（常用于查询的单字段或组合索引），单表索引建议控制在5个以内
   • 规划分表（单表超500万行或容量超2G才分表）