MySQL 学习笔记三

作者: 东山絮柳仔

41.RESET SLAVE For a server where GTIDs are in use (gtid_mode is ON), issuing RESET SLAVE has no effect on the GTID execution history. The statement does not change the values of gtid_executed or gtid_purged, or the mysql.gtid_executed table. If you need to reset the GTID execution history, use RESET MASTER, even if the GTID-enabled server is a replica where binary logging is disabled. To use RESET SLAVE, the replication threads must be stopped, so on a running replica use STOP SLAVE before issuing RESET SLAVE. To use RESET SLAVE on a Group Replication group member, the member status must be OFFLINE, meaning that the plugin is loaded but the member does not currently belong to any group. A group member can be taken offline by using a STOP GROUP REPLICATION statement. 42.SHOW SLAVE STATUS Master_Log_File The name of the source binary log file from which the I/O thread is currently reading. Read_Master_Log_Pos The position in the current source binary log file up to which the I/O thread has read. Relay_Log_File The name of the relay log file from which the SQL thread is currently reading and executing. Relay_Log_Pos The position in the current relay log file up to which the SQL thread has read and executed. Relay_Master_Log_File The name of the source binary log file containing the most recent event executed by the SQL thread. Exec_Master_Log_Pos The position in the current source binary log file to which the SQL thread has read and executed, marking the start of the next transaction or event to be processed.You can use this value with the CHANGE MASTER TO statement's MASTER_LOG_POS option when starting a new replica from an existing replica, so that the new replica reads from this point.The coordinates given by (Relay_Master_Log_File, Exec_Master_Log_Pos) in the source's binary log correspond to the coordinates given by (Relay_Log_File, Relay_Log_Pos) in the relay log. Inconsistencies in the sequence of transactions from the relay log which have been executed can cause this value to be a “low-water mark”. In other words, transactions appearing before the position are guaranteed to have committed, but transactions after the position may have committed or not. Retrieved_Gtid_Set The set of global transaction IDs corresponding to all transactions received by this replica. Empty if GTIDs are not in use.This is the set of all GTIDs that exist or have existed in the relay logs. Each GTID is added as soon as the Gtid_log_event is received. This can cause partially transmitted transactions to have their GTIDs included in the set.When all relay logs are lost due to executing RESET SLAVE or CHANGE MASTER TO, or due to the effects of the –relay-log-recovery option, the set is cleared. When relay_log_purge = 1, the newest relay log is always kept, and the set is not cleared. Executed_Gtid_Set The set of global transaction IDs written in the binary log. This is the same as the value for the global gtid_executed system variable on this server, as well as the value for Executed_Gtid_Set in the output of SHOW MASTER STATUS on this server. Empty if GTIDs are not in use. 43.FLUSH Statement The FLUSH statement has several variant forms that clear or reload various internal caches, flush tables, or acquire locks. To execute FLUSH, you must have the RELOAD privilege. FLUSH HOSTS Empties the host cache and the Performance Schema host_cache table that exposes the cache contents, and unblocks any blocked hosts. Flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs for connections from legitimate hosts. (See Section B.4.2.5, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 100. To avoid this error message, start the server with max_connect_errors set to a large value. FLUSH LOGS Closes and reopens any log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. If relay logging is enabled, the sequence number of the relay log file is incremented by one relative to the previous file.FLUSH LOGS has no effect on tables used for the general query log or for the slow query log. FLUSH SLOW LOGS Closes and reopens any slow query log file to which the server is writing. FLUSH TABLES Closes all open tables, forces all tables in use to be closed, and flushes the query cache and prepared statement cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.FLUSH TABLES is not permitted when there is an active LOCK TABLES … READ. To flush and lock tables, use FLUSH TABLES tbl_name … WITH READ LOCK instead. FLUSH TABLES tbl_name [, tbl_name] … With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. If a named table does not exist, no error occurs. 44 Add PRIMARY KEY If you create a table without a primary key, InnoDB chooses one for you, which can be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY clause as part of the CREATE TABLE statement.The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it is more efficient than using ALGORITHM=COPY because:

• No undo logging or associated redo logging is required for ALGORITHM=INPLACE. These operations add overhead to DDL statements that use ALGORITHM=COPY.

• The secondary index entries are pre-sorted, and so can be loaded in order.

• The change buffer is not used, because there are no random-access inserts into the secondary indexes. Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement. 45.creates a new clustered index MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database. DDL support for column operations

| **Operation**                                     | **In Place** | **Rebuilds Table** | **Permits Concurrent DML** | **Only Modifies Metadata** | **Remark**                                                                                                                                                                       |
| Adding a column                                   | Y            | Y                  | Y\*                        | N                          | Concurrent DML is not permitted when adding an auto-increment column.                                                                                                            |
| Dropping a column                                 | Y            | Y                  | Y                          | N                          |                                                                                                                                                                                  |
| Renaming a column                                 | Y            | N                  | Y\*                        | Y                          | To permit concurrent DML, keep the same data type 、\[NOT\] NULL attribute and only change the column name.Be not supported for renaming a generated column                       |
| Reordering columns                                | Y            | Y                  | Y                          | N                          | To reorder columns, use FIRST or AFTER in CHANGE or MODIFY operations. Data is reorganized substantially, making it an expensive operation.                                      |
| Setting a column default value                    | Y            | N                  | Y                          | Y                          | Only modifies table metadata. Default column values are stored in the .frm file for the table, not the InnoDB data dictionary.                                                   |
| Changing the column data type                     | N            | Y                  | N                          | N                          | Changing the column data type is only supported with ALGORITHM=COPY.                                                                                                             |
| Extending VARCHAR column size                     | Y            | N                  | Y                          | Y                          | The number of length bytes required by a VARCHAR column must remain the same.                                                                                                    |
| Dropping the column default value                 | Y            | N                  | Y                          | Y                          |                                                                                                                                                                                  |
| Changing the auto increment value                 | Y            | N                  | Y                          | N\*                        | Modifies a value stored in memory, not the data file.                                                                                                                            |
| Making a column NULL                              | Y            | Y\*                | Y                          | N                          | Rebuilds the table in place.                                                                                                                                                     |
| Making a column NOT NULL                          | Y\*          | Y\*                | Y                          | N                          | Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. |
| Modifying the definition of an ENUM or SET column | Y            | N                  | Y                          | Y                          |                                                                                                                                                                                  | DDL support for index operations

| Operation                            | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata | Remark                                                                                                                                                                   |
| Creating or adding a secondary index | Y        | N              | Y                      | N                      | The table remains available for read and write operations while the index is being created.                                                                              |
| Dropping an index                    | Y        | N              | Y                      | Y                      | The table remains available for read and write operations while the index is being dropped.                                                                              |
| Renaming an index                    | Y        | N              | Y                      | Y                      |                                                                                                                                                                          |
| Adding a FULLTEXT index              | Y\*      | N\*            | N                      | N                      | Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Additional FULLTEXT indexes may be added without rebuilding the table. |
| Adding a SPATIAL index               | Y        | N              | N                      | N                      |                                                                                                                                                                          |
| Changing the index type              | Y        | N              | Y                      | Y                      |                                                                                                                                                                          |

48.Online DDL Performance Online DDL improves several aspects of MySQL operation:

• Applications that access the table are more responsive because queries and DML operations on the table can proceed while the DDL operation is in progress. Reduced locking and waiting for MySQL server resources leads to greater scalability, even for operations that are not involved in the DDL operation.

• In-place operations avoid the disk I/O and CPU cycles associated with the table-copy method, which minimizes overall load on the database. Minimizing load helps maintain good performance and high throughput during the DDL operation.

• In-place operations read less data into the buffer pool than the table-copy operations, which reduces purging of frequently accessed data from memory. Purging of frequently accessed data can cause a temporary performance dip after a DDL operation. 49.The LOCK during a DDL operation LOCK clauses are described below, in order of least to most restrictive: • LOCK=NONE: Permits concurrent queries and DML. For example, use this clause for tables involving customer signups or purchases, to avoid making the tables unavailable during lengthy DDL operations. • LOCK=SHARED: Permits concurrent queries but blocks DML. For example, use this clause on data warehouse tables, where you can delay data load operations until the DDL operation is finished, but queries cannot be delayed for long periods. • LOCK=DEFAULT: Permits as much concurrency as possible (concurrent queries, DML, or both). Omitting the LOCK clause is the same as specifying LOCK=DEFAULT. Use this clause when you know that the default locking level of the DDL statement will not cause availability problems for the table. • LOCK=EXCLUSIVE: Blocks concurrent queries and DML. Use this clause if the primary concern is finishing the DDL operation in the shortest amount of time possible, and concurrent query and DML access is not necessary. You might also use this clause if the server is supposed to be idle, to avoid unexpected table accesses. 50.在线修改 注意1 .在线修改拓展VARCHAR字段的长度。

解释说明: In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). 注意2 .在线收缩VARCHAR字段

