MySQL常用技巧

MySQL查看锁情况

查看锁表情况

show status like ‘Table%’;

查看正在被锁定的的表

show OPEN TABLES where In_use > 0;

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查看当前执行的SQL

show processlist

批量修改数据表字符集

先执行下面的 SQL ，将查询结果导出；调整为单条 SQL，然后批量执行。

SELECT
   CONCAT( 'ALTER TABLE ',TABLE_NAME,' CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_general_ci;') 
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME;

数据库的备份与恢复

全库备份

# 1. 基本全库备份（不含系统库）
mysqldump -u[用户名] -p[密码] --single-transaction --master-data=2 --routines --triggers --events --all-databases > full_backup_$(date +%Y%m%d).sql

# 2. 压缩备份（推荐）
mysqldump -u[用户名] -p[密码] --single-transaction --master-data=2 --routines --triggers --events --all-databases | gzip > full_backup_$(date +%Y%m%d).sql.gz

# 参数详解：
# --single-transaction: 对 InnoDB 启用一致性快照备份，保证数据一致性，避免锁表。
# --master-data=2: 以注释形式记录备份时刻的二进制日志位置，用于搭建主从或 PITR（时点恢复）。
# --routines: 备份存储过程和函数。
# --triggers: 备份触发器。
# --events: 备份事件调度器。
# --all-databases: 备份所有库（排除系统库如 information_schema）。

指定表备份

# 备份单个库
mysqldump -u[用户名] -p[密码] --single-transaction --master-data=2 [数据库名] > db_backup.sql

# 备份单个表
mysqldump -u[用户名] -p[密码] --single-transaction [数据库名] [表名] > table_backup.sql

# 只备份结构（无数据）
mysqldump -u[用户名] -p[密码] --no-data [数据库名] > schema_only.sql

# 只备份数据（无结构）
mysqldump -u[用户名] -p[密码] --no-create-info [数据库名] > data_only.sql
```

备份恢复

# 1. 解压（如果备份被压缩）
gzip -d full_backup_20231027.sql.gz

# 2. 恢复到新实例/空库
mysql -u[用户名] -p[密码] < full_backup_20231027.sql

# 3. 恢复单库（从全库备份中提取）
# 方法A：导入时指定库名（需确保该库在目标实例存在）
sed -n '/^-- Current Database: `your_db`/,/^-- Current Database: `/p' full_backup.sql > db_extract.sql
mysql -u[用户名] -p[密码] your_db < db_extract.sql

# 方法B：使用第三方工具 mysql-utilities 中的 `mysqlbinlog`（不推荐手工操作，易出错）

