MySQL 开发随笔

发表于 阅读次数： 评论数： 本文字数： 5.8k 阅读时长 ≈ 5 分钟

MySQL 数据库备份

导出整个数据库

  • 导出某个数据库的表结构和表数据
1
mysqldump -h 127.0.0.1 -u your_username -p --databases shop > shop.sql

提示

  • 当添加了参数 --databases，导出的 SQL 文件会包含 CREATE DATABASE 语句，适用于导入到全新 MySQL 服务器中。
  • 当不添加参数 --databases，导出的 SQL 文件不会包含 CREATE DATABASE 语句，适用于在另一个已存在的数据库中导入。

导出多个数据库

  • 一次性导出多个数据库的表结构和表数据
1
mysqldump -h 127.0.0.1 -u your_username -p --databases db1 db2 > backup.sql

只导出某张表

  • 只导出某个数据库的某张表的表结构和表数据
1
mysqldump -h 127.0.0.1 -u your_username -p shop table1 > shop_selected.sql

只导出某些表

  • 只导出某个数据库的某些表的表结构和表数据
1
mysqldump -h 127.0.0.1 -u your_username -p shop table1 table2 > shop_selected.sql

排除导出某张表

  • 导出某个数据库的表结构和表数据，除了某张表之外
1
mysqldump -h 127.0.0.1 -u your_username -p --databases shop --ignore-table=shop.user > shop_without_user.sql

排除导出多张表

  • 导出某个数据库的表结构和表数据，除了某些表之外
1
2
# (1) 首先，获取 shop 数据库中的所有表
mysql -u your_username -p -e "SHOW TABLES FROM shop;" > tables.txt
1
# (2) 然后，在 `tables.txt` 文件中删除某些表
1
2
# (3) 最后，导出 `tables.txt` 文件中指定的表
mysqldump -h 127.0.0.1 -u your_username -p shop $(cat tables.txt) > shop_filtered.sql

只导出表结构（不包含数据）

  • 只导出某个数据库的表结构，不导出数据
1
mysqldump -h 127.0.0.1 -u your_username -p --no-data shop > shop_schema.sql

只导出表数据（不包含表结构）

  • 只导出某个数据库的表数据（默认会锁住当前正在导出的表），不导出表结构
1
mysqldump -h 127.0.0.1 -u your_username -p --no-create-info shop > shop_data.sql
  • 只导出某个数据库的表数据（默认会锁住当前正在导出的表），不导出表结构，但会包含创建和切换数据库的 SQL 语句（CREATE DATABASE 与 USE）
1
mysqldump -h 127.0.0.1 -u your_username -p --no-create-info --databases shop > shop_data.sql

导出数据时锁表与不锁表

  • 使用以下 SQL 语句只导出某个数据库的表数据时，默认会锁住当前正在导出的表（只能读不能写），其他表可正常读写
1
2
3
4
5
# 默认会锁住当前正在导出的表（只能读不能写），其他表可正常读写
mysqldump -h 127.0.0.1 -u your_username -p --no-create-info shop > shop_data.sql

# 如果需要在导出期间锁住所有表（FLUSH TABLES WITH READ LOCK - 全局读锁），可以使用 --lock-all-tables 参数，所有表都会变成只能读不能写（整个数据库被锁定，数据一致性最强）
mysqldump -h 127.0.0.1 -u your_username -p --lock-all-tables --no-create-info shop > shop_data.sql
  • 对于 InnoDB 表，使用 --single-transaction 可以在不锁表的情况下获得一致性备份（导出期间可以对所有表进行正常读写，但导出的数据不包含新写入的记录，因为导出是基于启动事务时的快照）
1
mysqldump -h 127.0.0.1 -u your_username -p --no-create-info --single-transaction shop > shop_data.sql

