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

Rinai
172 阅读5分钟

MySQL学习笔记

存储引擎

1. 常用的存储引擎

  1. InnoDB

    • 特点：
      • 支持事务（遵循ACID模型）。
      • 实现行级锁，提高并发性能。
      • 支持外键约束，确保数据完整性。
    • 文件结构：
      • xxx.ibd: 存储数据和索引。
      • 表结构文件（frm、sdi）也与之相关。
    • 其他参数：
      • innodb_file_per_table: 决定每张表是否有独立表空间。
    • 使用场景：适用于需要高可靠性和高并发访问的应用程序。

  2. MyISAM

    • 特点：
      • 不支持事务和外键。
      • 使用表级锁，不能支持行级锁。
      • 访问速度快，适合读多写少的场景。
    • 文件结构：
      • xxx.sdi: 存储表结构信息。
      • xxx.MYD: 存储数据。
      • xxx.MYI: 存储索引。
    • 使用场景：适用于以数据读取为主，且不需要事务支持的应用。

  3. Memory

    • 特点：
      • 数据存放在内存中，速度极快。
      • 受到硬件问题和断电的影响，适合用作临时表或缓存。
    • 文件结构：存放在内存中，不生成物理文件。
    • 使用场景：适用于需要快速访问的临时数据存储。

2. 存储引擎比较表

特点InnoDBMyISAMMemory
存储限制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. 索引结构及支持

索引结构描述InnoDBMyISAMMemory
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. 聚集索引选取规则

  1. 如果存在主键，主键索引即为聚集索引。
  2. 如果没有主键，将使用第一个唯一索引作为聚集索引。
  3. 如果表没有主键或可用的唯一索引，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. 语法

  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);

  2. 查看索引：

    • 语法： 
      SHOW INDEX FROM table_name;

  3. 删除索引：

    • 语法： 
      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. 注意事项
  • 对于频繁查询且数据量大的字段应建立索引。
  • 使用区分度高的列作为索引可以提高效率。
  • 避免过多索引，以免影响增删改的性能。
avatar
文章
阅读
粉丝