MySQL 高频面试要点梳理

未分类 15小时前程序员胖胖胖虎阿

4 0 0

MySQL 基础概念

1. 何谓MySQL？其具备哪些特性？

MySQL是一款开源的关系型数据库管理系统，由瑞典的MySQL AB公司研发，如今归属于Oracle公司。

其主要特性如下：
- 开源且免费（社区版）
- 运行性能出色，速度较快
- 支持多用户、多线程操作
- 可跨平台运行（涵盖Windows、Linux、Mac等系统）
- 能与多种编程语言进行接口适配
- 遵循标准的SQL数据语言规范
- 提供事务支持以及外键约束功能
- 可应对大型数据库的存储需求
- 具备多种存储引擎可供选择
- 安全性与连接性良好

2. MySQL中的存储引擎有哪些？它们存在何种区别？

常见的存储引擎包括：
- InnoDB：支持事务、行级锁和外键约束，是MySQL 5.5版本以后的默认存储引擎
- MyISAM：不支持事务和行级锁，但查询速度较快
- MEMORY：数据存储于内存中，速度快但关机后数据会丢失
- ARCHIVE：适用于存储大量归档数据
- CSV：以CSV格式存储数据
- BLACKHOLE：接收数据却不进行存储

它们的对比情况如下：

特性	InnoDB	MyISAM	MEMORY
事务支持	是	否	否
锁机制	行级锁	表级锁	表级锁
外键支持	是	否	否
崩溃恢复	是	否	否
全文索引	是（5.6+）	是	否
存储限制	64TB	256TB	内存大小

3. InnoDB与MyISAM的主要区别是什么？

事务支持：InnoDB支持ACID事务，MyISAM不支持事务。
锁级别：InnoDB采用行级锁，MyISAM采用表级锁。
外键约束：InnoDB支持外键，MyISAM不支持外键。
崩溃恢复：InnoDB崩溃后有安全恢复能力，MyISAM崩溃后数据易损坏。
性能特点：InnoDB写操作性能较好，MyISAM读操作性能较好。
存储结构：InnoDB是聚簇索引，MyISAM是非聚簇索引。

4. 什么是事务？MySQL如何支持事务？

事务定义：事务是一组原子性的SQL查询，要么全部执行成功，要么全部失败回滚。

MySQL对事务的支持：通过InnoDB等支持事务的存储引擎来实现。默认处于自动提交(auto-commit)模式，可使用 SET autocommit=0 来关闭自动提交。其操作示例如下：

START TRANSACTION;
-- 此处放置SQL语句
COMMIT;  -- 提交事务
-- 或者
ROLLBACK; -- 回滚事务

ACID原则详解

ACID原则是事务的四个基本特性：

1. Atomicity（原子性）

事务是不可分割的工作单元，事务中的操作要么全部完成，要么全部不完成，失败时会自动回滚所有操作。

2. Consistency（一致性）

事务执行前后，数据库从一个一致状态转变为另一个一致状态，不会破坏数据库的完整性约束，例如转账前后总金额保持不变。

3. Isolation（隔离性）

多个事务并发执行时互不干扰，通过隔离级别来控制，隔离级别包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable），防止出现脏读、不可重复读和幻读问题。

4. Durability（持久性）

事务提交后，修改将永久保存，即使系统崩溃，数据也不会丢失，通过事务日志和恢复机制来保证。

MySQL 数据库设计

1. 主键、外键和索引分别是什么？

主键（Primary Key）

定义：是唯一标识表中每行记录的列或列组合，不允许NULL值，每个表只能有一个主键。
特点：保证实体完整性，自动创建聚集索引（在InnoDB中），常用自增整数（AUTO_INCREMENT）作为主键。

外键（Foreign Key）

定义：是建立两个表数据之间关联的字段，引用另一个表的主键，保证引用完整性。
特点：防止无效数据插入，可设置级联操作（CASCADE），InnoDB支持，MyISAM不支持。

索引（Index）

定义：是提高查询性能的数据结构，类似书籍的目录。
类型：包括普通索引、唯一索引、主键索引、组合索引、全文索引等。

