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