Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL how to achieve table maintenance, the article introduces in great detail, has a certain reference value, interested friends must read it!

Table maintenance

Why do you want to do table maintenance operations, what problems to solve?

Table maintenance operations are required in two cases, one is that the table is damaged due to the crash of the server, and the other is that the query processing of the table is slow.

The main tools for performing table maintenance are MySQL Workbench, MySQL Enterprise Monitor, SQL (DML) maintenance statements, mysqlcheck, myisamchk;. These tools are introduced one by one below.

1.1. Table maintenance SQL statement

The SQL statements used to perform table maintenance are: ANALYZE TABLE (update index statistics), CHECK TABLE (thorough integrity check), CHECKSUM TABLE (thorough integrity check), REPAIR TABLE (repair), and OPTIMIZE TABLE (optimization). Each statement contains one or more table names and optional keywords. Examples of maintenance statements and output:

Mysql > CHECK TABLE world_innodb.City

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | world_innodb.City | check | status | OK | |

+-+

After performing the requested operation, the server returns information about the results of the operation performed on the client. This information is displayed as a four-column result set:

L Table: indicates the table on which the operation is performed

L Op: indicates the operation (check, repair, analyze, or optimize)

L Msg_type: indicates success or failure

L Msg_text: provide additional information

1.1.1. ANALYZE TABLE statement

ANALYZE TABLE statements analyze and store key distribution statistics for tables for better query execution selection, processing InnoDB, NDB and MyISAM tables, and supporting partitioned tables

ANALYZE TABLE option: NO_WRITE_TO_BINLOG or LOCAL: disable binary logging

An example of a normal result of ANALYZE TABLE:

Mysql > ANALYZE LOCAL TABLE Country

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | world_innodb.Country | analyze | status | OK | |

+-+

When performing join operations on non-constant objects, MySQL uses the stored key distribution statistics to determine the order in which the optimizer joins tables. In addition, the key distribution determines the index that MySQL uses for specific tables in the query.

You can execute ANALYZE TABLE statements to analyze and store statistics, or you can configure InnoDB to automatically collect statistics after a large amount of data has changed or when table or index metadata is queried.

ANALYZE TABLE characteristics:

L use read locks to lock the table for InnoDB and MyISAM,MySQL during analysis.

This statement is equivalent to using mysqlcheck-- analyze.

L requires SELECT and INSERT permissions on the table.

L supports partition tables. You can also use ALTER TABLE...ANALYZE PARTITION to check one or more partitions.

If the table has not changed since the last ANALYZE TABLE statement was run, MySQL does not analyze the table. By default, MySQL writes ANALYZE TABLE statements to the binary log and copies them to the replication subordinate role. Logging is prohibited using the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

You can use the following options to control how MySQL collects and stores key distribution statistics:

L innodb_stats_persistent: when this option is ON, MySQL will enable the STATS_PERSISTENT setting for the newly created table. When using CREATE TABLE or ALTER TABLE statements, you can also set STATS_PERSISTENT on the table. By default, MySQL does not persist key distribution statistics on disk, so it is sometimes necessary to generate them (such as after a server restart). For STATS_PERSISTENT-enabled tables, MySQL stores its key distribution statistics on disk, eliminating the need to generate statistics for those tables frequently. Over time, the optimizer for this operation creates a more consistent query plan.

L innodb_stats_persistent_sample_pages:MySQL recalculates the statistics by reading the sample index page of the STATS_PERSISTENT table instead of the entire table. By default, 20 pages of samples are read. Increasing this number improves the quality of the generated statistics and query plans. Reducing this number reduces the cost of generating statistics.

L innodb_stats_transient_sample_pages: this option controls the number of index pages sampled from tables that do not have STATS_PERSISTENT settings.

The following options control how MySQL automatically collects statistics.

L innodb_stats_auto_recalc: when this option is enabled, if 10% of the rows in the STATS_PERSISTENT table have changed since the previous recalculation, MySQL automatically generates statistics for the table.

L innodb_stats_on_metadata: enable this option to update statistics when executing metadata statements such as SHOW TABLE STATUS or querying INFORMATION_SCHEMA.TABLES. This option is disabled by default.

