-- 查看所有的数据库
show databases;

-- 创建数据库
create database if not exists basedb;
create database if not exists archedb;
show create database basedb;
show create database archedb;
-- 创建用户
create user if not exists 'base'@'%' identified with mysql_native_password by '123456789',
			              'arche'@'%' identified with mysql_native_password by '123456789';
-- 修改用户密码
alter user 'base'@'%' identified with mysql_native_password by '123456789' password expire never;
alter user 'arche'@'%' identified with mysql_native_password by '123456789' password expire never;
-- 创建角色
create role if not exists 'baserole'@'%', 'archerole'@'%';
-- 授予权限
grant all privileges on basedb.* to 'baserole'@'%';
grant all privileges on archedb.* to 'archerole'@'%';
-- grant select on *.* to 'baserole'@'%';
-- grant select on *.* to 'archerole'@'%';
-- 将角色权限授予给用户
grant 'baserole' to 'base'@'%';
grant 'archerole' to 'arche'@'%';
-- set default role 语句：定义了当用户连接到服务器并进行身份验证时，或在会话中执行 SET ROLE DEFAULT 语句时，哪些角色将被激活。
set default role all to 'base'@'%', 'arche'@'%';
-- 将任何库下任何表的主从复制权限授予给角色
grant replication slave on *.* to 'baserole'@'%';
-- 刷新权限表
flush privileges;

-- 使用basedb数据库
use basedb; 
select host, user, plugin from mysql.user;
show grants for 'baserole'@'%';
show grants for 'base'@'%';
show tables from basedb;


-- 删除角色
drop role if exists 'baserole', 'archerole';
-- 删除用户
drop user if exists 'base'@'%', 'arche'@'%';
-- 删除数据库
drop database if exists basedb;
drop database if exists archedb;

表和索引

-- 查看某个库的所有表
show tables from basedb;
show tables;
show tables like '%base_%';
show indexes from 表名称;

-- 查看表的创建信息
show create table 表名称;
show create table 表名称\G;
-- 查看表结构
desc 表名称;

-- 普通索引：CREATE        INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
-- 唯一索引：CREATE UNIQUE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
CREATE INDEX create_time ON t_base_table_template (`create_time`);
DROP INDEX create_time ON t_base_table_template;

会话的连接ID与线程ID

CONNECTION_ID() 返回当前会话的连接 ID（与 SHOW PROCESSLIST 中 Id 一致）。

performance_schema.threads 表映射连接 ID 与 MySQL 内部线程 ID（THREAD_ID）的对应关系。

线程类型：

foreground 前台线程，由客户端连接触发，用于处理SQL请求。
background 后台线程，由系统内部维护，负责如日志刷新、清理、复制、检查点等操作。

-- 查看当前会话的 连接ID、线程ID、线程名称
SELECT PROCESSLIST_ID, THREAD_ID, NAME, TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID();
-- 当前活跃线程数：
SELECT COUNT(*) FROM performance_schema.threads;
-- 仅客户端连接线程：
SELECT COUNT(*) FROM performance_schema.threads WHERE TYPE = 'FOREGROUND';

常用视图

-- 系统变量
SELECT * FROM performance_schema.global_variables;
SELECT * FROM performance_schema.session_variables;
-- 状态变量
SELECT * FROM performance_schema.global_status;
SELECT * FROM performance_schema.session_status;
-- 查询表数据占用空间
SELECT 
    table_name AS '表名',
    ROUND(data_length / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)'
FROM 
    information_schema.tables
WHERE table_schema = 'archedb';

常用变量

系统变量和状态变量

SET 语法用于变量赋值，官网：https://dev.mysql.com/doc/refman/8.4/en/set-variable.html

特性	System Variables（系统变量）	Status Variables（状态变量）
作用	控制 MySQL 的行为或配置项（如缓存大小，最大连接数）	显示 MySQL 当前的运行状态、统计信息、性能指标。状态变量可用于系统调优。
是否可修改	✅ 可配置，`SET` 语句或配置文件	❌ 只读，无法配置
重启后	恢复配置文件值或默认值	被清零或重置
生效范围	Global（全局）、Session（会话）、Both（两者兼有）	Global（全局）、Session（会话）、Both（两者兼有）
示例变量	`max_connections`, `join_buffer_size`	`Threads_connected`, `Questions`, `Uptime`

系统变量

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr] -- 默认session级别

SELECT * FROM performance_schema.global_variables; -- 推荐
SELECT * FROM performance_schema.session_variables; -- 推荐
-- 模糊查询
SHOW GLOBAL  VARIABLES LIKE '%buffer_size%';
SHOW SESSION VARIABLES LIKE '%buffer_size%';
SHOW         VARIABLES where variable_name LIKE '%buffer_size%';
-- 具体查询
SELECT @@GLOBAL.join_buffer_size, @@SESSION.join_buffer_size;

-- 变量赋值
SET GLOBAL sort_buffer_size = 256*1024, SESSION sort_buffer_size = 2*1024*1024;
SET @@GLOBAL.sort_buffer_size = 256*1024, @@SESSION.sort_buffer_size = 2*1024*1024;
SET @@GLOBAL.sort_buffer_size = 256*1024, @@sort_buffer_size = 2*1024*1024; -- session级别为2MB
SET @@GLOBAL.sort_buffer_size = 256*1024, sort_buffer_size = 2*1024*1024; -- session级别为2MB

状态变量

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr] -- 默认session级别

SELECT * FROM performance_schema.global_status; -- 推荐
SELECT * FROM performance_schema.session_status; -- 推荐
-- 模糊查询
SHOW GLOBAL  STATUS LIKE '%questions%';
SHOW SESSION STATUS LIKE '%questions%';
SHOW         STATUS where variable_name like '%questions%';

运行时长

将服务器的所有状态变量清零（重置为初始值）：FLUSH STATUS; 。

show global status where variable_name like '%uptime%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| Uptime                    | 5838907 | -- Global。服务器已运行秒数。
| Uptime_since_flush_status | 5838907 | -- Global。自上次执行 FLUSH STATUS 语句以来经过的秒数。
+---------------------------+---------+

连接

-- 连接管理
show variables where variable_name like '%connection%';
+-----------------------------------+-----------------+
| Variable_name                     | Value           |
+-----------------------------------+-----------------+
| character_set_connection          | utf8            |
| collation_connection              | utf8_general_ci |
| gaia_super_priv_extra_connections | 50              |
| max_connections                   | 4000            | -- Global。默认151。最大连接数。
| max_user_connections              | 0               | -- Both。允许任何给定 MySQL 用户账户的最大同时连接数。值为 0（默认值）表示“无限制”。该变量具有全局值，可在服务器启动时或运行时设置。它还具有只读会话值，用于指示当前会话关联账户的有效同时连接限制。会话值的初始化方式见官网说明。
+-----------------------------------+-----------------+

-- 连接统计
show global status where variable_name like '%connection%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_accept          | 0                   |
| Connection_errors_internal        | 0                   | -- Global。由于服务器内部错误导致的连接拒绝次数，例如无法启动新线程或内存不足的情况。
| Connection_errors_max_connections | 0                   | -- Global。由于服务器 max_connections 限制已达到，导致的连接拒绝次数。
| Connection_errors_peer_address    | 0                   |
| Connection_errors_select          | 0                   |
| Connection_errors_tcpwrap         | 0                   |
| Connections                       | 48940663            | -- Global。尝试连接 MySQL 服务器的次数（成功或失败）。
| Max_used_connections              | 602                 | -- Global。同时使用的最大连接数。
| Max_used_connections_time         | 2025-07-03 20:34:37 | -- Max_used_connections 达到当前值的时间。
+-----------------------------------+---------------------+

线程

-- 与线程相关的系统变量
show variables where variable_name like 'thread%';
+-------------------------------+---------------------------+
| Variable_name                 | Value                     |
+-------------------------------+---------------------------+
| thread_cache_size             | 1024                      | -- Global。默认值-1。服务器应缓存多少个线程以供重复使用。当客户端断开连接时，如果缓存中的线程数量少于 thread_cache_size，则该客户端的线程会被放入缓存。请求线程时，系统会优先从缓存中重复使用线程，只有当缓存为空时才会创建新线程。如果您的服务器有大量新连接，可以增加此变量值以提升性能。通常，如果线程实现良好，这不会带来明显的性能提升。然而，如果服务器每秒处理数百个连接，通常应将 thread_cache_size 设置得足够高，以便大多数新连接使用缓存线程。通过比较 Connections 和 Threads_created 状态变量的差异，可以评估线程缓存的效率。
| thread_handling               | one-thread-per-connection |
| thread_pool_high_prio_mode    | transactions              |
| thread_pool_high_prio_tickets | 4294967295                |
| thread_pool_idle_timeout      | 60                        |
| thread_pool_max_threads       | 100000                    |
| thread_pool_oversubscribe     | 20                        |
| thread_pool_size              | 2                         |
| thread_pool_stall_limit       | 10                        |
| thread_stack                  | 196608                    |
+-------------------------------+---------------------------+

-- 与线程相关的状态变量
show global status where variable_name like 'thread%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| Threads_cached       | 0        | -- Global。
| Threads_connected    | 22       | -- Global。当前打开的连接数。
| Threads_created      | 429082   | -- Global。表示创建过的线程数。该值不断增大是因为 thread_cache_size 使用默认值 -1。
| Threads_running      | 1        | -- Global。未处于睡眠状态的线程数量。一般低于 connected 数值。准确来说，代表当前并发

超时

-- 与超时相关的系统变量
show variables where variable_name like '%execution%' or variable_name in (
    'long_query_time',
    'connect_timeout',
    'wait_timeout',
    'interactive_timeout'
);
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| connect_timeout     | 5        | -- Global。默认10秒。MySQL 服务器在收到连接请求后，等待响应“Bad handshake”错误的秒数（大白话：在指定秒数内没完成握手，连接会被拒绝）。
| wait_timeout        | 3600     | -- Both。默认28800秒。服务器在关闭 非交互式连接（后端程序发起的连接，如JDBC）前，等待该连接有活动操作的秒数。
| interactive_timeout | 3600     | -- Both。默认28800秒。服务器在关闭 　交互式连接（mysql命令、客户端工具）前，等待该连接有活动操作的秒数。
| max_execution_time  | 0        | -- Both。SELECT 语句的执行超时时间（会中断超时 SQL，返回错误），单位为毫秒。默认值0，表示不启用超时。对于存储程序中的 SELECT 语句，max_execution_time 将被忽略。
| long_query_time     | 1.000000 | -- Both。如果查询耗时超过指定秒数，服务器会将 Slow_queries 状态变量加一。如果启用了慢查询日志，该查询会被记录到慢查询日志文件中。默认值10秒，最小0秒。
+---------------------+----------+

-- 与超时相关的状态变量
show global status where variable_name like '%execution%' or variable_name in(
    'Slow_queries'
);
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Max_execution_time_exceeded      | 0     |
| Max_execution_time_set           | 0     |
| Max_execution_time_set_failed    | 0     |
| Secondary_engine_execution_count | 0     |
| Slow_queries                     | 298   | -- Both。耗时超过 long_query_time 秒的查询数。无论是否启用慢查询日志，此计数器都会递增。
+----------------------------------+-------+

数据包

-- 与数据包相关的系统变量
show variables where variable_name in (
    'max_allowed_packet'
);
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_allowed_packet  | 536870912 | -- Both。默认67108864(64MB)。控制单个数据包（单条SQL语句）的最大大小，影响 大字段存储、多行批量插入、存储过程和二进制日志。
+---------------------+-----------+

语句数量