# 4. 恢复单表（从库备份中提取）
# 先恢复表结构，再恢复数据（需要操作备份文件，比较繁琐）
```

bin log 按时间节点恢复

# 1. 恢复一个全量备份（逻辑或物理）
# 假设使用 mysqldump 备份，并记录了 binlog 位置 (MASTER_LOG_POS)

# 2. 从二进制日志中提取从备份点到误操作前的 SQL
# 查看备份文件头部，找到 binlog 位置
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

# 3. 解析 binlog，生成恢复 SQL
mysqlbinlog --start-position=154 --stop-datetime="2023-10-27 14:30:00" /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 > recovery.sql

# 4. 应用恢复 SQL（在恢复的备份实例上）
mysql -u[用户名] -p[密码] < recovery.sql

MySQL 索引设计与优化详解

索引的本质

索引是排好序的数据结构，用于快速查找数据。MySQL InnoDB默认使用B+Tree索引。

B+Tree核心特性

[图示：B+Tree结构]
根节点 → 分支节点 → 叶子节点（双向链表）
特点：
1. 所有数据存储在叶子节点
2. 叶子节点形成有序双向链表
3. 非叶子节点只存索引键（导航作用）
4. 树高度通常3-4层（千万级数据）

索引类型对比

索引类型	数据结构	适用场景	限制
主键索引	B+Tree	唯一标识，表必须有	自动创建，不可重复
唯一索引	B+Tree	确保列值唯一	允许NULL值（可多个）
普通索引	B+Tree	加速查询	无唯一性约束
全文索引	倒排索引	文本内容搜索	仅MyISAM/InnoDB（5.6+）
组合索引	B+Tree	多列联合查询	遵循最左前缀原则

EXPLAIN执行计划深度解析

EXPLAIN基础用法

-- 基本用法
EXPLAIN SELECT * FROM users WHERE age > 25;

-- 详细分析（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;

-- 实际执行（MySQL 8.0+，会真正执行查询！）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

EXPLAIN输出字段详解

1. id（执行顺序标识）

相同id：执行顺序从上到下
不同id：id值越大优先级越高，越先执行
NULL：表示结果集，如UNION结果

-- 示例：复杂查询的执行顺序
EXPLAIN
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id IN (
    SELECT user_id FROM logs WHERE action = 'login'
);
-- 执行顺序：子查询(id=2) → users(id=1) → orders(id=1)

2. select_type（查询类型）

类型	说明	示例场景
SIMPLE	简单查询，不含子查询或UNION	`SELECT * FROM users`
PRIMARY	最外层查询，或UNION中的第一个查询	主查询部分
SUBQUERY	子查询（SELECT列表或WHERE中）	`SELECT id, (SELECT name FROM...)`
DERIVED	派生表（FROM子句中的子查询）	`SELECT * FROM (SELECT ...) t`
UNION	UNION中的第二个及以后的查询	`SELECT ... UNION SELECT ...`
UNION RESULT	UNION的结果集	UNION查询的最后一行

3. table（访问的表）

表名或别名
<derivedN>：派生表，N是id值
<unionM,N>：UNION结果，M、N是参与UNION的id

4. partitions（分区信息）

对于分区表，显示命中的分区

5. type（访问类型） - 关键指标！

性能从优到劣排序：

-- 性能金字塔（从上到下递减）
system > const > eq_ref > ref > range > index > ALL

详细解释：

system：表中只有一行记录

-- 示例：从只有一行的系统表查询

const：通过主键或唯一索引的等值查询

EXPLAIN SELECT * FROM users WHERE id = 1;
-- 使用PRIMARY索引，type: const

eq_ref：联表查询时，使用主键或唯一索引关联

EXPLAIN SELECT * FROM users u 
JOIN user_info i ON u.id = i.user_id  -- i.user_id是唯一索引
WHERE u.age > 20;

ref：使用普通索引的等值查询

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- email有普通索引，type: ref

range：使用索引进行范围查询

EXPLAIN SELECT * FROM users 
WHERE age BETWEEN 20 AND 30 
   OR created_at > '2023-01-01';
-- 使用索引进行范围扫描

index：全索引扫描

EXPLAIN SELECT COUNT(*) FROM users;  -- 使用主键索引全扫描
EXPLAIN SELECT id FROM users;        -- 覆盖索引扫描

ALL：全表扫描（需要优化！）

EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';
-- 前模糊查询导致索引失效

6. possible_keys（可能使用的索引）

查询可能用到的索引
为空表示没有可用索引

7. key（实际使用的索引）

实际选择的索引
为NULL表示全表扫描

8. key_len（使用的索引长度）

计算规则（单位：字节）：

数值类型：tinyint(1)、smallint(2)、int(4)、bigint(8)
时间类型：date(3)、timestamp(4)、datetime(8)
字符串：字符集×长度
- utf8mb4: 每个字符4字节
- utf8: 每个字符3字节
- latin1: 每个字符1字节

-- 示例：计算key_len
CREATE TABLE test (
    id INT,                 -- 4字节
    name VARCHAR(50),       -- utf8mb4: 50*4 + 2(变长字段)=202
    age TINYINT,            -- 1字节
    INDEX idx_name_age (name, age)
);

EXPLAIN SELECT * FROM test WHERE name = '张三';
-- key_len: 202 (只用到了name索引部分)

EXPLAIN SELECT * FROM test WHERE name = '张三' AND age = 25;
-- key_len: 203 (name 202 + age 1)

9. ref（索引的等值匹配值）

显示索引查找所用到的列或常量
const：常量值
库名.表名.列名：关联查询的列

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;
-- o表的ref显示：test.u.id

10. rows（预估扫描行数）

重要优化指标！越少越好
基于统计信息估算，可能不准确
使用 ANALYZE TABLE 更新统计信息

11. filtered（过滤百分比）

存储引擎层过滤后，剩余行的百分比
理想情况：100%

12. Extra（额外信息）

常见重要值：

Using index（覆盖索引）

EXPLAIN SELECT id, name FROM users WHERE age > 20;
-- 如果 (age, id, name) 有联合索引，则出现 Using index

优点：无需回表，性能最佳

Using where

EXPLAIN SELECT * FROM users WHERE age > 20 AND name LIKE '张%';
-- 索引过滤age，但name条件需要在server层过滤

Using index condition（索引条件下推，ICP）

-- MySQL 5.6+ 特性
EXPLAIN SELECT * FROM users 
WHERE name LIKE '张%' AND age > 20;
-- 将age条件推送到存储引擎层过滤

Using temporary（使用临时表） - 需要优化！

EXPLAIN SELECT DISTINCT name FROM users;
EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;
-- 可能需要临时表进行去重或分组

Using filesort（文件排序） - 需要优化！

EXPLAIN SELECT * FROM users ORDER BY name;
-- 如果name无索引，需要额外排序

Using join buffer

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;
-- 关联查询时使用连接缓冲区

索引设计最佳实践

索引设计原则

原则1：最左前缀匹配

-- 创建组合索引
CREATE INDEX idx_city_age ON users(city, age, gender);

-- 能使用索引的查询
SELECT * FROM users WHERE city = '北京';                    -- √ 使用索引
SELECT * FROM users WHERE city = '北京' AND age > 20;       -- √ 使用索引
SELECT * FROM users WHERE city = '北京' AND age > 20 AND gender = 'M'; -- √

-- 不能完全使用索引的查询
SELECT * FROM users WHERE age > 20;                         -- ✗ 不满足最左前缀
SELECT * FROM users WHERE city = '北京' AND gender = 'M';   -- ✗ 跳过了age

原则2：覆盖索引优化

-- 回表查询（需要优化）
EXPLAIN SELECT * FROM users WHERE city = '北京';
-- type: ref, Extra: NULL (需要回表查数据)

-- 覆盖索引（最优）
EXPLAIN SELECT city, age, id FROM users WHERE city = '北京';
-- type: ref, Extra: Using index (无需回表)

-- 创建覆盖索引
CREATE INDEX idx_city_age_covering ON users(city, age, id);
-- 或利用InnoDB聚簇索引特性

原则3：索引列独立

-- 错误：索引列参与计算
SELECT * FROM users WHERE YEAR(created_at) = 2023;          -- ✗
SELECT * FROM users WHERE age + 1 > 20;                     -- ✗

-- 正确：保持列独立
SELECT * FROM users WHERE created_at >= '2023-01-01' 
                    AND created_at < '2024-01-01';          -- √
SELECT * FROM users WHERE age > 19;                         -- √

-- 错误：使用函数
SELECT * FROM users WHERE LEFT(name, 1) = '张';             -- ✗

-- 正确（MySQL 8.0+支持函数索引）
ALTER TABLE users ADD INDEX idx_name_first ((LEFT(name, 1))); -- √

索引选择策略

选择区分度高的列

-- 计算区分度（越接近1越好）
SELECT 
    COUNT(DISTINCT gender) / COUNT(*) as gender_distinct,
    COUNT(DISTINCT city) / COUNT(*) as city_distinct,
    COUNT(DISTINCT age) / COUNT(*) as age_distinct
FROM users;

-- 结果示例：
-- gender_distinct: 0.001 (差，只有男/女)
-- city_distinct: 0.3     (中等)
-- age_distinct: 0.05     (较差)
-- 应该优先选择city建立索引

索引代价模型

-- 索引成本 = 索引查找成本 + 回表成本
-- 优化器会根据成本选择索引

-- 强制使用索引（通常不推荐）
SELECT * FROM users FORCE INDEX(idx_city) WHERE city = '北京';

-- 查看索引统计信息
SHOW INDEX FROM users;
-- Cardinality(基数)：估算的不同值数量，影响优化器选择

常见索引失效场景

-- 1. LIKE 前模糊查询
SELECT * FROM users WHERE name LIKE '%张%';      -- ✗ 索引失效
SELECT * FROM users WHERE name LIKE '张%';       -- √ 使用索引

-- 2. OR 条件非所有列有索引
SELECT * FROM users WHERE city = '北京' OR age > 20;
-- 如果只有city有索引，age无索引，则全表扫描

-- 3. 类型转换
SELECT * FROM users WHERE phone = 13800138000;   -- ✗ phone是varchar类型
SELECT * FROM users WHERE phone = '13800138000'; -- √

-- 4. NOT、!=、<> 查询
SELECT * FROM users WHERE city != '北京';        -- ✗ 通常全表扫描

-- 5. 范围查询后的列索引失效
-- 索引：idx_city_age_gender
SELECT * FROM users WHERE city = '北京' AND age > 20 AND gender = 'M';
-- gender列索引失效（范围查询age后面的列）

索引优化实战案例

案例1：分页查询优化

-- 问题：深度分页慢
SELECT * FROM users ORDER BY id LIMIT 1000000, 20;
-- 需要扫描1000020行

-- 优化方案1：记录上次位置
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 优化方案2：延迟关联
SELECT * FROM users u
JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 20
) AS tmp ON u.id = tmp.id;

案例2：联合索引顺序优化

-- 查询需求：
-- 1. WHERE status = 1 AND type = 'A'
-- 2. WHERE status = 1 ORDER BY created_at DESC
-- 3. WHERE type = 'A' AND created_at > '2023-01-01'

-- 错误索引：INDEX(status, type, created_at)
-- 对查询2只能用到status，created_at需要filesort

-- 正确索引：INDEX(status, created_at, type)
-- 查询1：使用status，type在索引中
-- 查询2：使用status，created_at排序
-- 查询3：使用type，created_at范围查询

案例3：重复索引检测

-- 查找可能重复的索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
    COUNT(*) as col_count
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME, columns
HAVING COUNT(*) > 1;

-- 常见冗余：
-- INDEX(a) 和 INDEX(a,b)  -- (a)是冗余的
-- INDEX(a,b) 和 INDEX(b,a) -- 顺序不同，功能不同

高级索引技术

函数索引（MySQL 8.0+）

-- 创建函数索引
CREATE TABLE logs (
    id INT PRIMARY KEY,
    content TEXT,
    created_at DATETIME
);

-- 为日期部分创建索引
CREATE INDEX idx_created_date 
ON logs((DATE(created_at)));

-- 查询使用函数索引
EXPLAIN SELECT * FROM logs 
WHERE DATE(created_at) = '2023-10-27';
-- type: ref, key: idx_created_date

降序索引（MySQL 8.0+）

-- 支持降序排序优化
CREATE INDEX idx_age_desc ON users(age DESC);

-- 混合排序
CREATE INDEX idx_city_asc_age_desc ON users(city ASC, age DESC);

SELECT * FROM users 
WHERE city = '北京' 
ORDER BY city ASC, age DESC;  -- √ 使用索引避免filesort

不可见索引

-- 创建不可见索引（优化器忽略）
CREATE INDEX idx_test ON users(email) INVISIBLE;

-- 切换可见性
ALTER TABLE users ALTER INDEX idx_test VISIBLE;
ALTER TABLE users ALTER INDEX idx_test INVISIBLE;

-- 用途：删除索引前的安全测试

索引监控与维护

索引使用情况监控

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ DESC;

-- 查找无用索引（从未被使用）
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL 
  AND COUNT_READ = 0 
  AND COUNT_FETCH = 0;

索引碎片整理

-- 查看索引碎片率
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragment_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND DATA_FREE > 1024 * 1024 * 10  -- 碎片大于10MB
ORDER BY fragment_pct DESC;

-- 重建索引（InnoDB）
ALTER TABLE users ENGINE=InnoDB;  -- 重建表，所有索引
OPTIMIZE TABLE users;             -- 同义词，重建表

-- 重建单个索引
ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name(name);

索引创建最佳实践

-- 1. 在线创建索引（MySQL 5.6+）
CREATE INDEX idx_email ON users(email) ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 分阶段创建大表索引
-- 步骤1：创建不含数据的索引结构
CREATE INDEX idx_large ON big_table(column) ALGORITHM=INPLACE, LOCK=SHARED;

-- 步骤2：分批更新统计信息
ANALYZE TABLE big_table PERSISTENT FOR ALL;

-- 3. 监控索引创建进度（MySQL 8.0+）
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%stage/innodb/alter%';

✅ 索引设计检查清单

[ ] 每张表必须有主键（建议自增INT/BIGINT）
[ ] 频繁查询的WHERE条件列建立索引
[ ] 排序、分组、去重的列建立索引
[ ] 遵循最左前缀原则设计联合索引
[ ] 优先使用覆盖索引减少回表
[ ] 索引列保持独立（不参与计算）
[ ] 选择区分度高的列建立索引
[ ] 定期清理无用索引
[ ] 监控索引碎片率（>30%考虑重建）
[ ] 使用EXPLAIN验证索引效果

❌ 索引使用禁忌

[ ] 不在低区分度列建索引（如性别、状态）
[ ] 避免过度索引（每表建议5个以内）
[ ] 不滥用全文索引（有专门搜索引擎）
[ ] 不在频繁更新的列建过多索引
[ ] 不使用SELECT *（破坏覆盖索引）