MySQL数据库扩展

mysql_扩展

1. 扩展 - mysql 定时任务

在 mysql 中是通过事件机制可以完成数据库中的定时任务，主要原理是在指定的时间调用指定的存储过程。

注意：在 mysql 中想要使用 "事件" 功能需要先开启该功能，事件机制是 mysql5.1 版本开始引入的，这意味着版本低于 5.1 的可能无法使用。

• 查看 mysql 是否开启事件功能

show variables like 'event_scheduler';

off/0：代表关

on/1：代表开

• 开启事件功能

set global event_scheduler = on;

1.1 首先我先创建一个简单存储过程

delimiter $
create procedure prc_xld_event_timing()
BEGIN
INSERT INTO xld_event_timing (id, now_date, random) VALUES (NULL, NOW(), ROUND(rand()*1000000,0));
end $
delimiter ;

该存储过程就是一个很简单的插入数据的操作！

1.2 创建一个事件

创建一个事件，让事件按照某种规则去调用存储过程，这样就可以实现定时任务的功能了

创建事件的语法：

CREATE [DEFINER = {USER | CURRENT_USER}] EVENT [IF NOT EXISTS] 事件名 
	ON SCHEDULE { AT 某个确定的时间点 | EVERY 期望的时间间隔 [STARTS datetime][END datetime ] } 
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
DO 
	BEGIN 
	  # 具体的语句,注意： call 存储过程除外 
	END

子句	是否必选	描述
DEFINER	可选	定义 "事件" 执行时检查权限的用户
IF NOT EXISTS	可选	判断要创建的事件是否存在
EVENT event_name	必选	指定事件名称
ON SCHEDULE schedule	必选	定义执行计划（执行的时间和时间间隔） schedule：具体的执行计划规则
ON COMPLETION [NOT] PRESERVE	可选	定义事件是否循环执行 指定事件一次执行还是永久执行，默认 NOT PRESERVE：一次执行。
[ENABLE，DISABLE，DISABLE ON SLAVE]	可选	指定事件是否开启，默认 ENABLE ENABLE：开启 DISABLE：关闭 DISABLE ON SLAVE：从库中关闭
COMMENT ‘comment’	可选	定义事件的注释 comment：注释内容
DO event_body	必选	指定事件启动时所要执行的事件主体代码 可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，则可以使用 BEGIN…END 复合结构 event_body：执行的代码

例如：

CREATE EVENT event_xld_event_timing 
ON SCHEDULE EVERY 1 DAY STARTS '2022-10-03 00:00:00' 
ON COMPLETION PRESERVE ENABLE DO
CALL prc_xld_event_timing ();

创建事件说明：从 2022年10月3日0点开始，每隔 1 天自动调用 prc_xld_event_timing 存储过程。

• 1 day：表示 1 天 1 次
• 2 year：表示 2 年 1 次
• 1 hour：表示 1 小时 1 次，依次类推等...

真心想说比 oracle 好理解多了。

注意：事件创建好以后就会立刻执行一次，并且一般是默认开启的。

-- 创建事件，指定事件名
CREATE EVENT IF NOT EXISTS event_demo
-- 每天凌晨1点执行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
-- 永久执行
ON COMPLETION PRESERVE
-- 开启事件
ENABLE
-- 事件说明
COMMENT 'T+1的定时任务'
-- 指定事件启动时所要执行的代码
DO 
BEGIN
	insert into employees.test(name) values('test');
END

1.3 控制某个事件的运行状态

• 查看所有事件的状态：

show events;
# 或者
SELECT * FROM information_schema.EVENTS;

• 查看某一个事件的信息：

show events like '%event_xld_%';
# 或者
SELECT * FROM information_schema.EVENTS where event_name like '%event_xld_%';

• 指定数据库所有事件信息：

show events from demo; # demo 数据库

• 开启一个事件

alter event 事件名 on completion preserve enable; 

例如：

alter event event_xld_event_timing on completion preserve enable; 

