Undo Segment Caching

To improve the efficiency of undo segmentallocation, InnoDB caches some undo segments during transaction commit. Both insert undo and update undo segments can be cached if the following two conditions are satisfied concurrently.

1. The undo segment contains only one undo page.
2. The space used in the only undo page must be less than three-quarters of the data page size. For an undo page of the default size of 16K, the space used in the undo page must be less than 12K.

If an insert undo segment meets the caching condition, it will be added to the head of the insert_undo_cached linked list of the rollback segment. If an update undo segment meets the caching condition, it will be added to the head of the update_undo_cached linked list of the rollback segment.

InnoDB Transaction Committing

In the two-phase commit process, the flush sub-phase in the commit phase flushes all redo logs generated in the prepare phase and earlier to disk, and writes all binlog logs generated during transaction execution to the binlog log files. The sync sub-phase decides whether to trigger the operating system to flush the binlog logs to disk based on the value of the system variable sync_binlog.

The first two sub-phases handle only logs and do not involve InnoDB transactions. After these two phases are completed, the InnoDB transaction has not yet been committed, and the transaction is still in the preparing-to-commit state (TRX_STATE_PREPARED).

The commit sub-phase is when the InnoDB transaction is actually committed. This phase completes the transaction commitment.

Modifying the Insert Undo Segment State

If a transaction inserts records into a user ordinary table, InnoDB allocates an insert undo segment for the transaction. If a transaction inserts records into a user temporary table, InnoDB allocates a different insert undo segment for the transaction. InnoDB may allocate 0 to 2 insert undo segments for a transaction.

The commit sub-phase modifies the states of all insert undo segments allocated for the transaction. If an insert undo segment satisfies the caching condition, its state will be changed to TRX_UNDO_CACHED. Otherwise, its state will be changed to TRX_UNDO_TO_FREE. After the transaction commit is completed, InnoDB caches or releases the insert undo segments based on their states.

Generating the Transaction Commit Number

The transaction commit number is the no attribute of the transaction object, which is usually represented as trx->no. In the code, the annotation for transaction commit number is transactionserializationnumber, which directly translates into transaction serial number or transaction serial number in Chinese. Because trx->no is generated when the transaction is committed, it is easier to understand if we call it the transaction commit number.

Only the update undo segment requires the transaction commit number. When the purge thread cleans up the update undo log, it decides whether to clean up the undo log in this undo log group based on the transaction commit number stored in the undo log group of the update undo segment.

InnoDB generates a transaction commit number and stores it in the transaction object's no attribute before modifying the state of the update undo segment.

数据库表结构设计，常见的数据库管理系统

