MySQL学习笔记4【存储引擎和索引】

存储引擎

1. 常用的存储引擎

InnoDB
- 特点：
  - 支持事务（遵循ACID模型）。
  - 实现行级锁，提高并发性能。
  - 支持外键约束，确保数据完整性。
- 文件结构：
  - xxx.ibd: 存储数据和索引。
  - 表结构文件（frm、sdi）也与之相关。
- 其他参数：
  - innodb_file_per_table: 决定每张表是否有独立表空间。
- 使用场景：适用于需要高可靠性和高并发访问的应用程序。
MyISAM
- 特点：
  - 不支持事务和外键。
  - 使用表级锁，不能支持行级锁。
  - 访问速度快，适合读多写少的场景。
- 文件结构：
  - xxx.sdi: 存储表结构信息。
  - xxx.MYD: 存储数据。
  - xxx.MYI: 存储索引。
- 使用场景：适用于以数据读取为主，且不需要事务支持的应用。
Memory
- 特点：
  - 数据存放在内存中，速度极快。
  - 受到硬件问题和断电的影响，适合用作临时表或缓存。
- 文件结构：存放在内存中，不生成物理文件。
- 使用场景：适用于需要快速访问的临时数据存储。

2. 存储引擎比较表

特点

InnoDB

MyISAM

Memory

存储限制

64TB

有

事务安全

支持

锁机制

行锁

表锁

B+树索引

支持

哈希索引

支持

全文索引

支持（5.6版本后）

支持

空间使用

高

低

N/A

内存使用

高

低

中等

批量插入速度

低

高

支持外键

支持

3. 相关操作

查看建表语句：
```
SHOW CREATE TABLE account;
```

建表时指定存储引擎：

CREATE TABLE 表名 (
    ...
) ENGINE=INNODB;

查看当前数据库支持的存储引擎：
```
SHOW ENGINES;
```

查看 MySQL 变量：

SHOW VARIABLES LIKE 'innodb_file_per_table';

从 .ibd 文件提取表结构数据：
```
ibd2sdi xxx.ibd
```

4. 选择

存储引擎

特点

适用场景

InnoDB

- 事务完整性要求高

如果应用对事务的完整性有比较高的要求，且在并发条件下要求数据一致性。

- 支持多种数据操作（插入、查询、更新、删除）

适用于包括大量更新和删除操作的应用。

MyISAM

- 读操作和插入操作为主，更新和删除操作非常少

如果应用主要以读操作和插入操作为主，且对事务的完整性、并发性要求不高。

- 不支持事务和外键

适用于不需要事务支持的场景。

Memory

- 所有数据保存在内存中，访问速度极快

适用于需要快速访问的临时表和缓存(不如Redis)。

- 对表的大小有限制，太大的表无法缓存在内存中

Memory 的缺陷是无法保障数据的安全性。

- 不生成物理文件

索引

内容

索引定义

索引是帮助 MySQL 高效获取数据的数据结构，通常是有序的。

工作原理

数据库系统维护特定查找算法的数据结构以引用数据，提升查询效率。

1. 优缺点

优缺点

描述

优点

1. 提高数据检索效率，降低数据库的 I/O 成本。 2. 利用索引列对数据排序，降低排序成本，降低 CPU 的消耗。

缺点

1. 索引需要占用额外空间。 2. 更新（INSERT/UPDATE/DELETE）操作速度降低。

2. 索引结构及支持

索引结构

描述

InnoDB

MyISAM

Memory

B+Tree

最常见的索引类型，支持范围查询。

支持

Hash

哈希表实现，仅支持精确匹配，无法支持范围查询。

不支持

支持

R-Tree

主要用于地理空间数据，适用于范围查询。

不支持

支持

不支持

Full-Text

文本搜索，通过倒排索引快速匹配文档。

5.6版本后支持

支持

不支持

3. 数据结构比较

数据结构

描述

B-Tree

多路平衡查找树，节点可存储多个关键字。

B+Tree

所有数据在叶子节点，形成链表，适合范围查询，优化区间访问性能。

4. Hash 索引原理

特点

描述

适用场景

仅能用于等值比较（=、IN），不支持范围查询（BETWEEN、>、<）。

排序操作

