MySQL⑥存储引擎、索引

1、MySQL 体系结构

体系结构图

image-20220313162348744

  1. 系统管理和控制工具：备份与恢复、安全、复制、集群等

  2. 连接层：引入线程池的概念

    • 认证，线程管理，连接管理等
    • 可以实现基于 SSL 的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  3. 服务层：完成大多数的核心服务功能

    • SQL 接口：处理 SQL 命令，存储过程，试图，触发器等
    • 解析器
    • 查询优化器：是否使用索引等
    • 缓存

  4. 引擎层：内存、索引、存储管理

    • 真正负责 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信。
    • 不同的存储引擎具有不同的功能，根据需要选取合适的存储引擎。
    • 实现索引

  5. 存储层

2、存储引擎

存储引擎：存储数据、建立索引、更新/查询数据等技术的实现方式。

  • 基于表，而不是基于库（因此存储引擎也称为表类型）。
  • 建表的时候可以指定存储引擎，否则自动使用默认引擎。

2.1、特点

2.1.1、InnoDB

InnoDB：兼顾高可靠性和高性能的通用存储引擎。

  • MySQL 5.5 之后的默认存储引擎。
  • 详细讲解：

特点

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

文件

  • InnoDB 引擎的每张表，都对应一个 ibd 表空间文件。
  • 存储表结构信息、数据和索引。

逻辑存储结构

image-20220313170930188

2.1.2、MyISAM

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

特点

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

文件

  • sdi：存储表结构信息
  • MYD：存储数据
  • MYI：存储索引

2.1.3、Memory

Memory：表数据存储在内存中。

由于受到硬件、断电等问题的影响，只能作临时表或缓存使用。

特点：存放再内存、支持 hash 索引。

文件：sdi 文件，存储表结构信息。

2.1.4、区别

特点 InnoDB MyISAM Memory
存储限制 64TB 有 有
事务 ? - -
外键 ? - -
锁机制 行级锁 表锁 表锁
B+tree 索引 ? ? ?
Hash 索引 - - ?
全文索引 ?（5.6 后） ? -
空间使用 高 低 N/A
内存使用 高 低 中等
批量插入速度 低 高 高

面试常问：InnoDB 和 MyISAM 的区别

主要从 事务、外键、锁 的角度，也可以从索引结构、存储限制等方面深入回答。

2.2、选择

原则：只有合不合适，没有好坏之分。

  • 根据应用系统的特点，选择合适的存储引擎。
  • 对于复杂的应用系统，可以选择多种存储引擎组合。
  • InnoDB
    • 对事务、并发性要求高
    • 除了读操作和插入操作，还有很多更新、删除操作。
  • MyISAM（很少使用，用 MongoDB 替代）
    • 对事务、并发性要求不高。
    • 以读操作和插入操作为主，很少更新、删除操作。
  • Memory（很少使用，用 Redis 替代）
    • 通常用于临时表和缓存，访问速度快
    • 缺点是对表的大小有限制，无法保障数据的安全性。

3、索引（!）

索引（index）：帮助 MySQL 高效获取数据的有序数据结构。

数据库系统既维护数据，还维护着实现了特定查找算法的数据结构（即索引，以某种方式引用数据）

优点

  1. 提高数据查询效率，降低数据库 IO 成本。
  2. 通过有序索引列对数据排序，降低排序成本，降低 CPU 消耗。

缺点：索引列也占用空间，导致表的更新速度降低。

3.1、数据结构

MySQL 索引在存储引擎层实现

  • 不同存储引擎的索引结构可能有所不同。
  • 在平时使用中，若没有特别指明，则是 B+ 树索引。

主要的索引结构

InnoDB MyISAM Memory
B+tree ? ? ?
Hash - - ?
R-tree - ? -
Full-text 5.6 之后? ? -

3.1.1、说明

InnoDB 使用优化的 B+ 树索引结构。

（先了解二叉树、B 树、标准的 B+ 树）

二叉树

  • 结构：每个结点存放：key（包括数据）、指针

  • 特点

    • 线性情况：假如元素是顺序插入，会形成单向链表，而不是二叉树。
    • 大数据量：层级多，检索速度慢。

  • 图例

    image-20220313182256080

  • 红黑树可以解决线性情况的问题，但是无法解决大数据量的问题。

