05_索引、执行计划与存储引擎

课程目标

[ ] 了解多表关系和关联查询

[ ] 了解多表间的约束

[ ] 知道数据的存储引擎有哪些及区别

[ ] 知道.ibd和.myd两个文件的区别

[ ] 掌握开启慢查询日志

[ ] 掌握添加索引

[ ] 掌握explain或desc分析sql语句

---

一、MySQL体系结构

面试题：MySQL底层，一条SQL语句的执行流程/原理？

答：经过四层，分别是连接层（连接池）、服务层（查询缓存、分析器、优化器、执行器）、引擎层、存储层。

1.1 客户端(连接者)

MySQL的客户端可以是某个客户端软件（DataGrip）

MySQL的客户端可以是不同的编程语言(Python/Java等)编写的应用程序

MySQL的客户端还可以是一些API的接口

查看当前的连接者show processlist

1.2 连接层

主要作用：管理和缓冲用户连接，为客户端请求做连接处理；身份认证等。

面试过程：进程和线程？ 进程：一个应用软件，启动后往往会产生1个甚至多个进程，进程需要消耗一定的计算机资源（CPU、内存、磁盘、网络），适合CPU密集型应用（大量的计算程序，需要消耗资源）,进程之间的数据是不共享的。 线程：一个进程可以产生多个线程，线程不能单独存在，必须依赖进程。所有线程共享进程资源，线程启动、停止快，资源开销小。适合IO密集型应用（文件操作、网络爬虫、数据库连接）进程是资源分配的基本单位，而线程是操作系统进行CPU调度和执行的最小单位。

连接层中的缓存池，就是为了优化数据库连接而设置的机制，专门用来缓存和复用已建立的连接。其主要作用是：

1. 减少连接开销：避免每次新建连接带来的系统负担，提升性能。 2. 提升响应速度：连接池里有现成的连接，随取随用，减少等待时间。 3. 优化资源利用：通过控制最大连接数，防止过多连接耗尽系统资源。

工作流程很简单：创建连接→用完放回池中→再次复用，保持高效循环。

总结就是：连接池通过缓存连接，减少开销、加快响应、合理利用资源，让数据库更高效应对高并发。

连接池配置相关参数：

# 模糊查看
root@localhost [(none)]> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

# 查看当前mysql中系统变量
# mysql中变量调用用 @@变量名
root@localhost [(none)]> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

# 调整在 /etc/my.cnf中的  [mysqld] 模块下进行对应的配置
vi /etc/my.cnf

[mysqld]
max_connections=100
thread_cache_size=4
-- 在 MySQL 8 中，参数名可能略有不同。例如，wait_timeout 应该改为 interactive_timeout 和 wait_timeout
wait_timeout=300
interactive_timeout=300

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

1.3 服务层

主要作用：接受用户的SQL请求，查询分析，权限处理，优化，结果缓存等。

1.4 引擎层（重要）

什么是存储引擎？

1）存储引擎说白了就是如何管理操作数据(存储数据、如何更新、查询数据等)的一种方法和机制。

2）在MySql数据库中提供了多种存储引擎，各个存储引擎的优势各不一样，现在mysql中推荐使用Innodb

3）用户可以根据不同需求为数据表选择不同的存储引擎，也可以根据自己需要编写自己的存储引擎。

4）甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

-- 查看当前mysql实例中支持的存储引擎种类
show engines;

-- 查看当前默认的存储引擎
root@localhost [(none)]> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

MyISAM与InnoDB 引擎的对比表：

特性	MyISAM	InnoDB
事务支持	不支持	支持（ACID特性）
外键支持	不支持	支持
锁机制	表级锁	行级锁
适用场景	读多写少的应用，查询性能好	高并发写操作的应用
崩溃恢复	数据损坏风险高，无法自动恢复	自动恢复，数据更安全,双写(dw double write)
存储结构	每个表有单独的文件	表和索引存储在表空间中
性能	查询性能较好，但不支持事务处理	支持事务处理，写操作性能较高

面试：你用过哪些数据库引擎？各自特点？

答：早期MySQL5.5版本使用过MyISAM引擎，后期MySQL5.7、MySQL8.0等等都是使用InnoDB引擎，偶尔也了解MEMORY引擎。

① MyISAM引擎，擅长数据查询，支持较好的索引优化、数据压缩、支持表级锁以及全文索引技术，安全性相对于InnoDB略差一些。

索引优化 => 主键索引（图书目录），有索引，查询速度会更快

数据压缩 => 减少存储空间占用

表级锁 => 只能进行表级锁，就是锁表时，要锁定整个数据表，在这个过程中，这个表只能进行查询操作，而不能进行增删改等操作，但是粒度太大，对并发有一定的影响。

全文索引 => 从一篇文章中搜索指定内容，类似模糊查询，更加强大一些。

② InnoDB引擎，擅长数据安全，支持支持行级锁，支持事务处理，支持外键约束等等，强调安全性。