无法通过索引完成排序操作。

查询效率

查询通常只需一次检索，效率高于 B+Tree 索引。

5. 存储引擎支持情况

存储引擎

特点

Memory

支持 Hash 索引。

InnoDB

自适应 Hash 功能，在特定条件下自动构建的 Hash 索引。

6. MySQL 索引分类

分类

含义

特点

关键字

主键索引

针对表中主键创建的索引

默认自动创建，只能有一个

PRIMARY

唯一索引

避免同一表中重复数据

可以有多个

UNIQUE

常规索引

快速定位特定数据

可以有多个

全文索引

根据文本中的关键词查找，而非索引中的值

可以有多个

FULLTEXT

7. 存储引擎中的索引形式

在 InnoDB 存储引擎中，索引还可以按照其存储形式分为以下两种：

分类

含义

特点

聚集索引

将数据存储与索引放在一起，叶子节点保存行数据

每个表必须有且只有一个，查询效率高，直接访问数据行，适合范围查询。

二级索引

将数据与索引分开存储，叶子节点关联主键

可以存在多个，查询时需进行两次查找：一次是查找二级索引，另一次是通过主键查找数据。

tip：根据特点，我们可以知道根据主键查找会更快，如果通过其他字段来查找数据，这个字段就会作为一个二级索引，查找到相应的主键（聚集索引）后，通过回表查询来再在聚集索引中查询相应的行数据。

8. 聚集索引选取规则

如果存在主键，主键索引即为聚集索引。
如果没有主键，将使用第一个唯一索引作为聚集索引。
如果表没有主键或可用的唯一索引，InnoDB 会生成一个自动隐藏的 rowid 作为聚集索引。

9.性能分析

性能分析是数据库优化的重要环节，以下是关于 MySQL 中一些常用的性能分析工具和方法：

9.1 查看执行频次

可以通过以下命令查看当前数据库中的 INSERT、UPDATE、DELETE 和 SELECT 的访问频次：

全局状态：
```
SHOW GLOBAL STATUS LIKE 'Com_______';
```
会话状态：
```
SHOW SESSION STATUS LIKE 'Com_______';
```

9.2 慢查询日志

慢查询日志用于记录执行时间超过指定阈值的 SQL 语句。配置步骤如下：

在 MySQL 配置文件（通常是 /etc/my.cnf）中添加以下配置：

# 开启慢查询日志
slow_query_log=1
# 设置慢查询时间为2秒
long_query_time=2

配置完成后，重启 MySQL 服务。慢查询日志的默认存储路径为：/var/lib/mysql/localhost-slow.log。
注意，这里的默认存储文件每个人可能不一样，如果没找到查看mysql的配置文件或者实在不行可以问AI来解决。

9.3 查看慢查询日志开关状态

SHOW VARIABLES LIKE 'slow_query_log';

9.4 Profiling

Profiling 是用于分析 SQL 查询性能的工具。

检查 MySQL 是否支持 Profiling：
```
SELECT @@have_profiling;
```
开启 Profiling（会话或全局）：
```
SET profiling = 1;
```
查看所有 SQL 语句的耗时：
```
SHOW PROFILES;
```
查看指定 query_id 的 SQL 语句耗时：
```
SHOW PROFILE FOR QUERY query_id;
```
查看指定 query_id 的 SQL 语句 CPU 使用情况：
```
SHOW PROFILE CPU FOR QUERY query_id;
```

9.5 Explain

使用 EXPLAIN 或 DESC 命令可以获取 MySQL 如何执行 SELECT 语句的信息。

语法示例：

EXPLAIN 
SELECT 字段列表 
FROM 表名 
WHERE 条件;

EXPLAIN 各字段含义：

id：查询的序列号，表示执行顺序。
select_type：表示 SELECT 的类型，如 SIMPLE、PRIMARY、UNION、SUBQUERY 等。
type：连接类型，性能由好到差依次为 NULL(不访问任何表)、system(访问系统表)、const(主键或唯一索引)、eq_ref(查询通过主键或唯一索引进行等值连接)、ref(非唯一性索引)、range(通过索引进行范围扫描)、index(查询通过扫描整个索引树来获取数据)、all(全表遍历)。(一半在优化的时候是难以达到NULL的级别，但是尽可能向左优化)。
possible_key：可能应用的索引列表。
Key：实际使用的索引，若为 NULL 则没有使用索引。
Key_len：使用的索引字节数，越短越好。
rows：估计需要执行的行数。
filtered：返回结果行数占需读取行数的百分比，值越大越好。

