MySQL面试知识点Markdown整理
2025-04-16 20:55:32 0 举报AI智能生成
### MySQL面试知识点整理 这份文档精选了MySQL的核心面试知识点，旨在帮助求职者高效复习与准备数据库管理及优化相关的问题。内容涵盖了基础知识、查询优化、存储引擎、索引、事务与锁定等关键领域，帮助候选人夯实数据库理论基础，并掌握实际应用技术。 --- **文件类型**: `.md` **修饰语**: 精心筛选、高效复习、基础知识、查询优化、存储引擎、索引机制、事务处理 该Markdown文档面向准备参加面试的数据库工程师、数据分析师和相关IT职位的技术人员，内容的深度和广度经过精心设计，能够作为一份全面的参考材料使用。
作者其他创作
大纲/内容
一、基础概念
数据库定义
数据库是长期存储在计算机内、有组织、可共享的大量数据集合，能为多个用户提供服务，通过数据库管理系统（DBMS）进行管理。
关系型数据库特点
数据结构：以二维表形式组织数据，表由行和列构成，每一行代表一条记录，每一列代表一个字段。
数据操作：支持 SQL 语言，可方便地进行数据的增删改查操作。
数据完整性：通过主键、外键等约束保证数据的完整性和一致性。
事务支持：具备事务处理能力，确保数据操作的原子性、一致性、隔离性和持久性。
MySQL 简介
MySQL 是开源的关系型数据库管理系统，具有高性能、可靠性和易用性等特点，广泛应用于 Web 应用开发。
二、存储引擎
InnoDB
特点
支持事务处理，遵循 ACID 特性，确保数据的一致性和完整性。
支持外键约束，可维护表间的引用完整性。
采用聚簇索引，数据和索引存储在一起，提高查询效率。
支持行级锁和 MVCC（多版本并发控制），提高并发性能。
适用场景：适用于高并发、事务处理要求高的场景，如电商系统、金融系统等。
MyISAM
特点
不支持事务和外键，查询性能较高。
索引和数据是分开存储的，索引文件和数据文件分离。
支持表级锁，并发性能较低。
适用场景：适用于只读或读多写少的场景，如新闻网站、博客系统等。
Memory
特点
数据存储在内存中，读写速度极快。
支持哈希索引和 B - 树索引。
数据在服务器重启后丢失。
适用场景：适用于临时数据存储、缓存等场景，如会话数据、临时统计结果等。
三、数据类型
数值类型
整数类型
TINYINT：1 字节，范围 - 128 到 127（有符号）或 0 到 255（无符号）。
SMALLINT：2 字节，范围 - 32768 到 32767（有符号）或 0 到 65535（无符号）。
MEDIUMINT：3 字节，范围 - 8388608 到 8388607（有符号）或 0 到 16777215（无符号）。
INT：4 字节，范围 - 2147483648 到 2147483647（有符号）或 0 到 4294967295（无符号）。
BIGINT：8 字节，范围 - 9223372036854775808 到 9223372036854775807（有符号）或 0 到 18446744073709551615（无符号）。
浮点类型
FLOAT：4 字节，单精度浮点数，精度约为 7 位小数。
DOUBLE：8 字节，双精度浮点数，精度约为 15 位小数。
定点类型
DECIMAL：用于精确存储小数，可指定精度和小数位数，适用于财务计算等场景。
字符串类型
CHAR：固定长度字符串，长度范围为 0 到 255 字节，存储时会用空格填充到指定长度。
VARCHAR：可变长度字符串，长度范围为 0 到 65535 字节，存储时只占用实际长度的空间。
TEXT：用于存储大量文本数据，有 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 四种类型，存储范围从 255 字节到 4GB。
BLOB：用于存储二进制数据，有 TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 四种类型，存储范围从 255 字节到 4GB。
日期和时间类型
YEAR：1 字节，存储年份，范围为 1901 到 2155。
TIME：3 字节，存储时间，范围为 - 838:59:59 到 838:59:59。
DATE：3 字节，存储日期，格式为 'YYYY - MM - DD'。
DATETIME：8 字节，存储日期和时间，格式为 'YYYY - MM - DD HH:MM:SS'，范围为 1000 - 01 - 01 00:00:00 到 9999 - 12 - 31 23:59:59。
TIMESTAMP：4 字节，存储日期和时间，格式为 'YYYY - MM - DD HH:MM:SS'，范围为 1970 - 01 - 01 00:00:01 UTC 到 2038 - 01 - 19 03:14:07 UTC，会自动更新为当前时间。
四、索引
索引类型
B + 树索引
是 MySQL 最常用的索引类型，所有数据存储在叶子节点，非叶子节点只存储索引键和指向下一层节点的指针。
支持范围查询，查找效率为 O (logn)。
哈希索引
基于哈希表实现，通过哈希函数将索引键映射到哈希表的一个位置。
适用于等值查询，查找效率高，但不支持范围查询。
全文索引
用于对文本字段进行全文搜索，支持自然语言搜索和布尔搜索。
可在 CHAR、VARCHAR 和 TEXT 类型的字段上创建。
R - Tree 索引
用于空间数据类型的索引，如 GEOMETRY、POINT、LINESTRING 等。
可高效处理空间查询，如范围查询、邻接查询等。
索引创建与删除
创建索引：使用 CREATE INDEX 语句创建普通索引，使用 ALTER TABLE 语句创建主键索引、唯一索引和外键索引。
删除索引：使用 DROP INDEX 语句删除普通索引，使用 ALTER TABLE 语句删除主键索引、唯一索引和外键索引。
索引优化
选择合适的列创建索引：选择经常用于查询条件、连接条件和排序的列。
遵循最左前缀原则：对于复合索引，查询时要从左到右依次使用索引列。
避免在索引列上使用函数或进行运算：会导致索引失效。
注意索引的覆盖性：使查询的列都包含在索引中，避免回表查询。
定期维护索引：如重建索引、分析索引等，提高索引性能。
五、事务
事务特性（ACID）
原子性：事务中的操作要么全部执行，要么全部不执行，不可分割。
一致性：事务执行前后，数据库的状态保持一致，数据的完整性和约束条件得到满足。
隔离性：多个事务并发执行时，相互之间不受干扰，每个事务都感觉不到其他事务的存在。
持久性：事务提交后，对数据库的修改永久保存，即使系统崩溃也不会丢失。
事务隔离级别
读未提交（READ UNCOMMITTED）
允许一个事务读取另一个未提交事务的数据，可能导致脏读、不可重复读和幻读。
隔离级别最低，并发性能最高。
读已提交（READ COMMITTED）
只能读取已提交事务的数据，避免了脏读，但仍可能出现不可重复读和幻读。
是大多数数据库的默认隔离级别。
可重复读（REPEATABLE READ）
在一个事务内，多次读取相同数据时，结果保持一致，避免了脏读和不可重复读，但可能存在幻读。
是 MySQL InnoDB 存储引擎的默认隔离级别。
串行化（SERIALIZABLE）
事务串行执行，完全避免了并发问题，但性能最低。
会对事务中的读操作加共享锁，写操作加排他锁。
事务的实现与控制
事务的开始：使用 START TRANSACTION 或 BEGIN 语句开始一个事务。
事务的提交：使用 COMMIT 语句提交事务，将事务中的所有操作永久保存到数据库中。
事务的回滚：使用 ROLLBACK 语句回滚事务，撤销事务中的所有操作。
保存点：使用 SAVEPOINT 语句设置保存点，可在事务中部分回滚到指定的保存点。
六、锁机制
（一）锁的类型
表锁
对整个表进行锁定，分为表共享读锁（共享锁）和表独占写锁（排他锁）。
开销小，加锁快，但并发度低。
行锁
只对需要操作的行进行锁定，分为行共享锁（共享锁）和行独占锁（排他锁）。
开销大，加锁慢，但并发度高。
间隙锁
在可重复读隔离级别下，为了防止幻读，会在索引记录之间的间隙加锁。
会影响并发性能，增加死锁的可能性。
（二）死锁
定义：两个或多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，导致事务无法继续执行。
产生原因：事务之间的资源竞争和加锁顺序不一致。
预防措施：设置合理的事务隔离级别，优化事务逻辑，减少事务持有锁的时间，使用索引避免全表扫描等。
检测与处理：MySQL 会自动检测死锁，并选择一个事务进行回滚，以解除死锁。
七、SQL 语句
查询语句（SELECT）
基本语法：SELECT [DISTINCT] 列名 1, 列名 2,... FROM 表名 [WHERE 条件][GROUP BY 分组列][HAVING 分组条件][ORDER BY 排序列 [ASC|DESC]][LIMIT 偏移量，行数]。
子查询：在一个查询语句中嵌套另一个查询语句，可用于条件过滤、结果集比较等。
连接查询
内连接（INNER JOIN）：只返回两个表中匹配的记录。
左连接（LEFT JOIN）：返回左表中的所有记录，以及右表中匹配的记录。
右连接（RIGHT JOIN）：返回右表中的所有记录，以及左表中匹配的记录。
全连接（FULL JOIN）：返回两个表中的所有记录。
插入语句（INSERT）
插入单条记录：INSERT INTO 表名 (列名 1, 列名 2,...) VALUES (值 1, 值 2,...)。
插入多条记录：INSERT INTO 表名 (列名 1, 列名 2,...) VALUES (值 1, 值 2,...),(值 3, 值 4,...),...。
从其他表插入数据：INSERT INTO 表名 1 (列名 1, 列名 2,...) SELECT 列名 1, 列名 2,... FROM 表名 2 [WHERE 条件]。
更新语句（UPDATE）
基本语法：UPDATE 表名 SET 列名 1 = 值 1, 列名 2 = 值 2,... [WHERE 条件]。
批量更新：可通过子查询或连接查询来批量更新数据。
删除语句（DELETE）
基本语法：DELETE FROM 表名 [WHERE 条件]。
TRUNCATE TABLE：用于快速删除表中的所有数据，与 DELETE 语句的区别在于，TRUNCATE 是直接删除表数据并重置自增长列，不可回滚，而 DELETE 是逐行删除，可回滚。
八、数据库设计
范式
第一范式（1NF）：确保每列的原子性，即每列的数据不可再分。
第二范式（2NF）：在第一范式的基础上，要求非主键列完全依赖主键，消除部分依赖。
第三范式（3NF）：在第二范式的基础上，要求非主键列之间不存在传递依赖。
反范式化
概念：为了提高查询性能，适当违反范式规则，增加冗余数据。
适用场景：当查询性能成为瓶颈，且写操作相对较少时，可考虑反范式化。
数据库设计流程
需求分析：明确数据库的功能需求和性能需求。
概念设计：绘制 E - R 图，描述实体、属性和关系。
逻辑设计：将 E - R 图转换为关系模型，设计表结构和约束。
物理设计：选择合适的存储引擎、索引和数据类型，优化数据库性能。
实施与维护：创建数据库和表，导入数据，进行日常维护和优化。
九、性能优化
索引优化
索引分析：使用 EXPLAIN 语句分析查询语句的执行计划，查看索引使用情况。
索引创建：根据查询需求，为经常用于查询条件、连接条件和排序的列创建索引。
索引删除：删除不再使用的索引，避免索引过多影响写性能。
查询优化
避免全表扫描：使用索引、合理使用连接查询、避免在索引列上使用函数或进行运算。
优化子查询：尽量将子查询转换为连接查询，提高查询效率。
分页查询优化：对于大数据量的分页查询，可使用覆盖索引、记录上次查询的最大 ID 等方法。
表结构优化
选择合适的数据类型：根据数据范围和精度要求，选择占用空间小的数据类型。
避免使用大字段：如 TEXT、BLOB 类型，可将其存储在文件系统中，只在数据库中存储文件路径。
合理设计表的字段和索引：避免字段过多、索引过多或过少。
服务器配置优化
调整内存参数：如 innodb_buffer_pool_size、key_buffer_size 等，提高数据库的缓存命中率。
调整线程参数：如 max_connections、thread_cache_size 等，优化数据库的并发处理能力。
调整日志参数：如 binlog_format、sync_binlog 等，平衡日志记录和性能。
十、主从复制
原理
主服务器将数据库的更改记录到二进制日志（binlog）中。
从服务器通过 I/O 线程连接到主服务器，读取主服务器的 binlog，并将其存储到中继日志（relay log）中。
从服务器的 SQL 线程读取中继日志中的内容，并将其应用到自己的数据库中，实现数据同步。
作用
数据冗余备份：防止主服务器数据丢失。
分担读压力：将读请求分发到从服务器，提高系统的并发处理能力。
提高系统的可用性和扩展性：当主服务器出现故障时，可快速切换到从服务器。
配置步骤
主服务器配置：开启二进制日志，设置唯一的服务器 ID，创建用于复制的用户。
从服务器配置：设置唯一的服务器 ID，配置主服务器的连接信息，启动复制进程。
十一、分布式数据库
分库分表
水平分库分表
水平分库：将不同的表分布到不同的数据库中，按照一定的规则（如时间、业务类型等）将数据划分到多个数据库。
水平分表：将表中的数据按照一定的规则（如哈希、范围等）分布到多个表中。
垂直分库分表
垂直分库：将同一表的数据按照业务功能或字段的使用频率，分布到不同的数据库中。
垂直分表：将表中的列按照字段的使用频率、数据类型等，分布到多个表中。
分布式事务
两阶段提交（2PC）
准备阶段：协调者向所有参与者发送准备请求，参与者执行事务操作并将结果反馈给
提交阶段：如果所有参与者都准备好，协调者发送提交请求，参与者提交事务；否则，协调者发送回滚请求，参与者回滚事务。
优缺点：优点是实现简单，保证强一致性；缺点是存在单点故障问题，协调者故障可能导致整个事务失败，且性能较低，事务执行期间需要锁定资源。
三阶段提交（3PC）
询问阶段：协调者向所有参与者发送询问请求，参与者评估是否可以执行事务。
准备阶段：如果所有参与者都可以执行事务，协调者发送准备请求，参与者执行事务操作并将结果反馈给协调者。
提交阶段：如果所有参与者都准备好，协调者发送提交请求，参与者提交事务；否则，协调者发送回滚请求，参与者回滚事务。
优缺点：相比 2PC，3PC 降低了参与者的阻塞范围，一定程度上减少了单点故障的影响；但依然存在协调者单点故障问题，并且引入询问阶段增加了通信开销。
TCC(Try-Confirm-Cancel)
Try阶段：尝试执行事务操作，预留资源。此阶段主要是对业务系统做检测及资源预留。
Confirm阶段：如果 Try 阶段成功，确认执行事务操作，释放预留资源。该阶段做的事情是对 Try 阶段预留的资源进行确认提交。
Cancel阶段：如果 Try 阶段失败，取消执行事务操作，释放预留资源。即对 Try 阶段预留的资源进行释放。
优缺点：优点是可以实现高并发场景下的分布式事务，性能较高；缺点是开发成本高，需要业务系统提供 Try、Confirm 和 Cancel 三个接口，并且需要处理幂等性、空回滚、悬挂等问题。
0 条评论
下一页
