CHAR和VARCHAR存取的差别


你真的知道CHAR和VARCHAR类型在存储和读取时的区别吗?

还是先抛几条结论吧:
1、存储的时候,CHAR总是会补足空格后再存储,不管用户插入数据时尾部有没有包含空格。
2、存储的时候,VARCHAR不会先补足空格后再存储,但如果是用户在插入时特地加了空格那就会如实存储,而不会给删除。
3、读取数据时,CHAR总是会删除尾部空格(哪怕是写入时包含空格)。
4、读取数据时,VARCHAR总是如实取出之前存入的值(如果存储时尾部包含空格,就会继续保留着,不会像CHAR那样删除尾部空格)。

下面是测试验证过程。
1、测试CHAR类型
表结构:

CREATE TABLE `tchar` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `c1` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入几条记录:

insert into tchar values (1, concat('a', repeat(' ',19)));
insert into tchar values (2, concat(' ', repeat('a',19)));
insert into tchar values (3, 'a');
insert into tchar values (4, ' ');
insert into tchar values (5, '');

查看存储结构:

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... <- id=1
(4) normal record offset:169 heapno:3 ... <- id=2
(5) normal record offset:212 heapno:4 ... <- id=3
(6) normal record offset:255 heapno:5 ... <- id=4
(7) normal record offset:298 heapno:6 ... <- id=5

看到这坨东西有点懵是不是,还记得我给你们安利过的一个工具不,看这里:innblock | InnoDB page观察利器,https://mp.weixin.qq.com/s/yfi5XikDJlh6-nS-eoJbcA。

可以看到,无论我们存储多长的字符串进去,每条记录实际都是占用43(169-126=43)字节。由此结论1成立。
简单说下,43字节的由来:
DB_TRX_ID, 6字节。
DB_ROLL_PTR, 7字节。
id, int, 4字节。
c1, char(20), 20字节;因为是CHAR类型,还需要额外1字节。
每条记录总是需要额外5字节头信息(row header)。
这样总的加起来就是43字节了。

再看下读取tvarchar表的结果:

select id,concat('000',c1,'$$$'),length(c1) from tchar ;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$')     | length(c1) |
+----+----------------------------+------------+
|  1 | 000a$$$                    |          1 | <- 删除尾部空格
|  2 | 000 aaaaaaaaaaaaaaaaaaa$$$ |         20 |
|  3 | 000a$$$                    |          1 |
|  4 | 000$$$                     |          0 | <- 删除尾部空格,结果和id=5一样
|  5 | 000$$$                     |          0 |
+----+----------------------------+------------+

2、测试CHAR类型
表结构:

CREATE TABLE `tvarchar` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `c1` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入几条记录:

insert into tvarchar values (1, concat('a', repeat(' ',19)));
insert into tvarchar values (2, concat(' ', repeat('a',19)));
insert into tvarchar values (3, 'a');
insert into tvarchar values (4, ' ');
insert into tvarchar values (5, '');
insert into tvarchar values (6, '');

查看存储结构:

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... <- id=1
(4) normal record offset:169 heapno:3 ... <- id=2
(5) normal record offset:212 heapno:4 ... <- id=3
(6) normal record offset:236 heapno:5 ... <- id=4
(7) normal record offset:260 heapno:6 ... <- id=5
(8) normal record offset:283 heapno:7 ... <- id=6

可以看到,几条记录的字节数分别是:43、43、24、24、23、23(最后一条记录和id=5那条记录一样)。
对上面这个结果有点诧异是不是,尤其是id=1的记录(插入的是'a…后面19个空格'),居然也要消耗43字节,这就佐证了上面的结论2。
同样的,id=3和id=4这两条记录都是占用24字节,而id=5和id=6这两条记录都是占用23字节(没有额外存储字符串的字节数,只有id列4个字节)。

再看下读取tvarchar表的结果:

select id,concat('000',c1,'$$$'),length(c1) from tvarchar;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$')     | length(c1) |
+----+----------------------------+------------+
|  1 | 000a                   $$$ |         20 | <- 读取结果中没有删除尾部的空格
|  2 | 000 aaaaaaaaaaaaaaaaaaa$$$ |         20 | 
|  3 | 000a$$$                    |          1 |
|  4 | 000 $$$                    |          1 | <- 读取结果中没有删除此空格
|  5 | 000$$$                     |          0 |
|  6 | 000$$$                     |          0 |
+----+----------------------------+------------+

总的来说,可以总结成两条结论:
1、从读取的结果来看,CHAR类型列看起来像是在存储时把空格给吃了,但实际上只是在读取时才给吃了(显示层面上把空格删除了)。
2、从读取的结果来看,VARCHAR类型列看起来像是反倒保留了多余的空格,实际上也是只在读取时才恢复这些空格(但实际物理存储时还是会删掉这些空格)。

最后,来看下文档里怎么说的:

When CHAR values are stored, they are right-padded with spaces to the specified length. 简言之,CHAR列在存储时尾部加空格补齐长度。

When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled. 简言之,CHAR列在读取时会去掉尾部空格,除非设置sql_mode值PAD_CHAR_TO_FULL_LENGTH=1。

VARCHAR values are not padded when they are stored. 简言之,存VARCHAR时尾部不加空格。

Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. 简言之,读取VARCHAR时会显示空格。

以上测试使用的版本及环境:

mysql> select version()\G
...
version(): 8.0.15

mysql> select @@sql_mode\G
...
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

参考文档

【全文完】


由叶老师主讲的知数堂「MySQL优化课」第17期已发车,课程从第15期就升级成MySQL 8.0版本了,现在上车刚刚好,扫码开启MySQL 8.0的修行之旅吧。

另外,叶老师在腾讯课堂的短课程《MySQL性能优化》已开课,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度。

下面是报名小程序码

更安全的MySQL 8.0之全新密码策略


