码厩开发者笔记

#25 MySQL 相关的命令汇总

通过 apt 和 apt-file 列出 mysql 提供的所有可执行文件。

#24 MySQL 版本历史

历史

• 在更早的 20 年间，MySQL 的前身 UNIREG 被 Monty 开发
• 1994 年瑞典公司 TcX 成立，Monty 是三位创始人之一。次年，这家公司改名为 MySQL AB。
  TcX 将 UNIREG 改造为一款支持 SQL 的关系型数据库。
• 1995 年，MySQL 3.11 发布。
• 1999 年，MySQL 3.23 集成 BDB，开始了可插拔存储引擎架构。
• 2000 年，ISAM 改良成 MyISAM，同时 MySQL 基于 GPL 协议开源。
• 2001 年，MySQL AB 和芬兰公司 Infobase Oy 合作，为 MySQL 3.22 带来了 InnoDB 引擎。
  重点是事务和行级锁。
  注意：InnoDB 是 GPL / 专有协议双授权。
• 2005 年，Innobase Oy 被甲骨文收购。同年, MySQL 5.0 发布。
  注意：我们现在数据库系统课的很多概念，触发器，存储过程，视图，游标等都是 5.0 开始支持的。
• 2006 年，Oracle 曾经试图收购 MySQL 但是遭到拒绝。
• 2008-02-26 MySQL AB 被 SUN 10 亿美元收购。
• 2008-11-27 MySQL 5.1，开始支持分区，和主从同步时的行复制（RBR）。
• 2009-04-20 SUN 被甲骨文 74 亿美元收购，收购直到 2010-01-27 才完成。
  最重要的存储引擎 InnoDB 终于和 MySQL 版权归于一处了。
• 2010-12-03 MySQL 5.5，InnoDB 成为默认引擎（5.5.5 开始）。
• innodb 改进
• utf8mb4
• metadata lock
• 2013-02-05 MySQL 5.6
• 2015-10-21 MySQL 5.7
• 2018-04-19 MySQL 8.0
• 最新版本是 2020-10-19 发布的 8.0.22
  2021-10-19 发布的 8.0.27 (Update @ 2021-11-16)

说明：

1. 5.6，5.7 应该是现在最常用的两个版本。
   8.0 还比较新，我还没有在线上业务中用过。但是有很多我感兴趣的特性。
2. 列举的特性可能是在不同子版本引入，开发中可能需要留意不同子版本的差异。
   就现在我理解的主流开发思想，对组件应该简单应用，就数据库而言，应该只用来做基础存储，不要涉及复杂功能，不要涉及所谓特性，最要用好几个大版本就在的功能，其他数据库系统都有的功能。
   我觉得有点过了。这个另说吧。

MySQL 5.6 新特性

• GTID复制。
• 无损复制。
• 延迟复制。
• 基于库级别的并行复制。
• mysqlbinlog 可远程备份 binlog。
• 对 TIME, DATETIME 和 TIMESTAMP 进行了重构，可支持微秒（6 位小数）。
• DATETIME 的空间需求也从之前的 8 个字节减少到 5 个字节。
• 可以有多个时间类型 DEFAULT current_timestamp() ON UPDATE current_timestamp()
  之前只能支持 1 个 TIMESTAMP 类型
• YEAR(2) 被抛弃，新建时会自动转换成 YEAR(4)
• Online DDL，部分 ALTER 操作不再阻塞 DML。
• 可传输表空间（transportable tablespaces）。
• 统计信息的持久化。避免主从之间或数据库重启后，同一个SQL的执行计划有差异。
• 全文索引。
• EXPLAIN 可用来查看 DELETE，INSERT，REPLACE，UPDATE 等 DML 操作的执行计划，在此之前，只支持SELECT操作。

• 分区表的增强

• 最大可用分区数增加至 8192

• 支持分区和非分区表之间的数据交换

• 操作时显式指定分区，比如：

  SELECT * FROM employees PARTITION (p0, p2)
  

• Undo Log 可保存在独立表空间中，因其是随机 IO，更适合放到 SSD 中。但仍然不支持空间的自动回收。