–single-transaction 的说明

  • --single-transaction 的核心作用

    • 用于在不锁表的情况下，导出一致性的数据快照
    • 数据导出开始时，会创建一个统一时间点的数据快照，确保导出的数据是某个时间点的完整状态，而不是导出过程中不断变化的数据
    • 普通导出（不加 --single-transaction 参数）会对表加读锁（LOCK TABLES xxx READ），导致数据导出期间无法写入；而 --single-transaction 使用了事务机制，不会锁表，允许数据导出期间执行正常的读写操作

  • --single-transaction 的工作原理

    • 开启一个可重复读（REPEATABLE READ）隔离级别的事务
    • 执行 START TRANSACTION WITH CONSISTENT SNAPSHOT
    • 基于事务快照读取数据，导出过程中的数据变更不会被包含在里面
    • 导出期间其他会话可以对所有表正常执行 INSERT、UPDATE、DELETE 操作

  • --single-transaction 不使用时的导出过程

    • (1) LOCK TABLES users READ（锁表）
    • (2) 读取 users 表的数据
    • (3) UNLOCK TABLES（解锁）
    • (4) 数据导出期间 users 表只能读，不能写
    • 注意：不使用 --single-transaction 参数时，默认会锁住当前正在导出的表（只能读不能写），其他表可正常读写

  • --single-transaction 使用时的导出过程

    • (1) 开启事务，获取一致性快照
    • (2) 基于快照读取数据（不锁表）
    • (3) 其他会话可以对所有表正常执行读写
    • 注意：导出的数据不包含新写入的记录，因为导出是基于启动事务时的快照

  • --single-transaction 的使用注意事项

    • 只对 InnoDB 表有效
      1
      2
      # 如果数据库混合使用 InnoDB 和 MyISAM 存储引擎，建议加上 --lock-tables=false 避免自动锁表
      mysqldump -h 127.0.0.1 -u your_username -p --no-create-info --single-transaction --lock-tables=false shop > shop_data.sql
    • 导出期间避免 DDL 操作
      1
      2
      -- 导出期间执行 DDL 可能导致快照不一致，应避免在导出期间执行 DDL 操作
      ALTER TABLE users ADD COLUMN age INT;
    • 需要有足够的事务日志空间
      • 导出大数据量时，Undo 日志会增长
      • 确保 innodb_undo_tablespaces 有足够空间

  • --single-transaction 的典型使用场景

    • 适合场景：
      • 生产环境在线备份（7x24 小时服务）
      • 需要一致性的逻辑备份
      • InnoDB 为主的数据库
    • 不适用场景：
      • MyISAM 表为主的数据库
      • 需要导出时同时导出 Binlog 位置（需要配合其他参数）
      • 对备份速度要求极高（会有轻微性能开销）
    • 对存储引擎的支持：
      存储引擎是否支持说明
      InnoDB✅ 支持利用 MVCC 机制，无需锁表
      MyISAM❌ 不支持会自动降级为锁表方式

  • --single-transaction 的对性能的影响

    • 优点：
      • 不阻塞业务写入，适合生产环境
    • 缺点：
      • 导出期间 Undo 日志不能清理，可能导致磁盘空间增加
      • 长事务可能会影响数据库性能（特别是有大量变更时）
      • 导出时间过长可能会导致锁等待或死锁

导出表数据的其他常用参数

