定期分析表、检查表和优化表

定期分析表、检查表和优化表

​ 注意： 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 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。