高性能学习笔记
锁
按读写划分
共享锁 (Shared Locks, S Lock / 读锁)
- 获取方式：
SELECT ... LOCK IN SHARE MODE; - 特点： 多个事务可以对同一行数据加S锁（共享）。S锁会阻塞其他事务对同一行加排他锁 (X锁)，但不阻塞其他事务加S锁或进行普通
SELECT（快照读）。
- 获取方式：
排他锁 (Exclusive Locks, X Lock / 写锁)
- 获取方式：
SELECT ... FOR UPDATE;、UPDATE、DELETE、INSERT（隐式）。 - 特点： 一个事务对一行数据加X锁后，其他任何事务都不能再对该行加S锁或X锁，直到该事务释放锁。X锁是互斥的。
- 获取方式：
按颗粒度划分
全局锁 (Global Lock)
- 作用范围： 整个MySQL实例的所有数据库、所有表。
- 实现方式：
FLUSH TABLES WITH READ LOCK (FTWRL)。 - 特点： 非常粗粒度，会阻塞所有对表的写操作（DML、DDL），读操作可以执行。常用于做全库逻辑备份（mysqldump在不加
--single-transaction时使用），但会严重影响业务。 - 影响： DDL语句（如
ALTER TABLE）即使是对表加读锁也会被全局读锁阻塞。
表级锁 (Table-level Locks)
- 作用范围： 整个表。
- 实现方式：
LOCK TABLES ... READ/WRITE：显式加表锁。- 元数据锁 (MDL - Metadata Lock)：这是InnoDB在访问表时自动加的锁，无需显式操作。它保护表结构（元数据）。
- 读锁 (MDL_READ)： 对表进行
SELECT等只读操作时加。多个读锁之间兼容。 - 写锁 (MDL_WRITE)： 对表进行
INSERT,UPDATE,DELETE,ALTER等修改数据或结构的操作时加。写锁与读锁、写锁之间互斥。
- 读锁 (MDL_READ)： 对表进行
- 特点： 粒度较粗，开销小，加锁快，但并发度低。MyISAM引擎主要使用表锁。InnoDB虽然以行锁为主，但在某些操作（如没有索引的全表扫描）或需要表级操作时也会用到表锁或MDL。
- 常见问题： DDL语句（如
ALTER TABLE）需要获取MDL写锁，如果此时有一个长时间运行的SELECT持有MDL读锁，那么DDL会被阻塞，后续所有对该表的请求（包括SELECT）都会被阻塞，导致连接堆积（"毛刺"现象）。
行级锁 (Row-level Locks)
- 作用范围： 单个数据行。
- 实现引擎： InnoDB 存储引擎的核心特性。
- 特点： 粒度最细，并发度最高，但也意味着锁管理开销最大。能最大程度地支持并发访问。
事务
SQL中事务的隔离级别
| 隔离级别 (Isolation Level) | 脏读(Dirty Read) | 不可重复读 (Non-Repeatable Read) | 幻读 (Phantom Read) | 特点与说明 |
|---|---|---|---|---|
| 读未提交 (Read Uncommitted) | 可能 | 可能 | 可能 | 最低级别。事务可以读取到其他事务未提交的修改（脏数据）。并发性能最高，但数据一致性最差。极少使用。 |
| 读已提交 (Read Committed, RC) | 不可能 | 可能 | 可能 | 事务只能读取到已提交的修改。解决了脏读问题。在同一个事务中，多次读取同一行数据可能得到不同结果（因为其他事务提交了修改）。Oracle、SQL Server 的默认级别。 |
| 可重复读 (Repeatable Read, RR) | 不可能 | 不可能 | 通常不可能发生 (在 MySQL InnoDB 中) | 保证在同一个事务中，多次读取同一数据结果一致。解决了脏读和不可重复读。MySQL InnoDB 的默认级别。InnoDB 通过 MVCC (多版本并发控制) 和 临键锁 (Next-Key Locks) 机制，在该级别下也解决了幻读问题。 |
| 串行化 (Serializable) | 不可能 | 不可能 | 不可能 | 最高级别。事务完全串行执行，避免了所有并发问题。通过强制加锁或队列化事务来实现。并发性能最低，只在对数据一致性要求极高且并发量不大的场景使用。 |
提示
MySQL默认隔离级别为可重复读
死锁问题
死锁问题的产生：
两个或多个事务相互等待对方持有的锁被释放，导致所有相关事务都无法继续执行，陷入永久等待的状态。
假设有两个事务（事务 A 和 事务 B）和两个数据行（行1 和 行2）。
| 时间点 | 事务 A (Transaction A) | 事务 B (Transaction B) | 锁状态 |
|---|---|---|---|
| T1 | BEGIN; | BEGIN; | 无锁 |
| T2 | UPDATE table SET ... WHERE id = 1; (成功获取行1的X锁) | 事务A持有行1的X锁 | |
| T3 | UPDATE table SET ... WHERE id = 2; (成功获取行2的X锁) | 事务B持有行2的X锁 | |
| T4 | UPDATE table SET ... WHERE id = 2; (尝试获取行2的X锁 → 被阻塞，等待事务B释放) | 事务A等待行2，事务B持有行2 | |
| T5 | UPDATE table SET ... WHERE id = 1; (尝试获取行1的X锁 → 被阻塞，等待事务A释放) | 事务B等待行1，事务A持有行1 |
MySQL 的 InnoDB 存储引擎具备自动死锁检测机制。它不会让死锁永远持续下去。
- 检测： InnoDB 会定期检查事务等待图（Wait-for Graph）中是否存在循环等待。
- 解决： 一旦检测到死锁，InnoDB 会选择一个代价较小的事务进行回滚 (Rollback)。
- 代价通常根据事务已经修改的数据量等因素估算，一般选择影响行数较少的事务
- 释放： 被回滚的事务会释放它持有的所有锁。
- 继续： 另一个（未被回滚的）事务就能获得所需的锁，从而继续执行并最终提交。
- 报错：被回滚的事务会收到一个错误提示
提示
死锁不会导致系统崩溃，但会导致其中一个事务失败。
数据类型
优化原则:
更小的通常更好 (Smaller is usually better)
在满足业务需求的前提下，选择占用存储空间最小的数据类型。
- 减少磁盘 I/O: 更小的数据意味着单次读取可以加载更多行，提高缓存命中率（如 InnoDB Buffer Pool）。
- 提高内存效率: 排序、分组、连接等操作需要在内存中进行，更小的数据类型消耗更少的内存。
- 加快索引速度: 索引也存储数据，更小的列意味着更小的索引，索引树层级更少，查询更快。
简单就好 (Simple is better)
优先选择 MySQL 内置的、简单的数据类型，避免使用复杂的或需要额外处理的类型。
- 处理速度快: MySQL 处理整数比处理字符串快得多。
- 减少计算开销: 内置类型有高度优化的比较、排序算法。
- 避免应用层处理: 将复杂性留在数据库层处理，简化应用逻辑。
尽量避免使用 NULL
在设计表结构时，除非业务逻辑明确要求，否则应将列定义为
NOT NULL。- 存储和性能开销:
NULL值在行格式中需要额外的位（bit）来标记，增加了存储开销。查询时处理NULL也比处理普通值更复杂。 - 索引效率: 对于
NULL值，某些索引（如唯一索引）的行为可能不符合预期（例如，唯一索引允许有多个NULL值）。 - 查询复杂性:
NULL参与比较（=、!=）和计算时会产生意外结果（NULL = NULL的结果是UNKNOWN，不是TRUE）。需要使用IS NULL/IS NOT NULL来判断。 - 聚合函数:
COUNT(*)会统计所有行，但COUNT(列名)会忽略NULL值。
- 存储和性能开销:
数值类型 (Numeric Types)
| 类型 | 存储 (Bytes) | 有符号范围 (Signed) | 无符号范围 (Unsigned) | 适用场景与建议 |
|---|---|---|---|---|
| TINYINT | 1 | -128 到 127 | 0 到 255 | 状态码、布尔值 (0/1)。优先使用 TINYINT UNSIGNED。 |
| SMALLINT | 2 | -32,768 到 32,767 | 0 到 65,535 | 较小的计数器、年份。 |
| MEDIUMINT | 3 | -8,388,608 到 8,388,607 | 0 到 16,777,215 | 中等规模的 ID 或计数。 |
| INT / INTEGER | 4 | -2,147,483,648 到 2,147,483,647 | 0 到 4,294,967,295 | 最常用的整数类型。主键、用户 ID、订单量等。除非明确需要更大范围，否则优先选择 INT。 |
| BIGINT | 8 | -2^63 到 2^63-1 | 0 到 2^64-1 | 超大 ID (如分布式系统)、大额金额 (分单位)、时间戳 (毫秒/微秒)。注意：占用空间是 INT 的两倍。 |
| FLOAT(M,D) | 4 | 单精度浮点数 | 科学计算、对精度要求不高的小数。存在精度丢失问题，不推荐用于金额。 | |
| DOUBLE(M,D) | 8 | 双精度浮点数 | 科学计算、对精度要求较高的小数。同样存在精度丢失问题，不推荐用于金额。 | |
| DECIMAL(M,D) | 可变 | 精确小数 | 金额、财务数据、需要精确计算的场景。M 是总位数，D 是小数位数。虽然性能略低于 FLOAT/DOUBLE，但精确性是第一位的。 |
日期和时间类型 (Date and Time Types)
| 类型 | 格式 | 范围 | 精度 | 存储 (Bytes) | 适用场景与建议 |
|---|---|---|---|---|---|
| DATE | YYYY-MM-DD | '1000-01-01' 到 '9999-12-31' | 天 | 3 | 只存储日期，如生日、入职日。 |
| TIME | HH:MM:SS[.fraction] | '-838:59:59.000000' 到 '838:59:59.000000' | 微秒 | 3 + 分数部分 | 存储时间或时间间隔。 |
| DATETIME | YYYY-MM-DD HH:MM:SS[.fraction] | '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999' | 微秒 | 5 + 分数部分 | 最常用的时间类型。存储精确的日期和时间。与时区无关。 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS[.fraction] | '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC | 微秒 | 4 + 分数部分 | 存储时间戳。存储的是从 1970-01-01 00:00:00 UTC 开始的秒数。受时区影响（存储 UTC，检索时转换为当前会话时区）。常用于 created_at, updated_at。 |
| YEAR | YYYY | 1901 到 2155 | 年 | 1 | 只存储年份。 |
字符串类型 (String Types)
| 类型 | 可变/定长 | 最大长度 | 存储 | 适用场景与建议 |
|---|---|---|---|---|
| CHAR(M) | 定长 | 0 到 255 个字符 | 固定 M 字节 (不足用空格填充) | 存储长度固定的短字符串，如国家代码 (CHAR(2)), 状态码 (CHAR(1)), MD5 值 (CHAR(32))。避免用于可变长文本，会造成空间浪费。 |
| VARCHAR(M) | 可变长 | 0 到 65,535 字节 (受行大小限制) | 实际长度 + 1 或 2 字节 (记录长度) | 最常用的字符串类型。存储可变长度的字符串，如用户名、邮箱、地址。M 是字符数，不是字节数（注意字符集，如 UTF8MB4 一个字符最多 4 字节）。 |
| TEXT | 可变长 | 65,535 字节 (约 64KB) | 行外存储 (off-page) | 存储较长的文本，如文章内容、备注。TEXT 列通常不存储在主记录中，而是存储指针，访问稍慢。 |
| MEDIUMTEXT | 可变长 | 16,777,215 字节 (约 16MB) | 行外存储 | 存储超长文本。 |
| LONGTEXT | 可变长 | 4,294,967,295 字节 (约 4GB) | 行外存储 | 存储极大文本。 |
| BINARY(M) | 定长 | M 字节 | 固定 M 字节 | 存储二进制数据，长度固定。 |
| VARBINARY(M) | 可变长 | M 字节 | 实际长度 + 长度字节 | 存储可变长二进制数据，如小文件、加密数据。比 BLOB 更高效。 |
| BLOB | 可变长 | 65,535 字节 | 行外存储 | 存储二进制大对象，如图片、文件。不推荐在数据库中直接存储大文件，应存文件路径。 |
范式化和反范式化
各自的优缺点
范式化 (Normalization)
定义: 将数据表按照范式（1NF, 2NF, 3NF, BCNF 等）的规则进行拆分和组织，以消除数据冗余和数据异常。
优点
- 减少数据冗余:
- 相同的数据（如用户信息、产品类别）只存储一次，大大节省了存储空间。
- 保证数据一致性与完整性:
- 更新数据时，只需修改一处，避免了因多处修改而导致的数据不一致（更新异常）。
- 通过外键约束等机制，可以有效防止插入无效数据（插入异常）或意外删除关联数据（删除异常）。
- 易于维护和扩展:
- 表结构清晰，职责单一（符合单一职责原则），便于理解和维护。
- 添加新功能或修改现有逻辑时，通常只需要修改或添加少数几个表，影响范围小。
- 数据模型清晰:
- 实体和关系明确，更贴近现实世界的逻辑结构，便于业务分析和沟通。
缺点
- 查询性能可能下降:
- 为了获取完整信息，经常需要对多个表进行
JOIN操作。 - 复杂的
JOIN（尤其是多表JOIN或大表JOIN）会消耗大量的 CPU 和内存资源，导致查询变慢。
- 为了获取完整信息，经常需要对多个表进行
- SQL 语句复杂:
- 查询语句变得复杂，需要编写更长的
SELECT语句，包含多个JOIN和ON条件，增加了开发和调试的难度。
- 查询语句变得复杂，需要编写更长的
- 索引优化复杂:
JOIN操作对索引的要求更高，需要为连接字段建立合适的索引，优化难度增加。
反范式化 (Denormalization)
定义: 为了提高查询性能，有意识地引入数据冗余，将原本需要 JOIN 才能获取的数据，预先合并到一张表中。
优点
- 显著提升查询性能:
- 减少了
JOIN操作，甚至可以做到单表查询，大大加快了读取速度。 - 特别适用于读操作远多于写操作的场景（如报表、数据分析）。
- 减少了
- 简化查询语句:
SELECT语句变得简单，通常只需要查询一张表，降低了开发复杂度。
- 减少锁竞争:
- 减少了跨表操作，降低了因
JOIN导致的锁等待和死锁风险。
- 减少了跨表操作，降低了因
缺点
- 增加数据冗余:
- 相同的数据在多个地方重复存储，占用了更多的磁盘空间。
- 可能导致数据不一致:
- 当需要更新冗余数据时，必须确保所有副本都被同步更新。如果更新逻辑不完善，很容易导致数据不一致。
- 例如，用户姓名在
orders表和order_details表中都有冗余，修改姓名时需要同时更新两个表。
- 增加写操作的复杂性和开销:
- 为了维护数据一致性，写入（INSERT, UPDATE, DELETE）操作的逻辑变得复杂，可能需要触发器、存储过程或应用层代码来保证所有冗余字段同步。
- 一次更新可能需要修改多行或多张表，增加了 I/O 开销和事务时间。
- 维护难度增加:
- 数据模型变得不那么“干净”，冗余字段的来源和更新逻辑需要清晰的文档说明，否则容易造成混乱。
索引
索引分类
按数据结构分类
- B+Tree 索引：这是最常见的索引类型，InnoDB 和 MyISAM 存储引擎都使用 B+Tree 来实现索引。它支持范围查询、排序和部分匹配。
- 哈希索引（Hash Index）：仅在 MEMORY 存储引擎中默认支持。哈希索引提供了快速的等值查找，但不支持范围查询或排序。
- 全文索引（Full-Text Index）：用于全文搜索，适用于大文本字段的搜索。MyISAM 和 InnoDB（5.6版本后）都支持这种类型的索引。
- R-Tree 索引（空间索引）：用于存储和查询地理空间数据。MyISAM 支持 R-Tree 索引，而 InnoDB 自从 MySQL 5.7 开始也支持空间索引。
按物理存储（与数据的关系）
- 聚簇索引（Clustered Index）：在 InnoDB 中，表的数据行按照主键的顺序存储，这就是聚簇索引。每个表只能有一个聚簇索引，通常就是主键索引。
- 非聚簇索引（Non-Clustered Index/Secondary Index）：除聚簇索引之外的所有其他索引都是非聚簇索引。在 InnoDB 中，非聚簇索引的叶子节点包含的是指向聚簇索引的指针，因此查询时可能需要额外的一次查找操作（回表）。
按逻辑功能/使用特性划分
- 普通索引（Index/KEY）：最基本的索引类型，没有任何唯一性约束，主要用于加速查询。
- 唯一索引（Unique Index）：确保索引列中的值是唯一的，但允许存在多个
NULL值。 - 主键索引（Primary Key）：一种特殊的唯一索引，不允许
NULL值，并且每个表只能有一个主键索引。 - 前缀索引（Prefix Index）：对字符串类型的列创建索引时，可以指定只对前 N 个字符建立索引以节省空间。
- 组合索引/联合索引（Composite Index）：多列联合索引，遵循“最左前缀原则”，即查询条件必须包含索引中最左边的列才能有效利用该索引。
- 覆盖索引（Covering Index）：当一个索引包含了查询所需的所有列时，称为覆盖索引。这样数据库可以直接从索引中获取数据，不需要访问表中的数据行。
| 存储引擎 | 主要索引类型 | 底层算法/结构 | 特点 |
|---|---|---|---|
| InnoDB （默认） | B+Tree 索引 | B+Tree | 支持事务、行锁，数据聚簇存储 |
| MyISAM（旧） | B+Tree 索引 | B+Tree | 不支持事务，表锁，非聚簇 |
| MEMORY | HASH / B+Tree | HASH 或 B+Tree | 内存表，快但数据不持久 |
查询优化
切分查询（拆分长SQL）
当一个SQL查询过于复杂时，可以考虑将其拆分成几个更小、更简单的查询。这样做有几个好处：
- 简化逻辑：更易于理解和维护。
- 提高可读性：有助于其他开发者理解代码意图。
- 便于缓存：某些中间结果集可以通过缓存来加速多次查询。
- 错误隔离：如果一个查询失败，只需重新运行出错的部分，而不是整个大查询
分解关联查询（拆分JOIN语句）
JOIN操作有时会导致性能瓶颈，尤其是在处理大量数据时。拆分JOIN语句通常意味着减少每个查询中的JOIN数量，或者避免不必要的JOIN。
优化：
- 延迟JOIN：只在必要时才执行JOIN操作。例如，首先筛选出主表的数据，然后再根据这些数据进行JOIN。
- 使用子查询代替JOIN：有时候，子查询可能比JOIN更高效，特别是当你只需要访问主表的一部分记录时。
- 增加索引：确保JOIN条件涉及的列上有适当的索引，以加快连接速度。
- 限制返回的数据量：在JOIN之前使用
WHERE子句过滤数据，以减少需要合并的数据量。
sql
SELECT FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
-JOIN post ON tag_post.post_id=post.id
-WHERE tag.tag='mysql';分解后:
sql
mysq1>SELECT FROM tag WHERE tag='mysql';
mysql>SELECT FROM tag_post WHERE tag id=1234;
mysql>SELECT FROM post WHERE post.id in (123,456,567,9098,8904);特定语句优化：
count():
统计行数时可以使用count(*),MySQL会忽略所有的列直接统计所有行数。
数据量较大时可以采用逆向思维查询，例如：
优化前：
sql
SELECT COUNT(*)FROM world.city WHERE ID>5优化后:
sql
SELECT (SELECT COUNT(*)FROM world.city)-COUNT(*)
-FROM world.City WHERE ID <5