1
2
# 数据导出命令示例
mysqldump -h 127.0.0.1 -u your_username -p --lock-all-tables --no-create-info --complete-insert --insert-ignore --default-character-set=utf8mb4 --databases shop > shop_data.sql
参数作用说明使用场景注意事项
--single-transaction在导出前开启一个事务（可重复读隔离级别），获取一致性快照，导出期间不锁表。InnoDB 表的首选备份方式，适用于生产环境在线备份，不阻塞读写业务。仅对 InnoDB 引擎有效；会生成一个长事务，需要足够的 undo 空间；导出期间避免 DDL 操作。
--lock-all-tables导出前对所有数据库的所有表加全局读锁（FLUSH TABLES WITH READ LOCK），直到导出完成才释放。适用于 MyISAM 引擎的表备份，或需要保证全局数据完全在同一时间点的场景。会阻塞所有表的写入操作（包括其他数据库），对业务影响大。InnoDB 表推荐使用 --single-transaction 代替。
--databases将参数后面列出的名称当作数据库名来处理。导出时会包含 CREATE DATABASE 和 USE 语句。需要导出完整的、可独立导入的一个或多个数据库时使用。不加此参数时，第一个名称会被当作数据库名，但不包含建库和切换库的语句。
--no-create-info导出时不包含创建表的语句（CREATE TABLE）。只备份表数据，或目标库中表已存在，只需更新数据时。与 --no-data 相反，后者只导出表结构不导出数据。
--complete-insert生成的 INSERT 语句中包含完整的列名，如 INSERT INTO table (col1, col2) VALUES (...);。当目标表的列顺序或数量与原表不一致时（如新增了列），能避免导入出错，提高兼容性。会让导出文件稍大，但更安全、可读性更好。
--insert-ignore生成 INSERT IGNORE 语句，导入时遇到主键或唯一键冲突则跳过该行，继续插入后面的数据。向已有数据的表中增量导入新数据，或导入时允许部分数据重复。不会报错，但需要关注到底跳过了多少行，可能掩盖数据问题。
--replace生成 REPLACE INTO 语句，导入时遇到主键或唯一键冲突则先删除旧行，再插入新行。需要完全覆盖目标表中的现有数据，以导出文件为准的场景（如数据同步、全量刷新）。相当于删除 + 插入，会改变自增 ID，可能触发外键级联删除。需谨慎使用，以免意外丢数据。
--extended-insert默认开启。将多行数据合并成一条 INSERT 语句，例如 INSERT INTO t VALUES (1),(2),(3);。日常备份和数据迁移，可以大幅减小文件体积，提高导入速度。合并的行数由 --net_buffer_length 控制。
--skip-extended-insert关闭 --extended-insert，每行数据生成一条独立的 INSERT 语句。需要对 SQL 文件进行版本管理（如 Git diff），或逐条调试数据时使用。文件会变大，导入速度显著变慢。不推荐用于常规备份恢复。
--default-character-set指定导出时使用的字符集。强烈推荐使用 utf8mb4，特别是处理多语言、特殊符号或 emoji 的场景，避免乱码或数据截断。应与数据库、表的默认字符集保持一致。

MySQL 服务器配置

MySQL 8 设置默认字符集

为了让 MySQL 8 支持存储 Emoji 表情包（占用 4 个字节），可以在 MySQL 服务端的 /etc/my.conf 配置文件中指定默认使用的字符集为 utf8mb4，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[client]
# 设置客户端的默认字符集
default-character-set=utf8mb4

[mysql]
# 设置命令行客户端的默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置每个客户端在连接时默认使用的字符集，如何客户端在连接时明确指定了字符集，则客户端指定的字符集具有较高优先级
init_connect = 'SET NAMES utf8mb4'
# 设置服务端的默认字符集
character-set-server = utf8mb4
# 设置服务端的默认排序规则
collation-server=utf8mb4_unicode_ci

MySQL 设置事务隔离级别

事务隔离级别的介绍

事务表示多个数据操作组成一个完整的事务单元，在这个事务内的所有数据操作要么同时成功，要么同时失败。数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。一个事务与其他事务隔离的程度称为 隔离级别。SQL 标准中规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但数据库的并发性能越差。为了解决事务并发问题（脏读、不可重复读、幻读），主流的关系型数据库都会提供以下四种事务隔离级别。

  • 读未提交（Read Uncommitted）
    • 在该隔离级别，所有事务都可以看到其他未提交事务所做的改变。
    • 该隔离级别是最低的隔离级别，虽然拥有超高的并发处理能力及很低的系统开销，但很少用于实际应用。
    • 该隔离级别只能解决第一类更新丢失问题，不能解决脏读、不可重复读、幻读的问题。
  • 读已提交（Read Committed）
    • 这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的），例如 Oracle 数据库。
    • 该隔离级别满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。
    • 该隔离级别可以解决脏读问题，但会出现不可重复读、幻读问题。
  • 可重复读（Repeatable Read）
    • 这是 MySQL 的默认事务隔离级别，它可以确保在整个事务过程中，对同一条数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管其他事务更新提交与否。
    • 该隔离级别可以解决脏读、不可重复读的问题，但会出现幻读问题。
  • 串行化（Serializable）
    • 这是最高的事务隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决脏读、不可重复读、幻读、第一类更新丢失、第二类更新丢失问题。
    • 该隔离级别可以解决所有的事务并发问题，但可能导致大量的超时现象和锁竞争，通常数据库不会用这个隔离级别，可用其他的方案来解决这些问题，例如乐观锁和悲观锁。