-- 查询相关的状态变量
show global status where variable_name in (
    'Com_select',
    'Com_insert',
    'Com_update',
    'Com_delete',
    'Queries',
    'Questions'
);
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Com_delete    | 26145937   | -- Com_xxx 语句计数变量表示每个 xxx 语句被执行的次数。每种类型的语句都有一个状态变量。
| Com_insert    | 26150143   |
| Com_select    | 805128892  |
| Com_update    | 11688853   |
| Queries       | 4051726800 | -- Both。服务器执行的语句数量。此变量包含存储程序中执行的语句，与Questions变量不同。它不计入COM_PING或COM_STATISTICS命令。
| Questions     | 2751086323 | -- Both。服务器执行的语句数量。此变量仅统计客户端发送至服务器的语句，不包括存储程序内部执行的语句，与 Queries 变量不同。该变量不统计 COM_PING、COM_STATISTICS、COM_STMT_PREPARE、COM_STMT_CLOSE 或 COM_STMT_RESET 命令。
+---------------+------------+

日志

-- 日志管理
show variables where variable_name in (
    'log_bin',
    'binlog_format',
    'general_log',
    'slow_query_log'
);
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| binlog_format  | ROW   | -- Both。默认ROW。
| general_log    | ON    | -- Global。默认OFF。
| log_bin        | OFF   | -- Global。默认ON。
| slow_query_log | ON    | -- Global。默认OFF。
+----------------+-------+

事务

-- 事务控制
show variables where variable_name in (
    'autocommit',
    'innodb_flush_log_at_trx_commit',
    'transaction_isolation'
);
+--------------------------------+-----------------+
| Variable_name                  | Value           |
+--------------------------------+-----------------+
| autocommit                     | ON              | -- Both。默认ON。自动提交模式。禁用当前会话自动提交 SET autocommit=0;
| innodb_flush_log_at_trx_commit | 1               | -- Global。默认1。
| transaction_isolation          | REPEATABLE-READ | -- Both。默认REPEATABLE-READ。设置默认事务隔离级别。
+--------------------------------+-----------------+

-- 事务统计
show global status where variable_name in (
    'Handler_commit',
    'Handler_rollback'
);
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| Handler_commit   | 1214936199 | -- Both。事务提交次数。
| Handler_rollback | 9078630    | -- Both。事务回滚次数。
+------------------+------------+

字符集

-- 字符集设置
show variables where variable_name in (
    'character_set_server',
    'collation_server'
);
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| character_set_server | utf8mb4            | -- Both。默认utf8mb4。服务器默认字符集。
| collation_server     | utf8mb4_0900_ai_ci | -- Both。默认utf8mb4_0900_ai_ci。服务器默认排序规则。
+----------------------+--------------------+

锁与等待

-- 锁与等待
show global status where variable_name in (
    'Innodb_row_lock_waits',
    'Innodb_row_lock_time'
);
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Innodb_row_lock_time  | 131943249 | -- Global。获取 InnoDB 表的行锁所花费的总时间（以毫秒为单位）。
| Innodb_row_lock_waits | 2489499   | -- Global。InnoDB 表上的操作必须等待行锁的次数。
+-----------------------+-----------+

网络流量

-- 网络流量统计
show global status where variable_name in (
    'Bytes_received',
    'Bytes_sent'
);
+----------------+---------------+
| Variable_name  | Value         |
+----------------+---------------+
| Bytes_received | 1878077493070 |
| Bytes_sent     | 2773050821181 |
+----------------+---------------+

缓存

缓冲池：Buffer Pool，缓存表和索引数据的内存区域。

缓冲区：缓冲池划分的区域。

缓存池命中率：指的是 Buffer Pool 的命中率。

-- 缓存控制
show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 67108864       | -- 默认8388608(8MB)。用于 MyISAM 存储引擎，它控制 MyISAM 在执行批量插入时的缓冲区大小，可以提高批量插入的性能。
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | OFF            |
| innodb_buffer_pool_instances        | 8              | -- 非默认，默认为自动调整
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 8589934592     | -- Global。默认134217728(128MB)
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | none           |
| innodb_ddl_buffer_size              | 1048576        |
| innodb_log_buffer_size              | 67108864       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 8388608        | -- Both。默认262144(256KB)
| key_buffer_size                     | 8388608        |
| myisam_sort_buffer_size             | 67108864       | -- 非默认
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 2097152        | -- 默认131072(128KB)
| read_rnd_buffer_size                | 8388608        | -- 非默认
| select_into_buffer_size             | 131072         |
| set_operations_buffer_size          | 262144         |
| sort_buffer_size                    | 2097152        | -- Both。默认262144(256KB)
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+

缓存池命中率

show global status where variable_name like 'Innodb_buffer_pool_read%' or variable_name in (
    'Innodb_data_reads',
    'Innodb_data_writes'
);
+---------------------------------------+---------------+
| Variable_name                         | Value         |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0             |
| Innodb_buffer_pool_read_ahead         | 50067388      |
| Innodb_buffer_pool_read_ahead_evicted | 4011140       |
| Innodb_buffer_pool_read_requests      | 5507255725593 | -- Global。逻辑读，尝试从 Buffer Pool 中读取数据页的总次数。
| Innodb_buffer_pool_reads              | 1297867754    | -- Global。物理读，不能从 Buffer Pool 找到页，只能从磁盘读取数据页的次数。
| Innodb_data_reads                     | 80001         | -- Global。物理读磁盘的次数。Buffer Pool 未命中，需从磁盘把数据页读进buffer。一次IO可能读取多个页。
| Innodb_data_writes                    | 47413         | -- Global。物理写磁盘的次数。包括redo log、buffer的刷盘，每次刷盘就+1。
+---------------------------------------+---------------+

缓存池命中率 = 从缓存中命中的读次数 / 总读次数，即 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests。

理想值：命中率应在 99.9%+。如果命中率低，说明 innodb_buffer_pool_size 太小，需要增大。

select
  round((1 - (p2.variable_value / p1.variable_value)) * 100, 2) as innodb_buffer_pool_hit_rate 
from
  performance_schema.global_status p1, performance_schema.global_status p2 
where
  p1.variable_name = 'innodb_buffer_pool_read_requests' and p2.variable_name = 'innodb_buffer_pool_reads';

临时文件合并

如果一个查询需要排序（比如 ORDER BY），内存（sort buffer）放不下所有要排序的数据，那就会把数据分批写到磁盘临时文件，最后再做多路合并（merge passes），拼成有序数据。每完成一次这样的“磁盘归并”，Sort_merge_passes 就+1。

show global status where variable_name in (
    'Sort_merge_passes'
);
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 60162 | -- Both。执行排序（sort）时，临时文件合并的次数。如果此值较大，则应考虑增加 sort_buffer_size 系统变量的值。
+-------------------+-------+

重点变量详细解释

innodb_buffer_pool_size：以字节为单位。默认134217728(128MB)，最小5242880(5MB)。
innodb_buffer_pool_instances：InnoDB缓冲池划分的区域数。对于缓冲池大小在几GB范围内的系统，将缓冲池划分为单独的实例可以提高并发性，因为不同线程读取和写入缓存页面时会减少争用。使用哈希函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表、刷新列表、LRU 以及连接到缓冲池的所有其他数据结构，并受自己的缓冲池互斥锁保护。\n 总缓冲池大小在所有缓冲池之间分配。为了获得最佳效率，请指定 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的组合，以便每个缓冲池实例至少为 1GB。
innodb_buffer_pool_dump_pct：指定每个缓冲池要读出并转储的最近使用的页面的百分比。范围是 1 到 100。默认值为 25。例如，如果有 4 个缓冲池，每个缓冲池有 100 个页面，并且 innodb_buffer_pool_dump_pct 设置为 25，则会转储每个缓冲池中最近使用的 25 个页面。人话：MySQL正常关闭时把buffer中页信息保存到磁盘，启动后再从磁盘加载到buffer，实现服务器快速预热。
join_buffer_size：**为两个表之间的每个连接分配n个连接缓冲区。对于未使用索引的多个表之间的复杂连接，可能需要多个连接缓冲区。**用于普通索引扫描、范围索引扫描和全表扫描（无索引）的连接所使用的缓冲区的大小。如果设置得过大，可能会浪费内存资源，尤其是当有很多并发连接时。

join_buffer_size 只存储驱动表的部分数据（受buffer大小限制），Join 结果会输出到客户端或写入目标表，若是中间结果则使用临时表（内存临时表或磁盘临时表）。

当执行计划显示 Using join buffer ，小表驱动大表且小表 join 字段无法一次装入buffer，SHOW STATUS LIKE 'Select_full_join'; 计数高，请考虑优化索引和增加该变量值。

举例：

1、嵌套循环连接

Nested Loop Join：遍历驱动表(外层表)，每次拿一行，逐行扫描**被驱动表(内层表)**做匹配，无需额外的缓冲区。当内层表很大时，即便 on 条件列有索引，效率也不够高。

Block Nested Loop Join （BNLJ）：是Nested Loop Join的优化版，引入了缓存（buffer）批量读。外层表一次不是拿一行，而是拿一块（block）多行到内存（buffer），然后扫描内层表时，与外层表的一个 block 数据进行比较，减少磁盘IO或扫描次数，提高join查询效率。MySQL实际执行是Block版！

执行计划显示：Using Join buffer (Block Nested Loop)。

2、哈希连接（Hash join）MySQL 8.0

本质上是 Block Nested Loop Join（BNLJ）的一种特殊优化版！以驱动表(外层表)的 join 字段为 key 建成一个哈希表（Hash Table）， join buffer 用作缓存这个哈希表数据，和 BNLJ 相同（先装入buffer、扫描内层表）。

把小表建成一个哈希表（Hash Table）用于驱动表(外层表)，Key 是 join 字段，一次拿一块（block）多行到内存（buffer），然后扫描大表，每一行根据 join key 去哈希表查找匹配。

执行计划显示：Using join buffer (hash join)。

3、合并连接（merge join / Sort Merge Join）

MySQL 8.0之后，开始正式引入对Sort-Merge Join的一些优化策略（主要是Hash Join更主流）。

一般情况下，两表都有序（走索引或排序后），场景：大表 JOIN 大表。
sort_buffer_size：**必须执行sort的每个连接都会分配一个该大小的缓冲区。**不特定于任何存储引擎，并且以通用方式应用以进行优化。值必须足够大，以便在排序缓冲区中容纳十五个元组。增加 max_sort_length 的值可能需要增加 sort_buffer_size 的值。如果您在 SHOW GLOBAL STATUS 输出中看到每秒有许多 Sort_merge_passes，则可以考虑增加 sort_buffer_size 值以加快无法通过查询优化或改进索引来改进的 ORDER BY 或 GROUP BY 操作。默认262144(256KB)，最小32768(32KB)。

1、有索引的 sort 操作：如果在排序时能利用合适的索引，MySQL 会直接利用索引进行排序，而无需使用 sort_buffer_size。

2、无索引的 sort 操作：只要不能使用索引完成排序（ORDER BY / GROUP BY），就需要借助 sort_buffer_size 完成，执行计划会显示 Using filesort 。当排序数据超出 sort_buffer_size，就会利用外部磁盘排序。若同时 SHOW STATUS LIKE 'Sort_merge_passes'; 计数高，请考虑优化索引和增加该变量值。

临时表

临时表是数据库中用于存储临时数据的表，主要用于 group by、子查询、复杂查询的中间结果存储。也可手动创建。

仅对创建它的会话可见，其他会话无法访问，会话结束或连接关闭时自动删除。

主要作用：

存储复杂查询的中间结果，能够提高性能或简化查询逻辑。
减少对主表的操作负载。
数据清洗和转换。

使用场景：

数据转换和清洗。
临时存储计算结果。
提高复杂查询的性能。
分批处理大量数据。
简化复杂的多步骤查询。

创建：

CREATE TEMPORARY TABLE temp_table (
    id INT,
    name VARCHAR(50)
);