解释说明:Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY). 注意3 .行的最大长度,即受限与最大行长度的最大字段长度。
The maximum row length is slightly less than half a database page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row length is slightly less than 8KB for the default 16KB InnoDB page size.
LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB. —-个人学习总结笔记,可能比较粗糙,观者见谅。





  • MySQL实战宝典-27分布式事务:我们到底要不要使用2PC? 27 分布式事务:我们到底要不要使用 2PC?计。但是我们一直在回避分布式数据库中最令人头疼的问题,那就是分布式事务。 今天,我们就来学习分布式事务的概念,以及如何在海量互联网业务中实现它。 分布式事务概念 事务的概念相信你已经非
  • MySQL实战宝典-11索引出错:请理解CBO的工作原理 11 索引出错:请理解 CBO 的工作原理的使用已经有了一定的了解。 而在实际工作中,我也经常会遇到一些同学提出这样的问题:MySQL 并没有按照自己的预想来选择索引,比如创建了索引但是选择了全表扫描,这肯定是 MySQL 数据库的
  • MySQL实战宝典-21数据库备份:备份文件也要检查! 21 数据库备份:备份文件也要检查!据库可以进行切换(比如 20 讲基于复制或者 InnoDB Cluster 技术的高可用解决方案)。 除了高可用设计外,对架构师来说,还要做好备份架构的设计。因为我们要防范意外情况的发生,比如黑客删
  • MySQL实战宝典-15MySQL复制:最简单也最容易配置出错 15 MySQL 复制:最简单也最容易配置出错结构设计、索引设计。对业务开发的同学来说,掌握这些内容已经能很好地面向业务逻辑进行编码工作了。 但是业务需要上线,所以除了表和索引的结构设计之外,你还要做好高可用的设计。因为在真实的生产环
  • MySQL实战宝典-07表的访问设计:你该选择SQL还是NoSQL? 07 表的访问设计:你该选择 SQL 还是 NoSQL?的访问选型。这样一来,字段类型选择 + 物理存储设计 + 表的访问设计,就完成了表结构设计的所有内容。 前面 6 讲,我演示的都是通过 SQL 的方式对表进行访问,但从 MySQ
  • MySQL实战宝典-17高可用设计:你怎么活用三大架构方案? 17 高可用设计:你怎么活用三大架构方案?是为了铺垫 MySQL 数据库的高可用架构设计。因为复制是高可用的基础,但只用复制同步数据又远远不够,你还要结合自己的业务进行高可用设计。 同时,高可用也不仅仅是数据库的事情,你要从业务的全流
  • MySQL实战宝典-06表压缩:不仅仅是空间压缩 06 表压缩:不仅仅是空间压缩用户表、订单表。既然我们已经掌握了表的逻辑设计,那这一讲就继续学习不同业务表的物理存储设计。 据我观察,很多同学不会在表结构设计之初就考虑存储的设计,只有当业务发展到一定规模才会意识到问题的严重性。而物理
  • MySQL实战宝典-19高可用套件:选择这么多,你该如何选? 19 高可用套件:选择这么多,你该如何选?求,通过无损半同步复制的方式进行三园区的同城容灾设计,以及三地务中心的跨城容灾设计。 但是当数据库发生宕机时,MySQL 的主从复制并不会自动地切换,这需要高可用套件对数据库主从进行管理。
  • MySQL实战宝典-09索引组织表:万物皆索引 09 索引组织表:万物皆索引步深入了解 MySQL 的 B+ 树索引的具体使用,这一讲我想和你聊一聊 MySQL InnoDB 存储引擎的索引结构。 InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎,在海量大并发的 OL
  • MySQL实战宝典-12JOIN连接:到底能不能写JOIN? 12 JOIN 连接:到底能不能写 JOIN?优工作。但除了单表的 SQL 语句,还有两大类相对复杂的 SQL,多表 JOIN 和子查询语句,这就要在多张表上创建索引,难度相对提升不少。 而很多开发人员下意识地认为 JOIN 会降低
  • 近期文章