• 可 dump 和 load Buffer pool 的状态，避免数据库重启后需要较长的预热时间。
• InnoDB 优化，并默认使用 InnoDB 做存储引擎。
• NoSQL API：InnoDB Memcached plugin。
• InnoDB 内部的性能提升，包括拆分 kernel mutex，引入独立的刷新线程，可设置多个 purge 线程，减少大内存系统中的buffer_pool竞争。
• Redo Log 总大小的限制从之前的 4G 扩展至 512G（innodb_log_file_size）。
• 死锁算法改进，并记录到错误日志中。
• 为了避免大 buffer_pool 的实例重启服务时过长的warmup时间，你可以在重启后立即加载缓存页面。MySQL可以在关闭时导出完全数据文件，检阅此文件找出重启时需要加载的pages。你可以在任何手工导入导出buffer_pool，比如在性能测试时或者在执行复杂的OLAP查询后。
• 优化器性能提升，引入了ICP，MRR，BKA 等特性，针对子查询进行了优化。
• 若干安全性提升（包括密码自动过期 (mysql.user 的 password_expired)）

复制和日志(replication)

1)支持延时复制，MySQL现在支持延迟复制，默认是0秒。使用CHANGE MASTER TO参数 MASTER_DELAY 来设置延迟。可以让slave跟master之间控制一个时间间隔，方便特殊情况下的数据恢复。(以前是使用第三方工具可以做到) 2)行级复制功能加强，可以降低磁盘、内存、网络等资源开销(只记录能确定行记录的字段即可)。3)现在支持多线程复制。如果开启，sql线程作为协调者协调多个工作线程，数量取决于slave_parallel_workers。现在多线程复制以单库为基础，特定库的更新的相对顺序和主库一样。不过，没有必要协调不同库之间的事务。事务可以被分布到每个库，意味着一个复制从库的工作线程可以顺序执行事务而不必等待其它库的更新完成。4)支持以全局统一事务ID(GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。5)支持启用GTID，对运维人员来说应该是一件令人高兴的事情，在配置主从复制，传统的方式里，你需要找到binlog和POS点，然后change master to指向，而不是很有经验的运维，往往会将其找错，造成主从同步复制报错，在mysql5.6里，如果使用了GTIDs，启动一个新的复制从库或切换到一个新的主库，就不必依赖log文件或者pos位。需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。
6)binlog的读写现在是崩溃安全的，因为只有完整的事件(或者事务)才会被记录和读取。默认会记录事件的大小以及事件本身，使用大小来验证事件被正确记录。你也可以使用参数binlog_checksum设置使用crc32记录事件的校验值。使用参数master_verify_checksum可以让服务读取校验值。slave-sql-verify-checksum参数使从库读relay日志的时候读取校验值。

MySQL支持在表中保存主库连接信息了。使用参数–master-info-repository和 –relay-log-info-repository来设置。设置 –master-info-repository为表，会记录连接信息到slave_master_info表。设置–relay-log-info-repository为表，会记录relay log信息到slave_relay_log_info表。这几个表都是自动建立在mysql系统库。

增强Performance Schema数据库(Mysql performance schema)

1)记录表的输入与输出，操作包括行级访问表和临时表，如insert,upate,delete. 2)表的事件过滤，以库或者表名为基础。3)线程的事件过滤，更多关于线程的信息被搜集4)表和索引I/O以及表锁的统计表。5)记录命令以及命令的阶段。

不允许在存储过程中或者函数参数中或者存储程序本地变量中使用default来指定(如：SET var_name = DEFAULT命令)，但可以在指定系统变量时使用default。

MySQL 5.7 新特性

1. 引入对 JSON 的支持，JSON 类型和相关函数。