CREATE TEMPORARY TABLE temp_data AS
SELECT * FROM raw_data WHERE status = 'pending';

DROP TEMPORARY TABLE temp_table;

设置：

-- 临时表控制
show variables like '%table_size%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+ -- 默认16777216(16MB)。
| max_heap_table_size | 536870912 | -- Both。每个连接 查询过程中由优化器创建的内存临时表(MEMORY引擎) 和 手动创建的MEMORY引擎表 的最大大小。
| tmp_table_size      | 536870912 | -- Both。每个连接 查询过程中由优化器创建的内存临时表(MEMORY引擎) 的最大大小。
+---------------------+-----------+ -- 内部临时表的最大上限是这两个参数中较小的那个。

[!NOTE]

内存临时表（MEMORY）：当临时表较小且符合 tmp_table_size 和 max_heap_table_size 限制时，MySQL 会使用 MEMORY 引擎来存储临时表。

磁盘临时表（InnoDB）：如果临时表的大小超过内存限制，MySQL会将临时表从内存转移到磁盘，并使用磁盘上的存储引擎保存（8.0.16起仅支持InnoDB）。

建议：tmp_table_size 应该等于 max_heap_table_size。

了解 MySQL 在执行查询时创建临时表的情况：

-- 临时表统计
show global status like '%tmp%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Created_tmp_tables      | 932876216 | -- Both。所有创建的临时表的计数，不论临时表是存储在内存还是磁盘上。
| Created_tmp_disk_tables | 36731     | -- Both。在磁盘上创建的临时表的计数。
| Created_tmp_files       | 201307    | -- Global。在磁盘上创建的临时文件的计数。
+-------------------------+-----------+

磁盘临时表 和 临时文件 ：

临时文件 是 MySQL 用于存储 磁盘临时表 的物理文件。当 MySQL 需要将临时表存储在磁盘上时，它会创建这些临时文件以保存表的数据。临时文件通常存储在系统的临时目录中（由 tmpdir 参数指定）。

当 MySQL 创建磁盘临时表时，通常会为每个磁盘临时表生成一个或多个临时文件。这些文件用于存储临时表中的数据。通常一个磁盘临时表会有多个文件，特别是在临时表非常大时。例如，执行一个复杂查询时，MySQL 创建了一个磁盘临时表来存储中间结果数据，这个临时表会分配若干个临时文件，以便在磁盘上存储数据。查询结束后，这些临时文件和临时表会被删除。

临时表落盘率

临时表落盘率 = Created_tmp_disk_tables / Created_tmp_tables。

如果临时表落盘率较高（理想值：< 10%），可以增大临时表，让更多的临时表保存在内存中，减少磁盘操作，前提是内存扛得住。
如果 Created_tmp_tables 值过高，检查查询中是否有不必要的临时表生成，尝试优化查询以减少临时表的使用，当查询无法优化时再增大临时表。

select
 round((disk.created_tmp_disk_tables / total.created_tmp_tables) * 100, 2) as tmp_disk_table_rate 
from
 (select variable_value as created_tmp_disk_tables from performance_schema.global_status where variable_name = 'created_tmp_disk_tables') disk,
 (select variable_value as created_tmp_tables from performance_schema.global_status where variable_name = 'created_tmp_tables') total;

MySQL 版本：创新和 LTS

MySQL 的发布模型分为两个主要版本：长期支持（LTS）和创新版本。所有 LTS 和创新版本均包含漏洞修复和安全更新，并被视为生产级质量。

MySQL 程序

https://dev.mysql.com/doc/refman/8.4/en/programs.html

MySQL 程序概述

https://dev.mysql.com/doc/refman/8.4/en/programs-overview.html

使用 MySQL 程序

https://dev.mysql.com/doc/refman/8.4/en/programs-using.html

https://dev.mysql.com/doc/refman/8.4/en/program-options.html 指定程序选项（指定选项文件、处理顺序等）。

服务器和服务器启动程序

https://dev.mysql.com/doc/refman/8.4/en/programs-server.html

mysqld

https://dev.mysql.com/doc/refman/8.4/en/mysqld.html

mysqld（MySQL 服务器）命令会读取选项文件中 [mysqld] 选项组。

mysqld —— MySQL 服务器主程序

全称：MySQL Daemon
作用：这是 MySQL 数据库服务本身，负责启动数据库、监听端口、处理连接和 SQL。
使用场景：
- 启动数据库服务（最底层命令）
- 通常由 systemd 或 mysqld_safe 调用

常见用法：

1	mysqld --defaults-file=/etc/my.cnf --user=mysql

mysqld_safe

https://dev.mysql.com/doc/refman/8.4/en/mysqld-safe.html

mysqld_safe 是 MySQL 服务启动脚本，内部调用 mysqld，所以会读取选项文件中 [mysqld]、[server] 和 [mysqld_safe] 选项组。

作用：启动 mysqld，并提供：
- 日志写入功能
- 崩溃时自动重启（更稳定）
- 设置 ulimit、环境变量等预处理操作
使用场景：
- 手动或早期系统中用于更安全地启动 MySQL
- 现代系统中已逐步被 systemd 替代

常见用法：

1	mysqld_safe --defaults-file=/opt/local/mysql/my.cnf --defaults-group-suffix=@3306 --user=mysql &

mysql.server

https://dev.mysql.com/doc/refman/8.4/en/mysql-server.html

/opt/local/mysql/support-files/mysql.server 脚本通过 mysqld_safe 启动 MySQL 服务器，它读取选项文件中的 [mysql.server] 和 [mysqld] 选项组。为了向后兼容，它也会读取 [mysql_server] 部分，但为了保持最新，您应该将此类部分重命名为 [mysql.server]。

mysqld_multi

https://dev.mysql.com/doc/refman/8.4/en/mysqld-multi.html

mysqld_multi 命令会在 my.cnf 文件（或由 –defaults-file 选项指定的文件）中搜索名为 [mysqldN] 的选项组。N（选项组编号或 GNR）用于区分不同的选项组，并作为 mysqld_multi 命令的参数，用于指定要启动、停止或获取状态报告的服务器。这些组中列出的选项与用于启动 mysqld 的 [mysqld] 组中使用的选项相同。有关在多服务器环境中哪些选项必须在每个服务器上唯一的更多信息，请参阅第 7.8 节 “在同一台机器上运行多个 MySQL 实例”。

另外，/opt/local/mysql/support-files/mysqld_multi.server 脚本也使用此命令。mysqld_multi 命令会在选项文件中搜索 [mysqld_multi] 和 [mysqldN] 选项组。[mysqld_multi] 组可用于设置 mysqld_multi 命令本身的相关选项。[mysqldN] 组可用于设置传递给特定 mysqld 实例的选项。

--mysqld ：mysqld_multi 独有选项，指定实际启动每个 MySQL 实例时所调用的程序。如果使用 mysqld_safe 启动服务器，就可以在 [mysqldN] 选项组中包含 mysqld 选项。

mysqld_multi —— 多实例管理工具

作用：用于管理（启动、停止、查看状态）多个 MySQL 实例。
原理：从 [mysqldN] 组读取配置，分别启动多个 mysqld。
使用场景：
- 在同一台机器上运行多个端口（如 3306、3307、3308）
- 适用于测试、隔离环境、主从部署

配置示例（写在 my.cnf 中）：

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/opt/mysql/multi.log

[mysqld3306]
port=3306
datadir=/opt/mysql/3306/data
...

[mysqld3307]
port=3307
datadir=/opt/mysql/3307/data
...

常用命令：

1
2
3

mysqld_multi start 3306,3307
mysqld_multi stop 3306
mysqld_multi report

客户端程序

https://dev.mysql.com/doc/refman/8.4/en/programs-client.html

mysql

https://dev.mysql.com/doc/refman/8.4/en/mysql.html

mysql —— 主命令行客户端

作用：连接 MySQL 执行 SQL 命令。
最常用的工具，你可以用它执行所有 DDL/DML/查询等操作。

1	mysql --defaults-file=/opt/local/mysql/my.cnf -h 127.0.0.1 -P 3306 -u root -p123456789 basedb --ssl-mode=disable

以 –ssl 开头的选项用于指定是否使用加密连接到服务器，并指示 SSL 密钥和证书的位置。

mysqladmin

https://dev.mysql.com/doc/refman/8.4/en/mysqladmin.html

mysqladmin —— 数据库管理工具

作用：控制/监控 MySQL 服务。
适合 DBA 做服务级操作

1
2
3

mysqladmin -h 127.0.0.1 -P 3306 -u root -p123456789 shutdown           # 安全关闭服务
mysqladmin -h 127.0.0.1 -P 3306 -u root -p123456789 status             # 显示连接数、查询等状态
mysqladmin -h 127.0.0.1 -P 3306 -u root -p123456789 flush-privileges   # 刷新权限表

mysqldump

https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html mysqldump 选项可以在 命令行中 或 选项文件的 [mysqldump] 和 [client] 组中 指定。

mysqldump —— 逻辑备份工具

作用：导出数据库、表结构、数据。
输出为 SQL 文件，可用来恢复、迁移或版本控制。

mysqlimport

https://dev.mysql.com/doc/refman/8.4/en/mysqlimport.html

mysqlimport —— 快速数据导入工具

作用：将 CSV/TSV 文件导入 MySQL（基于 LOAD DATA INFILE）。
比逐行插入 SQL 更快

mysqlshow

https://dev.mysql.com/doc/refman/8.4/en/mysqlshow.html

mysqlshow —— 快速查看元数据工具

作用：查看数据库、表、字段结构。
不进入 MySQL 客户端即可查看结构

1
2
3

mysqlshow -h 127.0.0.1 -P 3306 -u root -p123456789                      # 显示所有数据库
mysqlshow -h 127.0.0.1 -P 3306 -u root -p123456789 basedb               # 显示数据库中的表
mysqlshow -h 127.0.0.1 -P 3306 -u root -p123456789 basedb t_base_user   # 显示表结构（字段）

MySQL 服务器管理

https://dev.mysql.com/doc/refman/8.4/en/server-administration.html

MySQL服务器

https://dev.mysql.com/doc/refman/8.4/en/mysqld-server.html

https://dev.mysql.com/doc/refman/8.4/en/server-configuration-validation.html 服务器配置验证

https://dev.mysql.com/doc/refman/8.4/en/server-option-variable-reference.html mysqld 中适用的所有命令行选项、系统变量和状态变量。

列名	含义
Cmd-Line	✅ 是否可以通过命令行（如 `mysqld --port=3307`）设置该选项。即是否是服务器启动参数。
Option File	✅ 是否可以在配置文件（如 `my.cnf` 或 `my.ini`）中设置该参数。
System Var	✅ 是否为系统变量，可通过 `SET` 或 `SHOW VARIABLES` 查看或设置，如 `SET GLOBAL max_connections = 200;`。
Status Var	✅ 是否为状态变量，可通过 `SHOW STATUS` 查看运行时统计信息，比如连接数、IO 等。
Var Scope	标明变量作用域是 `Global`（全局）、`Session`（会话）或两者都支持（`Both`）。
Dynamic	✅ 是否支持动态修改。比如如果是动态的，就可以在不重启服务器的情况下用 `SET GLOBAL` 修改；如果是 No，则必须重启 MySQL 服务才能生效。

MySQL服务器插件

https://dev.mysql.com/doc/refman/8.4/en/server-plugins.html

安全

访问控制和账户管理

MySQL 提供的权限

https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html

设置账户资源限制

https://dev.mysql.com/doc/refman/8.4/en/user-resources.html

用户级别的限制：最大用户连接数、最大每小时查询数、最大每小时更新数、最大每小时连接数

使用加密连接

https://dev.mysql.com/doc/refman/8.4/en/encrypted-connections.html

配置 MySQL 使用加密连接

https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html