• 关闭一个事件

alter event 事件名 on completion preserve disable;

例如：

alter event event_xld_event_timing on completion preserve disable;

• 查看一个事件定义信息：

SHOW CREATE EVENT 事件名;

例如：

SHOW CREATE EVENT event_xld_event_timing;

• 修改事件的执行计划：

ALTER EVENT event_name ON SCHEDULE schedule;

• 修改事件主体：

ALTER EVENT event_name DO event_body;

• 重命名事件：

ALTER EVENT event_name RENAME TO new_event_name;

• 删除一个事件：

DROP EVENT 事件名;

例如：

DROP EVENT event_xld_event_timing;

1.4 小结

• 事件有两种自动执行的方式：
  • 在某个确定的时间点执行。
  • 每隔一段时间执行一次。
• 在某个时间点执行的事件，在执行完事件后，默认会把该事件删除掉，通过在 AT … 语句和 DO … 语句中间加 ON COMPLETION PRESERVE来取消自动删除。

1.5 扩展 - 定时时间示例

• 每隔1分钟执行

ON SCHEDULE EVERY 1 MINUTE

• 每天凌晨1点执行

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)

• 每个月的第1天凌晨1点执行

ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)

• 每3个月，从现在起1周后开始

ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK

• 每12个小时，从现在起30分钟后开始，并于现在起4个星期后结束

ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

2. 扩展 - mysql 生成列

2.1 生成列的简介

MySQL 5.7 后引入了一个名为 generated column（生成列）的新功能。我叫它 "计算列"，因为 该列中的数据是基于预定义的表达式或者其他列计算得来的。

应用场景：

• 需要将多字段组成一个结果时，例如：表中存了 "姓" 和 "名字"，现在需求是要全名。
• 需要计算多个字段的结果时，例如：表中存了 "已付款" 和 "总金额"，现在需求是要付款百分比。
• 等多种需求....

以上的这些需求，我们是可以在 select 查询时使用表达式解决的，但这种方式的 SQL 语句会显的很臃肿，不方便。 MySQL 为了简化 SQL 语句，在 5.7 推出了 "生成列" 的新功能，主要是用于解决列与列之间计算的问题。

2.2 mysql 生成列的类型

• 存储列：在更新（增，删，改）数据时会物理计算存储列，以及存储到物理磁盘中。

注意事项：

• 存储列，是通过在更新数据时触发计算列的计算（消耗写的性能，提升查询的速度）。
• 在使用了存储列之后，如果使用 alter table 语句对一张大表进行修改的话，会非常的耗时，因为 alter table 语句会重建表。

• 虚拟列：每次读取（select）数据时才会动态计算虚拟列。

注意事项：

• 虚拟列，只会在查询时触发计算（消耗读的性能，节约了更新的速度）。

以上两种请根据具体业务选择，个人感觉 "虚拟列" 会比较常用。

2.3 使用生成列

• 定义生成列的语法：

[create | alter] table
...
column_name data_type [ generated always ] AS (expression) [ VIRTUAL | STORED ] [ UNIQUE [ KEY ]]
...

• column_name：列名。
• data_type：数据类型。
• generated always：创建生成列的关键字。
• as（expression）：在括号内指定表达式。表达式可以包含文字，没有参数的内置函数，运算符或对同一表中类的引用（如果使用函数，它必须是标量和确定性的）。
• [ VIRTUAL | STORED ]：声明生成列的类型，VIRTUAL（虚拟列）或 STORED（存储列）。默认使用 VIRTUAL（虚拟列）。
• 如果使用了存储列，则可以为其定义，唯一约束。

• 创建表时 - 创建生成列（虚拟列）

create table
if not exists 表名 (
	...,
	full_name varchar (20) generated always as (concat(surname, name)) virtual comment '计算列-全名'
) engine = innodb default charset = utf8mb4 comment '';

例如：