一、数据场景 1、表结构简介 任何工具类的东西都是为了解决某个场景下的问题，比如Redis缓存系统热点数据，ClickHouse解决海量数据的实时分析，MySQL关系型数据库存储结构化数据。 数据的存储则需要设计对应的表结构，清楚的表结构，有助于快速开发业务，和理解系统。 表结构的设计通常从下面几个方面考虑：业务场景、设计规范、表结构、字段属性、数据管理。 2、用户场景 例如存储用户基础信息数据，通常都会下面几个相关表结构：用户信息表、单点登录表、状态管理表、支付账户表等。 用户信息表 存储用户三要素相关信息：姓名，手机号，身份证，登录密码，邮箱等。 CREATE TABLE `ms_user_center` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 用户ID,`user_name` varchar(20) NOT NULL COMMENT 用户名,`real_name` varchar(20) DEFAULT NULL COMMENT 真实姓名,`pass_word` varchar(32) NOT NULL COMMENT 密码,`phone` varchar(20) NOT NULL COMMENT 手机号,`email` varchar(32) DEFAULT NULL COMMENT 邮箱,`head_url` varchar(100) DEFAULT NULL COMMENT 用户头像URL,`card_id` varchar(32) DEFAULT NULL COMMENT 身份证号,`user_sex` int(1) DEFAULT 1 COMMENT 用户性别:0-女,1-男,`create_time` datetime DEFAULT NULL COMMENT 创建时间,`update_time` datetime DEFAULT NULL COMMENT 更新时间,`state` int(1) DEFAULT 1 COMMENT 是否可用,0-不可用,1-可用,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=用户表;单点登录表 用意是在多个业务系统中，用户登录一次就可以访问所有相互信任的业务子系统，是聚合业务平台常用的解决方案。 CREATE TABLE `ms_user_sso` (`user_id` int(11) NOT NULL COMMENT 用户ID,`sso_id` varchar(32) NOT NULL COMMENT 单点信息编号ID,`sso_code` varchar(32) NOT NULL COMMENT 单点登录码,唯一核心标识,`log_ip` varchar(32) DEFAULT NULL COMMENT 登录IP地址,`create_time` datetime DEFAULT NULL COMMENT 创建时间,`update_time` datetime DEFAULT NULL COMMENT 更新时间,`state` int(1) DEFAULT 1 COMMENT 是否可用,0-不可用,1-可用,PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=用户单点登录表;状态管理表 系统用户在使用时候可能出现多个状态，例如账户冻结、密码锁定等，把状态聚合到一起，可以更加方便的管理和验证。 CREATE TABLE `ms_user_status` (`user_id` int(11) NOT NULL COMMENT 用户ID,`account_status` int(1) DEFAULT 1 COMMENT 账户状态：0-冻结,1-未冻结,`real_name_status` int(1) DEFAULT 0 COMMENT 实名认证状态：0-未实名,1-已实名,`pay_pass_status` int(1) DEFAULT 0 COMMENT 支付密码是否设置：0-未设置,1-设置,`wallet_pass_status` int(1) DEFAULT 0 COMMENT 钱包密码是否设置：0-未设置,1-设置,`wallet_status` int(1) DEFAULT 1 COMMENT 钱包是否冻结:0-冻结,1-未冻结,`email_status` int(1) DEFAULT 0 COMMENT 邮箱状态:0-未激活,1-激活,`message_status` int(1) DEFAULT 1 COMMENT 短信提醒开启：0-未开启,1-开启,`letter_status` int(1) DEFAULT 1 COMMENT 站内信提醒开启：0-未开启,1-开启,`emailmsg_status` int(1) DEFAULT 0 COMMENT 邮件提醒开启：0-未开启,1-开启,`create_time` datetime DEFAULT NULL COMMENT 创建时间,`update_time` datetime DEFAULT NULL COMMENT 更新时间,`state` int(1) DEFAULT 1 COMMENT 是否可用,0-不可用,1-可用,PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=用户状态表;支付账户表 用户交易的核心表，存储用户相关的账户资金信息。 CREATE TABLE `ms_user_wallet` (`wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 钱包ID,`user_id` int(11) NOT NULL COMMENT 用户ID,`wallet_pwd` varchar(32) DEFAULT NULL COMMENT 钱包密码,`total_account` decimal(20,2) DEFAULT 0.00 COMMENT 账户总额,`usable_money` decimal(20,2) DEFAULT 0.00 COMMENT 可用余额,`freeze_money` decimal(20,2) DEFAULT 0.00 COMMENT 冻结金额,`freeze_time` datetime DEFAULT NULL COMMENT 冻结时间,`thaw_time` datetime DEFAULT NULL COMMENT 解冻时间,`create_time` datetime DEFAULT NULL COMMENT 创建时间,`update_time` datetime DEFAULT NULL COMMENT 更新时间,`state` int(1) DEFAULT 1 COMMENT 是否可用,0-不可用,1-可用,PRIMARY KEY (`wallet_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=用户钱包;二、设计规范 1、涉及模块 通过上面几个表设计的案例，可以看到表设计关联到数据库的各个方面知识：数据类型，索引，编码，存储引擎等。 表设计是一个很大的命题，不过也遵循一个基本规范：三范式。 2、三范式 基础概念 一范式 表的列的具有原子性,不可再分解，即列的信息，不能分解,关系型数据库MySQL、Oracle等自动的满足。 二范式 每个事实的数据记录只会出现一次, 不会冗余, 通常设计一个主键来实现。 三范式 要求一个表中不包含已经存在于其它表的非主键信息，例如部门和员工的信息，员工表包含部门表的主键ID，则可以关联获取相关信息，没必要在员工表保存相关信息。 优缺点对比 范式化设计 范式化结构设计通常更新快，因为冗余数据较少，表结构轻巧，也更好的写入内存中。 但是查询起来涉及到关联，代价非常高，非常损耗查询性能。 反范式化设计 所有的数据都在一张表中，避免关联查询，索引的有效性更高，但是数据的冗余性极高。 建议结论 上述的两种设计方式在实际开发中都是不存在的，在实际开发中都是混合使用。 比如汇总统计，缓存数据，都会基于反范式化的设计。 三、字段属性 合适的字段类型对于高性能来说非常重要，基本原则如下：简单的类型占用资源更少；在可以正确存储数据的情况下，选最小的数据类型。 1、数据类型选择 整数类型 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，根据数据类型范围合理选择即可。 实数类型 FLOAT、DOUBLE、DECIMAL，建议资金货币相关类型使用高精度DECIMAL存储，或者把数据成倍扩大为整数，采用BIGINT存储，不过处理相对麻烦。 字符类型 CHAR、VARCHAR，长度不确定建议采用VARCHAR存储，不过VARCHAR类型需要额外开销记录字符串长度。 CHAR适合存储短字符，或者定长字符串，例如MD5的加密结构。 时间类型 DATETIME、TIMESTAMP，DATETIME保存大范围的值，精度秒。 TIMESTAMP以时间戳的格式，范围相对较小，效率也相对较高，所以通常情况建议使用。 MySQL的字段类型有很多种，可以根据数据特性选择合适的，这里只描述常见的几种类型。 2、基础用法操作 数据类型 修改字段类型ALTER TABLE ms_user_sso MODIFY state CHAR(1) DEFAULT 0 ;ALTER TABLE ms_user_sso MODIFY state INT(1) DEFAULT 1 COMMENT 状态：0不可用,1可用; 修改名称位置ALTER TABLE ms_user_sso CHANGE log_ip login_ip VARCHAR(32) AFTER update_time ;索引使用 索引类型：主键索引，普通索引，唯一索引，组合索引，全文索引。 这里展示普通索引的操作。 MySQL的核心模块，后续详说。 添加索引 ALTER TABLE ms_user_wallet ADD INDEX user_id_index(user_id) ;CREATE INDEX state_index ON ms_user_wallet(state) ; 查看索引 SHOW INDEX FROM ms_user_wallet; 删除索引 DROP INDEX state_index ON ms_user_wallet ; 修改索引 不具有真正意义上的修改，可以把原有的索引删除之后，再次添加索引。 外键关联 用处：外键关联的作用保证多个数据表的数据一致性和完整性，建表时先有主表，后有从表；删除数据表，需要先删从表，再删主表。 复杂场景不建议使用，实际开发中用的也不多。 添加外键 ALTER TABLE ms_user_wallet ADD CONSTRAINT user_id_out_key FOREIGN KEY(user_id) REFERENCES ms_user_center(id) ; 删除外键 ALTER TABLE ms_user_wallet DROP FOREIGN KEY user_id_out_key ;四、表结构管理 1、查看结构DESC ms_user_status ;SHOW CREATE TABLE ms_user_status ;2、字段结构 添加字段ALTER TABLE ms_user_status ADD `delete_time` datetime DEFAULT NULL COMMENT 删除时间 ;删除字段ALTER TABLE ms_user_status DROP COLUMN delete_time ;3、修改表名ALTER TABLE ms_user_center RENAME ms_user_info ;4、存储引擎 存储引擎SELECT VERSION() ; SHOW ENGINES ; MySQL 5.6 支持的存储引擎有InnoDB、MyISAM、Memory、Archive、CSV、BLACKHOLE等。 一般默认使用InnoDB，支持事务管理。 该模块MySQL核心，后续详解。 修改引擎 数据量大的场景下，存储引擎修改是一个难度极大的操作，容易会导致表的特性变动，引起各种后续反应，后续会详说。 ALTER TABLE ms_user_sso ENGINE = MyISAM ;5、修改编码 表字符集默认使用utf8，通用，无乱码风险，汉字3字节，英文1字节，utf8mb4是utf8的超集，有存储4字节例如表情符号时使用。 查看编码SHOW VARIABLES LIKE character%;修改编码ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4; 五、数据管理 1、增删改查 添加数据 INSERT INTO ms_user_sso ( user_id,sso_id,sso_code,create_time,update_time,login_ip,state)VALUES (1,SSO,SSO,2019-12-24 11:56:57,2019-12-24 11:57:01,127.0.0.1,1 ); 更新数据 UPDATE ms_user_sso SETuser_id = 1,sso_id = SSO,sso_code = SSO, create_time = 2019-11-24 11:56:57,update_time = 2019-11-24 11:57:01, login_ip = 127.0.0.1,state = 1WHERE user_id = 1; 查询数据 一般情况下都是禁止使用 select* 操作。 SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state FROM ms_user_sso WHERE user_id = 1; 删除数据 DELETE FROM ms_user_sso WHERE user_id = 2 ; 不带where条件，就是删除全部数据。 原则上不允许该操作，优化篇会详解。 TRUNCATE TABLE也是清空表数据，但是占用的资源相对较少。 2、数据安全 不可逆加密 这类加密算法，多用来做数据验证操作，比如常见的密码验证。 SELECT MD5(cicada)=b1241ad2cfbd0c44efda1b6b6ba ;SELECT SHA(cicada)=a2e4fd34e1dfc4dedc7d;SELECT PASSWORD(smile)=*B4FB95D86DCFC3F33ADC742CD ;可逆加密 安全性要求高的系统，需要做三级等保，对数据的安全性极高，数据在存储时必须加密入库，取出时候需要解密，这些就需要可逆加密。 SELECT DECODE(ENCODE(,key_salt),key_salt) ;SELECT AES_DECRYPT(AES_ENCRYPT(cicada,salt123),salt123); 上述数据安全的管理，也可以基于应用系统的服务(代码)层进行处理，相对专业的流程是从数据生成源头处理，规避数据传递过程泄露，造成不必要的风险。

深入理解MySQL核心技术的内容简介

从公共可用性的意义上讲，MySQL源代码是开放源代码，但如果对其不了解，则实质上，它对于您来说是封闭的。 MysQL开发团队的前成员Sasha Pachev通过《深入理解MySQL核心技术》给出了MySQL 5的全面指南，揭示了这一强大数据库的内部运作。 您将直奔MySQL核心技术，了解各种数据结构和各种方便的功能的运作情况，了解如何添加新的存储引擎和配置选项等。 《深入理解MySQL核心技术》从结构概况讲起，在这一部分解释了MysQL的不同组件是如何协同工作的。 接着将学习设置有效的可编译代码副本的步骤，然后使用基本架构添加自己的配置变量和存储引擎。