有多个配置参数可用于指定是否使用加密连接，以及指定相应的证书和密钥文件。本节提供关于配置服务器和客户端以支持加密连接的通用指南：

服务器端加密连接的启动配置
服务器端加密连接的运行时配置和监控
客户端加密连接配置 mysql --ssl-mode=disable
配置证书验证强制执行
配置加密连接为强制要求

加密连接 TLS 协议和密码

https://dev.mysql.com/doc/refman/8.4/en/encrypted-connection-protocols-ciphers.html

备份和恢复

https://dev.mysql.com/doc/refman/8.4/en/backup-and-recovery.html

https://dev.mysql.com/doc/refman/8.4/en/backup-types.html 备份和恢复类型

https://dev.mysql.com/doc/refman/8.4/en/backup-methods.html 数据库备份方法

使用 mysqldump 进行备份

https://dev.mysql.com/doc/refman/8.4/en/using-mysqldump.html

SQL 格式-常用

使用 mysqldump 转储 SQL 格式的数据：

打印从选项文件中获取的所有选项：

1	mysqldump --defaults-file=/opt/local/mysql/my.cnf --print-defaults

场景：将数据库从一个服务器复制到另一个服务器。

1
2
3

# 使用 --all-databases 或 --databases 选项时，mysqldump 会在每个数据库的导出输出之前写入 CREATE DATABASE 和 USE 语句。这确保了当导出文件被重新加载时，如果数据库不存在，它会创建该数据库并将其设为默认数据库，从而将数据库内容加载到与原始数据库相同的数据库中。若需强制在重建数据库前先删除每个数据库，请同时使用 --add-drop-database 选项。此时，mysqldump 会在每个 CREATE DATABASE 语句前写入 DROP DATABASE 语句。
mysqldump -S /tmp/mysql_3306.sock -u root -p123456789 --all-databases > dump.sql
mysqldump -S /tmp/mysql_3306.sock -u root -p123456789 --databases basedb archedb > dump.sql

场景：制作数据库副本。

# 在单数据库情况下，可以省略 --databases 选项，如果不使用 --databases 选项，导出文件中将不包含任何 CREATE DATABASE 或 USE 语句。这有以下几点影响：
# 1、当你重新加载导出文件时，必须指定一个默认数据库名称，以便服务器知道要重新加载哪个数据库。
# 2、在重新加载时，您可以指定与原始名称不同的数据库名称，从而将数据重新加载到不同的数据库中。
# 3、如果要重新加载的数据库不存在，您必须先创建它。
# 4、由于输出中不包含 CREATE DATABASE 语句，因此 --add-drop-database 选项无效。如果您使用该选项，它将不会生成 DROP DATABASE 语句。
mysqldump -h 127.0.0.1 -P 3306 -u root -p123456789 basedb > basedb.sql
mysqldump -h 127.0.0.1 -P 3306 -u root -p123456789 basedb t_base_country t_base_nation > basedb.sql # 指定导出表


# 常用选项：
# --quick				此选项适用于导出大型表。它强制 mysqldump 每次从服务器获取表中的一行数据，而不是先将整个行集从服务器获取并缓存在内存中，然后再写出。
# --single-transaction	此选项将事务隔离级别设置为可重复读（REPEATABLE READ），并在导出数据前向服务器发送START TRANSACTION SQL语句。该选项仅适用于事务性表（如InnoDB），因为它能在不阻塞任何应用程序的情况下，导出在START TRANSACTION语句执行时数据库的一致状态。要导出大型表，请将 --single-transaction 选项与 --quick 选项结合使用。
# --skip-lock-tables	避免表锁，因为 --single-transaction 比 --lock-tables 更优。实测，加了也没效果，也许是某些选项强制锁表。
# --hex-blob			使用十六进制表示法导出二进制列。受影响的数据类型包括 BINARY、VARBINARY、BLOB 类型、BIT、所有空间数据类型 等等。
# --create-options      在 CREATE TABLE 语句中包含所有 MySQL 特定的表选项（ENGINE=、AUTO_INCREMENT=、COLLATE=）。似乎没有相反的选项！！！
# --routines --triggers --events   导出存储过程和函数，导出触发器，导出事件调度器
# --insert-ignore		使用INSERT IGNORE语句而非INSERT语句。
# --extended-insert		扩展插入语句（一次插多行），可提高插入效率。使用包含多个VALUES列表的多行语法编写INSERT语句。这将生成较小的导出文件，并在重新加载文件时加快插入速度。
# --replace             使用REPLACE语句而非INSERT语句。
# --add-drop-table      在每个CREATE TABLE语句之前编写一个DROP TABLE语句。
# --order-by-primary    按表的主键排序导出每张表的行，如果存在第一个唯一索引，则按该索引排序。这在将MyISAM表导出以加载到InnoDB表时非常有用，但会使导出操作耗时显著增加。
mysqldump -h 127.0.0.1 -P 3306 -u root -p123456789 \
  --quick \
  --single-transaction \
  --hex-blob \
  --create-options \
  --routines --triggers --events \
  --insert-ignore \
  --extended-insert \
  --add-drop-table \
  --order-by-primary \
  basedb > basedb.sql


# --no-data 			选项指示 mysqldump 不要导出表数据，导出文件仅包含创建表的语句。
# --no-create-info 		选项指示 mysqldump 从输出中抑制 CREATE 语句，导出文件仅包含表数据。
# 例如，要分别导出 basedb 库的定义和数据，请使用以下命令：
mysqldump -h 127.0.0.1 -P 3306 -u root -p123456789 --no-data --routines --triggers basedb > basedb-defs.sql
mysqldump -h 127.0.0.1 -P 3306 -u root -p123456789 --no-create-info basedb > basedb-data.sql

重新加载 SQL 格式的备份：

# 使用 --all-databases 或 --databases 选项生成的备份文件包含 CREATE DATABASE 和 USE 语句，无需指定默认数据库来加载数据。否则必须指定数据库：
$> mysql -h 127.0.0.1 -P 3306 -u root -p123456789 basedb < basedb.sql
# 或者，在 MySQL 中使用 source 命令：
mysql> source /opt/local/filebrowser/admin/backup/basedb.sql

分隔文本格式-了解

使用 mysqldump 转储分隔文本格式的数据：

如果你使用 –tab=dir_name 选项调用 mysqldump，它会将 dir_name 作为输出目录（依赖 secure_file_priv 的设置，指定的目录必须是 secure_file_priv 允许写入的路径），并在此目录中单独导出每个表，每个表使用两个文件。表名是这些文件的基础名称。对于名为 t1 的表，文件名为 t1.sql 和 t1.txt。 .sql 文件包含该表的 CREATE TABLE 语句。 .txt 文件包含表数据，每行对应表中的一行数据。

服务器将导出表的 CREATE 定义发送给 mysqldump，后者将其写入 .sql 文件。因此，这些文件的所有者是执行 mysqldump 的用户。

包含表格数据的.txt文件由服务器生成，因此这些文件归属于运行服务器的系统账户。服务器使用 SELECT … INTO OUTFILE 语句来写入这些文件，因此您必须具备FILE权限才能执行此操作，如果指定的.txt文件已存在，则会发生错误。

对于 –tab=dir_name，服务器默认将表数据写入 .txt 文件，每行一行，列值之间用制表符分隔，列值周围没有引号，换行符作为行终止符。（这些默认设置与 SELECT … INTO OUTFILE 相同。）

要使数据文件能够使用不同的格式进行写入，mysqldump 支持以下选项：

--fields-terminated-by=str				# 用于分隔列值的字符串（默认：制表符）。
--fields-enclosed-by=char				# 用于包围列值的字符（默认：无字符）。
--fields-optionally-enclosed-by=char	# 用于包围非数字列值的字符（默认：无字符）。
--fields-escaped-by=char				# 用于转义特殊字符的字符（默认：不转义）。
--lines-terminated-by=str				# 行结束字符串（默认：换行符）。

根据您为这些选项中的任何一个指定的值，可能需要在命令行中对值进行适当的引号或转义处理，以适应您的命令解释器。或者，您可以使用十六进制表示法指定值。假设您希望 mysqldump 在双引号内引用列值。为此，请将双引号指定为 –fields-enclosed-by 选项的值。但此字符通常对命令解释器具有特殊含义，因此必须特殊处理。例如，在 Unix 系统中，您可以通过以下方式引用双引号：

1
2
3

--fields-enclosed-by='"'
# 在任何平台上都使用十六进制格式指定该值。ASCII 码共定义了 128 个字符，双引号对应的的 ASCII 值是 34，十进制转成十六进制就是 22
--fields-enclosed-by=0x22

通常会同时使用多个数据格式化选项。例如，以逗号分隔值格式导出表格、每行以换行符（\n 或 \r\n）结尾，请使用以下命令（将其输入到一行中）：

mysqldump -h 127.0.0.1 -P 3306 -u root -p123456789 \
  --tab=/opt/local/mysql/3306/mysql-files \
  --fields-terminated-by=',' \
  --lines-terminated-by='\n' \
  basedb t_base_country t_base_nation

# 只导出包含表格数据的.txt文件
mysql> SELECT * INTO OUTFILE '/opt/local/mysql/3306/mysql-files/t_base_country.txt'
          fields terminated by ','
          lines terminated by '\n'
          FROM t_base_country;

如果您使用任何数据格式化选项导出表数据，则在后续重新加载数据文件时，必须指定相同的格式，以确保文件内容能被正确解析。

建议仅将 –tab 选项用于导出本地服务器。若与远程服务器配合使用，–tab 目录必须在本地和远程主机上均存在，且 .txt 文件由服务器写入远程目录（服务器主机上），而 .sql 文件则由 mysqldump 写入本地目录（客户端主机上）。

重新加载分隔文本格式的备份：

使用 mysqldump –tab 命令生成的备份中，每个表在输出目录中由一个 .sql 文件和一个 .txt 文件表示。其中，.sql 文件包含该表的 CREATE TABLE 语句，而 .txt 文件包含该表的数据。

首先切换到输出目录，然后使用 mysql 处理 .sql 文件以创建一个空表：

1 2	cd /opt/local/mysql/3306/mysql-files mysql -h 127.0.0.1 -P 3306 -u root -p123456789 basedb < t_base_country.sql

再使用 mysqlimport 处理 .txt 文件以将数据加载到表中，要求导入文件名必须与表名一致（如 t_base_country.csv ➜ t_base_country 表）。使用 mysqlimport 加载数据文件的替代方法是通过 mysql 客户端使用 LOAD DATA 语句：

1
2
3

mysqlimport -h 127.0.0.1 -P 3306 -u root -p123456789 basedb t_base_country.txt
# 替代方法
mysql> load data infile 't_base_country.txt' into table basedb.t_base_country;

如果您在最初导出表时使用了任何数据格式化选项，则在使用 mysqlimport 或 LOAD DATA 导入数据时，必须使用相同的选项，以确保正确解析数据文件的内容：

mysqlimport -h 127.0.0.1 -P 3306 -u root -p123456789 \
  --fields-terminated-by=',' \
  --lines-terminated-by='\n' \
  basedb t_base_country.txt t_base_nation.txt
# 替代方法
mysql> load data infile '/opt/local/mysql/3306/mysql-files/t_base_country.txt' into table basedb.t_base_country
       fields terminated by ','
       lines terminated by '\n';

优化

优化概述

https://dev.mysql.com/doc/refman/8.4/en/optimize-overview.html

优化 SQL 语句

https://dev.mysql.com/doc/refman/8.4/en/statement-optimization.html

优化和索引

https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html

https://www.postgresql.org/docs/current/indexes.html

https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/indexes-and-index-organized-tables.html

要提升SELECT操作的性能，最佳方法是在查询中涉及的列上创建索引。索引条目类似于指向表行的指针，使查询能够快速确定哪些行符合WHERE子句中的条件，并检索这些行的其他列值。MySQL的所有数据类型均可创建索引。

