MySQL数据库基础

沈二到不行
156 阅读12分钟

主题

整理一些数据库层面的概念性的东西,很多时候搞成了了习惯,但忽略了一些词条，导致沟通或表述上的差异，在此以作整理,便于后续的回顾

数据库三范式

  • 1NF：确保表格中的每一列都是单一值，避免多值字段。
  • 2NF：确保非主键字段完全依赖于主键，而不是主键的一部分。
  • 3NF：确保非主键字段之间没有依赖关系，只依赖于主键。

第一范式 (1NF)：确保“表格化”

  • 核心思想: 每个字段的值必须是不可再分的基本值。
  • 通俗解释: 表格中的每一列只能存放单一的数据，不能包含列表或多个值。
  • 举例:
    • 错误示例：学生表 中有一列 课程，存储了多个课程名称（如 "数学, 英语, 物理"）。
    • 正确做法：将 课程 分解为单独的行，或者创建一个独立的 课程表 来存储每个学生的课程信息。

第二范式 (2NF)：消除部分依赖

  • 核心思想: 在满足 1NF 的基础上，确保表中的非主键字段完全依赖于主键，而不是主键的一部分。
  • 通俗解释: 如果主键是由多个字段组成的复合主键，那么所有非主键字段必须依赖于整个主键，而不仅仅是主键的一部分。
  • 举例:
    • 错误示例：订单详情表 中有 (订单ID, 商品ID, 商品名称)，其中 (订单ID, 商品ID) 是主键，但 商品名称 只依赖于 商品ID，而不是整个主键。
    • 正确做法：将 商品名称 移到单独的 商品表 中，通过 商品ID 进行关联。

第三范式 (3NF)：消除传递依赖

  • 核心思想: 在满足 2NF 的基础上，确保表中的非主键字段之间没有依赖关系。
  • 通俗解释: 非主键字段不能依赖于其他非主键字段，只能直接依赖于主键。
  • 举例:
    • 错误示例：员工表 中有 (员工ID, 部门ID, 部门名称)，其中 部门名称 依赖于 部门ID，而不是直接依赖于 员工ID。
    • 正确做法：将 部门名称 移到单独的 部门表 中，通过 部门ID 进行关联。

事务隔离级别

事务隔离级别是数据库系统中用于控制多个事务并发执行时数据一致性和可见性的机制。以下是 MySQL 数据库中常见的四种事务隔离级别的总结：

1. 未提交读 (Read Uncommitted)

  • 定义: 允许一个事务读取另一个事务尚未提交的数据。
  • 特点:
    • 可能会出现“脏读”（Dirty Read），即读取到其他事务未提交的修改数据。
    • 性能最高，但数据一致性最差。
  • 适用场景: 对数据一致性要求不高，但对性能要求较高的场景。
  • MySQL 默认支持: 不支持此级别（最低级别为“读已提交”）。

2. 读已提交 (Read Committed)

  • 定义: 一个事务只能读取到另一个事务已经提交的数据。
  • 特点:
    • 解决了“脏读”问题。
    • 可能会出现“不可重复读”（Non-Repeatable Read），即同一事务中多次读取同一数据结果可能不同。
  • 适用场景: 需要避免脏读，但可以容忍不可重复读的场景。
  • MySQL 默认支持: 支持此级别。

3. 可重复读 (Repeatable Read)

  • 定义: 在同一个事务中，多次读取同一数据的结果是一致的。
  • 特点:
    • 解决了“不可重复读”问题。
    • 可能会出现“幻读”（Phantom Read），即在同一事务中，两次查询之间插入或删除了某些符合条件的行。
  • 适用场景: 需要保证事务内数据的一致性，但可以容忍幻读的场景。
  • MySQL 默认支持: 是 InnoDB 存储引擎的默认隔离级别。

4. 串行化 (Serializable)

  • 定义: 强制事务串行执行，避免所有并发问题。
  • 特点:
    • 完全避免了脏读、不可重复读和幻读。
    • 性能最低，因为事务需要排队执行，可能导致锁等待和死锁。
  • 适用场景: 对数据一致性要求极高，且并发度较低的场景。
  • MySQL 默认支持: 支持此级别。

隔离级别对比

隔离级别脏读不可重复读幻读性能
Read Uncommitted✅✅✅最高
Read Committed❌✅✅较高
Repeatable Read❌❌✅中等
Serializable❌❌❌最低

MySQL 默认隔离级别

  • InnoDB 存储引擎的默认隔离级别是 Repeatable Read。
  • 可以通过以下命令查看当前隔离级别： 
    SELECT @@transaction_isolation;

索引类型

按照应用维度划分

1. 主键索引 (Primary Key)

  • 核心原理: 唯一标识表中每一行数据的索引，不允许 NULL 值。
  • 适应场景: 需要唯一标识每条记录的场景，例如用户 ID 或订单编号。
  • 性能特征: 查询速度快，插入和更新时需要维护索引树。
  • 最佳实践: 主键应选择短且稳定的字段，避免频繁更新。
  • 示例: CREATE TABLE users (id INT PRIMARY KEY);
  • 失效场景: 主键字段允许 NULL 值或重复值时失效。
  • 数据库引擎支持: InnoDB（强制要求主键），MyISAM（可选）。

