MySql进阶(三)——存储引擎&EXPLAIN详解

存储引擎

MySQL体系结构：
MySql进阶(三)——存储引擎&EXPLAIN详解-Nickの博客

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。
默认存储引擎是InnoDB。

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎

特点：

DML 操作遵循 ACID 模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和正确性

文件：
xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
参数：innodb_file_per_table，决定多张表共享一个表空间还是每张表对应一个表空间

知识点：

查看 Mysql 变量：
show variables like 'innodb_file_per_table';

从idb文件提取表结构数据：（在cmd运行）
ibd2sdi xxx.ibd

InnoDB 逻辑存储结构：
MySql进阶(三)——存储引擎&EXPLAIN详解-Nickの博客

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件：

xxx.sdi: 存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

Memory

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

存放在内存中，速度快
hash索引（默认）

文件：
xxx.sdi: 存储表结构信息

存储引擎特点

MySql进阶(三)——存储引擎&EXPLAIN详解-Nickの博客

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。
Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。

EXPLAIN性能分析

参考上一篇博客：https://qkongtao.cn/?p=2851

explain

EXPLAIN 是 MySQL 中用于分析 SQL 查询执行计划的重要工具，通过它可以查看查询如何使用索引、扫描数据的方式以及表之间的连接顺序等信息。以下是 EXPLAIN 输出各字段的详细解释：

EXPLAIN 基本语法

EXPLAIN SELECT * FROM users WHERE age > 18;

输出结果包含以下核心字段：
MySql进阶(三)——存储引擎&EXPLAIN详解-Nickの博客

EXPLAIN各字段详细解释

1、id（查询标识符）
含义：查询的唯一标识符，标识 SQL 语句的执行顺序。

规则：

id 相同：按表的读取顺序执行（从上到下）。
id 不同：id 值越大越先执行（如子查询的 id 通常更大）。
id 为 NULL：表示汇总操作（如UNION的结果合并）。

2、select_type（查询类型）
常见取值：

SIMPLE：简单查询（不包含子查询或 UNION）。
PRIMARY：主查询（包含子查询时，最外层查询）。
SUBQUERY：子查询（嵌套在 SELECT、WHERE 或 FROM 中的查询）。
DERIVED：派生表（FROM 子句中的子查询，MySQL 会将其结果存入临时表）。
UNION：UNION 操作的第二个及后续查询。
UNION RESULT：UNION 结果的合并操作。

3、table（表名）
含义：当前执行的表名（可能是实际表名或别名，派生表显示为，N 为 id 值）。

4、partitions（分区信息）
含义：查询匹配的分区（仅对分区表有效，否则为 NULL）。

5、type（连接类型，重要！）
含义：表示表的访问方式，反映查询效率（从最优到最差排序）：

system：表中只有一行数据（系统表，如information_schema）。
const：通过索引一次匹配一行数据（如WHERE id = 1）。
eq_ref：唯一性索引扫描，每行仅匹配一条记录（常见于JOIN操作，如ON t1.id = t2.id）。
ref：非唯一性索引扫描，返回匹配某个值的所有行（如WHERE name = 'Alice'）。
range：索引范围扫描（如WHERE id BETWEEN 1 AND 10或WHERE age > 18）。
index：全索引扫描（遍历整个索引树，如SELECT id FROM users，id 是索引）。
ALL：全表扫描（未使用索引，性能最差）。

6、possible_keys（可能使用的索引）
含义：MySQL 认为可能用于优化查询的索引。
注意：可能存在多个索引，但实际不一定使用（需结合key字段判断）。

7、key（实际使用的索引）
含义：MySQL 实际选择的索引。
若为 NULL：表示未使用索引（如type=ALL）。

8、key_len（索引使用的字节数）
含义：表示索引中使用的字节数，可评估索引的精确程度（值越小越精确）。
计算示例：
对于VARCHAR(200) UTF8类型，key_len = 200 * 3 + 2 = 602（3 字节 / 字符 + 2 字节长度前缀）。
若索引包含NULL列，需额外加 1 字节。

9、ref（哪些列或常量与索引比较）
含义：显示哪些字段或常量被用于查找索引列上的值。
常见取值：
const：常量值（如WHERE id = 1）。
表名。列名：其他表的列（如JOIN操作中使用的列）。

10、rows（预估扫描的行数）
含义：MySQL 估计执行查询需要扫描的行数（非精确值，基于统计信息）。
意义：值越小，查询效率越高。

11、filtered（过滤后的行百分比）
含义：表示rows经过过滤后剩余的比例（如50.00%表示约一半数据被过滤）。
计算公式：最终结果行数 = rows * filtered / 100。

12、Extra（额外信息，重要！）
含义：包含查询执行的额外细节，常见取值及优化建议：

Using filesort：需额外排序（如ORDER BY未使用索引），性能较差。
优化建议：对ORDER BY字段添加索引。
Using temporary：使用临时表（如GROUP BY或UNION操作），性能开销大。
优化建议：避免复杂的GROUP BY或改用INDEX覆盖索引。
Using index：使用覆盖索引（查询的列直接从索引中获取，无需回表），性能最优。
Using where：使用WHERE子句过滤数据。
Using join buffer：使用连接缓冲区（未使用索引的表连接）。
优化建议：为JOIN条件添加索引。

覆盖索引：避免回表的性能提升

什么是覆盖索引？
覆盖索引是指一个索引包含了查询所需的所有字段，MySQL可以直接从索引中获取需要的数据，而无需回表查询数据行。这就像是一本教科书，如果目录已经包含了你要找的全部信息，就不需要翻到正文页面了。
覆盖索引的优势

减少IO操作：避免回表操作，减少磁盘IO
提升查询速度：索引数据通常比行数据小，且更可能缓存在内存中
减少内存占用：只需要加载索引数据，不需要加载整行数据

实战场景：订单查询的性能优化

假设有一个电商平台的订单表，结构如下：

CREATE TABLE `orders` (
  `order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `order_time` DATETIME NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 0,
  `remark` VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_user_time` (`user_id`, `order_time`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

现在有一个高频查询：获取某个用户最近一个月的订单记录，只需要订单ID、用户ID、下单时间和订单金额。

SELECT order_id, user_id, order_time, amount 
FROM orders 
WHERE user_id = 1001 
  AND order_time >= '2023-05-01' 
  AND order_time < '2023-06-01';

实战优化：
在订单表例子中，现有索引idx_user_time包含了user_id和order_time，但查询还需要amount字段。为了使用覆盖索引，我们可以创建新索引：

ALTER TABLE orders ADD INDEX idx_user_time_amount (user_id, order_time, amount);

现在执行同样的查询，使用EXPLAIN分析执行计划：

EXPLAIN SELECT order_id, user_id, order_time, amount 
FROM orders 
WHERE user_id = 1001 
  AND order_time >= '2023-05-01' 
  AND order_time < '2023-06-01';

EXPLAIN结果分析：
MySql进阶(三)——存储引擎&EXPLAIN详解-Nickの博客

从EXPLAIN结果可以看到：

key字段显示使用了idx_user_time_amount索引
Extra字段显示"Using index"，表示使用了覆盖索引
key_len为9，表示索引使用了9字节（user_id占4字节，order_time占5字节）

提示：key_len表示查询实际使用索引的字节长度，可以判断联合索引使用深度。

覆盖索引的使用建议：

针对高频查询，设计专门的覆盖索引
将WHERE条件中的字段和SELECT需要的字段都包含在索引中
注意索引长度，避免创建过大的联合索引