行级锁 => 只会对某一行进行锁定，不会全表锁定，粒度更细，并发能力更强。

事务处理 => 一种数据安全策略，保证数据安全

外键约束

③ Memory引擎，擅长数据缓存，加快数据查询，但是由于数据放置于内存，所以安全性没有MyISAM以及InnoDB好。

扩展：InnoDB事务处理

应用场景：银行转账（最典型）

我的银行卡：1.00

itwu银行卡：2000.00

发生一系列操作：① itwu发起转账，扣款1000，余额-1000 ② 银行接收任务，处理（ATM） ③ 我的银行卡接收到1000，余额+1000

---

update bank set money=money-1000 where name = "itwu"

交易系统停电了

update bank set money=money+1000 where name = "我"

---

操作步骤：事务处理配合Python/Java程序一起使用，就是把所有要执行的SQL语句当做一个整体，要么全部成功，要么全部失败。

要想完成事务操作，你的创建表时所用的存储引擎，必须且一定要为 innodb

show create table 表名;

① 开启事务处理功能 => start transaction;

② 执行一系列的SQL语句（多条）

update bank set money=money-1000 where name = "itwu"

update bank set money=money+1000 where name = "我"

③ 判断SQL语句是否全部执行成功，如果成功则提交事务 => commit; 失败，则回滚事务 => rollback;。

create table bank (
    id int primary key auto_increment,
    name varchar(20),
    money int not null default 1
);

show create table bank;

insert into bank (name,money) values ('itwu',2000),('我',1);

-- 代码示例：
-- 开始一个事务
start transaction;
update bank set money=money-1000 where name = "itwu";
update bank set money=money+1000 where name = "我";
-- 如果事务中没有问题，则提交commit    如果发现有问题则可以 rollback
commit;

1.5 存储层（物理层）

核心作用：物理层负责与底层的操作系统交互，将数据存储到磁盘上，并确保数据的物理安全。

默认存储在/usr/local/mysql/data数据目录下

工作方式：

将数据以物理文件的形式存储在磁盘上（如表空间文件、数据文件、日志文件等）。

通过文件系统与操作系统进行交互，管理数据的读写、缓存、索引文件等。

二、数据引擎

2.1 MyISAM引擎

-- 永久修改默认存储引擎方案
vi /etc/my.cnf
[mysqld]

# 默认就是innodb
#default_storage_engine=myisam
default_storage_engine=innodb

------------------------- 在创建表时来指定存储引擎  engine ----------------------------------
root@localhost [test3]> create database test5;
Query OK, 1 row affected (0.00 sec)

root@localhost [test3]> use test5;
Database changed