组复制
InnoDB Cluster
多源复制
增强半同步（AFTER_SYNC）
基于WRITESET的并行复制。
在线开启GTID复制。
在线设置复制过滤规则。
在线修改Buffer pool的大小。
在同一长度编码字节内，修改VARCHAR的大小只需修改表的元数据，无需创建临时表。
可设置NUMA架构的内存分配策略（innodb_numa_interleave）。
透明页压缩（Transparent Page Compression）。
UNDO表空间的自动回收。
查询优化器的重构和增强。
可查看当前正在执行的SQL的执行计划（EXPLAIN FOR CONNECTION）。
引入了查询改写插件（Query Rewrite Plugin），可在服务端对查询进行改写。
EXPLAIN FORMAT=JSON会显示成本信息，这样可直观的比较两种执行计划的优劣。
引入了虚拟列，类似于Oracle中的函数索引。
新实例不再默认创建test数据库及匿名用户。
引入ALTER USER命令，可用来修改用户密码，密码的过期策略，及锁定用户等。
mysql.user表中存储密码的字段从password修改为authentication_string。
表空间加密。
优化了Performance Schema，其内存使用减少。
Performance Schema引入了众多instrumentation。常用的有Memory usage instrumentation，可用来查看MySQL的内存使用情况，Metadata Locking Instrumentation，可用来查看MDL的持有情况，Stage Progress instrumentation，可用来查看Online DDL的进度。
同一触发事件（INSERT，DELETE，UPDATE），同一触发时间（BEFORE，AFTER），允许创建多个触发器。在此之前，只允许创建一个触发器。
InnoDB原生支持分区表，在此之前，是通过ha_partition接口来实现的。
分区表支持可传输表空间特性。
集成了SYS数据库，简化了MySQL的管理及异常问题的定位。
原生支持JSON类型，并引入了众多JSON函数。
引入了新的逻辑备份工具-mysqlpump，支持表级别的多线程备份。
引入了新的客户端工具-mysqlsh，其支持三种语言：JavaScript, Python and SQL。两种API：X DevAPI，AdminAPI，其中，前者可将MySQL作为文档型数据库进行操作，后者用于管理InnoDB Cluster。
mysql_install_db被mysqld --initialize代替，用来进行实例的初始化。
原生支持systemd。
引入了super_read_only选项。
可设置SELECT操作的超时时长（max_execution_time）。
可通过SHUTDOWN命令关闭MySQL实例。
引入了innodb_deadlock_detect选项，在高并发场景下，可使用该选项来关闭死锁检测。
引入了Optimizer Hints，可在语句级别控制优化器的行为，如是否开启ICP，MRR等，在此之前，只有Index Hints。
GIS的增强，包括使用Boost.Geometry替代之前的GIS算法，InnoDB开始支持空间索引。

MySQL 8.0 新特性

最重要的是窗口函数和 CTE

1. 窗口函数，这个功能补齐了，非常香。
2. CTE (Common Table Expressions)
3. 递归 SQL
4. UTF8 的变更，默认的 UTF8 字符集是 UTF8MB4
5. 优化和完善了对 JSON 的支持，最重要的是 JSON Partial Updates，支持对 JSON 的局部更新。

其他MySQL 8.0计划更新的特性包括：

在锁定行方面增加了更多选项，如SKIP LOCKED和NOWAIT两个选项。其中，
SKIP LOCKED允许在操作中不锁定那些需要忽略的行；NOWAIT则在遇到行的锁定的时候马上抛出错误。
MySQL能根据可用内存的总量去伸缩扩展，以更好利用虚拟机的部署。
新增“隐藏索引”的特性，这样索引可以在查询优化器中变为不可见。索引在标记为不可用后，和表的数据更改同步，但是优化器不会使用它们。对于使用隐藏索引的建议，是当不决定某个索引是否需要保留的时候，可以使用。