CREATE TABLE
IF NOT EXISTS xld_generated_column (
	id INT auto_increment PRIMARY KEY COMMENT '主键id',
	surname VARCHAR (10) NOT NULL COMMENT '姓',
	NAME VARCHAR (10) NOT NULL COMMENT '名称',
	full_name VARCHAR (20) generated always AS (CONCAT(surname, NAME)) VIRTUAL COMMENT '计算列-全名'
) ENGINE = INNODB DEFAULT charset = utf8mb4 COMMENT 'xld的生成列测试表';

测试：

# 插入数据
INSERT INTO xld.xld_generated_column (id, surname, NAME)
VALUES
	(NULL, '张', '三'),
	(NULL, '李', '四'),
	(NULL, '王', '五');

注意：在插入数据时，mysql 是不允许为 "生成列" 字段插入数据的！

报错如下：

[Err] 3105 - The value specified for generated column '列名' in table '表名' is not allowed.

[Err]3105-不允许为表“表名”中生成的列“列名”指定值。

# 查询
SELECT * from xld_generated_column;
--------------------------------------------------------------------------------------------------------------------------------
1	张	三	张三 
2	李	四	李四
3	王	五	王五

• 修改表时 - 创建生成列

alter table 表名 add 列名 数字类型 [ generated always ] AS (expression) [ VIRTUAL | STORED ] [ UNIQUE [ KEY ]];

例如：

alter table xld_generated_column add full_name VARCHAR (20) generated always AS (CONCAT(surname, NAME)) VIRTUAL COMMENT '计算列-全名';

• 查看生成列的类型

desc 表名;

例如：

desc xld_generated_column;

• 修改生成列

alter table 表名 modify column 列名 数字类型 [ generated always ] AS (expression) [ VIRTUAL | STORED ] [ UNIQUE [ KEY ]];

例如：

alter table xld_generated_column modify column full_name VARCHAR (20) generated always AS (CONCAT(surname,'_',NAME)) VIRTUAL COMMENT '计算列-全名';

注意：mysql 是不允许修改 "生成列" 的类型的！

报错如下：

[Err] 3106 - 'Changing the STORED status' is not supported for generated columns.

[Err]3106-生成的列不支持“更改存储状态”。

• 删除生成列

alter table 表名 drop column 列名;

例如：

alter table xld_generated_column drop full_name;

3. 扩展 - 其他特性的收集

1. 获取两个日期之间的日期列表

SELECT
	DATE_FORMAT(DATE_ADD(CONCAT('2022-01-01'),INTERVAL (help_topic_id) DAY),'%Y-%m-%d') DT
FROM
	mysql.help_topic
WHERE
	help_topic_id <= TIMESTAMPDIFF(
		DAY,
		CONCAT('2022-01-01'),
		CONCAT('2022-01-15'));

说明："2022-01-01" 为开始日期，"2022-01-15" 为结束日期。

2. MySQL 执行动态 SQL

MySQL 中可以通过组合字符串的方式动态地构建 SQL 语句，然后执行该语句。在使用组合字符串的方式时，需要注意以下几点：

1. 字符串中的值必须使用单引号括起来，以便 MySQL 能够正确识别它们。
2. 对于 可能包含单引号的值，需要使用双引号括起来，以便避免 SQ L注入攻击。
3. 应该 尽可能地避免使用动态 SQL，以防止安全漏洞。

• 动态 SQL 案例：

SET @OBJ_CODE = 'ROOT_CODE_WY';
# 拼接 SQL 
SET @SQL = CONCAT('SELECT * FROM base_obj WHERE OBJ_CODE =','"',@OBJ_CODE,'"');
# 分配拼接后的 SQL
PREPARE xld_sql FROM @SQL;
# 执行 SQL
EXECUTE xld_sql;
# 解除分配 
DEALLOCATE PREPARE xld_sql;

4. 面试重点 - 锁等待查询

1. 查询数据库中存在的锁：

   SHOW OPEN TABLES WHERE In_use > 0;
   

