定期分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
本语句用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表,本语句与使用myisamchk -a相当。
MySQL使用已存储的关键字分布来决定,当您对除常数以外的对象执行联合时,表按什么顺序进行联合。
mysql> analyze table a;
+--------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+-----------------------------+
| test.a | analyze | status | Table is already up to date |
+--------+---------+----------+-----------------------------+
1 row in set (0.00 sec)
定期检查表
CHECK TABLE tbl_name [, tbl_name] [option]
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表,关键字统计数据被更新。
mysql> check table a;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.a | check | status | OK |
+--------+-------+----------+----------+
1 row in set (0.00 sec)
CHECK TABLE也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在。
我们为上面的表a创建一个视图
mysql> create view a_view as select * from a;
Query OK, 0 rows affected (0.02 sec)
然后CHECK一下该视图,发现没有问题
mysql> check table a_view;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.a_view | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
现在删掉视图依赖的表
mysql> drop table a;
Query OK, 0 rows affected (0.01 sec)
再CHECK一下刚才的视图,发现报错了
mysql> check table a_view/G;
*************************** 1. row ***************************
Table: test.a_view
Op: check
Msg_type: Error
Msg_text: Table 'test.a' doesn't exist
*************************** 2. row ***************************
Table: test.a_view
Op: check
Msg_type: Error
Msg_text: View 'test.a_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
Table: test.a_view
Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)
ERROR:
No query specified
定期优化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。
在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表,OPTIMIZE TABLE按如下方式操作:
如果表已经删除或分解了行,则修复表。
如果未对索引页进行分类,则进行分类。
如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复),则进行更新。
mysql> OPTIMIZE table a;
+--------+----------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-----------------------------+
| test.a | optimize | status | Table is already up to date |
+--------+----------+----------+-----------------------------+
1 row in set (0.00 sec)
****
以上某些的段落是直接摘自MySQL的中文手册,详细可以直接查看MySQL的帮助手册,这里只是简单指出几种定期优化的方式,需要注意的是无论是ANALYZE,CHECK还是OPTIMIZE在执行期间将对表进行锁定,因此请注意这些操作要在数据库不繁忙的时候执行
****
参考
《MySQL 5.1参考手册》
by 陈于

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

WebStorm Mac version
Useful JavaScript development tools

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Dreamweaver CS6
Visual web development tools

Atom editor mac version download
The most popular open source editor

SublimeText3 English version
Recommended: Win version, supports code prompts!