引入了原生的，基于InnoDB的数据字典。数据字典表位于mysql库中，对用户不可见，同mysql库的其它系统表一样，保存在数据目录下的mysql.ibd文件中。不再置于mysql目录下。
Atomic DDL。
重构了INFORMATION_SCHEMA，其中，部分表已重构为基于数据字典的视图，在此之前，其为临时表。
PERFORMANCE_SCHEMA查询性能提升，其已内置多个索引。
不可见索引（Invisible index）。
降序索引。
直方图。
角色（Role）。
资源组（Resource Groups），可用来控制线程的优先级及其能使用的资源，目前，能被管理的资源只有CPU。
引入了innodb_dedicated_server选项，可基于服务器的内存来动态设置innodb_buffer_pool_size，innodb_log_file_size和innodb_flush_method。
快速加列（ALGORITHM=INSTANT）。
自增主键的持久化。
可持久化全局变量（SET PERSIST）。
默认字符集由latin1修改为utf8mb4。
默认开启UNDO表空间，且支持在线调整数量（innodb_undo_tablespaces）。在MySQL 5.7中，默认不开启，若要开启，只能初始化时设置。
备份锁。
Redo Log的优化，包括允许多个用户线程并发写入log buffer，可动态修改innodb_log_buffer_size的大小。
默认的认证插件由mysql_native_password更改为caching_sha2_password。
默认的内存临时表由MEMORY引擎更改为TempTable引擎，相比于前者，后者支持以变长方式存储VARCHAR，VARBINARY等变长字段。从MySQL 8.0.13开始，TempTable引擎支持BLOB字段。
Grant不再隐式创建用户。
SELECT ... FOR SHARE和SELECT ... FOR UPDATE语句中引入NOWAIT和SKIP LOCKED选项，解决电商场景热点行问题。
正则表达式的增强，新增了4个相关函数，REGEXP_INSTR()，REGEXP_LIKE()，REGEXP_REPLACE()，REGEXP_SUBSTR()。
查询优化器在制定执行计划时，会考虑数据是否在Buffer Pool中。而在此之前，是假设数据都在磁盘中。
ha_partition接口从代码层移除，如果要使用分区表，只能使用InnoDB存储引擎。
引入了更多细粒度的权限来替代SUPER权限，现在授予SUPER权限会提示warning。
GROUP BY语句不再隐式排序。
MySQL 5.7引入的表空间加密特性可对Redo Log和Undo Log进行加密。
information_schema中的innodb_locks和innodb_lock_waits表被移除，取而代之的是performance_schema中的data_locks和data_lock_waits表。
引入performance_schema.variables_info表，记录了参数的来源及修改情况。
增加了对于客户端报错信息的统计（performance_schema.events_errors_summary_xxx）。
可统计查询的响应时间分布（call sys.ps_statement_avg_latency_histogram()）。
支持直接修改列名（ALTER TABLE ... RENAME COLUMN old_name TO new_name）。
用户密码可设置重试策略（Reuse Policy）。
移除PASSWORD()函数。这就意味着无法通过“SET PASSWORD ... = PASSWORD('auth_string') ”命令修改用户密码。
代码层移除Query Cache模块，故Query Cache相关的变量和操作均不再支持。
BLOB, TEXT, GEOMETRY和JSON字段允许设置默认值。
可通过RESTART命令重启MySQL实例。

参考资料与拓展阅读

• Old Reliable: A History of MySQL
• O'Reilly, The History of MySQL
• CNET, Oracle tried to buy open-source MySQL
• MySQL 官方文档, MySQL 5.6 Release Notes
• 1.3 What Is New in MySQL 5.6
• MySQL 官方文档, MySQL 5.7 Release Notes
• 1.3 What Is New in MySQL 5.7
• MySQL 官方文档, MySQL 8.0 Release Notes
• 1.3 What Is New in MySQL 8.0

#23 MySQL 常见引擎

SHOW VARIABLES LIKE '%engine%';
SHOW ENGINES;

以 MySQL 8.0 为例：

除了 PERFORMANCE_SCHEMA，

Percona (8.0) 和 MySQL 保持一致，不过 InnoDB 实际被替换成他们自己开发的兼容引擎 XtraDB。

MariaDB (10.1.45) 也使用 XtraDB 替换了 InnoDB，而且没有 BLACKHOKE, ARCHIVE，但是多了：

MyISAM

在 2010 年发布的 MySQL 5.5.5 之前，一直是 MySQL 的默认引擎。

InnoDB

InnoDB 是甲骨文的。

TokuDB

• https://en.wikipedia.org/wiki/TokuDB
• https://github.com/Tokutek/tokudb-engine

TokuTek 公司开发，后来被 Percona 公司收购，现在已被 Percona 废弃（仓库归档）。

其他

• Blackhole
• Mrg_MyISAM
• Memory
• CSV
• Archive

第三方

• federated

参考资料与拓展阅读

#22 常见字段对应的 MySQL 数据类型

关于状态，个人偏好使用 TINYINT

存储 IP（IPv4 32 位 / IPv6 128 位）的常见方案

常见操作：排序，按网段查询

1. char 可读但低效，即便去掉小数点，每一段对齐到 3 位，IPv4 也需要 12 字节（牺牲可读性）
2. int 的问题是无法支持 IPv6

存储日期时间的常见方案

• 仅需日期时用DATE（3 字节），仅需年份时用YEAR（1 字节）。
• 格式：
  • 无时区: 2025-06-17 15:30:00
  • ISO 8601: 2025-06-17T15:30:00+08:00
• 精度：秒，毫秒，微秒，纳秒
  • 普通业务（如订单时间）用秒级（DATETIME/TIMESTAMP）；
  • 金融交易、性能监控需毫秒/微秒级（DATETIME(3)/BIGINT）。