从MySQL 8.0开始,安全策略有了重大加强,采用了新的密码插件,增加历史密码限制、双密码、密码强度约束等新特性。

从MySQL 8.0开始,安全策略有了重大加强。
对我们影响最直接的是,从MySQL 8.0里将caching_sha2_password作为默认的身份验证插件,而不再使用mysql_native_password插件。这时候如果再用旧版本客户端或协议连接,就会报告下面的错误:

$ mysql -uyejr -p<br />
Enter password:<br />
ERROR 2059 (HY000): Authentication plugin &#39;caching_sha2_password&#39; cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found<br />

临时解决方案是将该账号密码插件改回mysql_native_password即可,或者修改全局参数:

#my.cnf
[mysqld]
default_authentication_plugin = mysql_native_password

不过,还是强烈建议采用新的插件,因为它更安全,性能也更高

其他的几个新密码策略主要有:

  • 支持密码过期策略,需要周期性修改密码
  • 增加历史密码检测机制,防止总是几个密码反复重用
  • 需要提供旧密码才能修改新密码,防止被篡改
  • 支持双密码机制,可以选择采用主密码还是第二密码连接,安全性更高
  • 增加密码强度约束,避免使用弱密码

下面我挑几个重点的来说下吧。
1、设置账号密码过期策略,要求定期修改密码
例如设置规则:密码默认42天过期(在美上市公司要求的SOX 404法案规则)

#my.cnf
[mysqld]
default_password_lifetime=42

当然了,也可以单独设置某个账户永不过期

[mysql@yejr.me]> ALTER USER yejr PASSWORD EXPIRE NEVER;

当密码过期后,该账户连接登入后,就会提示下面的告警:

[mysql@yejr.me]> \s<br />
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.<br />

这时候,可以自己动手修改密码,例如:

[mysql@yejr.me]> alter user yejr@'%' IDENTIFIED WITH 
  caching_sha2_password BY 'abcd';

这时候,为了防止使用弱密码或重用历史密码,就可以设置更严格的密码策略了。

2、设置历史密码检测机制,防止反复重用旧密码
例如设置规则:存储5个历史密码,且在180天内不能重复使用这些旧密码

#my.cnf
[mysqld]
#存储最近的6次密码
password_history=6
#最近6次密码1年内不得重用
password_reuse_interval=365

这时如果修改密码使用了最近一年内用过的历史密码,则会报告下面的错误:
[mysql@yejr.me]> alter user yejr identified with <br />
caching_sha2_password by &#39;abcd&#39;;<br />
ERROR 3638 (HY000): Cannot use these credentials for &#39;yejr@%&#39; because they contradict the password history policy

3、启用双密码机制
上面提到,在美上市公司要求每42天修改一次密码,这种情况下,可能我们会非常担心有某些地方忘了修改,从而没有很好的执行这个政策。
有了双密码机制后,就可以放心的修改密码了,因为相当于新旧密码都可以同时使用,那么可以留有一定的缓冲时间进行检查确认了,防止有些地方漏了修改。
可以执行下面这样的命令使用双密码:

[mysql@yejr.me]> alter user yejr@'%' IDENTIFIED WITH 
  caching_sha2_password BY 'new-abc' RETAIN CURRENT PASSWORD;

最后建议大家早日升级到最好用的MySQL 8.0版本,享受新特性,以及更好的性能。

enjoy mysql。

【全文完】


由叶老师主讲的知数堂「MySQL优化课」第17期已发车,课程从第15期就升级成MySQL 8.0版本了,现在上车刚刚好,扫码开启MySQL 8.0的修行之旅吧。

另外,叶老师在腾讯课堂的短课程《MySQL性能优化》已开课,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度。

下面是报名小程序码

略奇葩的多表跳跃自增需求


在同一个实例下,如何让多个表有不同自增起始值,且以相同步幅自增?

某天,小明提了个需求,是下面这样的

  1. 有10个结构一样的表
  2. 每个表的主键起始值分别从 1 递增到 10
  3. 每个表的主键自增值每次都跳跃 10(因为总共10个表),也就是说第一个表的主键值是这样的1, 11, 21, 31。

接下来,小明是这么做的:

1、修改auto_increment设置

[root@yejr.me]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+

2、创建表,并插入数据

[root@yejr.me]> create table m01(
id int primary key auto_increment, name varchar(64));
...
[root@yejr.me]> create table m10(
id int primary key auto_increment, name varchar(64));

[root@yejr.me]> insert into m01(id,name) values(1,'a');
[root@yejr.me]> insert into m01(id,name) values(0,'a');
...
[root@yejr.me]> insert into m10(id,name) values(10,'j');
[root@yejr.me]> insert into m10(id,name) values(0,'j');

3、读取数据检查正确与否

[root@yejr.me]> select * from m01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
| 11 | a    |
+----+------+

[root@yejr.me]> select * from m02;
+----+------+
| id | name |
+----+------+
|  2 | b    |
| 11 | b    |  --本来期望的值是12才对
+----+------+

[root@yejr.me]> select* from m10;
+----+------+
| id | name |
+----+------+
| 10 | j    |
| 11 | j    |  --本来期望的值是20才对
+----+------+

并不符合预期,那到底怎么做才对呢?

看来,小明并没有正确设置关于自增的2个选项。

我们先来看下这2个选项的作用描述:

  • auto_increment_increment,控制每次自增的步幅,也就是每次自增长的幅度
  • auto_increment_offset,设置自增列的起始值

事实上,这2个选项通常用于双主或环形复制环境,让两个节点分别使用不同的自增值,比如一个是1、3、5、7,另一个是2、4、6、8。

在误操作或不慎在两边都写入数据时,不会发生主键值重复冲突的情况。尤其是在发生故障切换时,原来的主库A可能有部分数据没及时复制到从库B上,当旧主库A恢复后,这些数据再复制到从库B后也不会发生主键值重复冲突的问题。