尽管可能有诱惑力为查询中使用的每个可能列创建索引，但不必要的索引会浪费空间并浪费 MySQL 确定使用哪个索引的时间。索引还会增加插入、更新和删除的成本，因为每个索引都必须更新。您必须找到合适的平衡点，以使用最优的索引集实现快速查询。

索引失效场景

联合索引的使用不满足最左前缀原则

索引列使用了函数或参与运算：where id / 10 > 8

4、like 的占位符位于条件的首部

5、索引列发生隐式转换（若索引字段为int，参数为varchar，mysql会将参数转化为int类型，依旧会走索引）

6、or两边出现非索引列

7、or两边出现 “>” 和 “<” 范围查询，即便是索引列也不会走

8、两个索引列做比较

使用 “<>” 或 “!=” 作为条件查询时，需要慎重，普通索引会查询结果集占比，较大时索引会失效。

is not null；如果加上 limit n，当 n 很小时索引有效。

not in：主键索引有效；普通索引失效；普通索引 + limit n，当 n 很小时索引有效。

12、not exists：索引失效。

13、当进行范围查询（>、< 、>=、<=、in等条件）时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。举例，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。

优化数据库结构

https://dev.mysql.com/doc/refman/8.4/en/optimizing-database-structure.html

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html 表列数和行大小的限制

了解执行计划

https://dev.mysql.com/doc/refman/8.4/en/execution-plan-information.html

https://www.postgresql.org/docs/current/indexes-examine.html

https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/preface.html

使用 EXPLAIN 来分析查询语句的执行计划，了解 MySQL 优化器如何实际执行 SQL，从而优化性能。EXPLAIN 输出的关键内容包括：

执行计划：分析查询语句explain或describe(表的读取顺序、读取数据的访问类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、使用索引长度、每张表有多少行被优化器查询)。

id：id值越大，优先级越高，越先执行；id相同是一组，从上往下顺序执行；id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好；
type：表的访问类型，从好到坏：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL
system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory。
const：根据主键或者唯一二级索引列与常数进行等值匹配时出现，比如对单表使用where等值匹配查询就是const。
eq_ref：对被驱动表的访问方法。在连接查询时，当被驱动表通过主键或者唯一二级索引列等值匹配的方式进行访问情况下会出现。
ref：当通过普通的二级索引列与常量进行等值匹配时，对表的访问类型就可能是ref。
ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，对表的访问类型就可能是ref_or_null。
index_merge：索引合并，利用多个索引，key 和 key_len 是多个值。一般情况下对于某个表的查询只能使用到一个索引，当使用 or、union、union all 时会出现？
unique_subquery：子查询包含IN，若查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配时出现。
index_subquery：子查询使用普通索引
range：范围索引，使用 in、between、>、< 时会出现。
index：当使用索引覆盖，但需要扫描全部的索引记录时出现。覆盖索引：非聚簇复合索引包括查询里的SELECT、JOIN和WHERE子句用到的所有列（ 索引列+主键 包含 SELECT 到 FROM之间查询的列 ），无需回表。联合索引不能直接进行ref和range访问，只能扫描整个索引的记录。
all：全表扫描。
possible_keys和key：可能用到的索引 和 实际使用的索引。一个字段可能存在多个索引（单列索引、联合索引）。
key_len：实际使用到的索引长度，帮助检查 是否充分的利用了索引，值越大越好，主要对 联合索引 有参考意义。
ref：当前索引被哪个列(前一张表的关联字段)或常量(where=’a’)使用。
rows：预估的需要读取或扫描的行数，即使用索引过滤后剩下的行数。值越大性能越差。
filtered：最终行数 占 通过指定索引过滤剩下的行数（即 rows） 的百分比。
Extra：包含不适合在其他列中显示但十分重要的额外信息，帮助更准确的理解MySQL到底将如何执行给定的查询语句，额外信息有好几十个，官网 EXPLAIN Extra Information 。举例：

No tables used：没有FROM子句时。
Impossible WHERE：where子句永远为 FALSE 时，即查询结果为0行。
Using where：使用了 where 过滤。
Using index：仅使用索引树中的信息即可从表中检索列信息（覆盖索引），而无需执行额外的查找来读取实际行（无需回表）。
Using index condition：where中虽然出现了索引列，却不能使用索引。比如 WHERE key1 > 'z' AND key1 LIKE '%a';，最后一个条件未使用索引。
Using join buffer (Block Nested Loop)：官方：将早期连接的表分部分读入连接缓冲区，然后使用缓冲区中的行与当前表执行连接。也就是说，EXPLAIN 输出中上一行表中的键被缓冲，并且从出现 Using join buffer 的行所表示的表中分批获取匹配的行。尚硅谷：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。
Using sort_union(...), Using union(...), Using intersect(...)：访问类型为 index_merge 时会出现。
Using temporary：查询过程中需要创建临时表来保存中间结果。比如包含 distinct、group by、order by、union 等子句的查询过程中，如果不能有效利用索引完成查询，很可能会建立临时表。最好使用索引替换掉临时表。
Using filesort：排序时没走索引，利用 sort_buffer 或外部磁盘排序。若使用 文件排序 检索的行非常多，必须优化为 索引排序 检索。

优化 MySQL 服务器

https://dev.mysql.com/doc/refman/8.4/en/optimizing-server.html

调优总结

先查看慢查询日志或通过 performance_schema 找到慢的 SQL。接着用 EXPLAIN 看执行计划，关注是否用了索引、是否 filesort 或临时表。检查 SQL 是否有函数包列、隐式转换、返回行数是否过多。根据具体问题会考虑加索引、改写 SQL 或做数据结构优化等。

先看总耗时（events_statements）
再拆分阶段（events_stages）
看是否有等待（events_waits）
配合 EXPLAIN ANALYZE 看是否计划走错方向
排除客户端网络问题

慢查询日志文件轮转

默认 Linux 系统中，logrotate 是每天跑一次：

# 列出 /etc 目录中与 cron 相关的配置目录或文件
ll /etc | grep cron
# 查看 logrotate 默认每天执行了什么
cat /etc/cron.daily/logrotate
# logrotate 默认配置文件：
cat /etc/logrotate.conf

创建配置文件 truncate -s 0 /etc/logrotate.d/mysql-slow-3306 && vim /etc/logrotate.d/mysql-slow-3306：

/opt/local/mysql/3306/data/localhost-slow.log {
#轮转设置
    # 最多保留多少个“历史轮转日志文件”
    rotate 10
    # 日志文件被移动到指定目录进行轮换。该目录必须与被轮换的日志文件位于同一物理设备上，除非使用了copy、copytruncate或renamecopy选项。除非指定了绝对路径名，否则该目录默认相对于存储日志文件的目录。当使用此选项时，所有旧版本的日志文件都会被移动到指定目录。此选项可被noolddir选项覆盖。
    olddir ./

#轮转频率控制
    # 日志文件每小时轮换一次。请注意，hourly、daily、weekly、monthly 都不是定时器，它们只是条件标签。真正决定“什么时候轮转日志”的，是系统定期运行 logrotate 的方式（如 cron 或 systemd.timer）。
    #daily
    # 只有超过指定大小的日志才会轮转，不看时间。与 hourly、daily、weekly、monthly 等时间选项互斥，如果你同时写了 daily 和 size，则最后写的生效。使用场景：只想根据大小轮转，忽略时间。
    size 100M

#文件筛选规则
    # 如果日志文件缺失，则继续处理下一个文件，且不显示错误信息。
    missingok
    # 如果日志为空，则不轮转日志（此设置将覆盖 ifempty 选项）。
    notifempty
    # 删除旋转后超过多少天的日志文件。仅在日志文件需要旋转时才会检查日志文件的年龄。如果已配置maillast和mail，则文件将被发送到配置的地址。
    maxage 30
    # 如果日志文件超过 maxsize，就轮转，不等待 daily/weekly 等时间条件到达。使用 maxsize 时，logrotate 会综合考虑文件大小和轮转时间，谁先满足就触发y一次轮转。与时间（daily/weekly）兼容。maxsize 和 size 选择一个即可，若想忽略时间，请使用 size。
    #maxsize 100M

#Files and Folders
    # 轮转完成后、postrotate 脚本执行前，立刻创建一个新的空日志文件，文件名和被轮转的原日志文件相同。可以用 nocreate 来关闭 create 的效果，即轮转后不新建日志文件。
    #create 640 mysql mysql
    # 不重命名、不移动原始日志文件，而是先复制一份，然后把原日志文件就地清空（置零）。从复制到清空之间，日志文件仍在增长，所以这个瞬间写入的数据可能丢失。用了 copytruncate，日志文件不会被新建，所以 create 选项设置的权限、属主都无效。olddir 可以放在另一个挂载点（设备），copytruncate 支持这种行为。扩展：有些程序无法接收重新打开日志文件的信号，比如 MySQL、Nginx 如果没有专门信号或命令，就会继续写老文件。这时，如果你重命名了旧日志，它仍然写的是“被你重命名但文件句柄未关闭”的文件。
    copytruncate

#Compression
    # 不压缩旧日志
    nocompress

#Filenames
    start 0

#Mail
    # 当日志被轮换删除时，它将被发送到指定地址。如果某个日志不应生成邮件，可以使用 nomail 指令。注意：需要安装并配置邮件服务（MTA），步骤如下：
    # 1、安装 postfix：yum install postfix
    # 2、配置 Postfix 使用外部 SMTP 服务器：vim /etc/postfix/main.cf
    mail v_zhaolanqi@baidu.com
}

强行轮转一次，可用于测试配置是否有效L：

1	logrotate -f /etc/logrotate.d/mysql-slow-3306

每小时执行一次，vim /etc/cron.hourly/logrotate-hourly ：

#!/bin/bash

# -f 表示强制轮转
#/usr/sbin/logrotate -f /etc/logrotate.d/mysql-slow-3306
/usr/sbin/logrotate /etc/logrotate.d/mysql-slow-3306

赋予可执行权限：

1	chmod +x /etc/cron.hourly/logrotate-hourly

找到慢SQL之慢查询日志

临时开启慢查询 SET GLOBAL slow_query_log=on; 。慢查询日志分析工具 mysqldumpslow （只能在 MySQL 服务器本地运行）用于从 慢查询日志文件（slow query log）中提取和汇总慢 SQL 语句。mysqldumpslow 官网手册：https://dev.mysql.com/doc/refman/8.4/en/mysqldumpslow.html

# 显示 平均查询时间最多的前10条SQL，且不进行参数抽象化（保留具体数值）。>>>>>> t表示查询时间，at表示平均查询时间。
mysqldumpslow -s at -t 10 -a /opt/local/mysql/3306/data/localhost-slow.log
# 显示 平均锁等待时间最多的前10条SQL。>>>>>> l表示锁等待时间，al表示平均锁等待时间。
mysqldumpslow -s al -t 10 -a /opt/local/mysql/3306/data/localhost-slow.log
# 显示 平均返回行数最多的 SQL。>>>>>> r表示返回行数，ar表示平均返回行数。
mysqldumpslow -s ar -t 10 -a /opt/local/mysql/3306/data/localhost-slow.log
# 显示 执行次数最多的前10条SQL。
mysqldumpslow -s c -t 10 -a /opt/local/mysql/3306/data/localhost-slow.log

# 显示 平均查询时间最多、含有"left join" 的前10条SQL，且不进行参数抽象化（保留具体数值）。
mysqldumpslow -s at -g "left join" -t 10 -a /opt/local/mysql/3306/data/localhost-slow.log
# 结合 | 和 more 使用，防止爆屏情况出现
mysqldumpslow -s at -g "left join" -t 10 -a /opt/local/mysql/3306/data/localhost-slow.log | more

输出说明（样例）