B 树（多路平衡查找树）

  • 结构

    • 每个结点存放：key（包括数据）、指针
    • 相对于二叉树，每个结点可以存储多个数据，有多个分支

  • 图例：最大度数为 5 的 B 树

    image-20220313182924686

  • 特点

    1. 每个结点最多存储 4 个 Key、5 个指针。
    2. 当结点存储的 Key 数量达到度数，中间元素向上分裂。
    3. 每个结点都会存放指针和数据。

B+ 树（B 树的变体）

  • 结构：

    • 非叶子结点仅存放 Key（不包括数据） 和指针；叶子结点存储 Key（包括数据）。
    • 叶子结点形成一个单向链表。

  • 即：非叶子结点仅起到索引作用，具体数据存储在叶子结点中。

  • 图例：最大度数为 4 的 B+ 树

    image-20220313203627149

3.1.2、B+tree（MySQL）

MySQL 索引的数据结构对 B+ 树进行了优化

相比标准的 B+ 树，叶子节点形成双向循环链表。

image-20220313205325659

3.1.3、Hash

相当于数组 + 链表，参考

  • Memory：支持
  • InnoDB：不支持，但提供自适应哈希索引的机制。

通过 Hash 算法，将 key 值转换为 Hash 值，映射到 Hash 表的对应位置。

  • 哈希冲突：通过拉链法（形成链表）的方式解决。

  • 特点

    • 支持等值查询（=，in），不支持范围查询（between，> 等）
    • 不支持排序
    • 查询效率高，通常只需一次检索（前提是不存在哈希冲突）

  • 图示

    image-20220313204626865

面试题：为什么 InnoDB 存储引擎选择使用 B+tree 索引结构

（思路：对比二叉树、B 树、Hash）

  1. 二叉树可能出现线性情况；在数据量大的情况下，层级多，性能低。
  2. B 树的每个节点都会保存数据，导致一页中能存储的 Key 和指针数目减少、在数据量大的情况下，需要增加树的高度，导致性能降低。
  3. Hash 索引不支持范围查询和排序。

3.2、分类

3.2.1、MySQL 索引类型

MySQL 中索引的具体类型如下

含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建，只有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 查找文本关键词，而不是比较索引值 可以有多个 FULLTEXT

3.2.2、InnoDB 索引存储形式

在 InnoDB 存储引擎中，根据索引的存储形式分为以下类型

含义 特点
聚集索引
(Clustered)		 叶节点保存行数据
（数据与索引一起存储）		 有且只有一个
二级索引
(Secondary)		 叶节点关联主键
（数据与索引分开存储）		 0 或多个
image-20220314002356051

聚集索引的选取规则

  1. 存在主键：使用主键索引。
  2. 不存在主键：使用首个唯一索引。
  3. 不存在主键和唯一索引：自动生成一个 rowid 作为隐藏的聚集索引。

示例：索引查找过程

以 SELECT * FROM user WHERE name = 'Arm' 为例。

由于不是查询条件不是主键。因此先查二级索引，再查聚类索引。

  1. 先根据 name 字段的二级索引，进行匹配查询，查找到对应的主键的值。

  2. 回表查询：根据二级索引查到的主键，到聚类索引中查找对应的行数据。

    image-20220314003005448

3.4、语法

索引相关语法

  • 命名：一般为 idx_表名_列名
  • 联合索引：为多个列创建的索引。

  1. 创建索引

    • UNIQUE：唯一索引
    • FULLTEXT：全文索引
    • 不指定：常规索引
    CREATE [UNIQUE |FULLTEXT] INDEX 索引名
ON 表名 (列名, ... ) ;

  2. 查看索引

    SHOW INDEX FROM 表名;

  3. 删除索引

    DROP INDEX 索引名
ON 表名;

实例：根据不同情况，为 t_user 表创建索引

  1. name 姓名字段，可能重复。

    # 常规索引
CREATE INDEX idx_user_name
ON t_user(name);

  2. phone 手机号字段，非空且唯一。

    # 唯一索引
CREATE UNIQUE INDEX idx_user_phone
ON t_user(phone);

  3. 为 profession、age、status 字段创建索引。

    # 联合索引
