你好，我是俊达。

这一讲主要是对专栏基础篇中思考题的一个统一的答疑。实际上在评论区中， 我也看到了同学们对很多思考题的精彩解答。这里做一个汇总和补充，方便大家查看。

第1讲

问题：早期的版本中，登录服务器本地的MySQL默认不需要密码。这存在一定的安全风险，因为你只要能登录到数据库服务器，就能访问这台服务器上的MySQL数据库。从5.7版本开始，给数据库的root账号设置了默认密码，首次登录时需要先修改密码。在部署一套MySQL数据库环境时，小明按规范修改了数据库root账号的密码，但是当时忘了将root密码记录下来，因此小明向你寻求帮助。你有办法帮小明解决这个问题吗？

@飒飒秋风 在评论区提供了这个问题的标准处理方法。

  1. 关闭MySQL。
1
kill <mysqld进程的pid>
  1. 添加–skip-grant-tables参数，启动MySQL。
1
mysqld_safe --defaults-file=my.cnf --skip-grant-tables --skip-networking &
  1. 执行flush privileges，加载权限表。
1
flush privileges;
  1. 执行alter user命令，修改root用户密码
1
alter user 'root'@'localhost' identified by 'password';
  1. 重新启动MySQL，去掉–skip-grant-tables参数。

使用参数–skip-grant-tables启动的MySQL，不校验用户名和密码，也不校验用户权限，可以执行任何操作。因此一般建议同时加上–skip-networking参数，这样就只能在服务器本地登陆数据库。不过8.0使用–skip-grant-tables时，默认就只能本地访问了。

要先执行flush privileges命令加载权限表，然后才能修改密码。修改完密码后，重新启动MySQL，这次去掉–skip-grant-tables参数。

第2讲

问题：一般情况下，我们都建议将数据库部署到内网，因为将数据库暴露到公网上有比较大的安全风险。但是你的公司有一个特殊的业务，就是需要通过公网访问MySQL数据库。请你评估下将数据库放到公网有哪些风险？你应使用哪些方法来尽量保证数据库和数据的安全？

@Geek_0126 @Amosヾ的留言中，都讲到了数据库公网访问的风险和解决方法。

风险1. 数据库被各种手段攻击，用户密码被破解。

为了避免风险，首先在网络层限制来源IP。这和创建MySQL用户时指定访问IP还不一样。创建用户时指定IP，并不会阻止在网络层面访问数据库端口。数据库的用户密码要有一定的复杂度，避免使用简单密码。同时也要遵循最小权限原则。

风险2. 传输的数据被被拦截捕获。

如果MySQL客户端和服务端之间传输的TCP数据被捕获，并且没有使用加密传输，那么很容易解析出客户端发送的命令、服务端返回的数据。解决这个问题最好的办法是使用加密传输。

还可以在创建用户时，指定用户必须使用加密传输，还可以要求验证客户端证书。

1
2
create user 'user1'@'%' identified by 'somepass' require ssl;
create user 'user2'@'%' identified by 'somepass' require subject 'some subject';

第3讲

问题：开发同学反馈访问数据库总是报错，并提供了一些报错日志。你应该怎么来分析和解决这个问题呢？

1
2
3
4
5
6
7
8
9
10
ERROR druid.sql.Statement -{conn-10094, stmt-26348} execute error. SELECT 1 FROM DUAL
com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 11,394,944 milliseconds ago. The last packet sent successfully to the server was 11,394,950 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.


The last packet successfully received from the server was 899,883 milliseconds ago. The last packet sent successfully to the server was 899,890 milliseconds ago.
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.


The last packet successfully received from the server was 1,799,883 milliseconds ago. The last packet sent successfully to the server was 1,799,891 milliseconds ago.
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

这是使用JDBC访问MySQL经常会遇到的一个报错。报错信息“N milliseconds ago”里的时间是一个关键的信息。比如上面例子中的“899,883 milliseconds ago”，说明15分钟连接就断开了。需要检查下wait_timeout的值，是不是真的是15分钟。