2. 唯一索引 (Unique Index)

  • 核心原理: 确保索引列中的所有值必须唯一，允许一个 NULL 值。
  • 适应场景: 需要确保某列或某几列组合值唯一的场景，例如邮箱地址或电话号码。
  • 性能特征: 查询性能较高，但插入和更新时需额外检查唯一性约束。
  • 最佳实践: 避免在高并发写入场景下使用唯一索引，可能导致锁冲突。
  • 示例: CREATE UNIQUE INDEX idx_email ON users(email);
  • 失效场景: 插入重复值时索引失效，导致操作失败。
  • 数据库引擎支持: InnoDB 和 MyISAM。

3. 普通索引 (Normal Index)

  • 核心原理: 对指定列创建的非唯一索引，允许重复值和 NULL 值。
  • 适应场景: 提高查询速度的通用场景，例如按名称或类别筛选数据。
  • 性能特征: 查询性能较好，对插入和更新影响较小。
  • 最佳实践: 避免为低基数列（如性别）创建索引，效果有限。
  • 示例: CREATE INDEX idx_name ON users(name);
  • 失效场景: 查询条件未命中索引列时失效。
  • 数据库引擎支持: InnoDB 和 MyISAM。

4. 复合索引 (Composite Index)

  • 核心原理: 在多个列上创建的联合索引，遵循最左前缀原则。
  • 适应场景: 涉及多列联合查询的场景，例如按城市和年龄筛选用户。
  • 性能特征: 能有效优化多列查询，但索引文件较大。
  • 最佳实践: 尽量将过滤性最强的列放在索引前列。
  • 示例: CREATE INDEX idx_city_age ON users(city, age);
  • 失效场景: 查询条件未包含索引最左列时失效。
  • 数据库引擎支持: InnoDB 和 MyISAM。

5. 全文索引 (Full-text Index)

  • 核心原理: 支持全文搜索功能，适用于文本字段的模糊匹配。
  • 适应场景: 搜索文章内容、评论等文本数据的场景。
  • 性能特征: 搜索效率高，但索引构建和更新开销较大。
  • 最佳实践: 配合布尔模式或自然语言模式使用，提高搜索精度。
  • 示例: CREATE FULLTEXT INDEX idx_content ON articles(content);
  • 失效场景: 查询条件未使用 MATCH AGAINST 函数时失效。
  • 数据库引擎支持: InnoDB 和 MyISAM（仅限某些版本支持）。

6. 空间索引 (Spatial Index)

  • 核心原理: 专为地理空间数据设计，支持范围查询和邻近查询。
  • 适应场景: GIS 应用场景，例如查找某个区域内的地点。
  • 性能特征: 查询性能优越，但索引维护成本较高。
  • 最佳实践: 使用前需确保数据符合地理坐标格式。
  • 示例: CREATE SPATIAL INDEX idx_location ON locations(geo_point);
  • 失效场景: 查询条件未涉及空间数据时失效。
  • 数据库引擎支持: MyISAM（默认支持），InnoDB（自 MySQL 5.7 起支持）。

7. 前缀索引 (Prefix Index)

  • 核心原理: 对字符串字段的部分字符创建索引，节省存储空间。
  • 适应场景: 字符串字段较长且无需全文索引的场景。
  • 性能特征: 存储空间小，但可能降低查询精度。
  • 最佳实践: 根据实际查询需求选择合适的前缀长度。
  • 示例: CREATE INDEX idx_title_prefix ON articles(title(10));
  • 失效场景: 查询条件未匹配到前缀部分时失效。
  • 数据库引擎支持: InnoDB 和 MyISAM。

8. 降序索引 (Descending Index)

  • 核心原理: 按降序顺序存储索引数据。
  • 适应场景: 需要频繁进行降序排序的场景。
  • 性能特征: 优化降序查询性能，但仍需维护索引。
  • 最佳实践: 结合升序索引一起使用以满足不同查询需求。
  • 示例: CREATE INDEX idx_salary_desc ON employees(salary DESC);
  • 失效场景: 查询条件未匹配索引顺序时失效。
  • 数据库引擎支持: InnoDB（自 MySQL 8.0 起支持）。

存储方式维度

1. 聚簇索引 (Clustered Index)

  • 核心原理: 数据行与索引存储在一起，通常为主键索引。
  • 适应场景: 需要快速访问主键相关数据的场景。
  • 性能特征: 查询性能高，但更新或删除操作较慢。
  • 最佳实践: 主键应尽量选择连续递增的字段。
  • 示例: 自动创建于主键字段。
  • 失效场景: 非主键查询时失效。
  • 数据库引擎支持: InnoDB（默认支持）。

2. 非聚簇索引 (Non-clustered Index)

  • 核心原理: 索引与数据行分开存储，通过指针指向数据行。
  • 适应场景: 辅助查询优化，尤其是非主键字段。
  • 性能特征: 查询性能良好，但需额外的指针跳转。
  • 最佳实践: 避免过多创建非聚簇索引，增加维护成本。
  • 示例: CREATE INDEX idx_phone ON users(phone);
  • 失效场景: 查询条件未命中索引列时失效。
  • 数据库引擎支持: InnoDB 和 MyISAM。

