1
-- 查看最大连接数
2
SHOW VARIABLES LIKE 'max_connections';
3

4
-- 查看当前连接数
5
SHOW STATUS LIKE 'Threads_connected';
6

7
-- 查看连接详情
8
SHOW PROCESSLIST;

1
+----+------+-----------+------+---------+------+----------+------------------+
2
| Id | User | Host      | db   | Command | Time | State    | Info             |
3
+----+------+-----------+------+---------+------+----------+------------------+
4
|  5 | root | localhost | test | Query   |    0 | starting | SHOW PROCESSLIST |
5
|  6 | app  | 10.0.0.1  | prod | Sleep   |  120 |          | NULL             |
6
+----+------+-----------+------+---------+------+----------+------------------+

1
// 应用层连接池配置（以 Node.js 为例）
2
const pool = mysql.createPool({
3
  host: "localhost",
4
  user: "root",
5
  password: "password",
6
  database: "test",
7
  connectionLimit: 10, // 最大连接数
8
  waitForConnections: true, // 等待可用连接
9
  queueLimit: 0, // 等待队列无限制
10
});

1
-- MySQL 权限存储在 mysql 系统库
2
USE mysql;
3
SHOW TABLES LIKE '%priv%';
4

5
-- 用户权限表
6
SELECT Host, User, Select_priv, Insert_priv, Update_priv
7
FROM user WHERE User = 'root';
8

9
-- 数据库权限表
10
SELECT Host, Db, User, Select_priv, Insert_priv
11
FROM db;
12

13
-- 表权限表
14
SELECT Host, Db, User, Table_name, Table_priv
15
FROM tables_priv;

1
-- 查看缓存配置
2
SHOW VARIABLES LIKE 'query_cache%';
3

4
-- 缓存条件
5
-- 1. 查询必须是确定的（不含 NOW()、RAND() 等）
6
-- 2. 不使用用户变量
7
-- 3. 不使用临时表
8
-- 4. 表无写操作

1
SELECT name FROM users WHERE id = 1

1
Token 流：
2
SELECT (关键字)
3
name (标识符)
4
FROM (关键字)
5
users (标识符)
6
WHERE (关键字)
7
id (标识符)
8
= (操作符)
9
1 (数值字面量)

1
-- 原始 SQL
2
SELECT * FROM users WHERE 1 = 1 AND id > 0;
3

4
-- 优化后
5
SELECT * FROM users;
6

7
-- 原始 SQL
8
SELECT * FROM users WHERE id = 1 AND id = 2;
9

10
-- 优化后（条件冲突，返回空结果）
11
Impossible WHERE

1
-- 原始子查询
2
SELECT * FROM orders
3
WHERE user_id IN (SELECT id FROM users WHERE status = 1);
4

5
-- 优化为 JOIN
6
SELECT orders.* FROM orders
7
JOIN users ON orders.user_id = users.id
8
WHERE users.status = 1;

1
-- 查看执行计划
2
EXPLAIN SELECT * FROM users WHERE id = 1;

1
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
3
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
4
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
5
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

1
-- 多表连接
2
SELECT * FROM orders o
3
JOIN users u ON o.user_id = u.id
4
JOIN products p ON o.product_id = p.id
5
WHERE u.status = 1;
6

7
-- 优化器决定连接顺序
8
-- 通常从小表开始驱动

1
-- 查看索引统计
2
SHOW INDEX FROM users;
3

4
-- 分析表更新统计信息
5
ANALYZE TABLE users;

1
-- 索引选择示例
2
-- 假设有索引: idx_name(name), idx_age(age), idx_name_age(name, age)
3

4
-- 查询 1：使用 idx_name_age（最左前缀）
5
SELECT * FROM users WHERE name = 'Alice' AND age = 25;
6

7
-- 查询 2：使用 idx_age（name 不在条件中）
8
SELECT * FROM users WHERE age = 25;
9

10
-- 查询 3：使用 idx_name_age 覆盖索引
11
SELECT name, age FROM users WHERE name = 'Alice';
12

13
-- 查询 4：可能全表扫描（选择度低）
14
SELECT * FROM users WHERE gender = 'M';  -- 假设 50% 数据匹配