1 2	Count: 18 Time=23.07s (415s) Lock=0.00s (0s) Rows=3596.0 (64728) SELECT * FROM users WHERE age = N AND status = N;

含义：

Count: 18：执行了 18 次
Time=23.07s (415s)：平均每次执行耗时 23.07 秒（18 次总共耗时 415 秒）。
注意：Query_time 包含的过程：解析 SQL（parse） + 优化查询（优化器阶段） + 查询执行（包括读取磁盘/内存） + 等待锁（InnoDB 可能等待行锁） + 返回结果给客户端（包括网络传输时间）。
Lock=0.00s (0s)：平均锁等待时间为 0 秒，说明没有锁竞争（总锁等待时间 0 秒）
Rows=3596.0 (64728)：平均每次返回 3596 行（总共返回 64728 行）

线上建议关闭慢查询日志。为了避免日志过大，请实现 “慢查询日志文件轮转”。手动清理步骤：

# 1. 查看当前慢查询日志相关的选项配置，包括 开启状态、慢查询日志文件名称
mysql -u root -p123456789 -S /tmp/mysql_3306.sock -e "SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';"
# 3. 临时关闭慢查询日志
mysql -u root -p123456789 -S /tmp/mysql_3306.sock -e "SET GLOBAL slow_query_log = 'OFF';"
# 4. 移动旧日志文件为备份
mv /opt/local/mysql/3306/data/localhost-slow.log /opt/local/mysql/3306/data/localhost-slow.log.20250725
# 5. 重新开启慢查询日志（会重新创建文件）
mysql -u root -p123456789 -S /tmp/mysql_3306.sock -e "SET GLOBAL slow_query_log = 'ON';"

找到慢SQL之性能分析

官网：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html

以下示例演示了如何使用 performance_schema 的 statement events 和 stage events 来检索与 show profiles 和 show profile 语句提供的性能分析信息相当的数据。

setup_actors 表可用于按主机、用户或账户限制历史事件的收集，从而减少运行时开销并降低历史表中收集的数据量。示例的第一步演示了如何将历史事件的收集范围限制为特定用户。

性能模式以皮秒（1 秒 = 1,000,000,000,000 皮秒（ps））为单位显示事件计时器信息，以将计时数据标准化为标准单位。在下面的示例中，TIMER_WAIT 值除以 1000000000000，以秒为单位显示数据。值还被截断为小数点后 6 位，以与 SHOW PROFILES 和 SHOW PROFILE 语句显示的数据格式一致。

1、将历史事件的收集范围限制为执行查询的用户。

默认情况下，setup_actors 配置允许对所有前台线程（由客户端连接触发的线程）进行监控和历史事件收集：

mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+

将 setup_actors 表中的默认行，更新为禁用所有前台线程的历史事件收集和监控，并插入一行以启用对运行该查询的用户的监控和历史事件收集：

1
2

UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';
INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('%','root','%','YES','YES');

此时 setup_actors 表数据如下：

mysql> SELECT * FROM performance_schema.setup_actors;
+-----------+-----------+------+---------+---------+
| HOST      | USER      | ROLE | ENABLED | HISTORY |
+-----------+-----------+------+---------+---------+
| %         | %         | %    | NO      | NO      |
| %         | root      | %    | YES     | YES     |
+-----------+-----------+------+---------+---------+

2、确保通过更新 setup_instruments 表来启用 statement 和 stage 监控。某些监控项可能已默认启用。

setup_instruments 表列出了可收集事件的仪器化对象的类别（要监控的事件，又叫监控点）。
1
2
3
NAME    字段：事件名称（监控点）
ENABLED 字段：是否启用这个监控点？默认部分开启
TIMED   字段：是否记录时间？（比如开始/结束时间、耗时）
每类监控点的作用详解

类别说明

statement/sql/% SQL语句级别监控（最重要）

stage/sql/% 阶段级别（用于分析SQL慢在哪一步：解析？优化？执行？）

wait/io/file/sql/% 文件IO，排查 SQL 慢是否因为磁盘

wait/synch/%/innodb/% 分析 InnoDB 内部是否锁冲突或资源等待（排查表级锁、行锁、buffer pool等）

wait/io/socket/sql/% 是否存在网络传输瓶颈，检查 SQL 是否卡在网络层（客户端慢、返回慢）

1
2
3

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%wait/%';

3、确保已启用 events_statements_* 和 events_stages_* 消费者。部分消费者可能已默认启用。

setup_consumers 表列出了可以存储事件信息且已启用的消费者类型（定义监控数据写到哪里，也就是说，数据收集后写入哪些表）。

1
2
3

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_wait_%';

4、在您正在监控的用户账户下，运行您想要分析的语句。例如：

1	SELECT id, account, name FROM basedb.t_base_user WHERE name = '曹操';

5、通过查询 events_statements_history_long 表来确定语句的 EVENT_ID。此步骤与运行 SHOW PROFILES 命令以确定 Query_ID 的操作类似。以下查询生成的输出与 SHOW PROFILES 命令的结果相似：

events_statements_history_long 表包含全局范围内所有线程中最近结束的 N 个语句事件。语句事件在结束之前不会被添加到该表中。当表已满时，添加新行时会丢弃最旧的一行，无论该行是由哪个线程生成的。

events_statements_history 表包含每个线程最近结束的 N 个语句事件。语句事件在结束前不会被添加到该表中。当表中某线程的行数达到最大值时，添加该线程的新行时会丢弃最旧的行。当线程结束时，其所有行都会被丢弃。

show variables like 'perf%';
SELECT
    THREAD_ID,
    EVENT_ID,
    EVENT_NAME, -- 对应 setup_instruments 表的 NAME 字段
    TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, -- 皮秒 转为 秒
    SQL_TEXT
  FROM performance_schema.events_statements_history_long
 WHERE SQL_TEXT like '%曹操%' -- 慢查询的SQL文本
   and SQL_TEXT not like '%performance_schema%'; -- 此条件是为了排除当前SQL语句
+----------+----------+--------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT                                               |
+----------+----------+--------------------------------------------------------+
|       18 | 0.028310 | SELECT * FROM basedb.t_base_user WHERE name = '曹操'    |
+----------+----------+--------------------------------------------------------+

6、查询 events_stages_history_long 表以获取语句的 stage 事件。stage 通过事件嵌套与语句相关联。每个 stage 事件记录都包含一个 NESTING_EVENT_ID 列，该列存储父语句的 EVENT_ID。

SELECT
    event_name AS Stage,
    TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration -- 皮秒 转为 秒
  FROM performance_schema.events_stages_history_long
 WHERE NESTING_EVENT_ID=18 and THREAD_ID = 76;
+----------------------------------------+----------+
| Stage                                  | Duration |
+----------------------------------------+----------+
| stage/sql/starting                     | 0        |
| stage/sql/checking permissions         | 0        |
| stage/sql/Opening tables               | 0        |
| stage/sql/init                         | 0        |
| stage/sql/System lock                  | 0        |
| stage/sql/optimizing                   | 0        |
| stage/sql/statistics                   | 0        |
| stage/sql/preparing                    | 0        |
| stage/sql/executing                    | 0.0003   |
| stage/sql/end                          | 0        |
| stage/sql/query end                    | 0        |
| stage/sql/closing tables               | 0        |
| stage/sql/freeing items                | 0        |
| stage/sql/logging slow query           | 0        |
| stage/sql/cleaning up                  | 0        |
| stage/sql/starting                     | 0        |
| stage/sql/query end                    | 0        |
| stage/sql/closing tables               | 0        |
| stage/sql/freeing items                | 0        |
| stage/sql/cleaning up                  | 0        |
| stage/sql/converting HEAP to ondisk    | 0.0496   |
| stage/sql/converting HEAP to ondisk    | 0.0525   |
| stage/sql/converting HEAP to ondisk    | 0.1795   |
| stage/sql/converting HEAP to ondisk    | 0.0486   |
| stage/sql/converting HEAP to ondisk    | 0.052    |
| stage/sql/converting HEAP to ondisk    | 0.0457   |
| stage/sql/converting HEAP to ondisk    | 1.5681   |
| stage/sql/end                          | 0        |
| stage/sql/query end                    | 0        |
| stage/sql/waiting for handler commit   | 0.015    |
| stage/sql/closing tables               | 0        |
| stage/sql/freeing items                | 0        |
| stage/sql/logging slow query           | 0        |
| stage/sql/cleaning up                  | 0        |
+----------------------------------------+----------+

7、综合

-- 一次性展示一条语句的所有阶段和等待事件
select 
  s.thread_id,
  s.event_id as statement_event_id,
  s.event_name as statement_event_name,
  s.timer_wait as statement_timer_wait,
  -- s.sql_text,
  
  st.thread_id,
  st.event_id as stage_event_id,
  st.event_name as stage_event_name,
  st.timer_wait as stage_timer_wait,
  
  w.thread_id,
  w.event_id as event_wait_id,
  w.event_name as event_wait_name,
  w.timer_wait as event_timer_wait,
  w.object_type,
  w.object_name
from performance_schema.events_statements_history_long s
left join performance_schema.events_stages_history_long st
  on s.thread_id = st.thread_id and st.nesting_event_id = s.event_id
left join performance_schema.events_waits_history_long w
  on st.thread_id = w.thread_id and w.nesting_event_id = st.event_id
where s.event_id = 85 and s.thread_id = 112
order by s.timer_start ,st.timer_start, w.timer_start;

SQL的执行时间&等待时间

SQL执行时间长：根据执行计划优化索引、减少表关联、优化表结构。

SQL等待时间长：调整MySQL系统配置，比如调整缓冲池大小。

根据 Status Var 调优

调优的基本逻辑是：✅ 读状态变量 → 发现瓶颈 → 调整系统变量 → 观察变化

-- 使用mysql命令连接DB
mysql -h 192.168.0.7 -P 3306 -u base -p 123456789;
use ideps;
#或者
mysql -h192.168.0.7 -P3306 -ubase -p123456789 basedb;

show [FULL] processlist; -- 活动的线程和进程（与正在使用的MySQL账户相关的线程），若不加full则只列出前100条。
KILL [CONNECTION | QUERY] processlist_id; -- 终止线程，默认选项 CONNECTION

-- 查看GLOBAL系统变量。
select
  variable_name,
  variable_value / 1024 / 1024 as variable_value -- 单位：MB
from
  performance_schema.global_variables
where
  variable_name in (
    'innodb_buffer_pool_size', -- 关注 缓存池命中率
    'max_heap_table_size',     -- 关注 临时表落盘率
    'tmp_table_size',          -- 关注 临时表落盘率
    'join_buffer_size',
    'sort_buffer_size')        -- 关注 Sort_merge_passes
 order by variable_name;
 
# 调整系统变量。应该先调整SESSION系统变量（推荐使用 mysql 工具连接），再更新到GLOBAL。
SET GLOBAL   innodb_buffer_pool_size  = 1 * 1024 * 1024 * 1024; -- 1GB/GLOBAL
-- SET SESSION  join_buffer_size         = 8 * 1024 * 1024; -- 10MB/Both
-- SET SESSION  sort_buffer_size         = 2 * 1024 * 1024; -- 2MB/Both
SET SESSION  max_heap_table_size      = 512 * 1024 * 1024; -- 512MB/Both
SET SESSION  tmp_table_size           = 512 * 1024 * 1024; -- 512MB/Both

分页查询性能优化

offset 越大越慢，即便使用了索引列。例如：SELECT * FROM user_info ORDER BY id LIMIT 10 OFFSET 100000000;

如果 id 是主键或有索引，这条语句：

✅会使用 id 的索引来排序；
✅从最小的 id 开始，一条一条扫描跳过10000000行；
❌前 100000 条记录仍然要读取索引（甚至回表）再丢弃掉，性能会很差。