好了,既然知道了这2个选项的作用,那么正确的做法就该是这样的。

往第1个表里写数据:

[root@yejr.me]> set auto_increment_offset = 1;
[root@yejr.me]> set auto_increment_increment = 10;
[root@yejr.me]> insert into m01(id,name) values(1,'a');
[root@yejr.me]> insert into m01(id,name) values(0,'a');
...
[root@yejr.me]> select * from m01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
| 11 | a    |
| 21 | a    |
+----+------+

往第2个表里写数据:

[root@yejr.me]> set auto_increment_offset = 2; ---注意这里
[root@yejr.me]> set auto_increment_increment = 10;
[root@yejr.me]> insert into m02(id,name) values(2,'b');
[root@yejr.me]> insert into m02(id,name) values(0,'b');
...
[root@yejr.me]> select * from m02;
+----+------+
| id | name |
+----+------+
| 12 | b    |
| 22 | b    |
| 32 | b    |
+----+------+

往第10个表里写数据:

[root@yejr.me]> set auto_increment_offset = 10; ---注意这里
[root@yejr.me]> set auto_increment_increment = 10;
[root@yejr.me]> insert into m10(id,name) values(10,'j');
[root@yejr.me]> insert into m10(id,name) values(0,'j');
...
[root@yejr.me]> select * from m10;
+----+------+
| id | name |
+----+------+
| 10 | c    |
| 20 | c    |
| 30 | c    |
+----+------+

这样就是符合预期的了。

最后小结一下。

  1. 一般只会在双主或环形复制环境下才会特意设置自增偏移量和步幅。
  2. 若要在同一个实例内不同表之间也实现不同偏移量的自增,就需要在针对每个表的那个写入会话中单独调整自增选项设置。

虽然这个案例并不常见,不过也有点意思,就拿出来说说了,就酱。

enjoy mysql。

【全文完】


由叶老师主讲的知数堂「MySQL优化课」第17期已发车,课程从第15期就升级成MySQL 8.0版本了,现在上车刚刚好,扫码开启MySQL 8.0的修行之旅吧。

另外,叶老师在腾讯课堂的短课程《MySQL性能优化》已开课,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度。

下面是报名小程序码

The MySQL 8.0.19 Maintenance Release is Generally Available

The MySQL Development team is very happy to announce that MySQL 8.0.19 is now available for download at dev.mysql.com. In addition to bug fixes there are a few new features added in this release. Please download 8.0.19 from dev.mysql.com or from the MySQL Yum, APT, or SUSE repositories. The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.19 Release Notes. Here are the highlights. Enjoy!

InnoDB ReplicaSet

Following InnoDB Cluster as our first, fully integrated MySQL HA solution based on Group Replication, InnoDB ReplicaSet delivers another complete solution, this time based on MySQL Replication. The basic idea for InnoDB ReplicaSet is to do the same for classic MySQL Replication as InnoDB Cluster did for Group Replication. We take a strong technology that is very powerful but can be complex, and provide an easy-to-use AdminAPI for it in the MySQL Shell.

In just a few easy to use Shell commands, a MySQL Replication database architecture can be configured from scratch, including data provisioning using CLONE, setting up replication and performing manual switchover/failover. MySQL Router understands the topology and will automatically load balance/redirect traffic.

Router