wait_timeout是一个会话级的变量，每个会话可以设置成不同的值。因此需要确认被断开的连接，wait_timeout是怎么设置的。8.0中可以通过performance_schema.variables_by_thread表，查看其他会话的变量值。

1
2
3
4
5
6
7
8
mysql>  select * from performance_schema.variables_by_thread 
    where variable_name = 'wait_timeout';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        64 | wait_timeout  | 28800          |
|        65 | wait_timeout  | 3600           |
+-----------+---------------+----------------+

你还可以使用其他一些方法来验证空闲超时时间。比如查看processlist，看看command为Sleep的连接，最大的时间是多少。或者执行select sleep(N)，看看查询是否能正常完成。

在上面这个例子中，会话的wait_timeout的设置是几万秒，并不是15分钟。但是超过15分钟，连接就会被断开，Processlist中看不到空闲时间超过15分钟的会话。执行sleep(901)也无法成功。

最终定位的原因是使用了Nginx来访问MySQL，而代理的超时时间配置成了900秒。连接池的keepalive配置也没有生效。

第4讲

问题：我们写SQL语句时，关键字一般不区分大小，不同的人可能有不同的习惯。对于库名、表名、列名，不同的数据库有不同的处理方法。比如Oracle中，表名默认不区分大小写。在MySQL中，根据操作系统的不同，表名就可能会区分大小写。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| Ta             |
| ta             |
+----------------+
2 rows in set (0.01 sec)


mysql> select * from ta;
Empty set (0.01 sec)


mysql> SELECT * From tA;
ERROR 1146 (42S02): Table 'db01.tA' doesn't exist

参数lower_case_table_names可用来控制表名是否区分大小写。

1
2
3
4
5
6
7
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

MySQL 8.0中，这个参数只能在数据库初始化之前设置，之后就不能再修改了，修改后数据库都无法启动。

1
2
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

你觉得这个参数应该怎样设置？原因是什么？

MySQL会根据操作系统和底层文件系统的特性，来选一个默认的lower_case_table_names参数。但是我觉得这并不是很好的一种做法。

一般我们写SQL时，并不会特别注意表名的大小写，不同的人可能有不同的习惯，有时使用大写，有时使用小写。比如下面这3个SQL，我相信大多数人都会认为这其实是同一个SQL。

1
2
3
select * from tab;
select * from TAB;
select * from Tab;

但是linux下，如果使用默认的lower_case_table_names=0，这3个SQL访问了完全不同的3个表。

1
2
3
4
5
6
7
8
mysql> show tables;
+----------------+
| Tables_in_db00 |
+----------------+
| TAB            |
| Tab            |
| tab            |
+----------------+

这除了引起混乱，还有其他什么重要的作用吗？

有时，这也会给跨操作系统的数据库迁移带来麻烦，比如将MySQL从Windows迁移到Linux，或者反过来迁移。我的建议是所有环境的MySQL都将参数lower_case_table_names设置为1，统一转换成小写。

第5讲

问题：你刚刚接到了1个需求，要开发一个员工管理系统。该系统计划使用MySQL数据库，设计人员提供了表结构，其中就包括下面这2个表。作为一位资深的MySQL使用者，你觉得这2个表存在哪些问题？你会怎么改进呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table t_employee(
    emp_id int not null comment '员工编号',
    emp_name varchar(200) comment '员工姓名',
    birth_day varchar(200) comment '出生日期',
    age int comment '年龄',
    gender varchar(200) comment '性别',
    address varchar(200) comment '住址',
    photo blob comment '员工照片',
    primary key(emp_id)
) engine=innodb charset utf8;


create table t_emp_salary(
    emp_id varchar(30) not null comment '员工编号',
    effect_date varchar(200) comment '生效日期',
    salary double comment '薪资',
    descripton varchar(2000) comment '备注',
    primary key(emp_id, effect_date)
) engine=innodb charset utf8;