• 2038 问题：1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.499999
  • 截止到最新的 8.0 版本，TIMESTAMP 类型的 2038 年问题依然没有解决。
• MySQL 5.6+ 支持 datetime / timestamp 带小数位，最多支持 6 位小数（微秒级）

#21 MySQL 管理语句

MySQL 语句：

1. DDL
2. DML
3. 事件和锁
4. Replication
5. Prepared
6. Compound
7. 数据库管理
8. 工具

用户管理

角色 (用户模板)

create role stuff@localhost;
... 分配权限，修改属性
select host, user as role from mysql.user where authentication_string = '';

• CREATE ROLE [IF NOT EXISTS] role [, role ] ...
  锁定，密码为空
• DROP ROLE
• SET ROLE
• SET DEFAULT ROLE

用户

select host, user, plugin, left(authentication_string, 5) as pass, account_locked from mysql.user;
select distinct user from mysql.user;

select current_user(), current_role();

• CREATE USER
• DROP USER
• ALTER USER
• RENAME USER

密码

• SET PASSWORD

权限

• GRANT
• REVOKE

资源组管理

• CREATE RESOURCE GROUP
• DROP RESOURCE GROUP
• ALTER RESOURCE GROUP
• SET RESOURCE GROUP

表维护

• ANALYZE TABLE
• CHECK TABLE
• CHECKSUM TABLE
• OPTIMIZE TABLE
• REPAIR TABLE

组件，插件，Loadable Function

• CREATE FUNCTION

• DROP FUNCTION

• INSTALL COMPONENT

• UNINSTALL COMPONENT

• INSTALL PLUGIN

• UNINSTALL PLUGIN

CLONE

SET

• SET 设置变量
• SET CHARACTER SET
• SET NAMES

SHOW

• SHOW BINARY LOGS binlog
• SHOW BINLOG EVENTS binlog
• SHOW CHARACTER SET 字符集
• SHOW COLLATION 排序规则
• SHOW COLUMNS 查看表结构

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

• SHOW CREATE DATABASE
• SHOW CREATE EVENT
• SHOW CREATE FUNCTION
• SHOW CREATE PROCEDURE
• SHOW CREATE TABLE
• SHOW CREATE TRIGGER
• SHOW CREATE USER

• SHOW CREATE VIEW

• SHOW DATABASES 数据库

• SHOW ENGINE 查看指定引擎的状态

部分引擎会提供一些状态信息，比如 INNODB, PERFORMANCE_SCHEMA

• SHOW ENGINES 存储引擎
• SHOW ERRORS
• SHOW EVENTS
• SHOW FUNCTION CODE
• SHOW FUNCTION STATUS
• SHOW GRANTS 用户权限
• SHOW INDEX 查看指定表的索引
• SHOW MASTER STATUS
• SHOW OPEN TABLES
• SHOW PLUGINS 插件
• SHOW PRIVILEGES
• SHOW PROCEDURE CODE
• SHOW PROCEDURE STATUS
• SHOW PROCESSLIST
• SHOW PROFILE
• SHOW PROFILES
• SHOW RELAYLOG EVENTS
• SHOW REPLICAS
• SHOW SLAVE HOSTS | SHOW REPLICAS
• SHOW REPLICA STATUS
• SHOW SLAVE | REPLICA STATUS
• SHOW STATUS
• SHOW TABLE STATUS
• SHOW TABLES 表
• SHOW TRIGGERS
• SHOW VARIABLES
• SHOW WARNINGS

其他

• BINLOG
• CACHE INDEX
• FLUSH
• KILL
• LOAD INDEX INTO CACHE
• RESET
• RESET PERSIST
• RESTART
• SHUTDOWN

#20 MySQL 字段类型 varchar 和 text 的差异

一次数据库表结构调整，引起了我对 MySQL 字段类型 TEXT 和 VARCHAR 的思考。

#19 MySQL 数据类型总结

数值

• bit [1 - 64]

• tinyint 1

• smallint 2
• mediumint 3
• int 4

• bigint 8

• float 4 浮点（单精度）

• double 8 浮点（双精度）
• decimal(m, d) max(m, d) + 2 定点

别名 Synonyms：