尽量减少二级索引间接扫描聚簇索引（或回表）并跳过相应行数的过程。推荐写法（基于索引位置）：

把 ‘limit n offset m’ 改写为 ‘WHERE id > y LIMIT x’ 或者 ‘id BETWEEN n AND m’，也就是使用索引分页。

数据类型

MySQL

https://dev.mysql.com/doc/refman/8.4/en/data-types.html

https://dev.mysql.com/doc/refman/8.4/en/data-type-defaults.html 数据类型默认值

https://dev.mysql.com/doc/refman/8.4/en/storage-requirements.html 数据类型存储要求

PostgreSQL

https://www.postgresql.org/docs/current/datatype.html

Oracle

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlqr/Data-Types.html

数值

PL-SQL#数值类型 - 维基百科

number 类型： number[([precision], [scale])]

precision 表示十进制有效数字的个数，最多不能超过38个有效数字 number(38,0) （实际支持39-40位十进制数字）。

Scale的范围为[-84，127]。Scale为正数时，表示从小数点到最不重要的十进制有效数字的个数（小数点后多余舍弃）；为负数时，其绝对值表示从最不重要的十进制有效数字到小数点的位数（小数点前四舍五入变为0）。

如果指定了精度，没有指定scale，scale默认为0。

如果没有指定精度，precision与scale默认为最大的取值区间（维基百科是这样说的），实际测试如下（Oracle 11g）：

插入数据	查询结果
0.123456789123456789123456789123456789	0.123456789123457
1.123456789123456789123456789123456789	1.12345678912346
12.123456789123456789123456789123456789	12.1234567891235
123456789123456789.123456789123456789123456789123456789	123456789.123457
123456789123456789.123456789123456789123456789123456789	123456789123457000

结论：从左到右，最多保留十进制有效数字15个。其余部分，小数点前四舍五入变为0，小数点后舍弃。

浮点类型：binary_float、binary_double

日期和时间类型

秒、毫秒、微秒、纳秒

date 类型：精度到秒，占用7个字节的存储空间。

timestamp 类型：最高精度可以到ns(纳秒)，小数位数可以指定为0-9，默认为6位。如果精度为0，则用7字节存储，与date类型功能相同；如果精度大于0，则用11字节存储。

timestamp with time zone 类型：timestamp 类型的变种，它包含了时区偏移量的值，保持 client 的 timezone。

timestamp with local time zone 类型：把 client 的 timezone 转换成 DB 的 timezone 对应的时间。

带时区的类型需要 Java 也是用带时区的？

字符串类型

固定长度类型：char/nchar，自动补足空格，最多可以存储2000字节。
可变长度类型：varchar2/nvarchar2，最大字节数都是4000，自动删除首尾的空格.

格式：char(size [byte| char]) 、varchar2(size [byte| char]) 、nvarchar2(size)

例：

char(8 byte) ：默认是byte

varchar2(4000) / varchar2(4000 char) ：可以存入4000个字母，不能存入4000个汉字。如果数据库字符集是GBK，那么varchar2最多存放2000个汉字；如果字符集是UTF-8，那么最多存放1333个汉字了。存放汉字建议使用nvarchar2。

nvarchar2(2000) ：每个字符长度都是2个字节，所以size最大值为4000/2=2000，最多存放2000个汉字或字母，不受数据库字符集的影响。

函数和运算符

MySQL

https://dev.mysql.com/doc/refman/8.4/en/functions.html

select
  CONVERT ( '-1', signed ), -- 有符号
  CONVERT ( '-1', unsigned ), -- 无符号
  
  IF ( dt.operator IS NULL, 0, 1 ) AS isExistData,
  IFNULL ( SUM( t.spart_cnt ), 0 ) AS quantity,
  COALESCE ( NULL, NULL, NULL, '巴顿' ) AS NAME, -- COALESCE：返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错。
  
  CASE '1' 
      WHEN '1' THEN '正常' 
      WHEN '2' THEN '受控' 
      WHEN '3' THEN '禁止'
    ELSE NULL
  END AS sale_state,
  CASE 
    WHEN t.name is null THEN '正常' 
    ELSE '异常'
  END AS name_state,
  
  concat ( t.region_name_cn, '-', t.country_name_en, '-', t.customer_name_en ) AS customer_name_en,
  group_concat ( item.product_cn ORDER BY product_cn DESC SEPARATOR '$' ) AS product_name 
  LEFT ( opp.`month`, 4 ) AS 'year',
  TRUNCATE( ROUND ( T.sum_quantity :: NUMERIC / t2.total_quantity :: NUMERIC, 4 ) * 100, 2 ) quantity_percent,
  CAST ( '2022-01-31 13:28:56' AS TIMESTAMP ),
  DATE_FORMAT ( '2022-01-06 13:56:35', '%Y-%m-%d %H:%i:%S' ),
  DATE_FORMAT ( now(), '%Y-%m-%d %H:%i:%S' ),
  CHARSET ( 'ABC' ),
  CHARSET ( CONVERT( 'ABC' USING gbk ) )
from table_name
where
  <!-- 历史6个月 -->
  <![CDATA[
      t.`month` <= DATE_FORMAT( CURDATE(), '%Y%m' )
  AND t.`month` >= DATE_FORMAT(DATE_SUB( NOW(), INTERVAL 5 MONTH ), '%Y%m')
  ]]>
  <!-- 未来6个月 -->
  <![CDATA[
      t.`MONTH` >= DATE_FORMAT( NOW(), '%Y%m' )
  AND t.`MONTH` <= DATE_FORMAT( DATE_ADD( NOW(), INTERVAL 5 MONTH ), '%Y%m' )
  ]]>
  
  AND binary asd.name like concat( '%', 'petty','%') -- binary区分大小写，mysql默认不区分大小写

PostgreSQL

https://www.postgresql.org/docs/current/functions.html

