文章目录

  • 定期分析表、检查表和优化表
  • 一、分析表
  • 二、检查表
  • 三、优化表


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

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