Add support for ReplicaSet in Router (WL#13188) This work by Andrzej Religa extends the MySQL Router to understand the concept of InnoDB ReplicaSets. This means loading and caching the topology information from the metadata schema as well as regularly updating the status information of the setup in order to perform the correct routing operations.

Handle the metadata upgrade in the Router (WL#13417) This work by Andrzej Religa extends the Router to understand and properly handle the “0.0.0” version and the “metadata_upgrade_in_progress” lock.

New bootstrap option (–account) to allow reuse of an existing account (WL#13177) This work by Pawel Mroszczyk adds a startup option to specify the account the router should use when talking to the server. The router needs a server account to be able to talk to the InnoDB Cluster. Until now, this account is always automatically created during bootstrapping.

SQL

Implement ALTER TABLE … DROP/ALTER CONSTRAINT syntax (WL#12798) This work by Praveenkumar Hulakund adds the SQL DROP CONSTRAINT and ALTER CONSTRAINT clauses to the ALTER TABLE statement. This work supplements WL#929 where the SQL CHECK CONSTRAINT clauses were added to the CREATE TABLE and ALTER TABLE statements and fixes Bug#3742.

Table with JSON Schema validation constraint should return error for concrete row (WL#13195) This work by Praveen Hulakund adds an SQL condition in the Diagnostics area with an error code pinpointing the schema violation in the form of “The JSON document location X failed requirement Y at JSON Schema location Z”. See also WL#13196 below.

TIMESTAMP/DATETIME values can include timezone details (WL#10828) This work by Martin Hansson makes it possible to include time zone details along with the TIMESTAMP value. Without explicit time zone information or offset, there might exist date/times which in some time zones that are not well defined, like when Daylight Saving Time changes the time backwards. This is a feature request from Booking.com (Bug#83852).

Support LIMIT in recursive common table expression (WL#12534) This work by Guilhem Bichot adds support for LIMIT in CTEs. The LIMIT is on the total number of rows in the CTE, not on the number of rows that each iteration may produce. See also Bug#92857.

Implement table value constructors: VALUES (WL#10358) This work by Thomas Aven implements standard SQL syntax for table value constructors. But due to a conflict with the VALUES() function, a MySQL non-standard feature, we decided to use the SQL standard verbose form of the table value constructors: VALUES ROW(1, 2),… See also Bug#77639.

Referencing old/new row in INSERT … ON DUPLICATE KEY UPDATE (WL#6312) This work by Thomas Aven extends the INSERT … VALUES/SET … ON DUPLICATE KEY UPDATE syntax to make it possible to declare an alias for the new row and columns in that row, and refer to those aliases in the UPDATE expression. The intention with this new feature is to be able to replace VALUES() clauses with row and column alias names.

Optimizer

Enhance group-by loose index scan (WL#13066) This work by Sergey Gluhov improves the loose index scan (LIS) used for GROUP BY queries by lifting the current limitation on the number of infix ranges (range on non-grouping column) that can be present in the query. This improvement will make the optimizer choose LIS for more queries. This feature is based on a contribution by Facebook (Bug#90951).

BKA in iterator executor (WL#13002) This work by Steinar H. Gunderson implements BKA (Batch Key Access) in the iterator executor. For now, only inner joins are supported, other join types will be added later.

Information Schema

INFORMATION_SCHEMA views for roles (WL#10895) This work by Gopal Shankar implements SQL Standard Information Schema views for SQL Roles. The APPLICABLE_ROLES view shows the roles that are applicable for the current user. The ADMINISTRABLE_ROLE_AUTHORIZATIONS view shows the roles that are applicable for the current user, which can also be granted to other users. The ENABLED_ROLES view shows the roles that are enabled for the current user. The ROLE_TABLE_GRANTS view shows the table grants for the roles that are enabled for the current user. The ROLE_COLUMN_GRANTS view shows the column grants for the roles that are enabled for the current user. And finally, the ROLE_ROUTINE_GRANTS view shows the routine grants for the roles that are enabled for the current user.

Security

FAILED_LOGIN_ATTEMPTS/PASSWORD_LOCK_TIME counters per user (WL#13515) This work by Georgi Kodinov enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. See CREATE USER Password-Management Options.

Store secret data in keyring (WL#12859) This work by Harin Vadodaria enhances the existing keyring plugins to support a new type of data called SECRET. This allows users to store, retrieve, and manage any opaque data in the keyring (max 16K). The keyring backend will not interpret the secret but simply treat it as a BLOB. This functionality will typically be used to store secret data such as passwords and certificates.

InnoDB

InnoDB: Support for sampling table data for generating histograms (WL#8777) This work by Darshan M N implements the handler API defined in WL#9127 by sampling data pages from the clustered index and providing the records stored in these pages.

Innodb: Case insensitive names for partition tables (WL#13352) This work by Debarun Banerjee solves user issues related to alphabetical case differences in partition name and separators. The solution is to always handle partition name, partition separator (#p#), sub-partition name and sub-partition separator (#sp#) in a case insensitive manner so that the difference in case doesn’t matter for file names on disk and metadata stored in the data dictionary. This includes handling of partitioned tablespace file names during import.

Replication

Configure replication applier to require row-based replication (WL#12968) This work by Pedro Gomes ensures that a replication channel only accepts row-based replication. This allows restricting the type of instruction executed on the slave, and as a side effect, limits the number of privileges a user needs when associated to the replication applier.

MTS: slave-preserve-commit-order when log-slave-updates/binlog is disabled (WL#7846) This work by Hemant Dangi implements applier commit order, even when the worker threads are not logging the changes to the replicas binary log (–log-slave-updates=off). The main motivation for this is to be able to turn on parallel replication on a replica that has no binary log enabled without having different commit histories between the master and the slave. In other words, the transaction commit history, as observed in the replication stream, is preserved because the applier executes incoming transactions in parallel but commits them in the order that they appear in the stream.

Support for TLS 1.3 in Asynchronous Replication (WL#13392) This work by Nuno Carvalho supplements previous work to support TLS 1.3 in the connections between MySQL clients and servers (WL#12361) by adding support for TLS 1.3 to Replication as well. This work implements the MASTER_TLS_CIPHERSUITES option on CHANGE MASTER command, the group_replication_recovery_tls_version plugin option and the group_replication_recovery_tls_ciphersuites plugin option.

X Protocol

Connection Compression (WL#9252) This work by Lukasz Kotula implements client-server data compression over the X Protocol. The compression algorithm is negotiable based on server capabilities.

Zstd compression (WL#13442) This work by Grzegorz Szwarc implements zstd compression in the X Protocol. WL#9252 added deflate and lz4 compression support to xprotocol. The X Protocol now supports three compression algorithms: deflate, lz4, and zstd. The zstd algorithm has been found to perform better than deflate and has a compression ratio much better than lz4.

Add schema validation to the create collection method (WL#12965) This work by Tomasz Stepniak implements a document schema validator in the X Plugin. From the DevAPI perspective, the user calls “var coll = schema.createCollection(“mycollection”, {validator: validationDoc})”. The createCollection() is implemented in the X Plugin and this implementation is extended with a call to the JSON_SCHEMA_VALID(, ) function introduced by WL#11999.

Collection with json-schema validation must return error for concrete row (WL#13196) This work by Tomasz Stepniak extends WL#12965 by identifying the exact location where a document violates a schema constraint. See WL#13195 above.

MTR testsuite

Move testcases in binlog, sys_vars, and funcs_1 suites that needs MyISAM to a separate .test file (WL#13410 and WL#13241) This work by Pooja Lamba moves the sections that need MyISAM to a separate .test file. This allows the MTR test suite to run on a server that is built without the MyISAM engine.

Other

Make mysql command line tool’s –binary-as-hex be on by default for interactive terminals (WL#13038) This work by Georgi Kodinov enables the option –binary-as-hex by default for command line tools running in “interactive mode”. The current default of –binary-as-hex (OFF) is kept for non-interactive runs. Historically, the MySQL command line tools have asked the server to convert all the data it receives to text so it can display them. But for some data (e.g. GEOMETRY data types) the conversion spawns binary strings that might contain non-printable symbols which can garble certain terminals. This is avoided by printing the hex dump instead of text.

Specifying Character Sets in the UDF API (WL#12370) This work by Rahul Sisondia extends the User Defined Functions (UDF) API to let the UDF creator specify which character sets to use in the arguments and the return value. Previously the UDF API simply assumed ASCII for both string arguments and the return value.

Sys Schema, Replace Stored Functions With Native Functions (WL#13439) This work by Chris Powers replaces stored functions with performance schema native functions in SYS Schema stored programs. The performance schema native functions are: format_bytes(), format_pico_time(), ps_current_thread_id(), and ps_thread_id(connection_id). The original SYS Schema functions are deprecated and will be removed in some future release.

Enable the “system” mysql command line command for windows (WL#13391) This work by Georgi Kodinov enables the system command in the MySQL command line tool on Windows. The system command executes its arguments as an OS command and displays the result. It has been working for Linux in the past and since the MSVC CRT now supports all the necessary APIs it is time to enable it for Windows as well. This fixes Bug#58680.

Component service to add “admin” session (WL#13378) This work by Georgi Kodinov adds a new component service to allow its caller to create a special kind of SQL session that is not a subject to the max_connection limit. This is done to ensure that internal mechanisms are not limited by max_connections. For example, group replication cannot operate when the server hits max_connections limit (see Bug#94987).

Add more information to Duplicate Key Error (WL#12589) This work by Jens Even Blomsøy extends the error information given in cases of DUP_ENTRY_KEY to include the table name along with the key name and key value in the error message. This work is based on a contribution from Facebook (Bug#92530). See also Bug#47207.

Use signal SIGUSR1 to flush logs (WL#13689) This work by Jens Even Blomsøy redefines signal SIGUSR1 to be a light version of SIGHUP. The SIGUSR1 will cause the server to flush error log, general log and slow query log, but not send the MySQL status report.

Split the errmgs-utf8.txt file (WL#13423) This work by Tatjana Nurnberg splits the errmgs-utf8.txt file into one file for messages to clients and another for messages to the error log (messages_to_clients.txt and messages_to_error_log.txt). This is a code refactoring helping developers to distinguish between client messages and error log messages.

Deprecation and Removal

Deprecate display width and UNSIGNED for the YEAR datatype (WL#13537) This work by Jon Olav Hauglid deprecates the syntax YEAR(4). Use the equivalent YEAR instead. This work also deprecates the use of UNSIGNED with YEAR. UNSIGNED is not documented to work with YEAR and has no effect.

Remove integer display width from SHOW CREATE output (WL#13528) This work by Jon Olav Hauglid changes SHOW CREATE to not output integer display width unless ZEROFILL is also used. Without ZEROFILL, integer display width has no effect. This work is a logical consequence of deprecating the display width attribute for integer types (WL#13127).

XProtocol: Removal of the deprecated namespace of Mysqlx.Sql.StmtExecute message (WL#13057) This work by Grzegorz Szwarc removes the namespace xplugin from the list of supported namespaces. Thus, the xplugin namespace becomes an unknown identifier and any administrative commands sent within this namespace will not be handled. If the user continues to use this namespace, they will be notified by a standard error message. The xplugin namespace in the Mysqlx.Sql.StmtExecute message has been deprecated since 5.7.14.

Thank you for using MySQL!

跑步七年之痒

跑步七年回顾。

1. 跑步经历

我是2012年8月从北京回到福州工作的。

刚到福州没多久,就和同事龚千万约好去公司旁边的公园跑步。当时的出发点很简单,因为马上快要当爹了,就是为了能有个能抗住半夜起来泡奶的好身体,同时也是为了持续改善神经衰弱的毛病。

为了跑步大业,我还特地去买了一双跑鞋,好像是安踏还是阿迪之类的,反正是很不专业的那种,穿着并不是很舒服。刚开始起步,我跑个一公里都有点困难,经过一两个月的练习,慢慢能跑到三公里左右了。也慢慢发展了几位一起跑步健身的同事。

就这么没什么系统性的跑了一阵后,正好同事和勇同学从北京也来到福州工作,他带着我们几个人更加科学的跑,我也跟着买了个佳明620运动手表,跑步的水平也得到慢慢提升。

第一次参加马拉松(半程)有点机缘巧合,当时正好赶上武夷山马拉松报名快截止了,心里还有点忐忑,不太敢报名,最后还是咬咬牙报名了,心想也跑步有阵子了,试着参加一次看看成绩如何。几个小伙伴一起报名,最后跑下来成绩也都还算符合预期。此后,就陆陆续续参加几场省内的马拉松,有宁德霞浦的三沙山地马拉松,三明泰宁大金湖马拉松,厦门马拉松,以及福州本地的赛事,还有一次凑巧去台北,也顺便跑了一场,这时候还都只是敢报半程的,还跑过一次15公里的越野马拉松,那个时期刚接触马拉松,还挺热衷参加的,也正是国内马拉松赛事刚刚兴起的时候。

第一次跑全程马拉松是2016年在厦门,当时检录还没有很严格,我用了别人的名额去跑的(此处特别强调,不要顶替别人名额去跑马,容易出事,且要背锅),那次成绩也还算理想。此后也参加过武夷山、北京的全马,但成绩一次比一次差。

时间进入2018年,我开始重视提高腰背腿的核心力量训练(使得在长距离跑步后期还能有体力),同时可以提高步频并调低步幅(有点小步快跑的意思,可以降低触底时长,减少能量消耗)。此后成绩开始逐步提升了。再到2019下半年,实现2个小时内的半马成绩小突破。再到2020年初的厦门马拉松,实现了4小时17分的PB成绩,进步还是挺明显的。

特别提一下2020年的厦马,我在前面的38公里,除了稍微停顿补给外,几乎都在跑动,没有什么特别的疲劳和不适感(特别备了髌骨带也都还没用上)。在38-41公里期间,差不多是跑800米就改快走几步的节奏,到了最后1.5公里时,看到430的兔子(此兔子至少比我早出发10分钟)追上来了,就又咬着牙持续跑完没再改快走,直到终点。

2. 关于跑步损伤

事实上,我在几年的跑步生涯中也是有受过伤的。说几次比较严重的。

第一次是参加厦门半马,人太多了,跑不开,于是左冲右突式前进,最后造成右脚踝受伤,大概休息了半年才好。

第二次是抱娃时用力过猛,腰扭伤了,趴在床上一整天,再贴虎皮膏药以及撒隆巴斯之类的,两三天后才基本恢复。

第三次是跳绳导致膝盖受伤,可能是因为没热身到位,或者跳绳的姿势不对,也休了快两个月才恢复。

幸好都不是什么太严重的伤痛,没影响到跑步。在这里要给各位认真的建议,如果遇到运动损伤,一定要确认休息到位了,才能继续运动,当然最好是去找专业的医生检查下更放心。

3. 关于跑步配套

关注公众号「老叶茶馆」(imysql_wx),发送 “跑步装备” 四个字到后台,即可得到推送,我这里不多赘述。

4. 关于马拉松

很多人可能不理解,跑完马拉松,可能整个人都快虚脱了,要死要活的有什么意思呢。就说说我个人的体验吧。

上面提到了,一开始跑步是为了有健康的体魄,第一次参加半马是为了检验跑步成果。后来的几次参赛,则是对一种新事物刚迷上时的热衷心态。到最近几次参赛,则是为了磨练意志力了。试想下,那么艰难(当然对有些人来说是小菜一碟)的全马都能咬牙跑完,还有什么事是做不来的呢。我现在工作的MacBook Pro就是完成第一场全马的到的自我奖励。

人生亦是一场马拉松,没挂之前,都得努力奔跑,一停下就会被身后的人超越,甚至被迫登上收容车。

最后,广告是不能少滴。

我的新课程《MySQL性能优化》已经在腾讯课堂发布,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度。下面是报名小程序码,厚着脸皮请求大家推荐给需要的小伙伴们。

下面是本课程内容目录

更多关于本课程内容还可以点击文末“阅读原文”查看。

厦马归来,我做了个任性的决定

厦马前我发了个愿。

今天,我参加了厦门马拉松,老天爷给面子,在我跑完之前还没出太阳,虽然湿度略高,不过对我这种长期生活在福建的人而言,这都不是事。 2019年我平时的拉练还算努力,所以这次前面的38公里整体状态还不错。过了38公里后,才有点疲了,偶尔改成快走。到了最后一公里还能坚持慢跑到底,也是挺佩服自己的。 这是本次的服装和奖牌

这是跑前状态

这是跑后状态,感觉还好,还没到累死累活的那种

这是成绩详情

说完厦马,再说说另一个事。在赛前,我在内部说了下,如果这次能PB(跑出个人最好成绩,Personal Best),我主理的MySQL优化班就搞优惠促销活动。最近的整体环境不太好,大家手头的钱也比较紧张,所以才有这个想法。这次的优惠促销活动计划把MySQL优化班单班价格从4600元调整到4200元,如果报双班则还是8000元不变。更具体的规则请扫码详询知数堂助教妹子。希望这次的优惠活动能帮到那些有心学习MySQL优化知识的同学,也感谢大家对我们一贯的支持。

好了,就这些。

最后,放个俺家茶叶小店的二维码,欢迎下单选购便宜不贵的口粮茶。

good-book-by-woqutech-20191021

《千金良方——MySQL性能优化金字塔法则》

作者:李春、罗小波、董红禹

简介:

《千金良方——MySQL性能优化金字塔法则》一共分为3篇:基础篇、案例篇和工具篇。“基础篇”从理论基础和基本原理层面介绍了MySQL的安装与配置、升级和体系结构,information_schema、sys_schema、performance_schema和mysql_schema,MySQL复制,MySQL事务,SQL语句优化及架构设计基础知识。“案例篇”从硬件和系统、MySQL架构等方面给出了性能优化的十几个案例。“工具篇”介绍了在MySQL性能优化过程中需要用到的各种工具。

《千金良方——MySQL性能优化金字塔法则》一书已经在10月18号正式预售,一经上线小编朋友圈立即被知数堂小伙伴们刷屏,叶老板也对此书十分推崇,这是为什么呢?

本书还有4个附录,知数堂为首发平台,将作为本文福利赠送给大家,文末提供下载方式。

叶老师推荐序
本书由知数堂的战略合作伙伴,杭州沃趣公司的几位业界大牛李春、罗小波、董红禹联合著作。老叶强烈推荐!
本书作者李春是阿里较为早期的DBA之一,罗小波和董红禹则都是知数堂的优秀校友,有一次偶然知道了罗小波经历,更是大为赞叹,这么努力的人理应获得如此成果。
第一次注意到罗小波是他的投稿文章“MySQL排序内部原理探秘”,真的是从头到脚,从上到下全方位解读了MySQL内部排序的方方面面。再后来,又关注到罗小波推出PFS和sys schema系列连载文章,更是对其静心深入学习能力表示钦佩,整个系列文章甚是详实细致到位。董红禹的功底也非常深厚,他对其他数据库也有较多了解,二位同学也曾多次被知数堂邀请回来做公开课分享。
有一次我突然感叹沃趣能培养出来不少好工程师,其人才体系肯定有很多可学之处,于是也邀请了李春来知数堂做公开课分享,就是希望能把他们的人才培养机制分享给业界。
拿到书稿我快速浏览了案例篇的全部内容,发现几乎篇篇都是精华,里面涉及到了相当多的经典案例解析,如果能从这些案例中吸收解决问题的思路,相信以后如果遇到其他问题,基本上都没什么困难的了。
本书既有涉及体系结构、PFS、I_S、统计信息、复制、锁、InnoDB等众多基础知识的铺垫(其实并不基础),后面又有众多案例详解。内容丰富详实,是难得一见的MySQL优化参考书,建议每位DBA以及从事MySQL相关应用开发的同学都读一读。
最后,说个段子。我曾经开玩笑地说,大家以后要买书的话,记得先看有没有我给写的推荐序,如果有的话,那就放心买,如果没有的话,就要谨慎点了。本书我是推荐的,所以可以放心大胆买,嘿嘿。

附录内容
由于本书内容量实在太大了(三大篇,51章),所以我受委托将本书的附录内容在知数堂平台首发,大家可以先围观本书几位作者的倾心之作。附录内容包含:
附录A 线程状态信息详解
附录B show engine innodb status详解

附录C MySQL常用配置变量和状态变量详解

附录D explain 执行计划详解

分割线,以下是福利内容(还有免费赠书活动)

如何获取附录
老叶茶馆公众号回复“附录”两个关键字,可获取四个附录的下载地址

如何购买

本书现仅在京东一个平台7.9折预售,购买地址:https://item.jd.com/12728070.html

重磅预告(免费赠书)

知数堂将推出免费赠书活动回馈一直支持我们的同学。想参加的同学,可以关注下明天的活动。 小编我只能告诉你跟这个公开课《资深DBA带你学习SQL开发和优化》有关,先报个名占个前排位置。

2019.10.24(周四) 20:30

松华老师公开课

《资深DBA带你学习SQL开发和优化》

在腾讯课堂准时发车

欢迎大家参加

扫码直达报名地址

分割线箭头动态

扫码加入MySQL技术Q群

(群号:579036588)

好书推荐《千金良方:MySQL性能优化金字塔法则》| By 叶金荣

知数堂的战略合作伙伴,杭州沃趣公司的几位业界大牛李春、罗小波、董红禹联合著书《千金良方:MySQL性能优化金字塔法则》。本书机缘8月份交付印刷,正在最后的校稿过程中。该书的案例篇内容我已经全部看过,的确是本好书。下面是我给写的推荐序:

本书作者李春是阿里较为早期的DBA之一,罗小波和董红禹则都是知数堂的优秀校友,有一次偶然知道了罗小波经历,更是大为赞叹,这么努力的人理应获得如此成果。

第一次注意到罗小波是他的投稿文章”MySQL排序内部原理探秘“,真的是从头到脚,从上到下全方位解读了MySQL内部排序的方方面面。再后来,又关注到罗小波推出PFS和sys schema系列连载文章,更是对其静心深入学习能力表示钦佩,整个系列文章甚是详实细致到位。董红禹的功底也非常深厚,他对其他数据库也有较多了解,二位同学也曾多次被知数堂邀请回来做公开课分享。

有一次我突然感叹沃趣能培养出来不少好工程师,其人才体系肯定有很多可学之处,于是也邀请了李春来知数堂做公开课分享,就是希望能把他们的人才培养机制分享给业界。

拿到书稿我快速浏览了案例篇的全部内容,发现几乎篇篇都是精华,里面涉及到了相当多的经典案例解析,如果能从这些案例中吸收解决问题的思路,相信以后如果遇到其他问题,基本上都没什么困难的了。

本书既有涉及体系结构、PFS、I_S、统计信息、复制、锁、InnoDB等众多基础知识的铺垫(其实并不基础),后面又有众多案例详解。内容丰富详实,是难得一见的MySQL优化参考书,建议每位DBA以及从事MySQL相关应用开发的同学都读一读。

最后,说个段子。我曾经开玩笑地说,大家以后要买书的话,记得先看有没有我给写的推荐序,如果有的话,那就放心买,如果没有的话,就要谨慎点了。本书我是推荐的,所以可以放心大胆买,嘿嘿。

由于本书内容量是在太大了(三大篇,51章),所以我受委托将本书的附录内容在知数堂平台首发,大家可以先围观本书几位作者的倾心之作。下面是4篇附录文档的下载地址。

多属性、多分类MySQL模式设计

0、导读

这是来自B乎的一个问答。 当数据同时具备多个属性/分类时,改如何设计表结构和查询?

1、需求描述

我偶尔也会逛逛B乎,看到一些感兴趣的话题也会回复下。 有一次,看到这样的一个话题:

链接:https://www.zhihu.com/question/337083976/answer/767075575

[mysql] 当数据同时属于多个分类时,该怎么查询?

分类cate字段为[1,2,3,4,5] ,假如要查询满足分类’2’和’5′ 的数据该怎么查询? 我尝试过用 cate like ‘%2%’ AND cate like ‘%5%’去查。 想问有没有更好的办法,我这样写数据少了还好,多了根本没法查,效率太低了。

恰好我以前做过类似的业务需求设计,所以就回复了这个问题。

2、模式设计思路

这个需求可以有几种不同的解决思路,我们分别展开说一下。

2.1 用bit数据类型

大概思路如下: 1、物品属性列c1 用bit数据类型 来表示,也就是只有0、1两种取值 2、当物品属性具备某个分类属性时,其值为1,否则为0 3、假如共有5个分类,当物品拥有全部分类属性时,则其值为11111,若其不具备第3个分类属性,则其值为11011,在数据库中转成十进制存储 4、上述两种情况下,将二进制转换成十进制表示,即分别是31和27

[root@yejr.me] [zhishutang]> select conv(11111, 2, 10), conv(11011, 2, 10);
+--------------------+--------------------+
| conv(11111, 2, 10) | conv(11011, 2, 10) |
+--------------------+--------------------+
| 31                 | 27                 |
+--------------------+--------------------+

5、然后,只需要对该列用十进制值进行查询比对就行 6、现在如果想判断是否同时具备2、5两个分类属性时,其二进制表示为01001,转成十进制为9,只需要用条件 where c1=9 即可

我们来演示一下:

[root@yejr.me] [zhishutang]>show create table t_bit\G
*************************** 1. row ***************************
       Table: t_bit
Create Table: CREATE TABLE `t_bit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`)
) ENGINE=InnoDB;

insert into t_bit select 0,conv(00001, 2, 10), 'item1';
insert into t_bit select 0,conv(00011, 2, 10), 'item2';
insert into t_bit select 0,conv(00111, 2, 10), 'item3';
insert into t_bit select 0,conv(01111, 2, 10), 'item4';
insert into t_bit select 0,conv(11111, 2, 10), 'item5';
insert into t_bit select 0,conv(10111, 2, 10), 'item6';
insert into t_bit select 0,conv(11011, 2, 10), 'item7';
insert into t_bit select 0,conv(11101, 2, 10), 'item8';
insert into t_bit select 0,conv(11110, 2, 10), 'item9';

[root@yejr.me] [zhishutang]>select * from t_bit;
+----+----+-------+
| id | c1 | c2    |
+----+----+-------+
|  1 |  1 | item1 |
|  2 |  3 | item2 |
|  3 |  7 | item3 |
|  4 | 15 | item4 |
|  5 | 31 | item5 |
|  6 | 23 | item6 |
|  7 | 27 | item7 |
|  8 | 29 | item8 |
|  9 | 30 | item9 |
+----+----+-------+

[root@yejr.me] [zhishutang]>select * from t_bit where c1 = conv(11011,2,10);
+----+----+-------+
| id | c1 | c2    |
+----+----+-------+
|  7 | 27 | item7 |
+----+----+-------+

# 同时我们也注意到这个SQL是可以正常使用索引的
[root@yejr.me] [zhishutang]>desc select * from t_bit where c1 = conv(11011,2,10)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_bit
   partitions: NULL
         type: ref
possible_keys: c1
          key: c1
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

下面两种方法是B乎网友的回复,大家也可以参考下。 1. 用JSON数据类型,然后利用JSON_CONTAINS()函数进行查询 2. 用SET数据类型,然后利用FIND_IN_SET()函数进行查询

不过,JSON和SET这两种数据类型都不方便加索引以及利用索引扫描,即便是用了5.7的JSON+虚拟列功能,索引效率也是比较低的。而支持JSON数据类型 多值索引(multi-valued Indexes) 也要8.0.17 以上版本才支持。

3、总结

这样看来,总的来说,用二进制转十进制方式来解决本案例需求更为高效,也欢迎提出更多方案思路。

延伸阅读

Enjoy MySQL :)


最后,欢迎扫码订阅《乱弹MySQL》专栏,快人一步获取我最新的MySQL技术分享
enter image description here

部署MySQL延迟从库的几个好处

导读

MySQL延迟从库的好处多多,你了解吗

MySQL的主从复制(replication)关系,不太严谨的叫法是“同步”或者“主从同步”。实际上在早期,MySQL的主从并不能实现真正的“同步”(sync),而是“异步”的(async)。

MySQL主从复制它可以有多种模式,最经典的也是最早出现的异步复制(async replication),从5.5版本开始有了半同步复制(semi-sync replication),到了5.7又有了增强半同步。本文要讨论的延迟从库,也是在5.6之后才有的功能,在这之前需要用percona pt-slave-delay 工具来变相实现。

另外,从5.6版本开始增加了并行复制,不过这时还是基于schema的并行模式(slave-parallel-type=DATABASE),效率非常差,意义不大。到了5.7,才实现了真正的并行复制(slave-parallel-type=LOGICAL_CLOCK),复制效率提升很多;还有新增了多源复制,很方便的就能实现多主一从的架构。

了解完MySQL复制的简史,我们切入主题。

MySQL延迟从库的好处主要有几点:
1、误删除时,能更快恢复数据。 有时候手抖了,把线上数据给误删除了,或者误删除库、表、其他对象,或不加WHERE条件的更新、删除,都可以让延迟从库在误操作前的时间点停下,然后进行恢复。

2、把延迟从库作为专用的备份节点。虽然有一定的延迟,但并不影响利用该节点作为备份角色,也不影响生产节点数据库库。

3、还可以把延迟从库当做一些问题、案例研究的对象。个别时候,可能有些binlog event在普通从库上会有问题(例如早期版本中无主键会导致从库更新非常慢的经典问题),这时就有时间在延迟从库上慢慢琢磨研究了。

启用延迟从库的方法也挺简单的,下面是在MySQL 8.0的做法:

#直接用 CHANGE MASTER TO 设置,后面的N单位是秒数
CHANGE MASTER TO MASTER_DELAY = N

当发生误操作需要让延迟从库在某个位置上停下来时,用下面的命令:

START SLAVE
  UNTIL {
    #1、直到指定的GTID位置停下
    {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set

    #2、直到指定的binlog位置停下
    |   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

    #3、直到指定的relay log位置停下
    |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

    #4、直到slave上多个并行线程之前没有延迟差距了就停下
    #因为多线程复制,不同线程的复制进度不一样,因此有差距
    |   SQL_AFTER_MTS_GAPS  }

P.S,从5.7起,修改 MASTER_DELAY 选项可以在线立即生效,而无需重启 slave 线程。

至于具体 MASTER_DELAY 设置多少合适,要估算如果发生误操作时,DBA平均能到现场的时间,一般建议1小时左右。

延伸阅读

16.3.10 Delayed Replication,https://dev.mysql.com/doc/refman/8.0/en/replication-delayed.html

MySQL复制 | SLAVE复制为什么停了

MySQL复制 | SLAVE延迟很大怎么办

MySQL复制 | 精确监控SLAVE延迟

MySQL复制 | 列类型被自动修改导致复制失败