文章目录
- 定期分析表、检查表和优化表
- 一、分析表
- 二、检查表
- 三、优化表
定期分析表、检查表和优化表
注意: ANALYZE、CHECK、OPTIMIZE、ALTER TABLE 执行期间将对表进行锁定,因此一定注意要在数据库不繁忙的时候执行相关的操作。
一、分析表
分析表,analyze 分析表语句用于分析和存储表的关键字分布,分析的结果可以使用系统得到更准确的统计信息,使得 SQL 能够生成正确的执行计划。在分析期间对表有一个读取锁定。这对于 MyISAM、BOB 和 InnoDB 表有作用。对于 MyISAM 表可以使用 myisamchk -a 相当。
mysql> analyze table payment;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sakila.payment | analyze | status | OK |
+----------------+---------+----------+----------+
1 row in set (0.10 sec)
二、检查表
检查表的作用是检查一个或者多个表是否有错误。 CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。
mysql> check table payment_myisam;
+-----------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| sakila.payment_myisam | check | status | OK |
+-----------------------+-------+----------+----------+
1 row in set (0.05 sec)
CHECK TABLE 还可以检查视图是否有错误,比如在视图定义中被引用的表已经不存在。
1)创建一个视图
mysql> create view v_payment_myisam as select * from payment_myisam;
Query OK, 0 rows affected (0.01 sec)
2)检查该视图
mysql> check table v_payment_myisam;
+-------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| sakila.v_payment_myisam | check | status | OK |
+-------------------------+-------+----------+----------+
1 row in set (0.00 sec)
3)删除视图依赖的表
mysql> drop table payment_myisam;
Query OK, 0 rows affected (0.00 sec)
4)再次 CHECK 该视图,可以看出提示出错的原因是 sakila.payment_myisam 不存在(Table 'sakila.payment_myisam' doesn't exist)
mysql> check table v_payment_myisam \G;
*************************** 1. row ***************************
Table: sakila.v_payment_myisam
Op: check
Msg_type: Error
Msg_text: Table 'sakila.payment_myisam' doesn't exist
*************************** 2. row ***************************
Table: sakila.v_payment_myisam
Op: check
Msg_type: Error
Msg_text: View 'sakila.v_payment_myisam' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
Table: sakila.v_payment_myisam
Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)
ERROR:
No query specified
三、优化表
如果已经删除了表的大部分,或者如果对含有可变长度行的表(含有 VARCHAR、BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但 OPTIMIZE TABLE 命令只对 MyISAM、BOB 和 InnoDB 表起作用。命令执行期间 MyISAM 表会全程锁表,而 InnoDB 表则会将优化命令转换为重建表和分析表两个操作,加锁时间也仅仅在整个工作的 prepare 和 commit 期间做短暂的加锁工作,对于表的读写几乎没有影响。
mysql> optimize table payment_myisam;
+-----------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| sakila.payment_myisam | optimize | status | OK |
+-----------------------+----------+----------+----------+
1 row in set (0.01 sec)
对于 InnoDB 引擎的表在大量删除数据后,可以通过 alter table 但是不修改引擎的方式来回收不用的空间。
mysql> alter table payment_myisam engine=innodb;
Query OK, 16049 rows affected (0.58 sec)
Records: 16049 Duplicates: 0 Warnings: 0
注意: ANALYZE、CHECK、OPTIMIZE、ALTER TABLE 执行期间将对表进行锁定,因此一定注意要在数据库不繁忙的时候执行相关的操作。