9.6 索引的使用

1. 语法

创建索引：

语法：

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

示例：

CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user (phone);
CREATE INDEX idx_user_pro_age_stat ON tb_user(profession, age, status); #联合索引
CREATE INDEX idx_user_email ON tb_user(email);

查看索引：
- 语法：
  SHOW INDEX FROM table_name;
删除索引：
- 语法：
  DROP INDEX index_name ON table_name;

2. 使用规则

最左前缀法则：查询条件应从索引的最左列开始，跳过某一列将导致索引的部分失效。
范围查询影响：联合索引中一旦出现范围查询（如 <, >），该范围右侧的列索引将失效。
索引失效情况：
- 在索引列上进行运算操作（例如 substring(phone, 10, 2)）。
- 字符串类型字段不加引号时索引失效（如 where phone = 17799990015）。
- 模糊查询中，前部模糊匹配将使索引失效，比如 LIKE '%工程'。
- 使用 OR 时，如果其中一个条件的列没有索引，涉及的索引都可能不会被使用。

3. SQL优化提示

使用提示：
- USE INDEX：建议使用的索引
- IGNORE INDEX：不使用的索引
- FORCE INDEX：强制使用指定的索引
覆盖索引与回表查询：
- 尽量使用覆盖索引，避免 SELECT *，以减少回表查询的次数。

4. 前缀索引

用于长字符串的索引，可以只建立部分前缀索引来节约空间，提高效率。

5. 单列索引与联合索引

建议在常用的查询条件上建立联合索引。
单列索引适用于简单查询条件。

6. 注意事项

对于频繁查询且数据量大的字段应建立索引。
使用区分度高的列作为索引可以提高效率。
避免过多索引，以免影响增删改的性能。

上一页MySQL学习笔记3【事务】下一页MySQL学习笔记5【SQL优化/视图/存储过程/触发器】

最后更新于1天前

MySQL学习笔记4【存储引擎和索引】

存储引擎

1. 常用的存储引擎

InnoDB
- 特点：
  - 支持事务（遵循ACID模型）。
  - 实现行级锁，提高并发性能。
  - 支持外键约束，确保数据完整性。
- 文件结构：
  - xxx.ibd: 存储数据和索引。
  - 表结构文件（frm、sdi）也与之相关。
- 其他参数：
  - innodb_file_per_table: 决定每张表是否有独立表空间。
- 使用场景：适用于需要高可靠性和高并发访问的应用程序。
MyISAM
- 特点：
  - 不支持事务和外键。
  - 使用表级锁，不能支持行级锁。
  - 访问速度快，适合读多写少的场景。
- 文件结构：
  - xxx.sdi: 存储表结构信息。
  - xxx.MYD: 存储数据。
  - xxx.MYI: 存储索引。
- 使用场景：适用于以数据读取为主，且不需要事务支持的应用。
Memory
- 特点：
  - 数据存放在内存中，速度极快。
  - 受到硬件问题和断电的影响，适合用作临时表或缓存。
- 文件结构：存放在内存中，不生成物理文件。
- 使用场景：适用于需要快速访问的临时数据存储。

2. 存储引擎比较表

特点

InnoDB

MyISAM

Memory

存储限制

64TB

有

事务安全

支持

锁机制

行锁

表锁

B+树索引

支持

哈希索引

支持

全文索引

支持（5.6版本后）

支持

空间使用

高

低

N/A

内存使用

高

低

中等

批量插入速度

低

高

支持外键

支持

3. 相关操作

查看建表语句：
```
SHOW CREATE TABLE account;
```

建表时指定存储引擎：

CREATE TABLE 表名 (
    ...
) ENGINE=INNODB;

查看当前数据库支持的存储引擎：
```
SHOW ENGINES;
```

查看 MySQL 变量：

SHOW VARIABLES LIKE 'innodb_file_per_table';