CREATE INDEX idx_user_pro_age_sta
ON t_user(profession, age, status);

3.5、SQL 性能分析

3.5.1、执行频率

查询 SQL 语句的执行频率

  • 以得知数据库以查询为主，还是以增删改为主。
  • 若以查询为主，则可考虑设计索引。

通过执行频率可以得知，当前数据库的 SQL 整体执行情况。

语法：show [session|global] status

image-20220315155049796

3.5.2、慢查询日志

记录执行时间超过指定参数的 SQL 语句

  • slow_query_log：慢查询日志开关
  • long_query_time：指定时间

通过慢查询日志，可以定位执行效率低的 SQL 并优化。

慢查询日志

# 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log;

MySQL 配置文件（/etc/my.cnf）

# 开启MySQL慢查询日志
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒就会视为慢查询
long_query_time=2

重启 MySQL 服务器，完成配置。

3.5.3、profile

查看 SQL 语句的执行耗时

查看 MySQL 是否支持 profile 操作

SELECT @@have_profiling;

开启 profiling

SET profiling = 1;

3.5.4、explain

查看 SELECT 语句的执行信息

  • 在 DQL 语句之前使用 EXPLAIN 关键字
  • 也可以使用 DESC 关键字

执行信息中，各个字段的含义如下

含义 说明
id 序列号，即 DQL 的执行顺序 id 大的先执行；相同 id 从上到下执行
select_type 查询类型 SIMPLE：简单表，无需连接或者子查询
PRIMARY：主查询，即外层的查询
UNION：连接查询
SUBQUERY：子查询
type 连接类型 性能由好到差：NULL、system、const、eq_ref、ref、range、 index、all 。
possible_key 可能使用的索引 一个或多个，NULL 表示没有
key 实际使用的索引
key_len 索引中使用的字节数 值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好
rows 需执行查询的行数 innodb 引擎的表中，是一个估计值，可能并不准确
filtered 返回结果的行数占需读取行数的百分比 值越大越好
Extra 其他信息

3.6、使用

  • 单列索引：一个索引只包含单个列
  • 联合索引：一个索引包含多个列

3.6.1、最左前缀法则

最左前缀法则：条件查询的字段，包括从索引的最左列开始，不跳过索引的任一列。

  • 使用联合索引必须遵守最左前缀法则。
  • 如果跳过某一列，会导致该列之后的索引失效。

示例

# 创建一个联合索引
CREATE INDEX idx_prof_age_status
ON tb_user(profession, age, status);

不失效情况

EXPLAIN SELECT * FROM tb_user 
WHERE profession = '计算机科学' AND age = 17 AND status = '0';

EXPLAIN SELECT * FROM tb_user 
WHERE profession = '计算机科学' AND age = 17;

EXPLAIN SELECT * FROM tb_user 
WHERE profession = '计算机科学';

失效情况

# 完全失效：跳过profession，后面两列索引失效
EXPLAIN SELECT * FROM tb_user 
WHERE age = 17 AND status = '0';
# 完全失效：跳过profession和age，后面索引失效
EXPLAIN SELECT * FROM tb_user 
WHERE status = '0';

# 部分失效：跳过age，age之后的索引失效
EXPLAIN SELECT * FROM tb_user 
WHERE profession = '计算机科学' AND status = '0';

注意：只要最左列在查询条件中存在，与条件编写的先后顺序无关。

# 调换profession和age顺序，仍满足最左前缀法则
EXPLAIN SELECT * FROM tb_user 
WHERE age = 17 AND profession = '计算机科学';

3.6.2、覆盖索引

覆盖索引：查询使用了索引，且索引中包含（覆盖）了所有要查询的列。

  • 覆盖索引一般是联合索引
  • 应尽量使用覆盖索引，且减少使用 SELECT *

示例：SELECT id, name FROM tb_user WHERE name='Arm'

  • 回表查询：根据二级索引查到的主键，到聚类索引中查找对应的行数据。

    image-20220314003005448

  • 覆盖查询：根据二级索引查到主键 id，和 name 都已查出（该二级索引就是一个覆盖索引）

    image-20220315164900139

3.6.3、前缀索引