-- 创建一个表并指定它的存储引擎为myisam
root@localhost [test5]> create table user1(id int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

2.2 InnoDB引擎

------------------------- 在创建表时来指定存储引擎  engine ----------------------------------
root@localhost [test3]> create database test5;
Query OK, 1 row affected (0.00 sec)

root@localhost [test3]> use test5;
Database changed

-- 创建一个表并指定它的存储引擎为innodb  如果默认mysql它的引擎就为innodb，所以你可以不指定
root@localhost [test5]> create table user2(id int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

-- 查看一下表空间大小
root@localhost [test5]> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

InnoDB引擎：

.ibd：每个表都会有一个独立的 .ibd 文件，存储该表的表数据和索引。

ibdata1：用于存储全局的表空间、数据字典和事务日志等。

表空间：为了方便扩容，类似于磁盘中的LVM技术。

共享表空间

独立表空间 -- 数据所在位置 mysql8.x 默认就是开启了独立表空间 innodb_file_per_table=1|ON

临时表空间

表空间：MySQL 的 InnoDB 存储引擎采用表空间来管理数据，可以把它看作是数据库内容的物理存储仓库。用这样的方式为了更好的去扩容。

vi /etc/my.cnf
------------------------------
[mysqld]

#innodb_file_per_table=1
# 建议最多不要超过3个
innodb_data_file_path=ibdata1:12M;ibdata2:100M:autoextend

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

vi /etc/my.cnf

[mysqld]
# 每个重做日志文件的大小
innodb_log_file_size = 50M
innodb_redo_log_capacity=100M
# 重做日志组中日志文件的个数 2（默认值）
innodb_log_files_in_group = 2

# https://blog.csdn.net/weixin_72610956/article/details/154077839
innodb_log_group_home_dir = /usr/local/mysql/data  # redo log 文件存放路径  data/#innodb_redo  目录下面

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

三、慢查询日志

3.1 启动慢查询日志（重点）

SQL语句：执行过程中，有快有慢，找出慢查询SQL。

作用：慢查询日志记录了所有执行时间超过设定阈值的查询（300ms、1s、5s根据不同业务场景而定），帮助你发现需要优化的慢查询SQL语句，辅助优化。

如何启用：

vi /etc/my.cnf

[mysqld]
...(省略其他原有信息)

# 开启慢查询日志
slow_query_log=1
# 设置超过 1 秒的查询被记录
long_query_time=1
# 指定慢查询日志文件存放路径
slow_query_log_file=/usr/local/mysql/mysql-slow.log

# 记录未使用索引的查询（可选）
log_queries_not_using_indexes=1

# 通用日志  开启会他们记录所有的sql操作，建议不要在生产环境中去开启，只要测试环境中去开启，方便调试
general_log=1
general_log_file=/usr/local/mysql/data/general.log

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

systemctl restart mysqld

# Time: 2026-05-26T10:00:38.070872Z
# User@Host: root[root] @ localhost []  Id:     8
# 执行的多久
# Query_time: 0.419292  Lock_time: 0.000002 Rows_sent: 655360  Rows_examined: 655360
use test5;
SET timestamp=1779789637;
# 语句
select * from user2;

命令作用：

使用 systemctl 对 mysqld 服务执行 restart 操作：启动、停止、重启、查看状态或设置开机自启。

执行结果：

成功后会对 mysqld 服务执行 restart 操作；restart 会造成短暂中断，enable 影响开机自启。

关键参数：

注意事项：

添加200万条数据到数据表中，做测试（不要求掌握以下，只是为了做测试）

);

commit;


做一个查询，然后查看效果

简单来说：explain/desc执行计划就是用于分析一个SQL语句如何执行的，核心作用：帮助我们提升SQL查询效率，加快查询速度。

学不认识的汉字，会去词典当中寻找

如何使用：




![](/assets/feishu-images/55a0040358680f26eb082dc4.png)



![](/assets/feishu-images/40abcd73ba0c58bc139624a4.png)

> 
> 


+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | tb_people | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1992637 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | tb_people | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1992637 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | tb_people | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1992637 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_people | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   20 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_people | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   38 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ +----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | tb_people | NULL | index | NULL | PRIMARY | 4 | NULL | 1992637 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

+----+-------------+-----------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ +----+-------------+-----------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | tb_people | NULL | index | NULL | name | 203 | NULL | 1992637 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+------+---------+------+---------+----------+-------------+




+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_people | NULL       | range | age_index     | age_index | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_people | NULL       | range | name          | name | 203     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ +----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb_people | NULL | ref | age_index | age_index | 5 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+



今后在工作中，如果要通过条件来搜索数据，能用主键去过滤就一定要用主键，这样的速度是最快的。

+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_people | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

同样的，在慢日志中，也可以看到记录

索引类型普通索引唯一索引联合索引







----------------------------------------------------------------------
);


+------------+-------------+------+-----+---------+----------------+
+------------+-------------+------+-----+---------+----------------+
+------------+-------------+------+-----+---------+----------------+



+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

联合索引，其实是普通索引的扩展。通常适用于加快查询多个字段的结果。

------------------------------------------------------------------------ );

+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | person2 | NULL | range | name_age | name_age | 87 | NULL | 1 | 100.00 | Using index condition | +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+ +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | person2 | NULL | ref | name_age | name_age | 82 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | person2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | person2 | NULL | ALL | name_age | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+---------+------


**索引失效**

+-------+-------------+------+-----+---------+-------+
+-------+-------------+------+-----+---------+-------+
+-------+-------------+------+-----+---------+-------+

+----+-------------+-----------+------------+------+----------------+------+---------+-------+------+----------+-------------+
+----+-------------+-----------+------------+------+----------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_people | NULL       | ref  | name,age_index | name | 203     | const |    1 |    50.00 | Using where |
+----+-------------+-----------+------------+------+----------------+------+---------+-------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------+------+---------+------+---------+----------+-------------+
+----+-------------+-----------+------------+------+----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | tb_people | NULL       | ALL  | name,age_index | NULL | NULL    | NULL | 1992637 |    33.33 | Using where |
+----+-------------+-----------+------------+------+----------------+------+---------+------+---------+----------+-------------+

+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_people | NULL       | range | name          | name | 203     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | tb_people | NULL       | ALL  | name          | NULL | NULL    | NULL | 1992637 |    50.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+


![](/assets/feishu-images/111fec864f88be6810e50113.png)

![](/assets/feishu-images/0e13e01f832a6cf176801518.png)

这个输出告诉我们：


**总结对比**

key_ken长度说明：





---


在运维环境中，发现MySQL运行缓慢，如何去解决？

答：





}

注意事项：

注意：适合执行时间较长的SQL语句


分析重点：

**Command**：表示查询的状态，比如`Query`表示正在执行查询，`Sleep`表示连接空闲。

**Time**：表示查询的执行时间，时间较长的查询可能是性能瓶颈。



这些状态是SQL语句在执行过程中的不同阶段。如果某个阶段耗时过长，往往指向了具体的性能瓶颈。

![](/assets/feishu-images/47093f92ee0efc86ec486233.png)

本文由飞书云文档同步生成。涉及命令、SQL、配置示例时，请以飞书源文档和实际环境执行结果为准。