从 .ibd 文件提取表结构数据：
```
ibd2sdi xxx.ibd
```

4. 选择

存储引擎

特点

适用场景

InnoDB

- 事务完整性要求高

如果应用对事务的完整性有比较高的要求，且在并发条件下要求数据一致性。

- 支持多种数据操作（插入、查询、更新、删除）

适用于包括大量更新和删除操作的应用。

MyISAM

- 读操作和插入操作为主，更新和删除操作非常少

如果应用主要以读操作和插入操作为主，且对事务的完整性、并发性要求不高。

- 不支持事务和外键

适用于不需要事务支持的场景。

Memory

- 所有数据保存在内存中，访问速度极快

适用于需要快速访问的临时表和缓存(不如Redis)。

- 对表的大小有限制，太大的表无法缓存在内存中

Memory 的缺陷是无法保障数据的安全性。

- 不生成物理文件

索引

内容

索引定义

索引是帮助 MySQL 高效获取数据的数据结构，通常是有序的。

工作原理

数据库系统维护特定查找算法的数据结构以引用数据，提升查询效率。

1. 优缺点

优缺点

描述

优点

1. 提高数据检索效率，降低数据库的 I/O 成本。 2. 利用索引列对数据排序，降低排序成本，降低 CPU 的消耗。

缺点

1. 索引需要占用额外空间。 2. 更新（INSERT/UPDATE/DELETE）操作速度降低。

2. 索引结构及支持

索引结构

描述

InnoDB

MyISAM

Memory

B+Tree

最常见的索引类型，支持范围查询。

支持

Hash

哈希表实现，仅支持精确匹配，无法支持范围查询。

不支持

支持

R-Tree

主要用于地理空间数据，适用于范围查询。

不支持

支持

不支持

Full-Text

文本搜索，通过倒排索引快速匹配文档。

5.6版本后支持

支持

不支持

3. 数据结构比较

数据结构

描述

B-Tree

多路平衡查找树，节点可存储多个关键字。

B+Tree

所有数据在叶子节点，形成链表，适合范围查询，优化区间访问性能。

4. Hash 索引原理

特点

描述

适用场景

仅能用于等值比较（=、IN），不支持范围查询（BETWEEN、>、<）。

排序操作

无法通过索引完成排序操作。

查询效率

查询通常只需一次检索，效率高于 B+Tree 索引。

5. 存储引擎支持情况

存储引擎

特点

Memory

支持 Hash 索引。

InnoDB

自适应 Hash 功能，在特定条件下自动构建的 Hash 索引。

6. MySQL 索引分类

分类

含义

特点

关键字

主键索引

针对表中主键创建的索引

默认自动创建，只能有一个

PRIMARY

唯一索引

避免同一表中重复数据

可以有多个

UNIQUE

常规索引

快速定位特定数据

可以有多个

全文索引

根据文本中的关键词查找，而非索引中的值

可以有多个

FULLTEXT

7. 存储引擎中的索引形式

在 InnoDB 存储引擎中，索引还可以按照其存储形式分为以下两种：

分类

含义

特点

聚集索引

将数据存储与索引放在一起，叶子节点保存行数据

每个表必须有且只有一个，查询效率高，直接访问数据行，适合范围查询。

二级索引

将数据与索引分开存储，叶子节点关联主键

可以存在多个，查询时需进行两次查找：一次是查找二级索引，另一次是通过主键查找数据。

8. 聚集索引选取规则

如果存在主键，主键索引即为聚集索引。
如果没有主键，将使用第一个唯一索引作为聚集索引。
如果表没有主键或可用的唯一索引，InnoDB 会生成一个自动隐藏的 rowid 作为聚集索引。

9.性能分析

性能分析是数据库优化的重要环节，以下是关于 MySQL 中一些常用的性能分析工具和方法：

9.1 查看执行频次

可以通过以下命令查看当前数据库中的 INSERT、UPDATE、DELETE 和 SELECT 的访问频次：

全局状态：
```
SHOW GLOBAL STATUS LIKE 'Com_______';
```
会话状态：
```
SHOW SESSION STATUS LIKE 'Com_______';
```

9.2 慢查询日志

慢查询日志用于记录执行时间超过指定阈值的 SQL 语句。配置步骤如下：

