MySQ存储引擎、索引、事务以及SQL调优

一、MySQL 存储引擎

存储引擎是 MySQL 处理数据的底层组件，决定了表的存储方式、支持的功能（如事务、锁机制）等。MySQL 支持多种存储引擎，可通过SHOW ENGINES;查看所有引擎及支持情况。

1. 常用存储引擎及特性

2. 存储引擎选择依据

• 是否需要事务（优先 InnoDB）；
• 并发读写频率（写多→InnoDB 行锁；读多→MyISAM 表锁更高效）；
• 数据持久性（Memory 不持久，适合临时数据）；
• 存储需求（Archive 适合压缩归档）。

二、索引

索引是帮助 MySQL 高效查询数据的数据结构（类似书籍目录），核心作用是减少扫描的数据量。但索引会增加写操作（插入 / 更新 / 删除）的开销（需维护索引结构），需合理设计。

1. 索引类型

• B + 树索引（最常用）：
• 结构：叶子节点有序且相连（便于范围查询），非叶子节点只存索引键，叶子节点存数据地址（非聚簇）或数据本身（聚簇）；
• 适用：等值查询（=）、范围查询（>、<、between）、排序（order by）。
• 聚簇索引 vs 非聚簇索引：
• 聚簇索引：索引与数据物理存储在一起（InnoDB 中主键是默认聚簇索引），查询效率最高；
• 非聚簇索引（二级索引）：索引与数据分离，查询需先查二级索引，再通过主键查聚簇索引（回表）。
• 其他索引：
• 哈希索引：适用于等值查询（O (1)），不支持范围查询（Memory 引擎默认）；
• 全文索引：用于文本内容模糊匹配（如 MATCH AGAINST）；
• 空间索引：用于地理数据查询。

2. 索引设计原则

• 最左前缀原则：复合索引（a,b,c）仅支持以 a 开头的查询（如 where a=?、a=? and b=?），中间跳过字段会导致索引失效；
• 避免过度索引：一张表索引建议不超过 5 个（写操作会变慢）；
• 选择区分度高的字段：如身份证号（区分度 100%）适合建索引，性别（区分度低）不适合；
• 小字段优先：索引字段长度越小，索引树占用空间越小，查询越快（如用 int 代替 varchar 存状态）。

3. 索引失效场景

• 索引列参与函数运算（如 where SUBSTR(name,1,3)='abc'）；
• 索引列使用不等于（!=、<>）、not in、is not null；
• 模糊查询前缀带 %（如 where name like '%abc'）；
• 隐式类型转换（如字符串字段用数字查询：where phone=13800138000）；
• or 连接的字段中，部分字段无索引（会全表扫描）。

三、事务

事务是一组不可分割的 SQL 操作，要么全成功，要么全失败（保证数据一致性）。

1. ACID 特性

• 原子性（Atomicity）：事务中操作要么全执行，要么全回滚（通过 undo log 实现）；
• 一致性（Consistency）：事务前后数据状态合法（如转账后总金额不变）；
• 隔离性（Isolation）：多个事务并发执行时，相互不干扰（通过锁和 MVCC 实现）；
• 持久性（Durability）：事务提交后，数据永久保存（通过 redo log 实现）。

2. 隔离级别（解决并发问题）

MySQL 定义了 4 种隔离级别，默认是 可重复读（RR）：

3. 事务实现原理

• redo log：保证持久性，事务执行时记录 “数据修改”，崩溃后可通过 redo log 恢复已提交的修改；
• undo log：保证原子性，记录 “数据修改前的状态”，事务回滚时通过 undo log 恢复；
• MVCC：多版本并发控制，通过保存数据的多个版本，实现读不加锁（提高并发），是 RR 和 RC 隔离级别的核心。

四、SQL 调优

SQL 调优的核心目标是减少查询耗时，需从索引、语句、表结构、配置等多维度优化。

1. 索引优化（基础）

• 用EXPLAIN分析查询计划，重点看：
• type：查询类型（const>ref>range>ALL，ALL表示全表扫描，需优化）；
• key：实际使用的索引（NULL表示未用索引）；
• rows：预估扫描行数（越小越好）。
• 修复索引失效问题（见上文 “索引失效场景”）；
• 用覆盖索引避免回表（如查询字段均为索引字段：SELECT id,name FROM user WHERE name=?，若(name,id)为联合索引，则无需回表）。

2. SQL 语句优化

• 避免SELECT *：只查需要的字段（减少数据传输，可能触发覆盖索引）；
• 优化子查询：子查询可能生成临时表，改用JOIN（如SELECT * FROM user WHERE id IN (SELECT user_id FROM order) → JOIN改写）；
• 控制JOIN表数量：多表JOIN会增加关联开销，建议不超过 5 张表；
• 避免LIMIT深分页：如LIMIT 100000, 10需扫描 100010 行，可改用索引定位：WHERE id > 100000 LIMIT 10（需 id 有序）。

3. 表结构优化

• 字段类型合理化：
• 用INT代替VARCHAR存数字（如手机号用BIGINT，避免字符串运算）；
• 用TINYINT（1 字节）存性别（0/1），而非VARCHAR；
• 避免TEXT/BLOB存大字段（可拆到单独表，或用对象存储）。
• 分表分库：
• 水平分表（按数据量拆分，如订单表按时间分表：order_2023、order_2024）；
• 垂直分表（按字段拆分，如用户表拆为user_base（基本信息）和user_detail（详细信息））。

4. 配置与架构优化

• 调整 MySQL 配置：
• innodb_buffer_pool_size：InnoDB 缓存池（建议设为物理内存的 50%-70%，减少磁盘 IO）；
• join_buffer_size：连接缓存（不宜过大，避免内存占用过高）。
• 读写分离：主库写、从库读（通过 binlog 同步数据），分摊主库压力；
• 缓存热点数据：用 Redis 缓存高频查询结果（如商品详情），减少 MySQL 访问。