SELECT
	-- 数学函数
    ABS ( - 17.4 ) AS "绝对值",-- 17.4
    sign( - 8.5 ) AS "参数的符号(-1,0,+1)",-- -1
    SQRT ( 4.0 ) AS "	平方根",-- 2
    cbrt( 27.0 ) AS "立方根",-- 3
    CEIL ( - 42.8 ) AS "向上取整",-- -42
    FLOOR ( - 42.8 ) AS "向下取整",-- -43
    MOD ( 9, 4 ) AS "取余",-- 1
    trunc( 42.438 ) AS "截断小数位",-- 42
    trunc( 42.438, 2 ) AS "截断小数，保留2位小数",-- 42.43
    round( 42.4 ) AS "四舍五入",-- 42
    round( 42.438, 2 ) AS "四舍五入，保留2位小数",-- 42.44
    TRUNC( ROUND( '10' :: NUMERIC / '100' :: NUMERIC, 4 ) * 100, 2 ) AS percent,-- 10.00
    POWER ( 2.0, 3.0 ) AS "2的3次幂",-- 8.0
    log( 2.0, 8.0 ) AS "以2为底8的对数",-- 3.0
    log( 100.0 ) AS "以10为底100的对数",-- 2.0
    EXP ( 1.0 ) AS "自然指数",-- 2.7182818284590452
    LN ( 2.7182818284590452 ) AS "自然对数",-- 1.0
    degrees( 1 ) AS "弧度转角度",-- 57.29577951308232
    radians( 57.29577951308232 ) AS "角度转弧度",-- 1
    pi( ) AS "π常量",-- 3.141592653589793
    random( ) AS "0.0到1.0之间的随机数值"
    
    -- 三角函数列表
    
    
    
    -- 其他
    asd.proj_cn_name ILIKE concat ( '%', #{requestParam.likeCondition,jdbcType=VARCHAR}, '%' ),
    to_number( RIGHT ( tt.mon_asd, 2 ), '99' ) AS name_cn,
    CAST ( offer.pc_id AS VARCHAR ( 50 ) ) AS pc_id,
  from table_name
  where
    <![CDATA[
        asd.mon_asd <= to_char( CURRENT_TIMESTAMP, 'YYYYMM' )
    AND asd.mon_asd >= to_char( CURRENT_TIMESTAMP + '-5 month', 'YYYYMM' )
    ]]>
    
    AND asd.name ilike concat( '%', 'petty','%')

Oracle

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlqr/SQL-Functions.html

SQL 语句

https://dev.mysql.com/doc/refman/8.4/en/sql-statements.html

DDL（数据定义语言）

alter、create、drop、rename、truncate 。

https://dev.mysql.com/doc/refman/8.4/en/sql-data-definition-statements.html

https://www.postgresql.org/docs/current/ddl.html

DML（数据操纵语言）

https://dev.mysql.com/doc/refman/8.4/en/sql-data-manipulation-statements.html

https://www.postgresql.org/docs/current/dml.html

事务和锁定语句

https://dev.mysql.com/doc/refman/8.4/en/sql-transactional-statements.html

复制语句（控制主从复制）

https://dev.mysql.com/doc/refman/8.4/en/sql-replication-statements.html

可以通过 SQL 接口使用本节中描述的语句来控制复制。语句分为三类：一类用于控制主服务器，一类用于控制从服务器，另一类用于控制组复制。

预编译语句

https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html

MySQL 8.4 支持服务器端预编译语句。此支持利用了高效的客户端/服务器二进制协议。使用带参数占位符的预编译语句具有以下优势：

每次执行语句时减少解析开销。通常，数据库应用程序处理大量几乎相同的语句，仅在 WHERE 子句（用于查询和删除）、SET 子句（用于更新）以及 VALUES 子句（用于插入）等条款中对字面量或变量值进行修改。
防范 SQL 注入攻击。参数值可以包含未转义的 SQL 引号和分隔符字符。

复合语句语法

https://dev.mysql.com/doc/refman/8.4/en/sql-compound-statements.html

本节描述了复合语句 BEGIN … END 的语法以及可在存储程序主体中使用的其他语句：存储过程和函数、触发器以及事件。这些对象通过存储在服务器上的 SQL 代码进行定义，以便后续调用（参见第 27 章，存储对象）。

复合语句是一个块，可以包含其他块；变量、条件处理程序和游标的声明；以及循环和条件测试等流程控制结构。

数据库管理语句

https://dev.mysql.com/doc/refman/8.4/en/sql-server-administration-statements.html

其他行政语句

实用语句

https://dev.mysql.com/doc/refman/8.4/en/getting-information.html 获取数据库和表的相关信息

字符集、排序规则、Unicode

https://dev.mysql.com/doc/refman/8.4/en/charset.html

字符集和排序规则概述

字符集是一组符号和编码。排序规则是一组用于比较字符集中的字符的规则。让我们通过一个虚构的字符集示例来明确这一区别。

假设我们有一个包含四个字母的字母表：A、B、a、b。我们为每个字母分配一个数字：A = 0，B = 1，a = 2，b = 3。字母A是一个符号，数字0是A的编码，而这四个字母及其编码的组合构成一个字符集。

假设我们想比较两个字符串值A和B。最简单的方法是查看它们的编码：A对应0，B对应1。由于0小于1，我们说A小于B。我们刚才所做的就是对字符集应用了排序规则。排序规则是一组规则（本例中仅有一条规则）：“比较编码”。”我们称这种最简单的排序规则为二进制排序规则。

但如果我们希望小写字母和大写字母视为等价呢？那么我们至少需要两条规则：(1) 将小写字母 a 和 b 视为与大写字母 A 和 B 等价；(2) 然后比较编码。我们称这种排序规则为不区分大小写的排序规则。它比二进制排序规则稍复杂一些。

在现实生活中，大多数字符集包含大量字符：不仅包括A和B，还包括整个字母表，有时甚至包含多个字母表或拥有数千个字符的东方书写系统，以及大量特殊符号和标点符号。同样在现实生活中，大多数排序规则包含大量规则，不仅涉及是否区分大小写，还涉及是否区分音标（音标是指附加在字符上的标记，例如德语中的Ö），以及多字符映射规则（例如德语两种排序规则中的一种规定Ö等于OE）。

MySQL可以为您处理这些问题：

使用多种字符集存储字符串。
使用多种排序规则比较字符串。
在同一服务器、同一数据库甚至同一表中混合使用不同字符集或排序规则的字符串。
在任何级别启用字符集和排序规则的指定。

要有效使用这些功能，您必须了解可用字符集和排序规则的类型、如何更改默认设置，以及它们如何影响字符串运算符和函数的行为。

MySQL 中的字符集和排序规则

https://dev.mysql.com/doc/refman/8.4/en/charset-mysql.html

MySQL 服务器支持多种字符集，查看可用的字符集，可以使用 INFORMATION_SCHEMA.CHARACTER_SETS 表或 SHOW CHARACTER SET 语句。

默认情况下，SHOW CHARACTER SET 语句会显示所有可用的字符集。该语句可选地包含 LIKE 或 WHERE 子句，用于指定要匹配的字符集名称。以下示例展示了部分基于 Unicode 转换格式的 Unicode 字符集：

mysql> SHOW CHARACTER SET LIKE 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
| utf8mb3 | UTF-8 Unicode    | utf8mb3_general_ci |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_0900_ai_ci |      4 |
+---------+------------------+--------------------+--------+

给定的字符集始终至少包含一个排序规则，而大多数字符集包含多个排序规则。要列出字符集的显示排序规则，可以使用 INFORMATION_SCHEMA.COLLATIONS 表或 SHOW COLLATION 语句。

默认情况下，SHOW COLLATION 语句会显示所有可用的排序规则。它支持可选的 LIKE 或 WHERE 子句，用于指定要显示的排序规则名称。例如，要查看默认字符集 utf8mb4 的排序规则，请使用以下语句：

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
| ...                        |         |     |         |          |         |               |
| ...                        |         |     |         |          |         |               |
| ...                        |         |     |         |          |         |               |
+----------------------------+---------+-----+---------+----------+---------+---------------+

指定字符集和排序规则

https://dev.mysql.com/doc/refman/8.4/en/charset-syntax.html

字符集和排序规则的默认设置分为四个层次：服务器、数据库、表和列。以下各节的描述可能显得复杂，但在实际应用中发现，多层次默认设置会产生自然且直观的结果。

CHARACTER SET 用于指定字符集的子句中。CHARSET 可作为 CHARACTER SET 的同义词使用。

字符集问题不仅影响数据存储，还影响客户端程序与 MySQL 服务器之间的通信。如果您希望客户端程序使用与默认不同的字符集与服务器通信，则需要指定具体字符集。例如，要使用 latin1 Unicode 字符集，请在连接到服务器后执行以下语句：

1	SET NAMES 'latin1';

排序规则命名约定

https://dev.mysql.com/doc/refman/8.4/en/charset-collation-names.html

排序后缀含义

后缀	全称	含义说明
_ai	Accent-insensitive	不区分重音符号（accent）。如 `café = cafe`。
_as	Accent-sensitive	区分重音符号。如 `café ≠ cafe`。
_ci	Case-insensitive	不区分大小写。如 `ABC = abc`。
_cs	Case-sensitive	区分大小写。如 `ABC ≠ abc`。
_ks	Kana-sensitive	区分日文片假名与平假名。如 `カ ≠ か`。
_bin	Binary	使用原始二进制编码进行比较，完全区分大小写、重音、编码等，是最精确但最严格的比较方式。

binary 会强制将字符串按字节级（binary）比较，从而实现大小写敏感的匹配，但会使索引失效：select binary 'a' = 'A'。

连接字符集和排序规则

https://dev.mysql.com/doc/refman/8.4/en/charset-connection.html

“连接”是指客户端程序在连接到服务器时建立的连接，用于开始一个会话，在此会话中客户端与服务器进行交互。客户端通过会话连接发送SQL语句，例如查询语句。服务器通过连接将响应（如结果集或错误消息）发送回客户端。

当客户端连接到服务器时，它会指定希望用于与服务器通信的字符集。（实际上，客户端指定的是该字符集的默认排序规则，服务器可据此推断出字符集。）服务器使用此信息将字符集系统变量（character_set_client、character_set_results、character_set_connection）设置为该字符集，并将排序规则系统变量（collation_connection）设置为该字符集的默认排序规则。实际上，服务器执行了相当于 SET NAMES 操作的操作。

用于连接字符集配置的SQL语句

连接建立后，客户端可以修改当前会话的字符集和排序规则系统变量。这些变量可以使用 SET 语句单独修改，用于连接字符集配置的SQL语句有两个，都可以批量修改与连接相关的字符集系统变量：

1、SET NAMES 'charset_name' [COLLATE 'collation_name']，此语句等同于以下三个语句：

1
2
3

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

2、SET CHARACTER SET 'charset_name'，此语句等同于以下三个语句：

1
2
3

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

Unicode 支持

https://dev.mysql.com/doc/refman/8.4/en/charset-unicode.html

utf8mb4：一种使用每个字符占用1至4个字节的UTF-8编码方案，用于表示Unicode字符集。

InnoDB存储引擎

https://dev.mysql.com/doc/refman/8.4/en/innodb-storage-engine.html

MVCC（多版本并发控制）

https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html InnoDB 多版本控制

https://www.postgresql.org/docs/current/mvcc.html

https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/data-concurrency-and-consistency.html

InnoDB 架构

https://dev.mysql.com/doc/refman/8.4/en/innodb-architecture.html

InnoDB 架构图，展示内存中和磁盘上的结构。

内存中结构包括：

缓冲池
更改缓冲区
自适应哈希索引
日志缓冲区

磁盘上结构包括：

表空间（系统表空间又称共享表空间、每个表一个文件表空间又称独立表空间、通用表空间、撤销表空间、临时表空间）。
一张 InnoDB 表数据量很小，但表空间占用却很大的原因：
- 数据曾经很多，后来删除了，但空间未回收。InnoDB 不会自动将 .ibd 文件缩小；删除数据只是标记为可复用空间，文件实际大小不变；所以：表小了，文件还很大。
- 频繁插入/删除/更新导致页碎片严重，空间利用率低。
- 存在大字段（如 TEXT/BLOB）页外存储。大字段虽然记录数少，但每个字段会单独占页（或页链），导致表空间变大；
表空间优化 OPTIMIZE TABLE your_table; 。其他注意事项见：https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html
双写缓冲区文件
重做日志(Redo)
撤销日志(Undo)

InnoDB 内存结构

https://dev.mysql.com/doc/refman/8.4/en/innodb-in-memory-structures.html

InnoDB 磁盘结构

https://dev.mysql.com/doc/refman/8.4/en/innodb-on-disk-structures.html

InnoDB 限制

https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

本节描述了InnoDB表、索引、表空间以及InnoDB存储引擎其他方面的限制。

一个表最多可以包含 1017 个列。虚拟生成的列也包含在此限制内。
一个表最多可以包含 64 个二级索引。
对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，索引键前缀长度限制为 3072 字节。3072 / 4 = 768 字符，所以联合索引的多个字段长度加起来不能超过 768。
1
2
3
4
CREATE TABLE `t` (
`name` varchar(1000) DEFAULT NULL,
KEY `idx_name` (`name`(768)) -- 前缀索引，只索引前 768 个字符
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
对于使用REDUNDANT或COMPACT行格式的InnoDB表，索引键前缀长度限制为 767 字节。例如，如果在UTF-8字符集下，对 TEXT 或 VARCHAR 列使用超过191个字符的列前缀索引，且每个字符最多占用4字节，则可能触发此限制。

尝试使用超过限制的索引键前缀长度将引发错误。

如果您在创建 MySQL 实例时通过指定 innodb_page_size 选项将 InnoDB 页面大小设置为 8KB 或 4KB，则索引键的最大长度将成比例降低，基于 16KB 页面大小时 3072 字节的限制。即，当页面大小为8KB时，索引键的最大长度为1536字节；当页面大小为4KB时，最大长度为768字节。

适用于索引键前缀的限制同样适用于全列索引键（索引整个字段的值，不限制前缀）。
多列索引的列数最多允许16列。超过限制将返回错误。
不包括任何存储在页面外的可变长度列，最大行大小略小于页面大小的一半，适用于4KB、8KB、16KB和32KB的页面大小。例如，默认innodb_page_size为16KB时，最大行大小约为8000字节。然而，对于InnoDB页面大小为64KB的情况，最大行大小约为16000字节。LONGBLOB和LONGTEXT列的大小必须小于4GB，且包括BLOB和TEXT列在内的总行大小也必须小于4GB。

如果一行长度小于半页，则整个行都存储在页面内。如果超过半页，则选择可变长度列进行外部页面外存储，直到行长度小于半页，如第 17.11.2 节“文件空间管理”中所述。
尽管InnoDB内部支持大于65,535字节的行大小，但MySQL本身对所有列的总大小设定了65,535字节的行大小限制。请参阅第10.4.7节“表列数和行大小的限制”。
最大表或表空间大小受服务器文件系统影响，该文件系统可能设置的最大文件大小小于InnoDB定义的内部64 TiB大小限制。例如，Linux上的ext4文件系统最大文件大小为16 TiB，因此最大表或表空间大小变为16 TiB而非64 TiB。另一个例子是FAT32文件系统，其最大文件大小为4 GB。

如果您需要更大的系统表空间，请使用多个较小的数据文件而非一个大型数据文件进行配置，或将表数据分布到按表分配的文件和通用表空间数据文件中。
InnoDB 日志文件的总最大大小为 512GB。
表空间的最小大小略大于10MB。表空间的最大大小（即表的最大大小）取决于InnoDB的页面大小。

InnoDB Page Size Maximum Tablespace Size

4KB 16TB

8KB 32TB

16KB 64TB

32KB 128TB

64KB 256TB
一个InnoDB实例最多支持2^32（4294967296）个表空间，其中一小部分表空间被保留用于回滚和临时表。

启用 innodb_file_per_table=ON 时，每张 InnoDB 表 会单独拥有一个表空间（对应 .ibd 文件），所以此时：表空间 ≈ 表的个数 。
共享表空间支持最多 2^32（4294967296）个表。
表空间文件的路径（包括文件名）在 Windows 系统中不能超过 MAX_PATH 限制。在 Windows 10 之前，MAX_PATH 限制为 260 个字符。自 Windows 10 版本 1607 起，MAX_PATH 限制已从常见的 Win32 文件和目录函数中移除，但您需要启用此新行为。

替代存储引擎

https://dev.mysql.com/doc/refman/8.4/en/storage-engines.html

information_schema

https://dev.mysql.com/doc/refman/8.4/en/information-schema.html

INFORMATION_SCHEMA 提供对数据库元数据的访问，包括 MySQL 服务器相关信息，例如数据库或表的名称、列的数据类型或访问权限。此类信息有时也被称为数据字典或系统目录。

performance_schema

https://dev.mysql.com/doc/refman/8.4/en/performance-schema.html

sys Schema

https://dev.mysql.com/doc/refman/8.4/en/sys-schema.html

mysql System Schema（系统数据库）

https://dev.mysql.com/doc/refman/8.4/en/system-schema.html

类别	说明
`statement/sql/%`	SQL语句级别监控（最重要）
`stage/sql/%`	阶段级别（用于分析SQL慢在哪一步：解析？优化？执行？）
`wait/io/file/sql/%`	文件IO，排查 SQL 慢是否因为磁盘
`wait/synch/%/innodb/%`	分析 InnoDB 内部是否锁冲突或资源等待（排查表级锁、行锁、buffer pool等）
`wait/io/socket/sql/%`	是否存在网络传输瓶颈，检查 SQL 是否卡在网络层（客户端慢、返回慢）