2. 查询 innodb 行锁的信息：

   SHOW STATUS LIKE 'innodb_row_lock%';
   

3. 查询事务锁信息：主要是查看 "事务状态" 处于 "锁等待" 的事务（即：trx_state = LOCK WAIT）

   SELECT
   	*
   FROM
   	information_schema.innodb_trx
   

   字段	取值 - 说明
   trx_id	事务id：具有唯一性。对应 data_locks 表的 engine_transaction_id
   trx_state	事务执行状态： 1. RUNNING：运行中 2. LOCK WAIT：锁等待 3. ROLLING BACK：回滚中 4. COMMITTING：提交中
   trx_started	事务开始时间，格式如：2024-11-23 10:18:38。
   trx_requested_lock_id	锁等待 id，对应 data_locks 表的 engine_lock_id，以及 data_lock_waits 表的 requesting_engine_lock_id！
   trx_wait_started	锁等待的开始时间。
   trx_weight	事务权重，衡量 "更改的行数" 和 "锁的行数"，在死锁的时候，引擎会有优先回滚 "低权重" 的事务。
   trx_mysql_thread_id	MySQL 中的线程 id，可用于 performance_schema .threads where id = x 的查询。
   trx_query	事务过程执行的 SQL 语句。
   trx_operation_state	事务当前的状态：fetching rows 获取行。
   trx_tables_in_use	事务过程中使用到的表数量。
   trx_tables_locked	事务过程中被锁的表数量。
   trx_lock_structs	事务保留的锁数量。
   trx_lock_memory_bytes	锁使用的内存大小。
   trx_rows_locked	事务大约锁定的行数。
   trx_rows_modified	事务过程中更新或插入的行数。
   trx_concurrency_tickets	事务并发票数，由系统变量 innodb_concurrency_tickets 设置。
   trx_isolation_level	事务隔离级别
   trx_unique_checks	是否打开唯一性检测的标识，加载大量数据时关闭。
   trx_foreign_key_checks	是否打开外键检查的标识，加载大量数据时关闭。
   trx_last_foreign_key_error	最后一次的外键错误的信息
   trx_adaptive_hash_latched	自适应哈希索引是否被当前事务锁定。
   trx_adaptive_hash_timeout
   trx_is_read_only	1：表示只读
   trx_autocommit_non_locking	1：表示没使用 for update 或 lock in shared mode 的 select 语句。

4. 查看正在等待的事务：查询 "锁等待" 信息！

   select
   	blocking_engine_lock_id, # 阻塞者锁id
   	blocking_engine_transaction_id, # 阻塞者事务id
   	blocking_thread_id, # 阻塞者线程id
   	blocking_event_id # 阻塞者的事件 id
   from
   	performance_schema.data_lock_waits 
   where requesting_engine_transaction_id  = ? # innodb_trx 表的 trx_id
   

   字段	取值 - 说明
   engine	存储引擎
   requesting_engine_lock_id	请求获取锁的 "锁id"。将此列与 data_locks 表的 engine_lock_id 列联接
   requesting_engine_transaction_id	请求获取锁的 "事务id"（即：正处于等待的事务），innodb_trx 表的 trx_id
   requesting_thread_id	请求获取锁的 "线程id（MySQL 内部的线程id）"
   requesting_event_id	请求获取锁的 "事件 id"，在请求锁定的会话中。
   requesting_object_instance_begin	请求获取的 "锁"，在内存中的地址
   blocking_engine_lock_id	造成阻塞的 "锁id"。将此列与 data_locks 表的 engine_lock_id 列联接
   blocking_engine_transaction_id	造成阻塞的 "事务id"（即：持有锁的 "事务id"），innodb_trx 表的 trx_id
   blocking_thread_id	造成阻塞的 "线程id"（即：持有锁的 "线程id"），可用于 performance_schema .threads where id = x 的查询
   blocking_event_id	造成阻塞的 "事件 id"，在持有锁的会话中。
   blocking_object_instance_begin	造成阻塞的 "锁"，在内存中的地址。