2. 数据库范式是什么？通常设计到第几范式？

数据库范式定义：是规范化数据库设计的指导原则，用于减少数据冗余，提高数据一致性。

主要范式：
- 1NF：属性具有原子性，无重复组，需消除重复列。
- 2NF：满足1NF，且非主属性完全依赖主键，需消除部分依赖。
- 3NF：满足2NF，且消除传递依赖，需消除非主属性间的依赖。
- BCNF：是更强的3NF，需消除主属性对候选键的部分依赖。

设计建议：通常设计到第三范式(3NF)，根据实际性能需求考虑反范式化，关联查询多的场景可适当降低范式级别。

3. 如何优化数据库表结构设计？

结构优化：
- 选择合适的数据类型，比如用INT存储数字，用DATETIME/TIMESTAMP存储时间。
- 进行规范化设计，遵循适当的范式级别，合理拆分大表。
- 保持命名规范，使用有意义的表名和字段名，保持命名风格一致。

索引优化：
- 为常用查询条件创建索引，避免过度索引（影响写性能），使用组合索引时注意最左前缀原则。

其他优化：
- 适当使用垂直/水平分表，考虑使用分区表，为常用查询创建视图。

4. 什么是反范式化设计？何时使用它？

反范式化设计定义：是故意增加冗余数据的设计方法，违反范式原则以提高查询性能。

使用场景：
- 读密集场景，如报表系统、数据分析应用。
- 性能关键路径，如高频查询的表、需要快速响应的核心业务。
- 特定技术需求，如数据仓库、OLAP系统。

实现方式：增加冗余字段、使用预计算字段、创建汇总表。

注意事项：需要额外维护数据一致性，适合读多写少的场景，需权衡查询性能与数据一致性。

MySQL SQL查询

1. 解释SELECT语句的执行顺序

SQL查询逻辑执行顺序：
1. FROM和JOIN：确定数据来源表。
2. WHERE：筛选符合条件的行。
3. GROUP BY：对数据进行分组。
4. HAVING：筛选分组后的数据。
5. SELECT：选择要返回的列。
6. DISTINCT：去除重复行。
7. ORDER BY：对结果排序。
8. LIMIT/OFFSET：限制返回行数。

实际示例：

SELECT DISTINCT column1, COUNT(*) 
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE condition = 'value'
GROUP BY column1
HAVING COUNT(*) > 1
ORDER BY column1
LIMIT 10;

2. JOIN有哪些类型？它们有什么区别？

JOIN类型	语法	描述	结果
INNER JOIN	`A INNER JOIN B ON A.id=B.id`	只返回两表中匹配的行	两表的交集
LEFT JOIN	`A LEFT JOIN B ON A.id=B.id`	返回左表所有行+匹配的右表行	左表全集+匹配部分
RIGHT JOIN	`A RIGHT JOIN B ON A.id=B.id`	返回右表所有行+匹配的左表行	右表全集+匹配部分
FULL JOIN	`A FULL JOIN B ON A.id=B.id`	返回两表所有行（MySQL用UNION实现）	两表的并集
CROSS JOIN	`A CROSS JOIN B`	返回两表的笛卡尔积	所有可能的组合

3. 什么是子查询？有哪些类型的子查询？

子查询定义：嵌套在另一个查询中的SELECT语句。

子查询类型：
- WHERE子句中的子查询：

sql SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)

FROM子句中的子查询（派生表）：

sql SELECT * FROM (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) as user_orders WHERE order_count > 5

SELECT子句中的子查询（标量子查询）：

sql SELECT product_name, (SELECT COUNT(*) FROM orders WHERE product_id = p.id) as order_count FROM products p

EXISTS/NOT EXISTS子查询：

sql SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)

IN/NOT IN子查询：

sql SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY')

4. 如何优化SQL查询性能？

索引优化：
- 合理创建索引，为WHERE、JOIN、ORDER BY字段建立索引，组合索引字段顺序遵循高频查询字段在前、高选择性字段在前的原则。

sql -- 创建组合索引示例 CREATE INDEX idx_user_status ON users(status, register_date);