这个问题 评论区中@吃苹果的考拉 @123 @Geek_0126 的回答都比较完整了。

t_employee表：

  • emp_name varchar长度不用设置得这么长
  • birth_day 使用日期类型更好
  • age 使用smallint也足够了
  • gender 可以使用tinyint或enum
  • photo 建议数据库中值保存一个文件地址。图片存储在分布式文件系统中。

t_emp_salary表：

  • emp_id和t_employee保持一致
  • effect_date使用日期类型
  • salary使用Decimal或int类型
  • 联合主键的问题。

第6讲

问题：MySQL的备库复制中断了，查看错误信息，发现是有一个建表的语句报错了。

1
2
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'c1a67221-f9fc-11ed-bffd-fa8338b09400:106' at master log binlog.000020, end_log_pos 4203259. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
                                           CHANNEL_NAME:
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1118
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'c1a67221-f9fc-11ed-bffd-fa8338b09400:106' at master log binlog.000020, end_log_pos 4203259; Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.' on query. Default database: 'repl'. Query: 'create table t_inno1(
       c01 varchar(768),
       c02 varchar(768),
       c03 varchar(768),
       c04 varchar(768),
       c05 varchar(768),
       c06 varchar(768),
       c07 varchar(768),
       c08 varchar(768),
       c09 varchar(768),
       c10 varchar(768),
       c11 varchar(398)
    ) engine=innodb row_format=compact charset latin1'

但是到主库上查看后，发现这个表创建成功了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql >show create table t_inno1\G
*************************** 1. row ***************************
       Table: t_inno1