5. 查询造成 "锁等待" 的 SQL 语句：

   select
   	t.*,
   	e.*
   from
   	performance_schema.events_statements_history e
   left join performance_schema .threads t on e.thread_id = t.thread_id
   where
   	t.thread_id = 1100955 # 阻塞者进程id，即：data_lock_waits.blocking_thread_id
   

   • performance_schema .threads 表结构：

   字段	取值 - 说明
   thread_id	MySQL 中的线程 id
   name	与服务器中 "线程" 执行的代码，关联的名称（即：线程是做什么的）
   type	线程类型 FOREGROUND：前台线程（即：用户连接的线程 - 会话线程） BACKGROUND：后台线程（即：与内部服务器活动相关联的线程）
   processlist_id	MySQL 的进程 id，与 "用户会话线程" 关联information_schema.processlist.id = n （即：MySQL 的 "任务/进程列表" ）
   processlist_user	与 "前台线程/会话线程" 关联的 "MySQL 用户"。"后台线程/内部线程" 则为：NULL
   processlist_host	与 "前台线程/会话线程" 关联的 "主机名 + 端口"。"后台线程/内部线程" 则为：NULL
   processlist_db	线程的连接的 "数据库"
   processlist_command	"线程" 的命令状态（执行，等待，空闲等...）
   processlist_time	"线程" 处于该状态的时间（秒）。对于副本 SQL 线程，该值为与上次时间戳之间的秒数（实时时间）
   processlist_state	"线程" 当前操作的状态 与 processlist_command 字段有关联
   processlist_info	"线程" 正在执行的语句
   parent_thread_id	"父线程" 的id，如果当前线程是一个子线程，该列就是 "派生线程" 的值
   role	闲置
   instrumented	是否检测 - "线程执行的事件"（yes / no）
   history	是否记录 - "线程的历史事件"（yes / no）
   connection_type	用于 "建立连接" 或用于 "后台线程" 的网络协议。
   thread_os_id	该 "线程" 所对应 "操作系统" 的 "线程id"。
   resource_group	资源组标签
   execution_engine	执行引擎
   controlled_memory	MySQL 分配给该线程的内存空间。
   max_controlled_memory	在 "线程" 执行期间，分配的最大内存。
   total_memory	当前 "线程" 所使用的内存空间。
   max_total_memory	在 "线程" 执行期间，使用的最大内存。
   telemetry_active	线程是否具有活动的遥测（yes / no）

   • performance_schema.events_statements_history 表结构：

   说明：events_statements_history 表具有与 events_statements_current 相同的 "列" 和 "索引"。

   字段	取值 - 说明
   thread_id	MySQL 中的线程 id
   event_id	事件开始时的 "事件id"
   end_event_id	事件结束时的 "事件id"，对应 data_lock_waits.blocking_event_id
   event_name	事件所在的 "工具" 的名称（即：描述 SQL 语句行为）。来自于 setup_instruments 表。
   source	源文件的名称生成事件的代码和进行检测的文件。
   timer_start	事件开始时间（单位：皮秒（万亿分之一秒）
   timer_end	事件结束时间（单位：皮秒（万亿分之一秒）
   timer_wait	事件的执行时间（单位：皮秒（万亿分之一秒）
   lock_time	等待表锁所花费的时间（单位：微秒）
   sql_text	SQL 语句的文本
   digest	该语句将 SHA-256 值，"摘要" 为字符串 64 十六进制字符
   digest_text	规范化语句 "摘要" 文本
   current_schema	SQL 语句的数据库
   object_type	对于嵌套语句（存储程序），这些列包含有关 "父语句" 的信息 - 类型
   object_schema	对于嵌套语句（存储程序），这些列包含有关 "父语句" 的信息 - 数据库
   object_name	对于嵌套语句（存储程序），这些列包含有关 "父语句" 的信息 - 名称
   object_instance_begin	标识该 SQL 语句，在内存中对象的地址
   mysql_errno	SQL 语句的错误号
   returned_sqlstate	SQL 语句 SQLSTATE 值
   message_text	SQL 语句的错误信息
   errors	SQL 语句是否出错，0：完成/警告，1：出现错误
   warnings	SQL 语句的警告数
   rows_affected	受 SQL 语句影响的行数
   rows_sent	SQL 语句返回的行数
   rows_examined	服务器层检测到的行数
   created_tmp_disk_tables	在服务器创建的内部临时表（物理表）的数量
   created_tmp_tables	在服务器创建的内部临时表的数量
   select_full_join	多表连接时，扫描的联接数，则没有使用索引。如果此值不为 0，则应仔细检查表的索引。
   select_full_range_join	多表连接时，使用 "范围搜索" 的联接数
   select_range	在第一个表上使用 "范围搜索" 的联接数
   select_range_check	检查无键联接数。如果此值不为 0，则应仔细检查表的索引
   select_scan	对第一个表执行完全扫描的连接数
   sort_merge_passes	排序算法必须执行的合并传递次数。如果此值很大，则应考虑增加 sort_buffer_size 系统变量的值。
   sort_range	使用范围完成的排序数
   sort_rows	排序的行数
   sort_scan	通过扫描表完成的排序数
   no_index_used	如果语句没有使用索引，则为 0，否则为 1
   no_good_index_used	如果服务器找不到可用的索引，则为 0，否则为 1
   nesting_event_id
   nesting_event_type
   nesting_event_level
   statement_id	服务器在 SQL 级别维护的查询 ID。 该值对于服务器实例是唯一的，因为这些 ID 是使用全局计数器生成的。