避免索引失效场景，如避免对索引列使用函数或运算、避免使用!=或<>操作符、避免使用前导通配符LIKE查询等。

```sql
-- 索引失效的反例
SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

-- 优化后的正例
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';
```

查询优化：
- 避免全表扫描，使用LIMIT限制返回行数，避免SELECT *，只查询需要的列。

```sql
-- 全表扫描的反例
SELECT * FROM order_details;

-- 优化后的正例
SELECT order_id, product_id, quantity FROM order_details WHERE order_id = 1001;
```

JOIN优化，确保JOIN字段有索引，遵循小表驱动大表的原则。

sql -- JOIN优化示例 SELECT * FROM small_table s JOIN large_table l ON s.id = l.id;

分页优化：

```sql
-- 低效的分页查询
SELECT * FROM large_table LIMIT 100000, 10;

-- 高效的分页查询(使用覆盖索引)
SELECT * FROM large_table WHERE id > 100000 ORDER BY id LIMIT 10;
```

5. EXPLAIN命令是做什么的？如何使用它？

EXPLAIN命令作用：分析SQL查询的执行计划，查看MySQL如何使用索引，识别查询性能瓶颈。

基本使用方法：

```sql
-- 基本语法
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 查看详细执行计划(MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 100;
```

关键输出字段：
- type：访问类型，从好到差依次为system > const > eq_ref > ref > range > index > ALL。
- key：实际使用的索引。
- rows：预估需要检查的行数。
- Extra：额外信息，如Using index表示使用覆盖索引，Using temporary表示使用临时表，Using filesort表示需要额外排序。

使用案例：

```sql
-- 分析查询执行计划
EXPLAIN SELECT u.name, o.order_no
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 AND o.amount > 1000
ORDER BY o.create_time DESC;

-- 优化后添加索引
ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);
ALTER TABLE users ADD INDEX idx_status (status);
```

MySQL索引

1. MySQL中有哪些类型的索引？

主要索引类型包括：
- 普通索引：最基本的索引类型，无唯一性限制。
- 唯一索引：索引列的值必须唯一，允许NULL值。
- 主键索引：特殊的唯一索引，不允许NULL值。
- 组合索引：多个列组合创建的索引。
- 全文索引：用于全文搜索。
- 空间索引：用于地理空间数据类型。
- 前缀索引：对字符串前几个字符建立的索引。

sql -- 创建各类索引示例 CREATE INDEX idx_name ON users(name); -- 普通索引 CREATE UNIQUE INDEX idx_email ON users(email); -- 唯一索引 ALTER TABLE users ADD PRIMARY KEY (id); -- 主键索引 CREATE INDEX idx_name_age ON users(name, age); -- 组合索引

2. 什么是聚簇索引和非聚簇索引？

聚簇索引(Clustered Index)：
- 索引和数据存储在一起。
- InnoDB的主键索引就是聚簇索引。
- 一个表只能有一个聚簇索引。
- 物理存储顺序与索引顺序一致。

非聚簇索引(Non-clustered Index)：
- 索引和数据分开存储。
- MyISAM使用的都是非聚簇索引。
- 一个表可以有多个非聚簇索引。
- 通过指针指向实际数据位置。

3. 如何创建高效的索引？

高效索引创建原则：
- 选择区分度高的列建立索引。
- 频繁作为查询条件的列应建立索引。
- 组合索引遵循最左前缀原则。
- 避免创建过多索引（影响写性能）。
- 尽量使用数据量小的数据类型作为索引。

```sql
-- 高效组合索引示例
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 低效索引示例(区分度低)
CREATE INDEX idx_gender ON users(gender);
```

4. 什么情况下索引会失效？

索引失效常见场景：
- 对索引列使用函数或运算。
- 使用!=或<>操作符。
- 使用前导通配符的LIKE查询。
- 隐式类型转换。
- OR条件未全部使用索引。
- 不符合最左前缀原则的组合索引。

sql -- 索引失效示例 SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 使用函数 SELECT * FROM products WHERE name LIKE '%apple%'; -- 前导通配符 SELECT * FROM orders WHERE amount+100 > 500; -- 对列运算