MySQL 学习笔记(二)
23.提升回滚大表操作的方法
DBA有时会遇到操作一张大表,发现操作时间过长或者影响在线业务了,想要回退大表操作。对于提升大事务回滚效率,官方提供了两种方法:
一是增加innodb_buffer_pool_size参数大小,二是合理利用innodb_force_recovery=3参数,跳过事务回滚过程。第一种方式比较温和,innodb_buffer_pool_size参数是可以动态调整的,可行性也较高。第二种方式相较之下较暴力,但效果较好。
第二种方法操作流程如下:
kill -9 MySQL 进程;备份MySQL数据及日志目录;为MySQL Server设置innodb_force_recovery=3参数;然后启动MySQL进程;正常关闭MySQL Server进程;去掉innodb_force_recovery=3参数启动MySQL进程。完成恢复过程。(innodb_force_recovery这个参数一般都用于“严重故障排除场景”,生产环境慎用,若用于生产环境需首先明确innodb_force_recovery设置对现有生产数据可能的影响情况)。
在完成最后启动操作之后,错误日志种会记录一条“【note】Inn哦DB:Rollback of non-prepared transactions completed”信息。此方式无需等待事务回滚操作,完成上述操作步骤的时间即位环境恢复的时间。
两种方式各有自己的优点,第一种方式对线上业务系统影响较小,不会中断在线业务。第二种方式效果更显著,会短暂影响业务连续,回滚所有没有提交的事务。
24 关于log_error_verbosity参数设置
参数取值 | 影响范围 | 是否默认 |
1 | errors only. | 否 |
2 | errors and warnings. | 否 |
3 | errors, warnings, and notes.the server logs aborted connections and access-denied errors for new connection attempts | 是 |
25 Error log 文件切割
If you flush the error log using FLUSH ERROR LOGS, FLUSH LOGS, or mysqladmin flush-logs, the server closes and reopens any error log file to which it is writing. To rename an error log file, do so manually before flushing. Flushing the logs then opens a new file with the original file name. For example, assuming a log file name of host_name.err, to rename the file and create a new one, use the following commands:
mv host_name.err host_name.err-old mysqladmin flush-logs mv host_name.err-old backup-directory
---应注意文件Owner,防止没有权限写操作
26 密码在general query log 中的体现
Passwords in statements written to the general query log are rewritten by the server not to occur literally in plain text. Password rewriting can be suppressed for the general query log by starting the server with the --log-raw option. This option may be useful for diagnostic purposes, to see the exact text of statements as received by the server, but for security reasons is not recommended for production use. --P853
27 binlog文件大于定义的max_binlog_size的情况
A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files.
28 关于参数binlog_cache_size的解读
binlog_cache_size:当事务开始时,MySQL会分配一个binlog_cache_size大小的缓冲区(默认32K)给session用于存储二进制日志的缓存。如果事务大于binlog_cache_size,线程将打开一个 临时文件 用于存储事务(默认 /tmp 下,参照参数tmpdir),当线程结束时,临时文件会自动删除。
29 关于参数 binlog_error_action的解读
binlog_error_action:默认值为ABORT_SERVER,会使MySQL在写binlog遇到严重错误时直接退出(即Crash动作),比如磁盘满了,文件系统不可写入等。在ABORT_SERVER选项下,binlog和从库都是安全的,这是官方选择此默认值的原因。
30.MySQL单条记录最大长度限制
Row Size Limits The maximum row size for a given table is determined by several factors: The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
MySQL Server 层的限制比较宽,一条记录不要超过65535个字节即可。也就是说,即使你的存储引擎支持更大的行长度,但是MYSQL 依然限制 Row size为65535;
BLOB and TEXT 这两种类型字段只占用行存储的9-12个字节,其他的内容分开存储。
31.InnoDB存储引擎下Row的长度限制
InnoDB关于Row的长度限制相对严格,一条记录的长度,不能超过innodb_page_size大小的一半(实际上还会略小,因为需要扣除页中关于页及记录的元数据信息)。如果页的大小是MySQL官方默认的16K,那么此时的单条记录的长度不能超过8126Byte。InnoDB是以B+树来组织数据的,所以InnoDB一个页至少包含两行数据(否在数据结构会退化成单链表)。
32.VARCHAR 类型字段最多能存储的字符数
在utf8mb4 编码中,字符的最大编码长度是4,比如中文;所以为了保证存储的字符串实际存储空间小于65535字节,字符串长度不能大于 floor(65535/4)=16383。
在latin1 编码字符集中,VARCHAR 类型字段最多能存储65533 个字符;
在utf8 编码字符集中,VARCHAR 类型字段最多能存储21844 个字符;
在utf8mb4 编码字符集中,VARCHAR 类型字段最多能存储16383 个字符;
33.index key 的最大长度限制
Both DYNAMIC and COMPRESSED row formats support index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix configuration option, which is enabled by default.
在DYNAMIC and COMPRESSED 行格式下默认支持索引长度不能超过3072字节.3072字节除以 utf8mb4 的最大编码长度4字节,在主键字段上长度上限应该是768;在utf8 编码字符集中,字符的最大编码长度是3字节,比如中文;所以如果 name作为主键,这个字段字符长度不能超过 3072/3=1024。
34.字段个数的限制
单表有多少个列也是有限制的:(1)Server层规定一个表的字段个数最多为4096个;(2)InnoDB层规定一个表的字段个数最多为1017个。
35.设置账号的过期时间
create user test@'%' identified by 'XXXXX' password expire interval 90 day; alter user test@'%' identified by 'XXXXX' password expire interval 90 day; # 禁用过期,永久不过期: create user test@'%' identified by 'XXXXX' password expire never; alter user test@'%' identified by 'XXXXX' password expire never;
36 row-level locking and InnoDB storage engine
InnoDB tables use row-level locking so that multiple sessions and applications can read from and write to the same table simultaneously, without making each other wait or producing inconsistent results. For this storage engine, avoid using the LOCK TABLES statement, because it does not offer any extra protection, but instead reduces concurrency. The automatic row-level locking makes these tables suitable for your busiest databases with your most important data, while also simplifying application logic since you do not need to lock and unlock tables. Consequently, the InnoDB storage engine is the default in MySQL.
MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB. The locking operations themselves do not have much overhead. But because only one session can write to a table at any one time, for best performance with these other storage engines, use them primarily for tables that are queried often and rarely inserted into or updated.
37.Indexing a Generated Column to Provide a JSON Column Index
As noted elsewhere, JSON columns cannot be indexed directly. To create an index that references such a column indirectly, you can define a generated column that extracts the information that should be indexed, then create an index on the generated column.[这一点和MongoDB还是有差别的。]
Adding or dropping a secondary index on a virtual column is an in-place operation.
38.同实例,库之间表移动【Rename】
You can use RENAME TABLE to move a table from one database to another:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
Using this method to move all tables from one database to a different one in effect renames the database (an operation for which MySQL has no single statement), except that the original database continues to exist, albeit with no tables.Like RENAME TABLE, ALTER TABLE ... RENAME can also be used to move a table to a different database.
不支持:1.带有triggers的表移动;2.不支持TEMPORARY表;3.不支持视图移动。
39.文件导入导出
The LOAD DATA statement reads rows from a text file into a table at a very high speed. LOAD DATA is the complement of SELECT ... INTO OUTFILE.To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA.
You can also load data files by using the mysqlimport utility,mysqlimport operates by sending a LOAD DATA statement to the server.
40.UNLOCK TABLES
Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases
41.GTID的主从复制模式下 Slave不生成GTID
Slave节点读取Relay Log中从Master传送过来的GTID,并设置其gtid_next的值为该GTID值,从而告知Slave必须使用此GTID记录下一个事务。由于gtid_next不为空,slave不会尝试为该事务生成新的GTID。一个事务之后在集群中都会始终对应这个GTID值,且不会发生变化。
42.字符与字节
再次说明,需要注意的是varchar(N)中,N代表的是字符,而不是字节。字符集不同,每个字符需要的字节也不一样。在utf8字符集中,一个字符需要三个字节;utf8mb4字符集中,一个字符需要4个字节。(1)varchar字符集在存储时除了数据,还需要1-2字节的前缀,前缀长度表示varchar中的字节数,当字段的字节数不大于255时,需要1个字节前缀,当大于255时,需要两个字节长度。(2)innodb存储引擎,在扩大varchar字符长度时,当字节长度1-255之间或者256开始扩大,能用inplace特性,当从255扩大到256时,只能copy表。(3)varchar类型的缩小字符长度和字段数据类型更改都只能用copy的方式。
----个人学习总结笔记,可能比较粗糙,观者见谅。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库