6. 查询某一个 "事务锁" 的信息：根据事务表（innodb_trx）的中 trx_id 查询该事务的 "锁信息"!

   SELECT
   	*
   FROM
   	performance_schema.data_locks
   WHERE
   	ENGINE_TRANSACTION_ID = 17073795 # 事务id，即：innodb_trx.trx_id 
   

   字段	取值 - 说明
   engine	存储引擎
   engine_lock_id	存储引擎内部的 "锁id"，该值会发生动态变化，外部系统不应该依赖该值。
   engine_transaction_id	请求锁的 "事务id"（即：锁的持有者），innodb_trx 表的 trx_id
   thread_id	MySQL 中的线程id，可用于 performance_schema .threads where id = x 的查询。
   event_id	事件 id
   object_schema	数据库名称
   object_name	表名称
   partition_name	分区名称
   subpartition_name	子分区名称
   index_name	索引名称
   object_instance_begin	"锁" 的内存空间起始地址。
   lock_type	锁类型：TABLE = 表锁，RECORD = 行锁。
   lock_mode	锁模式： 1. IX：表意向锁 2. X：记录锁 + 间隙锁 3. X,REC_NOT_GAP：记录锁 4. X,GAP：间隙锁 5. LOCK_INSERT_INTENTION：插入意向锁
   lock_status	锁状态：GRANTED = 持有锁，WAITING = 等待锁
   lock_data	锁的数据，当是 "行锁" 的时候，才会有数据（如果是 "聚族索引" 则直接显示主键，如果是非聚族索引则是，当前数据以及主键数据）。

• 扩展 - 查询/清除等待锁的事务

1. 查看进程：

SHOW PROCESSLIST;

2. 查看是否锁表：

SHOW OPEN TABLES WHERE In_use > 0;

3. 查看正在锁（持有锁）的事务：

SELECT * FROM INFORMATION_SCHEMA.data_locks; 

4. 查看等待锁的事务：

SELECT * FROM INFORMATION_SCHEMA.data_lock_waits; 

5. 查询正在执行的事务：

SELECT * FROM information_schema.innodb_trx;

6. 杀死被锁线程 trx_mysql_thread_id 为被锁的事务id，可以使用 kill 线程id 杀死对应的线程，达到释放锁效果。

kill 999; # trx_mysql_thread_id