避免索引失效

  1. 查询条件不合理：如使用函数、表达式、通配符等。
  2. 数据类型不匹配：隐式类型转换导致索引失效。
  3. 复合索引使用不当：未遵循最左前缀原则。
  4. 连接和排序问题：连接条件或排序条件未命中索引。

1. 使用函数或表达式操作索引列

  • 描述: 在查询条件中对索引列使用函数或表达式时，MySQL 无法直接利用索引。
  • 示例: 
    SELECT * FROM users WHERE YEAR(birth_date) = 1990; -- 索引失效
  • 原因: 数据库引擎需要对每一行数据计算 YEAR(birth_date) 的值，无法直接使用索引。
  • 解决方法: 将函数移到查询外部，或者改写为范围查询： 
    SELECT * FROM users WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';

2. 使用不匹配的数据类型进行比较

  • 描述: 查询条件中的数据类型与索引列的类型不一致时，可能导致隐式类型转换，从而导致索引失效。
  • 示例: 
    SELECT * FROM users WHERE id = '1'; -- 如果 id 是 INT 类型，索引可能失效
  • 原因: 隐式类型转换会阻止索引的使用。
  • 解决方法: 确保查询条件中的数据类型与索引列的数据类型一致： 
    SELECT * FROM users WHERE id = 1;

3. 使用 LIKE 模糊查询时前缀通配符

  • 描述: 当 LIKE 查询以 % 开头时，索引无法被有效利用。
  • 示例: 
    SELECT * FROM users WHERE name LIKE '%John'; -- 索引失效
  • 原因: 前缀通配符会导致数据库引擎无法快速定位匹配的索引值。
  • 解决方法: 尽量避免前缀通配符，或者使用全文索引（Full-text Index）处理模糊查询。

4. 使用 OR 条件且部分条件未命中索引

  • 描述: 在 OR 条件中，如果部分条件未命中索引，则整个查询可能无法使用索引。
  • 示例: 
    SELECT * FROM users WHERE id = 1 OR name = 'John'; -- 如果 name 列未建立索引，索引失效
  • 原因: 数据库引擎无法同时优化多个条件。
  • 解决方法: 使用 UNION 分开查询，确保每个子查询都能命中索引： 
    SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE name = 'John';

5. 使用 IS NULL 或 IS NOT NULL 判断非索引列

  • 描述: 对非索引列执行 IS NULL 或 IS NOT NULL 判断时，索引无法生效。
  • 示例: 
    SELECT * FROM users WHERE email IS NULL; -- 如果 email 列未建立索引，索引失效
  • 原因: 数据库引擎需要扫描全表来判断 NULL 值。
  • 解决方法: 为相关列创建索引。

6. 使用 != 或 <=> 进行不等值比较

  • 描述: 不等值比较（如 != 或 <=>）通常无法利用索引。
  • 示例: 
    SELECT * FROM users WHERE age != 30; -- 索引失效
  • 原因: 不等值比较需要扫描所有不符合条件的行。
  • 解决方法: 改写查询逻辑，尽量避免不等值比较。

7. 索引列上有隐式类型转换

  • 描述: 当索引列参与隐式类型转换时，索引可能失效。
  • 示例: 
    SELECT * FROM users WHERE id = '1'; -- 如果 id 是 INT 类型，隐式转换导致索引失效
  • 原因: 隐式类型转换会阻止索引的使用。
  • 解决方法: 确保查询条件的数据类型与索引列一致。

8. 复合索引未遵循最左前缀原则

  • 描述: 在复合索引中，查询条件未包含索引的最左列时，索引失效。
  • 示例: 
    CREATE INDEX idx_city_age ON users(city, age);
SELECT * FROM users WHERE age = 25; -- 索引失效
  • 原因: 复合索引要求查询条件从最左列开始匹配。
  • 解决方法: 确保查询条件包含复合索引的最左列： 
    SELECT * FROM users WHERE city = 'New York' AND age = 25;

9. 使用 LIMIT 但未配合排序

  • 描述: 单独使用 LIMIT 时，如果没有排序条件，MySQL 可能会选择全表扫描。
  • 示例: 
    SELECT * FROM users LIMIT 10; -- 索引可能失效
  • 原因: 数据库引擎无法确定哪些行满足 LIMIT 条件。
  • 解决方法: 结合 ORDER BY 使用索引列进行排序： 
    SELECT * FROM users ORDER BY id LIMIT 10;

10. 表连接时未正确使用索引

  • 描述: 在多表连接查询中，如果连接条件未命中索引，可能导致索引失效。
  • 示例: 
    SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name = 'John'; -- 如果 user_id 或 id 未建索引，索引失效
  • 原因: 数据库引擎需要扫描全表来完成连接操作。
  • 解决方法: 确保连接条件中的列均已建立索引。
avatar
文章
阅读
粉丝