前缀索引：为字符串类型的字段建立的索引。

  • 如果不建立索引，字符串（varchar, text 等）的索引占用内存大
  • 浪费大量磁盘 IO，影响查询效率
  • 根据字符串的一部分前缀，设计索引。

语法：

CREATE INDEX 索引名
ON 表名(COLUMN(前缀长度))

前缀长度：根据索引的选择性来决定

  • 选择性：数据表中，不重复的索引值与记录总数的比值。
  • 索引查询效率与选择性成正比。
  • 唯一索引：选择性为 1，查询效率最高。

示例：

image-20220315170331488

3.6.4、SQL 提示

SQL 提示：在 SQL 语句中加入提示信息，提示 MySQL 做出相应动作。

  • USE INDEX：建议使用指定索引。
  • FORCE IDNEX：强制使用指定索引。
  • IGNORE INDEX：忽略指定索引。

示例

# 建议使用指定索引
EXPLAIN SELECT * FROM tb_user
USE INDEX(idx_user_pro)
WHERE profession = '软件工程';
# 强制使用指定索引
EXPLAIN SELECT * FROM tb_user
FORCE INDEX(idx_user_pro)
WHERE profession = '软件工程';
# 忽略指定索引
EXPLAIN SELECT * FROM tb_user
IGNORE INDEX(idx_user_pro)
WHERE profession = '软件工程';

3.7、失效情况

  1. 索引本身失效。

  2. 最左前缀法则：联合索引不遵守时，部分索引失效。

  3. 范围查询

    • 联合索引中某个列使用范围查询（ > 和 <），会导致联合索引中该列右侧的列索引失效。

    • 解决方案：在业务允许的情况下，改用 >= 和 <=

    • 示例：age 右侧的 status 列索引失效。

      # 联合索引
CREATE INDEX idx_prof_age_status
ON tb_user(profession, age, status);

# 范围查询：age
SELECT * FROM tb_user
WHERE profession = '计算机科学'
	AND age < 20
	AND status='0';

  4. 索引列运算：在索引列上进行运算。

    # 索引
CREATE INDEX idx_phone ON tb_user(phone);

# 导致idx_phone失效
SELECT * FROM tb_user
WHERE SUBSTRING(phone,8,4) = '6333';

  5. 字符串不加引号：数据库会隐式类型转换，将数字转换为字符串，但索引失效。

    # 正常
SELECT * FROM tb_user
WHERE phone = '15882288333';
# 索引失效
SELECT * FROM tb_user
WHERE phone = 15882288333;

  6. 头部模糊查询

    # 正常：尾部、中间模糊匹配
SELECT * FROM tb_user WHERE profession = '计算%';
SELECT * FROM tb_user WHERE profession = '计%程';

# 失效：头部模糊匹配
SELECT * FROM tb_user WHERE profession = '%工程';
SELECT * FROM tb_user WHERE profession = '%工%';

  7. or 连接条件：or 连接左右两侧的列都有索引才有效

  8. 数据分布影响：当查询记录数是表的大部分数据，MySQL 评估使用全表查询效率更高，索引失效。

3.8、设计原则

  1. 建立索引
    • 数据量较大，且查询比较频繁的表
    • 常作为查询条件（where）、排序（order by）、分组（group by）操作的字段
    • 尽量选择区分度高的列建立索引，尽量建立唯一索引。
  2. 前缀索引：字符串类型的字段，且字段长度较长。
  3. 联合索引：尽量减少单列索引。联合索引往往可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  4. 索引数量：要控制索引的数量。索引越多，维护开销越大，影响增删改的效率。
  5. NULL 值：如果索引列不能存储NULL值，在创建表时使用 NOT NULL约束。
mysql

相关

关于MySQL中的事务、提交（commit）、回滚（rollback）

MySQL索引类型

Windows7环境，Dbeaver 安装连接Mysql5.7

linux 恢复mysql 误删除数据

mac 安装mysql workbench8.0.23闪退。

Linux-Mysql 遗忘密码如何解决？

mysql数据库死锁的产生原因及解决办法

linux安装mysqlclient库

【Mysql学习】django.db.utils.OperationalError: (1045:Access denied for user 'roo

mysql改变配置文件后启动报错

mysql binlog恢复命令

MySQL left join、right join和join的区别

标签