MySQL8 底层架构与调优
任务背景
随着用户量和数据量的快速增长，系统的MySQL数据库面临着越来越多的查询性能问题，特别是在高并发情况下，查询响应时间显著增加，影响了系统的稳定性和用户体验。运维团队的主要任务是通过SQL查询的监控与优化，确保数据库在大数据量和高并发环境下仍然能够保持良好的性能表现。
任务拆解
-
MySQL体系结构（了解）
-
查询性能监控与分析（重点） => 优化手段
-
- 启用MySQL的慢查询日志，定期分析执行时间较长的SQL查询，识别出性能瓶颈。
- 使用
EXPLAIN命令分析复杂查询的执行计划，识别全表扫描等问题。 - 通过
SHOW PROCESSLIST工具，实时监控正在运行查询，找出占用系统资源较多的SQL语句，分析其影响。
任务目标
- 通过慢查询日志和性能监控工具，识别并分析查询性能问题。
一、MySQL 体系结构
面试题
MySQL底层，一条SQL语句的执行流程/原理？
答：经过四层，分别是连接层（连接池）、服务层（查询缓存、分析器、优化器、执行器）、引擎层、存储层。
**数据：**相当于以前的汉语词典中的汉字
**索引：**相当于以前的汉语词典中的目录
**全表扫描：**没有经过索引的操作，叫做全表扫描
走索引（主键索引、唯一索引、普通索引、前缀索引、联合索引...）
1、客户端
2、服务端
1、连接层
2、服务层
查询缓存
1、分析器（词法、语法分析）
优化器
2、执行器
3、引擎层（重点）
4、存储层
1、客户端(连接者)
- MySQL的客户端可以是某个客户端软件(Navicat/DataGrip)
- MySQL的客户端可以是不同的编程语言(Python/Java等)编写的应用程序
- MySQL的客户端还可以是一些API(Application Programming Interface)的接口
2、连接层
主要作用：管理和缓冲用户连接，为客户端请求做连接处理，身份认证等。
面试问题：什么是进程和线程？
**进程：**一个应用软件，启动后往往会产生1个甚至多个进程，进程需要消耗一定的计算机资源（CPU、内存、磁盘、网络），适合CPU密集型应用（大量的计算程序，需要消耗资源）。
**线程：**一个进程可以产生多个线程，线程不能单独存在，必须依赖进程。所有线程共享进程资源，线程启动、停止快，资源开销小。适合IO密集型应用（文件操作、网络爬虫、数据库连接）。
连接层中的缓存池，就是为了优化数据库连接而设置的机制，专门用来缓存和复用已建立的连接。
其主要作用是：
- 减少连接开销：避免每次新建连接带来的系统负担，提升性能。
- 提升响应速度：连接池里有现成的连接，随取随用，减少等待时间。
- 优化资源利用：通过控制最大连接数，防止过多连接耗尽系统资源。
工作流程很简单：创建连接→用完放回池中→再次复用，保持高效循环。
总结就是：连接池通过缓存连接，减少开销、加快响应、合理利用资源，让数据库更高效应对高并发。
连接池配置相关参数：
- max_connections：指定MySQL可以同时处理的最大连接数，控制最大连接数目。
- wait_timeout：定义一个连接在闲置状态下最多可以等待的时间，超过这个时间将被关闭。
- thread_cache_size：控制线程缓存池的大小，以缓存空闲线程，避免频繁创建和销毁线程所带来的开销。
thread_cache_size：根据系统并发情况设置，通常设置为CPU核心数的2倍（超线程），以便高效处理并发请求；超线程是一种“锦上添花”的技术，它通过更充分地利用CPU内部的闲置资源来提升多线程工作负载的处理能力。在配置像MySQL这样的并发密集型软件时，按照逻辑处理器的数量（即物理核心数 * 2）来设置相关参数是一个非常合理的起点。
3、服务层
主要作用：接受用户的SQL请求，查询分析，权限处理，优化，结果缓存等。
重要变化：
在 MySQL 5.7 及之前的版本中，确实存在 查询缓存（Query Cache） 功能，用来缓存 SELECT 查询的结果集。
- 优点：对于完全相同的查询语句，可以直接从缓存返回结果，避免重新解析和执行，提高性能。
- 缺点：一旦涉及写操作（INSERT/UPDATE/DELETE 等），相关缓存就会失效，导致频繁的缓存清理和锁竞争，反而可能降低性能。
因此，从 MySQL 8.0 开始，官方彻底移除了查询缓存，理由是：
- 查询缓存机制和 InnoDB Buffer Pool、现代应用的缓存架构（Redis、Memcached 等）相比，效果有限。
- 查询缓存引发的全局锁竞争会成为性能瓶颈。
- 社区用户大多推荐使用外部缓存，而不是依赖数据库内部的 Query Cache。
在 MySQL 8.0 中，如果你要提升查询性能，一般会用：
- InnoDB Buffer Pool（缓存数据和索引页）
- 索引优化（BTREE、HASH 等）
- 外部缓存中间件（Redis、Memcached）
- Proxy 层缓存（如 ProxySQL）
| 对比项 | MySQL 5.7 及之前 | MySQL 8.0 |
|---|---|---|
| 是否存在查询缓存 | ✅ 存在 | ❌ 已移除 |
| 参数控制 | query_cache_size、query_cache_type、query_cache_limit 等 | 不支持，参数被删除 |
| 工作机制 | 缓存 SELECT 查询结果，如果再次执行完全相同的语句，直接返回结果 | 依赖 InnoDB Buffer Pool（缓存数据页、索引页），不再有结果级缓存 |
| 失效机制 | 表一旦有写操作（INSERT/UPDATE/DELETE），相关缓存全部失效 | 无查询缓存，无失效问题 |
| 性能影响 | - 小数据量、读多写少场景下有提升 - 但在写多的场景会频繁失效，反而拖慢性能（全局锁） | 性能更稳定，避免了 Query Cache 带来的锁竞争 |
| 替代方案 | 官方建议关闭（query_cache_type=0） | 使用外部缓存（Redis、Memcached）、ProxySQL 缓存、或依赖 InnoDB Buffer Pool |
记忆要点：
- MySQL 5.7 有，但不推荐用（常常建议禁用）。
- MySQL 8.0 已彻底移除（完全没有）。
- 现代方案：索引 + InnoDB Buffer Pool + 外部缓存（Redis）。
4、引擎层（重要）
- 什么是存储引擎？
1）存储引擎说白了就是 如何管理操作数据(存储数据、如何更新、查询数据等)的 一种方法和机制。
2）在MySQL数据库中提供了多种存储引擎，各个存储引擎的优势各不一样 => show engines
3）用户可以根据不同需求为数据表选择不同的存储引擎，也可以根据自己需要编写自己的存储引擎。
4）甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。
mysql> show engines;
**XA：XA （eXtended Architecture 扩展架构）**是X/Open组织提出的分布式数据库的一种协议标准；
**Savepoints：**保存点，是事务中的标记点，允许部分回滚（回滚到指定标记，而非整个事务），可以更精准地进行事务记录和回滚。
最常用的存储引擎是InnoDB和MyISAM
| 存储引擎 | 描述 |
|---|---|
| InnoDB（MySQL5.6版本及以后） | 支持拥有ACID特性事务（Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性））的存储引擎，并且提供行级的锁定，支持外键、应用广泛。侧重于数据安全，默认引擎。 |
| MyISAM（MySQL5.5及之前版本） | 查询速度快，有较好的索引优化和数据压缩技术；但不支持事务、不支持外键约束；适用于读多写少的应用场景。 |
| NDB | 用于MySQL Cluster的集群存储引擎，提供数据层面的高可用性。 |
| MEMORY | 存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。 |
| BLACKHOLE | 黑洞存储引擎，写入的任何数据都会消失，应用于主备复制中的分发主库（中继slave）。 |
面试：你用过哪些数据库引擎？各自有哪些特点？
答：早期MySQL5.5版本使用过MyISAM引擎，后期MySQL5.7、MySQL8.0等等都是使用InnoDB引擎，偶尔也了解NDB、MEMORY引擎。
① MyISAM引擎，擅长数据查询，支持较好的索引优化、数据压缩、支持表级锁以及全文索引技术，安全性相对于InnoDB略差一些。
索引优化 => 主键索引（图书目录），有索引，查询速度会更快。
数据压缩 => 减少存储空间占用。
表级锁 => 只能进行表级锁，就是锁表时，要锁定整个数据表，在这个过程中，这个表只能进行查询操作，而不能进行增删改等操作，粒度太大，对并发有一定的影响。
全文索引 => 从一篇文章中搜索指定内容，类似模糊查询，更加强大一些。
② InnoDB引擎，擅长数据安全，支持支持行级锁，支持事务处理，支持外键约束等等，强调安全性。
行级锁 => 只会对某一行进行锁定，不会全表锁定，粒度更细，并发能力更强。
事务处理 => 一种数据安全策略，保证数据安全。
外键约束
③ Memory引擎，擅长数据缓存，加快数据查询，但是由于数据放置于内存，所以安全性没有MyISAM以及InnoDB好。
扩展：InnoDB事务处理
应用场景：银行转账（最典型）
我的银行卡：0.10
李文凯银行卡：2000.00
发生一系列操作：① 李文凯发起转账，扣款1000，余额-1000 ② 银行接收任务，处理（ATM） ③ 我的银行卡接收到1000，余额+1000
update bank set money=money-1000 where name = "李文凯";
update bank set money=money+1000 where name = "我";
假如ATM停电了会怎样？
操作步骤：事务处理把所有要执行的SQL语句当做一个整体，要么全部成功，要么全部失败。
① 开启事务处理功能 => start transaction;
② 执行一系列的SQL语句（多条）
update bank set money=money-1000 where name = "李文凯";
update bank set money=money+1000 where name = "我";
③ 判断SQL语句是否全部执行成功，如果成功则提交事务 => commit; 失败，则回滚事务 => rollback;。
-- 代码示例：
start transaction;
update bank set money=money-1000 where name = "李文凯";
update bank set money=money+1000 where name = "我";
commit;
InnoDB事务处理实战演示
-- 创建数据库
DROP DATABASE IF EXISTS bank_system;
CREATE DATABASE bank_system;
USE bank_system;
-- 创建银行账户表
CREATE TABLE IF NOT EXISTS bank (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
money DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入初始数据
INSERT INTO bank (name, money) VALUES
('李文凯', 2000.00),
('我', 0.10);
-- 查看初始余额
SELECT * FROM bank;
-- 开始事务处理
START TRANSACTION;
-- 执行转账操作
UPDATE bank SET money = money - 1000 WHERE name = '李文凯';
UPDATE bank SET money = money + 1000 WHERE name = '我';
-- 模拟查看转账过程中的余额变化（此时其他会话还看不到这些变化）
SELECT * FROM bank;
-- 提交事务（如果所有操作都成功）
COMMIT;
-- 查看最终余额
SELECT * FROM bank;
-- 场景1：模拟停电故障（在第二个UPDATE之前停止）
START TRANSACTION;
UPDATE bank SET money = money - 1000 WHERE name = '李文凯';
-- 查看当前余额
SELECT * FROM bank;
-- 假设这里ATM停电了，事务没有COMMIT;提交
-- 由于没有提交，我们可以回滚来撤销操作
ROLLBACK;
-- 验证余额是否恢复
SELECT * FROM bank;
-- 场景2：完整的成功转账
START TRANSACTION;
UPDATE bank SET money = money - 1000 WHERE name = '李文凯';
UPDATE bank SET money = money + 1000 WHERE name = '我';
-- 提交事务
COMMIT;
-- 验证转账结果
SELECT * FROM bank;
mysql> -- 创建数据库和表
mysql> DROP DATABASE IF EXISTS bank_system;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE bank_system;
Query OK, 1 row affected (0.00 sec)
mysql> USE bank_system;
Database changed
mysql>
mysql> -- 创建银行账户表
mysql> CREATE TABLE IF NOT EXISTS bank (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> money DECIMAL(10,2) NOT NULL DEFAULT 0.00,
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> -- 插入初始数据
mysql> INSERT INTO bank (name, money) VALUES
-> ('李文凯', 2000.00),
-> ('我', 0.10);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 查看初始余额
mysql> SELECT * FROM bank;
+----+-----------+---------+---------------------+
| id | name | money | created_at |
+----+-----------+---------+---------------------+
| 1 | 李文凯 | 2000.00 | 2025-11-24 11:00:59 |
| 2 | 我 | 0.10 | 2025-11-24 11:00:59 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)
mysql> -- 开始事务处理
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> -- 执行转账操作
mysql> UPDATE bank SET money = money - 1000 WHERE name = '李文凯';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE bank SET money = money + 1000 WHERE name = '我';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> -- 模拟查看转账过程中的余额变化（此时其他会话还看不到这些变化）
mysql> SELECT * FROM bank;
+----+-----------+---------+---------------------+
| id | name | money | created_at |
+----+-----------+---------+---------------------+
| 1 | 李文凯 | 1000.00 | 2025-11-24 11:00:59 |
| 2 | 我 | 1000.10 | 2025-11-24 11:00:59 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)
mysql> -- 提交事务（如果所有操作都成功）
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> -- 查看最终余额
mysql> SELECT * FROM bank;
+----+-----------+---------+---------------------+
| id | name | money | created_at |
+----+-----------+---------+---------------------+
| 1 | 李文凯 | 1000.00 | 2025-11-24 11:00:59 |
| 2 | 我 | 1000.10 | 2025-11-24 11:00:59 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)
mysql> -- 场景1：模拟停电故障（在第二个UPDATE之前停止）
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> UPDATE bank SET money = money - 1000 WHERE name = '李文凯';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> -- 查看当前余额
mysql> SELECT * FROM bank;
+----+-----------+---------+---------------------+
| id | name | money | created_at |
+----+-----------+---------+---------------------+
| 1 | 李文凯 | 0.00 | 2025-11-24 11:00:59 |
| 2 | 我 | 1000.10 | 2025-11-24 11:00:59 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
+----+-----------+---------+---------------------+
| id | name | money | created_at |
+----+-----------+---------+---------------------+
| 1 | 李文凯 | 1000.00 | 2025-11-24 11:00:59 |
| 2 | 我 | 1000.10 | 2025-11-24 11:00:59 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)
mysql> -- 场景2：完整的成功转账
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> UPDATE bank SET money = money - 1000 WHERE name = '李文凯';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE bank SET money = money + 1000 WHERE name = '我';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> -- 提交事务
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> -- 验证转账结果
mysql> SELECT * FROM bank;
+----+-----------+---------+---------------------+
| id | name | money | created_at |
+----+-----------+---------+---------------------+
| 1 | 李文凯 | 0.00 | 2025-11-24 11:00:59 |
| 2 | 我 | 2000.10 | 2025-11-24 11:00:59 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)
mysql>
注意：START TRANSACTION; 与 autocommit 模式
**在 MySQL 中，一个事务的生命周期是从 START TRANSACTION 开始，到 COMMIT 或 ROLLBACK 结束。**一旦事务结束（无论提交还是回滚），连接就自动回到 autocommit 模式（除非你手动关闭了 autocommit）。
-- 查看当前 autocommit 状态
SELECT @@autocommit; -- 返回 1 表示开启，0 表示关闭
-- 关闭自动提交
SET autocommit = 0;
5、存储层（物理层）
核心作用：物理层负责与底层的操作系统交互，将数据存储到磁盘上，并确保数据的物理安全。
默认存储在/export/server/mysql/data数据目录下
工作方式：
- 将数据以物理文件的形式存储在磁盘上（如表空间文件、数据文件、日志文件等）。
- 通过文件系统与操作系统进行交互，管理数据的读写、缓存、索引文件等。
总结
- MySQL体系结构分为哪几层？四层（连接层、服务层（分析器、优化器、执行器）、引擎层、物理层）
- 每一层是如何工作的？
- MySQL8.0 版本以后默认的存储引擎是哪个？有什么特点？除了这种引擎，你还了解哪些其他引擎？（至少说出2-3种）
扩展：生产环境下，MySQL到底应该如何配置呢？
答：这里说的配置主要是针对/etc/my.cnf（MySQL优化、处理等等都是由my.cnf决定的）
主库
cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
server-id = 1
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_rows_query_log_events=ON
expire_logs_days=7
EOF
解释说明
# 使用cat命令创建/覆盖MySQL主配置文件 /etc/my.cnf
# <<EOF 开始读取输入内容，直到遇到EOF结束并写入文件
cat >/etc/my.cnf<<EOF
# MySQL 服务端进程（mysqld）核心配置区域
# 所有数据库运行、端口、路径、主从复制参数都在此配置
[mysqld]
# MySQL 服务监听的端口号，默认 3306
port=3306
# MySQL 安装根目录（bin、lib、plugin 等程序文件所在路径）
basedir=/export/server/mysql
# MySQL 数据存储目录（数据库文件、索引、日志等数据存放位置）
datadir=/export/server/mysql/data
# MySQL 本地套接字文件（本机客户端连接 MySQL 使用的文件）
socket=/tmp/mysql.sock
# 数据库服务端默认使用的字符集：utf8
character_set_server=utf8
# 数据库服务端默认排序规则：utf8_unicode_ci（大小写不敏感排序）
collation-server=utf8_unicode_ci
# 主从架构中唯一标识，主库固定为 1，从库必须不同（2/3/4...）
server-id = 1
# 开启二进制日志（主库必备，记录所有数据变更，用于同步给从库）
log_bin=mysql-bin
# 二进制日志格式：ROW 行模式（主从复制最安全、最推荐，无 SQL 注入风险）
binlog_format=ROW
# 开启 GTID 全局事务ID（主从复制自动定位，无需手动指定文件和位置）
gtid_mode=ON
# 强制 GTID 事务一致性，禁止执行不支持 GTID 的语句，保证复制安全稳定
enforce_gtid_consistency=ON
# 在 ROW 模式二进制日志中记录原始 SQL 语句，方便运维排查问题
binlog_rows_query_log_events=ON
# 二进制日志自动过期清理时间：7 天
# 超过 7 天的 binlog 会自动删除，防止磁盘占满
expire_logs_days=7
# 结束配置输入，将所有内容写入 /etc/my.cnf
EOF
从库
cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
server-id = 2
relay_log = relay-bin
read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
EOF
解释说明
# 1. 使用 cat 命令创建/覆盖 /etc/my.cnf 文件
# <<EOF 表示开始读取输入，直到遇到 EOF 为止
# /etc/my.cnf 是 MySQL 全局配置文件（Linux 系统默认路径）
cat >/etc/my.cnf<<EOF
# ==============================================
# [mysqld] 段：MySQL 服务端（mysqld）核心配置区
# 所有服务启动、路径、字符集、主从复制参数都写在这里
# ==============================================
[mysqld]
# MySQL 服务监听的端口，默认就是 3306
port=3306
# MySQL 安装根目录（二进制文件、bin、lib 等所在路径）
basedir=/export/server/mysql
# MySQL 数据存放目录（库表文件、ibd 文件、redo/undo 日志都在这里）
datadir=/export/server/mysql/data
# MySQL 本地套接字文件（本机连接 MySQL 时使用，不用走 TCP/IP）
socket=/tmp/mysql.sock
# 服务端默认字符集：utf8（注意：生产建议用 utf8mb4 支持 emoji）
character_set_server=utf8
# 服务端默认排序规则：utf8_unicode_ci（大小写不敏感排序）
collation-server=utf8_unicode_ci
# ==============================================
# 以下是 MySQL 主从复制 / GTID 相关配置
# 这台机器当前配置为 从库（slave）
# ==============================================
# 集群唯一 ID，主从架构中必须唯一，不能和其他节点重复
server-id = 2
# 中继日志文件名前缀（从库接收主库 binlog 后写入的日志）
relay_log = relay-bin
# 开启从库只读（除超级管理员外，普通用户无法写入数据）
# 作用：防止从库被误写入数据，保证主从一致性
read_only = ON
# 开启 GTID 模式（Global Transaction ID，全局事务ID）
# 主从复制不再依赖 binlog 文件名和位置，自动同步更可靠
gtid_mode = ON
# 强制 GTID 一致性（禁止使用不支持 GTID 的语句，保证复制安全）
enforce_gtid_consistency = ON
# 将主库连接信息（master.info）存到系统表（mysql.slave_master_info）
# 替代传统文件存储，更安全、更易管理
master_info_repository = TABLE
# 将从库中继日志应用位置信息存到系统表（mysql.slave_relay_log_info）
# 崩溃恢复更可靠
relay_log_info_repository = TABLE
# 结束配置输入，写入文件
EOF
二、数据引擎详解
MySQL体系结构
1、存储引擎层
存储引擎层：简单来说，就是数据的存储方式。在MySQL中，我们可以使用 **show engines;** 查看当前数据库版本支持哪些引擎，常见的数据存储引擎：InnoDB、MyISAM等等。
MyISAM与InnoDB 引擎的对比表：
| 特性 | MyISAM | InnoDB |
|---|---|---|
| 事务支持 | 不支持 | 支持（ACID特性） |
| 外键支持 | 不支持 | 支持 |
| 锁机制 | 表级锁 | 行级锁 |
| 适用场景 | 读多写少的应用，查询性能好 | 高并发写操作的应用 |
| 崩溃恢复 | 数据损坏风险高，无法自动恢复 | 自动恢复，数据更安全 |
| 存储结构 | 每个表有单独的文件 | 表和索引存储在共享表空间 |
| 性能 | 查询性能较好，但不支持事务处理 | 支持事务处理，写操作性能较高 |
面试题：MySQL中，MyISAM、InnoDB引擎的区别？
参考：MyISAM 不支持事务和外键，索引与数据分离，查询快但安全性低；InnoDB 支持事务、外键，聚簇索引，适合高并发场景，安全性高。
事务ACID特性
Atomicity（原子性）：要么全做，要么不做。
Consistency（一致性）：前后状态合法。
Isolation（隔离性）：各自互不干扰。
Durability（持久性）：提交后就不会丢。
2、数据文件存储
问题：数据库到底是如何保存数据文件的？
mysql> create database db_mysql default charset=utf8;
/etc/my.cnf配置文件
[root@mysql-server ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
当数据库创建完毕后，查看/export/server/mysql/data文件夹：
3、MyISAM 引擎
mysql> use db_mysql;
mysql> show tables;
mysql> create table tb_test_myisam(id int) engine=myisam default charset=utf8mb4;
mysql> show tables;
mysql> show create table tb_test_myisam;
查看db_mysql目录结构，如下图所示:
MyISAM引擎：
*.sdi=> 序列化字典信息（Serialized Dictionary Information）表的元数据信息，主要用于数据字典管理；数据表结构、字段、类型等等。
*.MYD=> Data 数据文件，主要用于存储 数据 文件。
*.MYI=> INDEX索引，主要用于存放 索引 文件。
早期MySQL5.7及以前版本，没有*.sdi文件，只有*.frm文件
早期MySQL5.7及以前版本，我们可以通过cp .frm、.MYI、*.MYD这三个文件来实现MyISAM引擎表的备份；MySQL8.0及以后引入更多复杂的功能，导致没有办法直接copy，只能通过物理备份或逻辑备份来实现！
4、InnoDB 引擎
mysql> create database db_itheima; # 可选
mysql> use db_itheima;
mysql> create table tb_user2(id int, name char(10)) default charset=utf8mb4;
mysql> show tables;
mysql> show create table tb_user2;
InnoDB引擎：
.ibd：每个表都会有一个独立的 .ibd 文件，存储该表的表数据和索引。
ibdata1：用于存储全局的表空间、数据字典和事务日志等。
redo log配置（了解）：
vim /etc/my.cnf
[mysqld]
innodb_log_file_size = 50M # 单个 redo log 文件的大小
innodb_log_files_in_group = 2 # MySQL 默认是 2，所以会生成两个 redo log 文件
innodb_log_group_home_dir = /export/server/mysql/data # redo log 文件存放路径
systemctl restart mysqld
[root@mysql-server ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_log_file_size = 50M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /export/server/mysql/data
redo log日志文件（ib_logfile0, ib_logfile1 等）：用于存储事务日志，确保数据一致性和恢复能力。
在 MySQL 5.7 及之前，redo log 文件是：
ib_logfile0``ib_logfile1
在 MySQL 8.0，redo log 文件被放在一个独立的目录 #innodb_redo 中。
- 里面有多个小文件，MySQL 通过 循环写入 来管理。
- 默认名字类似
#ib_redo10_tmp,#ib_redo11_tmp…
对于中等负载的数据库，innodb_log_file_size 可以设置为 50M 到 256M 之间。
对于高负载数据库，可以考虑将 innodb_log_file_size 设置为 1GB 或更大。
拓展：在Linux命令行执行MySQL命令
格式
mysql -u用户名 -p'密码' -e 'SQL语句'
mysql -u用户名 -p"密码" -e "SQL语句"
mysql -u用户名 -p密码 -e 'SQL语句'
mysql -u用户名 -p密码 -e "SQL语句"
[root@mysql-server ~]# mysql -uroot -pMySQL@666 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| db_itheima |
| db_mysql |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
[root@mysql-server ~]#
5、删除幽灵数据库（拓展）
解决方法：
cd /export/server/mysql/data
mkdir db_itheima
chown mysql:mysql db_itheima
drop database db_itheima;
6、特殊字符（不可见字符）
[root@mysql-server ~]# ifconfig
-bash: ifconfig : command not found
[root@mysql-server ~]# ifconfig
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.88.101 netmask 255.255.255.0 broadcast 192.168.88.255
ether 00:50:56:37:56:ff txqueuelen 1000 (Ethernet)
RX packets 43083 bytes 45666467 (43.5 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 18641 bytes 1318344 (1.2 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@mysql-server ~]#
报错版配置
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_log_file_size = 50M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /export/server/mysql/data
[root@mysql-server ~]# vim /etc/my.cnf
[root@mysql-server ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_log_file_size = 50M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /export/server/mysql/data
[root@mysql-server ~]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xeu mysqld.service" for details.
[root@mysql-server ~]#
正确版配置
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_log_file_size = 50M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /export/server/mysql/data
[root@mysql-server ~]# vim /etc/my.cnf
[root@mysql-server ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_log_file_size = 50M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /export/server/mysql/data
[root@mysql-server ~]# systemctl restart mysqld
[root@mysql-server ~]#
可以用 cat-A 来查看不显示字符
# 报错版配置
[root@mysql-server ~]# cat -A /etc/my.cnf
[mysqld]$
basedir=/export/server/mysqlM-BM- $
datadir=/export/server/mysql/dataM-BM- $
port=3306$
socket=/tmp/mysql.sock$
character_set_server=utf8mb4$
collation-server=utf8mb4_unicode_ci$
innodb_log_file_size = 50M$
innodb_log_files_in_group = 2$
innodb_log_group_home_dir = /export/server/mysql/data$
[root@mysql-server ~]#
# 正确版配置
[root@mysql-server ~]# cat -A /etc/my.cnf
[mysqld]$
basedir=/export/server/mysql$
datadir=/export/server/mysql/data$
port=3306$
socket=/tmp/mysql.sock$
character_set_server=utf8mb4$
collation-server=utf8mb4_unicode_ci$
innodb_log_file_size = 50M$
innodb_log_files_in_group = 2$
innodb_log_group_home_dir = /export/server/mysql/data$
[root@mysql-server ~]#
三、慢查询日志
1、启动慢查询日志（重点）
SQL语句：执行过程中，有快有慢，找出慢查询SQL。
作用：慢查询日志记录了所有执行时间超过设定阈值的查询（300ms、1s、5s根据不同业务场景而定），帮助你发现需要优化的慢查询SQL语句，辅助优化。
如何启用：
vim /etc/my.cnf
[mysqld]
...(省略其他原有信息)
# 开启慢查询日志
slow_query_log=1
# 指定慢查询日志文件存放路径
slow_query_log_file=/export/server/mysql/logs/mysql-slow.log
# 设置超过 1 秒的查询被记录
long_query_time=1
# 记录未使用索引的查询（可选）
log_queries_not_using_indexes=1
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_log_file_size = 50M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /export/server/mysql/data
slow_query_log=1
slow_query_log_file=/export/server/mysql/logs/mysql-slow.log
long_query_time=1
log_queries_not_using_indexes=1
# 创建日志目录
mkdir -p /export/server/mysql/logs
# 必须修改权限
chown -R mysql:mysql /export/server/mysql
重启MySQL
systemctl restart mysqld
补充：my.cnf参考配置
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
#character_set_server=utf8
#collation-server=utf8_unicode_ci
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# 开启慢查询日志
slow_query_log=1
# 指定慢查询日志文件存放路径
slow_query_log_file=/export/server/mysql/logs/mysql-slow.log
# 设置超过 1 秒的查询被记录
long_query_time=1
#innodb_log_file_size = 50M # 单个 redo log 文件的大小。
#innodb_log_files_in_group = 2 # MySQL 默认是 2，所以会生成两个 redo log 文件。
#innodb_log_group_home_dir = /export/server/mysql/data # redo log 文件存放路径
分析日志：慢查询日志可以显示查询的执行时间、锁等待时间、以及查询的详细内容。分析日志后，优化这些慢查询是提升性能的首要任务。
案例设计 - 慢查询
添加200万条数据到数据表中，做测试（不要求掌握以下数据准备操作，只是为了做测试）
SQL高级：类似Python/Shell代码，支持if结构、循环结构以及函数或者存储过程等等。
Prompt提示词：传入表结构，根据simple_table表结构，编写一个MySQL存储过程，循环向数据表中插入200万条测试数据。
create database if not exists db_itheima;
use db_itheima;
drop table if exists tb_people;
create table tb_people (
id int auto_increment primary key,
name varchar(50),
age int
);
-- 创建存储过程
drop procedure if exists insertdata;
delimiter //
create procedure insertdata()
begin
declare i int default 1;
start transaction;
while i <= 2000000
do
insert into tb_people (name, age)
values (concat('user_', i), floor(18 + (rand() * 42)));
set i = i + 1;
end while;
commit;
end //
delimiter ;
-- delimiter用于临时更改语句结束符（默认;），常用于定义存储过程、触发器等包含分号的复合语句。
-- 调用存储过程
call insertdata();
-- 检查数据生成结果
select count(*) from tb_people;
做一个查询，然后查看效果
# 检查数据生成结果
mysql> select count(*) from tb_people;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
1 row in set (0.15 sec)
# 查询指定id相关的信息
mysql> select * from tb_people where id = 1990000;
+---------+--------------+------+
| id | name | age |
+---------+--------------+------+
| 1990000 | user_1990000 | 43 |
+---------+--------------+------+
1 row in set (0.00 sec)
# 查看性能分析
mysql> EXPLAIN select * from tb_people where id = 1990000;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_people | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
# 查询所有数据（测试环境无所谓，生产环境慎重！）
# select * from tb_people;
执行 select * from tb_people; 出现大量数据刷屏的现象！
...
...
...
| 642339 | user_642339 | 40 |
| 642340 | user_642340 | 28 |
| 642341 | user_642341 | 48 |
| 642342 | user_642342 | 53 |
| 642343 | user_642343 | 19 |
| 642344 | user_642344 | 43 |
| 642345 | user_642345 | 57 |
| 642346 | user_642346 | 55 |
| 642347 | user_642347 | 43 |
| 642348 | user_642348 | 34 |
^C| 642348 | user_642348 | 34 |
^C -- query aborted
+---------+--------------+------+
2000000 rows in set (2.37 sec)
cat /export/server/mysql/logs/mysql-slow.log
注意：如果在多实例的环境下操作，可能会出现很难看到慢查询日志的情况！
排查命令
-- 1. 查看慢查询日志相关配置
SHOW VARIABLES LIKE 'slow_query_log'; -- 检查慢查询日志是否开启（ON/OFF）
SHOW VARIABLES LIKE 'slow_query_log_file'; -- 查看慢查询日志文件存储路径
SHOW VARIABLES LIKE 'long_query_time'; -- 查看慢查询阈值（单位：秒），执行时间超过此值的SQL会被记录
-- 2. 开启慢查询日志（需具有SUPER权限）
SET GLOBAL slow_query_log = 'ON'; -- 启用慢查询日志记录功能
-- 3. 模拟慢查询（用于测试）
SELECT SLEEP(12); -- 让当前会话休眠12秒，模拟一个执行时间较长的查询
-- 若long_query_time设置值小于12，此查询会被记录到慢查询日志中
mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------+
| slow_query_log_file | /export/server/mysql/logs/mysql-slow.log |
+---------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql>
建议：用一台新的 Linux 机器来安装 MySQL（默认单实例），然后重新配置慢查询日志。
2、使用 EXPLAIN 分析执行计划（重点）
简单来说：explain执行计划就是用于分析一个SQL语句如何执行的
核心作用：帮助我们提升SQL查询效率，加快查询速度。
☆ Index 索引
学不认识的汉字，会去词典当中寻找
传统：一页一页进行查找，如果要查的数据在最后一页，整体要翻一遍 => 全表扫描
索引：给所有的汉字添加一个目录页（拼音、偏旁部首），以后在查找汉字的时候，不是一页一页翻，而是先查找目录页，然后在根据目录页快速定位汉字 => 走索引（索引优化）=> 快速定位我们要查找的内容
☆ EXPLAIN 命令
简单来说，EXPLAIN 是用来 查看数据库如何执行一个 SQL 查询 的工具。它能告诉你数据库在执行查询时，选择了什么样的操作步骤（比如是扫描整个表还是使用索引等），并且通过这些信息你可以判断查询是否高效。
作用：假设你写了一个 SQL 查询来查询数据，如果查询效率低下（比如很慢），你可能想知道数据库是怎么执行的，找出瓶颈在哪。EXPLAIN 就是用来帮你“拆解”查询，了解数据库是如何处理每一步的。
如何使用：
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
explain select * from tb_people;
EXPLAIN select * from tb_people where id = 1990000;
1、分析结果
当你在查询前加上 EXPLAIN 时，它会显示一个执行计划的表格，这个表格给出了执行查询时的每一个细节。
type ALL 表示全表查询，性能最差！
EXPLAIN 输出各列含义
| 列名 | 含义 | 你例子里的值 |
|---|---|---|
| id | 查询中 SELECT 的序号（或子查询/union 的标识）。越大越先执行。 | 1 → 只有一个简单查询 |
| select_type | 查询的类型：SIMPLE（简单查询）、PRIMARY（外层查询）、SUBQUERY、UNION 等 | SIMPLE |
| table | 当前访问的表 | tb_people |
| partitions | 如果用了分区表，这里显示分区信息，否则 NULL | NULL |
| type | 连接类型（性能关键）：ALL（全表扫描）、index、range、ref、eq_ref、const、system 等。越靠后性能越好。 | const → 主键等值匹配，直接命中 |
| possible_keys | 优化器认为可能用到的索引 | PRIMARY |
| key | 实际用到的索引 | PRIMARY |
| key_len | 索引长度（字节数），这里是 4，说明你的 id 是 INT | 4 |
| ref | 索引列和什么值比较，比如 const、func、col | const |
| rows | 预计要扫描多少行 | 1 |
| filtered | 预计符合条件的行占比（百分比）。优化器基于统计信息做出的估算。 | 100 |
| Extra | 额外信息，比如 Using index、Using where、Using temporary、Using filesort 等 | NULL |
关于 filtered（拓展）
为什么 **filtered = 100.00**？
- 这里条件是
id = 1990000，并且用的是 主键（PRIMARY KEY）。 - 主键查询只会命中唯一一行，没有额外过滤条件，所以符合率是 100%。
什么时候 **filtered** 不是 100.00？ 没有任何主键和索引时。
小结
filtered反映了 行过滤的选择性，100% 表示所有行都符合条件。- 当查询条件包含 范围查询、非索引字段过滤 或 复合条件 时，
filtered才会低于 100%。 - 一般来说，
filtered越低，说明过滤效果越强，但也可能意味着索引没用好，MySQL 要扫描更多无用行。 - 统计信息驱动：
filtered的值来源于优化器基于统计信息所做的预估。 - 索引更新统计信息： 添加或删除索引会触发 MySQL 更新表的统计信息，使其更准确地反映数据的真实分布。
- 更准确的预估： 有了更准确的统计信息，优化器就能做出更准确的预估。
补充
核心列 type 非常关键，它反映了查询的效率。这里列出常见的几种类型，效率从高到低排序：
- const：最优，表示数据库通过常量查找一个行。
- ref：比较高效，表示数据库通过索引查找匹配行。
- range：表示数据库通过范围扫描来查找数据。
- index：表示数据库扫描整个索引（但不会读取数据表），效率不如
range。 - 索引=字段目录，数据=记录
- ALL：最差，表示数据库执行了全表扫描，效率最低。
2、对照慢日志
同样的，在慢日志中，也可以看到记录
问题总结
没有查看到慢日志，是怎么回事？
1、没有配置好参数 slow_query_log=1
2、没有权限写入数据：chown -R mysql.mysql /export/server/mysql
3、没有找对配置文件：systemctl status mysqld 按照自己的服务去找配置文件
把双引号当中的命令，systemctl status mysqld.service 或者 journalctl -xeu mysqld.service 执行一下
然后进一步分析，解决问题
EXPLAIN vs EXPLAIN ANALYZE 详细对比（拓展）
| 特性 | EXPLAIN | EXPLAIN ANALYZE |
|---|---|---|
| 核心功能 | 查询执行计划（预估） | 查询执行分析（实际） |
| 数据来源 | 基于统计信息估算 | 基于实际执行测量 |
| 是否执行查询 | ❌ 不执行查询 | ✅ 实际执行查询 |
| 输出内容 | 预估成本、行数、扫描类型 | 实际时间、行数、循环次数 |
| 性能影响 | 无性能影响 | 有性能开销（会执行查询） |
| MySQL版本 | 所有版本支持 | MySQL 8.0+ 支持 |
| 使用场景 | 日常优化分析 | 深度性能调优 |
☆ 查看索引与添加索引
准备测试数据
CREATE TABLE students (
id INT,
name VARCHAR(20),
age INT,
gender ENUM('male', 'female'),
score DECIMAL(11, 2),
cls_id INT
);
desc students;
INSERT INTO students (id, name, age, gender, score, cls_id) VALUES
(1, '张三', 18, 'male', 85.50, 101),
(2, '李四', 19, 'female', 92.00, 101),
(3, '王五', 20, 'male', 78.25, 102),
(4, '赵六', 17, 'female', 88.75, 102),
(5, '孙七', 21, 'male', 59.00, 103),
(6, '周八', 19, 'female', 96.50, 103);
select * from students;
create database if not exists db_itheima;
use db_itheima;
drop table if exists tb_people;
create table tb_people (
id int auto_increment primary key,
name varchar(50),
age int
);
-- 创建存储过程
drop procedure if exists insertdata;
delimiter //
create procedure insertdata()
begin
declare i int default 1;
start transaction;
while i <= 2000000
do
insert into tb_people (name, age)
values (concat('user_', i), floor(18 + (rand() * 42)));
set i = i + 1;
end while;
commit;
end //
delimiter ;
-- delimiter用于临时更改语句结束符（默认;），常用于定义存储过程、触发器等包含分号的复合语句。
-- 调用存储过程
call insertdata();
-- 检查数据生成结果
select count(*) from tb_people;
1、查看索引
SHOW INDEX FROM students; -- 详细显示所有索引
SHOW INDEX FROM tb_people;
-- 等价于
show keys from students;
SHOW keys FROM tb_people;
-- 第二种：直接展示
desc students; -- 简略显示，PRI/UNI/MUL 标记索引类型
desc tb_people;
mysql> show index from students;
Empty set (0.00 sec)
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select * from students;
+------+--------+------+--------+-------+--------+
| id | name | age | gender | score | cls_id |
+------+--------+------+--------+-------+--------+
| 1 | 张三 | 18 | male | 85.50 | 101 |
| 2 | 李四 | 19 | female | 92.00 | 101 |
| 3 | 王五 | 20 | male | 78.25 | 102 |
| 4 | 赵六 | 17 | female | 88.75 | 102 |
| 5 | 孙七 | 21 | male | 59.00 | 103 |
| 6 | 周八 | 19 | female | 96.50 | 103 |
+------+--------+------+--------+-------+--------+
6 rows in set (0.00 sec)
mysql> show index from tb_people;
+-----------+------------+----------+--------------+-------------+-----------+---+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | e | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+---+---------+---------------+---------+------------+
| tb_people | 0 | PRIMARY | 1 | id | A | | | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+---+---------+---------------+---------+------------+
1 row in set (0.01 sec)
mysql> show keys from tb_people;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_people | 0 | PRIMARY | 1 | id | A | 1845943 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
mysql> desc tb_people;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
2、唯一索引 unique
-- unique 唯一索引
alter table 表名 add unique (字段);
alter table tb_people add unique (name);
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table students add unique (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> desc tb_people;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> alter table tb_people add unique (name);
Query OK, 0 rows affected (8.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_people;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | UNI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
3、普通索引 key/index
在 MySQL 当中，Index的中文意思是索引。
Key 本意有键的意思，比如，Primary Key 是主键约束。
Key 也有索引的意思。比如，去掉 Primary 的 Key，就是普通索引。因此，这两个单词在 MySQL 中等价。
drop table if exists students;
CREATE TABLE students (
id INT,
name VARCHAR(20),
age INT,
gender ENUM('male', 'female'),
score DECIMAL(11, 2),
cls_id INT
);
desc students;
INSERT INTO students (id, name, age, gender, score, cls_id) VALUES
(1, '张三', 18, 'male', 85.50, 101),
(2, '李四', 19, 'female', 92.00, 101),
(3, '王五', 20, 'male', 78.25, 102),
(4, '赵六', 17, 'female', 88.75, 102),
(5, '孙七', 21, 'male', 59.00, 103),
(6, '周八', 19, 'female', 96.50, 103);
select * from students;
-- key/index 普通索引，这里以 name 举例
alter table student add index 索引名称 (字段);
-- 或者
create index 索引名称 on 表名 (字段);
-- 使用 ALTER TABLE 方式
ALTER TABLE students ADD INDEX idx_name (name);
desc students;
show create table students;
-- 删除 idx_name 索引
DROP INDEX idx_name ON students;
desc students;
show create table students;
-- 使用 CREATE INDEX 方式
CREATE INDEX idx_name ON students (name);
desc students;
show create table students;
-- 为 age 字段创建索引
ALTER TABLE students ADD INDEX idx_age (age);
desc students;
show create table students;
-- 为 cls_id 字段创建索引
ALTER TABLE students ADD INDEX idx_cls_id (cls_id);
desc students;
show create table students;
mysql> CREATE TABLE students (
-> id INT,
-> name VARCHAR(20),
-> age INT,
-> gender ENUM('male', 'female'),
-> score DECIMAL(11, 2),
-> cls_id INT
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE students ADD INDEX idx_name (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> DROP INDEX idx_name ON students;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> CREATE INDEX idx_name ON students (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE students ADD INDEX idx_age (age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE students ADD INDEX idx_cls_id (cls_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
4、联合索引
联合索引，其实是普通索引的扩展。通常适应于加快查询多个字段的结果。
-- 联合索引：针对 name 和 age 字段创建索引
ALTER TABLE students ADD INDEX idx_name_age (name,age);
desc students;
show create table students;
-- 删除索引
DROP INDEX idx_name_age ON students;
desc students;
show create table students;
-- 或者使用 CREATE INDEX 方式
CREATE INDEX idx_name_age ON students (name,age);
desc students;
show create table students;
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show create table students;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` enum('male','female') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` decimal(11,2) DEFAULT NULL,
`cls_id` int DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_cls_id` (`cls_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE students ADD INDEX idx_name_age (name,age);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` enum('male','female') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` decimal(11,2) DEFAULT NULL,
`cls_id` int DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_cls_id` (`cls_id`),
KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DROP INDEX idx_name_age ON students;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show create table students;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` enum('male','female') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` decimal(11,2) DEFAULT NULL,
`cls_id` int DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_cls_id` (`cls_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> CREATE INDEX idx_name_age ON students (name,age);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` enum('male','female') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` decimal(11,2) DEFAULT NULL,
`cls_id` int DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_cls_id` (`cls_id`),
KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
案例验证 - 不加索引查询与加索引查询
-- 1. 先移除 AUTO_INCREMENT 属性
ALTER TABLE tb_people MODIFY id INT;
-- 2. 再移除主键
ALTER TABLE tb_people DROP PRIMARY KEY;
-- 3. 验证结果
DESC tb_people;
-- 没加索引情况
explain select * from tb_people where id = 1990000;
select * from tb_people where id = 1990000;
-- 添加索引
alter table tb_people add primary key(id);
desc tb_people ;
show create table tb_people;
show index from tb_people;
-- 添加索引的情况
explain select * from tb_people where id = 1990000;
select * from tb_people where id = 1990000;
mysql> desc tb_people;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | UNI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> -- 1. 先移除 AUTO_INCREMENT 属性
mysql> ALTER TABLE tb_people MODIFY id INT;
Query OK, 2000000 rows affected (21.87 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql> -- 2. 再移除主键
mysql> ALTER TABLE tb_people DROP PRIMARY KEY;
Query OK, 2000000 rows affected (22.31 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 3. 验证结果
mysql> DESC tb_people;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(50) | YES | UNI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> explain select * from tb_people where id = 1990000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | tb_people | NULL | ALL | NULL | NULL | NULL | NULL | 1994245 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> -- 添加索引
mysql> alter table tb_people add primary key(id);
Query OK, 0 rows affected (14.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_people ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | YES | UNI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show create table tb_people;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_people | CREATE TABLE `tb_people` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show index from tb_people;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_people | 0 | PRIMARY | 1 | id | A | 1845943 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_people | 0 | name | 1 | name | A | 1995000 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
mysql> explain select * from tb_people where id = 1990000;
+----+-------------+-----------+------------+-------+---------------+---------+
| id | select_type | table | partitions | type | possible_keys | key |
+----+-------------+-----------+------------+-------+---------------+---------+
| 1 | SIMPLE | tb_people | NULL | const | PRIMARY | PRIMARY |
+----+-------------+-----------+------------+-------+---------------+---------+
1 row in set, 1 warning (0.00 sec)
mysql>
这个输出告诉我们：
- id:
1，表示这是查询的第一步；查询中 SELECT 的序号（或子查询/union 的标识）。越大越先执行。 - select_type:
SIMPLE，表示这是一个简单查询，没有复杂的连接操作。 - table:
tb_people，表示查询的数据表。 - type:
const，表示使用常量值（如 WHERE id = 1990000 中的 1990000）来匹配到了结果;ALL全表扫描。 - possible_keys:
Primary，表示数据库考虑过使用的Key；NULL空值。 - key:
Primary，表示数据库实际使用的的主键约束来查的；NULL空值。 - key_len: 4，表示数据库使用了该索引，占了4Bytes；
NULL空值。 - ref:
const，表示查询近似于使用了常量时间（即查询复杂度低）；NULL空值。 - rows:
1999081，表示数据库预计需要扫描 1999081行数据；11行数据。 - filtered：
100，表示过滤（where id = 1990000）出来的数据，100%命中了结果。 - Extra:
Using where， 用到了where，NULL，没有额外的信息。
总结对比
| 类型 | 类比 | 速度 | 适用场景 |
|---|---|---|---|
| const | 直接翻到指定页码 | 最快 | 主键/唯一索引的等值查询 |
| ref | 按目录查几个名字 | 快 | 普通索引的等值查询 |
| range | 按目录查一个连续区间（如 20~30 岁） | 较快 | 索引范围查询 |
| index | 扫完整本目录（不读正文） | 较慢 | 只需索引字段，不查数据 |
| ALL | 一页一页翻完整本书 | 最慢 | 无索引或强制全表扫描 |
key_ken长度说明：
INT 类型占 4 字节
VARCHAR(n) 类型占用的字符数是 n（最大长度）
DATE 类型占用 3 字节
CHAR(n) 类型占用 n 字符
面试题：在运维环境中，发现MySQL运行缓慢，如何去解决？
答：
① 从系统层面，检查系统资源使用情况，如CPU负载（top）、检查内存占用（free -h）、检查磁盘空间使用（df -h），因为MySQL把数据写入到磁盘，读写都涉及磁盘io（iostat）
② 从日志层面，开启慢查询日志，把查询缓慢的SQL写入到慢查询日志中，进行捕获
③ 从SQL语句层面，使用explain执行计划分析SQL执行过程，是否有走缓慢等等，查看具体慢的原因。如果是全表扫描，可以考虑引入索引（如主键索引、唯一索引、普通索引）等等实现优化操作
四、MySQL 性能分析
1、SHOW PROCESSLIST
作用：SHOW PROCESSLIST可以显示当前正在执行的SQL语句及其状态，帮助你发现正在运行的长时间查询。
注意：适合分析执行时间较长的SQL语句，同时也可以查看有哪些用户在连接MySQL数据库。
SHOW PROCESSLIST;
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1552 | Waiting on empty queue | NULL |
| 8 | root | localhost | db_itheima | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+-----------+------------+---------+------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
分析重点：
Command：表示查询的状态，比如Query表示正在执行查询，Sleep表示连接空闲。
Time：表示查询的执行时间，时间较长的查询可能是性能瓶颈。
State：显示当前查询执行的阶段，帮助定位查询的卡顿点（如Copying to tmp table表示查询在使用临时表）。
2、SHOW PROFILES
作用：SHOW PROFILES可以显示最近执行的SQL查询的执行时间，以及每个阶段的耗时，帮助你了解查询在执行时哪个环节耗时最长。
-- 开启性能分析（开关）
SET profiling = 1;
SELECT @@profiling;
-- 先移除 AUTO_INCREMENT 属性
ALTER TABLE tb_people MODIFY id INT;
-- 再移除主键
ALTER TABLE tb_people DROP PRIMARY KEY;
select * from tb_people where id = 1990000;
-- 查看所有SQL的执行情况
SHOW PROFILES;
-- 查看指定 ID 查询的详细耗时
SHOW PROFILE ALL FOR QUERY ID号;
详细分析各阶段耗时
| 阶段 | 说明 | 耗时(秒) |
|---|---|---|
| executing | 查询执行阶段 - 主要耗时 | 2.249519 |
| starting | 查询开始准备 | 0.000145 |
| Opening tables | 打开表 | 0.000048 |
| optimizing | 优化器工作 | 0.000014 |
| statistics | 统计信息收集 | 0.000029 |
| preparing | 准备执行计划 | 0.000087 |
| 其他所有阶段 | 都可以忽略不计 | < 0.001 |
小结
如果我们想捕获SQL语句详细执行，以及底层执行过程，以及耗时，都可以使用(show profiles;)
优点：适合在开发/测试环境，快速定位单条 SQL 的性能问题，轻量级且无需额外工具。
缺点：无法拆分显示子查询的单独性能。
五、今日内容回顾
- MySQL 四层结构以及每一层作用（了解）
- 慢日志查询（重点）
- EXPLAIN 执行计划（掌握）
- SHOW PROFILES 查看查询的详细耗时（了解）