事务隔离级别的案例

上图中是典型的第二类丢失更新问题，后果非常严重。当数据库隔离级别为读已提交（Read Committed）及以下隔离级别时，会出现不可重复读的现象。从上面的表格可以看出，当事务隔离级别设置为可重复读（Repeatable Read）时，可以避免不可重复读的现象出现。

事务隔离级别的设置

1
2
3
4
5
6
7
8
-- 设置下一次事务的隔离级别
set transaction isolation level repeatable read;

-- 设置当前会话的事务隔离级别
set session transaction isolation level repeatable read;

-- 设置全局的事务隔离级别
set global session transaction isolation level repeatable read;

事务隔离级别的总结

上述四种事务隔离级别会产生的并发问题如下（YES 表示存在对应的问题，NO 表示不存在对应的问题）：

各种关系型数据库对事务隔离级别的支持程度如下（YES 表示支持，NO 表示不支持）：

OracleMySQL
Read UncommittedNOYES
Read CommittedYES (默认)YES
Repeatable ReadNOYES (默认)
SerializableYESYES

提示

  • 在 Spring 框架中，事务隔离级别可以通过 @Transactional 注解中的 isolation 属性定义。
  • MySQL 的默认隔离级别是可重复读 (Repeatable Read)，而 Oracle 的默认隔离级别是读已提交 (Read Committed)。

数据库表如何删除重复数据

面试题

线上数据库表存在重复数据，如何删除同一字段下的所有重复数据，或者仅仅留下一条数据。

  • 创建表，并插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `student` (`id`, `name`, `age`) VALUES ('1', 'cat', 22);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('2', 'dog', 33);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('3', 'fish', 44);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('4', 'cat', 55);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('5', 'dog', 11);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('6', 'cat', 45);
  • 查看 name 字段的重复数据
1
2
3
SELECT NAME, COUNT(1) FROM student GROUP BY NAME HAVING COUNT(1) > 1;
-- 或者
SELECT NAME, COUNT(NAME) FROM student GROUP BY NAME HAVING COUNT(NAME) > 1;
1
2
3
4
5
+------+-------------+
| NAME | COUNT(NAME) |
+------+-------------+
| cat  |           3 |
| dog  |           2 |
1
2
-- 或者
SELECT * FROM student WHERE NAME IN ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1) > 1 );
1
2
3
4
5
6
7
8
9
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | cat  |   22 |
|  2 | dog  |   33 |
|  4 | cat  |   55 |
|  5 | dog  |   11 |
|  6 | cat  |   45 |
+----+------+------+
  • 删除 name 字段下的所有重复数据，只要有重复都删除掉
1
2
3
DELETE FROM student WHERE NAME IN (
    SELECT t.NAME FROM ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1) > 1 ) t
)
1
2
-- 查询删除重复数据后的结果
select * from student;
1
2
3
4
5
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | fish |   44 |
+----+------+------+
  • 删除 name 字段下的重复数据，仅仅留下一条数据
1
2
3
4
-- 此方法要求主键（ID）为自增长
DELETE FROM student WHERE id NOT IN ( 
    SELECT t.id FROM ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t
)
1
2
-- 查询删除重复数据后的结果
select * from student;
1
2
3
4
5
6
7
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | cat  |   22 |
|  2 | dog  |   33 |
|  3 | fish |   44 |
+----+------+------+

优化千万级数据的分页查询

优化的关键

在 SELECT 语句中，可以使用 LIMIT 子句来约束结果集中的行数。LIMIT 子句接受一个或两个参数，两个参数的值都必须为零或者正整数，具体的参数说明如下：

  • offset：指定要返回的第一行数据的偏移量。第一行数据的偏移量为 0，而不是 1。
  • count：指定要返回的最大行数。