• integer -> int
• bool / boolean -> tinyint(1)
• dec / numeric / fixed -> decimal
• double precision / real -> double

PS: 如果开启 REAL_AS_FLOAT 模式，REAL 就变成了 FLOAT 的别名。

字符串/文本

• char

• varchar

• tinytext

• text
• mediumtext

• longtext

• JSON MySQL 5.7 加入

二进制

• binary

• varbinary

• tinyblob

• blob
• mediumblob
• longblob

时间

1. 用字符串（varchar）表示时间，除了失去数据库这一层的数据类型保证之外，还会在时间比较运算场景下，失去索引命中可能。
2. 用整型数 bigint（8 字节）表示时间，可以扩大 timestamp 时间范围，但要自己维护数据（其实还是比较好维护的）。
3. 带时区的时间：timestamp
4. 带精度的时间：time、datetime、timestamp，精度最大为 6，表示毫秒。比如：timestamp(6)

其他

• enum
• set
• Geometry 系列

货币

1. 以分为单位 Integer
2. Decimal，不要用 float，double

参考资料与拓展阅读

• https://dev.mysql.com/doc/refman/5.7/en/data-types.html

#18 一道有趣的 MySQL 小题目

在开源中国每日动弹中看到这么一道题目，蛮有意思，还学到了一个新的 MySQL 语法：CTE。

#17 MySQL: wait for table metadata lock

问题

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Connection id:      17190053
Current database:   gkbb
Current user:       root@10.9.165.246
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.5-10.1.26-MariaDB MariaDB Server
Protocol version:   10
Connection:     10.9.108.125 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         13 days 21 hours 14 min 38 sec

Threads: 276  Questions: 31378648  Slow queries: 212  Opens: 2977  Flush tables: 1  Open tables: 2761  Queries per second avg: 26.155
--------------

mysql> show global variables like "innodb_version";
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| innodb_version | 5.6.36-82.1 |
+----------------+-------------+
1 row in set (0.06 sec)

PS: 查看 status 还有一个快捷方式 \s。

编辑测试库表结构（添加字段），卡住，任何操作都不行了，等一个多小时，还是不行。。
还一度怀疑是不是表结构设计问题，字段、数据是不是太多了。

过程

偶尔想起看看会话情况：

SELECT * FROM information_schema.processlist WHERE db = 'mydb';

或命令：

• mysqladmin -uroot -p123456 processlist
• mysql -uroot -p123456 -e 'SHOW PROCESSLIST'

看到里面好几个会话的状态都是 wait for table metadata lock，这就有点奇怪了，之前没有见过。

网上的资料显示：

为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ，来保护表的元数据信息。
因此在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait 。

如果资料没错，那么就是说，如果有事务没有结束，DDL 操作请求 MDL（metadata lock）时会卡住这张表。

我想起我们的服务中确实存在会话没有关闭的情况。

1. 用了 SQLAlchemy 做 ORM
2. 每次查询都使用一个会话，包括 SELECT
3. 增删改操作都立即 commit 了，SELECT 却没有（记得是有个什么原因特意如此）

合理怀疑：这个查询 SESSION 没有关闭，导致 ALTER 语句进入 MDL 等待状态，然后导致了表无法进行任何操作（包括查询，至于为什么这样，我不知道）。

本地复现

1. 开两个终端，分别建立 MySQL 连接。
2. 其中一个终端（A）：
3. SET SESSION auto_commit = 0;
4. SELECT * FROM test.test LIMIT 1;
5. 另一个终端（B）只需要：TRUNCATE test.test;，然后发现：卡住了。

PS:

1. DDL 需要 metadata 锁。
2. TRUNCATE 属于 DDL，可能因为其非事务性（不支持提交和回滚）。参考：https://dba.stackexchange.com/questions/36607/why-is-truncate-ddl

现在，回到终端 A：