1.1.2. CHECK TABLE statement

The ANALYZE TABLE statement checks the integrity of the table structure and checks for errors in the content, validates view definitions, supports partitioned tables, and processes InnoDB, CSV, MyISAM, and ARCHIVE tables

CHECK TABLE options:

Z. FOR UPGRADE: check whether the table is applicable to the current server.

Angular QUICK: do not scan lines to check for incorrect links.

If CHECK TABLE finds a problem with the InnoDB table, the server will shut down to prevent the error from spreading, and MySQL will write the error to the error log

CHECK TABLE characteristics:

For the MyISAM table, the key statistics are also updated.

Z. can also check if there is a problem with the view, such as the table referenced in the view definition no longer exists.

Z. supports partition tables. You can also use ALTER TABLE...CHECK PARTITION to check one or more partitions.

When using FOR UPGRADE, the server checks each table to determine if the table structure is compatible with the current version of MySQL. Incompatibility may occur due to changes in the storage format or sort order of a data type. If there is a potential incompatibility, the server runs a full check on the table. If the full check is successful, the server marks the. frm file of the table with the current MySQL version number. Tagging the .frm file ensures that future checks on tables of the same version as the server will be faster.

"FOR UPGRADE is recommended for InnoDB, MyISAM, and ARCHIVE storage engines." Use QUICK for InnoDB and MyISAM tables. MyISAM supports other options. Please visit

Http://dev.mysql.com/doc/refman/5.6/en/check-table.htm

CHECK TABLE statement

An example of a normal result of CHECK TABLE:

Mysql > CHECK TABLE Country

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | world_innodb.Country | check | status | OK | |

+-+

If the output of CHECK TABLE indicates that there is a problem with a table, fix the table. For example, you can use CHECK TABLE statements to detect hardware problems, such as memory failures or disk sector corruption, before repairing the table.

The Msg_text output column is usually OK. If the output is not OK or Table is already up to date, run a repair on the table. If the table is marked as corrupted or not closed properly, but CHECK TABLE does not find any problems in the table, the table is marked as OK.

1.1.3. CHECKSUM TABLE statement

The CHECKSUM TABLE statement reports the table checksum, which is used to verify that the contents of the table are the same before and after backup, rollback, or other operations

The CHECKSUM TABLE statement reads the entire table line by row to calculate the checksum

This behavior is provided by the default EXTENDED option.

The angular QUICK option is available for the MyISAM table.

This is the default option when the MyISAM CHECKSUM=1 setting is included.

An example of a CHECKSUM TABLE statement:

Mysql > CHECKSUM TABLE City

+-+ +

| | Table | Checksum |

+-+ +

| | world_innodb.City | 531416258 | |

+-+ +

CHECKSUM TABLE characteristics:

CHECKSUM TABLE requires SELECT permission on the table.

For tables that do not exist, CHECKSUM TABLE returns NULL and generates a warning.

If the EXTENDED option is used, the entire table is read row by row and the checksum is calculated.

If the QUICK option is used: the real-time table checksum will be reported (if available); otherwise NULL will be reported. This operation is very fast. Real-time checksum is enabled for the CHECKSUM=1 table by specifying the MyISAM table option when the table is created.

If neither QUICK nor EXTENDED is specified, MySQL is assumed to be EXTENDED (except for the MyISAM table of CHECKSUM=1).

The checksum value depends on the row format in the table. If the row format changes, the checksum also changes. For example, the storage format of VARCHAR has changed since MySQL 4.1, so the checksum value will change if the table contains the VARCHAR field after upgrading the table to a later version.

Note: if the checksums of the two tables is different, it is likely that the two tables are different in some way. However, because the hash functions used by CHECKSUM TABLE are not guaranteed to conflict, there is a slim possibility that two different tables will generate the same checksum.

1.1.4. OPTIMIZE TABLE statement

