MySQL 核心知识整理【一】-海口c网

MySQL 核心知识整理【一】

article/2025/6/25 6:09:17

图片来自网络

一、MySQL存储引擎对比：InnoDB vs MyISAM

在使用MySQL时，选择合适的存储引擎对性能影响很大。最常见的两个引擎是 InnoDB 和 MyISAM，它们各自的设计目标不同，适用场景也不一样。

事务与数据安全性方面，InnoDB 支持事务处理，支持 COMMIT 和 ROLLBACK，适合电商、金融等要求数据一致性的场景。而 MyISAM 不支持事务，一旦写入错误无法回滚，更适合读多写少的业务。

外键约束方面，InnoDB 支持外键，可以定义表之间的约束，保证数据的完整性。MyISAM 不支持外键，所有的约束需要在应用层控制。

锁机制方面，InnoDB 支持行级锁和表锁，行锁可以大幅提高并发性能。MyISAM 只支持表锁，多个操作不能并发修改，容易造成阻塞。

全文索引方面，MyISAM 支持全文索引，适合文档类或搜索类应用。而 InnoDB 直到 MySQL 5.6 之后才开始支持全文索引。

适用建议：InnoDB 更适合高并发、大数据量、需要事务保障的场景，比如订单系统、交易系统。MyISAM 更适合读多写少、不涉及事务的查询系统，比如日志分析或CMS后台。

选择时需根据业务特性决定，不能单凭“速度快”或“支持事务”去判断哪个更好。

二、如何防止 SQL 注入

SQL 注入是应用程序中非常常见且危险的安全漏洞之一，攻击者可以通过构造恶意 SQL 语句，获取、篡改甚至删除数据库中的数据。避免这类问题的关键在于：不要直接拼接 SQL 语句，而是使用参数化方式传递变量。

以 Java 为例，使用 PreparedStatement 可以自动对输入进行转义，防止用户注入非法语句。正确的写法是：

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

而不是：

String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

后者非常危险，一旦输入类似 admin' OR '1'='1，整个逻辑就会被绕过。

在使用 MyBatis 等框架时，正确方式是使用 #{} 进行参数绑定，如：

<select id="getUser" resultType="User">SELECT * FROM users WHERE username = #{username}
</select>

不要使用 ${}，因为它会将参数直接拼接进 SQL。

核心原则是：变量永远不要直接写进 SQL 字符串里。无论使用什么语言或框架，都应该寻找它们所提供的“安全插值”机制。这不仅能提升代码可读性，也能极大增强应用的安全性。

三、怎样实现幂等

幂等，是指一个操作无论执行多少次，结果都保持一致，不会产生副作用。在后端开发中非常常见，尤其是涉及支付、下单、接口请求等场景。如果没有幂等保护，重复提交可能导致数据重复写入、订单重复创建等严重问题。

常见的幂等实现方式有以下三种：

1. 唯一索引（主键约束）

通过数据库的主键或唯一索引约束，来保证数据只写入一次。比如订单号、请求流水号等字段设置为唯一，一旦第二次写入相同数据，数据库会直接抛出异常。适用于插入类操作。

CREATE UNIQUE INDEX idx_order_sn ON orders(order_sn);

只要每次提交的订单号唯一，系统就能准确拒绝重复订单。

2. 乐观锁（版本号）

在更新数据时引入 version 字段，每次更新时要求版本号匹配，如果版本不一致则拒绝操作。这种方式适合控制并发更新，确保每次修改都基于上一次的结果。

UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 2;

3. Token机制 + Redis

客户端发起操作前，先从服务端获取一个唯一 token，执行操作时必须携带该 token，并通过 Redis 设置 token 的一次性消费规则。一旦被使用，就立即从 Redis 中删除，后续请求再带同样的 token 就会被拦截。

这种方式适合防止重复点击、重复提交等场景，特别是在高并发请求中非常有效。

四、一条 SQL 语句的执行流程

MySQL 执行一条 SQL 查询语句，背后其实是一个完整的流程，涉及多个组件协同工作。主要可以分为两层：Server 层 和 存储引擎层。

1. 建立连接

客户端连接 MySQL，连接器首先验证用户名和密码，并加载该用户的权限信息。连接成功后才能继续发送 SQL 请求。

2. 查询缓存（MySQL 8.0 已移除）

曾经 MySQL 会先查缓存，如果完全相同的语句执行过，就直接返回缓存结果，跳过后续步骤。但由于命中率低、维护成本高，MySQL 8.0 起已移除该功能。

3. 语法解析和语义分析

SQL 语句会先交给 解析器。这个阶段会检查 SQL 是否拼写正确、语法是否合法，确认涉及的表、字段是否存在。还会将 SQL 转换为内部的数据结构，便于后续处理。

4. 查询优化

经过语义分析的 SQL，会交给 优化器。优化器的任务是选择最佳的执行计划，比如判断用哪个索引、使用何种连接方式（如 nested loop、hash join）。最终目标是尽可能提高执行效率。

5. 权限检查

执行器在执行语句前，会再次验证当前用户是否有权限对相关表或字段进行操作。如果权限不足，直接返回错误。

6. 调用存储引擎执行

执行器将最终的执行计划交给对应的存储引擎（如 InnoDB）来完成具体的数据操作，并返回结果给客户端。