mysql> select * from information_schema.processlist where db = 'test';
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                                                           |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
|  3 | root | localhost | test | Query   |    0 | executing                       | select * from information_schema.processlist where db = 'test' |
|  5 | root | localhost | test | Query   | 6111 | Waiting for table metadata lock | truncate test                                                  |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421232684444408
                 trx_state: RUNNING
               trx_started: 2019-03-29 16:06:14
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 3
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-03-29 19:04:40 0x7f1bcc1d6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 11228 srv_idle
srv_master_thread log flush and writes: 11234
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 16
OS WAIT ARRAY INFO: signal count 10
RW-shared spins 0, rounds 27, OS waits 12
RW-excl spins 0, rounds 32, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 27.00 RW-shared, 32.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 54542
Purge done for trx's n:o < 54542 undo n:o < 0 state: running but idle
History list length 53
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421232684445328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421232684443488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
639 OS file reads, 99 OS file writes, 21 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 4 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 26417867
Log flushed up to   26417867
Pages flushed up to 26417867
Last checkpoint at  26417858
0 pending log flushes, 0 pending chkp writes
17 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 133441
Buffer pool size   8192
Free buffers       7710
Database pages     482
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 446, created 41, written 72
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 482, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=4492, Main thread ID=139757271107328, state: sleeping
Number of rows inserted 6, updated 0, deleted 0, read 20
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

表明：事务存在，TRUNCATE 锁等待。

如果，kill 3 干掉这个没有 commit 的查询 SESSION，TRUNCATE 就会正常执行下去。

结论

1. 不管怎样，应该保持事务的短小精干，快速执行和退出。
   PS：业务代码中 SELECT 之前为什么不提交的问题需要进一步检查。
2. DDL 需要 MDL，而没有结束的事务会阻止 MDL。
   更多细节，需要更多深入了解。

#16 MySQL 分区

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

分区类型

• [LINEAR] HASH(expr) 根据值的哈希分区
• [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
• RANGE{(expr) | COLUMNS(column_list)} 根据值得范围分区
• LIST{(expr) | COLUMNS(column_list)} 根据不同的值分区

COLUMNS 不限于整数

创建分区

PARTITION BY LIST(column) (
    PARTITION a VALUES IN (a1, a2, a3),
    PARTITION b VALUES IN (b1, b2, b3),
    PARTITION c VALUES IN (c1, c2, c3)
)

PARTITION BY RANGE(column) (
    PARTITION 2012q1 VALUES LESS THAN('2012-04-01'),
    PARTITION 2012q2 VALUES LESS THAN('2012-07-01'),
    PARTITION 2012q3 VALUES LESS THAN('2012-10-01'),
    PARTITION 2012q4 VALUES LESS THAN('2013-01-01')
)

PARTITION BY HASH(column) PARTITIONS 128
PARTITION BY HASH(dayofmonth(date)) PARTITIONS 31

查看分区信息

SELECT * FROM `information_schema`.`PARTITIONS`;

子分区

1. PARTITION 关键字换成 SUBPARTITION，PARTITIONS 关键字换成 SUBPARTITIONS，接在分区语句后面。
2. 可以是不同类型。

比如：

PARTITION BY HASH (prod_id) SUBPARTITION BY HASH (cust_id)
PARTITIONS 4 SUBPARTITIONS 4;

脚本

如果是 By Range 分区，一般需要自动创建新的分区，删除久的分区。

比如：

CREATE TABLE `test` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `key` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
    `value` VARCHAR(300) NOT NULL COLLATE 'utf8mb4_general_ci',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`, `date`) USING BTREE,
    UNIQUE INDEX `key` (`date`, `key`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
/*!50100 PARTITION BY RANGE (to_days(`date`))
(PARTITION p20230123 VALUES LESS THAN (738909) ENGINE = InnoDB,
 PARTITION p20230124 VALUES LESS THAN (738910) ENGINE = InnoDB,
 PARTITION p20230125 VALUES LESS THAN (738911) ENGINE = InnoDB)  */;

然后，通过下面这个 cron 任务自动更新分区：

#!/bin/bash

# 开启调试模式，输出每条执行的命令及其执行结果
set -x

# 检查当前机器 IP 地址中是否包含指定的 VIP（虚拟 IP）
# 确认在主 MySQL 上执行
vip_w="192.168.12.34"
if [ $(/sbin/ip a | grep "${vip_w}" | wc -l) -eq 0 ]; then echo 'WARN: Wrong Machine!!!'; exit 1; fi

# 删除 90 天前的分区
# PS：如果分区不存在，TRUNCATE 不会报错。
delete_date=$(date -d '90 days ago' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test TRUNCATE PARTITION p$delete_date;"  # DROP

# 创建未来分区
create_date=$(date -d '7 days' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test ADD PARTITION (PARTITION p$delete_date VALUES LESS THAN (TO_DAYS("$delete_date")));"