1. explain
| 列名 | 含义 | 取值范围及含义 |
|---|---|---|
| id | 查询的序列号，标识查询中各个 SELECT 子句的顺序 | 整数，值越大越先执行；相同值时，按从上到下顺序执行；为 NULL 时表示该查询是一个派生表（如子查询）的一部分 |
| select_type | 查询的类型 | SIMPLE：简单查询，不包含子查询或 UNION；PRIMARY：最外层的查询；SUBQUERY：子查询；DERIVED：派生表，即 FROM 子句中的子查询；UNION：UNION 中的第二个及后续查询；UNION RESULT：UNION 结果集 |
| table | 查询涉及的表名 | 实际的表名，如果是子查询或派生表，可能显示为 <derivedN>（N 为派生表的编号）或 <unionM,N>（表示 UNION 操作的结果） |
| partitions | 查询可能涉及的分区 | 如果表是分区表，显示查询可能访问的分区；如果不是分区表，则为 NULL |
| type | 表示表的连接类型，反映了查询的效率，从好到差依次排列 | system：表中只有一行记录，是 const 类型的特例；const：通过索引一次就找到记录，用于 PRIMARY KEY 或 UNIQUE 索引；eq_ref：多表连接时，对于前面的每一行，当前表只通过索引匹配一行；ref：使用非唯一索引或唯一索引的前缀查找；range：使用索引进行范围扫描；index：全索引扫描；ALL：全表扫描 |
| possible_keys | 可能使用的索引 | 列出查询可能使用的索引名，如果为空表示没有可用的索引 |
| key | 实际使用的索引 | 显示查询实际使用的索引名，如果为空表示没有使用索引 |
| key_len | 实际使用的索引长度 | 索引使用的字节数，该值越短越好，它能帮助判断索引的使用情况和效率 |
| ref | 显示哪些列或常量被用于和索引一起从表中查找记录 | 显示与索引进行比较的列或常量，如果为 NULL 表示没有使用索引进行比较 |
| rows | MySQL 估计为了找到所需的行而要读取的行数 | 一个估计值，该值越小说明查询效率越高 |
| filtered | 表示通过条件过滤后，剩余记录的百分比 | 取值范围是 0.00 - 100.00，值越高表示过滤后剩余的记录越多 |
| Extra | 额外的信息，提供了关于查询执行的其他重要细节 | Using filesort：需要额外的排序操作，通常表示查询效率较低；Using temporary：使用了临时表来处理查询结果；Using index：使用了覆盖索引；Using where：使用了 WHERE 子句进行过滤；Using join buffer：使用了连接缓存；Impossible WHERE：WHERE 子句的条件永远为 FALSE |
下面举例进行说明
表结构：
Create Table: CREATE TABLE `tt` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `bc` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=131056 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
表数据：
create table tt(id int primary key auto_increment, a int , b int, index (a), index bc(b,c))engine=innodb;
insert into tt(a,b,c) values(1,1,1),(2,2,2),(3,3,3),(4,4,4);
insert into tt(a,b,c) select a,b,c from tt;//重复插入到16384行 insert into tt(a,b,c) values(5,5,5);
insert into tt(a,b,c) select a,b,c from tt;
insert into tt(a,b,c) select a,b,c from tt; //总共65540行
create table t2 like tt;
insert into t2 select * from tt;
1.1 主键上的查询
主键上的等值查询，type 为 const，用了 PRIMARY 索引
主键上的范围查询，type 为 range，范围查询用了 where 条件，因此 extra 中为 Using where
1.2 二级索引上的查询
二级索引上等值查询，type 为 ref，且能用上覆盖索引，Extra 为 Using index
二级索引上的范围查询，type 为 range，能用上索引且是范围查询，所以 Extra 为 Using where，Using index
1.3 非索引或者非前缀索引查询
非前缀索引等值和范围查询，type 都为 all，由于是非索引，所以就算是等值，也需要用 where 进行判断，因此 Extra 为 Using where，key 唯 null
如果查询结果能用上覆盖索引，由于一般二级索引都比主键索引要小，因此这种情况下会遍历二级索引，如下：
type 为 index 表示全索引扫描（二级索引，主键索引为 all），对应的 analyze 结果如下：
1.4 连表查询
先用 a 进行等值查询，由于 a 有索引，因此 type 为 ref，ref 为 const 表示是常数
然后拿到查询的结果跟 b 连表，这里的 type 为 eq_ref 就可以理解为 const，表示连表查询时，使用唯一键进行等值查询，然后 ref 为 test.a.id，表示使用 test 库 a 表的 id 做等值查询
1.5 order by
需要使用索引 a 进行等值查询，因此 type 为 ref，有 order by，需要排序，因此 Extra 中有 Using filesort
Using filesort 强调的是排序操作不能直接通过索引有序性来实现，需要额外的排序步骤。“filesort” 是 MySQL 内部的一种排序机制的统称，它并不特指在文件（磁盘）上排序，也可能在内存中进行，只是表示无法单纯依赖索引来完成结果集的排序。
1.6 group by
MySQL 从 where 条件和 group by 语句选择的待选 key 为 a，bc，但是计算后发现单纯使用其中某一个，都需要回表，还不如直接全表查询来得快，因此最终选择了全表查询，type 为 all
由于 group by 算法会创建只有两列的临时表用来处理数据，因此 Extra 中有 Using temporary
1.7 distinct
可以看到，distinct 和 group by 算法很像，也用了临时表
如果 distinct 的列本来就能保证唯一性，比如主键，唯一索引等，那么是不用到临时表去聚合的
如果结果集中需要计算，尽管 id+0 跟 id 是一致的，但是 MySQL 可没这么智能，还是会老老实实的用临时表来进行聚合，然后讲结果返回，因此这里有 Using temporary，同时，判断能否使用覆盖索引，只要查询和条件字段在一个索引中都包含，那么就能用上覆盖索引，所以这里有 Using index
1.8 是否使用临时表
当我们想查看一个语句是否有用临时表，以及是否有用到磁盘临时表时，可以通过 optimizer_trace 来查看
set optimizer_trace= 1；
explain analyze select distinct id+0 from tt where a > 1;
select * from information_schema.optimizer_trace\G;
这里能看到使用了临时表，但是没有使用磁盘临时表
2. show engine innodb status
2.1 事务相关结果
show engine innodb status 结果很长，但我们只用关注事务状态即可，即关注下面内容：
2.2 读事务（读锁）不计入
这里只显示写锁相关的事务，读锁不计入，比如
begin;
select * from tt where id = 12 for share;
结果为：
2.3 写事务（写锁）计入
如果是写锁：
begin;
select * from tt where id = 2 for update;
结果为：
由于我是直接在上面的会话中加了一个新的写锁，因此这里 lock struct 是 4，包含 IX、IS、上一个行读锁和这个行写锁，如果我完全新起一个事务，那么结果如下：
这也从侧面说明了 innodb 在分配事务 id 时，只有写事务才会分配事务 id，读事务不分配，id 为一个很长的数字
2.4 history 和 undo
两个线程如下执行：
begin;
select * from tt where id = 12;
update t set c = c + 1 where id = 2;
update t set c = c + 1 where id = 3
结果为：
可以看到，history 表示已经提交但未 purge 的 undo 数
undo log entries 表示未提交的 undo 数量
2.5 锁冲突
begin;
select * from tt where id = 2;
select * from tt where id = 2 for update;
update t set c = c + 1 where id = 2;
可以看到事务正在等待锁，等的是lock_mode X locks rec but not gap waiting行锁，非间隙锁，非 Next-Key lock
表只有 4 列，为什么上图中有 6 列呢？多余两列为隐式的 roll_ptr 和 trx_id，0 是 id 列，1 是 trx_id，2 是 roll_ptr，后面就分别每个列以及值
3. mysqlbinlog
3.1 binlog 开头文件解析
mysqlbinlog 是 MySQL 自带的查看 binlog 的工具，binlog 文件开头为
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
带 '#' 的都是注释，/**/ 也是注释，但是如果是 /*!……*/，那就不是注释了，比如/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;，表示如果版本大于等于 5.530，这句话就执行，每个中间的 0 都是 '.'
3.2 binlog 内容解析
# at 3231894
#250215 15:10:11 server id 1 end_log_pos 3231978 CRC32 0x3494bc57 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1739603411/*!*/;
BEGIN
/*!*/;
# at 3231978
#250215 15:10:11 server id 1 end_log_pos 3232029 CRC32 0xf978a687 Table_map: `test`.`tt` mapped to number 99
# has_generated_invisible_primary_key=0
# at 3232029
#250215 15:10:11 server id 1 end_log_pos 3232099 CRC32 0x89001328 Update_rows: table id 99 flags: STMT_END_F
BINLOG '
0z2wZxMBAAAAMwAAAB1RMQAAAGMAAAAAAAEABHRlc3QAAnR0AAQDAwMDAA4BAQCHpnj5
0z2wZx8BAAAARgAAAGNRMQAAAGMAAAAAAAEAAgAE//8AAQAAAAEAAAABAAAAAQAAAAABAAAAAQAA
AAEAAAACAAAAKBMAiQ==
'/*!*/;
# at 3232099
#250215 15:10:11 server id 1 end_log_pos 3232130 CRC32 0x361b0f71 Xid = 498
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
CRC32 表示主库开了 binlog_checksum，每次记录 binlog 都会计算 checksum，从库应用时也会校验，但是从库也能配置汇率校验 checksum
BINLOG '……' 这里面记录的就是 binlog 具体内容
original_committed_timestamp 表示主库生成 binlog 时间，单位微秒
immediate_commit_timestamp 表示从库应用 binlog 的时间，用这两个能计算主从延迟，但是如果有级联情况，比如 A->B->C，C 上 original_committed_timestamp 是 A 的值而不是 B 的
SET @@SESSION.GTID_NEXT= 'ANONYMOUS' 表示匿名事务。当将 @@SESSION.GTID_NEXT 设置为 'ANONYMOUS' 时，意味着接下来的事务将不会使用 GTID 进行标识，而是作为一个匿名事务来执行。在某些情况下，你可能需要执行一些不希望被 GTID 管理的特殊事务，例如恢复备份数据、执行一些测试性的事务等。通过设置 @@SESSION.GTID_NEXT = 'ANONYMOUS'，可以确保这些事务不会干扰正常的 GTID 序列，避免对主从复制等基于 GTID 的机制产生影响。
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'表示接下来的事务会由 MySQL 系统按照既定规则自动分配一个唯一的 GTID
Xid 事务 id，用于崩溃恢复，当 redo 中有 prepare，但是没有 commit 时，看有没有 Xid，有的话就提交，没有就回滚
4. mysqldump
备份整个库
mysqldump -uroot -pxxx database --flush-logs --single-transaction > backup.sql
结果为：
-- MySQL dump 10.13 Distrib 9.2.0, for macos15 (x86_64)
--
-- Host: localhost Database: test // 这里为 test 具体值表示只备份 test 库
-- ------------------------------------------------------
-- Server version 9.2.0
// 这里是开头，现将部分变量保存起来，同时设置为需要的值，等完成后再恢复
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */; // 设置字符集
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; // 设置为 0 作用是先关闭唯一索引和外键影响
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
// 创建表
--
-- Table structure for table `t`
--
DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
`e` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_idx` (`c`),
KEY `d_e_idx` (`d`,`e`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t`
--
// 插入数据，此处注意插入数据是需要锁表的
LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */; // 只对 myisam 有效，临时禁用指定表 `t` 上的非唯一索引
INSERT INTO `t` VALUES (1,0,0,0),(5,5,5,5),(10,10,10,10),(15,15,15,15),(20,20,20,20),(25,25,25,25);
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;
// 恢复变量
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2025-02-15 18:15:20
创建视图，如果视图依赖的表还未创建，怎么办？mysqldump 会先创建一个假的视图，如下：
然后在库备份最后重建视图，如下：
5. innodb_trx
语句 select * from information_schema.innodb_trx\G; 作用是查询 MySQL 数据库中 InnoDB 存储引擎当前正在运行的事务信息，结果如下：
trx_id: 2041 // 事务 id，只有更新或者写锁事务 id 是正常数据，读或者读锁事务 id 是一个很大数字
trx_state: LOCK WAIT // 事务状态
trx_started: 2025-02-16 13:20:58 // 事务开始时间
trx_requested_lock_id: 140562234655416:428:6:7:2:140562695098912 // 事务正在请求的锁的唯一标识符
trx_wait_started: 2025-02-16 13:20:58 // 锁等待开始时间
trx_weight: 2 // 事务权重，回滚时可用（锁+回滚段）
trx_mysql_thread_id: 31 // show processlist 中的 id
trx_query: select * from tt where id = 1 for update // 正在执行的语句
trx_operation_state: starting index read // 事务执行状态，正在索引上查找时被堵住了
trx_tables_in_use: 1 // 事务当前正在执行的语句用了几张表
trx_tables_locked: 1 // 意向锁数量
trx_lock_structs: 2 // 持有锁数量
trx_lock_memory_bytes: 1128
trx_rows_locked: 1
trx_rows_modified: 0 // 事务修改的行数量
trx_concurrency_tickets: 0 // InnoDB 用来控制并发事务数量的一种手段，当事务需要执行某些操作（如加锁、访问数据页等）时，会消耗一定数量的票据。当事务完成操作或者释放锁时，会将相应的票据释放回系统，供其他事务使用。避免小事务饿死
trx_isolation_level: REPEATABLE READ // 隔离级别
trx_unique_checks: 1 // 唯一键检查
trx_foreign_key_checks: 1 // 外键检查
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: 1 // 事务调度权重，值越大表示越容易拿到锁资源解锁执行
问答
-
binlog 单个文件设置大点好还是小点好？
建议设置大些，因为 binlog 文件每次切的时候，都会将上面关联的 redo 数据刷盘，如果设置太小，就会频繁触发切文件刷盘的操作，导致系统抖动，但是也不能设置太大，太大的话崩溃恢复时间会拉长
-
mysqldump
--quick默认参数是 ON，应该保持不变？是的，这里的 quick 的意思是客户端不缓存结果，直接落盘，在执行 mysqldump 时这样设置是合理的，并且也应该这样做，因为如果要缓存，可能客户端内存会 OOM，但是这个参数为 ON 对服务端来说就不是 quick 了，因为如果有缓存，服务端数据发送后，客户端缓存后可以直接接着发送，但是现在需要等待客户端落盘后才能继续发送，因此服务端来讲是慢的。
同时，客户端默认值为 OFF，一般也不建议修改，因为这样能加快服务端速度
-
mysqldump 如果指定
--single-transaction，会在输出文件里输出start transaction with consistent snapshot吗？不会，因为这个参数是为了备份线程获取一致性视图加的，从库应用时并不需要
-
mysqldump 如果同时指定
--flush-logs --single-transaction的时候，每备份一个库，都会执行一次 flush logs 吗？不是，flush logs 作用是切所有 log 文件，包括 binlog，如果在事务中执行该语句，那么会隐式的提交当前事务，如果每备份一个库都 flush 一次 logs，那么会切 log，事务就会被提交，一致性视图就被摧毁了，这个语句仅会在备份第一个库时，才执行，后面不执行
-
单独备份一个库，需要指定
--routines才能备份存储过程？是的，如果不加该参数，那么只会备份这个库的数据，不备份存储过程，只有加上
--routines才会去系统库中将这个库相关的所有存储过程备份