1
SELECT column1, column2, ... FROM table LIMIT offset, count;

假设 emp 表中有 600 万条的数据，使用以下 SQL 进行分页查询，大概需要花费 18 秒才能查询得到结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT SQL_NO_CACHE
  a.empno,
  a.ename,
  a.job,
  a.sal,
  b.deptno,
  b.dname 
FROM
  emp a 
  LEFT JOIN dept b 
    ON a.deptno = b.deptno 
ORDER BY a.id
LIMIT 4950000, 30;

为什么上面的查询会耗费那么多时间呢？其实这是因为 LIMIT 后面的偏移量太大导致的。比如 LIMIT 4950000, 30，这等同于数据库要扫描出 4950030 条数据，然后再丢弃前面的 49500000 条数据，最后返回剩下的 30 条数据给用户，显然这种做法是不合理的。MySQL 的分页操作通常会使用 LIMIT 加上偏移量的办法来实现，可能还会加上合适的 ORDER BY 子句。当偏移量非常大的时候，它会导致 MySQL 扫描大量不需要的行，然后再抛弃掉，这在生产环境存在一定的性能隐患。在阿里巴巴的开发手册中，提到以下的分页查询优化方案：

优化的方案

方案一，使用覆盖索引 + 子查询

如果数据库表中有 ID 字段，并且在上面建立了索引（主键索引），那么可以先在索引树中找到开始位置的 ID 值，然后再根据找到的 ID 值查询数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
  a.empno,
  a.ename,
  a.job,
  a.sal,
  b.deptno,
  b.dname 
FROM
  emp a 
  LEFT JOIN dept b 
    ON a.deptno = b.deptno 
WHERE a.id >= (SELECT id FROM emp ORDER BY id LIMIT 4950000, 1)
ORDER BY a.id
LIMIT 30;

方案二，起始位置重定义

记住上次分页查询结果的主键位置，避免使用 LIMIT 的偏移量 offset。比如，记住上次分页查询结果的最后一条数据的 ID 是 4950000，然后就直接跳过 4950000，从 4950001 开始扫描表进行查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
  a.empno,
  a.ename,
  a.job,
  a.sal,
  b.deptno,
  b.dname 
FROM
  emp a 
  LEFT JOIN dept b 
    ON a.deptno = b.deptno 
WHERE a.id > 4950000
ORDER BY a.id
LIMIT 30;

方案三，服务降级不可用

  • 这是阿里巴巴推荐的解决方案，首先配置 LIMIT 的偏移量和获取行数的最大值，超过这个最大值，就返回空数据。因为按照业务来说，超过这个最大值后，用户已经不是在分页了，而是在刷数据。如果确认要查询数据，应该输入合适的查询条件来缩小范围，而不是一页一页地分页查询。

  • 当用户发出请求后，如果 offset 大于某个数值就直接返回一个 4xx 的错误，避免黑客攻击或者刷单行为。毕竟，正常人很少翻查 10 页以后的内容。另外，该有的服务降级、限流也应该考虑进去。比如，用户利用多线程工具调用接口，在短时间内执行 5000 次调用，那么就可以简单地使用计数器进行判断，并反馈给用户调用过于频繁，最后直接抛弃用户的请求。

如何往拥有千万级数据量的表添加索引

在千万级数据量的表中添加索引，操作步骤为：创建新表 + 创建索引 + 导入旧数据 + 废弃旧表。简而言之，” 腾笼换鸟”，稳定性压倒一切，保守一点不出错即可。

  • (1) 先创建一张跟原表 A 数据结构相同的新表 B。
  • (2) 在新表 B 上建立新的索引。
  • (3) 将原表 A 的数据迁移到新表 B。
  • (4) 将原表 A 改为别的表名，并将新表 B 改为原表的表名 A。

特别注意

在给表添加索引的时候，是会对表加锁的（表锁）。如果不谨慎操作，可能会出现严重的生产事故。比如，在添加索引的过程中，如果发生了数据修改（如 DELETE、UPDATE），则可能会导致客户端读取到不一致或错误的数据。因此，对应大数据量的表，需要使用 "腾笼换鸟" 的方式来添加索引。