1
选择度 = 不重复值数量 / 总行数
2

3
高选择度（接近 1）：适合建索引
4
低选择度（接近 0）：不适合建索引
5

6
示例：
7
- id（主键）：选择度 = 1，适合索引
8
- gender（性别）：选择度 ≈ 0.5，不适合索引
9
- email（邮箱）：选择度 ≈ 1，适合索引

1
// 存储引擎 Handler 接口（简化）
2
class handler {
3
public:
4
    // 打开/关闭表
5
    virtual int open(const char *name, int mode, uint test_if_locked) = 0;
6
    virtual int close(void) = 0;
7

8
    // 扫描操作
9
    virtual int rnd_init(bool scan) = 0;
10
    virtual int rnd_next(uchar *buf) = 0;
11
    virtual int rnd_end() = 0;
12

13
    // 索引操作
14
    virtual int index_init(uint idx, bool sorted) = 0;
15
    virtual int index_read(uchar *buf, const uchar *key,
16
                          uint key_len, enum ha_rkey_function find_flag) = 0;
17
    virtual int index_next(uchar *buf) = 0;
18

19
    // 写入操作
20
    virtual int write_row(uchar *buf) = 0;
21
    virtual int update_row(const uchar *old_data, uchar *new_data) = 0;
22
    virtual int delete_row(const uchar *buf) = 0;
23
};

1
-- 查看 Buffer Pool 配置
2
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
3

4
-- 查看 Buffer Pool 状态
5
SHOW STATUS LIKE 'Innodb_buffer_pool%';

1
Buffer Pool (默认 128MB)
2
├── Instance 0
3
│   ├── Chunk 0
4
│   │   ├── Page 0 (16KB)
5
│   │   ├── Page 1
6
│   │   └── ...
7
│   └── Chunk 1
8
└── Instance 1
9
    └── ...

1
InnoDB 数据页结构（16KB）：
2
┌────────────────────────────────────┐
3
│ File Header (38 bytes)             │ 页面通用信息
4
├────────────────────────────────────┤
5
│ Page Header (56 bytes)             │ 页面状态信息
6
├────────────────────────────────────┤
7
│ Infimum + Supremum (26 bytes)      │ 最小/最大记录
8
├────────────────────────────────────┤
9
│ User Records                       │ 用户数据
10
│ (行格式: Compact/Dynamic/...)      │
11
├────────────────────────────────────┤
12
│ Free Space                         │ 空闲空间
13
├────────────────────────────────────┤
14
│ Page Directory                     │ 页目录（槽位）
15
├────────────────────────────────────┤
16
│ File Trailer (8 bytes)             │ 校验和
17
└────────────────────────────────────┘

1
-- Redo Log 刷盘策略
2
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
3

4
-- 0: 每秒刷盘（可能丢失 1 秒数据）
5
-- 1: 每次提交刷盘（最安全，默认）
6
-- 2: 每次提交写入 OS 缓存，每秒刷盘

1
-- 查看完整执行计划
2
EXPLAIN FORMAT=JSON
3
SELECT * FROM users WHERE name = 'Alice';
4

5
-- 使用优化器跟踪
6
SET optimizer_trace='enabled=on';
7
SELECT * FROM users WHERE name = 'Alice';
8
SELECT * FROM information_schema.OPTIMIZER_TRACE;
9
SET optimizer_trace='enabled=off';

1
-- 开启慢查询日志
2
SET GLOBAL slow_query_log = 'ON';
3
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
4

5
-- 查看慢查询
6
SELECT * FROM mysql.slow_log
7
ORDER BY start_time DESC
8
LIMIT 10;

1
-- 查看索引使用情况
2
SELECT
3
    TABLE_NAME,
4
    INDEX_NAME,
5
    CARDINALITY,
6
    SEQ_IN_INDEX
7
FROM information_schema.STATISTICS
8
WHERE TABLE_SCHEMA = 'your_db'
9
ORDER BY TABLE_NAME, INDEX_NAME;
10

11
-- 分析索引建议
12
-- 使用 pt-index-usage 工具