在 MySQL 配置文件（通常是 /etc/my.cnf）中添加以下配置：

# 开启慢查询日志
slow_query_log=1
# 设置慢查询时间为2秒
long_query_time=2

配置完成后，重启 MySQL 服务。慢查询日志的默认存储路径为：/var/lib/mysql/localhost-slow.log。
注意，这里的默认存储文件每个人可能不一样，如果没找到查看mysql的配置文件或者实在不行可以问AI来解决。

9.3 查看慢查询日志开关状态

SHOW VARIABLES LIKE 'slow_query_log';

9.4 Profiling

Profiling 是用于分析 SQL 查询性能的工具。

检查 MySQL 是否支持 Profiling：
```
SELECT @@have_profiling;
```
开启 Profiling（会话或全局）：
```
SET profiling = 1;
```
查看所有 SQL 语句的耗时：
```
SHOW PROFILES;
```
查看指定 query_id 的 SQL 语句耗时：
```
SHOW PROFILE FOR QUERY query_id;
```
查看指定 query_id 的 SQL 语句 CPU 使用情况：
```
SHOW PROFILE CPU FOR QUERY query_id;
```

9.5 Explain

使用 EXPLAIN 或 DESC 命令可以获取 MySQL 如何执行 SELECT 语句的信息。

语法示例：

EXPLAIN 
SELECT 字段列表 
FROM 表名 
WHERE 条件;

EXPLAIN 各字段含义：

id：查询的序列号，表示执行顺序。
select_type：表示 SELECT 的类型，如 SIMPLE、PRIMARY、UNION、SUBQUERY 等。
type：连接类型，性能由好到差依次为 NULL(不访问任何表)、system(访问系统表)、const(主键或唯一索引)、eq_ref(查询通过主键或唯一索引进行等值连接)、ref(非唯一性索引)、range(通过索引进行范围扫描)、index(查询通过扫描整个索引树来获取数据)、all(全表遍历)。(一半在优化的时候是难以达到NULL的级别，但是尽可能向左优化)。
possible_key：可能应用的索引列表。
Key：实际使用的索引，若为 NULL 则没有使用索引。
Key_len：使用的索引字节数，越短越好。
rows：估计需要执行的行数。
filtered：返回结果行数占需读取行数的百分比，值越大越好。

9.6 索引的使用

1. 语法

创建索引：

语法：

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

示例：

CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user (phone);
CREATE INDEX idx_user_pro_age_stat ON tb_user(profession, age, status); #联合索引
CREATE INDEX idx_user_email ON tb_user(email);

查看索引：
- 语法：
  SHOW INDEX FROM table_name;
删除索引：
- 语法：
  DROP INDEX index_name ON table_name;

2. 使用规则

最左前缀法则：查询条件应从索引的最左列开始，跳过某一列将导致索引的部分失效。
范围查询影响：联合索引中一旦出现范围查询（如 <, >），该范围右侧的列索引将失效。
索引失效情况：
- 在索引列上进行运算操作（例如 substring(phone, 10, 2)）。
- 字符串类型字段不加引号时索引失效（如 where phone = 17799990015）。
- 模糊查询中，前部模糊匹配将使索引失效，比如 LIKE '%工程'。
- 使用 OR 时，如果其中一个条件的列没有索引，涉及的索引都可能不会被使用。

3. SQL优化提示

使用提示：
- USE INDEX：建议使用的索引
- IGNORE INDEX：不使用的索引
- FORCE INDEX：强制使用指定的索引
覆盖索引与回表查询：
- 尽量使用覆盖索引，避免 SELECT *，以减少回表查询的次数。

4. 前缀索引

用于长字符串的索引，可以只建立部分前缀索引来节约空间，提高效率。

5. 单列索引与联合索引

建议在常用的查询条件上建立联合索引。
单列索引适用于简单查询条件。

6. 注意事项

对于频繁查询且数据量大的字段应建立索引。
使用区分度高的列作为索引可以提高效率。
避免过多索引，以免影响增删改的性能。

上一页MySQL学习笔记3【事务】下一页MySQL学习笔记5【SQL优化/视图/存储过程/触发器】

最后更新于1天前