The OPTIMIZE TABLE statement cleans up the table by defragmenting the table, that is, defragmenting the table by rebuilding the table and releasing unused space; OPTIMIZE TABLE statement locks the table and updates index statistics during optimization, which is most suitable for fully populated permanent tables, supports processing of InnoDB, MyISAM and ARCHIVE tables, and supports partitioned tables

OPTIMIZE TABLE option: NO_WRITE_TO_BINLOG or LOCAL: disable binary logging.

OPTIMIZE TABLE characteristics:

Defragmentation involves reclaiming unused space generated by deletions and updates, as well as merging separated records and records stored in a discontiguous manner.

Z. requires SELECT and INSERT permissions on the table

Z. supports partition tables. You can also use ALTER TABLE...OPTIMIZE PARTITION to check one or more partitions.

For example, after modifying a large number of rows, you can use the OPTIMIZE TABLE statement to ReFactor an FULLTEXT index in InnoDB.

For InnoDB tables, OPTIMIZE TABLE maps to ALTER TABLE, which reconstructs the table to update index statistics and free unused space in the clustered index. InnoDB is not affected by fragmentation like other storage engines, so you don't need to use OPTIMIZE TABLE often.

Use OPTIMIZE TABLE on a table that uses the ARCHIVE storage engine to compress the table. The number of rows in the ARCHIVE table reported by SHOW TABLE STATUS is always accurate. An .ARN file may appear during the optimization operation.

OPTIMIZE TABLE statement

The following OPTIMIZE TABLE statement optimizes two fully populated tables in the mysql database:

Mysql > OPTIMIZE TABLE mysql.help_relation, mysql.help_topic

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | mysql.help_relation | optimize | status | OK | |

| | mysql.help_topic | optimize | status | OK | |

+-+

2 rows in set (0.00 sec)