Create Table: CREATE TABLE `t_inno1` (
  `c01` varchar(768) DEFAULT NULL,
  `c02` varchar(768) DEFAULT NULL,
  `c03` varchar(768) DEFAULT NULL,
  `c04` varchar(768) DEFAULT NULL,
  `c05` varchar(768) DEFAULT NULL,
  `c06` varchar(768) DEFAULT NULL,
  `c07` varchar(768) DEFAULT NULL,
  `c08` varchar(768) DEFAULT NULL,
  `c09` varchar(768) DEFAULT NULL,
  `c10` varchar(768) DEFAULT NULL,
  `c11` varchar(398) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
1 row in set (0.00 sec)

为什么会出现这种情况呢？

这里关键的报错信息是“Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.”。

建表是否成功和参数innodb_strict_mode的设置有关。分析这个问题时，先对比主库和备库上innodb_strict_mode的设置。

innodb_strict_mode可以在会话级别修改，主库上将innodb_strict_mode的会话值改成OFF，然后建表成功了。但是MySQL并没有把这个会话变量记录在Binlog中，备库执行时，innodb_strict_mode=ON，因此失败了。

一种解决方法是备库上临时将innodb_strict_mode的全局值修改成OFF，再开启复制。

第7讲

问题：某一个项目发布时，执行了以下数据初始化脚本。

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE `t_b` (
       `a` varchar(30) DEFAULT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.03 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into t_b values('中文符号');
Query OK, 1 row affected (0.00 sec)

对数据进行检查后，没有发现任何问题。

1
2
3
4
5
6
7
mysql> select * from t_b;
+--------------+
| a            |
+--------------+
| 中文符号     |
+--------------+
1 row in set (0.00 sec)

但是另外一个同事查询这个表时，发现无论怎么设置字符集，查出来都是乱码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> select * from t_b;
+------------------+
| a                |
+------------------+
| 娑??鏋冪粭锕褰? |
+------------------+
1 row in set (0.00 sec)


mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t_b;
+--------------+
| a            |
+--------------+
| 涓枃绗﹀彿 |
+--------------+
1 row in set (0.00 sec)

这位同事找到了你，请你帮忙分析为什么会出现这样的问题？应该如何解决呢？

@Shelly 在评论区中给出了这个问题的根本原因，set names指定的字符集和终端实际的编码不一致。

utf-8的终端下，执行set names gbk后，虽然写入、读取中文字符好像没有任何问题，但是实际上表里面存的数据已经不对了。

1
2
3
4
5
6
mysql> select a, hex(a), char_length(a) from t_b;
+--------------+--------------------------+----------------+
| a            | hex(a)                   | char_length(a) |
+--------------+--------------------------+----------------+
| 中文符号     | E4B8ADE69687E7ACA6E58FB7 |              6 |
+--------------+--------------------------+----------------+

“中文符号”的GBK编码是D6D0 CEC4 B7FB BAC5，但是表里面存的其实是“中文符号”的UTF8编码。这和表的字符集不一致了，因此执行char_length获取的字符数也不对了。

这种问题要从源头上解决，把错误的数据修正过来。比如删除有问题的数据，设置正确的字符集后，重新写入数据。

第8讲

问题：源库中有一个大表，表结构定义如下：

1
2
3
4
5
6
7
8
9
10
create table big_table(
    col1 varchar(32) not null,
    col2 varchar(32) not null,
    col3 varchar(32) not null,
    col4 varchar(256),
    col5 varchar(256),
    ....
    col10 varchar(512),
    primary key(col1, col2, col3)
) engine=innodb;

这个表总共有3000万行数据，平均行长度大约为2K。现在需要将这个表复制到目标库。源库和目标库都是MySQL。有一台4核8G的中转机器供你使用。请问你会怎么解决这个需求？如果要你写一段程序来完成这个任务，需要注意什么？如何提高数据复制的速度？

评论区中@123 @dream 给出了处理这个问题的一些思路。这里我总结一下。

  1. 使用多个线程来处理。一个线程负责查询数据，将记录缓存在内存的一个队列中。再使用一个或多个线程从队列中获取数据，执行Insert操作。Insert时，可以使用MySQL的批量插入，将多条数据（比如100条）拼接成一个Insert SQL再执行。
  2. 由于表的数据量比较大，客户端无法一次性将所有结果集都缓存起来，要么使用分页的SQL，要么使用流式处理。这个表使用了组合主键，分页SQL还不太好写。如果是用了自增主键，分页SQL就很容易写了，而且也可以很方便地使用多个线程分段查询数据。

第9讲

问题：这一讲我们提到了mysqldump的一个限制：导出的数据都存在同一个文件中，不方便并行导入。一次紧急故障中，需要将mysqldump备份出来的数据恢复出来，数据库比较大，单线程恢复的话，耗时又会比较久，你有哪些办法来加快恢复的速度？

@范特西 提到了可以设置数据库的一些参数来提升恢复的速度。

其实mysqldump生成的文件，有一些特定的格式，比如看下面这个例子，创建数据库、创建表、Insert数据前，都有相应的注释文本。你可以根据这个特点，将dump文件分割成多个文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--
--  Database: `backme`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backme` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `backme`;

--
-- Table structure for table `t_emoji`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t_emoji` (
  `a` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_emoji`
--

LOCK TABLES `t_emoji` WRITE;
/*!40000 ALTER TABLE `t_emoji` DISABLE KEYS */;
INSERT INTO `t_emoji` VALUES (_binary '😀😃'),(_binary '中文符号');
/*!40000 ALTER TABLE `t_emoji` ENABLE KEYS */;
UNLOCK TABLES;

第10讲

问题：由于公司的策略，需要将一个核心业务系统的Oracle数据库迁移到MySQL。这个Oracle数据库大概有1T数据，迁移过程中，要尽可能缩短业务停机的时间，业务方能接受的最大停机时间在1～2小时之内。请你设计一个方案，将数据平滑地迁移到MySQL。你需要考虑全量数据如何迁移，业务运行期间新产生的数据如何迁移。

@123 @ls @Geek_0126 在评论区提供了一些方法，比如使用一些数据同步的工具。

这个问题和第8讲的思考题有一些类似的地方，都需要将数据从一个数据库迁移到另外一个数据库。这个问题多了一些限制，源库变成了Oracle，源端和目标端使用了不同类型的数据库，而且还要考虑增量数据，要尽量减少停机时间。

这类异构数据迁移要考虑的几个问题。

  • 数据类型：不同的数据库在数据类型上会有一些区别，需要做一个映射。比如Oracle的Number类型，根据使用场景，可以对应到MySQL中的int系列类型或decimal。

  • 全量数据处理：全量数据的同步本质上就是Select+Insert。不过由于数据量大，要考虑效率问题。使用sqluldr2将Oracle的数据导出成文本，然后再使用load data命令导入到MySQL是效率比较高的一种方法。

  • 增量数据处理：增量数据有几种不同的处理方式。

    • 一种方式是在业务侧处理，比如使用双写，或者将源端所有的数据修改都记录一份到消息队列中，再写一个程序订阅消息，将变更同步到目标库。这对应用有比较高的要求。
    • 另一种方式是解析Oracle的Redo日志，同步增量数据。这通常需要借助一些工具，比如OGG。自己解析Redo有比较高的技术门槛，不同版本Oracle Redo格式可能还会有一些差异。可以尝试Oracle的LogMiner。
    • 还有一种方式是使用物化试图，或者给源端的表建立触发器，将数据变动记录到日志表，根据日志表的数据来增量同步数据。这会对源端的写入带来额外的负担。

综合来看，使用一些成熟的工具，是实施异构增量迁移比较好的一个选择。

第11讲

问题：只修改元数据的DDL、INSTANT DDL执行速度通常都很快，但是这些DDL执行也是需要获取元数据锁的，比如下面这个例子：

  • 会话1开启一个事务，执行一个select for update操作。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> desc t_ddl;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   | PRI | NULL    |       |
| b     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)


mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_ddl limit 1 for update;
Empty set (1.12 sec)
  • 会话2 执行一个instant DDL。

mysql> alter table t_ddl add c int, algorithm=instant;

  • 会话3 执行一个普通的select操作。

mysql> select * from t_ddl limit 1;

你会发现，会话2和会话3都被阻塞了。从processlist可以看到，它们都在等待元数据锁。（输出结果做了简化）

1
2
3
4
5
6
7
8
mysql> show processlist;
+----+---------+------+---------------------------------+------------------------------------------------+
| Id | Command | Time | State                           | Info                                           |
+----+---------+------+---------------------------------+------------------------------------------------+
|  8 | Sleep   |  116 |                                 | NULL                                           |
| 11 | Query   |  105 | Waiting for table metadata lock | alter table t_ddl add c int, algorithm=instant |
| 12 | Query   |   96 | Waiting for table metadata lock | select * from t_ddl limit 1                    |
+----+---------+------+---------------------------------+------------------------------------------------+

从performance_schema.metadata_locks也可以看到t_ddl表的元数据持有和请求情况。

1
2
3
4
5
6
7
8
9
mysql> select * from metadata_locks where object_name = 't_ddl';
-------------+-------------------+-------------+-------------------+-----------------+
 OBJECT_NAME | LOCK_TYPE         | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID |
-------------+-------------------+-------------+-------------------+-----------------+
 t_ddl       | SHARED_WRITE      | GRANTED     | sql_parse.cc:6093 |              49 |
 t_ddl       | SHARED_UPGRADABLE | GRANTED     | sql_parse.cc:6093 |              50 |
 t_ddl       | EXCLUSIVE         | PENDING     | mdl.cc:3753       |              50 |
 t_ddl       | SHARED_READ       | PENDING     | sql_parse.cc:6093 |              51 |
-------------+-------------------+-------------+-------------------+-----------------+

还有哪些情况会导致DDL无法获取到元数据锁？怎么快速定位到元数据锁的阻塞源？

@叶明 @Shelly @Geek_0126 提供了这个问题的解答。这里再简单总结下。

下面这几种情况都会阻塞DDL。

  • 有慢SQL还在运行中，访问了这个表。
  • 事务中，访问过这个表（查询或修改），并且事务还没有提交。
  • 全局锁，比如flush tables with read lock，lock instance for backup
  • 表锁，lock tables

从sys.schema_table_lock_waits、performance_schema.metadata_locks表查看表锁、元数据锁。

第12讲

问题：gh-ost是比较知名的一款在线DDL工具，在实现上也非常有特色。gt-ost在执行DDL变更时，不需要给源表建触发器，而是通过BINLOG来捕捉DDL变更期间发生过变化的数据。我尝试在测试环境做了一个实验。

1
2
3
4
5
6
7
8
gh-ost -alter "alter table employees_bak modify hire_date date" \
  -user user_01 \
  -host 127.0.0.1 \
  -password somepass \
  -database employees \
  --allow-on-master \
  --execute \
  -heartbeat-interval-millis 1000

在执行gh-ost前，我先开启了general_log，最终在general log中发现有以下这几类SQL。

  • create table like
1
2
create /* gh-ost */ table `employees`.`_employees_bak_gho` 
  like `employees`.`employees_bak`
  • alter table

alter /* gh-ost */ table employees._employees_bak_gho modify hire_date date

  • select
1
2
3
4
5
6
7
8
select  /* gh-ost `employees`.`employees_bak` iteration:0 */
    `emp_no`
from `employees`.`employees_bak`
where ((`emp_no` > _binary'10001') or ((`emp_no` = _binary'10001'))) 
and ((`emp_no` < _binary'20000') or ((`emp_no` = _binary'20000')))
order by `emp_no` asc
limit 1
offset 999
  • insert ignore into
1
2
3
4
5
6
7
8
9
10
insert /* gh-ost `employees`.`employees_bak` */ 
ignore into `employees`.`_employees_bak_gho` 
(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)
( 
  select `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` 
  from `employees`.`employees_bak` force index (`PRIMARY`)
  where (((`emp_no` > _binary'10001') or ((`emp_no` = _binary'10001')))
  and ((`emp_no` < _binary'11000') or ((`emp_no` = _binary'11000')))) 
  lock in share mode
)
  • replace into
1
2
3
replace /* gh-ost `employees`.`_employees_bak_gho` */ into
    `employees`.`_employees_bak_gho`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)
values (20001, '1962-05-16', _binary'Atreye', _binary'Eppinger', ELT(1, 'M','F'), '1990-04-18')
  • rename table

rename /* gh-ost */ table employees.employees_bak to employees._employees_bak_del, employees._employees_bak_gho to employees.employees_bak

上面的这几类SQL，分别起到了什么作用？insert ignore into和replace into的执行顺序，对最终数据的一致性有影响吗？执行insert ignore into … select from … 的时候，为什么要加上lock in share mode？

@叶明 @Shelly @123 在评论区提供了这个问题的解答。

  • create table like：创建临时表，复制表结构。
  • alter table：根据命令更改表表结构。
  • select：并发读取数据（看到了偏移量，应该是多线程）写入临时表。
  • insert ignore into：从源表select出来的数据，使用insert ignore into插入到临时表中，忽略重复的数据。
  • update：binlog中的Update事件解析为Update语句。
  • replace into：binlog中的Insert事件解析为replace into语句，将增量数据写到临时表。
  • delete：binlog中的Delete事件解析为Delete语句。
  • rename table：将临时表变更为生产表。

lock in share mode是为了保证影子表数据和原表一致。 Binlog可见和事物可见之间存在一个时间差，如果不加lock in shared mode，源库执行delete语句时，binlog中读到了delete语句，但select可能会读取到DELETE之前的数据。这样，如果先对影子表执行了delete，然后再执行insert ignore into时，会把本来应该已经delete掉的数据，插入到影子表，就多数据了。

第13讲

问题：国庆节假期，DBA小明突然接到大量数据库告警，登录数据库执行SHOW PROCESSLIST后，发现大量会话被阻塞了。下面提供了部分会话的信息。请你根据这些信息，帮小明一起分析下，为什么会出现这样的问题？应该怎么解决这个问题呢？有哪些地方可以改进？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Id: 1842782 
User: user_xx 
Host: xx.xx.xx.xx:59068 
db: db_xx 
Command: Query 
Time: 2326 
State: Waiting for table
Info: update stat_item_detail set sold=sold+1, money=money+19800, Gmt_create=now() where item_id=1234567801 and day='2011-10-07 00:00:00


Id: 1657130 
User: user_xx 
Host: yy.yy.yy.yy:40093 
db: db_xx 
Command: Query 
Time: 184551 
State: Sending data 
Info: select item_id, sum(sold) as sold from stat_item_detail where item_id in (select item_id from stat_item_detail where Gmt_create >= '2011-10-05 08:59:00') group by item_id


Id: 1044
User: system user
Host:
db:
Command: Connect 
Time: 27406
State: Flushing tables FLUSH TABLES
Info:

@叶明 @binzhang 提供了这个问题的解答。我再补充一些信息。

  • Id为1657130的线程是这里的阻塞源。这个SQL执行了2天多还没完成。当时用的应该是MySQL 5.1还是5.5，IN子查询的效率比较低，以主查询作为驱动表，但是主查询上又没有其他有效的过滤条件。
  • Id为1044的线程执行了Flushing tables FLUSH TABLES，由于Id为1657130的线程一直在执行SQL，因此被阻塞了。
  • Id为1044的User是system user，这比较有意思，因为这是一个SQL线程，说明FLUSH TABLES这个操作是从主库中复制过来的。这实际上是一个MM架构的环境，备库上使用mysqldump备份数据，发起了FLUSH TABLES操作，复制到了业务的主库。
  • Id为1842782的线程访问表时，被阻塞了。

这里其实还暴露了当时的监控不够完善。ID为1657130的线程，执行超过了2天。SQL执行时间超过一定的阈值就可以告警出来。

第14讲

问题：有一个生产环境的系统，有时候会出现数据库连接超时的报错。从报错的信息看，超时应该和网络有点关系。遇到这样的问题，你会使用哪些方法来定位原因呢？

如果你知道数据库的连接地址，用户名和密码，就可以使用客户端测试数据库是否能正常连接。有时候，我们可能并不真正知道数据库的连接信息，比如连接地址可能硬编码在代码中了，或者连接地址在多个配置文件中个呢配置，或者连接地址信息存在在某个数据库中。

有时候，可能是因为连接到了错误的IP地址。有什么办法查看应用连接了哪个IP呢？有几种方法可以尝试。

  • 使用strace，或者其他trace工具（如systemtap），查看应用程序的系统调用，分析系统调用的参数。
  • 使用net stat或ss命令，查看系统上的TCP连接。如果程序连接了错误的IP，可能会看到有TCP连接的状态是SYN_SENT。
  • 使用tcpdump抓取和分析网络流量。

第15讲

问题：MySQL中存在这么一个现象，平时执行得好好的SQL，在数据库很繁忙的时候，执行效率也会变得很差，当然，这可以理解。你从Processlist或慢SQL日志中看到执行耗时比较长的SQL，其中有些是引起数据库性能问题的罪魁祸首，有些则是受害者。你应该怎么区分这两种情况，找到真正需要优化的那些SQL呢？

@叶明 @范特西 提供很好的一个思路，根据SQL的扫描函数来判断。还可以根据SQL的执行历史来进行分析。

如果有一个空闲的备库，可以到备库上执行SQL，对比执行时间。

第16讲

问题：有些情况下，我们可能会使用逻辑的方式来升级数据库，比如将线下低版本的数据库迁移到云上高版本的实例中。使用逻辑的方式升级或降级数据库，可能会存在哪些问题？有哪些需要注意的地方？

使用逻辑备份，比如mysqldump导出的sql文件，到低版本的MySQL上执行时，可能会由于数据类型、SQL语法等不兼容问题，出现导入报错的情况。如果高版本的mysql中使用了一些新的特性，在低版本上不支持，就可能会遇到问题。