For MyISAM tables, use the OPTIMIZE TABLE statement after you delete a large amount of content in the table or make multiple changes to a table that contains variable-length rows (a table that contains VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows remain in the linked list, while subsequent INSERT operations reuse the positions of previous rows.

OPTIMIZE TABLE works best with fully populated tables and does not change much. If the data changes a lot and needs optimization frequently, the advantage of optimization will be greatly reduced.

1.1.5. REPAIR TABLE statement

REPAIR TABLE statement repairs possibly corrupted MyISAM or ARCHIVE tables. InnoDB is not supported, but partitioned tables are supported.

REPAIR TABLE options:

Angular QUICK: repair only the index tree and try to repair only the index file, not the data file. This type of repair is similar to that performed by myisamchk-- recover-- quick.

Angular EXTENDED: create an index line by line (instead of creating an ordered index at once), and MySQL will create an index row by row instead of creating an ordered index at once. This type of repair is similar to the one performed by myisamchk-- safe-recover.

·USE_FRM: recreate the. MYI file using the .FRM file, but cannot be used for partitioning tables.

Z. NO_WRITE_TO_BINLOG or LOCAL: disable binary logging.

REPAIR TABLE characteristics:

The angular QUICK option: try to repair only the index file, not the data file. This type of repair is similar to that performed by myisamchk-- recover-- quick.

The angular EXTENDED option: MySQL will create the index row by row, rather than creating an ordered index at once. This type of repair is similar to the one performed by myisamchk-- safe-recover.

The angular USE_FRM option cannot be used for partitioned tables.

Z. requires SELECT and INSERT permissions on the table

Z. supports partition tables. You can also use ALTER TABLE...REPAIR PARTITION to check one or more partitions.

It is a good idea to back up the table before performing a table repair operation; in some cases, this operation may result in data loss. Possible reasons include, but are not limited to, file system errors.

If the server crashes during a REPAIR TABLE operation, to avoid further damage, another REPAIR TABLE should be performed immediately after the restart, followed by any other action.

If you often need to use REPAIR TABLE to recover from a corrupted table, try to find the root cause to prevent the corruption and avoid using REPAIR TABLE.

REPAIR TABLE statement

An example of a REPAIR TABLE statement:

Mysql > REPAIR TABLE mysql.help_relation

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | mysql.help_relation | repair | status | OK | |

+-+

1 row in set (0.00 sec)

1.2. Mysqlcheck client program

Mysqlcheck is a command line client for checking, repairing, analyzing, and optimizing tables; it is more convenient than issuing SQL statements, can work with InnoDB, MyISAM, and ARCHIVE tables, and supports three check levels: specific tables, specific databases, and all databases

Some mysqlcheck maintenance options:

Get-- analyze: execute ANALYZE TABLE.

Z.-- check: execute CHECK TABLE (default).

Get-- optimize: execute OPTIMIZE TABLE.

Get-- repair: execute REPAIR TABLE.

In some cases, mysqlcheck is more convenient than issuing SQL statements directly. For example, if you provide a database name as its parameter, mysqlcheck determines the tables contained in the database and issues statements to process all of these tables. You do not need to provide an explicit table name called parameters. In addition, because mysqlcheck is a command-line program, it can be easily used in operating system jobs that perform scheduled maintenance.

For the mysqlcheck client program, Oracle recommends that you first run mysqlcheck without any options, and then rerun it if you need to fix it.

Some mysqlcheck modification options:

Get-- repair-- quick: try to fix it quickly.

Z.-- repair: normal repair (if quick fix fails).

Get-- repair-- force: force repair.

Mysqlcheck example:

Shell > mysqlcheck-- login-path=admin world_innodb

Shell > mysqlcheck-uroot-p mysql user-- repair

Shell > mysqlcheck-uroot-p-- all-databases # will check all tables in all databases

Shell > mysqlcheck-login-path=admin-analyze-all-databases

By default, mysqlcheck interprets its first non-option parameter as the database name and checks all tables in the database. If there are any other parameters after the database name, these parameters are treated as table names, so only those tables are checked.

1.3. Myisamchk utility

Myisamchk is a non-client utility for checking MyISAM tables, similar to mysqlcheck, except that myisamchk can enable or disable indexes and access table files directly (rather than through the server), which avoids and publishes access.

Some myisamchk options:

_ recover: repair the table.

_

Myisamchk example:

Shell > myisamchk / var/lib/mysql/mysql/help_topic

Shell > myisamchk help_category.MYI

Shell > myisamchk-- recover help_keyword

In theory, myisamchk and mysqlcheck have similar uses. However, myisamchk does not communicate with the MySQL server, but accesses the table file directly.

How to avoid and publish access while using myisamchk to perform table maintenance?

a. Ensure that the server does not access the table that is being processed. One way to do this is to lock the table or stop the server.

b. At the command prompt, change the location to the database directory where the table is located. This is a subdirectory of the server data directory, which has the same name as the database where the table to be checked is located. (the location is changed to make it easier to reference table files. You can skip this step, but the myisamchk must contain the directory where the table is located. )

c. Call myisamchk, using options to indicate the action to be performed, followed by parameters to specify the table on which myisamchk should perform the action. These parameters can be the name of the table or the file name of the index file of the table. The index file name is the same as the table name and contains the .MYI suffix. Therefore, tables can be referenced through table_name or table_name.MYI.

d. Restart the server.

Note: please try-- recover first because-- safe-recover is slow.

Options for mysqlcheck and myisamchk to control the type of maintenance performed:

Both mysqlcheck and myisamchk use several options to control the type of table maintenance operations performed. The above table summarizes some of the most commonly used options, most of which apply to both programs. If it does not apply to both programs, it will be recorded in the relevant option description.

Z.-- analyze: analyzes the distribution of key values in the table. This can improve query performance by speeding up index-based lookups.

Z.-- auto-repair: if the check operation finds a problem, it automatically fixes the table that has a problem.

-- check or-c: check the table for problems. If no other action is specified, the default action.

-- check-only-changed or-C: skip table checking (except for tables that have changed since the last check or tables that have not been properly closed). If the server crashes while the table is open, the latter occurs.

-- fast or-F: skip table checking (except for tables that are not normally closed).

0-- extended,-- extend-check, or-e: run an extension table check. For mysqlcheck, this option is used in conjunction with the repair option to perform a more thorough repair than if you use the repair option alone. That is,-- repair-- extended performs a more thorough repair operation than-- repair does.

-- medium-check or-m: run an intermediate table check.

-- quick or-Q: for mysqlcheck,-quick with no repair option causes only the index file to be checked, not the data file. For both programs, using-- quick with the repair option causes the program to repair only the index file, not the data file.

0-- repair,-- recover, or-r: run the table repair operation.

1.4. InnoDB table maintenance

After a failure, InnoDB automatically recovers. Use CHECK TABLE or client programs to identify inconsistencies, incompatibilities, and other issues. You can also restore the table by dumping it using mysqldump:

Shell > mysqldump >

Then, delete the table and recreate it from the dump file.

Shell > mysql

< 要在崩溃后修复表，请使用--innodb_force_recovery 选项重新启动服务器或者从备份中恢复表。使用ALTER TABLE 进行优化时，将重构表并释放群集索引中未使用的空间。 如果表检查表明存在问题，请通过使用mysqldump 转储该表、删除该表并从转储文件重新创建该表来将其恢复到一致状态。 如果MySQL 服务器或其运行主机崩溃，则某些InnoDB 表可能处于不一致状态。在InnoDB 的启动序列中，会执行自动恢复。服务器很少因为自动恢复故障而无法启动。如果出现此情况，请使用以下过程： A. 重新启动服务器，将--innodb_force_recovery 选项的值设置为1 到6 之间的值。这些值表示增加警告级别以避免崩溃，以及针对已恢复的表中可能存在的不一致状况增加容错级别。最好从值4 开始，该值可以阻止插入缓冲区合并操作。 B. 当在--innodb_force_recovery 设置为非零值的情况下启动服务器时，InnoDB将阻止INSERT、UPDATE 或DELETE 操作。因此，您应转储InnoDB 表，然后在该选项生效时将这些表删除。再在不使用--innodb_force_recovery 选项的情况下重新启动服务器。服务器启动之后，将从转储文件恢复InnoDB 表。 C. 如果前述步骤失败，则从前一个备份恢复表。 访问http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html 了解有关对损坏的数据库启动InnoDB 的更多信息。 1.5. MyISAM 表维护 MyISAM 表维护对于动态格式表和静态格式表，默认的CHECK TABLE 检查类型均为MEDIUM。如果将静态格式表类型设置为CHANGED 或FAST，则默认选项为QUICK。对于CHANGED 和FAST，将跳过行扫描，因为这些行很少损坏。如果表被标记为"已损坏"或"未正常关闭"，则CHECK TABLE 将更改表。如果未在表中发现任何问题，则会将表的状态标记为"最新"。如果表已损坏，则问题最有可能存在于索引而不是数据中。 shell>

Myisamchk-medium-check

Set up the server to run the check and automatically repair the table. Use the-- myisam-recover option to enable automatic repair. The server checks each MyISAM table the first time it accesses it after startup to ensure that the tables were closed correctly the previous time.

-- myisam-recover option values can contain a comma-separated list of values consisting of one or more of the following values:

Zug.DEFAULT: default check.

Angular BACKUP: instructs the server to back up all tables that must be changed.

Angular FORCE: perform a table recovery, even if it may result in multiple rows of data loss.

Z. QUICK: perform a quick recovery. The restore skips tables that do not contain row intervals (also known as "holes") caused by deletions or updates.

Forces the recovery of MyISAM tables from config files. For example, to instruct the server to perform a forced restore of the MyISAM table on which the problem was found, but to back up all tables it changed at the same time, add the following to the options file:

[mysqld]

Myisam-recover=FORCE,BACKUP

1.6. MEMORY table maintenance

When you delete multiple rows using the DELETE...WHERE statement, the MEMORY table does not free memory. To free memory, you must perform a null ALTER TABLE operation.

1.7. ARCHIVE table maintenance

The ARCHIVE table compresses the table row when it is inserted and decompresses the row as needed when it is retrieved. Some SELECT statements may weaken compression. Better compression can be achieved with OPTIMIZE TABLE or REPAIR TABLE, but OPTIMIZE TABLE is valid only when the table is not accessed (read or write).

The above is all the contents of the article "how to achieve table maintenance in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.